SQL Handbook - Interactive Guide

Introduction to Databases

What is a Database?

A Database is an organized collection of structured information or data, typically stored electronically in a computer system. It's a way to store data in a format that is easily accessible.

Example: Think of a library where books are organized by topic, author, and title – that's essentially what a database does with data.

Why Do We Need Databases?

To store, manage, and retrieve large amounts of data efficiently
To prevent data duplication and maintain data integrity
To allow multiple users to access and manipulate data simultaneously

What is SQL?

SQL (Structured Query Language) is the standard programming language used to communicate with and manipulate databases.

Common Operations (CRUD):

Operation	SQL Command	Description
Create	INSERT	Add new records
Read	SELECT	Retrieve data
Update	UPDATE	Modify existing data
Delete	DELETE	Remove records

Comparison with Excel

In Excel, a sheet is like a table in a database
Each row in the sheet is similar to a record (or entry) in a database table
Each column in Excel corresponds to a field (or attribute) in a table
Excel stores all data in one file, whereas a database can contain multiple related tables
In databases, you can define strict data types and rules, which Excel doesn't enforce
Unlike Excel, databases allow complex querying, relationships between tables, and secure multi-user access

Think of it this way: A database is a more powerful, structured, and scalable version of Excel for data management.

Relational vs Non-relational Databases

Relational databases store data in structured tables with predefined schemas and relationships between tables (e.g., MySQL, PostgreSQL). Non-relational databases (NoSQL) use flexible formats like documents, key-value pairs, or graphs, and don't require a fixed schema (e.g., MongoDB, Firebase).

Feature	Relational (SQL)	Non-relational (NoSQL)
Structure	Tables (rows & columns)	Documents, Key-Value
Language	SQL	Varies (Mongo Query, etc.)
Schema	Fixed schema	Flexible schema
Examples	MySQL, PostgreSQL	MongoDB, Firebase

What is DBMS?

A Database Management System (DBMS) is software that interacts with users, applications, and the database itself to capture and analyze data.

Examples: MySQL, PostgreSQL, Oracle Database, SQLite
Functions: Data storage, retrieval, security, backup, and recovery

What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses SQL.

Widely used in web development
High performance and reliability
Powers platforms like WordPress, Facebook (early days), and YouTube

Real-World Use Cases:

E-commerce websites to store customer orders and product listings
Banking systems to handle transactions securely
Social networks to manage user data, messages, and posts

Creating a Database in MySQL

Creating a Database

                    CREATE DATABASE database_name;

                    -- Example:
                    CREATE DATABASE student_db;
                

Tips:

Database names should be unique
Avoid using spaces or special characters
Use lowercase and underscores (_) for better readability (e.g., employee_records)

Viewing All Databases

SHOW DATABASES;

Switching to a Database

Before working with tables, you must select the database:

USE student_db;

Dropping a Database

⚠️ Warning: This action is irreversible! This will permanently delete all data and tables in the database.

                    DROP DATABASE database_name;

                    -- Example:
                    DROP DATABASE student_db;
                

Creating Tables in MySQL

Basic Syntax

                    CREATE TABLE table_name (
                    column1 datatype constraints,
                    column2 datatype constraints,
                    ...
                    );
                

Example: Creating a Students Table

                    CREATE TABLE students (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(100) NOT NULL DEFAULT 'No Name',
                    age INT,
                    email VARCHAR(100) UNIQUE,
                    admission_date DATE
                    );
                

Explanation:

id INT AUTO_INCREMENT PRIMARY KEY – A unique identifier for each student that auto-increments
name VARCHAR(100) NOT NULL – Name must be provided
age INT – Stores numeric values for age
email VARCHAR(100) UNIQUE – Each email must be different
admission_date DATE – Stores the date of admission

Commonly Used Data Types

Data Type	Description	Example Usage
INT	Whole numbers	age, quantity
VARCHAR(n)	Variable-length string	names, emails
TEXT	Long text strings	descriptions
DATE	Date values (YYYY-MM-DD)	birth dates
DATETIME	Date and time values	timestamps
BOOLEAN	TRUE or FALSE	flags

