SQL Fundamentals, Aggregation, Subqueries, Revision & Cheatsheet

1. Database Create Karna

Definition

CREATE DATABASE ka use naya database create karne ke liye hota hai. Database select karne ke liye USE database_name; execute karte hain.

Important Note

USE office; ka matlab hai ki ab aage bane wali saari tables office database ke andar hi hongi.

</> SQL

-- Create a new database
CREATE DATABASE office;

-- Select and switch to the database
USE office;

2. Table Structure & Constraints

Table store karta hai rows aur columns me data. Attributes Define karte hain column datatype aur constraints.

PRIMARY KEY

Uniquely identify karta hai har record ko. Duplicate aur NULL values strictly NOT allowed.

AUTO_INCREMENT

Automatically system unique number assign kar deta hai (1, 2, 3...) har nayi row ke liye.

FOREIGN KEY

Do tables ke beech me relationship establish karta hai parent-child relation me.

</> SQL

-- 1. Departments Table
CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL,
    location VARCHAR(50)
);

-- 2. Employees Table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10, 2),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

3. Data Retrieval (SELECT)

Table se data fetch karne ke liye SELECT statement use hota hai.

</> SQL

-- Select all columns from employees table
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name, salary FROM employees;

4. Column Aliases (AS)

AS keyword output column ka temporary user-friendly name badalne ke liye lagate hain.

</> SQL

SELECT 
    first_name AS Name, 
    salary AS Monthly_Pay 
FROM employees;

5. DISTINCT Keyword

DISTINCT duplicate values ko filter karke sirf unique records display karta hai.

</> SQL

-- Get unique department IDs
SELECT DISTINCT department_id FROM employees;

6. WHERE Clause & Comparison Operators

Data filtering ke liye WHERE condition add karta hai row-by-row level par.

</> SQL

-- Employees with salary greater than 60,000
SELECT * FROM employees 
WHERE salary > 60000;

7. Logical Operators (AND, OR, NOT)

AND Operator

Dono conditions True honi chahiye tabhi row fetch hogi.

OR Operator

Koi ek condition bhi True hogi to row include ho jayegi.

NOT Operator

Given condition ko reverse (negate) karta hai.

</> SQL

-- AND Example
SELECT * FROM employees 
WHERE department_id = 2 AND salary > 50000;

-- OR Example
SELECT * FROM employees 
WHERE department_id = 1 OR department_id = 3;

-- NOT Example
SELECT * FROM employees 
WHERE NOT department_id = 2;

8. IN, NOT IN & BETWEEN Operators

</> SQL

-- IN Operator
SELECT * FROM employees 
WHERE department_id IN (1, 2, 4);

-- BETWEEN Range
SELECT * FROM employees 
WHERE salary BETWEEN 40000 AND 80000;

9. LIKE Operator & Wildcard Characters

% : Zero, one, or multiple characters matching.
_ : Exactly one single character matching.

</> SQL

-- Name starts with 'A'
SELECT * FROM employees 
WHERE first_name LIKE 'A%';

-- Name has 'a' as second character
SELECT * FROM employees 
WHERE first_name LIKE '_a%';

10. ORDER BY (Sorting Results)

Data ko ascending (ASC) ya descending (DESC) order me arrange karne ke liye.

</> SQL

SELECT * FROM employees 
ORDER BY salary DESC;

11. LIMIT Clause

Output rows ki quantity strict constrain karne ke liye LIMIT use karein.

</> SQL

-- Top 5 highest paid employees
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 5;

12. NULL Values Handling

NULL is missing value. Check IS NULL or IS NOT NULL (never use = NULL).

</> SQL

SELECT * FROM employees 
WHERE department_id IS NULL;

13. UPDATE Data

Existing row values modify karne ke liye UPDATE table name SET column = value specify karte hain.

</> SQL

UPDATE employees 
SET salary = 75000 
WHERE employee_id = 3;

14. DELETE Data

Specific row ko remove karne ke liye DELETE FROM table with WHERE clause.

</> SQL

DELETE FROM employees 
WHERE employee_id = 5;

15. ALTER TABLE (Schema Modifications)

</> SQL

-- Add new column
ALTER TABLE employees ADD phone_number VARCHAR(15);

