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
Command
Description
Example
ADD
Adds 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 TABLE
Alters 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 COLUMN
Change 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;
ALL
Logical operator used with SELECT, WHERE and HAVING, and returns true if all the values satisfy the subquery condition
SELECT employee_name, joining_date from employee WHERE employee_id = ALL (select employee_id from department_details WHERE department = ‘R&D’);
AND
Logical 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;
ANY
Logical operator; returns true if even one of the subquery values satisfies the condition in where clause
SELECT employee_id, employee_name from employee WHERE employee_id = ANY (select employee_id from department_details WHERE department = ‘HR’ OR department = ‘R&D’);
AS
Creates an alias for the table or column till the time of query execution, useful when the name is used multiple times, especially during table joins
SELECT count(employee_id) AS employees_from_houston from employee WHERE city = 'Houston';
ASC
Returns 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;
BETWEEN
To select values within a range
SELECT employee_name, joining_date, department_id from employee WHERE salary BETWEEN 40000 AND 100000;
CASE
Consists 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 DATABASE
Creates a new database with the specified name
CREATE DATABASE movies_development;
CREATE TABLE
Creates a new table with the specified table name and column names and types
Sets a default value for the specified column, used with CREATE or ALTER TABLE commands
CREATE TABLE employee (joining_date SET DEFAULT CURRENT_DATE);
ALTER TABLE product ALTER is_available SET DEFAULT true;
DELETE
Deletes data from the specified table
DELETE from employee where employee_id = 345;
DESC
Returns 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 COLUMN
Deletes the specified column from the specified table.
ALTER TABLE employee DROP COLUMN employee_name;
DROP DATABASE
Deletes the entire database
DROP DATABASE movies_development;
DROP DEFAULT
Deletes the default value of the specified column
ALTER TABLE employee ALTER COLUMN is_available DROP DEFAULT;
DROP TABLE
Deletes the specified table
DROP TABLE employee;
EXISTS
Checks 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');
FROM
Specifies the table from which data should be selected or deleted
SELECT * FROM employee; DELETE FROM employee where employee_id = 345;
GROUP BY
Groups data as per the specified column, used for aggregate functions
Display 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;
IN
Used to select multiple values at once in a WHERE clause instead of using multiple OR conditions
SELECT employee_name FROM employee WHERE country IN ('India', 'United Kingdom', 'Singapore', 'Australia');
INDEX
Index 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;
SELECT employee_id from employee where employee_name IS NULL;
IS NOT NULL
Checks for values that are not null
SELECT employee_id, core_skill from employee where core_skill IS NOT NULL;
LIKE
Returns all the values that match a given pattern
SELECT employee_id, first_name, last_name where first_name LIKE ‘%tony’;
NOT LIKE
Returns all values that do not match the given pattern
SELECT employee_id, first_name, last_name where first_name NOT LIKE ‘%tony’;
OR
Returns true if one of the conditions in the where clause is satisfied
SELECT * from employee where country = ‘India’ OR country = ‘Australia’;
ORDER BY
Orders 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;
ROWNUM
Returns the specified number of rows mentioned in the WHERE clause of the query
SELECT * from employee where ROWNUM <= 5; This will return the first five rows in the resultset.
SELECT
Selects 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 INTO
Copies 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 TOP
Selects the specified number of records from the table
SELECT TOP 5 employee_id from employee where employee_rating = 5;
SET
Sets the value of a column to the new specified value during an UPDATE operation.
UPDATE employee SET first_name = ‘Tony’ WHERE employee_id = 345;
SOME
Returns 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 TABLE
Deletes data from the table – remember that the table will not be deleted.
TRUNCATE TABLE log_info;
UNION
Returns 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;
UNIQUE
Adds 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);
UPDATE
Updates the value of specified column with a new value
UPDATE employee SET first_name = ‘Tony’ WHERE employee_id = 345;
VALUES
Used with the INSERT command to add a new row of values into the table
Adds conditions to filter the result set of a select statement
SELECT * 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:
FUNCTION
DESCRIPTION
EXAMPLE
AVG
Returns the average value of the specified column
SELECT AVG(marks) from students where subject = ‘English’;
MIN
Returns the smallest value of the specified column
SELECT MIN(price) from product WHERE product_category = ‘shoes’;
MAX
Returns the largest value of the specified column
SELECT MAX(quantity), product_name from inventory;
COUNT
Returns the number of rows that satisfy the query
SELECT 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
SUM
Returns sum of the values of the specified numerical column
SELECT 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 TYPE
DESCRIPTION
SYNTAX
EXAMPLE
Inner join
Returns 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) join
Returns 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 join
Returns all the records from the left table and records that match the criteria on the right table
SELECT 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 join
Returns all the records from the right table and records that match the criteria on the left table
SELECT 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.
Contents
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:
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
Command
Description
Example
ADD
Adds 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 TABLE
Alters 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 COLUMN
Change 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;ALL
Logical operator used with SELECT, WHERE and HAVING, and returns true if all the values satisfy the subquery condition
SELECT employee_name, joining_date from employee WHERE employee_id = ALL (select employee_id from department_details WHERE department = ‘R&D’);AND
Logical 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;ANY
Logical operator; returns true if even one of the subquery values satisfies the condition in where clause
SELECT employee_id, employee_name from employee WHERE employee_id = ANY (select employee_id from department_details WHERE department = ‘HR’ OR department = ‘R&D’);AS
Creates an alias for the table or column till the time of query execution, useful when the name is used multiple times, especially during table joins
SELECT count(employee_id) AS employees_from_houston from employee WHERE city = 'Houston';ASC
Returns 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;BETWEEN
To select values within a range
SELECT employee_name, joining_date, department_id from employee WHERE salary BETWEEN 40000 AND 100000;CASE
Consists 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 DATABASE
Creates a new database with the specified name
CREATE DATABASE movies_development;CREATE TABLE
Creates a new table with the specified table name and column names and types
CREATE TABLE movie_info (movie_name varchar2(255), release_date datetime, lead_actor varchar2(255), music_director varchar2(255));DEFAULT
Sets a default value for the specified column, used with CREATE or ALTER TABLE commands
CREATE TABLE employee (joining_date SET DEFAULT CURRENT_DATE);ALTER TABLE product ALTER is_available SET DEFAULT true;DELETE
Deletes data from the specified table
DELETE from employee where employee_id = 345;DESC
Returns 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 COLUMN
Deletes the specified column from the specified table.
ALTER TABLE employee DROP COLUMN employee_name;DROP DATABASE
Deletes the entire database
DROP DATABASE movies_development;DROP DEFAULT
Deletes the default value of the specified column
ALTER TABLE employee ALTER COLUMN is_available DROP DEFAULT;DROP TABLE
Deletes the specified table
DROP TABLE employee;EXISTS
Checks 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');FROM
Specifies the table from which data should be selected or deleted
SELECT * FROM employee; DELETE FROM employee where employee_id = 345;GROUP BY
Groups data as per the specified column, used for aggregate functions
Display 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;IN
Used to select multiple values at once in a WHERE clause instead of using multiple OR conditions
SELECT employee_name FROM employee WHERE country IN ('India', 'United Kingdom', 'Singapore', 'Australia');INDEX
Index 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 INTO
Add new row in a table
INSERT INTO employee (employee_id, employee_name, salary, core_skill) VALUES (451, ‘Lee Cooper’, 40000, ‘Java’);IS NULL
Checks for null values
SELECT employee_id from employee where employee_name IS NULL;IS NOT NULL
Checks for values that are not null
SELECT employee_id, core_skill from employee where core_skill IS NOT NULL;LIKE
Returns all the values that match a given pattern
SELECT employee_id, first_name, last_name where first_name LIKE ‘%tony’;NOT LIKE
Returns all values that do not match the given pattern
SELECT employee_id, first_name, last_name where first_name NOT LIKE ‘%tony’;OR
Returns true if one of the conditions in the where clause is satisfied
SELECT * from employee where country = ‘India’ OR country = ‘Australia’;ORDER BY
Orders 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;ROWNUM
Returns the specified number of rows mentioned in the WHERE clause of the query
SELECT * from employee where ROWNUM <= 5; This will return the first five rows in the resultset.SELECT
Selects 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 INTO
Copies 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 TOP
Selects the specified number of records from the table
SELECT TOP 5 employee_id from employee where employee_rating = 5;SET
Sets the value of a column to the new specified value during an UPDATE operation.
UPDATE employee SET first_name = ‘Tony’ WHERE employee_id = 345;SOME
Returns 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 TABLE
Deletes data from the table – remember that the table will not be deleted.
TRUNCATE TABLE log_info;UNION
Returns 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;UNIQUE
Adds 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);UPDATE
Updates the value of specified column with a new value
UPDATE employee SET first_name = ‘Tony’ WHERE employee_id = 345;VALUES
Used with the INSERT command to add a new row of values into the table
INSERT INTO employee (employee_id, employee_name, salary, core_skill) VALUES (451, ‘Lee Cooper’, 40000, ‘Java’);WHERE
Adds conditions to filter the result set of a select statement
SELECT * 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:
FUNCTION
DESCRIPTION
EXAMPLE
AVG
Returns the average value of the specified column
SELECT AVG(marks) from students where subject = ‘English’;MIN
Returns the smallest value of the specified column
SELECT MIN(price) from product WHERE product_category = ‘shoes’;MAX
Returns the largest value of the specified column
SELECT MAX(quantity), product_name from inventory;COUNT
Returns the number of rows that satisfy the query
SELECT 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 20000SUM
Returns sum of the values of the specified numerical column
SELECT 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 TYPE
DESCRIPTION
SYNTAX
EXAMPLE
Inner join
Returns 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) join
Returns 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 join
Returns all the records from the left table and records that match the criteria on the right table
SELECT 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 join
Returns all the records from the right table and records that match the criteria on the left table
SELECT 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.