SQL, or Structured Query Language is a set of commands for managing relational database operations like delete, create, update, read, and so on.

SQL has been the international standard used by relational databases since 1987. There are four types of SQL commands:

  • Data Definition Language (DDL) – These commands define the structure of the database. For example, drop, rename, alter, and create. They affect the database structure. For example, creating a database or renaming a table.
  • Data Manipulation Language (DML) – These commands are used to work with the data. For example, insert, select, update, delete. These don’t affect the tables or database structure, but the data present in the tables. For example, inserting a new row, or updating a value in the row.
  • Transaction Control Language (TCL) – These commands control the transaction processing in the database. For example, rollback, save, commit. These commands have a permanent impact on the database. For example, rolling back the entire transaction to the previous state even if one process fails.
  • Data Control Language (DCL) – Data Control commands are used to authorize users and grant only the necessary permissions to a user or group. For example, one user can have read-only permission, whereas another can have read and write. The access control is done using the grant, deny and revoke commands.

Below is the SQL Cheat sheet containing the most useful commands. The cheat sheet will help you quickly reference the required commands with the correct syntax and expected result of a query. In this cheat sheet, we focus on DDL and DML commands as the other two types are quite straightforward to use.

Data Definition and Manipulation Queries

CommandDescriptionExample
ADDAdds a column or constraint (as specified) to the existing table.ALTER TABLE employee ADD last_name varchar2(255); ALTER TABLE employee ADD CONSTRAINT emp_det PRIMARY KEY (id, last_name);

ALTER TABLE employee ADD last_name varchar2(255); ALTER TABLE employee ADD CONSTRAINT emp_det PRIMARY KEY (id, last_name);
ALTER TABLEAlters the specified table to add, update or delete the column of a table.ALTER TABLE employee ADD last_name varchar2(255);

ALTER TABLE employee DROP COLUMN last_name;
ALTER COLUMNChange the data type of the column. For example, to change the type of joining_date column of the employee table from varchar2 to datetime.ALTER TABLE employee ALTER COLUMN joining_date datetime;
ALLLogical operator used with SELECT, WHERE and HAVING, and returns true if all the values satisfy the subquery conditionSELECT employee_name, joining_date from employee WHERE employee_id = ALL (select employee_id from department_details WHERE department = ‘R&D’);
ANDLogical operator which returns true only when all the conditions in the WHERE clause are satisfied.SELECT employee_name, salary from employee WHERE city = ‘California’ AND salary > 2000;
ANYLogical operator; returns true if even one of the subquery values satisfies the condition in where clauseSELECT employee_id, employee_name from employee WHERE employee_id = ANY (select employee_id from department_details WHERE department = ‘HR’ OR department = ‘R&D’);
ASCreates an alias for the table or column till the time of query execution, useful when the name is used multiple times, especially during table joinsSELECT count(employee_id) AS employees_from_houston from employee WHERE city = 'Houston';
ASCReturns the data in ascending order, used with the ORDER BY clause. ORDER BY itself sorts the results in ascending by default.SELECT employee_name, joining_date, salary from employee ORDER BY employee_name ASC;
BETWEENTo select values within a rangeSELECT employee_name, joining_date, department_id from employee WHERE salary  BETWEEN 40000 AND 100000;
CASEConsists of a set of statements; returns the value of the statement that is true, IF none of the conditions are met, the condition in ELSE part is executed. If there is no else, then returns NULL.SELECT order_amount, customer_id, contact_email CASE WHEN order_amount > 3000 THEN "Eligible for 40% discount" WHEN order_amount between 2000 and 3000 THEN "Eligible for 25% discount" ELSE "Eligible for 5% discount" END FROM order_details;
CREATE DATABASECreates a new database with the specified nameCREATE DATABASE movies_development;
CREATE TABLECreates a new table with the specified table name and column names and typesCREATE TABLE movie_info (movie_name varchar2(255), release_date datetime, lead_actor varchar2(255), music_director varchar2(255));
DEFAULTSets a default value for the specified column, used with CREATE or ALTER TABLE commandsCREATE TABLE employee (joining_date SET DEFAULT CURRENT_DATE);

