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 | 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 countrySELECT 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; |
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 | Shows a total number of records in the employee table.SELECT COUNT(*) from employee;Show the number of employees whose salary is greater than 20000 SELECT COUNT(*) from employee where salary > 20000; |
| SUM | Returns sum of the values of the specified numerical column | SELECT SUM(marks) from students where subject = ‘English’; |
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; |
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:
Intellipaat: SQL Commands Cheat Sheet