Complete SQL Guide: From Beginner to SQL

Developer & Automation Engineer

Overview
This guide covers every SQL topic you need to land roles as a SQL Developer, Automation
Engineer, Data Analyst, Database Administrator, or BI Developer. Topics are organized from
absolute basics to advanced professional skills with real-world applications.

Module 1: Absolute Basics (Foundation)

1.1 What is SQL?

SQL (Structured Query Language) — language used to communicate with relational
databases
DBMS (Database Management System) — software to store, retrieve, manage data (e.g.,
MySQL, PostgreSQL, SQL Server, Oracle)
RDBMS (Relational DBMS) — stores data in tables (rows & columns) with relationships
SQL vs NoSQL — SQL is structured/tabular; NoSQL (MongoDB) is document-
based/unstructured

1.2 Types of SQL Commands

Type Full Form Commands Purpose

DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE Define/modify structure

DML Data Manipulation Language INSERT, UPDATE, DELETE Modify data

DQL Data Query Language SELECT Retrieve data

DCL Data Control Language GRANT, REVOKE Permissions

TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT Manage transactions

1.3 Data Types

Category Examples
Numeric INT, BIGINT, FLOAT, DECIMAL, NUMERIC

String/Text VARCHAR, CHAR, TEXT, NVARCHAR

Date/Time DATE, TIME, DATETIME, TIMESTAMP

 Category Examples

Boolean BOOLEAN (TRUE/FALSE)

Binary/JSON BLOB, JSON, XML

1.4 Database Objects

Table — stores data in rows and columns
View — virtual table based on a query
Schema — logical container/namespace for database objects
Index — improves query performance
Stored Procedure — reusable block of SQL code
Trigger — auto-executes SQL on an event (INSERT/UPDATE/DELETE)

Module 2: Basic SQL Queries

2.1 SELECT Statement

SELECT column1, column2 FROM table_name;

SELECT * FROM employees;
SELECT DISTINCT department FROM employees;

2.2 WHERE Clause & Filtering

SELECT * FROM employees WHERE salary > 50000;

SELECT * FROM employees WHERE department = 'HR';

2.3 Comparison & Logical Operators

-- Comparison: =, <>, !=, >, <, >=, <=

-- Logical: AND, OR, NOT
SELECT * FROM employees WHERE salary > 40000 AND department = 'IT';

-- BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 30000 AND 60000;

-- IN
SELECT * FROM employees WHERE department IN ('HR', 'IT', 'Finance');

-- LIKE (pattern matching)

SELECT * FROM employees WHERE name LIKE 'A%'; -- starts with A
SELECT * FROM employees WHERE email LIKE '%@[Link]';
2.4 NULL Handling

SELECT * FROM employees WHERE manager_id IS NULL;

SELECT * FROM employees WHERE manager_id IS NOT NULL;
SELECT COALESCE(phone, 'N/A') FROM employees; -- replace NULL with 'N/A'

2.5 ORDER BY, LIMIT, OFFSET

SELECT * FROM employees ORDER BY salary DESC;

SELECT * FROM employees ORDER BY department ASC, salary DESC;
SELECT * FROM employees LIMIT 10; -- top 10 rows
SELECT * FROM employees LIMIT 10 OFFSET 20; -- pagination

2.6 Aliases

SELECT first_name AS "First Name", salary AS "Monthly Salary" FROM employees;

SELECT [Link], d.department_name FROM employees e JOIN departments d ON e.dept_id = d

Module 3: DDL — Creating & Managing Tables

3.1 CREATE TABLE

CREATE TABLE employees (

emp_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10,2) DEFAULT 0.00,
hire_date DATE,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

3.2 ALTER TABLE

ALTER TABLE employees ADD COLUMN phone VARCHAR(15);

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees RENAME COLUMN old_name TO new_name;
3.3 DROP & TRUNCATE

DROP TABLE employees; -- permanently deletes table + data

TRUNCATE TABLE employees; -- deletes all data, keeps structure
DROP DATABASE company_db;

Module 4: DML — Inserting, Updating, Deleting Data

4.1 INSERT

INSERT INTO employees (first_name, last_name, email, salary)

VALUES ('Ravi', 'Kumar', 'ravi@[Link]', 55000);

-- Insert multiple rows

INSERT INTO employees (first_name, salary) VALUES
('Priya', 45000),
('Amit', 60000),
('Sneha', 50000);

