SQL (Structured Query Language) is a powerful language for managing and manipulating relational SQL databases. These tried and tested data storage solutions continue to be an essential part of modern data infrastructure for business data storage, web development, data science, and more.
It’s no wonder then that SQL skills are still essential in 2024, with many looking to learn SQL to enhance their skills and resumes. And with database admins and data scientists earning average salaries of $80K+ and $120K+ respectively, understanding essential SQL commands can be a valuable investment.
In this article, we will cover the 14 most important SQL commands with examples you need to know in 2024, including the general syntax.
And to keep things organized, we’ve grouped the SQL command list into five core categories: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Transaction Control Language (TCL), and Data Query Language (DQL).
Download Hackr.io’s SQL Commands List PDF here.
What Are the Different Types of SQL Commands?
So, let’s start with a simple question, what are the commands in SQL? Well, SQL commands can be used to create, modify, and query databases, and these can be divided into five categories based on their function.
- Data Definition Language (DDL): These are used to define and manipulate the structure of database objects such as tables, indexes, and constraints.
- Data Manipulation Language (DML): These are used to manipulate the data stored in a database, including inserting, updating, and deleting data.
- Data Control Language (DCL): These are used to control access privileges to database objects, such as tables, views, procedures, and functions.
- Transaction Control Language (TCL): These are used to manage transactions, which are sets of database operations that are executed as a single unit.
- Data Query Language (DQL): These are used to retrieve data from a database, including selecting specific columns and the number of rows of data based on specified conditions.
And while there are certain differences between standard SQL and MySQL, for example, these general SQL command categories can always be applied.
What Are the Different Uses of SQL Commands?
Generally speaking, we use SQL commands to perform a well-defined range of activities, regardless of industry or role. Let’s take a closer look at these.
- Database Creation: Create new databases, along with tables, indexes, and other database objects.
- Data Insertion: Insert data into database tables, whether a single row or multiple rows of data.
- Data Modification: Update existing data in database tables, whether a single row or multiple rows of data.
- Data Deletion: Delete data from database tables, whether a single row or multiple rows of data.
- Data Retrieval: Retrieve data from database tables based on specific criteria or conditions.
- Database Management: Manage databases, such as creating backups, restoring backups, and changing database settings.
Learn from the Top SQL Courses at Udemy
DDL (Data Definition Language) Commands
1. CREATE
The CREATE command is used to create database objects, whether that’s tables, indexes, views, or databases. The CREATE command syntax varies depending on the type of object being created, but the general structure is similar.
In the syntax below, the CREATE DATABASE statement creates a new database called database_name. The database name must be unique and not the same as an existing database.
Command Syntax:
CREATE DATABASE database_name;In the syntax below, the CREATE statement creates a new table with the specified name. The TEMPORARY keyword is optional and can be used to create a temporary table that is deleted when the session ends.
The table columns defined above are within parentheses, each with a column name, data type, and optional parameters in square brackets, such as whether the column can be null, whether it should be unique, or whether it needs a default value.
Command Syntax:
CREATE [TEMPORARY] TABLE table_name
(
column1 datatype_1 [optional_parameters],
column2 datatype_2 [optional_parameters],
...
column_n datatype_n [optional_parameters]
);In the example below, we’ve created a new table called customers with four columns. The id column is an integer primary key, the name column is a non-null string, the email column is a unique string, and the created_at column is a datetime column with a default value of the current timestamp.
Example:
CREATE TABLE customers
(
id INT PRIMARY KEY,
name VARCHAR 255 NOT NULL,
email VARCHAR 255 UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);2. ALTER
The ALTER command is used to modify an existing table’s structure, and it can be used to add, drop, or modify columns and also to rename a table.
In the syntax below, the ALTER TABLE statement modifies table_name within an existing database. You can then choose to include the ADD keyword with a new column name and data type, the DROP COLUMN keyword with a column name, the MODIFY keyword with a column name and data type, or the RENAME keyword with the new table name.
Command Syntax:
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY column_name datatype;
ALTER TABLE table_name RENAME TO new_table_name;The example below adds a new column named salary with data type int to the employees table in the database.
Example:
ALTER TABLE employees ADD salary INT;3. DROP
The DROP command removes a database, schema, table, or view from a database. The IF EXISTS option avoids errors if the object to be dropped does not exist.
In the syntax below, there are two options. Firstly, the DROP command is followed by a database or schema with an optional IF EXISTS clause for the database name to avoid errors if the object does not exist.
The second option uses the DROP command followed by a table or view from a database with an optional IF EXISTS clause for the table name to avoid errors if the object does not exist.
Command Syntax:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
DROP {TABLE | VIEW} [IF EXISTS] tbl_name;The example below drops the employees table from the database, but only if it exists. The command will not result in an error if the table does not exist.
Example:
DROP TABLE IF EXISTS employees;4. RENAME
You can use the RENAME command to edit the name of an existing table, view, or database. This is useful when you want to update a database or object name to better reflect its contents or when you want to adhere to naming conventions.
In the syntax below, the RENAME statement changes the name of a database, table or view. The object you want to rename is specified after the ALTER keyword, along with the object's current name. This is then followed by RENAME TO and the new name for the object.
Command Syntax:
ALTER {DATABASE | TABLE | VIEW} old_name RENAMETO new_name;The example below renames the employees table to staff. The new name will be used to refer to the table in subsequent queries and operations.
Example:
ALTER TABLE employees RENAMETO staff;5. TRUNCATE
The TRUNCATE command is used to remove all rows from a table, and it also resets any auto-incrementing identity values to their starting point.
In the syntax below, the TRUNCATE TABLE statement is used to remove all rows from a table quickly and efficiently. The table_name parameter specifies the table name from which you want to remove all data.
Command Syntax:
TRUNCATE TABLE table_name;In the example below, the TRUNCATE command removes all rows from the orders table, resetting any auto-incrementing identity values to their starting point.
Note that TRUNCATE does not drop the table itself, nor does it affect any table structures, such as columns, indexes, or constraints. It simply removes all the data from the table.
Example:
TRUNCATE TABLE orders;DML (Data Manipulation Language) Commands
6. INSERT
The INSERT command is used to add new rows to a table in a database. The columns you want to insert data into are specified in the column list, while the values you want to insert are specified in the VALUES clause.
The syntax below uses INSERT INTO to add new rows to table_name by specifying a list of column names where you’d like to add new data. The VALUES parameter specifies the data you want to add to the table in each respective column. Note that the list of values matches the columns list on a position-by-position basis.
Command Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);The example below inserts a new row into the customers table by specifying the first_name, last_name, and email columns. The VALUES clause is then followed by a list of values to include, namely 'John', 'Doe', and 'johndoe@email.com'. You can see that the position of these values are matched to the column names in the columns list.
Example:
INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', 'johndoe@email.com');7. UPDATE
The UPDATE command is used to modify existing data in a table. It updates the values of one or more columns in one or more table rows that satisfy the specified condition.
In the syntax below, the UPDATE statement is used to modify table_name. This is followed by the SET keyword, which specifies the columns to modify and their new values. Modifying multiple columns simultaneously is possible by separating the column/value pairs with commas.
The WHERE clause specifies the condition to meet for the update to take place, ensuring that the update is only implemented on rows that meet the condition.
Command Syntax:
UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;The example below updates the employees table. The SET clause increases the salary of all employees by 10%, and the WHERE clause ensures this change is only reflected for employees that belong to the Sales department.
Example:
UPDATE employees SET salary = salary * 1.1
WHERE department = 'Sales';8. DELETE
The DELETE command removes one or more rows from a table that satisfy the specified condition.
In the syntax below, the DELETE FROM statement is used to remove one or more rows from table_name, and the WHERE parameter specifies the condition that must be met for the deletion to occur. The only rows that will be deleted are those that meet the condition.
Command Syntax:
DELETE FROM table_name WHERE condition;The example below removes all rows from the employees table with a WHERE clause that checks if the employee department is equal to IT.
Important: If the WHERE clause is omitted, all rows in a table will be deleted. It's important to use caution when using DELETE, as it can have unintended consequences if used improperly.
Example:
DELETE FROM employees WHERE department = 'IT';DCL (Data Control Language) Commands
9. GRANT
The GRANT command grants specific permissions to a user or role on a particular database object, such as a table, view, or stored procedure.
In the syntax below, the GRANT statement is used with the permission_type parameter to specify whether to grant permissions for SELECT, INSERT, UPDATE, DELETE, or ALL.
The object_name specifies the database name to which you want to grant permission, and the user_name or role parameters specify who to grant the permissions to.
Command Syntax:
GRANT permission_type ON object_name TO {user_name | role};In the example below, the GRANT command is used to grant the SELECT and INSERT permissons on the employees table to the user Jane. This means the user can view and insert data into the employees table, but not modify or delete existing data.
Example:
GRANT SELECT, INSERT ON employees TO Jane;10. REVOKE
The REVOKE command removes permissions from a user or role in a database. It is often used in conjunction with the GRANT command, which grants permissions to users or roles.
In the syntax below, the REVOKE statement revokes previously granted permissions from a user, role, or public. The permission_type parameter specifies the name of the permission you want to revoke, such as SELECT, INSERT, UPDATE, DELETE, or ALL.
The object_name parameter specifies the database object name from which you want to revoke the permission. The user_name, PUBLIC, or role_name parameter specifies where to revoke the permission.
Command Syntax:
REVOKE permission_type ON object_name
FROM {user_name | PUBLIC | role_name};In the example below, the REVOKE command is used to remove the SELECT permission on the employees table from a user with the name JDoe user, meaning they will no longer be able to select data from that table.
Note that the permission being revoked must match a privilege that was granted with a GRANT command.
Example:
REVOKE SELECT ON employees FROM JDoe;TCL (Transaction Control Language) Commands
11. COMMIT
The COMMIT command permanently saves changes to a database that were made in a transaction.
In the syntax below, the COMMIT statement saves the changes made in the current transaction to the database. Before making any database changes, you must start a new transaction with BEGIN TRANSACTION.
The SQL statements that modify the data in the database are executed between the BEGIN TRANSACTION and COMMIT statements. Once the changes have been made successfully, and you are ready to commit them to the database, use the COMMIT statement.
Command Syntax:
BEGIN TRANSACTION;
-- SQL statements here
COMMIT;In the example below, we have enclosed a series of SQL statements between BEGIN TRANSACTION and COMMIT. These statements are treated as a single transaction, and once they have been executed, the COMMIT command permanently saves the changes to the database.
Important: If an error occurs during the execution of the SQL statements, the ROLLBACK command (up next) can be used to undo any changes made during the transaction.
Example:
BEGIN TRANSACTION;
UPDATE employees SET salary = 55000 WHERE id = 123;
UPDATE employees SET salary = 60000 WHERE id = 456;
COMMIT;12. ROLLBACK
The ROLLBACK command can be used to undo changes made within a transaction, thus restoring the database to its previous state.
In the syntax below, ROLLBACK is used to undo the changes made in the current transaction, reverting the database to its previous state. This ROLLBACK command is included after a series of statements have been executed as a transaction.
Command Syntax:
BEGIN TRANSACTION;
-- SQL statements here
ROLLBACK;In the example below, a transaction is started with BEGIN TRANSACTION. Two SQL statements are executed to update the salaries of two employees.
If an error occurs during execution of the statements, the ROLLBACK command can be used to undo any changes made during the transaction and restore the database to its previous state.
Important: If the SQL statements execute without error, the COMMIT command should be used to permanently save the changes made within the transaction.
Example:
BEGIN TRANSACTION;
UPDATE employees SET salary = 55000 WHERE id = 123;
UPDATE employees SET salary = 60000 WHERE id = 456;
ROLLBACK;13. SAVEPOINT
The SAVEPOINT command creates a named point within a transaction that allows for a partial rollback in case of an error.
In the syntax below, the SAVEPOINT statement is used to create a named point in a transaction which you can use later to roll back.
This allows you to break up a large transaction into smaller parts, and if an error occurs in one part, you only need to rollback that part without affecting the rest of the transaction.
Command Syntax:
BEGIN TRANSACTION;
-- SQL statements here
SAVEPOINT savepoint_name;
-- More SQL statements here
ROLLBACK TO SAVEPOINT savepoint_name;
-- More SQL statements here
COMMIT;In the example below, Three SQL statements are executed in a transaction to update the salaries of three employees. A savepoint named first_savepoint is created using the SAVEPOINT command. Another SQL statement is executed to update another employee's salary, and then another savepoint named second_savepoint is created.
If an error occurs after the creation of the second_savepoint, the ROLLBACK TO SAVEPOINT command can be used to roll back the transaction to the first_savepoint, undoing the changes made to the salary of the employee with IDs 456 and 789.
Example:
BEGIN TRANSACTION;
UPDATE employees SET salary = 55000 WHERE id = 123;
SAVEPOINT first_savepoint;
UPDATE employees SET salary = 60000 WHERE id = 456;
SAVEPOINT second_savepoint;
UPDATE employees SET salary = 65000 WHERE id = 789;
ROLLBACK TO SAVEPOINT first_savepoint;
COMMIT;DQL (Data Query Language) Commands
14. SELECT
The SELECT command retrieves data from one or more tables in a database. It allows you to specify which columns you want to retrieve data from and optionally filter the results using a WHERE clause.
In the syntax below, the SELECT statement retrieves column1, column2, … from table_name.
Command Syntax:
SELECT column1, column2, … FROM table_name WHERE condition;In the example below, the * symbol is used to indicate that we want to retrieve data from every column in the employees table, and the WHERE clause filters the results to only include records where the salary column is greater than 50000.
Example:
SELECT * FROM employees WHERE salary > 50000;Bonus Section: 5 Essential SQL Keywords
Another important aspect of SQL is using SQL keywords. So let’s quickly distinguish between commands in SQL and SQL keywords.
SQL Commands relate to the actions that you can perform, such as SELECT, INSERT, DELETE, etc. Keywords are reserved words with special meanings, and they cannot be used as table or column names. Let’s take a look at five essential SQL keywords.
DISTINCT
The DISTINCT keyword selects unique values from a column in a table. The example below returns all city values from the customers table, but the DISTINCT keywords ensures there are no duplicate values.
Example:
SELECT DISTINCT city FROM customers;ORDER BY
The ORDER BY keyword sorts query results in descending or ascending order based on one or more columns. The example below returns all values from the orders table, sorted by order_date in descending order.
Example:
SELECT * FROM orders ORDER BY order_date DESC;GROUP BY
The GROUP BY keyword is used to group rows with the same values in one or more columns. The example below returns the number of customers in each region via the COUNT keyword (another bonus keyword for you there!).
Example:
SELECT region, COUNT(*) FROM customers GROUP BY region;JOIN
The JOIN keyword combines the rows from two or more tables based on a related column that they share. The example below returns a list of customer_name and order_date values for all orders in the orders table that have a matching customer_id in the customers table.
Example:
SELECT customers.customer_name, orders.order_date
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;WHERE
The WHERE keyword filters results from a query based on one or more conditions. The example below returns everything from the products table when the unit_price exceeds 10.
Example:
SELECT * FROM products WHERE unit_price > 10;Find the Latest Remote SQL Jobs
Conclusion
SQL commands are essential for managing and manipulating relational databases, whether for database admin, web development, data science, or any other data-driven role. This makes it essential to understand the different types of SQL commands for anyone working with relational databases.
This article provides a comprehensive guide to understanding the 14 most essential SQL commands grouped by category. We’ve also included the basics of each SQL command, general syntax, and code examples to help you see how to use them. Not to mention the bonus section on five of the most common and important SQL keywords.
So whether you're an SQL beginner or an experienced developer, our SQL tutorial and guide to essential commands will have something to help you get the most out of SQL!
Looking to level up your SQL skills in 2024? Check out
Frequently Asked Questions
1. What Are Some of the Basic SQL Commands?
This depends on your skill level and how much SQL you already know. However, some basic beginner commands include SELECT, FROM, INSERT, UPDATE, and DELETE.
2. What Is the SQL Syntax Code?
The SQL syntax code refers to the specific rules and guidelines that govern the structure of SQL statements, including using specific keywords and syntax structure.
The general syntax for an SQL statement is:
[COMMAND] [TABLE] [CONDITIONS];3. How Do I Write an SQL Query?
There are many types of basic SQL queries, each with its own specific rules to ensure it is handled correctly. Take a look at the example commands we have included in our guide above to learn how to correctly write simple SQL Queries.
4. What Is DDL in SQL?
DDL, or Data Definition Language, is a subset of SQL commands used to define and manipulate the structure of database objects such as tables, indexes, and constraints. Common DDL commands include CREATE, ALTER, and DROP.
People are also reading:
