SQL Complete Guide

Beginner to Advanced
Master Database Management with SQL

TABLE OF CONTENTS
1. Module 1: SQL Basics and Setup
2. Module 2: SELECT Statements
3. Module 3: WHERE Clause and Operators
4. Module 4: INSERT, UPDATE, DELETE
5. Module 5: Data Types and Constraints
6. Module 6: JOIN Operations
7. Module 7: Aggregate Functions
8. Module 8: GROUP BY and HAVING
9. Module 9: Subqueries
10. Module 10: Indexes and Performance
11. Module 11: Views and Stored Procedures
12. Module 12: Transactions and ACID
13. Module 13: Window Functions
14. Module 14: Advanced Queries
15. Module 15: Database Design and Normalization

MODULE 1: SQL BASICS AND SETUP

1.1 What is SQL?
SQL (Structured Query Language) is the standard language for managing relational
databases. It allows you to:
Retrieve data (SELECT)
Add data (INSERT)
Modify data (UPDATE)
Delete data (DELETE)
Create and manage database structures

Why SQL for Analytics?

✓ Industry standard for data analysis
✓ Works with all major databases (MySQL, PostgreSQL, SQL Server, Oracle)
✓ Essential for data analyst and data scientist roles
✓ Used in pharma, healthcare, finance industries
✓ High-demand skill with excellent career prospects
1.2 SQL Database Systems
Popular SQL Databases:
MySQL - Open source, free, widely used
PostgreSQL - Advanced open source, excellent for complex queries
SQL Server - Microsoft product, powerful enterprise solution
Oracle - Premium enterprise database
SQLite - Lightweight, perfect for learning and small projects

1.3 Installation (MySQL Example)

Windows:

1. Download MySQL from: [Link]

2. Run installer
3. Choose Setup Type (Developer Default recommended)
4. Configure MySQL Server (Port 3306, standard)
5. MySQL Workbench will install for GUI access
Verify Installation:
Open command prompt and type:
mysql --version

1.4 Basic Terminology

Database - Collection of related tables
Table - Organized data in rows and columns
Row/Record - Single entry in a table
Column/Field - Attribute of a table
Primary Key - Unique identifier for each row
Foreign Key - Link between tables
Schema - Structure of database (table definitions)

MODULE 2: SELECT STATEMENTS

2.1 Basic SELECT
-- Select all columns from a table
SELECT * FROM employees;
-- Select specific columns
SELECT first_name, last_name, salary FROM employees;
-- Select with alias
SELECT first_name AS name, salary AS annual_salary FROM employees;
2.2 SELECT with WHERE
-- Get employees with salary > 50000
SELECT * FROM employees WHERE salary > 50000;
-- Get employees from specific department
SELECT * FROM employees WHERE department = 'Sales';
-- Multiple conditions
SELECT * FROM employees
WHERE department = 'IT' AND salary > 60000;

2.3 DISTINCT and LIMIT

-- Get unique values
SELECT DISTINCT department FROM employees;
-- Limit number of results
SELECT * FROM employees LIMIT 10; -- First 10 rows
-- Offset and limit (pagination)
SELECT * FROM employees LIMIT 10 OFFSET 20; -- Rows 21-30

2.4 ORDER BY
-- Sort ascending (default)
SELECT * FROM employees ORDER BY salary;

-- Sort descending
SELECT * FROM employees ORDER BY salary DESC;
-- Sort by multiple columns
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

MODULE 3: WHERE CLAUSE AND OPERATORS

3.1 Comparison Operators
-- Equal
SELECT * FROM employees WHERE salary = 50000;
-- Not equal
SELECT * FROM employees WHERE salary != 50000;
-- or
SELECT * FROM employees WHERE salary <> 50000;
-- Greater than, Less than
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE salary <= 80000;

-- Between
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;
-- IN list
SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Sales');

3.2 String Operators

