SQL Quick Revision Guide

by tarun reddy
Table of Contents
Day Topic

1 SELECT STATEMENT
2 WHERE CLAUSE
3 DISTINCT
4 ORDER BY
5 LIMIT
6 OFFSET
7 AND, OR, NOT OPERATORS
8 IN OPERATOR
9 BETWEEN OPERATOR
10 LIKE OPERATOR
11 IS NULL / IS NOT NULL
12 GROUP BY
13 HAVING CLAUSE
14 COUNT FUNCTION
15 SUM FUNCTION
16 AVG FUNCTION
17 MIN AND MAX
18 CASE WHEN LOGIC
19 COALESCE AND NULLIF
20 STRING FUNCTIONS
21 DATE FUNCTIONS
22 JOINS – INNER, LEFT, RIGHT, FULL
23 SELF JOIN
24 UNION AND UNION ALL
25 SUB QUERIES
26 COMMON TABLE EXPRESSIONS – CTEs
27 VIEWS
28 INDEXING
29 TEMPORARY TABLES
30 CASE SENSITIVITY IN SQL
31 NULL HANDLING IN JOINS
32 RECURSIVE CTEs
33 AGGREGATE WITH JOINS
34 EXISTS VS IN
35 DEALING WITH DUPLICATES
36 SQL BEST PRACTICES
1: SELECT Statement
• Concept
Retrieve specific columns from a table.

• Use Case
SELECT Statement is commonly used in real-time data filtering, reporting, and search operations.

• Example Query
SELECT name, department FROM employees;

• Explanation
The query above demonstrates how to apply the SQL concept in a real-world scenario.

• Input Table
id name department
1 Alice HR
2 Bob Sales
3 Charlie IT

• Expected Output
name department
Alice HR
Bob Sales
Charlie IT

2: WHERE Clause
• Concept
Filter rows based on a condition.

• Use Case
WHERE Clause is commonly used in real-time data filtering, reporting, and search operations.

• Example Query
SELECT * FROM employees WHERE department = 'Sales';

• Explanation
The query above demonstrates how to apply the SQL concept in a real-world scenario.

• Input Table
id name department
1 Alice HR
2 Bob Sales
3 Charlie IT

• Expected Output
 id name department
2 Bob Sales

3: DISTINCT Keyword
• Concept
Get unique values from a column.

• Use Case
DISTINCT Keyword is commonly used in real-time data filtering, reporting, and search operations.

• Example Query
SELECT DISTINCT department FROM employees;

• Explanation
The query above demonstrates how to apply the SQL concept in a real-world scenario.

• Input Table
id name department
1 Alice HR
2 Bob Sales
3 Charlie HR

• Expected Output
department
HR
Sales

4: ORDER BY Clause
• Concept
Sort the result set.

• Use Case
ORDER BY Clause is commonly used in real-time data filtering, reporting, and search operations.

• Example Query
SELECT * FROM employees ORDER BY name ASC;

• Explanation
The query above demonstrates how to apply the SQL concept in a real-world scenario.

• Input Table
id name department
1 Charlie IT
2 Alice HR
 3 Bob Sales

• Expected Output
id name department
2 Alice HR
3 Bob Sales
1 Charlie IT

5: LIMIT Clause
• Concept
Limit the number of rows returned.

• Use Case
LIMIT Clause is commonly used in real-time data filtering, reporting, and search operations.

• Example Query
SELECT * FROM employees LIMIT 2;

• Explanation
The query above demonstrates how to apply the SQL concept in a real-world scenario.

• Input Table
id name department
1 Alice HR
2 Bob Sales
3 Charlie IT

• Expected Output
id name department
1 Alice HR
2 Bob Sales

6: OFFSET Clause
• Concept
Skip a number of rows in the result.

• Use Case
OFFSET Clause is commonly used in real-time data filtering, reporting, and search operations.

• Example Query
SELECT * FROM employees LIMIT 2 OFFSET 1;

• Explanation
The query above demonstrates how to apply the SQL concept in a real-world scenario.
• Input Table
id name department
1 Alice HR
2 Bob Sales
3 Charlie IT

• Expected Output
id name department
2 Bob Sales
3 Charlie IT