Common Constraints

Constraint	Description
PRIMARY KEY	Uniquely identifies each record
NOT NULL	Ensures the column cannot be left empty
UNIQUE	Ensures all values in a column are different
AUTO_INCREMENT	Automatically increases numeric values
DEFAULT	Sets a default value for the column
FOREIGN KEY	Enforces relationships between tables

Viewing Table Information

                    -- View all tables
                    SHOW TABLES;

                    -- View table structure
                    DESCRIBE students;

                    -- View table data
                    SELECT * FROM students;
                

Modifying Tables in MySQL

Renaming a Table

                    RENAME TABLE old_table_name TO
                    new_table_name;
                

Dropping a Table

⚠️ Warning: This permanently deletes the table and all its data!

DROP TABLE table_name;

Renaming a Column

                    ALTER TABLE table_name
                    RENAME COLUMN old_column_name TO
                    new_column_name;
                

Adding a Column

                    ALTER TABLE table_name
                    ADD COLUMN column_name datatype constraints;

                    -- Example:
                    ALTER TABLE students
                    ADD COLUMN gender VARCHAR(10);
                

Dropping a Column

                    ALTER TABLE table_name
                    DROP COLUMN column_name;
                

Modifying a Column

                    ALTER TABLE table_name
                    MODIFY COLUMN column_name new_datatype new_constraints;

                    -- Example:
                    ALTER TABLE students
                    MODIFY COLUMN name VARCHAR(150) NOT NULL;
                

Changing Column Order

                    ALTER TABLE table_name
                    MODIFY COLUMN column_name datatype AFTER
                    another_column_name;
                

💡 Tip: Always review changes on production databases carefully. Use DESCRIBE table_name to verify structure before and after modifications.

Inserting Data into Tables

Insert One Row

                    INSERT INTO student (id, name, age, grade, date_of_birth)
                    VALUES (1, 'Ayesha Khan', 16, '10th', '2007-05-15');
                

Insert Multiple Rows at Once

                    INSERT INTO student (id, name, age, grade) VALUES
                    (15, 'Ayesha Khan', 16, '10th'),
                    (25, 'Ravi Sharma', 17, '11th'),
                    (35, 'Meena Joshi', 15, '9th'),
                    (45, 'Arjun Verma', 18, '12th'),
                    (55, 'Sara Ali', 16, '10th');
                

Verify Inserted Records

SELECT * FROM student;

💡 Tip: Inserting multiple rows at once is more efficient than inserting them one by one.

Selecting Data from Tables

Basic SELECT Statement

                    -- Retrieve all data
                    SELECT * FROM student;

                    -- Retrieve specific columns
                    SELECT name, grade FROM student;
                

Using WHERE Clause

                    -- Students in 10th grade
                    SELECT * FROM student
                    WHERE grade = '10th';

                    -- Students older than 16
                    SELECT * FROM student
                    WHERE age > 16;
                

Comparison Operators

Operator	Description	Example
=	Equals	WHERE age = 16
!= or <>	Not equal to	WHERE grade != '12th'
>	Greater than	WHERE age > 16
<< /td>	Less than	WHERE age < 17
>=	Greater than or equal	WHERE age >= 16
<=< /td>	Less than or equal	WHERE age <= 18
BETWEEN	Within a range (inclusive)	WHERE age BETWEEN 15 AND 17
IN	Matches any in a list	WHERE grade IN ('10th', '12th')
NOT IN	Excludes list items	WHERE grade NOT IN ('9th', '11th')
LIKE	Pattern matching	WHERE name LIKE 'A%'

Handling NULL Values

Common Mistake: WHERE grade = NULL will NOT work!

                    -- Correct way to check for NULL
                    SELECT * FROM student
                    WHERE grade IS NULL;

                    -- Check for NOT NULL
                    SELECT * FROM student
                    WHERE grade IS NOT NULL;
                

Combining Conditions

                    -- Using AND
                    SELECT * FROM student
                    WHERE grade = '10th' AND age > 16;

                    -- Using OR
                    SELECT * FROM student
                    WHERE grade = '9th' OR grade = '12th';

                    -- Complex conditions
                    SELECT * FROM student
                    WHERE (grade = '10th' OR grade = '11th') AND age >= 16;
                