-- LIKE with wildcards
SELECT * FROM employees WHERE first_name LIKE 'John%'; -- Starts with John
SELECT * FROM employees WHERE email LIKE '%@[Link]'; -- Ends with @[Link]
SELECT * FROM employees WHERE name LIKE '%an%'; -- Contains 'an'
-- NOT LIKE
SELECT * FROM employees WHERE department NOT LIKE '%Sales%';

-- Case insensitive search

SELECT * FROM employees WHERE first_name LIKE 'john%';

3.3 NULL Handling

-- Find NULL values
SELECT * FROM employees WHERE phone_number IS NULL;
-- Find non-NULL values
SELECT * FROM employees WHERE phone_number IS NOT NULL;

-- Coalesce (replace NULL with default)

SELECT first_name, COALESCE(phone_number, 'N/A') AS phone
FROM employees;

3.4 Logical Operators

-- AND: both conditions must be true
SELECT * FROM employees
WHERE salary > 50000 AND department = 'IT';
-- OR: at least one condition true
SELECT * FROM employees
WHERE department = 'IT' OR department = 'HR';
-- NOT: negate condition
SELECT * FROM employees
WHERE NOT department = 'Sales';

MODULE 4: INSERT, UPDATE, DELETE

4.1 INSERT Statement
-- Insert single row
INSERT INTO employees (first_name, last_name, email, salary, department)
VALUES ('John', 'Smith', 'john@[Link]', 65000, 'IT');
-- Insert multiple rows
INSERT INTO employees (first_name, last_name, salary, department)
VALUES
('Sarah', 'Johnson', 72000, 'Sales'),
('Mike', 'Davis', 58000, 'HR'),
('Lisa', 'Brown', 81000, 'IT');
-- Insert with default values
INSERT INTO employees (first_name, last_name)
VALUES ('James', 'Wilson'); -- Other columns use defaults

4.2 UPDATE Statement

-- Update single column
UPDATE employees
SET salary = 75000
WHERE first_name = 'John';

-- Update multiple columns

UPDATE employees
SET salary = 75000, department = 'Management'
WHERE employee_id = 5;
-- Update with calculation
UPDATE employees
SET salary = salary * 1.1 -- 10% raise
WHERE department = 'IT';
-- Update all rows (be careful!)
UPDATE employees SET status = 'active';

4.3 DELETE Statement

-- Delete specific rows
DELETE FROM employees
WHERE employee_id = 5;
-- Delete with condition
DELETE FROM employees
WHERE department = 'Temp' AND hire_date < '2020-01-01';
-- Delete all rows (careful!)
DELETE FROM employees;

-- Safer way: use transactions

BEGIN TRANSACTION;
DELETE FROM employees WHERE department = 'Sales';
ROLLBACK; -- Undo if needed

MODULE 5: DATA TYPES AND CONSTRAINTS

5.1 Data Types
-- Numeric types
INT, BIGINT -- Whole numbers
DECIMAL(10,2), FLOAT -- Decimal numbers
-- String types
VARCHAR(50) -- Variable length string (max 50 chars)
CHAR(10) -- Fixed length string
TEXT -- Large text
-- Date/Time types
DATE -- 2024-01-20
TIME -- 15:30:45
DATETIME -- 2024-01-20 15:30:45
TIMESTAMP -- Automatic timestamp

-- Boolean type
BOOLEAN, BOOL -- TRUE or FALSE

5.2 CREATE 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,
salary DECIMAL(10,2),
department VARCHAR(50),
hire_date DATE DEFAULT CURDATE(),
is_active BOOLEAN DEFAULT TRUE,
phone_number VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

5.3 Constraints
-- Primary Key: Unique identifier
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
...
);
-- UNIQUE: No duplicate values
CREATE TABLE employees (
email VARCHAR(100) UNIQUE,
...
);

-- NOT NULL: Column required

CREATE TABLE employees (
first_name VARCHAR(50) NOT NULL,
...
);
-- DEFAULT: Default value
CREATE TABLE employees (
hire_date DATE DEFAULT CURDATE(),
...
);

