The ultimate list of the most important SQL statements

SQL is an English-like query language. It has a limited number of commands and those commands are very readable. So SQL is quite easy to pick up for the beginners. In this post, we are going to introduce some of the most important SQL statements used in practice.

1. SELECT

SELECT is by far the most important statement, as well as the most frequently used statement in SQL. It’s used to retrieve data from a datable. You can show everything from a table, a column or just some records based on the conditions.

Example:

This statement lists all records from the table students:

SELECT * FROM students;

This statement lists all records from the table students that match the condition of score value greater than 5:

SELECT * FROM students WHERE score > 3;

2. UPDATE

The UPDATE statement is used to modify data within a table and update it with a new value.

Example:

This query updates the value of the salary for the record with id 234. It’s when the employee no 234 got a raise for $3000 and the admin needs to update his salary in the salary database of the company.

UPDATE emp_salary
SET salary = salary + $3000
WHERE employee_id = 234;

3. DELETE

DELETE statement is used to remove data from a database.

Example:

A fashion shop stopped selling the skirt no. 653 and want to delete it from the product list. This DELETE statement will help:

DELETE FROM products
WHERE product_id = 653;

4. INSERT

In the opposite of DELETE, the INSERT statement is used to insert new data into a database.

Example:

Back to the fashion shop above, the store manager imported a new skirt model and want to add its info to the product list, assumed it has three attributes: product id, product name, and price, she runs this INSERT statement:

INSERT INTO products
values(665, 'Skirt for this summer', 4500);

5. CREATE

CREAT statement is another very important statement in SQL. It’s used to create a lot of things, from database, table, to index, or procedure, etc.

Example:

To create a new database named class:

CREATE DATABASE class

In that newly created database class, now we want to create a new table called score:

CREATE TABLE score (student_id int not null, full_name char(20) not null, score int not null);

6. ALTER

This ALTER statement is used to modify an existing database or table and update it with a new data structure.

Example:

For the newly created table score above, the database admin now wants to add one more column to take notes from the teacher for each student. She needs to run this ALTER statement:

ALTER TABLE score ADD teacher_notes varchar(100) null;

7. DROP

DROP statement is used to remove the entire database object from the system. It can be a database, a table.

Example:

When the table score is not used anymore and the DB admin wants to remove it from the system:

DROP TABLE score;

Please note that a DELETE statement can only remove the data records, while DROP removes the entire database structure from the database. That’s why DROP belongs to the group of Data Definition Language statements while DELETE belongs to the group of Data Manipulation Language statements.

8. JOINs

In fact, JOIN is not a very basic statement but it’s among the most frequently used SQL statements. A JOIN statement allows you to combine data in multiple tables. When you deal with a large set of data, this statement is going to be very handy.

Example:

You have table paid_customers containing info of the customers who already made the payment with two columns: customer_id and full_name; and table orders with data related to all orders including customer_id, order_id, order_time, total_value.

You want to list all orders that have been paid by the customers with the total amount greater than 300:

SELECT * FROM orders
    JOIN paid_customers 
    ON orders.customer_id = paid_customers.customer_id
WHERE total_value > 300;

Need a good GUI Tool for managing relational databases and practising SQL? Try TablePlus. It’s is a modern, native GUI that allows you to simultaneously manage multiple databases such as MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server… faster and easier. 