7: AND/OR Operators
• Concept
Combine multiple conditions.

• Use Case
AND/OR Operators is commonly used in real-time data filtering, reporting, and search operations.

• Example Query
SELECT * FROM employees WHERE department = 'Sales' OR department = 'HR';

• Explanation
The query above demonstrates how to apply the SQL concept in a real-world scenario.

• Input Table
id name department
1 Alice HR
2 Bob Sales
3 Charlie IT

• Expected Output
id name department
1 Alice HR
2 Bob Sales

8: IN Operator
• Concept
Check if a value exists in a list.

• Use Case
IN Operator is commonly used in real-time data filtering, reporting, and search operations.

• Example Query
SELECT * FROM employees WHERE department IN ('Sales', 'IT');

• Explanation
The query above demonstrates how to apply the SQL concept in a real-world scenario.

• Input Table
id name department
1 Alice HR
2 Bob Sales
3 Charlie IT

• Expected Output
id name department
2 Bob Sales
3 Charlie IT

9: BETWEEN Operator
• Concept
Filter values within a range.

• Use Case
BETWEEN Operator is commonly used in real-time data filtering, reporting, and search operations.

• Example Query
SELECT * FROM salaries WHERE amount BETWEEN 4000 AND 6000;

• Explanation
The query above demonstrates how to apply the SQL concept in a real-world scenario.

• Input Table
id amount
1 3500
2 4500
3 6000

• Expected Output
id amount
2 4500
3 6000

10: LIKE Operator

• Concept
Search for a pattern in a column.
• Use Case
LIKE Operator is commonly used in real-time data filtering, reporting, and search operations.

• Example Query
SELECT * FROM employees WHERE name LIKE 'A%';

• Explanation
The query above demonstrates how to apply the SQL concept in a real-world scenario.

• Input Table
id name department

1 Alice HR

2 Bob Sales

3 Angela IT

• Expected Output
id name department

1 Alice HR

3 Angela IT
11: IS NULL / IS NOT NULL
• Concept
This topic covers how to use IS NULL / IS NOT NULL in SQL to derive meaningful insights from structured data.

• Use Case
IS NULL / IS NOT NULL is typically used in business intelligence, dashboards, and analytics for summarizing or transforming
data.

• Example Query
SELECT * FROM employees WHERE department IS NULL;

• Explanation
This query shows how to apply the concept in real analysis to extract required values or apply transformations.

• Input Table
id name department
1 Alice HR
2 Bob
3 Charlie Sales

• Expected Output
id name department
2 Bob

12: GROUP BY
• Concept
This topic covers how to use GROUP BY in SQL to derive meaningful insights from structured data.

• Use Case
GROUP BY is typically used in business intelligence, dashboards, and analytics for summarizing or transforming data.

• Example Query
SELECT department, COUNT(*) FROM employees GROUP BY department;

• Explanation
This query shows how to apply the concept in real analysis to extract required values or apply transformations.

• Input Table
id name department
1 Alice HR
2 Bob HR
3 Charlie IT

• Expected Output
department COUNT(*)
 HR 2
IT 1

13: HAVING Clause

• Concept
This topic covers how to use HAVING Clause in SQL to derive meaningful insights from structured data.

• Use Case
HAVING Clause is typically used in business intelligence, dashboards, and analytics for summarizing or transforming data.

• Example Query
SELECT department, COUNT(*) as cnt FROM employees GROUP BY department HAVING cnt > 1;

• Explanation
This query shows how to apply the concept in real analysis to extract required values or apply transformations.

• Input Table
id name department
1 Alice Sales
2 Bob Sales
3 Charlie IT

• Expected Output
department cnt
Sales 2

14: COUNT Function

• Concept
This topic covers how to use COUNT Function in SQL to derive meaningful insights from structured data.

• Use Case
COUNT Function is typically used in business intelligence, dashboards, and analytics for summarizing or transforming data.

• Example Query
SELECT COUNT(*) FROM employees WHERE department = 'IT';

• Explanation
This query shows how to apply the concept in real analysis to extract required values or apply transformations.

• Input Table
id name department
1 Alice IT
2 Bob HR
3 Charlie IT
• Expected Output
COUNT(*)
2