-- Drop column
ALTER TABLE employees DROP COLUMN phone_number;

16. TRUNCATE vs DROP vs DELETE

Feature	DELETE	TRUNCATE	DROP
Type	DML	DDL	DDL
WHERE Clause	Supported	Not Supported	Not Supported
Table Structure	Remains intact	Remains intact	Completely Removed
Speed	Slower (Logs each row)	Very Fast	Instantaneous

17. Aggregate Functions

</> SQL

SELECT 
    COUNT(*) AS total_employees,
    SUM(salary) AS total_payroll,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees;

18. GROUP BY Clause

Similar values wale rows ko groups me collapse karke summary metrics derive karne ke liye.

</> SQL

SELECT 
    department_id, 
    COUNT(*) AS employee_count, 
    AVG(salary) AS avg_dept_salary
FROM employees
GROUP BY department_id;

19. HAVING vs WHERE Clause

WHERE

Individual raw rows ko filter karta hai before grouping.

HAVING

Aggregated grouped results ko filter karta hai after GROUP BY.

</> SQL

SELECT department_id, AVG(salary) AS avg_sal
FROM employees
WHERE salary > 30000
GROUP BY department_id
HAVING AVG(salary) > 50000;

20. 🔥 CASE WHEN — Conditional Logic & Aggregation

</> SQL

-- Salary Categorization
SELECT 
    first_name,
    salary,
    CASE
        WHEN salary >= 80000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

-- Conditional Aggregation (CASE + COUNT)
SELECT 
    COUNT(CASE WHEN salary >= 80000 THEN 1 END) AS high_salary_employees
FROM employees;

21. 🧠 Data Analyst Interview Queries & Subqueries

Q1. Employees earning more than Average Salary

</> SQL

SELECT * FROM employees 
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

Q2. Second Highest Salary

</> SQL

SELECT MAX(salary) AS second_highest 
FROM employees 
WHERE salary < (
    SELECT MAX(salary) FROM employees
);

22. SQL JOINs Overview

A JOIN is used to combine data from two or more tables based on a related column between them.

</> SQL

-- INNER JOIN
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- LEFT JOIN
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

23. ⚡ Conditional Expressions (CASE, IF, COALESCE, NULLIF)

Conditional expressions allow you to perform evaluation logic and substitute values conditionally inside SQL queries.

Command	Description	Syntax	Example Query
CASE Statement	Allows multi-branch conditional evaluation within a query.	SELECT col1, CASE WHEN cond THEN val ELSE default END FROM tbl;	SELECT order_id, CASE WHEN total > 1000 THEN 'High' ELSE 'Low' END FROM orders;
IF() Function	Evaluates a condition and returns a value based on true or false boolean result.	SELECT IF(condition, true_val, false_val) AS alias FROM tbl;	SELECT name, age, IF(age > 50, 'Senior', 'Junior') AS emp_cat FROM employees;
COALESCE() Function	Returns the very first non-NULL value from a given list of arguments.	SELECT COALESCE(val1, val2, ...) AS alias FROM tbl;	SELECT COALESCE(first_name, middle_name, 'N/A') AS pref_name FROM employees;
NULLIF() Function	Returns NULL if two specified expressions are equal; otherwise returns expression1.	SELECT NULLIF(expr1, expr2) AS alias FROM tbl;	SELECT NULLIF(total_amount, discounted_amount) AS diff_amount FROM orders;

</> SQL

-- 1. CASE Statement Example
SELECT order_id, total_amount,
    CASE 
        WHEN total_amount > 1000 THEN 'High Value Order'
        WHEN total_amount > 500 THEN 'Medium Value Order'
        ELSE 'Low Value Order'
    END AS order_status
FROM orders;

-- 2. IF() Function Example
SELECT name, age,
    IF(age > 50, 'Senior', 'Junior') AS employee_category
FROM employees;

-- 3. COALESCE() Function Example
SELECT COALESCE(first_name, middle_name, 'No Name') AS preferred_name
FROM employees;

-- 4. NULLIF() Function Example
SELECT NULLIF(total_amount, discounted_amount) AS diff_amount
FROM orders;