-- FOREIGN KEY: Link to another table

CREATE TABLE projects (
project_id INT PRIMARY KEY,
employee_id INT,
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
-- CHECK: Validate data
CREATE TABLE employees (
salary DECIMAL(10,2),
CHECK (salary > 0)
);

MODULE 6: JOIN OPERATIONS

6.1 INNER JOIN
-- Return rows that match in both tables
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
-- Equivalent to:
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

6.2 LEFT JOIN (LEFT OUTER JOIN)

-- Return all rows from left table + matching rows from right table
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Use case: Get all employees, even those without department assignment

6.3 RIGHT JOIN (RIGHT OUTER JOIN)

-- Return all rows from right table + matching rows from left table
SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
-- Use case: Get all departments, even those without employees

6.4 FULL OUTER JOIN

-- Return all rows from both tables
SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
-- Note: Not supported in MySQL; use UNION instead
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

6.5 CROSS JOIN

-- Cartesian product: every row from table1 with every row from table2
SELECT e.first_name, p.project_name
FROM employees e
CROSS JOIN projects p;
-- Result: 100 employees × 5 projects = 500 rows

6.6 Self Join

-- Join table to itself
SELECT e1.first_name AS employee, e2.first_name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

MODULE 7: AGGREGATE FUNCTIONS

7.1 Aggregate Functions
-- COUNT: Count rows
SELECT COUNT(*) FROM employees; -- Total employees
SELECT COUNT(DISTINCT department) FROM employees; -- Unique departments
-- SUM: Sum values
SELECT SUM(salary) FROM employees; -- Total payroll
SELECT SUM(salary) FROM employees WHERE department = 'IT';
-- AVG: Average
SELECT AVG(salary) FROM employees; -- Average salary
SELECT AVG(salary) FROM employees WHERE department = 'Sales';

-- MIN/MAX: Minimum and maximum

SELECT MIN(salary) AS lowest_salary, MAX(salary) AS highest_salary
FROM employees;
-- ROUND: Round decimal
SELECT ROUND(AVG(salary), 2) FROM employees;

7.2 String Functions

-- CONCAT: Combine strings
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
-- UPPER/LOWER: Case conversion
SELECT UPPER(first_name) FROM employees;
SELECT LOWER(email) FROM employees;

-- LENGTH: String length

SELECT first_name, LENGTH(first_name) FROM employees;
-- SUBSTRING: Extract part of string
SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username
FROM employees;
-- REPLACE: Replace text
SELECT REPLACE(phone_number, '-', '') FROM employees;

7.3 Date Functions

-- CURDATE: Current date
SELECT CURDATE(); -- 2024-01-20
-- DATEDIFF: Days between dates
SELECT DATEDIFF(CURDATE(), hire_date) AS days_employed
FROM employees;
-- YEAR/MONTH/DAY: Extract parts
SELECT YEAR(hire_date) FROM employees;
SELECT MONTH(hire_date) FROM employees;

-- DATE_ADD/DATE_SUB: Add/subtract dates

SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY); -- 30 days from today
SELECT DATE_SUB(hire_date, INTERVAL 1 YEAR); -- One year before hire date
-- DATE_FORMAT: Format date
SELECT DATE_FORMAT(hire_date, '%Y-%m-%d') FROM employees;

MODULE 8: GROUP BY AND HAVING

8.1 GROUP BY
-- Group by department and count
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
-- Group by multiple columns
SELECT department, job_title, COUNT(*) AS count
FROM employees
GROUP BY department, job_title;
-- Group with aggregate functions
SELECT department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department;

8.2 HAVING Clause

-- HAVING filters groups (WHERE filters rows)
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- Find departments with more than 5 employees

SELECT department, COUNT() AS emp_count
FROM employees
GROUP BY department
HAVING COUNT() > 5;
-- Complex HAVING
SELECT job_title, AVG(salary) AS avg_salary
FROM employees
GROUP BY job_title
HAVING AVG(salary) > 50000 AND COUNT(*) >= 3;

