SQL commands are essential for anyone working with databases. Mastering these commands is the key to performing queries, inserting or updating information, and solving problems practically in everyday tasks.
But do you know what the main commands are and how to apply them?
In this article, we will explore the categories of SQL commands, explaining their differences and presenting practical examples to facilitate understanding.
With this knowledge, you will be better prepared to handle challenging tasks in database management.
Enjoy your reading!
What are SQL commands?
SQL commands are instructions used to manage and manipulate data in relational databases.
The acronym SQL stands for “Structured Query Language”.
These commands allow you to perform operations such as querying, inserting, updating, and deleting information in systems like MySQL, PostgreSQL, Oracle, and SQL Server.
Although it is a standard language, each Database Management System (DBMS) has its own particularities.
SQL is widely used by various professionals, such as data analysts and Excel users, facilitating data extraction and analysis without requiring in-depth programming knowledge.
What are the 16 main SQL commands?
We can divide SQL commands into groups: DDL, DQL, DML, DCL, and TCL.
DDL (Data Definition Language) is a structural language. It serves to define aspects of the database structure, such as creating tables, deleting tables, altering tables, and more. It focuses more on a broad aspect and less on the data itself.
DQL (Data Query Language) commands are more focused on querying. That is, they are used to retrieve data and read or manipulate it. They indicate where the data you want to manage is located at a given moment.
After the query, you can use DML (Data Manipulation Language) commands, which are specific to direct manipulation.
DMLs are used to insert, update, modify, and delete data from a table. These actions directly change the data, rather than altering the structure (like DDLs).
DCLs (Data Control Languages) are extremely important in the context of increased emphasis on security: they are geared towards access control and authorization/permission for the use and viewing of data.
Furthermore, they control access privileges and can also revoke rights.
Finally, among the categories, we have TCL/DTL (Data Transaction Language). These are commands for data transactions.
Now, let’s look at a list of the main SQL commands, including practical examples to make them easier to understand.
1. CREATE DATABASE
The first command is CREATE DATABASE, which is used to create a database where we will store tables.
It belongs to the DDL (Data Definition Language) category, being the first step in structuring a database.
With it, you define the space where tables and other elements will be stored. For example:
CREATE DATABASE 365education_trainings;
This code creates a database called “my_database”. After that, it’s possible to proceed to creating tables and inserting data.
2. USE
After creating a database, the next step is to select it to work with. The USE command allows you to choose which database will be used at the moment. This defines the context for subsequent commands. Example:
USE 365education;
With this command, SQL identifies that all subsequent operations, such as table creation or queries, will be executed within the database “my_database”. This is an essential DDL command to begin manipulating the data.
3. CREATE TABLE
With the database selected, the next step is to create tables. The CREATE TABLE command allows you to define a new table with its columns and data types. For example:
CREATE TABLE clients (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
data_birth DATE
);
In this example, a table called “customers” is created with four columns: id, name, email, and birth_date. The ID is defined as the primary key, which serves to uniquely identify each record.
The CREATE TABLE command is a DDL command. It can be used after CREATE DATABASE, once the database has already been established.
The CREATE TABLE statement should specify the table columns and the data types to be inserted. It also helps configure the primary key, the element that allows tables to be linked.
4. INSERT INTO
If you don’t have any information in your table and want to add it, simply use the INSERT INTO command, which is the command to add data to a table using SQL.
The INSERT INTO command is a DML command, meaning it’s designed to manage and manipulate specific data within tables.
Therefore, the INTO clause must specify which table we are referring to. It is also necessary to specify the table fields that will receive the new values.
5. SELECT FROM
The SELECT FROM statement is a perfect example of a DQL, or data query command. It must be accompanied by a specific indication of where the data will be retrieved from (the FROM clause).
The SELECT statement is one of the most important and versatile SQL commands when working with SQL. It can be used for simple read queries, as well as for more complex processes. It all depends on the need.
SELECT * FROM Sales WHERE State= 'RJ';
So with this code, you will be able to select specific columns or all columns (using the *) from a table.
6. ORDER BY
Another DML (data manipulation) example. The function of ORDER BY is simply to sort the data according to some logic, some specific criterion, such as in ascending order (ASC) or descending order (DESC).
This command will help you sort the information within a column to make it easier to view.
SELECT name, birth_date FROM clients ORDER BY birth_date DESC;
It is used within a SELECT statement, specifying which element will be the reference for sorting.
7. WHERE
The WHERE clause is used in query cases to help specify the data that will be queried and manipulated.
It is used to filter data in a table.
Using filters makes it easier to visualize the data, showing only the information you need to see.
SELECT* FROM Sales WHERE State = 'RJ';
It is important to use this element to define exactly which data will be used for modification, as well as the line.
When people forget the WHERE clause, there’s a high risk of making a global change, meaning it affects all rows in the table, which creates a lot of confusion.
8. UPDATE
The UPDATE command is a classic example of DML, used to modify records in an existing table. It allows you to update specific values in one or more columns, based on a user-defined condition.
For example, if a person enters information on a form and then requests a change, the database programmer will go to that row and column and make the change using the UPDATE statement. See the example:
UPDATE Sales SET Value = 200 WHERE Course = 'Advanced Excel';
In this case, all records in the Sales table where the course is ‘Advanced Excel’ will have their value updated to 200.
9. DELETE FROM
The DELETE FROM statement is also a DML command that requires careful use. It deletes data based on a defined criterion or a specific piece of data.
Unlike TRUNCATE (which we will see later), it allows you to delete only rows that meet a defined condition.
For example, if you want to exclude all sales made in a specific state:
DELETE FROM Sales WHERE State = 'SP';
In this example, all records from the Sales table where the state is ‘SP’ will be removed. In this case, it’s important to use the WHERE clause to avoid deleting all the data from the table.
10. TRUNCATE TABLE
The TRUNCATE TABLE command is used to delete all records from a table, but keep its structure intact. It’s a quick way to clean up the table because it doesn’t perform row-by-row operations. For example:
TRUNCATE TABLE Sales;
Here, all records in the Sales table are deleted, but the table’s columns and definitions remain.
It’s ideal for situations where you need to reset the table without deleting it completely.
Obviously, it should be used in very specific cases, when there is absolute certainty that exclusion from the table is necessary.
11. ALTER TABLE
The ALTER TABLE command belongs to the DDL category and is used to modify the structure of an existing table, allowing you to add, modify, or delete columns. For example, if you want to add a “Date of Birth” column to the Customers table:
ALTER TABLE Clients ADD Birth_Date DATE;
With this command, the Customers table will include the new column. You can also use it to rename columns or change data types.
12. DROP TABLE
Classified as DDL, the DROP TABLE command is used to delete an entire table from the database, including all its records and structure.
Unlike TRUNCATE, which clears the data, DROP removes the table completely. For example:
DROP TABLE Sales;
After execution, the Sales table will be permanently deleted and cannot be recovered. Therefore, use this command with caution to avoid losing important data.
13. GROUP BY
The GROUP BY command is part of DQL, as it is used to organize records in an SQL query based on common values in one or more columns. It is typically used with aggregate functions such as COUNT, SUM, and AVG. For example, to group total sales by state:
SELECT State, COUNT(*) AS Total_Sales FROM Sales GROUP BY State;
In this case, the query returns the total number of sales for each state present in the Sales table. The GROUP BY clause is essential for analyses that require data aggregation.
14. HAVING
The HAVING command is used in conjunction with GROUP BY to filter groups of results based on a specific condition. It is also part of DQL.
It’s similar to the WHERE clause, but it’s applied after grouping. For example, to list only states with more than 50 sales:
SELECT State, COUNT(*) AS Total_Sales FROM Sales GROUP BY State HAVING COUNT(*) > 50;
Here, only states with more than 50 sales will be displayed. HAVING is ideal for applying filters after aggregation.
15. SELECT DISTINCT
The SELECT DISTINCT command, from the DQL category, is used to return unique records, eliminating duplicates from a column or set of columns in an SQL query.
For example, if you want to list all the unique states where sales occurred:
SELECT DISTINCT State FROM Sales;
This query returns a list of states without duplicates, showing each state only once. DISTINCT is useful for identifying unique values in large datasets.
16. JOIN
Finally, the JOIN command is a DQL used to combine records from two or more tables based on a common column. There are several types of JOIN, such as INNER JOIN, LEFT JOIN, and RIGHT JOIN.
For example, to obtain a sales list with customer details:
SELECT Sales.Course, Clients.Name
FROM Sales
INNER JOIN Clients ON Sales.Client_ID = Clients.ID;
In this example, the query returns the customer’s name and the course purchased, combining data from the Sales and Customers tables by the Customer_ID key. The JOIN is essential for analyses that depend on multiple tables.
How do I use SQL commands?
To use SQL commands correctly, it’s important to follow a basic workflow that begins with database creation and continues through data manipulation. Check out the main steps:
Create a database: Use the command CREATE DATABASE database_name to start your project.
Create a table: use the CREATE TABLE command to define the table structure.
Insert or query data: use INSERT INTO to add records and SELECT to perform queries.
Update or delete data: use UPDATE to modify records and DELETE to securely remove them.
Count on Hashtag Training to learn SQL!
Want to learn SQL practically and objectively? The SQL Impressive course from Hashtag Training is the right choice for you!
Regardless of your current level, we’ll teach you from the basics to advanced techniques, helping you master this essential language for data analysis.
Just like our other 365education courses, our SQL training covers everything you need to excel and become a leader in your company.
Conclusion
In this content, you learned 16 essential SQL commands to help you take your first steps in SQL.
You can test these commands to better solidify what you’ve learned and even use them in a test environment!
This training portion is very important because, in addition to better reinforcing what you’ve learned, you’ll be able to visualize the behavior of each command within the program and even spot any errors, should they occur, so you can then correct them.
Remember that typos are very common. So always double-check that your writing is correct! Did you like the tips? Check out other related content on our website! Also, visit our YouTube channel to watch complete videos and tutorials!
