intro
Let's look at a list of all the essential SQL commands that anyone interacting with databases should know and master.
SQL is a complex language that supports dozens of commands. Some are more important than others, and here you will see only the most important SQL commands you need to know. At the end of this article, you will be able to understand most SQL statements.
Follow this guide and discover what SQL has to offer!
What Is an SQL Command?
An SQL command is a statement used to interact with a DBMS (Database Management System). Each command has a different goal, such as retrieving, updating, deleting, or inserting data and objects into a database. Some SQL commands are very specific and allow you to perform operations such as giving a user particular rights.
Commands in SQL are standardized and usually work the same way in different database systems. In other words, they represent a common language for dealing with databases. At the same time, SQL dialects may introduce some variations in syntax and meaning.
SQL command examples include SELECT for retrieving data, INSERT for adding new records, UPDATE for modifying existing records, and DELETE for removing records. Some commands can be used only once in a query, while others can be applied multiple times in the same SQL statement. In addition, most commands can be combined to create more sophisticated queries and transactions.
SQL Commands: List With Examples
Time to see an alphabetically ordered list of some of the most basic SQL commands. Each will be accompanied by an example to illustrate how to use it. Note that some dialects of SQL may have other commands or a different syntax.
For a complete list of all commands supported by SQL, check out our SQL Cheat Sheet guide.
AS
AS renames a column or temporary table with the specified alias.
For example, the query below renames the column name to first_name:
1
SELECT name AS first_name
2
FROM users;
AS can usually be omitted. Thus, the query below is equivalent to the query above:
1
SELECT name first_name
2
FROM users;
Omitting AS can be useful in specific situations but may also make your queries less readable.
ALTER TABLE
ALTER TABLE is part of the DDL SQL command list and allows you to modify the structure of existing database objects. For example, it gives you the ability to add or remove columns from a table.
The snippet below adds a new column called last_name of type VARCHAR(255) to the users table:
1
ALTER TABLE users
2
ADD last_name VARCHAR(255);
AND
AND combines two or more logical conditions in an SQL expression. Note that a single SQL expression can contain several AND SQL commands.
When used in WHERE, all conditions must be met for the result to be selected. The query below returns all users whose name contains “Maria” and who are more than 35 years of age.
1
SELECT *
2
FROM users
3
WHERE name LIKE '%Maria%' AND age > 35;
BETWEEN
BETWEEN helps you filter results within a specified range. This is especially useful when dealing with numeric data or dates.
In the example below, you are selecting all users whose age is between 30 and 40:
1
SELECT *
2
FROM users
3
WHERE age BETWEEN 30 AND 40;
CREATE DATABASE
CREATE DATABASE adds a new database, assuming the user has the required rights to do it. Learn more in our MySQL CREATE DATABASE guide. You can also create a table like another table.
This query will create the company database:
1
CREATE DATABASE company;
CREATE INDEX
CREATE INDEX generates an index for a table. The right database index, helps you speed up a query up to 10x or more.
This will create an index called idx_last_name for the last_name column of the users table:
1
CREATE INDEX idx_last_name
2
ON users(last_name);
It should be noted that various database management systems support various kinds of indexes too—normally we are talking about B-Tree indexes, but there also are spatial indexes allowing us to work with geographic data, unique indexes that remove duplicate rows from a table, etc.
CREATE TABLE
CREATE TABLE is part of the DDL SQL commands and its goal is to add a new table inside of a database.
Use it to create a games table with three columns as follows and amend as necessary:
1
CREATE TABLE games(
2
id INT,
3
name VARCHAR(255),
4
players INT
5
);
CREATE VIEW
CREATE VIEW creates a virtual table based on the result set of an SQL statement. The resulting table is queryable but is usually generated on the fly and not saved to disk.
This SQL query will create a view storing all users whose age is less than 40.
1
CREATE VIEW young_users AS
2
SELECT *
3
FROM users
4
WHERE age < 40;
DELETE
In SQL commands, DELETE removes rows from a table based on specified conditions.
This will delete all users whose email is NULL:
1
DELETE FROM users
2
WHERE email IS NULL;
To remove all rows in a table at once, DELETE is not recommended — use TRUNCATE instead.
DROP DATABASE
DROP DATABASE removes the entire database, including all its tables, indexes, objects, and data.
See it in action in the example below:
1
DROP DATABASE company;
DROP TABLE
DROP TABLE deletes a table along with its data and related objects (such as SQL triggers, indexes, etc.).
1
DROP TABLE users;
EXCEPT
EXCEPT returns the distinct rows that appear in the first result set but not in the second result set of two SELECT statements:
1
SELECT full_name
2
FROM users
3
EXCEPT
4
SELECT full_name
5
FROM banned_users;
EXISTS
EXISTS tests for the existence of any record in a subquery.
This query will select only users who have bought at least one course
1
SELECT*
2
FROM users U
3
WHERE EXISTS (
4
SELECT 1
5
FROM orders O
6
WHERE O.type = "course" AND O.user_id = U.id
7
);
FETCH
FETCH specifies the number of rows to return after the OFFSET clause has been processed. This SQL command is particularly useful when implementing pagination behavior.
This query retrieves the next 5 rows of the users table after skipping the first 10 rows, ordered by the age column:
1
SELECT *
2
FROM users
3
ORDER BY age
4
OFFSET 10 ROWS
5
FETCH NEXT 5 ROWS ONLY;
FROM
FROM specifies the table from which to extract data. This is one of the most widely used and basic SQL commands.
This is how you can use it:
1
SELECT *
2
FROM users;
GRANT
GRANT gives a user particular rights related to specific database objects like tables, indexes, and more.
This will enable the user usr_cms to retrieve and add data to the orders table:
1
GRANT SELECT, INSERT ON orders TO usr_cms;
GROUP BY
GROUP BY groups rows with the same values into aggregate rows. This SQL command is generally used with SQL aggregate functions.
This will return the count of users by age:
1
SELECT age, COUNT(*)
2
FROM users
3
GROUP BY age;
HAVING
HAVING filters groups specified by the GROUP BY clause. It is similar to WHERE, but it is applied to GROUP BY and can filter rows based on aggregate functions.
This will return the count of users by age, whose age group contains at least 100 elements:
1
SELECT age, COUNT(*)
2
FROM users
3
GROUP BY age
4
HAVING COUNT(*) > 100;
IN
IN specifies multiple values a column can have in a WHERE clause.
This will return all orders that have the course, bootcamp, or walkthrough type.
1
SELECT *
2
FROM orders
3
WHERE type IN ("course", "bootcamp", "walkthrough");
INSERT INTO
INSERT INTO adds new records to the specified table. Again, this is one of the DDL SQL commands.
This will add a new user called John Doe to the users table:
1
INSERT INTO users (full_name, age)
2
VALUES ("John Doe", 42);
INTERSECT
INTERSECT returns the common records that appear in the result sets of two or more SELECT statements.
For example, use it to retrieve the common names between users and customers:
1
SELECT full_name
2
FROM users
3
INTERSECT
4
SELECT name
5
FROM customers;
IS NULL
IS NULL returns true when the column is tested with contains a NULL value. Note that = NULL and IS NULL produce to different results in most databases.
This will select all users whose age information is NULL:
1
SELECT *
2
FROM users
3
WHERE age IS NULL;
JOIN (INNER, LEFT, RIGHT, FULL)
JOIN combines rows from two or more tables based on a foreign key between them. Keep in mind that there are different types of JOINs:
This will return each user associated with their orders:
1
SELECT U.full_name, O.id, O.date, O.amount
2
FROM users U
3
INNER JOIN orders O
4
ON U.id = O.user_id;
LIKE
LIKE searches for a specified pattern in a column. That is useful for applying specific search conditions such as “give me all the users whose full name start with ‘A’”:
1
SELECT *
2
FROM users
3
WHERE full_name LIKE "A%";
Some of the supported patterns for LIKE are:
LIMIT
LIMIT constrains the number of rows returned in a result set. This SQL command comes in handy when dealing with large tables.
In the example below, the selects the full names of the first 10 users :
1
SELECT full_name
2
FROM users
3
LIMIT 10;
It is also notable that some database management systems automatically apply the LIMIT clause to statements after running certain statements like SELECT , etc. For example, in MySQL Server the default amount of rows that will be returned after running a SELECT statement is 25.
NOT
NOT negates a logical condition, as in the following example:
1
SELECT *
2
FROM users
3
WHERE age IS NOT NULL;
This will return all users whose age field is not NULL.
ORDER BY
ORDER BY defines the way the returned results should be sorted. When used with LIMIT, OFFSET, or similar SQL commands, it plays a key role in selecting the desired records only.
This will return all users sorted from oldest to youngest:
1
SELECT name
2
FROM users
3
ORDER BY age DESC;
OR
OR combines two or more logical conditions in an SQL statement. It works just like AND, but it applies a different logical operation.
You can use it to get all users whose age is 30 or 40 as follows:
1
SELECT *
2
FROM users
3
WHERE age = 30 OR age = 40;
SELECT
SELECT is used to retrieve data from a database. It enables you to specify the columns to select from the table specified in FROM:
Use it to get the full_name and age of all users:
1
SELECT full_name, age
2
FROM users;
The special SELECT * SQL command returns all columns contained in a table according to a specified clause:
1
SELECT *
2
FROM users;
SELECT DISTINCT
SELECT DISTINCT returns unique values in the specified columns of a given table.
Use it to get all the unique names of the users in the database:
1
SELECT DISTINCT full_name
2
FROM users;
TRUNCATE
TRUNCATE removes all rows from a table. You can use it to empty a table as below:
1
TRUNCATE users;
When needing to remove all rows from a specific table, TRUNCATE will be faster than DELETE.
UNION
UNION combines the result sets of two or more equivalent SELECT statements. By default, it removes duplicates from the resulting dataset.
Merge the names of the customers with the names of the users in the database:
1
SELECT full_name FROM customers
2
UNION
3
SELECT name FROM customers;
UNION ALL
UNION ALL combines the results of two or more equivalent SELECT statements, including duplicates.
Merge the names of the customers with the names of the users in the database, keeping duplicates:
1
SELECT full_name FROM customers
2
UNION
3
SELECT name FROM customers;
UPDATE
UPDATE is an SQL command to modify existing database objects in the database. For example, it can be used to update a record in a table.
This SQL query sets the age of the user with the id 8 to 41:
1
UPDATE users
2
SET age = 41
3
WHERE id = 8;
WHERE
WHERE filters rows based on specified conditions. Most queries that need to retrieve specific data involve a WHERE clause. For instance, you can use it to get all users whose age is greater than or equal to 21:
1
SELECT *
2
FROM users
3
WHERE age >= 21;
WITH
WITH provides a way to write subqueries for use in a larger query. Thanks to it, you can create CTEs (Common Table Expressions). Find out more in our PostgreSQL CTE guide.
1
WITH old_users AS (
2
SELECT full_name
3
FROM users
4
WHERE age > 70
5
)
6
SELECT * FROM old_users;
Conclusion
In this guide, you learned what an SQL command is and saw a list of the basic SQL commands you need to know. After reading this article, you can consider yourself a SQL enthusiast ready to become a master!
Given the number of keywords and commands SQL supports, a full-featured database client with autocomplete capabilities makes everything easier. The best tool based on user satisfaction? DbVisualizer! This powerful database client supports many DBMS technologies, has special commands for debugging and writing queries, and provides query optimization functionality. Try DbVisualizer for free!
FAQ
Where is the meaning of SQL statements defined?
The meaning of SQL statements is defined in the SQL standard, which is maintained by ISO/IEC and ANSI. SQL specifications detail the syntax, semantics, and behavior of SQL commands across different database management systems. That ensures compatibility and consistency in querying and managing databases.
What are the top 5 basic SQL commands?
The top 5 basic SQL commands are:
Do all DBMS technologies support the same SQL commands?
No, not all database management system technologies support the same SQL commands. While there are common SQL commands, each DBMS may have its own set of additional commands, variations, or proprietary extensions to SQL. Compatibility varies depending on the DBMS vendor and its adherence to SQL standards. For example, the main difference in the PostgreSQL vs MySQL comparison is that PostgreSQL adheres closely to standards, while MySQL does not.
What is the difference between an SQL command and an SQL command line?
An SQL command refers to a specific instruction for interacting with a database. Instead, an SQL command line typically refers to a command-line interface used to execute SQL commands directly in a database management system. SELECT is an example of SQL command while psql is an SQL command-line tool.
What are some good SQL commands interview questions?