Sorting Results with ORDER BY

                    -- Sort by age (ascending)
                    SELECT * FROM student
                    ORDER BY age ASC;

                    -- Sort by name (descending)
                    SELECT * FROM student
                    ORDER BY name DESC;
                

Limiting Results with LIMIT

                    -- Get only 5 rows
                    SELECT * FROM student
                    LIMIT 5;

                    -- Get 5 rows starting from the 3rd (offset 2)
                    SELECT * FROM student
                    LIMIT 2, 5;
                

Using Wildcards with LIKE

Wildcards:

% – Matches zero or more characters
_ – Matches exactly one character

                    -- Names starting with 'A'
                    SELECT * FROM students
                    WHERE name LIKE 'A%';

                    -- Dates with day = 05 (any year, any month)
                    SELECT * FROM attendance
                    WHERE date LIKE '____-__-05';

                    -- Any day in May 2025
                    WHERE date LIKE '2025-05-%';

                    -- 1st January of any year
                    WHERE date LIKE '____-01-01';
                

Updating Data in Tables

Basic Syntax

                    UPDATE table_name
                    SET column1 = value1, column2 = value2
                    WHERE condition;
                

Update a Single Row

                    UPDATE student
                    SET grade = '12th'
                    WHERE id = 2;
                

Update Multiple Columns

                    UPDATE student
                    SET age = 17, grade = '10th'
                    WHERE id = 3;
                

Update All Rows

⚠️ Warning: This will modify every row in the table!

                    UPDATE student
                    SET age = 18;
                

Conditional Updates

                    -- Promote all students in 9th grade
                    UPDATE student
                    SET grade = '10th'
                    WHERE grade = '9th';

                    -- Increase age by 1 for students younger than 18
                    UPDATE student
                    SET age = age + 1
                    WHERE age < 18; 

Update Using IS NULL

                            UPDATE student
                            SET grade = 'Unknown'
                            WHERE grade IS NULL;
                        

Verify the Update

SELECT * FROM student;

Deleting Data from Tables

Basic Syntax

                        DELETE FROM table_name
                        WHERE condition;
                    

⚠️ Important: If you omit the WHERE clause, all rows in the table will be deleted!

Delete a Specific Row

                        DELETE FROM student
                        WHERE id = 2;
                    

Delete Based on Conditions

                        -- Delete all students in 9th grade
                        DELETE FROM student
                        WHERE grade = '9th';

                        -- Delete students younger than 16
                        DELETE FROM student
                        WHERE age < 16; -- Delete students with no
                            grade assigned
                            DELETE FROM student
                            WHERE grade IS NULL;
                    

Delete All Rows

DELETE FROM student;

Completely Remove the Table

DROP TABLE student;

Difference:

DELETE FROM removes all rows but keeps the table structure
DROP TABLE removes both the data and the table structure

Transactions: AUTOCOMMIT, COMMIT, and ROLLBACK

What is a Transaction?

A transaction is a sequence of one or more SQL statements executed as a single unit. Transactions have four key properties (ACID):

Atomicity: All or nothing
Consistency: Valid state before and after
Isolation: Transactions do not interfere
Durability: Changes persist after commit

AUTOCOMMIT

By default, MySQL runs in autocommit mode. Every SQL statement is treated as a separate transaction and committed automatically.

                        -- Check autocommit status
                        SELECT @@autocommit;

                        -- Disable autocommit
                        SET autocommit = 0;

                        -- Enable autocommit
                        SET autocommit = 1;
                    

COMMIT

The COMMIT statement permanently saves all changes made in the current transaction.

                        START TRANSACTION;
                        UPDATE accounts SET balance = balance
                        - 100 WHERE id = 1;
                        UPDATE accounts SET balance = balance
                        + 100 WHERE id = 2;
                        COMMIT;
                    

ROLLBACK

