SQL Query Cheat Sheet – Complete Overview

by

Welcome to the “SQL Query Cheat Sheet,” your quick guide to the basics of SQL. SQL, or Structured Query Language, is the simple yet powerful language used to communicate with databases. It’s how we ask the database to give us the information we need, update records, add new data, or remove what’s no longer needed. This cheat sheet is designed for anyone looking to brush up on their SQL skills quickly.

Whether you’re just getting started or need a quick refresher, here you’ll find the key concepts and basic queries laid out in an easy-to-follow format. From selecting data to joining tables, we’ve boiled down SQL to its essential parts so you can get the information you need at a glance. This guide is here to help you get comfortable with SQL basics and use them confidently in your projects.

Table of Contents

Here the Overview In short

1. SELECT Statement

The SELECT statement is a fundamental SQL (Structured Query Language) command that is used to retrieve data from a database. With the help of select statement we can retrieve data from single table and multiple tables by specifying which columns you want to retrieve.

SQL
-- Basic SELECT statement
SELECT column1, column2 FROM table_name;

-- Retrieve all columns from a table
SELECT * FROM table_name;

-- Rename column(s) using aliases
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;

-- Filter rows using WHERE clause
SELECT * FROM table_name WHERE condition;

-- Order results using ORDER BY clause
SELECT * FROM table_name ORDER BY column_name ASC|DESC;

-- Limit the number of rows returned
SELECT * FROM table_name LIMIT n;

-- Retrieve distinct values
SELECT DISTINCT column_name FROM table_name;

2. Aggregate Functions

Aggregate functions is a built in functions in SQL. It is used to perform mathematical operation on the data on given column. It is also mostly used with the GROUP BY clause to perform calculations on the group of data. We can perform the operations like counting rows, calculating averages, finding the maximum or minimum value, and more.

SQL
-- Calculate the sum of a column
SELECT SUM(column_name) FROM table_name;

-- Calculate the average of a column
SELECT AVG(column_name) FROM table_name;

-- Find the minimum value in a column
SELECT MIN(column_name) FROM table_name;

-- Find the maximum value in a column
SELECT MAX(column_name) FROM table_name;

-- Count the number of rows
SELECT COUNT(*) FROM table_name;

-- Group rows and apply aggregate functions
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;

3. Filtering and Sorting

In SQL, Filtering and sorting are two fundamental operations. It is used to apply restrictions on the date during data retrieval process.

SQL
-- Basic WHERE clause conditions
SELECT * FROM table_name WHERE column_name = value;

-- Combine multiple conditions using AND or OR
SELECT * FROM table_name WHERE condition1 AND condition2;

-- Use wildcards with LIKE for pattern matching
SELECT * FROM table_name WHERE column_name LIKE 'pattern%';

-- Sort by multiple columns
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

4. Joins

Joins in SQL are used when we need to retrieve data from two or more tables based on related column. To perform join operation, one column must be common.

SQL
-- INNER JOIN: Return matching rows from both tables
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

-- LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

-- RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

-- FULL OUTER JOIN: Return all rows when there is a match in either table
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;

5. Subqueries

Subqueries, also known as nested queries or subselects. It means that one SQL queries is embedded within another query. It allow you to retrieve data from one table and use it as a condition or value within another query. Subqueries are a powerful feature in SQL and are often used for filtering, joining, and performing calculations on data

SQL
-- Scalar subquery: Retrieve a single value from a subquery
SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value FROM table1;

-- Correlated subquery: Use values from the outer query in the subquery
SELECT column1 FROM table1 WHERE column2 = (SELECT MAX(column2) FROM table2 WHERE table2.column3 = table1.column3);

6. Data Modification

To modify the data in the table, data modification statement like insert, update and delete will be used.

SQL
-- INSERT INTO: Add a new row to a table
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

-- UPDATE: Modify existing data in a table
UPDATE table_name SET column1 = new_value WHERE condition;

-- DELETE: Remove rows from a table
DELETE FROM table_name WHERE condition;

7. SQL Constraints

In SQL, Constraints are rules and conditions that we can apply on database tables. It helps to maintain data integrity and consistency in your database. Constraints ensure that data meets specific criteria and prevents the insertion, modification, or deletion of data that would violate these criteria.

