SQL Commands List for the Most Common Actions

This is a SQL commands list that covers all the necessary actions with SQL databases. Each SQL command is provided with its definition, a code snippet that represents the correct syntax, and some have live code examples that you can try modifying to see the command in action.

SQL Commands List

Note: some of the examples have no output because they deal with deleting or creating whole tables.

AND|OR

AND combines two or more conditions for a single query. All of the conditions used with this operator must be met in order to show the results.

SELECT * FROM Developers
WHERE Country='India' AND City='Delhi';

OR is used similarly, but it will output result with rows that comply with either of the conditions.

SELECT * FROM Developers
WHERE City='London' OR City='Paris';

ALTER TABLE

ALTER TABLE allows you to add or remove columns from a table.

ALTER TABLE Developers
ADD BirthDate date;

ALTER TABLE Developers
DROP COLUMN BirthDate;

AS (alias)

AS allows you to rename a column or table to a more convenient alias (a correlation name) without changing the original names in the database. This makes writing queries easier when the original table or column names are long or complicated.

SELECT ID as CustomerID, Name AS Customers
FROM Customers;

SELECT o.ID, c.Name
FROM Customers AS c, Customer_orders AS o
WHERE c.id = 2 AND c.ID = o.customer_id;

BETWEEN

BETWEEN operator filters the results and returns only the ones that fit the specified range. You can describe the value of this operator using dates, numbers, or text.

SELECT * FROM Orders
WHERE Price BETWEEN 10 AND 15;

CREATE DATABASE

When you need to create a new database, use the CREATE DATABASE statement. You must have admin rights to do that.

CREATE DATABASE testingDB;

CREATE TABLE

CREATE TABLE statement creates a new table in a database.

CREATE TABLE Suppliers (
    SupplierID int,
    FirstName varchar(255),
    LastName varchar(255),
    City varchar(255),
    Country varchar(255) 
);

CREATE INDEX

CREATE INDEX generates an index for a table. This enables retrieving data from a database faster. Users don't see indexes as they are only used to increase search speed.

CREATE INDEX idx_lastname
ON Persons (LastName);

CREATE VIEW

CREATE VIEW creates a narrower version of an existing table by getting a set of results based on a certain query. A view is not much different from a real table: it contains columns and rows with data, but it doesn't contain the fields of the real table that are irrelevant for your particular purpose.

CREATE VIEW [Present List Products] AS
SELECT ID, Name
FROM Products
WHERE Discontinued = No;

DELETE

If you need to remove certain rows from the table, use the DELETE FROM statement.

DELETE FROM Developers
WHERE Name='Antonio Indigo';

DELETE * FROM Developers;

GRANT

GRANT command is for giving users the access to a database.

GRANT SELECT, UPDATE ON YOUR_TABLE TO FIRST_USER, SECOND_USER;

REVOKE

REVOKE command is for taking away users' permisions.

REVOKE SELECT, UPDATE ON YOUR_TABLE FROM FIRST_USER, SECOND_USER;

COMMIT

COMMIT command is for saving every transaction to the database.

DELETE FROM CUSTOMERS  
WHERE AGE = 18;  
COMMIT;

ROLLBACK

ROLLBACK command is for undoing transactions which are not saved to the database.

DELETE FROM CUSTOMERS  
WHERE AGE = 18;  
ROLLBACK;

SAVEPOINT

SAVEPOINT command is for returning a transaction to a specific point without affecting the whole transaction.

SAVEPOINT SAVEPOINT_NAME;

DROP DATABASE

DROP DATABASE is one of the riskiest statements that should be used with extra caution. In SQL, drop means delete – and DROP DATABASE deletes the whole specified database together with all its parameters and data.

DROP DATABASE db_name

DROP INDEX

DROP INDEX will delete the index you specify. The syntax of this statement varies based on the DB system used.

SQL Server

DROP INDEX tbl_name.indx_name

MS Access

DROP INDEX indx_name ON tbl_name

DB2/Oracle

DROP INDEX indx_name

MySQL

ALTER TABLE tbl_name DROP INDEX indx_name

DROP TABLE

DROP TABLE statement deletes the whole table with its column parameters and datatype settings. If you want to remove only the contents of the rows but keep the table itself, use another statement – TRUNCATE TABLE.

DROP TABLE tbl_name

EXISTS

EXISTS operator allows you to check whether a record exists by writing a subquery. If the record is found, the result is displayed based on the statement you use this operator with. You can use it with SELECT, UPDATE, INSERT, and DELETE.