15: SUM Function

• Concept
This topic covers how to use SUM Function in SQL to derive meaningful insights from structured data.

• Use Case
SUM Function is typically used in business intelligence, dashboards, and analytics for summarizing or transforming data.

• Example Query
SELECT SUM(salary) FROM employees;

• Explanation
This query shows how to apply the concept in real analysis to extract required values or apply transformations.

• Input Table
id name salary
1 Alice 5000
2 Bob 6000
3 Charlie 7000

• Expected Output
SUM(salary)
18000

16: AVG Function

• Concept
This topic covers how to use AVG Function in SQL to derive meaningful insights from structured data.

• Use Case
AVG Function is typically used in business intelligence, dashboards, and analytics for summarizing or transforming data.

• Example Query
SELECT AVG(salary) FROM employees;

• Explanation
This query shows how to apply the concept in real analysis to extract required values or apply transformations.

• Input Table
 id name salary
1 Alice 4000
2 Bob 6000
3 Charlie 8000

• Expected Output
AVG(salary)
6000

17: MIN and MAX

• Concept
This topic covers how to use MIN and MAX in SQL to derive meaningful insights from structured data.

• Use Case
MIN and MAX is typically used in business intelligence, dashboards, and analytics for summarizing or transforming data.

• Example Query
SELECT MIN(salary), MAX(salary) FROM employees;

• Explanation
This query shows how to apply the concept in real analysis to extract required values or apply transformations.

• Input Table
id name salary
1 Alice 3500
2 Bob 4500
3 Charlie 2500

• Expected Output
MIN(salary) MAX(salary)
2500 4500

18: CASE WHEN Logic

• Concept
This topic covers how to use CASE WHEN Logic in SQL to derive meaningful insights from structured data.

• Use Case
CASE WHEN Logic is typically used in business intelligence, dashboards, and analytics for summarizing or transforming data.

• Example Query
SELECT name, salary, CASE WHEN salary > 5000 THEN 'High' ELSE 'Low' END AS status FROM
employees;
• Explanation
This query shows how to apply the concept in real analysis to extract required values or apply transformations.

• Input Table
id name salary
1 Alice 4000
2 Bob 6000
3 Charlie 5000

• Expected Output
name salary status
Alice 4000 Low
Bob 6000 High
Charlie 5000 Low

19: IFNULL / COALESCE

• Concept
This topic covers how to use IFNULL / COALESCE in SQL to derive meaningful insights from structured data.

• Use Case
IFNULL / COALESCE is typically used in business intelligence, dashboards, and analytics for summarizing or transforming data.

• Example Query
SELECT name, COALESCE(department, 'NA') FROM employees;

• Explanation
This query shows how to apply the concept in real analysis to extract required values or apply transformations.

• Input Table
id name department
1 Alice
2 Bob IT
3 Charlie

• Expected Output
name COALESCE(department, 'NA')
Alice NA
Bob IT
Charlie NA
20: String Functions
• Concept
String functions are used to manipulate string/text data in SQL, including changing case, trimming spaces, and concatenating
strings.

• Use Case
They are commonly used in formatting output, cleaning data, and preparing data for display in reports or for further analysis.

• Example Queries

UPPER Example:
SELECT UPPER(name) AS upper_name FROM employees;

Explanation
This query demonstrates the use of UPPER function to manipulate string data.

Input Table
id name first_name last_name
1 Alice Alice Smith
2 BOB Bob Jones
3 charlie Charlie Brown

Expected Output
upper_name
ALICE
BOB
CHARLIE

LOWER Example:
SELECT LOWER(name) AS lower_name FROM employees;

Explanation
This query demonstrates the use of LOWER function to manipulate string data.

Input Table
id name first_name last_name
1 Alice Alice Smith
2 BOB Bob Jones
3 charlie Charlie Brown

Expected Output
lower_name
alice
bob
charlie
TRIM Example:
SELECT TRIM(name) AS trimmed_name FROM employees;

Explanation
This query demonstrates the use of TRIM function to manipulate string data.

Input Table
id name first_name last_name
1 Alice Alice Smith
2 BOB Bob Jones
3 charlie Charlie Brown