The ROLLBACK statement undoes changes made in the current transaction.

                        START TRANSACTION;
                        UPDATE accounts SET balance = balance
                        - 100 WHERE id = 1;
                        -- An error or condition check fails here
                        ROLLBACK;
                    

Best Practices

Always use transactions when performing multiple related operations
Disable autocommit when working with critical data updates
Rollback if any step in your transaction fails
Test your transactions thoroughly before running them on production data

Date and Time Functions

Getting Current Date and Time

                        -- Current date (YYYY-MM-DD)
                        SELECT CURRENT_DATE;
                        -- Output: 2025-05-02

                        -- Current time (HH:MM:SS)
                        SELECT CURRENT_TIME;
                        -- Output: 14:23:45

                        -- Current date and time
                        SELECT CURRENT_TIMESTAMP;
                        SELECT NOW();
                        -- Output: 2025-05-02 14:23:45

                        -- Local time (synonyms for NOW)
                        SELECT LOCALTIME;
                        SELECT LOCALTIMESTAMP;
                    

Using in Table Inserts

                        INSERT INTO logs (event, created_at)
                        VALUES ('data_import', NOW());
                    

Date and Time Functions Recap

Function	Returns	Example Output
CURRENT_DATE	Date only	2025-05-02
CURRENT_TIME	Time only	14:23:45
NOW()	Date and time	2025-05-02 14:23:45
CURDATE()	Current date	2025-05-02
CURTIME()	Current time	14:23:45

SQL Constraints

Constraints are rules applied to table columns to enforce data integrity, consistency, and validity.

Why Use Constraints?

Ensure data quality and reliability
Prevent invalid, duplicate, or null data
Maintain business rules directly in the database layer

NOT NULL Constraint

Ensures that a column cannot contain NULL values.

                        CREATE TABLE employees (
                        id INT NOT NULL,
                        name VARCHAR(100) NOT NULL
                        );
                    

UNIQUE Constraint

Ensures that all values in a column are distinct (no duplicates).

                        CREATE TABLE users (
                        username VARCHAR(50) UNIQUE,
                        email VARCHAR(100) UNIQUE
                        );
                    

DEFAULT Constraint

Sets a default value for a column if none is provided during insert.

                        CREATE TABLE products (
                        name VARCHAR(100),
                        status VARCHAR(20) DEFAULT 'in_stock'
                        );
                    

CHECK Constraint

Validates that values in a column meet a specific condition.

                        CREATE TABLE accounts (
                        id INT,
                        balance DECIMAL(10,2) CHECK (balance
                        >= 0)
                        );
                    

Naming Constraints

                        CREATE TABLE students (
                        roll_no INT PRIMARY KEY,
                        age INT CONSTRAINT chk_age CHECK (age
                        >= 5),
                        email VARCHAR(100) UNIQUE
                        );
                    

Constraint Summary

Constraint	Purpose
NOT NULL	Disallow null values
UNIQUE	Disallow duplicate values
DEFAULT	Set default value if none given
CHECK	Enforce value conditions
PRIMARY KEY	Uniquely identify each record
FOREIGN KEY	Establish relationships between tables

Foreign Keys

What is a Foreign Key?

A foreign key is a column (or set of columns) that establishes a link between data in two tables.

Creating Tables with Foreign Keys

                        -- Create parent table
                        CREATE TABLE classes (
                        class_id INT AUTO_INCREMENT PRIMARY KEY,
                        class_name VARCHAR(50) NOT NULL
                        );

                        -- Create child table with foreign key
                        CREATE TABLE students (
                        student_id INT AUTO_INCREMENT PRIMARY KEY,
                        student_name VARCHAR(100) NOT NULL,
                        class_id INT,
                        FOREIGN KEY (class_id) REFERENCES
                        classes(class_id)
                        ON UPDATE CASCADE
                        ON DELETE SET NULL
                        );
                    

Understanding Referential Actions

ON UPDATE CASCADE

If the value in the parent table is updated, the corresponding foreign key value in the child table is automatically updated.

                        UPDATE classes SET class_id = 10 WHERE class_id = 1;
                        -- All students with class_id = 1 will automatically become 10
                    