4.2 UPDATE

UPDATE employees SET salary = 65000 WHERE emp_id = 101;

UPDATE employees SET salary = salary * 1.10 WHERE department = 'IT';

4.3 DELETE

DELETE FROM employees WHERE emp_id = 101;

DELETE FROM employees WHERE department = 'Temp';

⚠️Always use WHERE with UPDATE and DELETE to avoid modifying all rows
accidentally.

Module 5: Aggregate Functions & Grouping

5.1 Aggregate Functions

SELECT COUNT(*) FROM employees;

SELECT COUNT(DISTINCT department) FROM employees;
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary), MIN(salary) FROM employees;
5.2 GROUP BY

SELECT department, COUNT(*) AS total_employees, AVG(salary) AS avg_salary

FROM employees
GROUP BY department;

5.3 HAVING (Filter after GROUP BY)

SELECT department, AVG(salary) AS avg_salary

FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Note: WHERE filters rows before grouping; HAVING filters groups after aggregation.

5.4 ROLLUP & CUBE (Advanced Grouping)

SELECT department, job_role, SUM(salary)

FROM employees
GROUP BY ROLLUP(department, job_role); -- subtotals + grand total

GROUP BY CUBE(department, job_role); -- all possible combinations

Module 6: String, Date & Mathematical Functions

6.1 String Functions

SELECT UPPER(name), LOWER(name) FROM employees;

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
SELECT LENGTH(name) FROM employees;
SELECT SUBSTRING(name, 1, 3) FROM employees; -- first 3 chars
SELECT TRIM(' Hello '); -- remove spaces
SELECT REPLACE(name, 'old', 'new') FROM employees;
SELECT LEFT(name, 5), RIGHT(name, 5) FROM employees;

6.2 Date & Time Functions

SELECT NOW(); -- current date and time

SELECT CURDATE(), CURTIME();
SELECT YEAR(hire_date), MONTH(hire_date), DAY(hire_date) FROM employees;
SELECT DATEDIFF('2025-12-31', '2025-01-01'); -- difference in days
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) FROM employees;
SELECT DATE_FORMAT(hire_date, '%d-%m-%Y') FROM employees;
6.3 Mathematical Functions

SELECT ROUND(salary, 2) FROM employees;

SELECT CEIL(4.2), FLOOR(4.9); -- 5, 4
SELECT ABS(-500), SQRT(144), POWER(2,10);
SELECT MOD(17, 5); -- remainder = 2

Module 7: JOINs — Combining Tables

Joins are the most critical SQL skill for any developer or analyst.

7.1 Types of Joins

-- INNER JOIN: only matching rows in both tables

SELECT [Link], d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- LEFT JOIN: all rows from left table + matching from right
SELECT [Link], d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- RIGHT JOIN: all rows from right + matching from left

SELECT [Link], d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- FULL OUTER JOIN: all rows from both tables

SELECT [Link], d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

-- SELF JOIN: join table with itself (e.g., manager hierarchy)

SELECT [Link] AS employee, [Link] AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- CROSS JOIN: every row of A × every row of B (cartesian product)

SELECT * FROM products CROSS JOIN colors;

7.2 Anti Join (rows with NO match)

-- LEFT ANTI JOIN: employees with no department

SELECT [Link] FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
Module 8: Subqueries

8.1 Basic Subquery

-- Employees earning above average salary

SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

8.2 IN / NOT IN with Subquery

SELECT name FROM employees

WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'Hyderabad');

8.3 EXISTS / NOT EXISTS

SELECT name FROM employees e

WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.emp_id = e.emp_id
);

8.4 Correlated Subquery

-- Get max salary per department for each employee's department

