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 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’ permissions.
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
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';