SELECT id, name
FROM customers
WHERE EXISTS (SELECT id FROM customer_orders WHERE customer_orders.customer_id = customers.id AND customers.city = "Rome");

GROUP BY

Combine GROUP BY with SELECT statement in order to arrange identical data (rows with the same value) into groups (summarizing rows).

SELECT COUNT(ID), City
FROM Developers
GROUP BY City;

HAVING

HAVING specifies that you need to filter the results to only the rows that fulfill the described condition.

It performs the same action as the WHERE clause. The difference is that HAVING is used only for aggregate functions as WHERE doesn't work with them.

SELECT COUNT(ID), Country
FROM Pets
GROUP BY Country
HAVING COUNT(ID) > 2;

IN

The IN operator includes multiple values into the WHERE clause.

SELECT * FROM Developers
WHERE Country IN ('USA', 'France', 'India');

INSERT INTO

INSERT INTO statement inserts new rows of data into a table.

INSERT INTO Developers (Name, City, Country)
VALUES ('Luke Christon', 'London', 'UK');

INNER JOIN

INNER JOIN combines rows from different tables.

SELECT Orders.ID, Developers.Name
FROM Orders
INNER JOIN Developers ON Orders.ID = Developers.ID;

LEFT JOIN

LEFT JOIN retrieves records from the left table that match records in the right table. Some databases have a slightly different statement for this – LEFT OUTER JOIN.

SELECT Developers.Name, Customer_orders.ID
FROM Developers
LEFT JOIN Customer_orders ON Developers.ID = Customer_orders.customer_id
ORDER BY Developers.Name;

RIGHT JOIN

RIGHT JOIN retrieves records from the right table that match records in the left table. Some databases call this statement differently – RIGHT OUTER JOIN.

SELECT Customer_orders.ID, Employees.Last_name, Employees.First_name
FROM Customer_orders
RIGHT JOIN Employees ON Customer_orders.employee_id = Employees.ID
ORDER BY Customer_orders.ID;

FULL JOIN

FULL JOIN returns all the records that match either in left or right tables.

SELECT Customers.Name, Customer_orders.ID
FROM Customers
FULL OUTER JOIN Orders ON Customers.ID=Customer_orders.customer_id
ORDER BY Customers.Name;

LIKE

Combine LIKE with the WHERE clause for finding specific patterns in columns.

SELECT * FROM users WHERE email LIKE '%gmail%';

ORDER BY

ORDER BY sets the order (ascending by default) of result records.

SELECT * FROM users ORDER BY email DESC;

SELECT

SELECT is one of the main SQL statements. It selects data from a database and returns the table of results, called the result-set.

SELECT username,email 
FROM users;

SELECT *

SELECT used with an asterisk * operator selects all data records from a specified table.

SELECT * FROM Customers;

SELECT DISTINCT

SELECT DISTINCT returns only the data that is distinct, and does not include duplicate entries.

SELECT DISTINCT City FROM Developers;

SELECT INTO

SELECT INTO statement selects specified data in a table and copies it to another table.

SELECT * INTO CustomerBackup2018
FROM Customers;

SELECT Name, Contact INTO CustomerBackup2017
FROM Customers;

SELECT TOP

SELECT TOP specifies the maximum number or percentage of data entries to return in a result-set.

SELECT * FROM Customers
LIMIT 3;

SELECT TOP 50 PERCENT * FROM Customers;

TRUNCATE TABLE

TRUNCATE TABLE removes data entries from a table in a database, but keeps the table, its datatype and column parameters.

TRUNCATE TABLE tbl_name

UNION

You can combine multiple result-sets using the UNION operator with two or more SELECT statements.

SELECT City FROM Developers
UNION
SELECT City FROM Customers
ORDER BY City;

UNION ALL

UNION ALL is used to combine two or more result-sets and keep all the duplicate data entries.

SELECT City FROM Developers
UNION ALL
SELECT City FROM Customers
ORDER BY City;

UPDATE

UPDATE statement is used with the WHERE clause to update data in the table.

UPDATE Developers
SET City = 'Berlin', Country= 'Germany'
WHERE Name = 'Antonio Indigo';

WHERE

WHERE clause specifies your query to filter only the results that satisfy your set condition.

WHERE doesn't work with the aggregate functions, for that purpose, use HAVING instead.

SELECT * FROM Developers
WHERE Country='France';