Expected Output
trimmed_name
Alice
BOB
charlie

CONCAT Example:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Explanation
This query demonstrates the use of CONCAT function to manipulate string data.

Input Table
id name first_name last_name
1 Alice Alice Smith
2 BOB Bob Jones
3 charlie Charlie Brown

Expected Output
full_name
Alice Smith
Bob Jones
Charlie Brown
21: Date Functions
• Concept
Date functions in SQL help manipulate and extract components from date/time fields.
• Use Case
Used in reporting systems to filter, group, or display data based on date logic.
• Input Table
order_id order_date
101 2023-05-01
102 2023-05-15
103 2023-06-01
• Example Query
SELECT order_id, MONTH(order_date) AS order_month FROM orders;

• Output Table
order_id order_month
101 5
102 5
103 6
• Explanation
The MONTH function extracts the month from a date column.

22: Joins (INNER, LEFT, RIGHT, FULL)

• Concept
Joins combine rows from two or more tables based on related columns.
• Use Case
Used to merge data from multiple sources such as customer and orders.
• Input Table 1
customer_id customer_name
1 Alice
2 Bob
3 Charlie

• Input Table 2
order_id customer_id
101 1
102 2
103 4
• Example Query
SELECT c.customer_name, o.order_id FROM customers c LEFT JOIN orders o ON
c.customer_id = o.customer_id;

• Output Table
 customer_name order_id
Alice 101
Bob 102
Charlie

• Explanation
LEFT JOIN returns all records from the left table and matched records from the right.

23: Self Join

• Concept
A self join joins a table with itself to compare rows within the same table.
• Use Case
Used in hierarchical structures like employee-manager relationships.
• Input Table
emp_id emp_name manager_id
1 Alice
2 Bob 1
3 Charlie 1
• Example Query
SELECT e.emp_name, m.emp_name AS manager_name FROM employees e LEFT JOIN employees m
ON e.manager_id = m.emp_id;

• Output Table
emp_name manager_name
Alice
Bob Alice
Charlie Alice
• Explanation
The table is joined with itself to retrieve manager names using employee-manager relationships.

24: UNION and UNION ALL

• Concept
UNION combines results from multiple queries, removing duplicates. UNION ALL includes duplicates.
• Use Case
Used to combine rows from different regions or time periods.
• Input Table 1
name
Alice
Bob

• Input Table 2
name
 Bob
David
• Example Query
SELECT name FROM table1 UNION SELECT name FROM table2;

• Output Table
name
Alice
Bob
David
• Explanation
UNION removes duplicate rows while combining data.

25: Subqueries
• Concept
Subqueries are queries nested inside another query to filter or compute derived results.
• Use Case
Used to compare values to a derived maximum, average, etc.
• Input Table
emp_id emp_name salary
1 Alice 5000
2 Bob 7000
3 Charlie 6000
• Example Query
SELECT emp_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

• Output Table
emp_name
Bob
• Explanation
This query returns employees with salaries above the average.

26: Common Table Expressions (CTEs)

• Concept
CTEs are temporary result sets used to simplify complex queries.
• Use Case
Used for breaking down complex queries into readable parts.
• Input Table
id score
1 80
2 90
3 85
• Example Query
WITH top_scores AS (SELECT * FROM scores WHERE score > 85) SELECT * FROM top_scores;

• Output Table
id score
2 90
• Explanation
The CTE selects high scores which are then used in the main query.

27: Views
• Concept
Views are virtual tables defined by SQL queries to simplify access to complex queries.
• Use Case
Used to encapsulate complex joins or filters for reuse.
• Example Query
CREATE VIEW high_earners AS SELECT emp_name FROM employees WHERE salary > 6000;

• Explanation
Defines a virtual table to display employees with salary above 6000.

28: Indexing
• Concept
Indexes are used to speed up the retrieval of rows by using pointers.
• Use Case
Used for performance optimization in large datasets.
• Example Query
CREATE INDEX idx_emp_name ON employees(emp_name);