SQL
-- PRIMARY KEY: Uniquely identifies each row in a table
CREATE TABLE table_name (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- FOREIGN KEY: Establishes a link between two tables
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- UNIQUE: Ensures that values in a column are unique
CREATE TABLE table_name (
    email VARCHAR(255) UNIQUE,
    -- Other columns
);

-- CHECK: Defines a condition that must be met for a column's value
CREATE TABLE employees (
    age INT CHECK (age >= 18),
    -- Other columns
);

8. SQL Indexes

SQL indexes are database objects that provide a way to improve the performance of data retrieval operations on database tables. They are used to speed up the process of searching, querying, and retrieving data by creating a data structure that organizes and optimizes the storage of data.

SQL
-- INDEX: Improves the speed of data retrieval operations
CREATE INDEX index_name ON table_name (column_name);

-- Composite Index: Index on multiple columns
CREATE INDEX index_name ON table_name (column1, column2);

-- UNIQUE INDEX: Ensures unique values in indexed columns
CREATE UNIQUE INDEX index_name ON table_name (column_name);

9. SQL Views

SQL views are virtual database objects that provide a way to present data from one or more tables in a structured and controlled manner. Views allow you to create a logical representation of the data that can be queried like a regular table, while the underlying data remains unchanged.

SQL
-- CREATE VIEW: Creates a virtual table based on the result of a SELECT statement
CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;

-- DROP VIEW: Removes a view
DROP VIEW view_name;

10. SQL Transactions

An SQL transaction is a sequence of one or more SQL statements that are treated as a single unit of work. Transactions are used to ensure the consistency, integrity, and reliability of a database by following the principles of ACID.

SQL
-- BEGIN TRANSACTION: Start a transaction
BEGIN TRANSACTION;

-- COMMIT: Save changes made during the transaction
COMMIT;

-- ROLLBACK: Undo changes made during the transaction
ROLLBACK;

11. SQL Functions

SQL functions can be pre-defined and user-defined to perform specific tasks on data. And it return specific values when invoked within SQL statements.

SQL
-- User-Defined Functions (UDFs): Custom functions
CREATE FUNCTION function_name(parameter_type) RETURNS return_type AS
BEGIN
    -- Function logic
    RETURN result;
END;

-- Calling a UDF
SELECT function_name(argument);

12. SQL Triggers

SQL triggers automatically execute a specified set of actions or commands when a specific event occurs within the database. Some events are as INSERT, UPDATE, DELETE, or other actions.

SQL
-- CREATE TRIGGER: Defines a trigger that activates in response to an event
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic
END;

13. SQL User Management

SQL
-- CREATE USER: Create a new user
CREATE USER username IDENTIFIED BY 'password';

-- GRANT PRIVILEGES: Assign privileges to a user
GRANT privilege_name ON database_name.table_name TO username;

-- REVOKE PRIVILEGES: Remove privileges from a user
REVOKE privilege_name ON database_name.table_name FROM username;

14. SQL Window Functions

SQL window functions (also known as windowed or analytic functions) are a category of SQL functions that perform calculations across a set of rows related to the current row within a query result set. Unlike aggregate functions, which return a single value for a group of rows, window functions return a value for each row within the specified window or frame. Window functions are used for advanced analytical and reporting tasks.

SQL
-- PARTITION BY: Divides result set into partitions for calculations
SELECT column1, column2, SUM(column3) OVER (PARTITION BY column1) AS sum_column3
FROM table_name;

-- ROW_NUMBER(): Assigns a unique number to each row within a result set
SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column1) AS row_num
FROM table_name;

Detailed Explanations of Each SQL Statements with examples

Create Database

This syntax is used to create the database. Database creation depends on the which database you are using.

Syntax:

SQL
CREATE DATABASE your_database_name;

Example:

SQL
create database demoDatabase;

Create Table

Syntax:

SQL
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

In SQL Server

Below query contains identity() that works with SQL server. If you are using MySQL workbench then use other query.

SQL
create table employee
(
employeeid int identity(1,1) primary key,
employeenumber int not null unique check (employeenumber>0),
firstname varchar(30) not null,
lastname varchar(30) not null,
areacode int default 710100,
address varchar(50),
country varchar(50) default 'India'
);

In MySQL Workbench

Below query contains identity() that works with SQL server. If you are using MySQL workbench then use other query.

SQL
CREATE TABLE employee
(
    employeeid int NOT NULL AUTO_INCREMENT,
    employeenumber int not null unique check (employeenumber > 0),
    firstname varchar(30) not null,
    lastname varchar(30) not null,
    areacode int default 710100,
    address varchar(50),
    country varchar(50) default 'India',
    PRIMARY KEY (employeeid)
);

Insert values into table

INSERT INTO statement is used to add one or more rows of data to an existing table.

Syntax:

SQL
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

SQL
insert into employee (employeenumber, firstname, lastname,areacode, address, country) 
values (1001,'Naresh','Raj',default,'Delhi','Sri Lanka');


insert into employee (employeenumber, firstname, lastname,areacode, address, country) 
values (1002,'Gautan','Singh',default,'Hyderabad',default);

Select Value from Table

With the help of Select statement we can fetch data from employee. If we need all the column then will use * and if we need some specific column then we will give the name of that column. Below is the example for the better demonstration.

Syntax:

SQL
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:

SQL
-- Fetch all records
select * from employee;