8.3 GROUP BY with WHERE

-- WHERE filters before grouping, HAVING filters after
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01' -- Filter rows first
GROUP BY department
HAVING AVG(salary) > 50000; -- Filter groups second

MODULE 9: SUBQUERIES
9.1 Subqueries in SELECT
-- Find salary compared to average
SELECT first_name, salary,
(SELECT AVG(salary) FROM employees) AS avg_salary,
salary - (SELECT AVG(salary) FROM employees) AS difference
FROM employees;
9.2 Subqueries in WHERE
-- Find employees earning more than average
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Find employees in highest-paying departments
SELECT * FROM employees
WHERE department IN (
SELECT department FROM employees
GROUP BY department
HAVING AVG(salary) > 70000
);

9.3 Subqueries in FROM

-- Create derived table
SELECT department, avg_sal FROM (
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
) AS dept_salaries
WHERE avg_sal > 60000;

9.4 EXISTS Operator

-- Find employees who have assigned projects
SELECT * FROM employees e
WHERE EXISTS (
SELECT 1 FROM projects p
WHERE p.employee_id = e.employee_id
);

-- Find employees with no projects

SELECT * FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM projects p
WHERE p.employee_id = e.employee_id
);

MODULE 10: INDEXES AND PERFORMANCE

10.1 Creating Indexes
-- Single column index
CREATE INDEX idx_email ON employees(email);

-- Composite index (multiple columns)

CREATE INDEX idx_dept_salary ON employees(department, salary);
-- Unique index
CREATE UNIQUE INDEX idx_email_unique ON employees(email);
-- View indexes
SHOW INDEX FROM employees;
-- Drop index
DROP INDEX idx_email ON employees;

10.2 Query Optimization

-- Good: Use index
SELECT * FROM employees WHERE email = 'john@[Link]';

-- Bad: Function prevents index use

SELECT * FROM employees WHERE LOWER(email) = 'john@[Link]';
-- Good: Use LIMIT
SELECT * FROM employees LIMIT 100;
-- Bad: Select unnecessary columns
SELECT * FROM employees; -- Instead use specific columns

-- Good: Specific columns

SELECT first_name, last_name, salary FROM employees;

10.3 EXPLAIN Statement

-- Analyze query performance
EXPLAIN SELECT * FROM employees WHERE department = 'IT';
-- Look for:
-- - rows: How many rows examined
-- - possible_keys: Indexes that could be used
-- - key: Index actually used
-- - type: How rows are accessed (const, ref, range, etc.)

MODULE 11: VIEWS AND STORED PROCEDURES

11.1 Creating Views
-- Create a view (virtual table)
CREATE VIEW high_earners AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 70000;
-- Use the view
SELECT * FROM high_earners;

-- View with JOIN

CREATE VIEW employee_departments AS
SELECT e.first_name, e.last_name, d.department_name, [Link]
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- Drop view
DROP VIEW high_earners;

11.2 Stored Procedures

-- Create procedure
DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept_name VARCHAR(50))
BEGIN
SELECT * FROM employees
WHERE department = dept_name;
END //
DELIMITER ;
-- Call procedure
CALL GetEmployeesByDept('IT');

-- Procedure with output parameter

DELIMITER //
CREATE PROCEDURE GetTotalSalary(OUT total DECIMAL(10,2))
BEGIN
SELECT SUM(salary) INTO total FROM employees;
END //
DELIMITER ;
-- Get output
CALL GetTotalSalary(@total);
SELECT @total;

MODULE 12: TRANSACTIONS AND ACID

12.1 ACID Properties
Atomicity - All or nothing (complete or rollback)
Consistency - Data stays valid
Isolation - Concurrent transactions don't interfere
Durability - Committed data persists

12.2 Transaction Control

