Module 5: SQL Fundamentals

Structured Query Language (SQL) is the standardized declarative programming language designed for managing relational databases. SQL statements are categorized into five primary sublanguages based on operational intent.

Crucial Interview Contrast: DROP vs TRUNCATE vs DELETE

Feature	DELETE	TRUNCATE	DROP
Sublanguage	DML	DDL	DDL
WHERE clause?	Yes (filters rows)	No (clears whole table)	No (destroys object)
Table Schema?	Preserved	Preserved	Completely removed
Performance	Slow (logs each row delete)	Fast (deallocates data pages)	Instant (drops catalog entry)
Rollbackable?	Yes (inside transaction)	DBMS dependent (Auto-commits in MySQL)	Cannot rollback in MySQL/Oracle

Writing SQL queries requires understanding both the lexical order (the order you type the clauses) and the logical execution order (the internal order in which the database query engine executes them).

Common SQL Clauses & Patterns

-- Pattern 1: Pattern matching with LIKE
SELECT emp_name, salary 
FROM Employees 
WHERE emp_name LIKE 'S%t'; -- Starts with 'S' and ends with 't'

-- Pattern 2: Range & Set membership
SELECT emp_name, department_id 
FROM Employees 
WHERE salary BETWEEN 50000 AND 90000 
  AND department_id IN (10, 20, 30);

-- Pattern 3: NULL safety
-- NEVER use '= NULL'; always use IS NULL or IS NOT NULL!
SELECT emp_name 
FROM Employees 
WHERE manager_id IS NULL;

Aggregate functions perform a calculation across a set of rows and return a single summary scalar value. When combined with the GROUP BY clause, they calculate summaries across partitioned subsets of records.

Core Aggregate Functions

• COUNT(*): Counts total rows in the group, including rows containing NULL values.
• COUNT(column_name): Counts non-null values in the specified column.
• SUM(column_name): Returns the arithmetic sum of numeric values (ignores NULLs).
• AVG(column_name): Calculates the arithmetic mean (sum / count of non-nulls).
• MIN(col) / MAX(col): Returns minimum / maximum value (works with numbers, dates, strings).

Critical Interview Distinction: WHERE vs HAVING

Feature	WHERE Clause	HAVING Clause
Evaluation Point	Before rows are grouped (Step 2)	After groups are formed (Step 4)
Filtering Scope	Individual raw rows	Aggregated row groups
Aggregate Functions Permitted?	NO (e.g. WHERE AVG(sal) > 5k is INVALID)	YES (e.g. HAVING AVG(sal) > 5k is VALID)

-- Find departments with > 5 active employees earning an average salary > 60,000:
SELECT department_id, 
       COUNT(*) AS total_employees, 
       AVG(salary) AS avg_salary
FROM Employees
WHERE status = 'ACTIVE'                -- Row filter before grouping
GROUP BY department_id                -- Aggregate by department
HAVING COUNT(*) > 5                   -- Group filter after aggregation
   AND AVG(salary) > 60000
ORDER BY avg_salary DESC;