SELECT name, salary,
(SELECT MAX(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) AS max_de
FROM employees e1;

Module 9: Set Operations

-- UNION: combine results, remove duplicates

SELECT name FROM employees_2024
UNION
SELECT name FROM employees_2025;

-- UNION ALL: combine results, keep duplicates

SELECT name FROM employees_2024
UNION ALL
SELECT name FROM employees_2025;

-- INTERSECT: rows present in BOTH

SELECT emp_id FROM team_a
INTERSECT
SELECT emp_id FROM team_b;

-- EXCEPT / MINUS: rows in first but NOT in second

SELECT emp_id FROM all_employees
 EXCEPT
SELECT emp_id FROM resigned_employees;

Module 10: CASE WHEN (Conditional Logic)

SELECT name, salary,

CASE
WHEN salary >= 80000 THEN 'High'
WHEN salary BETWEEN 40000 AND 79999 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM employees;

-- In aggregation
SELECT
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees;

Module 11: Views & CTEs

11.1 Views (Virtual Tables)

-- Create a view
CREATE VIEW high_earners AS
SELECT name, salary, department FROM employees WHERE salary > 70000;

-- Use the view

SELECT * FROM high_earners;

-- Drop view
DROP VIEW high_earners;

11.2 CTEs — Common Table Expressions

-- Basic CTE
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
)
SELECT [Link], [Link], d.avg_sal
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE [Link] > d.avg_sal;
11.3 Recursive CTE (Hierarchy)