ALTER TABLE product ALTER is_available SET DEFAULT true;
DELETEDeletes data from the specified tableDELETE from employee where employee_id = 345;
DESCReturns the data in descending order, used with the ORDER BY clause.SELECT employee_name, joining_date, salary from employee ORDER BY employee_name DESC;
DROP COLUMNDeletes the specified column from the specified table.ALTER TABLE employee DROP COLUMN employee_name;
DROP DATABASEDeletes the entire databaseDROP DATABASE movies_development;
DROP DEFAULTDeletes the default value of the specified columnALTER TABLE employee ALTER COLUMN is_available DROP DEFAULT;
DROP TABLEDeletes the specified tableDROP TABLE employee;
EXISTSChecks if a record exists or not in the subquery, and returns true if one or more results are found.SELECT employee_id, contact_number FROM employee WHERE EXISTS (SELECT employee_id, department FROM department WHERE employee_id = 345 AND department = 'HR');
FROMSpecifies the table from which data should be selected or deletedSELECT * FROM employee; DELETE FROM employee where employee_id = 345;
GROUP BYGroups data as per the specified column, used for aggregate functionsDisplay the number of employees in each country

SELECT COUNT(employee_id), country from employee GROUP BY country;

Shows the average ratings of employees of each department

SELECT AVG(rating), department from employee GROUP BY department;
INUsed to select multiple values at once in a WHERE clause instead of using multiple OR conditionsSELECT employee_name FROM employee WHERE country IN ('India', 'United Kingdom', 'Singapore', 'Australia');
INDEXIndex makes querying data more efficient and faster. Indexes are usually created on columns that are searched most.Create index:
CREATE INDEX idx_employee ON employee (first_name, last_name);

Create a unique index where values cannot be duplicated:
CREATE UNIQUE INDEX idx_employee ON employee (first_name, last_name);

Delete the index:
ALTER TABLE employee DROP INDEX idx_employee;
INSERT INTOAdd new row in a tableINSERT INTO employee (employee_id, employee_name, salary, core_skill) VALUES (451, ‘Lee Cooper’, 40000, ‘Java’);
IS NULLChecks for null valuesSELECT employee_id from employee where employee_name IS NULL;
IS NOT NULLChecks for values that are not nullSELECT employee_id, core_skill from  employee where core_skill IS NOT NULL;
LIKEReturns all the values that match a given patternSELECT employee_id,  first_name, last_name  where  first_name LIKE ‘%tony’;
NOT LIKEReturns all values that do not match the given patternSELECT employee_id,  first_name, last_name  where  first_name NOT LIKE ‘%tony’;
ORReturns true if one of the conditions in the where clause is satisfiedSELECT * from employee where country = ‘India’ OR country = ‘Australia’;
ORDER BYOrders the results in ascending order (by default) or the order specified in the query (ascending or descending)SELECT employee_name, salary from employee ORDER BY salary DESC;
ROWNUMReturns the specified number of rows mentioned in the WHERE clause of the querySELECT * from employee where ROWNUM <= 5; This will return the first five rows in the resultset.
SELECTSelects the mentioned columns of the table based on the given conditions. If * is specified, all the column values are returned.SELECT employee_id from employee; SELECT * from employee;
SELECT INTOCopies data from the source table into another destination table. You can select all the columns (*) or specific columns.SELECT * INTO new_employee_info FROM employee; SELECT employee_name, joining_date, core_skill INTO new_employee_info FROM employee;
SELECT TOPSelects the specified number of records from the tableSELECT TOP 5 employee_id from employee where employee_rating = 5;
SETSets the value of a column to the new specified value during an UPDATE operation.UPDATE employee SET first_name = ‘Tony’ WHERE employee_id = 345;
SOMEReturns true if one of the conditions in the subquery is satisfied. SOME is similar to the ANY command.SELECT employee_id, employee_name from employee WHERE salary > SOME (select salary from employee WHERE department = ‘HR’);
TRUNCATE TABLEDeletes data from the table – remember that the table will not be deleted.TRUNCATE TABLE log_info;
UNIONReturns distinct values from 2 or more tables that are joined. To get the duplicate values also, use UNION ALL.SELECT city from employee UNION SELECT city from office_locations;
UNIQUEAdds a unique constraint to the specified column, meaning that the column cannot have duplicate values. Can be used during table create or alter commands.CREATE TABLE employee (employee_id int NOT NULL, UNIQUE(employee_id));

