MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER

Glossary of the SQL Commands You Need to Know

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:

Copy
        
1 SELECT name AS first_name 2 FROM users;

AS can usually be omitted. Thus, the query below is equivalent to the query above:

Copy
        
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:

Copy
        
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.

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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.

Copy
        
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:

Copy
        
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:

Copy
        
1 DROP DATABASE company;

DROP TABLE

DROP TABLE deletes a table along with its data and related objects (such as SQL triggers, indexes, etc.).

Copy
        
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:

Copy
        
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

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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.

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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’”:

Copy
        
1 SELECT * 2 FROM users 3 WHERE full_name LIKE "A%";

Some of the supported patterns for LIKE are:

  • %x: Selects all values that begin with x.
  • %x%: Selects all values that include x.
  • x%: Selects all values that end with x.
  • x%y: Selects all values that begin with x and end with y.
  • _x%: Selects all values have x as the second character.

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 :

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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:

Copy
        
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.

Copy
        
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:

  • SELECT: Retrieves data from a table based on specified criteria.
  • INSERT: Adds new rows into a table.
  • UPDATE: Modifies existing records in a table.
  • DELETE: Removes rows from a table based on specified conditions.
  • CREATE DATABASE: Creates new a database.

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 <a href="https://www.postgresql.org/docs/current/app-psql.html" target="_blank">psql</a> is an SQL command-line tool.

What are some good SQL commands interview questions?

  • What is the difference between INNER JOIN and OUTER JOIN?
  • Explain the purpose of the WHERE clause in SQL.
  • How do you use the GROUP BY clause in SQL?
  • What is the purpose of the HAVING clause in SQL?
  • Can you explain the difference between UNION and UNION ALL?
  • How do you handle NULL values in SQL?
  • Explain the difference between DROP and TRUNCATE commands.
Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL INTERSECT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 2 min 2024-05-23
title

SQL Formatter: Definition and Best Tools

author Antonello Zanini tags SQL 9 min 2024-05-20
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

PostgreSQL Full Text Search: The Definitive Guide

author Antonello Zanini tags POSTGRESQL Search 11 min 2024-05-13
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09
title

SQL UNION Operator: How To Combine Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER UNION 9 min 2024-05-06
title

SQL CONVERT: The Handbook of Data Conversion in SQL

author Antonello Zanini tags CONVERT Data Conversion MySQL SQL SERVER 7 min 2024-05-02
title

SQL CASE Statement: Definitive Guide

author Antonello Zanini tags CASE ORACLE POSTGRESQL SQL SQL SERVER 5 min 2024-04-29
title

SQL REPLACE Function: A Comprehensive Guide

author TheTable tags SQL 5 min 2024-04-25
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