-- Employee-Manager hierarchy
WITH RECURSIVE emp_hierarchy AS (
SELECT emp_id, name, manager_id, 1 AS level
FROM employees WHERE manager_id IS NULL

UNION ALL

SELECT e.emp_id, [Link], e.manager_id, [Link] + 1

FROM employees e
JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM emp_hierarchy ORDER BY level;

Module 12: Window Functions (Advanced Analytical SQL)

Window functions are essential for SQL Developer and Data roles.

12.1 ROW_NUMBER, RANK, DENSE_RANK

SELECT name, salary, department,

ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_nu
FROM employees;

Difference: RANK() skips numbers on ties; DENSE_RANK() doesn't.

12.2 LAG & LEAD (Compare Adjacent Rows)

SELECT name, salary,

LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary,
LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

12.3 FIRST_VALUE, LAST_VALUE

SELECT name, salary,

FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS top_sa
FROM employees;
12.4 NTILE, PERCENT_RANK

SELECT name, salary,

NTILE(4) OVER (ORDER BY salary DESC) AS quartile, -- split into 4 groups
PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
FROM employees;

12.5 Running Totals & Moving Averages

SELECT name, salary,

SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT
AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS
FROM employees;

Module 13: Constraints & Keys

13.1 Types of Constraints

CREATE TABLE orders (

order_id INT PRIMARY KEY, -- unique identifier
customer_id INT NOT NULL, -- cannot be null
product VARCHAR(50) NOT NULL,
amount DECIMAL CHECK (amount > 0), -- value validation
status VARCHAR(20) DEFAULT 'Pending', -- default value
email VARCHAR(100) UNIQUE, -- no duplicates
FOREIGN KEY (customer_id) REFERENCES customers(id) -- referential integrity
);

13.2 Key Types

Key Description
Primary Key Uniquely identifies each row; NOT NULL + UNIQUE

Foreign Key Links to Primary Key in another table

Unique Key All values must be unique; allows one NULL

Composite Key Primary key with multiple columns

Surrogate Key System-generated key (auto-increment)

Natural Key Real-world identifier (e.g., Aadhaar number)

Module 14: Indexing & Performance

14.1 What is an Index?

An index is a data structure that speeds up data retrieval. Like a book index — you jump directly
to the page instead of reading every page.

-- Create index
CREATE INDEX idx_salary ON employees(salary);
CREATE INDEX idx_dept_salary ON employees(dept_id, salary); -- composite

-- Unique index
CREATE UNIQUE INDEX idx_email ON employees(email);

-- Drop index
DROP INDEX idx_salary ON employees;

14.2 Index Types

Type Description

Clustered Physically sorts table data; one per table

Non-Clustered Separate structure with pointer to data

Composite Index on multiple columns

Covering Index Includes all queried columns

Filtered Index Index on a subset of rows

14.3 Query Performance Tips

Use EXPLAIN / EXPLAIN ANALYZE to view execution plans
Avoid SELECT * — select only needed columns
Avoid functions on indexed columns in WHERE clause
Use EXISTS instead of IN for large subqueries
Avoid LIKE '%value' (leading wildcard kills index)
Use indexed columns in JOIN ON conditions
Limit use of DISTINCT and ORDER BY on large tables

Module 15: Transactions & ACID Properties

15.1 ACID Properties
Property Meaning
Atomicity All operations succeed or all are rolled back

Consistency Database always remains in a valid state

Isolation Transactions don't interfere with each other

Durability Committed changes are permanent

15.2 Transaction Commands

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE acc_id = 101;

UPDATE accounts SET balance = balance + 5000 WHERE acc_id = 202;

COMMIT; -- save both changes

-- If error:
ROLLBACK; -- undo everything

-- Partial rollback
SAVEPOINT sp1;
-- ... more queries ...
ROLLBACK TO SAVEPOINT sp1;

15.3 Isolation Levels

Level Dirty Read Non-Repeatable Read Phantom Read

READ UNCOMMITTED Yes Yes Yes

READ COMMITTED No Yes Yes

REPEATABLE READ No No Yes

SERIALIZABLE No No No

Module 16: Stored Procedures, Functions & Triggers

16.1 Stored Procedures

DELIMITER $$
CREATE PROCEDURE get_employees_by_dept(IN dept_name VARCHAR(50))
BEGIN
SELECT * FROM employees WHERE department = dept_name;
END $$
DELIMITER ;
 -- Call it
CALL get_employees_by_dept('IT');

16.2 Functions

DELIMITER $$
CREATE FUNCTION calculate_bonus(salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE bonus DECIMAL(10,2);
SET bonus = salary * 0.15;
RETURN bonus;
END $$
DELIMITER ;

-- Use it
SELECT name, salary, calculate_bonus(salary) AS bonus FROM employees;

16.3 Triggers

-- Auto-log salary changes

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_at)
VALUES (OLD.emp_id, [Link], [Link], NOW());
END;

Module 17: Normalization & Database Design

17.1 Normal Forms

Form Rule
1NF No repeating groups; each cell has atomic value

2NF 1NF + no partial dependency on composite key

3NF 2NF + no transitive dependency

BCNF 3NF + every determinant is a candidate key

4NF No multi-valued dependencies

5NF No join dependencies

17.2 ER Diagrams
Entity — a real-world object (Employee, Product, Order)
Attribute — property of an entity (name, salary, ID)
Relationship — association between entities (Employee works in Department)
Cardinality — 1:1, 1:N, M:N

17.3 Star vs Snowflake Schema (Data Warehousing)

Schema Structure Performance Storage

Star Fact table + denormalized dimensions Faster queries More storage

Snowflake Fact table + normalized dimensions Slower (more joins) Less storage

Module 18: Security & User Management

-- Create user
CREATE USER 'analyst'@'localhost' IDENTIFIED BY 'SecurePass123';

-- Grant permissions
GRANT SELECT, INSERT ON company_db.employees TO 'analyst'@'localhost';
GRANT ALL PRIVILEGES ON company_db.* TO 'admin'@'localhost';

-- Revoke permissions
REVOKE INSERT ON company_db.employees FROM 'analyst'@'localhost';

-- Show grants
SHOW GRANTS FOR 'analyst'@'localhost';

-- Create role
CREATE ROLE data_reader;
GRANT SELECT ON company_db.* TO data_reader;
GRANT data_reader TO 'analyst'@'localhost';

-- Row-Level Security (SQL Server example)

CREATE SECURITY POLICY dept_filter
ADD FILTER PREDICATE dbo.fn_dept_filter(dept_id) ON [Link];

Module 19: Advanced Analytical SQL

19.1 PIVOT & UNPIVOT

-- PIVOT: rows to columns (SQL Server)

SELECT * FROM (
SELECT department, salary FROM employees
) AS src
PIVOT (
 AVG(salary) FOR department IN ([HR], [IT], [Finance])
) AS pvt;

19.2 Time Series Analysis

-- Monthly revenue trend

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
SUM(amount) AS monthly_revenue,
SUM(SUM(amount)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')) AS cumulativ
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

19.3 Cohort Analysis

WITH first_purchase AS (
SELECT customer_id, MIN(order_date) AS cohort_date FROM orders GROUP BY customer_
),
cohort_data AS (
SELECT o.customer_id,
PERIOD_DIFF(DATE_FORMAT(o.order_date,'%Y%m'), DATE_FORMAT(fp.cohort_date,
FROM orders o
JOIN first_purchase fp ON o.customer_id = fp.customer_id
)
SELECT month_number, COUNT(DISTINCT customer_id) AS customers FROM cohort_data GROUP

19.4 Percentiles & Distribution

SELECT
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY salary) AS median_salary,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS p25,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS p75
FROM employees;

Module 20: SQL for Automation Engineering

This module is specifically for Automation Engineers who use SQL in test automation and
CI/CD pipelines.

20.1 SQL in Test Automation

Automation engineers use SQL to:
Validate database state after application actions
Set up test data before running tests
Clean up test data after tests complete
Verify data integrity during regression testing
20.2 Data Validation Queries

-- Verify record was inserted

SELECT COUNT(*) FROM orders WHERE order_id = 12345 AND status = 'Confirmed';

-- Check no duplicate entries

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;

-- Validate referential integrity

SELECT o.order_id FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL; -- orphan records = data issue

20.3 Test Data Setup & Teardown

-- Setup: insert test data

INSERT INTO test_users (user_id, username, email, status)
VALUES (9999, 'test_user_auto', 'auto@[Link]', 'Active');

-- Teardown: cleanup
DELETE FROM test_users WHERE user_id = 9999;

20.4 SQL with Python (PyMySQL / SQLAlchemy)

import pymysql
import pandas as pd
from sqlalchemy import create_engine

# Connect using SQLAlchemy

engine = create_engine('mysql+pymysql://user:password@localhost/company_db')

# Run query and get as DataFrame

df = pd.read_sql("SELECT * FROM employees WHERE salary > 50000", engine)
print([Link]())

# Write DataFrame to DB
df.to_sql('high_earners_backup', engine, if_exists='replace', index=False)

20.5 SQL in Python for Automation (pytest + DB validation)

import pymysql
import pytest

@[Link]
def db_connection():
conn = [Link](host='localhost', user='root', password='pass', database=
yield conn
[Link]()

def test_user_created_in_db(db_connection):
 cursor = db_connection.cursor()
[Link]("SELECT COUNT(*) FROM users WHERE email = 'newuser@[Link]'")
count = [Link]()[0]
assert count == 1, "User should exist in DB after registration"

20.6 SQL with Power BI

Use Power Query (M language) to import SQL data
Write native SQL queries in Power BI data source settings
Use DirectQuery mode for live data
Create calculated columns and measures with DAX after SQL import

Module 21: Platform-Specific SQL

21.1 MySQL Specific

SHOW DATABASES;
SHOW TABLES;
DESCRIBE employees;
SHOW INDEX FROM employees;

21.2 SQL Server (T-SQL) Specific

-- Temp tables
SELECT * INTO #temp_employees FROM employees WHERE department = 'IT';

-- Table variables
DECLARE @emp_table TABLE (emp_id INT, name VARCHAR(50));

-- TOP clause
SELECT TOP 10 * FROM employees ORDER BY salary DESC;

-- TRY-CATCH error handling

BEGIN TRY
UPDATE employees SET salary = -100 WHERE emp_id = 1;
END TRY
BEGIN CATCH
PRINT 'Error: ' + ERROR_MESSAGE();
ROLLBACK;
END CATCH;
21.3 PostgreSQL Specific

-- Array data type

SELECT ARRAY['HR', 'IT', 'Finance'];

-- JSON operations
SELECT data->>'name' FROM json_table;

-- Generate series
SELECT generate_series(1, 10) AS num;

-- Window function with FILTER

SELECT dept_id, COUNT(*) FILTER (WHERE salary > 50000) AS high_earners
FROM employees GROUP BY dept_id;

Module 22: SQL for Data Warehousing & ETL

22.1 ETL Concepts with SQL

Extract — pull data from source (SELECT queries, linked servers)
Transform — clean, join, aggregate (CASE, JOINs, functions)
Load — write to target (INSERT INTO ... SELECT)

-- ETL: load transformed data into warehouse

INSERT INTO dw_employee_facts (emp_id, full_name, dept_name, salary, hire_year)
SELECT
e.emp_id,
CONCAT(e.first_name, ' ', e.last_name),
d.department_name,
[Link],
YEAR(e.hire_date)
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

22.2 Slowly Changing Dimensions (SCD)

-- SCD Type 2: track historical changes

UPDATE dim_employee
SET end_date = CURDATE(), is_current = 0
WHERE emp_id = 101 AND is_current = 1;

INSERT INTO dim_employee (emp_id, name, salary, start_date, end_date, is_current)

VALUES (101, 'Ravi Kumar', 70000, CURDATE(), '9999-12-31', 1);
Module 23: SQL Career Path & Certifications

Jobs That Require Strong SQL Skills

Role SQL Skills Needed

SQL Developer Advanced queries, stored procs, optimization

Database Administrator (DBA) Security, backup, performance, indexing

Data Analyst Aggregations, joins, window functions

Automation Engineer DB validation, Python+SQL integration

BI Developer Complex queries, Power BI, data modeling

Data Engineer ETL, data warehousing, big data SQL

Backend Developer Schema design, ORM, transactions

Recommended Certifications
Certification Platform Focus
Microsoft DP-900 Azure SQL + Data Fundamentals

Microsoft DP-300 Azure Database Administrator

Oracle Database SQL Oracle Core SQL

MySQL 8.0 Developer Oracle/MySQL MySQL Specific

PostgreSQL Associate EDB PostgreSQL

Google Data Analytics Google/Coursera SQL + Analytics

Learning Resources
Resource Type URL

W3Schools SQL Free Tutorial [Link]/sql

GeeksforGeeks SQL Free + Practice [Link]/sql-tutorial

LeetCode SQL Interview Practice [Link]

HackerRank SQL Challenges [Link]/domains/sql

SQLZoo Interactive [Link]

Mode Analytics Business SQL [Link]/sql-tutorial

Module 24: SQL Interview Questions (Top 50)

Basic Level
1. What is the difference between DELETE, TRUNCATE, and DROP?
2. What is a PRIMARY KEY vs UNIQUE KEY?
3. What is a NULL value? How is it different from 0 or empty string?
4. What is the difference between WHERE and HAVING?
5. What are aggregate functions? Give examples.
6. What is a JOIN? Explain all types.
7. What is the difference between UNION and UNION ALL?
8. What is a VIEW? When would you use it?
9. What is normalization? Explain 1NF, 2NF, 3NF.
10. What is a FOREIGN KEY?

Intermediate Level
11. What is a subquery? What are correlated subqueries?
12. Explain INNER JOIN vs LEFT JOIN with example.
13. How do you find duplicate records in a table?
14. How do you get the second highest salary?
15. What is a CTE? How is it different from a subquery?
16. Write a query to get employees hired in the last 6 months.
17. What is COALESCE? When to use it?
18. Explain GROUP BY with ROLLUP.
19. What is a self-join? Give a real-world example.
20. How do you pivot rows into columns in SQL?

Advanced Level
21. What are Window Functions? Explain RANK() vs DENSE_RANK().
22. What is the difference between ROW_NUMBER(), RANK(), DENSE_RANK()?
23. Explain LAG() and LEAD() with use cases.
24. What are ACID properties? Explain each.
25. What are isolation levels? What is dirty read?
26. What is a deadlock? How to avoid it?
27. What is an index? Explain clustered vs non-clustered.
28. How does EXPLAIN help in query optimization?
29. What is a recursive CTE? When to use it?
30. What is the difference between a stored procedure and a function?

Automation/Developer Focus
31. How do you validate database data in test automation?
32. How to connect Python to a MySQL database?
33. What is SQLAlchemy? How is it used?
34. How do you use SQL in Power BI?
35. How do you write a trigger? Give an example.
36. What is a stored procedure with IN/OUT parameters?
37. How do you handle errors in T-SQL (SQL Server)?
38. What are temp tables vs table variables in SQL Server?
39. What is SCD Type 2 in data warehousing?
40. How do you write an ETL process using SQL?

Practical Coding Questions

41. Find the top 3 salaries per department.
42. Find employees who have no manager.
43. Get the running total of sales per month.
44. Find customers who placed orders in both 2024 and 2025.
45. Write a query to identify gaps in a sequence of IDs.
46. Get the employee with the highest salary in each department.
47. Calculate month-over-month growth percentage.
48. Find all pairs of employees in the same department.
49. Write a query to transpose/pivot monthly data.
50. Find the nth highest salary without using LIMIT/TOP.

Module 25: 30-Day SQL Study Plan

Week Topics Daily Practice
Week 1 Basics: SELECT, WHERE, ORDER BY, Functions W3Schools + HackerRank Easy

Week 2 JOINs, Subqueries, Aggregations, GROUP BY GeeksforGeeks + LeetCode Easy

Week 3 Window Functions, CTEs, Transactions, Indexes LeetCode Medium

Week 4 Stored Procedures, Triggers, Design, Projects Real projects + Interview Prep
This document covers all SQL topics needed for SQL Developer, Automation Engineer, Data Analyst, BI
Developer, and Database Administrator roles. Practice daily on LeetCode and HackerRank for best
results.