-- Start transaction
START TRANSACTION;
-- Multiple operations
INSERT INTO employees (first_name, salary) VALUES ('John', 50000);
UPDATE employees SET salary = 60000 WHERE first_name = 'John';

-- Commit (save all changes)

COMMIT;
-- Or rollback (undo all changes)
ROLLBACK;
-- Savepoint: partial rollback
START TRANSACTION;
INSERT INTO employees VALUES (...);
SAVEPOINT sp1;
INSERT INTO employees VALUES (...);
ROLLBACK TO sp1; -- Undo second insert only
COMMIT;

MODULE 13: WINDOW FUNCTIONS

13.1 ROW_NUMBER, RANK, DENSE_RANK
-- ROW_NUMBER: Sequential numbering
SELECT employee_id, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;
-- RANK: Same value gets same rank, next rank skips
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
-- DENSE_RANK: No rank skipping
SELECT employee_id, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

13.2 Running Totals and Averages

-- Running total of salary
SELECT employee_id, salary,
SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM employees;
-- Average salary per department
SELECT employee_id, salary, department,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
-- Difference from department average
SELECT employee_id, salary, department,
salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

MODULE 14: ADVANCED QUERIES

14.1 UNION and UNION ALL
-- Combine results (remove duplicates)
SELECT first_name FROM employees WHERE department = 'IT'
UNION
SELECT first_name FROM employees WHERE salary > 70000;
-- Combine results (keep duplicates)
SELECT first_name FROM employees WHERE department = 'IT'
UNION ALL
SELECT first_name FROM employees WHERE salary > 70000;

14.2 CASE Expression

-- Conditional logic in SELECT
SELECT first_name, salary,
CASE
WHEN salary > 80000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM employees;

-- CASE with aggregate

SELECT department,
SUM(CASE WHEN salary > 60000 THEN 1 ELSE 0 END) AS high_earners
FROM employees
GROUP BY department;

14.3 Common Table Expressions (CTE)

-- WITH clause for readability
WITH high_earners AS (
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 70000
)
SELECT * FROM high_earners
ORDER BY salary DESC;
-- Recursive CTE
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;
MODULE 15: DATABASE DESIGN AND NORMALIZATION
15.1 Normalization Levels
First Normal Form (1NF):
All columns contain atomic values (no lists)
Second Normal Form (2NF):

Meets 1NF and all non-key columns depend on entire primary key
Third Normal Form (3NF):
Meets 2NF and all non-key columns depend only on primary key

15.2 Database Design Example

-- Good design (normalized)
-- Employees table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
-- Departments table
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL,
budget DECIMAL(10,2)
);
-- Projects table
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Employee Projects (junction table)

CREATE TABLE employee_projects (
employee_id INT,
project_id INT,
hours_allocated INT,
PRIMARY KEY (employee_id, project_id),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
FOREIGN KEY (project_id) REFERENCES projects(project_id)
);
KEY TAKEAWAYS
✓ Understand data relationships - Use proper JOINs
✓ Filter efficiently - Use WHERE before aggregation
✓ Use indexes - Speed up large queries
✓ Follow normalization - Avoid data redundancy
✓ Test queries - Use LIMIT during development
✓ Write readable code - Use aliases and comments
✓ Backup data - Before UPDATE/DELETE operations
✓ Understand ACID - Especially for data integrity

PRACTICE TIPS
Free Practice Platforms:
LeetCode Database (SQL challenges)
HackerRank SQL Challenges
Codewars SQL Katas
SQLZoo (interactive tutorials)
Mode Analytics SQL Tutorial

Real Dataset Practice:

[Link] (download datasets)
Use your own data
Practice with pharma datasets (your interest)
Pharma-Specific Practice Queries:
-- Sample queries for pharma domain
SELECT * FROM medicines WHERE expiry_date < CURDATE();
SELECT supplier_id, COUNT(*) FROM medicines GROUP BY supplier_id;
SELECT * FROM medicines WHERE inventory_qty < reorder_level;

Master SQL for a successful data analytics career! 📊