24. 🔄 SQL Query Execution Order

Understanding the exact sequence in which SQL evaluates clauses is essential for writing efficient queries and debugging errors (e.g., why aliases created in SELECT cannot be filtered in WHERE).

1

FROM

Choose and join base tables to form the target dataset.

2

WHERE

Filters the base data rows based on conditions.

3

GROUP BY

Aggregates base data into grouped sets.

4

HAVING

Filters the aggregated grouped data.

5

SELECT

Returns the final output columns and evaluates aliases.

6

ORDER BY

Sorts the final result dataset (ASC or DESC).

7

LIMIT

Limits the returned dataset to a specified row count.

Key Rule to Remember for Interviews

Because WHERE executes at Step 2 while aggregate calculations happen at Step 3 (GROUP BY), you CANNOT use aggregate functions inside WHERE clauses. Use HAVING instead!

25. 🚀 Master SQL Revision Cheatsheet & DDL / DML Guide

A quick-reference guide for fast exam and interview revision summarizing DDL, DML, clauses, and essential queries.

1. DDL vs DML Classification

Data Definition Language (DDL)

Defines & alters database schemas.

CREATE : Database, Table, View, Index
ALTER : Add/Drop/Modify Columns
DROP : Delete Table/Database
TRUNCATE : Empty table fast

Data Manipulation Language (DML)

Manipulates stored records inside tables.

INSERT : Add new records
UPDATE : Modify existing records
DELETE : Remove specific rows
SELECT : Retrieve data

2. Quick SQL Syntax Revision Snippets

</> SQL

-- Filter rows with WHERE
SELECT * FROM tbl WHERE col > 5;

-- Limit output rows
SELECT col1, col2 FROM tbl LIMIT 10;

-- Multiple conditions (OR)
SELECT * FROM tbl WHERE col1 > 5 OR col2 < 2;

-- Order by column index
SELECT col1, col2 FROM tbl ORDER BY 1;

-- Row count, Sum & Max
SELECT COUNT(*), SUM(col1), MAX(col1) FROM tbl;

-- Grouped aggregation
SELECT AVG(col1) FROM tbl GROUP BY col2;

-- Aggregate filter with HAVING
SELECT col1, COUNT(*) AS total FROM tbl GROUP BY col1 HAVING COUNT(*) > 10;

-- CASE expression
SELECT col1,
    CASE 
        WHEN col1 > 10 THEN 'more than 10'
        WHEN col1 < 10 THEN 'less than 10'
        ELSE '10'
    END AS NewColumnName
FROM tbl;

SQL Fundamentals & Analytics

SQL Fundamentals, Aggregation, Subqueries & Analytics

1. Database Create Karna

Definition

Important Note

2. Table Structure & Constraints

PRIMARY KEY

AUTO_INCREMENT

FOREIGN KEY

3. Data Retrieval (SELECT)

4. Column Aliases (AS)

5. DISTINCT Keyword

6. WHERE Clause & Comparison Operators

7. Logical Operators (AND, OR, NOT)

AND Operator

OR Operator

NOT Operator

8. IN, NOT IN & BETWEEN Operators

9. LIKE Operator & Wildcard Characters

10. ORDER BY (Sorting Results)

11. LIMIT Clause

12. NULL Values Handling

13. UPDATE Data

14. DELETE Data

15. ALTER TABLE (Schema Modifications)

16. TRUNCATE vs DROP vs DELETE

17. Aggregate Functions

18. GROUP BY Clause

19. HAVING vs WHERE Clause

WHERE

HAVING

20. 🔥 CASE WHEN — Conditional Logic & Aggregation

21. 🧠 Data Analyst Interview Queries & Subqueries

Q1. Employees earning more than Average Salary

Q2. Second Highest Salary

22. SQL JOINs Overview

23. ⚡ Conditional Expressions (CASE, IF, COALESCE, NULLIF)

24. 🔄 SQL Query Execution Order

Key Rule to Remember for Interviews

25. 🚀 Master SQL Revision Cheatsheet & DDL / DML Guide

1. DDL vs DML Classification

Data Definition Language (DDL)

Data Manipulation Language (DML)

2. Quick SQL Syntax Revision Snippets