ON DELETE SET NULL

If a row in the parent table is deleted, the foreign key in the child table will be set to NULL.

                        DELETE FROM classes WHERE class_id =
                        2;
                        -- All students in class 2 will have class_id set to NULL
                    

Other Referential Actions

Action	Description
ON DELETE CASCADE	Deletes the child rows when parent row is deleted
ON DELETE RESTRICT	Prevents deletion if any child rows exist
ON DELETE NO ACTION	Same as RESTRICT in MySQL
ON DELETE SET NULL	Sets foreign key to NULL when parent is deleted

Viewing Foreign Keys

                        -- View foreign key constraints
                        SHOW CREATE TABLE students;

                        -- List all foreign keys in database
                        SELECT
                        table_name,
                        column_name,
                        constraint_name,
                        referenced_table_name,
                        referenced_column_name
                        FROM information_schema.key_column_usage
                        WHERE referenced_table_name IS NOT
                            NULL
                        AND table_schema = 'school';
                    

MySQL Joins

Joins are used to combine data from multiple tables based on a related column.

INNER JOIN

Returns only rows that have matching values in both tables.

                        SELECT students.name, marks.subject, marks.score
                        FROM students
                        INNER JOIN marks ON students.id =
                        marks.student_id;
                    

LEFT JOIN

Returns all rows from the left table, and matching rows from the right table. If no match, NULL is returned.

                        SELECT students.name, marks.subject, marks.score
                        FROM students
                        LEFT JOIN marks ON students.id =
                        marks.student_id;
                    

RIGHT JOIN

Returns all rows from the right table, and matching rows from the left table.

                        SELECT students.name, marks.subject, marks.score
                        FROM students
                        RIGHT JOIN marks ON students.id =
                        marks.student_id;
                    

CROSS JOIN

Combines every row in the first table with every row in the second table.

                        SELECT students.name, marks.subject
                        FROM students
                        CROSS JOIN marks;
                    

Join Types Summary

Join Type	What it does
INNER JOIN	Only rows with a match in both tables
LEFT JOIN	All rows from left table, with matched data if any
RIGHT JOIN	All rows from right table, with matched data if any
CROSS JOIN	All combinations of rows from both tables

Using UNION

The UNION operator combines result sets of two or more SELECT statements into a single result.

Basic UNION

                        SELECT name, city FROM customers
                        UNION
                        SELECT name, city FROM vendors;
                    

Requirements for UNION

Same number of columns in all SELECT statements
Compatible data types in corresponding columns
Columns are matched by position, not by name

UNION vs UNION ALL

Key Difference:

UNION removes duplicate rows
UNION ALL keeps all rows including duplicates (faster)

                        -- UNION ALL keeps duplicates
                        SELECT name FROM students
                        UNION ALL
                        SELECT name FROM alumni;
                    

Sorting Combined Results

                        SELECT name FROM students_2023
                        UNION
                        SELECT name FROM students_2024
                        ORDER BY name;
                    

When to Use

Use UNION if you want a clean list without duplicates
Use UNION ALL if you want better performance and don't care about duplicates

MySQL Functions