-- Fetch particular columns
select customerid, customernumber, lastname, firstname
from employee;

Update Table/ Add New Value to new column

Update statement is used to update the value of given field in table. This statement allows you to modify existing records in a table based on a specified condition.

Syntax:

SQL
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

SQL
update employee set age= 28 where
employeenumber=1003;

Delete and Drop

Delete data/record/row from table

This will delete the complete row if condition matched. “DELETE” statement is used to remove specific rows from a table. It is typically used to remove data while keeping the table structure intact.

SQL
delete
from employee
where country='India';

entire table/Drop Table

Drop table deletes the entire table along with table schema. The “DROP” statement is used to remove database objects entirely, including tables, indexes, views, or even entire databases.

SQL
drop table employee;

Alter Table

Delete column from table

This is used to remove given column from the table.

SQL
alter table employee
drop column phonenumber;

Adding New Column

Alter Statement is used to modify the table. We can add new column, we can change the name of table, we can change the data types of the table with this Alter Statement. Below is the example of each.

SQL
alter table employee
add phonenumber varchar(20);

alter table employee
add age int default 21;

Change Data Type

SQL
alter table employee
modify column areacode varchar(10);

Rename Column

SQL
alter table employee
rename column areacode to newareacode;

Limit / Fetch Specific Number of Rows

Limit is use to restrict on the number of outputs. Limit 2 means only first two row should be in output.

In MySQL

SQL
select * from employee Limit 2;

In SQL Server

SQL
select top 2 * from employee;

Like / Search something

LIKE operator is used to search for a specific pattern.

  •  The percent sign % represents zero, one, or multiple characters.
  •  The underscore sign _ represents one, single character.
SQL
select * from employee
where lastname like '_a%';

IN / Search something

IN statement in SQL is used to filter results based on a given list of values. You can retrieve rows from a table where a given column’s value matches any value in the given list. The IN statement is often used with the SELECT statement, but it can also be used with other SQL statements like DELETE and UPDATE for filtering or updating specific rows.

Syntax:

SQL
SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example:

SQL
select * from employee
where lastname in ('Raj', 'Peter');

Not Equal <>

<> operator is know as not equal which is used to test for inequality between two values. It checks if the values on either side of the operator are not equal to each other. It will returns true if the values are different and false if they are the same.

Syntax:

SQL
SELECT column1, column2, ...
FROM table_name
WHERE value1 <> value2;

Example:

SQL
SELECT * FROM employee
WHERE lastname <> 'Peter';

IS NULL

IS NULL operator is used to check whether a column or expression has a NULL value. NULL represents the absence of a value in a database table. It is used with the WHERE clause of a SQL query to filter rows where a specific column or expression is NULL.

Syntax:

SQL
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

Example:

SQL
SELECT * FROM employee
WHERE lastname IS NULL;

IS NOT NULL

IS NOT NULL operator is used to check whether a column or expression has a value that is not NULL. It is the opposite of the IS NULL operator. When you use IS NOT NULL, you are essentially checking for the presence of a non-NULL value in a column or expression.

Syntax:

SQL
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

Example:

SQL
SELECT * FROM employee
WHERE lastname IS NOT NULL;

Between

BETWEEN operator is used to filter rows based on the given range values for a specific row. This range value is inclusive in the result output. It will be helpful, suppose we need all student whose age lies between 8-13.

Syntax:

SQL
SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example:

SQL
select * from employee
where employeenumber between 1002 and 1005;

count()

COUNT() function is used to count the number of rows that meets the given condition. We can also count the total number of rows in a table. It is an aggregate function that is often used in conjunction with the SELECT statement.

Syntax:

SQL
SELECT COUNT(column_name) FROM table_name WHERE condition;

Example:

SQL
select count(*) from employee
where employeenumber between 1002 and 1004;

OR

SQL
select count(*) As total from employee
where employeenumber between 1002 and 1004;

sum()

SUM() function is used to calculate the sum of values in a specific column of a table. It is used to perform mathematical operations on numeric data within a table.

Syntax:

SQL
select sum(age) from employee;

Example:

SQL
SELECT SUM(column_name) FROM table_name WHERE condition;

max()

MAX() function is used to retrieve the maximum (largest) value from a specific column in a table.

Syntax:

SQL
SELECT MAX(column_name) FROM table_name WHERE condition;

Example:

SQL
SELECT MAX(age)
FROM employee;

min()

MIN() function is used to retrieve the minimum (smallest) value from a specific column in a table.

Syntax:

SQL
SELECT MIN(column_name) FROM table_name WHERE condition;

Example:

SQL
SELECT MIN(age)
FROM employee;

AVG() / average

AVG() function is used to calculate the average (mean) value of a specific column in a table.

Syntax:

SQL
SELECT AVG(column_name) FROM table_name WHERE condition;

Example:

SQL
SELECT avg(age)
FROM employee;

Group By and having clause

Here we will see in details

Let’s create a table sales and insert some data in it.

Group By

SQL
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_id, product_id, sale_date, amount)
VALUES
    (1, 201, '2023-09-01', 55.00),
    (2, 202, '2023-09-01', 15.00),
    (3, 201, '2023-09-02', 64.00),
    (4, 203, '2023-09-02', 42.00),
    (5, 202, '2023-09-03', 81.00);

Query 1 : Group sales by product id and count the number of sales for each product.

SQL
SELECT product_id, COUNT(*) AS total_sales
FROM sales
GROUP BY product_id;

Query 2: Group sales by product id and calculate the average sale amount for each product.

SQL
SELECT product_id, AVG(amount) AS average_amount
FROM sales
GROUP BY product_id;

HAVING clause

In SQL, HAVING clause is used with the GROUP BY clause to filter the results of a grouped query. While the WHERE clause filters rows before grouping, the HAVING clause filters groups after the grouping has occurred. It allows you to specify conditions that groups must meet to be included in the result set.

Syntax

SQL
SELECT column1, column2, ...
FROM table
GROUP BY column1, column2, ...
HAVING condition;

Example

SQL
SELECT product_id, AVG(amount) AS average_amount
FROM sales
GROUP BY product_id
HAVING AVG(amount) > 50;

Output

SQL Join

Query To Create Table to Perform Join (MySQL)

Below Example is for demonstration to create table with primary key and foreign key.

Create Table

Create Class Table

SQL
CREATE TABLE class
(
    classid int NOT NULL AUTO_INCREMENT,
    classnumber int not null unique check (classnumber > 0),
    classname varchar(30) not null,
    PRIMARY KEY (classid)
);

Create student table

SQL
CREATE TABLE student
(
    studentid int NOT NULL AUTO_INCREMENT,
    studentnumber int not null unique check (studentnumber > 0),
    firstname varchar(30) not null,
    lastname varchar(30) not null,
    rollno int default 0,
    address varchar(50),
    country varchar(50) default 'India',
    classid int,
    PRIMARY KEY (studentid) 
);

Insert Value to table

Insert Into class table

SQL
insert into class (classnumber, classname) 
values (100,'1-A');
insert into class (classnumber, classname) 
values (101,'2-A');
insert into class (classnumber, classname) 
values (102,'3-A');
insert into class (classnumber, classname) 
values (103,'4-A');
insert into class (classnumber, classname) 
values (104,'5-A');

Table Will look like

Insert into student table

SQL
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (1001,'Naresh1','roy',1,'Delhi',default,1);
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (1002,'Naresh2','roy',2,'Delhi',default,1);
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (1003,'Naresh3','roy',3,'Delhi',default,1);
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (1004,'Naresh4','roy',1,'Delhi',default,5);
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (1005,'Naresh5','roy',2,'Delhi',default,5);
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (1006,'Naresh6','roy',3,'Delhi',default,5);
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (1007,'Naresh7','roy',4,'Delhi',default,5);
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (1008,'Naresh8','roy',1,'Delhi',default,3);
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (1009,'Naresh9','roy',2,'Delhi',default,3);
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (10010,'Naresh0','roy',3,'Delhi',default,3);
insert into student (studentnumber, firstname, lastname, rollno, address, country, classid) 
values (10011,'Naresh10','roy',4,'Delhi',default,4);

Table will look like

INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables.

SQL
SELECT student.studentnumber, student.firstname, class.classname
FROM class
INNER JOIN student ON student.classid = class.classid;

Output

LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all the rows from the left table and the matched rows from the right table. If there’s no match, NULL values are returned for columns from the right table.

SQL
SELECT student.studentnumber, student.firstname, student.lastname, class.classname, class.classnumber
FROM student
LEFT JOIN class ON student.classid = class.classid;

Right Join (or RIGHT OUTER JOIN)

A RIGHT JOIN is similar to a LEFT JOIN but it returns all the rows from the right table and the matched rows from the left table. Rest Value if not available in left table will be shown as null.

SQL
SELECT student.studentnumber, student.firstname, student.lastname, class.classname, class.classnumber
FROM student
right JOIN class ON student.classid = class.classid;

Output

Full Outer Join

In MySQL Workbench

SQL
SELECT student.studentnumber, student.firstname, student.lastname, class.classname, class.classnumber
FROM student
LEFT JOIN class ON student.classid = class.classid
UNION
SELECT student.studentnumber, student.firstname, student.lastname, class.classname, class.classnumber
FROM student
right JOIN class ON student.classid = class.classid;

In SQL Server

SQL
SELECT student.studentnumber, student.firstname, student.lastname, class.classname, class.classnumber
FROM student
FULL OUTER JOIN class ON student.classid = class.classid;

Output

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.