• Explanation
Creates an index on the emp_name column to improve query performance.
29: Temporary Tables
• Concept
Temporary tables are used to store intermediate results temporarily during a session.
• Use Case
Used in ETL pipelines to hold transient data or pre-process before final insert.
• Example Query
CREATE TEMPORARY TABLE temp_orders AS SELECT * FROM orders WHERE order_date >=
'2023-01-01';

• Explanation
Creates a temporary table from orders placed after Jan 1, 2023. It exists only during the session.

30: Case Sensitivity in SQL

• Concept
SQL keywords are generally case-insensitive, but string comparisons can be case-sensitive depending on the
database.
• Use Case
Important for filtering or joining data that includes case variations in text.
• Input Table
id name
1 Alice
2 alice
3 ALICE
• Example Query
SELECT * FROM users WHERE name = 'Alice';

• Output Table
id name
1 Alice
• Explanation
Depending on collation, this may return only the exact case match.

31: NULL Handling in Joins

• Concept
NULLs can cause rows to be excluded in joins if not handled properly.
• Use Case
Important for datasets where foreign keys might be missing.
• Input Table 1
emp_id name
1 John
2 Jane
3 Mike
• Input Table 2
emp_id dept
1 HR
Finance
• Example Query
SELECT e.name, d.dept FROM employees e LEFT JOIN departments d ON e.emp_id =
d.emp_id;

• Output Table
name dept
John HR
Jane
Mike
• Explanation
Rows with NULL keys in the joined table won't match unless explicitly checked.

32: Recursive CTEs

• Concept
Recursive CTEs repeatedly reference themselves to iterate through data hierarchies.
• Use Case
Used for traversing hierarchies like org charts or folder structures.
• Input Table
emp_id name manager_id
1 Alice
2 Bob 1
3 Charlie 2
• Example Query
WITH RECURSIVE hierarchy AS ( SELECT emp_id, name, manager_id FROM employees WHERE
manager_id IS NULL UNION ALL SELECT e.emp_id, e.name, e.manager_id FROM employees e
JOIN hierarchy h ON e.manager_id = h.emp_id ) SELECT * FROM hierarchy;

• Output Table
emp_id name manager_id
1 Alice
2 Bob 1
3 Charlie 2
• Explanation
Builds a hierarchy by recursively joining employee-manager chains.

33: Aggregate with JOINs

• Concept
Aggregate functions like COUNT, SUM can be combined with JOINs for grouped summaries.
• Use Case
Used for reporting total sales per customer or department.
• Input Table 1
cust_id cust_name
1 Tom
2 Jerry

• Input Table 2
order_id cust_id amount
101 1 200
102 1 300
103 2 150
• Example Query
SELECT c.cust_name, SUM(o.amount) AS total_amt FROM customers c JOIN orders o ON
c.cust_id = o.cust_id GROUP BY c.cust_name;

• Output Table
cust_name total_amt
Tom 500
Jerry 150
• Explanation
Groups orders by customer and calculates total amount spent.

34: EXISTS vs IN
• Concept
EXISTS checks for the existence of rows from a subquery, IN compares values from a list or subquery.
• Use Case
Used for filtering based on presence or absence in related datasets.
• Input Table 1
cust_id cust_name
1 A
2 B

• Input Table 2
order_id cust_id
101 1
• Example Query
SELECT * FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customers.cust_id =
orders.cust_id);

• Output Table
cust_id cust_name
1 A
• Explanation
EXISTS returns true if at least one matching row is found in the subquery.

35: Dealing with Duplicates

• Concept
Duplicates can be removed using DISTINCT or ROW_NUMBER with filtering.
• Use Case
Useful for cleaning data, especially during ingestion.
• Input Table
id name
1 Bob
2 Bob
3 Alice
• Example Query
SELECT DISTINCT name FROM people;

• Output Table
name
Bob
Alice
• Explanation
DISTINCT removes duplicate rows based on selected columns.

36: SQL Best Practices

• Concept
Best practices include using aliases, avoiding SELECT *, indexing, and avoiding NULL comparisons
without IS NULL.
• Use Case
Improve readability, performance, and maintainability.
• Example Query
SELECT c.name AS customer_name FROM customers c WHERE c.status IS NOT NULL;

• Explanation
Shows aliasing and proper NULL check. Avoid SELECT * for clarity.