String Functions

                        -- CONCAT: Join strings
                        SELECT CONCAT('Hello', ' ', 'World') AS greeting;
                        SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

                        -- LENGTH: String length in bytes
                        SELECT LENGTH('Harry'); -- Output: 5

                        -- UPPER/LOWER: Change case
                        SELECT UPPER('hello'); -- Output: HELLO
                        SELECT LOWER('MySQL'); -- Output: mysql

                        -- REPLACE: Replace substring
                        SELECT REPLACE('abc', 'b', 'x'); -- Output: axc

                        -- TRIM: Remove spaces
                        SELECT TRIM('
                            hello '); -- Output: hello
                    

Numeric Functions

                        -- ROUND: Round to decimal places
                        SELECT ROUND(12.6789, 2); -- Output: 12.68

                        -- FLOOR/CEIL: Round down/up
                        SELECT FLOOR(5.9); -- Output: 5
                        SELECT CEIL(5.1); -- Output: 6

                        -- ABS: Absolute value
                        SELECT ABS(-10); -- Output: 10

                        -- MOD: Remainder
                        SELECT MOD(10, 3); -- Output: 1
                    

Date Functions

                        -- DATEDIFF: Days between dates
                        SELECT DATEDIFF('2025-06-01', '2025-05-01'); -- Output: 31

                        -- YEAR, MONTH, DAY: Extract date parts
                        SELECT YEAR(NOW());
                        SELECT MONTHNAME('2025-05-03'); -- Output: May
                        SELECT DAY('2025-05-03'); -- Output: 3

                        -- DATE: Extract date from datetime
                        SELECT DATE(NOW());
                    

Aggregate Functions

                        -- COUNT: Count rows
                        SELECT COUNT(*) FROM students;

                        -- AVG: Average value
                        SELECT AVG(score) FROM marks;

                        -- SUM: Total sum
                        SELECT SUM(score) FROM marks;

                        -- MIN/MAX: Smallest/Largest value
                        SELECT MIN(score) FROM marks;
                        SELECT MAX(score) FROM marks;
                    

Other Useful Functions

                        -- IFNULL: Replace NULL with default
                        SELECT IFNULL(NULL, 'N/A'); --
                            Output: N/A

                        -- COALESCE: First non-NULL value
                        SELECT COALESCE(NULL, '', 'Hello'); -- Output: ''

                        -- RAND: Random decimal between 0 and 1
                        SELECT RAND();
                    

MySQL Views

What is a View?

A View is like a virtual table. It doesn't store data by itself but shows data from one or more tables through a saved SQL query.

Why Use Views?

To simplify complex queries by giving them a name
To hide sensitive columns from users
To show only specific rows/columns from a table
To reuse common query logic across your app or reports

Creating a View

                        CREATE VIEW public_employees AS
                        SELECT name, department, salary
                        FROM employees;
                    

Using a View

                        -- Query like a normal table
                        SELECT * FROM public_employees;

                        -- Apply filters
                        SELECT * FROM public_employees
                        WHERE department = 'IT';
                    

Updating a View

                        CREATE OR REPLACE VIEW public_employees AS
                        SELECT name, department
                        FROM employees;
                    

Dropping a View

DROP VIEW public_employees;

Important Notes:

Views don't store data. If the underlying table changes, the view reflects that automatically
Not all views are updatable. Simple views usually are, but complex ones may not allow INSERT, UPDATE, or DELETE

MySQL Indexes

What is an Index?

An index is a data structure that makes data retrieval faster—especially when using WHERE, JOIN, ORDER BY, or searching large tables.

Think of it like: The index in a book—instead of reading every page, MySQL uses the index to jump straight to the relevant row(s).

Why Use Indexes?

Speed up queries that search, filter, or sort data
Improve performance for frequent lookups or joins
Enhance scalability of your database over time

Creating an Index

                        -- Single column index
                        CREATE INDEX idx_email ON
                        users(email);

                        -- Multi-column (composite) index
                        CREATE INDEX idx_name_city ON
                        users(name, city);
                    

Dropping an Index

DROP INDEX idx_email ON users;

Viewing Indexes

SHOW INDEX FROM users;

When to Use Indexes

A column is often used in WHERE, JOIN, or ORDER BY clauses
You're searching by unique fields like email, username, or ID
You're filtering large tables for specific values regularly
You want to improve performance of lookups and joins

When NOT to Use Indexes

The table is small (MySQL can scan it quickly anyway)
The column is rarely used in searches or filtering
You're indexing a column with very few unique values
You're inserting or updating very frequently (indexes slow down writes)

⚠️ Warning: Overusing indexes or indexing the wrong columns can hurt performance. Use them wisely based on how your data is queried.

Subqueries in MySQL

What is a Subquery?

A subquery is a query nested inside another SQL query. It helps you perform complex filtering, calculations, or temporary data shaping.

Subquery in WHERE Clause

                        -- Employees who earn more than average
                        SELECT name, salary
                        FROM employees
                        WHERE salary > (
                        SELECT AVG(salary)
                        FROM employees
                        );
                    

Subquery in FROM Clause

                        -- Department-wise average salary above 50,000
                        SELECT department, avg_salary
                        FROM (
                        SELECT department, AVG(salary) AS avg_salary
                        FROM employees
                        GROUP BY department
                        ) AS dept_avg
                        WHERE avg_salary > 50000;
                    

Subquery in SELECT Clause

                        -- Count of projects per employee
                        SELECT name,
                        (SELECT COUNT(*)
                        FROM projects
                        WHERE projects.employee_id = employees.id) AS project_count
                        FROM employees;
                    

Correlated Subqueries

A correlated subquery depends on the outer query. It runs once for each row in the outer query.

                        -- Employee earning more than department's average
                        SELECT name, department, salary
                        FROM employees e
                        WHERE salary > (
                        SELECT AVG(salary)
                        FROM employees
                        WHERE department = e.department
                        );
                    

Types of Subqueries

Type	Description
Scalar Subquery	Returns a single value
Row Subquery	Returns one row with multiple columns
Table Subquery	Returns multiple rows and columns
Correlated Subquery	Refers to the outer query inside the subquery

GROUP BY in MySQL

What is GROUP BY?

The GROUP BY clause groups rows that have the same values in specified columns. It's usually combined with aggregate functions like COUNT(), SUM(), AVG(), MAX(), or MIN().

Basic GROUP BY

                        -- Count of employees in each department
                        SELECT department, COUNT(*) AS total_employees
                        FROM employees
                        GROUP BY department;

                        -- Average salary per department
                        SELECT department, AVG(salary) AS avg_salary
                        FROM employees
                        GROUP BY department;
                    

GROUP BY with Multiple Columns

                        -- Count by department and job title
                        SELECT department, job_title, COUNT(*)
                        AS count
                        FROM employees
                        GROUP BY department, job_title;
                    

The HAVING Clause

HAVING is like WHERE, but it works after the grouping is done. It filters groups based on aggregate results.

                        -- Departments with more than 5 employees
                        SELECT department, COUNT(*) AS total
                        FROM employees
                        GROUP BY department
                        HAVING COUNT(*) > 5;
                    

WHERE vs HAVING

Clause	Used For	Example
WHERE	Filters rows before grouping	WHERE salary > 50000
HAVING	Filters groups after grouping	HAVING AVG(salary) > 60000

Using Both WHERE and HAVING

                        SELECT department, AVG(salary) AS avg_salary
                        FROM employees
                        WHERE status = 'active'
                        GROUP BY department
                        HAVING AVG(salary) > 60000;
                    

Execution Order:

WHERE filters rows
GROUP BY groups them
HAVING filters the groups

Stored Procedures

What is a Stored Procedure?

A Stored Procedure is a saved block of SQL code that you can execute later by calling its name—just like a function in programming.

Why Use Stored Procedures?

To avoid repeating the same SQL logic in multiple places
To improve performance by reducing network traffic
To encapsulate complex business logic inside the database

Creating a Simple Procedure

Why change the DELIMITER?
MySQL ends a command at the first semicolon (;). Since stored procedures contain multiple SQL statements (each ending in ;), we need to tell MySQL not to end the procedure too early. So we temporarily change the delimiter to something else—then switch it back.

                        DELIMITER //

                        CREATE PROCEDURE list_employees()
                        BEGIN
                        SELECT * FROM employees;
                        END //

                        DELIMITER ;
                    

Calling a Stored Procedure

CALL list_employees();

Procedure with Parameters

                        DELIMITER //

                        CREATE PROCEDURE get_employee_by_id(IN
                        emp_id INT)
                        BEGIN
                        SELECT * FROM employees
                        WHERE id = emp_id;
                        END //

                        DELIMITER ;

                        -- Call with parameter
                        CALL get_employee_by_id(3);
                    

Dropping a Stored Procedure

DROP PROCEDURE IF EXISTS get_employee_by_id;

Best Practices

Always give clear names to procedures
Use IN, OUT, or INOUT for flexible parameter handling
Keep business logic in the database only if it improves clarity or performance
Document complex procedures for easier maintenance