ALTER TABLE employee ADD UNIQUE(employee_id);
UPDATEUpdates the value of specified column with a new valueUPDATE employee SET first_name = ‘Tony’ WHERE employee_id = 345;
VALUESUsed with the INSERT command to add a new row of values into the tableINSERT INTO employee (employee_id, employee_name, salary, core_skill) VALUES (451, ‘Lee Cooper’, 40000, ‘Java’);
WHEREAdds conditions to filter the result set of a select statementSELECT * from employee WHERE salary > 20000;
SQL Commands Cheat sheet for quick reference

Aggregate functions

Aggregate functions are data manipulation commands that work on numeric columns like int, and float. These are helpful in filtering and sorting data at the database level itself. Some commonly used aggregate functions are:

FUNCTIONDESCRIPTIONEXAMPLE
AVGReturns the average value of the specified columnSELECT AVG(marks) from students where subject = ‘English’;
MINReturns the smallest value of the specified columnSELECT MIN(price) from product WHERE product_category = ‘shoes’;
MAXReturns the largest value of the specified columnSELECT MAX(quantity), product_name from inventory;
COUNTReturns the number of rows that satisfy the querySELECT COUNT(*) from employee; - returns total number of records in the employee table.<br><br>SELECT COUNT(*) from employee where salary > 20000; - returns number of employees whose salary is greater than 20000
SUMReturns sum of the values of the specified numerical columnSELECT SUM(marks) from students where subject = ‘English’;
Common aggregate functions

SQL Joins

SQL joins are very important because they connect and filter data from multiple tables. Joins are a bit tricky and can give unexpected results, if not executed properly. The below table will help you quickly refer to the 4 types of SQL joins:

JOIN TYPEDESCRIPTIONSYNTAXEXAMPLE
Inner joinReturns records that are matching in the tables joined; it is similar to an intersection.SELECT column1, column2… from table1 INNER JOIN table2 on table1.columnN = table2.columnN;select c.customer_id, o.order_id, c.customer_phone from customer c INNER JOIN order o on c.customer_id = o.customer_id;
Full (outer) joinReturns all the records that have a match on either side – left or right. It is similar to a union.SELECT column1, column2… from table1 FULL OUTER JOIN table2 on table1.columnN = table2.columnN;select c.customer_id, o.order_id, c.customer_phone from customer c FULL OUTER JOIN order o on c.customer_id = o.customer_id;
Left joinReturns all the records from the left table and records that match the criteria on the right tableSELECT column1, column2… from table1 LEFT JOIN table2 on table1.columnN = table2.columnN;select c.country_id, c.country_name, l.location_name from country c LEFT JOIN locations l on c.country_id = l.country_id;
Right joinReturns all the records from the right table and records that match the criteria on the left tableSELECT column1, column2… from table1 RIGHT JOIN table2 on table1.columnN = table2.columnN;select c.country_id, c.country_name, l.location_name from country c RIGHT JOIN locations l on c.country_id = l.country_id;
Types of joins with syntax and example

Additional resources

SQL is an important tool for software developers, data scientists, and analysts alike. A handy reference of SQL commands in the form of a cheat sheet can save you a lot of time, and help you understand the expected output of each keyword.

For additional information, please refer to the following resources:

SQL Tutorial: SQL Cheat Sheet

Intellipaat: SQL Commands Cheat Sheet

WebsiteSetup: SQL Cheat Sheet

ProgrammingWithMosh: SQL Cheat Sheet

PostgreSQL Cheat Sheet