Black friday

Save big!

All courses under $5 - for a limited time!

Code has been added to clipboard!

SQL Commands List for the Most Common Actions

Reading time 7 min
Published Jun 3, 2019
Updated Oct 3, 2019

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.

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

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

ALTER TABLE

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

Example
ALTER TABLE Developers
ADD BirthDate date;
Example
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.

Example
SELECT ID as CustomerID, Name AS Customers
FROM Customers;
Example
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.

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

Example
CREATE DATABASE testingDB;

CREATE TABLE

CREATE TABLE statement creates a new table in a database.

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

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

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

Example
DELETE FROM Developers
WHERE Name='Antonio Indigo';
Example
DELETE * FROM Developers;

GRANT

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

Example
GRANT SELECT, UPDATE ON YOUR_TABLE TO FIRST_USER, SECOND_USER;

REVOKE

REVOKE command is for taking away users' permisions.

Example
REVOKE SELECT, UPDATE ON YOUR_TABLE FROM FIRST_USER, SECOND_USER;

COMMIT

COMMIT command is for saving every transaction to the database.

Example
DELETE FROM CUSTOMERS  
WHERE AGE = 18;  
COMMIT;

ROLLBACK

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

Example
DELETE FROM CUSTOMERS  
WHERE AGE = 18;  
ROLLBACK;

SAVEPOINT

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

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

Example
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

Example
DROP INDEX tbl_name.indx_name

MS Access

Example
DROP INDEX indx_name ON tbl_name

DB2/Oracle

Example
DROP INDEX indx_name

MySQL

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

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

Example
SELECT id, name
FROM customers
WHERE EXISTS (SELECT id FROM customer_orders WHERE customer_orders.customer_id = customers.id AND customers.city = "Rome");
Theory is great, but we recommend digging deeper!

GROUP BY

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

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

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

IN

The IN operator includes multiple values into the WHERE clause.

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

INSERT INTO

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

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

INNER JOIN

INNER JOIN combines rows from different tables.

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

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

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

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

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

ORDER BY

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

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

Example
SELECT username,email 
FROM users;

SELECT *

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

Example
SELECT * FROM Customers;

SELECT DISTINCT

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

Example
SELECT DISTINCT City FROM Developers;

SELECT INTO

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

Example
SELECT * INTO CustomerBackup2018
FROM Customers;
Example
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.

Example
SELECT * FROM Customers
LIMIT 3;
Example
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.

Example
TRUNCATE TABLE tbl_name

UNION

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

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

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

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

Example
SELECT * FROM Developers
WHERE Country='France';