What is a Database?

A database is an organized collection of data that is stored and managed electronically. It allows users to
efficiently store, retrieve, update, and delete data.

Why are Databases Important?

Databases are essential for managing structured data in various industries, such as:

 Banking & Finance – Storing transaction records

 E-commerce – Managing customer orders and inventory

 Healthcare – Keeping patient records secure

 Social Media – Handling user profiles and posts

Traditional File System vs. Databases

 File System: Stores data in files and folders but lacks efficient querying and relationships.

 Database System: Uses structured tables and allows for easy data retrieval and manipulation
using SQL.

Types of Databases

Databases are categorized into two main types based on their structure and data storage approach.

Relational Databases (SQL-based)

 Stores data in tables (rows & columns).

 Uses Structured Query Language (SQL) to manage data.

 Examples: MySQL, PostgreSQL, Oracle, SQL Server

Non-Relational Databases (NoSQL-based)

 Stores data in JSON, key-value pairs, or documents.

 Suitable for unstructured or semi-structured data.

 Examples: MongoDB, Firebase, Cassandra

 Key Difference: SQL databases follow a strict schema, while NoSQL databases offer flexible data
storage.

What is RDBMS (Relational Database Management System)?

An RDBMS is a software system used to create and manage relational databases. It organizes data
into tables that are linked through relationships.

Key Features of RDBMS:

Structured Storage: Data is stored in tables with predefined schemas.

Data Integrity & Consistency: Maintains accurate and consistent data using constraints.

Relationships: Tables are connected using Primary Keys & Foreign Keys.

Security & User Management: Supports authentication, authorization, and role-based access.

Understanding Tables, Rows, and Columns

 Table: A structured collection of data (e.g., employees table).

 Row (Record): A single entry in a table (e.g., an employee’s details).

 Column (Field): Represents an attribute of a record (e.g., name, salary).

Example Table: Employees

Employee_ID Name Department Salary

101 Alice IT 60,000

102 Bob HR 55,000

103 Carol Finance 70,000

Primary Key: Employee_ID (Uniquely identifies each employee)

Installing & Setting Up a Database

To start working with SQL, trainees need to install and configure a database management system.

Choosing a Database System

For this training, we will use MySQL or PostgreSQL:

MySQL: Easy to learn, widely used in web applications.

PostgreSQL: Advanced features, better for complex queries.

Installation Steps

1. Download & Install

o MySQL: Download from MySQL Official Website

o PostgreSQL: Download from PostgreSQL Official Website

2. Install SQL Workbench (For MySQL) or pgAdmin (For PostgreSQL)

3. Set Up a Local Database

4. Verify Installation by Running Basic Commands

Basic Database Operations

Once the database is installed, trainees will learn how to create and manage databases.

Creating a New Database

CREATE DATABASE Company;

 This command creates a new database named Company.

Checking Existing Databases

SHOW DATABASES;

 Lists all available databases.

Selecting a Database

USE Company;
  Activates the Company database for performing operations.

What is SQL?
SQL (Structured Query Language) is a standardized language used for storing, retrieving, and managing
data in relational databases.

Why is SQL Important?

Widely Used: SQL is the most popular language for managing relational databases.

Industry Standard: Used by companies like Google, Amazon, and Facebook.

Essential for Data Handling: Required for backend development, data analysis, and database
administration.

SQL Syntax Structure

A basic SQL query follows this structure:

SELECT column1, column2 FROM table_name WHERE condition ORDER BY column1;

 SELECT – Specifies which columns to retrieve.

 FROM – Specifies the table to query.

 WHERE – Applies conditions to filter results (optional).

 ORDER BY – Sorts the results (optional).

Writing Basic SQL Queries

Retrieving Data with SELECT

The SELECT statement is used to fetch data from a table.

Example: Retrieve all data from the employees table

SELECT * FROM employees;

  The * symbol selects all columns from the table.

Example: Retrieve only name and department from the employees table

SELECT name, department FROM employees;

Filtering Data with WHERE Clause

The WHERE clause is used to filter records based on a condition.

Example: Retrieve employees from the IT department

SELECT * FROM employees WHERE department = 'IT';

Example: Retrieve employees earning more than $60,000

SELECT * FROM employees WHERE salary > 60000;

Using Operators for Filtering

Comparison Operators

Operator Description Example

= Equal to salary = 50000

!= or <> Not equal to department <> 'HR'

> Greater than salary > 60000

< Less than salary < 50000

>= Greater than or equal to salary >= 60000

<= Less than or equal to salary <= 55000

Example: Retrieve employees who earn between $50,000 and $70,000

SELECT * FROM employees WHERE salary >= 50000 AND salary <= 70000;
Using ORDER BY for Sorting

The ORDER BY clause sorts query results in ascending (ASC) or descending (DESC) order.

Example: Retrieve employees sorted by salary (lowest to highest)

SELECT * FROM employees ORDER BY salary ASC;

Example: Retrieve employees sorted by name in descending order

SELECT * FROM employees ORDER BY name DESC;

Using BETWEEN, IN, and LIKE for Advanced Filtering

BETWEEN – Filtering within a range

Example: Retrieve employees with salaries between $40,000 and $70,000

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 70000;

IN – Filtering multiple values

Example: Retrieve employees from the IT and HR departments

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

LIKE – Searching for patterns in text data

Example: Retrieve employees whose names start with ‘A’

SELECT * FROM employees WHERE name LIKE 'A%';

 % is a wildcard that represents any sequence of characters.

 'A%' means names starting with “A”.

Example: Retrieve employees whose names contain ‘son’

SELECT * FROM employees WHERE name LIKE '%son%';

Hands-on Task: Writing Your First SQL Queries

Trainees will apply what they’ve learned by running SQL queries on a sample database.

Task 1: Retrieve all employees from the database

SELECT * FROM employees;

Task 2: Retrieve employees from the “Marketing” department

SELECT * FROM employees WHERE department = 'Marketing';

Task 3: Retrieve employees earning more than $50,000, sorted by salary in descending order

SELECT * FROM employees WHERE salary > 50000 ORDER BY salary DESC;

Task 4: Retrieve employees whose names start with ‘J’

SELECT * FROM employees WHERE name LIKE 'J%';

What is Data Definition Language (DDL)?

DDL (Data Definition Language) is a set of SQL commands used to define the structure of a database,
including tables, columns, and relationships.

Key DDL Commands

Command Description

CREATE TABLE Creates a new table

ALTER TABLE Modifies an existing table

DROP TABLE Deletes a table permanently

TRUNCATE TABLE Deletes all rows from a table but keeps its structure
Creating Tables with CREATE TABLE

A table consists of columns (fields) and rows (records). The CREATE TABLE command is used to define
the structure of a table, including column names, data types, and constraints.

SQL Syntax for Creating a Table

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

department VARCHAR(50),

salary DECIMAL(10,2) CHECK (salary > 0),

hire_date DATE DEFAULT CURRENT_DATE

);

Explanation of the Columns & Constraints

 employee_id INT PRIMARY KEY → Primary Key (unique identifier for each row).

 name VARCHAR(50) NOT NULL → String data type (max 50 characters), cannot be NULL.

 department VARCHAR(50) → String column for department names.

 salary DECIMAL(10,2) CHECK (salary > 0) → Decimal number with a constraint (must be greater
than 0).

 hire_date DATE DEFAULT CURRENT_DATE → Stores the date an employee was hired (default is
today’s date).

Example: Create a departments table with a primary key

CREATE TABLE departments (

department_id INT PRIMARY KEY,

department_name VARCHAR(100) NOT NULL

);
Modifying Tables with ALTER TABLE

The ALTER TABLE command allows you to modify an existing table by adding, modifying, or deleting
columns.

Adding a New Column to a Table

ALTER TABLE employees ADD email VARCHAR(100);

 Adds a new column email to the employees table.

Modifying an Existing Column

ALTER TABLE employees MODIFY salary DECIMAL(12,2);

 Changes the data type of the salary column.

Dropping (Deleting) a Column

ALTER TABLE employees DROP COLUMN email;

 Removes the email column from the table.

Defining Primary Keys & Foreign Keys

What is a Primary Key?

A Primary Key is a unique identifier for each row in a table. It must be unique and cannot be NULL.

Example: Define a Primary Key

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

name VARCHAR(50) NOT NULL

);

 employee_id uniquely identifies each employee.

What is a Foreign Key?

A Foreign Key is a column that creates a relationship between two tables. It references the primary
key of another table.
 Example: Creating a Foreign Key Relationship

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

department_id INT,

FOREIGN KEY (department_id) REFERENCES departments(department_id)

);

 department_id in employees refers to department_id in departments.

Using Constraints to Ensure Data Integrity

Constraints restrict invalid data and maintain consistency.

Common Constraints in SQL

Constraint Description

NOT NULL Ensures a column cannot have NULL values

UNIQUE Ensures all values in a column are unique

PRIMARY KEY A combination of NOT NULL and UNIQUE

FOREIGN KEY Ensures a valid relationship between tables

DEFAULT Assigns a default value if no value is provided

CHECK Ensures values meet a specific condition

Example: Using Constraints

CREATE TABLE employees (

 employee_id INT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

department VARCHAR(50),

salary DECIMAL(10,2) CHECK (salary > 0),

hire_date DATE DEFAULT CURRENT_DATE

);

 NOT NULL ensures name cannot be empty.

 CHECK (salary > 0) ensures salary is a positive number.

 DEFAULT CURRENT_DATE sets hire_date to today if no value is given.

Deleting Tables with DROP TABLE & TRUNCATE TABLE

DROP TABLE – Permanently Deletes a Table

DROP TABLE employees;

 Deletes the entire table, including its structure.

TRUNCATE TABLE – Deletes All Records but Keeps the Structure

TRUNCATE TABLE employees;

 Removes all records but keeps the table structure.

Key Difference:

Command Deletes Data? Deletes Table Structure?

DROP TABLE
Yes Yes

TRUNCATE TABLE
Yes No
Hands-on Tasks: Practice DDL Commands

Task 1: Create an employees Table with Constraints

CREATE TABLE employees (

employee_id INT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

department_id INT,

salary DECIMAL(10,2) CHECK (salary > 0),

hire_date DATE DEFAULT CURRENT_DATE,

FOREIGN KEY (department_id) REFERENCES departments(department_id)

);

Task 2: Add a New Column (email) to employees Table

ALTER TABLE employees ADD email VARCHAR(100);

Task 3: Delete the employees Table

DROP TABLE employees;

What is Data Manipulation Language (DML)?

DML (Data Manipulation Language) is a group of SQL commands used to modify data within tables.
Unlike DDL, which defines the database structure, DML allows users to insert, update, and delete data
records.

Key DML Commands

Command Description

INSERT INTO Adds new records to a table

UPDATE Modifies existing records

 Command Description

DELETE Removes records from a table

COMMIT Saves changes permanently

ROLLBACK Undoes changes before a commit

SAVEPOINT Sets a point to partially roll back transactions

Inserting Data into Tables (INSERT INTO)

The INSERT INTO command is used to add new rows into a table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

Example: Insert a new employee into the employees table

INSERT INTO employees (employee_id, name, department, salary, hire_date)

VALUES (101, 'Alice Johnson', 'IT', 60000, '2024-01-15');

 Adds a new row to the employees table.

Example: Insert multiple records at once

INSERT INTO employees (employee_id, name, department, salary, hire_date)

VALUES

(102, 'Bob Smith', 'HR', 55000, '2023-12-10'),

(103, 'Charlie Brown', 'Finance', 70000, '2022-09-05');

 Inserts two records at the same time.

 Example: Insert data into specific columns only

INSERT INTO employees (employee_id, name)

VALUES (104, 'David Lee');

 Missing values will be stored as NULL or use default values (if defined).

Updating Existing Records (UPDATE)

The UPDATE command is used to modify existing records in a table.

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2

WHERE condition;

Example: Update an employee’s salary

UPDATE employees

SET salary = 65000

WHERE employee_id = 101;

 Updates only one record where employee_id = 101.

Example: Increase the salary of all IT department employees by 10%

UPDATE employees

SET salary = salary * 1.10

WHERE department = 'IT';

 Updates multiple records for all IT employees.

WARNING: Always use the WHERE clause!

If you forget WHERE, all rows will be updated.
UPDATE employees

SET salary = 70000; -- Updates ALL employees' salaries!

Deleting Data from Tables (DELETE)

The DELETE command is used to remove records from a table.

Syntax:

DELETE FROM table_name

WHERE condition;

Example: Delete an employee by ID

DELETE FROM employees

WHERE employee_id = 103;

 Removes only one record where employee_id = 103.

Example: Delete all employees from the HR department

DELETE FROM employees

WHERE department = 'HR';

 Deletes multiple records from the employees table.

WARNING: If you forget WHERE, all data will be deleted!

DELETE FROM employees; -- Deletes ALL records!

Alternative: Use TRUNCATE TABLE if you want to remove all data

TRUNCATE TABLE employees;

 Faster than DELETE, but cannot be undone.

 Managing Transactions in SQL

A transaction is a sequence of database operations that must be executed as a single unit. Transactions
ensure data consistency and integrity using COMMIT, ROLLBACK, and SAVEPOINT.

Key Transaction Commands

Command Description

COMMIT Saves all changes permanently

ROLLBACK Undoes changes before they are committed

SAVEPOINT Sets a point within a transaction to partially roll back

Using COMMIT to Save Changes

The COMMIT command saves all changes made in a transaction.

Example: Insert data and commit changes

INSERT INTO employees (employee_id, name, department, salary)

VALUES (105, 'Emma Watson', 'Finance', 75000);

COMMIT; -- Saves the changes permanently

 After COMMIT, changes cannot be undone.

Using ROLLBACK to Undo Changes

The ROLLBACK command undoes changes made in a transaction before committing.

Example: Insert data, then rollback

INSERT INTO employees (employee_id, name, department, salary)

VALUES (106, 'Frank White', 'Marketing', 50000);

ROLLBACK; -- Cancels the transaction, record is NOT saved

 The record will not be inserted since ROLLBACK was used.

Using SAVEPOINT for Partial Rollbacks

The SAVEPOINT command creates checkpoints within a transaction, allowing partial rollbacks.

Example: Using SAVEPOINT

BEGIN;

INSERT INTO employees (employee_id, name, department, salary)

VALUES (107, 'George Harris', 'IT', 68000);

SAVEPOINT sp1; -- Savepoint created

INSERT INTO employees (employee_id, name, department, salary)

VALUES (108, 'Hannah Scott', 'HR', 53000);

ROLLBACK TO sp1; -- Undo the second insert, but keep the first one

COMMIT; -- Save the remaining changes

 Employee 107 is saved, but 108 is not due to rollback.

Hands-on Tasks: Practice DML Commands

Task 1: Insert New Employees

INSERT INTO employees (employee_id, name, department, salary, hire_date)

VALUES (109, 'Isabella Green', 'Sales', 62000, '2024-02-01');

Task 2: Update Employee Salary

UPDATE employees

SET salary = salary * 1.05

WHERE department = 'Sales';

Task 3: Delete an Employee Record

DELETE FROM employees

WHERE employee_id = 109;

Task 4: Use Transactions

BEGIN;

INSERT INTO employees (employee_id, name, department, salary)

VALUES (110, 'Jack Taylor', 'HR', 58000);

SAVEPOINT sp1;

DELETE FROM employees WHERE department = 'HR';

ROLLBACK TO sp1;

COMMIT;

Retrieving Data with SELECT

The SELECT statement is used to fetch data from a database.

Basic Syntax:

SELECT column1, column2 FROM table_name;

 Retrieves specific columns from a table.

 Example: Retrieve employee names and salaries

SELECT name, salary FROM employees;

 Fetches name and salary columns from the employees table.

Example: Retrieve all columns

SELECT * FROM employees;

 The * symbol selects all columns from the table.

Filtering Data with WHERE

The WHERE clause filters records based on conditions.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition;

Example: Retrieve employees from the IT department

SELECT name, department FROM employees WHERE department = 'IT';

Example: Retrieve employees with a salary greater than 50,000

SELECT name, salary FROM employees WHERE salary > 50000;

Using Multiple Conditions (AND, OR)

SELECT name, department, salary

FROM employees

WHERE department = 'IT' AND salary > 60000;

 Fetches employees only in IT with a salary greater than 60,000.

Sorting Results with ORDER BY

The ORDER BY clause sorts query results in ascending or descending order.

 Syntax:

SELECT column1, column2 FROM table_name ORDER BY column1 ASC|DESC;

 ASC (default) → Ascending Order

 DESC → Descending Order

Example: Retrieve employees sorted by salary (highest to lowest)

SELECT name, salary FROM employees ORDER BY salary DESC;

Example: Retrieve employees sorted by department (A to Z) and then by salary

SELECT name, department, salary

FROM employees

ORDER BY department ASC, salary DESC;

Grouping Data with GROUP BY

The GROUP BY clause groups rows that have the same values in a specified column. It is used with
aggregation functions.

Syntax:

SELECT column1, AGGREGATE_FUNCTION(column2)

FROM table_name

GROUP BY column1;

Example: Count employees in each department

SELECT department, COUNT(*) AS total_employees

FROM employees

GROUP BY department;

 Groups employees by department and counts them.

 Example: Find the average salary per department

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department;

 Groups salaries by department and calculates average salary.

Filtering Grouped Data with HAVING

The HAVING clause filters grouped results, similar to WHERE but used after aggregation.

Syntax:

SELECT column1, AGGREGATE_FUNCTION(column2)

FROM table_name

GROUP BY column1

HAVING condition;

Example: Show only departments with more than 5 employees

SELECT department, COUNT(*) AS total_employees

FROM employees

GROUP BY department

HAVING COUNT(*) > 5;

 Filters results after grouping to show departments with more than 5 employees.

Example: Find departments with an average salary above 60,000

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department

HAVING AVG(salary) > 60000;

  Shows only departments where average salary is greater than 60,000.

Using Aggregation Functions

Common Aggregate Functions in SQL

Function Description

COUNT(*) Counts the number of rows

SUM(column) Adds up all values in a column

AVG(column) Calculates the average value

MAX(column) Finds the highest value

MIN(column) Finds the lowest value

Example: Count total employees

SELECT COUNT(*) AS total_employees FROM employees;

Example: Find the highest and lowest salary

SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary FROM employees;

Example: Calculate total salary expense per department

SELECT department, SUM(salary) AS total_salary

FROM employees

GROUP BY department;

Hands-on Tasks: Practice Data Retrieval & Aggregation

 Task 1: Retrieve all employee names and salaries

SELECT name, salary FROM employees;

Task 2: Find employees in the IT department earning more than 60,000

SELECT name, salary FROM employees

WHERE department = 'IT' AND salary > 60000;

Task 3: Sort employees by highest salary first

SELECT name, salary FROM employees

ORDER BY salary DESC;

Task 4: Count the number of employees in each department

SELECT department, COUNT(*) AS employee_count

FROM employees

GROUP BY department;

Task 5: Find departments with an average salary greater than 65,000

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department

HAVING AVG(salary) > 65000;

SQL Constraints & Data Integrity

Lesson Summary

In this lesson, you will learn about SQL constraints and how they enforce data integrity in relational
databases. Constraints ensure that data remains accurate, consistent, and reliable by applying rules to
table columns. You will explore different types of constraints such as NOT NULL, UNIQUE, PRIMARY
KEY, FOREIGN KEY, CHECK, and DEFAULT, along with hands-on SQL queries to implement them.
Lesson Content

What Are SQL Constraints?

SQL constraints are rules that restrict what data can be inserted into a table, ensuring data accuracy
and consistency.

Types of SQL Constraints

1. NOT NULL – Ensures a column cannot have NULL values.

2. UNIQUE – Ensures all values in a column are distinct.

3. PRIMARY KEY – Uniquely identifies each row (NOT NULL + UNIQUE).

4. FOREIGN KEY – Links data between two tables, ensuring referential integrity.

5. CHECK – Ensures data meets specific conditions.

6. DEFAULT – Assigns a default value when no value is provided.

Implementing SQL Constraints

NOT NULL Constraint

Prevents NULL values in a column.

CREATE TABLE employees (

id INT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

department VARCHAR(50)

);

If you try to insert a record without a name, SQL will throw an error.

UNIQUE Constraint
Ensures column values are unique.

CREATE TABLE users (

user_id INT PRIMARY KEY,

email VARCHAR(100) UNIQUE

);

Trying to insert duplicate emails will cause an error.

PRIMARY KEY Constraint

A Primary Key uniquely identifies each row.

CREATE TABLE students (

student_id INT PRIMARY KEY,

name VARCHAR(50) NOT NULL

);

Each student_id must be unique and cannot be NULL.

FOREIGN KEY Constraint

Links a column to another table, ensuring referential integrity.

CREATE TABLE departments (

dept_id INT PRIMARY KEY,

dept_name VARCHAR(50) NOT NULL

);

CREATE TABLE employees (

emp_id INT PRIMARY KEY,

 name VARCHAR(50) NOT NULL,

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES departments(dept_id)

);

If you try to assign an employee to a non-existent department, SQL will prevent it.

CHECK Constraint

Ensures values meet a condition.

CREATE TABLE products (

product_id INT PRIMARY KEY,

price DECIMAL(10,2) CHECK (price > 0)

);

Prevents inserting negative prices.

DEFAULT Constraint

Assigns a default value if no value is provided.

CREATE TABLE orders (

order_id INT PRIMARY KEY,

order_status VARCHAR(20) DEFAULT 'Pending'

);

If you insert an order without specifying order_status, it defaults to 'Pending'.

Modifying Constraints on Existing Tables

Adding a Constraint

ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE(email);

Dropping a Constraint

ALTER TABLE employees DROP CONSTRAINT unique_email;

Learning Outcomes

By the end of this lesson, you will be able to:

Understand the importance of SQL constraints in data integrity.

Implement constraints like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and
DEFAULT.

Prevent invalid data entry and maintain database consistency.

Modify and manage constraints using ALTER TABLE.

Hands-on Exercise

Create a table with different constraints.

Try inserting invalid data and observe constraint violations.

Modify a table to add or remove constraints.

Advanced Filtering Techniques

SQL filtering methods allow us to extract specific and relevant data from large datasets.

Using the WHERE Clause for Filtering

The WHERE clause is used to specify conditions for filtering data.

SELECT * FROM employees WHERE department = 'Sales';

 Retrieves all employees working in the Sales department.

Pattern Matching with LIKE

The LIKE operator allows filtering text using wildcards:

 % = Zero or more characters

 _ = Exactly one character

SELECT * FROM customers WHERE name LIKE 'A%';

Retrieves customers whose names start with “A”.

SELECT * FROM customers WHERE name LIKE '_ohn';

Retrieves customers whose names end with “ohn” (e.g., John).

Using IN for Multiple Value Filters

The IN operator is a shorthand for multiple OR conditions.

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

Retrieves employees from Sales, HR, or IT departments.

Using BETWEEN for Range Filters

The BETWEEN operator filters values within a range.

SELECT * FROM products WHERE price BETWEEN 100 AND 500;

Retrieves products priced between $100 and $500.

Handling NULL Values

Filter missing data using IS NULL or IS NOT NULL.

SELECT * FROM employees WHERE department IS NULL;

Retrieves employees with no assigned department.

Using Logical Operators (AND, OR, NOT)

Combine multiple conditions for precise filtering.

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

Retrieves employees in Sales with a salary above $50,000.

SELECT * FROM employees WHERE department = 'HR' OR salary < 40000;

Retrieves employees in HR or earning less than $40,000.

Introduction to Subqueries (Nested Queries)

A subquery (or nested query) is a query inside another query.

Subquery in WHERE Clause

Find employees earning more than the company’s average salary.

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

The inner query calculates the average salary, and the outer query retrieves employees
earning above that value.

Subquery in SELECT Statement

Retrieve employee names and department names using a subquery.

SELECT name, (SELECT dept_name FROM departments WHERE departments.dept_id =

employees.dept_id) AS department FROM employees;
 Subquery in FROM Clause (Derived Table)

Calculate the average salary per department for employees earning above $40,000.

SELECT department, AVG(salary) AS avg_salary FROM (SELECT department, salary FROM employees
WHERE salary > 40000) AS temp_table GROUP BY department;

Subquery in INSERT, UPDATE, DELETE

Using Subquery in INSERT

Insert all IT employees into a backup table.

INSERT INTO employees_backup (emp_id, name, department, salary) SELECT emp_id, name,
department, salary FROM employees WHERE department = 'IT';

Using Subquery in UPDATE

Increase salaries of employees earning below the average salary by 10%.

UPDATE employees SET salary = salary * 1.10 WHERE salary < (SELECT AVG(salary) FROM employees);

Using Subquery in DELETE

Remove employees from departments that no longer exist.

DELETE FROM employees WHERE dept_id NOT IN (SELECT dept_id FROM departments);

Hands-on Exercise

Retrieve employees earning above the company’s average salary.

Find customers who placed an order in the last 30 days.

List employees belonging to departments that no longer exist.

 Identify products costing more than the average product price.

Use a subquery to update employee salaries based on department averages.

SQL Joins – Combining Data from Multiple Tables

Understanding SQL Joins

A JOIN in SQL is used to combine rows from two or more tables based on a related column. The most
common way to join tables is by using a primary key – foreign key relationship.

Syntax for SQL Joins

SELECT column_names

FROM table1

JOIN table2

ON table1.common_column = table2.common_column;

The ON clause specifies the condition for joining the tables.

Types of SQL Joins

INNER JOIN (Intersection of Tables)

Retrieves only matching records from both tables.

SELECT employees.emp_id, [Link], departments.dept_name

FROM employees

INNER JOIN departments

ON employees.dept_id = departments.dept_id;
 This query retrieves only employees who are assigned to a department.

If an employee does not have a department assigned, they will not be included.

Example Dataset

emp_id name dept_id

1 John 101

2 Alice 102

3 Bob NULL

dept_id dept_name

101 HR

102 IT

Result after INNER JOIN:

emp_id name dept_name

1 John HR

2 Alice IT

Notice: Bob is excluded because he does not have a department assigned.

LEFT JOIN (All Records from Left Table + Matching from Right)
 Retrieves all records from the left table and matching records from the right table.

If there is no match in the right table, it returns NULL.

SELECT employees.emp_id, [Link], departments.dept_name

FROM employees

LEFT JOIN departments

ON employees.dept_id = departments.dept_id;

Result after LEFT JOIN:

emp_id name dept_name

1 John HR

2 Alice IT

3 Bob NULL

Bob is included, but his dept_name is NULL since he does not have a department.

RIGHT JOIN (All Records from Right Table + Matching from Left)

Retrieves all records from the right table and matching records from the left table.

If there is no match in the left table, it returns NULL.

SELECT employees.emp_id, [Link], departments.dept_name

FROM employees

RIGHT JOIN departments

ON employees.dept_id = departments.dept_id;

If a department exists but has no employees, it will still be included in the result.
 FULL JOIN (All Records from Both Tables)

Retrieves all records from both tables, with NULL values if there is no match.

SELECT employees.emp_id, [Link], departments.dept_name

FROM employees

FULL JOIN departments

ON employees.dept_id = departments.dept_id;

Result after FULL JOIN:

emp_id name dept_name

1 John HR

2 Alice IT

3 Bob NULL

NULL NULL Finance

The Finance department appears, even though no employees are assigned to it.

CROSS JOIN (Cartesian Product of Tables)

Returns all possible combinations of rows from both tables.

The number of rows in the result = rows in Table 1 × rows in Table 2.

SELECT [Link], departments.dept_name

FROM employees

CROSS JOIN departments;

 This is useful when pairing every row from one table with every row from another table.

SELF JOIN (Joining a Table with Itself)

Used when a table references itself, such as hierarchical data (e.g., employees & managers).

SELECT [Link] AS Employee, [Link] AS Manager

FROM employees e1

LEFT JOIN employees e2

ON e1.manager_id = e2.emp_id;

This retrieves each employee’s manager’s name.

Hands-on Exercises

Retrieve a list of employees with their department names (INNER JOIN).

Get all employees, even those without a department (LEFT JOIN).

List all departments, including those with no employees (RIGHT JOIN).

Retrieve all employees and all departments (FULL JOIN).

Find all possible employee-department combinations (CROSS JOIN).

List employees with their managers (SELF JOIN).

Self Joins & Cross Joins

In this lesson, you will explore Self Joins and Cross Joins, two powerful SQL techniques used to analyze
relationships within the same table and generate all possible combinations of data.

 Self Join is used when a table references itself, such as employee-manager relationships,
hierarchical data structures, or product dependencies.
  Cross Join creates the Cartesian product of two tables, generating combinations that are useful
for pairing items, testing conditions, or analyzing data patterns.

By the end of this lesson, you will be able to apply Self Joins and Cross Joins to solve real-world database
problems.

Understanding Self Join

A Self Join is when a table is joined with itself. It is useful for cases where records in a table relate to
other records in the same table.

Self Join is commonly used for:

 Employee-manager relationships

 Product dependencies

 Organizational hierarchies

Syntax for Self Join

SELECT A.column_name, B.column_name

FROM table_name A

JOIN table_name B

ON A.common_column = B.common_column;

The table is given two aliases (A and B) so that it behaves like two separate tables.

Example: Employee-Manager Relationship

Consider an employees table where each employee has a manager who is also an employee.

Employee Table

emp_id name manager_id

1 John NULL
 emp_id name manager_id

2 Alice 1

3 Bob 1

4 David 2

Each employee has a manager_id, which refers to another emp_id in the same table.

Self Join Query to Retrieve Employee-Manager Pairs

SELECT [Link] AS Employee, [Link] AS Manager

FROM employees E1

LEFT JOIN employees E2

ON E1.manager_id = E2.emp_id;

E1 refers to the employee, while E2 refers to the manager.

Result

Employee Manager

John NULL

Alice John

Bob John

David Alice

John has no manager (NULL value).

Alice and Bob report to John, while David reports to Alice.

 Example: Product Dependencies

Imagine a table where products depend on other products for assembly.

Product Table

product_id product_name parent_product_id

1 Car NULL

2 Engine 1

3 Wheel 1

4 Piston 2

Self Join Query to Retrieve Product Dependencies

SELECT P1.product_name AS Product, P2.product_name AS Component

FROM products P1

LEFT JOIN products P2

ON P1.product_id = P2.parent_product_id;

This retrieves which components belong to which product.

Result

Product Component

Car Engine

Car Wheel

Engine Piston
 The Car is made of Engine and Wheel, and the Engine consists of Piston.

Understanding Cross Join

A Cross Join returns the Cartesian product of two tables, meaning it pairs every row from the first table
with every row from the second table.

Cross Joins are useful for:

 Generating all possible combinations of items

 Pairing employees with available shifts

 Comparing datasets for testing

Syntax for Cross Join

SELECT column_names

FROM table1

CROSS JOIN table2;

This does not require a ON condition because it joins every row with every row.

Example: Employee and Work Shifts

Consider a work schedule where employees need to be assigned to different shifts.

Employees Table

emp_id name

1 John

2 Alice
 Shifts Table

shift_id shift_time

A Morning

B Evening

Cross Join Query to Pair Employees with Shifts

SELECT [Link], shifts.shift_time

FROM employees

CROSS JOIN shifts;

Every employee is paired with every shift.

Result

name shift_time

John Morning

John Evening

Alice Morning

Alice Evening

Each employee gets assigned to both shifts.

Example: Generating All Possible Product Color Combinations

Consider a store selling T-shirts in different colors.

Products Table
 product_id product_name

1 T-shirt

2 Hoodie

Colors Table

color_id color_name

A Red

B Blue

Cross Join Query to Generate All Product-Color Combinations

SELECT products.product_name, colors.color_name

FROM products

CROSS JOIN colors;

Result

product_name color_name

T-shirt Red

T-shirt Blue

Hoodie Red

Hoodie Blue

All possible product-color combinations are generated.

 Hands-on Exercises

Retrieve employee-manager relationships using Self Join.

Find all products and their sub-components using Self Join.

Generate all possible employee-shift assignments using Cross Join.

Create all possible product-color combinations using Cross Join.

Hands-on SQL Query Challenge

Challenge 1: Employee Management System

Problem Statement

A company has an employees table and a departments table. You need to retrieve specific
information based on different conditions.

Employees Table

emp_id name dept_id salary hire_date

1 John 101 50000 2018-03-12

2 Alice 102 60000 2019-07-01

3 Bob NULL 45000 2020-11-15

4 David 101 70000 2017-09-23

Departments Table
 dept_id dept_name

101 HR

102 IT

Tasks

Retrieve all employees along with their department names (include employees
without a department).

Get the total number of employees in each department.

Find the employee(s) with the highest salary.

List employees who joined before 2019.

Sample Queries

Retrieve employees with department names (LEFT JOIN):

SELECT [Link], d.dept_name

FROM employees e

LEFT JOIN departments d

ON e.dept_id = d.dept_id;

Get total employees per department (GROUP BY):

SELECT d.dept_name, COUNT(e.emp_id) AS total_employees

FROM employees e

LEFT JOIN departments d

ON e.dept_id = d.dept_id

GROUP BY d.dept_name;
 Find employee with highest salary (MAX function & Subquery):

SELECT name, salary

FROM employees

WHERE salary = (SELECT MAX(salary) FROM employees);

List employees who joined before 2019:

SELECT name, hire_date

FROM employees

WHERE hire_date < '2019-01-01';

Challenge 2: E-Commerce Sales Data Analysis

Problem Statement

An online store maintains records of customer orders. You need to analyze the sales data.

Orders Table

order_id customer_id product_id quantity order_date total_price

1 101 1 2 2024-01-10 400

2 102 2 1 2024-02-15 250

3 101 3 5 2024-01-20 500

4 103 1 3 2024-02-18 600

Products Table
 product_id product_name price

1 Laptop 200

2 Phone 250

3 Keyboard 100

Tasks

Find the total revenue generated from all orders.

List all orders with product names instead of product IDs.

Find the customer who placed the most orders.

Get the average order value per customer.

Sample Queries

Calculate total revenue (SUM function):

SELECT SUM(total_price) AS total_revenue

FROM orders;

List orders with product names (JOIN orders & products):

SELECT o.order_id, p.product_name, [Link], o.total_price

FROM orders o

JOIN products p

ON o.product_id = p.product_id;

Find the customer who placed the most orders (GROUP BY & COUNT):
SELECT customer_id, COUNT(order_id) AS total_orders

FROM orders

GROUP BY customer_id

ORDER BY total_orders DESC

LIMIT 1;

Calculate average order value per customer:

SELECT customer_id, AVG(total_price) AS avg_order_value

FROM orders

GROUP BY customer_id;

Challenge 3: Library Management System

Problem Statement

A library keeps track of borrowed books. You need to analyze the borrowing trends.

Borrowed Books Table

borrow_id book_id member_id borrow_date return_date

1 101 1 2024-02-01 2024-02-10

2 102 2 2024-02-05 NULL

3 103 1 2024-02-07 2024-02-15

Books Table
 book_id title author

101 SQL for Beginners John Doe

102 Python Advanced Alice Smith

103 Data Science 101 Bob Martin

Tasks

Find the total number of books borrowed.

Retrieve details of books that haven’t been returned yet.

Get the most borrowed book.

List the members who borrowed books the most.

Sample Queries

Find total books borrowed:

SELECT COUNT(borrow_id) AS total_borrowed

FROM borrowed_books;

Get books that haven’t been returned (NULL check):

SELECT [Link], bb.member_id

FROM borrowed_books bb

JOIN books b

ON bb.book_id = b.book_id

WHERE bb.return_date IS NULL;

 Find the most borrowed book:

SELECT book_id, COUNT(*) AS borrow_count

FROM borrowed_books

GROUP BY book_id

ORDER BY borrow_count DESC

LIMIT 1;

Get the most active borrowers:

SELECT member_id, COUNT(*) AS borrow_count

FROM borrowed_books

GROUP BY member_id

ORDER BY borrow_count DESC

LIMIT 3;

Hands-on Exercises

Solve real-world SQL challenges using joins, subqueries, and aggregations.

Optimize SQL queries for performance and efficiency.

Work with NULL values, date filters, and grouped calculations.

Gain confidence in writing complex SQL queries.

Working with Views

What is a View in SQL?
A View is a virtual table that displays data based on a SELECT query. Unlike a regular table, a
View does not store data itself but dynamically pulls data from underlying tables.

Syntax for Creating a View

CREATE VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

The View will now behave like a table when queried.

Benefits of Using Views

Simplifies Complex Queries: Encapsulates long and complex queries into a single
virtual table.

Enhances Security: Restricts access to specific columns or rows by exposing only the
necessary data.

Improves Maintainability: Updates in the base tables automatically reflect in the

View.

Ensures Data Consistency: Provides a standardized way to access data without

modifying raw tables.

Types of Views

Simple Views: Based on a single table, without aggregations.

Complex Views: Based on multiple tables using Joins, Aggregations, or Subqueries.

Updatable Views: Allows users to insert, update, or delete data (with some
restrictions).

Read-Only Views: Prevents modifications to underlying data.

Example 1: Creating a Simple View

Scenario:

A company wants to allow employees to see only basic customer details without revealing
sensitive information.

Customers Table

customer_id name email phone address

1 John john@[Link] 12345678 New York

2 Alice alice@[Link] 23456789 Los Angeles

3 Bob bob@[Link] 34567890 Chicago

Create a View to Show Only Customer Names & Emails

CREATE VIEW customer_contacts AS

SELECT name, email

FROM customers;

Now, employees can retrieve customer information without accessing sensitive

details like phone numbers or addresses.

Query the View

SELECT * FROM customer_contacts;

 Result

name email

John john@[Link]

Alice alice@[Link]

Bob bob@[Link]

No phone numbers or addresses are exposed!

Example 2: Creating a Complex View with Joins

Scenario:

A company wants to generate a report that combines employee names and department
details without requiring complex joins every time.

Employees Table

emp_id name dept_id

1 John 101

2 Alice 102

3 Bob 101

Departments Table

dept_id dept_name

101 HR
 dept_id dept_name

102 IT

Create a View for Employee-Department Report

CREATE VIEW employee_department AS

SELECT [Link] AS employee_name, d.dept_name

FROM employees e

JOIN departments d

ON e.dept_id = d.dept_id;

Now, instead of writing a join every time, you can simply query the View.

Query the View

SELECT * FROM employee_department;

Result

employee_name dept_name

John HR

Alice IT

Bob HR

The View simplifies retrieving employee-department relationships!

Updating Data Through Views

If a View is based on a single table without aggregations or joins, you can INSERT,
UPDATE, or DELETE data.

If the View is complex (e.g., uses joins, subqueries, or aggregations), it may be read-
only.

Updating a Simple View

UPDATE customer_contacts

SET email = 'newemail@[Link]'

WHERE name = 'John';

This updates John’s email in the original customers table.

Updating a Complex View (Not Allowed)

UPDATE employee_department

SET dept_name = 'Finance'

WHERE employee_name = 'John';

Error: Updates on a View with Joins are not allowed.

Modifying & Deleting Views

Modify an Existing View

CREATE OR REPLACE VIEW customer_contacts AS

SELECT name, email, phone

FROM customers;
 This updates the View to include phone numbers.

Delete a View

DROP VIEW customer_contacts;

The View is removed, but the original table remains unchanged.

Hands-on Exercises

 Create a View for retrieving active orders from an e-commerce database.

 Design a View that hides sensitive employee details but keeps department information.
 Test whether a View is updatable or read-only by attempting an update.
 Modify a View to include additional columns from a base table.

Indexing for Performance Optimization

What is an Index in SQL?
An index is a database object that improves the speed of data
retrieval by creating a sorted structure for table columns.
Think of an index like a book’s table of contents – instead of
scanning every page, you can quickly find the topic you need.
Without an index, SQL must scan every row to find data (Full
Table Scan), which slows down performance.

How Indexing Works

When you create an index on a column, the database:
Sorts the data in that column.
Creates a B-tree or Hash-based structure for quick
lookups.
Uses the index whenever a query searches for indexed
values.

Example: Full Table Scan vs. Indexed Search

Without an Index (Slow Search)
SELECT * FROM customers WHERE email = 'john@[Link]';

The database scans every row in the customers table to

find the email.
With an Index (Fast Search)
CREATE INDEX idx_email ON customers(email);
SELECT * FROM customers WHERE email = 'john@[Link]';

Now, the database uses the index to locate the

email instantly instead of scanning every row.

Types of Indexes

 Single-Column Index
 Indexes a single column for faster lookups.
CREATE INDEX idx_lastname ON employees(last_name);

Best for: Searching, filtering, and sorting on one column.

 Composite (Multi-Column) Index

Indexes multiple columns together for multi-condition

queries.
CREATE INDEX idx_emp_dept ON employees(last_name,
department_id);

Best for: Queries using multiple WHERE conditions.

 Unique Index

Ensures values are unique and speeds up searches.

CREATE UNIQUE INDEX idx_email ON customers(email);

Best for: Email, username, or ID fields that must be

unique.

 Full-Text Index

Optimizes searches for large text fields (e.g., product

descriptions).
CREATE FULLTEXT INDEX idx_description ON
products(description);

Best for: Searching within text data (e.g., articles,

comments).

 Clustered Index

Defines physical storage order of table rows.

A table can have only ONE clustered index.
CREATE CLUSTERED INDEX idx_id ON employees(emp_id);

Best for: Primary keys & columns frequently used in

sorting.

 Non-Clustered Index

A separate structure storing only pointers to data

locations.
CREATE NONCLUSTERED INDEX idx_salary ON
employees(salary);

Best for: Fast lookups without affecting row storage

order.

Indexing Best Practices

 Index frequently searched columns: Use indexes on
columns used in WHERE, ORDER BY, and JOIN conditions.
Use Composite Indexes wisely: If queries always filter
by last_name and department_id, create an index on both.
Avoid indexing too many columns: Too many
indexes increase storage and slow down INSERT/UPDATE
operations.
Drop unused indexes: If an index is rarely used, it’s
better to remove it.
Use Unique Indexes for constraints: They prevent
duplicate values and speed up lookups.

Practical Examples

Example 1: Speeding Up a Customer Search

Scenario: A business frequently searches for customers by last
name. Without an index, searches are slow.
Solution: Create an Index
CREATE INDEX idx_lastname ON customers(last_name);

This makes searches like the one below much faster:

SELECT * FROM customers WHERE last_name = 'Smith';
 Example 2: Optimizing a Multi-Column Search
Scenario: A company frequently filters employees
by department and job title.

Without an Index (Slow)

SELECT * FROM employees WHERE department = 'IT' AND
job_title = 'Manager';

This results in a Full Table Scan every time.

With a Composite Index (Fast)

CREATE INDEX idx_dept_job ON employees(department,
job_title);

The query now runs significantly faster because SQL can

use the index.

Example 3: Using a Unique Index

Scenario: A website needs to prevent duplicate emails in
the users table.
Solution: Create a Unique Index
CREATE UNIQUE INDEX idx_email ON users(email);
 This ensures that duplicate emails cannot be inserted:
INSERT INTO users (email) VALUES ('test@[Link]'); --
Success
INSERT INTO users (email) VALUES ('test@[Link]'); --
Error: Duplicate email!

Removing an Index
If an index is not improving performance or causing slow
updates, remove it:
DROP INDEX idx_email ON customers;

Hands-on Exercises
 Create a single-column index on a frequently searched column.
 Design a composite index for a multi-condition query.
 Create a unique index on an email or username field.
 Test query performance before and after
indexing using EXPLAIN or ANALYZE.
 Drop an unnecessary index and observe the impact.

Stored Procedures & Functions

What are Stored Procedures in SQL?
A Stored Procedure is a reusable block of SQL statements stored in the database
that can be executed by calling its name.

Why use Stored Procedures?

 Encapsulation of Logic: Combines multiple SQL statements into a single

callable function.

 Performance Improvement: Precompiled and optimized for execution.

 Security: Limits access to underlying tables by providing controlled access

through stored procedures.

 Code Reusability: Avoids repetitive SQL queries by defining logic once and
reusing it.

Creating a Stored Procedure

Basic Syntax:

CREATE PROCEDURE procedure_name

AS

BEGIN

-- SQL Statements

END;

Example 1: Creating a Simple Stored Procedure

Scenario: A company wants to retrieve all employees from the database without
writing SELECT * FROM employees every time.

Create a Procedure
CREATE PROCEDURE GetAllEmployees

AS

BEGIN

SELECT * FROM employees;

END;

Execute the Procedure

EXEC GetAllEmployees;

The procedure executes and retrieves all employee records from

the employees table.

Using Parameters in Stored Procedures

Stored procedures can accept input parameters to perform operations

dynamically.

Syntax for Parameterized Stored Procedure:

CREATE PROCEDURE procedure_name (@param_name datatype)

AS

BEGIN

-- SQL Statements using @param_name

END;

Example 2: Procedure with Input Parameters

Scenario: A manager wants to retrieve employees by department without

writing different queries.
 Create a Procedure with a Parameter

CREATE PROCEDURE GetEmployeesByDepartment

@dept_id INT

AS

BEGIN

SELECT * FROM employees WHERE department_id = @dept_id;

END;

Execute the Procedure

EXEC GetEmployeesByDepartment @dept_id = 2;

The procedure fetches only employees from Department ID 2.

Updating & Deleting Data with Stored Procedures

Example 3: Procedure to Update Employee Salary

Scenario: A company wants to update an employee’s salary securely using a

stored procedure.

Create the Procedure

CREATE PROCEDURE UpdateEmployeeSalary

@emp_id INT,

@new_salary DECIMAL(10,2)

AS
BEGIN

UPDATE employees

SET salary = @new_salary

WHERE emp_id = @emp_id;

END;

Execute the Procedure

EXEC UpdateEmployeeSalary @emp_id = 5, @new_salary = 75000.00;

This updates Employee ID 5’s salary to $75,000.

Deleting a Stored Procedure

DROP PROCEDURE GetAllEmployees;

The procedure is permanently removed from the database.

What are Functions in SQL?

A Function in SQL is similar to a stored procedure but always returns a

value and cannot modify data.

Why use Functions?

 Reusable logic for calculations and transformations.

 Always returns a value (unlike procedures).

 Can be used inside SELECT queries.

Types of SQL Functions

 Scalar Functions – Returns a single value.

Table-Valued Functions – Returns a table.

Creating a Function in SQL

Syntax:

CREATE FUNCTION function_name (@param datatype)

RETURNS return_datatype

AS

BEGIN

-- SQL Statements

RETURN value;

END;

Example 4: Creating a Scalar Function

Scenario: A company wants to calculate the annual salary of an employee using

a function.

Create a Function

CREATE FUNCTION CalculateAnnualSalary (@monthly_salary DECIMAL(10,2))

RETURNS DECIMAL(10,2)

AS

BEGIN

RETURN @monthly_salary * 12;

END;

Use the Function in a Query

SELECT emp_id, name, salary, [Link](salary) AS

annual_salary

FROM employees;

This calculates annual salaries dynamically for all employees.

Example 5: Creating a Table-Valued Function

Scenario: A company wants to get employees with a salary above a certain

amount using a function.

Create a Table-Valued Function

CREATE FUNCTION GetHighSalaryEmployees (@min_salary DECIMAL(10,2))

RETURNS TABLE

AS

RETURN (

SELECT * FROM employees WHERE salary > @min_salary

);

Use the Function in a Query

SELECT * FROM [Link](60000);

This fetches all employees earning more than $60,000.

Differences: Stored Procedures vs. Functions

Feature Stored Procedure Function

No (unless using Yes

Returns a Value?
OUTPUT) (Always)

Yes (INSERT, UPDATE,

Modifies Data? No
DELETE)

Can Be Used in SELECT? No Yes

Can Have Input

Yes Yes
Parameters?

Can Call Other

Yes No
Functions?

Best Practices for Using Stored Procedures & Functions

 Use Stored Procedures for data modification and complex business logic.
 Use Functions for calculations and data transformations.
 Avoid modifying data inside Functions (use procedures for that).
 Use parameterized procedures to enhance security and reusability.
 Always test before deploying to a production environment.

Hands-on Exercises

 Create a stored procedure that inserts new employee data.

 Write a procedure with input parameters for retrieving sales reports.
 Design a function that calculates total sales for a given month.
 Optimize an existing query by replacing it with a stored procedure.
 Drop a stored procedure and a function, then recreate them.
Triggers & Events
What are Triggers in SQL?

A Trigger is a special type of stored procedure that executes automatically when

an event (INSERT, UPDATE, or DELETE) happens in a table.

Why Use Triggers?

 Enforce business rules (e.g., prevent negative account balances).

 Maintain audit logs (track changes in records).

 Automatically update related data (e.g., update stock when an order is

placed).

Types of Triggers

Type of
Description
Trigger

Executes after an INSERT, UPDATE, or DELETE

AFTER Trigger
operation.

BEFORE Executes before an INSERT, UPDATE, or DELETE

Trigger operation.

INSTEAD OF Replaces the default action of INSERT, UPDATE, or

Trigger DELETE with a custom action.

Creating a Trigger in SQL

Basic Syntax:
CREATE TRIGGER trigger_name

ON table_name

AFTER | BEFORE | INSTEAD OF INSERT | UPDATE | DELETE

AS

BEGIN

-- SQL Statements

END;

Example 1: Creating an AFTER INSERT Trigger

Scenario: A company wants to keep an audit log whenever a new employee is

added.

Create the Audit Log Table

CREATE TABLE employee_audit (

audit_id INT PRIMARY KEY IDENTITY,

emp_id INT,

action VARCHAR(50),

action_time DATETIME DEFAULT CURRENT_TIMESTAMP

);

Create the Trigger

CREATE TRIGGER trg_AfterEmployeeInsert

ON employees
AFTER INSERT

AS

BEGIN

INSERT INTO employee_audit (emp_id, action)

SELECT emp_id, 'INSERTED' FROM inserted;

END;

Test the Trigger

INSERT INTO employees (emp_id, name, department)

VALUES (101, 'John Doe', 'IT');

The trigger automatically adds an entry in employee_audit when a new

employee is added.

Example 2: Creating an AFTER UPDATE Trigger

Scenario: A company wants to track salary changes for employees.

Create the Trigger

CREATE TRIGGER trg_AfterSalaryUpdate

ON employees

AFTER UPDATE

AS

BEGIN

INSERT INTO employee_audit (emp_id, action)

 SELECT emp_id, 'SALARY UPDATED' FROM inserted;

END;

Test the Trigger

UPDATE employees SET salary = 80000 WHERE emp_id = 101;

The trigger records the update event in the audit table.

Example 3: Preventing Deletion with an INSTEAD OF Trigger

Scenario: A company does not want employees to be deleted but should mark
them as inactive instead.

Create the Trigger

CREATE TRIGGER trg_InsteadOfDelete

ON employees

INSTEAD OF DELETE

AS

BEGIN

UPDATE employees SET status = 'Inactive' WHERE emp_id IN (SELECT emp_id

FROM deleted);

END;

Test the Trigger

DELETE FROM employees WHERE emp_id = 101;

 Instead of deleting the employee, the trigger sets their status to
‘Inactive’.

Removing a Trigger

DROP TRIGGER trg_AfterEmployeeInsert;

The trigger is permanently removed.

What are SQL Events?

An Event is a scheduled SQL task that runs at a specified time automatically.

Why Use Events?

 Automate maintenance tasks (e.g., deleting old records).

 Schedule reports (e.g., daily sales reports).

 Perform periodic data updates (e.g., recalculating statistics).

Enabling SQL Events

SET GLOBAL event_scheduler = ON;

Creating an Event in SQL

Basic Syntax:

CREATE EVENT event_name

ON SCHEDULE AT 'YYYY-MM-DD HH:MI:SS' | EVERY interval

DO
 -- SQL Statements

Example 4: Deleting Old Records Automatically

Scenario: A company wants to automatically delete old logs every day.

Create the Event

CREATE EVENT DeleteOldLogs

ON SCHEDULE EVERY 1 DAY

DO

BEGIN

DELETE FROM employee_audit WHERE action_time < NOW() - INTERVAL 30

DAY;

END;

This event runs daily and removes logs older than 30 days.

Example 5: Sending Monthly Reports Automatically

Scenario: A company wants to generate monthly sales reports automatically.

Create the Event

CREATE EVENT GenerateMonthlySalesReport

ON SCHEDULE EVERY 1 MONTH

DO

BEGIN
 INSERT INTO reports (report_name, created_at)

VALUES ('Monthly Sales Report', NOW());

END;

This event runs every month and creates a new report entry.

Modifying or Dropping Events

Modify an Event:

ALTER EVENT DeleteOldLogs

ON SCHEDULE EVERY 7 DAY;

Changes the event to run weekly instead of daily.

Drop an Event:

DROP EVENT DeleteOldLogs;

Permanently removes the event.

Best Practices for Using Triggers & Events

 Use Triggers for enforcing business rules like auditing or preventing invalid
deletions.
 Use Events for scheduling repetitive tasks like backups, reports, and
maintenance.
 Avoid excessive triggers, as they can slow down performance if overused.
 Ensure events don’t interfere with daily operations by scheduling them
during non-peak hours.
 Always test triggers and events before implementing them in production.
Hands-on Exercises

 Create a trigger that prevents inserting employees with a negative salary.

 Design a trigger that automatically logs changes in order status.
 Implement an event that deletes old sales records every 60 days.
 Modify an existing event to run every week instead of daily.
 Drop an unused trigger from the database.

Transactions & Error Handling

What is a Transaction in SQL?

A Transaction is a sequence of one or more SQL statements that execute as a

single unit.

 If all statements succeed, the transaction is committed (saved

permanently).

 If any statement fails, the transaction is rolled back (canceled).

Example Scenario: Bank Transfer

Imagine a customer transfers money from one account to another.

 Step 1: Deduct money from Account A.

 Step 2: Add money to Account B.

 Step 3: If both succeed → COMMIT (Save Changes).

 Step 4: If either fails → ROLLBACK (Cancel Transaction).

Understanding ACID Properties

ACID ensures that transactions execute reliably in databases.

 ACID
Description
Property

All operations must succeed or none at all (all-or-

Atomicity
nothing).

The database remains in a valid state before & after a

Consistency
transaction.

Multiple transactions do not interfere with each

Isolation
other.

Once committed, changes persist even after system

Durability
failures.

SQL Transaction Control Commands

Command Description

BEGIN
Marks the start of a transaction.
TRANSACTION

COMMIT Saves the transaction permanently.

ROLLBACK Cancels the transaction if an error occurs.

Creates a temporary rollback point in a

SAVEPOINT
transaction.

Using Transactions in SQL

 Example 1: Implementing a Simple Transaction

Scenario: A customer transfers $500 from Account A (ID: 101) to Account B (ID:
202).

BEGIN TRANSACTION;

UPDATE accounts

SET balance = balance - 500

WHERE account_id = 101; -- Deduct from Account A

UPDATE accounts

SET balance = balance + 500

WHERE account_id = 202; -- Add to Account B

COMMIT;

If both updates succeed, the transaction commits (saves).

Example 2: Handling Errors with ROLLBACK

Scenario: If Account A has insufficient balance, ROLLBACK should cancel the

transaction.

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 500

WHERE account_id = 101; -- Deduct from Account A

IF @@ERROR <> 0 -- Check if an error occurred

BEGIN

ROLLBACK;

PRINT 'Transaction failed: Insufficient funds';

RETURN;

END

UPDATE accounts

SET balance = balance + 500

WHERE account_id = 202; -- Add to Account B

COMMIT;

If Account A does not have enough funds, the transaction rolls back.

Using SAVEPOINT for Partial Rollbacks

Savepoints allow rolling back a specific part of a transaction without canceling

everything.

Example 3: Using SAVEPOINT

Scenario: A company updates employee salaries but wants the option to
rollback only a specific department.

BEGIN TRANSACTION;

UPDATE employees

SET salary = salary * 1.10

WHERE department = 'IT';

SAVEPOINT it_salary_update; -- Create a rollback point

UPDATE employees

SET salary = salary * 1.05

WHERE department = 'HR';

ROLLBACK TO it_salary_update; -- Undo only HR salary changes

COMMIT;

The IT department salary update remains, while HR salaries rollback.

Error Handling in SQL Using TRY…CATCH

TRY…CATCH blocks help handle errors and prevent unexpected failures.

Example 4: Using TRY…CATCH for Error Handling

Scenario: A system logs errors when transactions fail.

BEGIN TRANSACTION;

BEGIN TRY

UPDATE employees

SET salary = salary + 500

WHERE emp_id = 5;

COMMIT;

PRINT 'Transaction Successful';

END TRY

BEGIN CATCH

ROLLBACK;

PRINT 'Error Occurred: Transaction Rolled Back';

END CATCH;

If no errors occur, the transaction commits.

If an error occurs, the transaction rolls back and prints an error message.

Using ERROR Functions in TRY…CATCH

SQL provides built-in error functions to capture error details.

 Function Description

ERROR_MESSAGE() Returns the error message.

ERROR_NUMBER() Returns the error code.

ERROR_SEVERITY() Returns the severity level of the error.

Example 5: Logging Errors to a Table

Scenario: If an error occurs, log it into an error_log table.

Create the Error Log Table

CREATE TABLE error_log (

error_id INT PRIMARY KEY IDENTITY,

error_message VARCHAR(255),

error_time DATETIME DEFAULT CURRENT_TIMESTAMP

);

Implement Error Logging

BEGIN TRANSACTION;

BEGIN TRY

UPDATE employees

SET salary = salary + 1000

WHERE emp_id = 7;
 COMMIT;

PRINT 'Transaction Successful';

END TRY

BEGIN CATCH

ROLLBACK;

INSERT INTO error_log (error_message)

VALUES (ERROR_MESSAGE());

PRINT 'Error Logged and Transaction Rolled Back';

END CATCH;

If an error occurs, it rolls back and logs the error.

Best Practices for Transactions & Error Handling

 Always use transactions when modifying multiple tables.

 Use COMMIT only when all operations are successful.
 Implement ROLLBACK for error handling to prevent data corruption.
 Use TRY…CATCH for structured error handling.
 Log errors to troubleshoot issues later.

Hands-on Exercises

 Create a transaction that transfers money between two accounts.

  Write a TRY…CATCH block that logs failed transactions.
 Implement a SAVEPOINT and roll back only part of a transaction.
 Modify an existing transaction to handle insufficient funds errors.
 Drop a transaction error log table and recreate it with improvements.

Working with JSON & XML in SQL

Understanding JSON & XML in Databases

JSON and XML are widely used formats for storing, exchanging, and processing
data.

Format Features

Lightweight, human-readable, commonly used in APIs and

JSON
web apps.

Structured, tag-based format, widely used in enterprise

XML
systems and document storage.

Many modern databases, including MySQL, PostgreSQL, SQL Server, and Oracle,
support native JSON/XML data types and functions.

Working with JSON in SQL

Storing JSON Data in a SQL Table

Most databases allow storing JSON in a TEXT, VARCHAR, or native JSON data
type.

Example: Creating a Table with a JSON Column

CREATE TABLE customers (

 id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(100),

contact_details JSON -- JSON column

);

This allows storing structured JSON objects directly in the database.

Inserting JSON Data into the Table

INSERT INTO customers (name, contact_details)

VALUES ('John Doe',

'{

"email": "john@[Link]",

"phone": "+1234567890"

}'

);

Querying JSON Data

Extracting Data from JSON Columns

Most databases provide JSON functions to extract specific values.

Example: Retrieving a Customer’s Email

SELECT name, contact_details->>'$.email' AS email

FROM customers;
 This extracts the email field from the JSON object.

Example: Filtering Data Based on JSON Fields

SELECT * FROM customers

WHERE contact_details->>'$.phone' = '+1234567890';

This retrieves customers based on their phone number stored in JSON.

Modifying JSON Data in SQL

Updating a JSON Field

UPDATE customers

SET contact_details = JSON_SET(contact_details, '$.email',

'newemail@[Link]')

WHERE id = 1;

JSON_SET() updates the email field inside the JSON object.

Converting Relational Data to JSON

Example: Converting SQL Query Results to JSON

SELECT id, name, JSON_OBJECT('email', contact_details->>'$.email') AS

json_data

FROM customers;

This formats the result into JSON format, useful for APIs.
Working with XML in SQL

Storing XML Data in a SQL Table

XML data can be stored in TEXT, VARCHAR, or XML-specific data types.

Example: Creating a Table with an XML Column

CREATE TABLE orders (

order_id INT PRIMARY KEY AUTO_INCREMENT,

order_details XML

);

Inserting XML Data

INSERT INTO orders (order_details)

VALUES ('<order><product>Phone</product><price>599</price></order>');

Querying XML Data

Extracting Data from XML Columns

SELECT order_details.value('(/order/product)[1]', 'VARCHAR(50)') AS

product_name

FROM orders;

Extracts the product name from the XML structure.

Converting Relational Data to XML

 Example: Converting SQL Query Results to XML

SELECT order_id, order_details

FROM orders

FOR XML AUTO;

Converts SQL query results into XML format.

JSON vs. XML: When to Use What?

Feature JSON XML

Readability Easy to read More complex

Performance Faster Slower

APIs, Web Enterprise Systems,

Use Case
Apps Configuration Files

No strict
Schema Supports schema validation
schema

Best Practices for JSON & XML in SQL

 Use native JSON/XML functions for better performance.

 Index JSON/XML columns if queries frequently filter on them.
 Use JSON for web & API-based applications and XML for structured
enterprise systems.
 Avoid storing large JSON/XML data in SQL tables—consider NoSQL
databases for heavy document storage.
Hands-on Exercises

 Create a table with a JSON column and insert sample data.

 Query specific fields from JSON using SQL functions.
 Update nested JSON fields in a SQL record.
 Convert relational data to JSON/XML for API responses.
 Use XML functions to extract structured data from XML columns.

Common Table Expressions (CTE) & Recursive Queries

What is a Common Table Expression (CTE)?

A CTE is a temporary named result set that exists only during the execution of a
query.

 It helps in breaking down complex queries into smaller, readable parts.

 CTEs are self-contained, meaning they don’t affect the database structure.

Syntax of a CTE

WITH cte_name AS (

SELECT column1, column2

FROM table_name

WHERE condition

SELECT * FROM cte_name;

The WITH clause defines the CTE, which can be used like a table in
a SELECT statement.

Example: Using a CTE for Better Readability

Imagine you need to find employees with high salaries from a database, and
then use the result for further calculations.

WITH HighSalaryEmployees AS (

SELECT emp_id, name, department, salary

FROM employees

WHERE salary > 50000

SELECT name, department, salary

FROM HighSalaryEmployees

ORDER BY salary DESC;

This CTE makes the query more readable and prevents nested
subqueries.

Benefits of CTEs over Subqueries

Feature CTE Subquery

Complex and
Readability Easier to understand
nested

Can be used multiple times

Reusability Not reusable
in the same query

Debugging Easy to debug Difficult to debug

Performance Optimized by the query May execute

 Feature CTE Subquery

engine multiple times

Recursive CTEs: Handling Hierarchical Data

What is a Recursive CTE?

A recursive CTE calls itself until a termination condition is met.

Used to traverse hierarchical relationships, such as employee reporting

structures, categories in an e-commerce store, or family trees.

Recursive CTE Syntax

WITH RecursiveCTE AS (

-- Anchor query (Base case)

SELECT column1, column2

FROM table_name

WHERE condition

UNION ALL

-- Recursive query (Self-referencing)

SELECT column1, column2

FROM table_name

JOIN RecursiveCTE ON table_name.column = [Link]

)

SELECT * FROM RecursiveCTE;

The anchor query selects the starting point.

The recursive query calls itself, processing hierarchical levels.

Example: Employee Hierarchy (Manager & Subordinates)

Imagine a company’s employee table with managers and subordinates.

Employee Table

emp_id name manager_id

1 Alice NULL

2 Bob 1

3 Carol 1

4 Dave 2

5 Eve 3

We want to retrieve all employees under a specific manager recursively.

WITH EmployeeHierarchy AS (

-- Base case: Start with top-level manager (Alice)

SELECT emp_id, name, manager_id, 1 AS level

FROM employees
 WHERE manager_id IS NULL

UNION ALL

-- Recursive case: Find subordinates of managers

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

FROM employees e

JOIN EmployeeHierarchy eh ON e.manager_id = eh.emp_id

SELECT * FROM EmployeeHierarchy;

The first part selects Alice (the top-level manager).

The recursive query finds Alice’s subordinates, then their subordinates,

and so on.

Output (Employee Hierarchy Result)

emp_id name manager_id level

1 Alice NULL 1

2 Bob 1 2

3 Carol 1 2

4 Dave 2 3
 emp_id name manager_id level

5 Eve 3 3

Levels represent hierarchy depth in the organization.

Another Use Case: Category Hierarchy in E-commerce

A database stores product categories with parent-child relationships.

category_id category_name parent_id

1 Electronics NULL

2 Mobiles 1

3 Laptops 1

4 Apple 2

5 Samsung 2

Query to Fetch Category Hierarchy

WITH CategoryHierarchy AS (

-- Base case: Root categories

SELECT category_id, category_name, parent_id, 1 AS level

FROM categories

WHERE parent_id IS NULL

 UNION ALL

-- Recursive case: Find subcategories

SELECT c.category_id, c.category_name, c.parent_id, [Link] + 1

FROM categories c

JOIN CategoryHierarchy ch ON c.parent_id = ch.category_id

SELECT * FROM CategoryHierarchy;

This recursively finds subcategories under a parent category.

Performance Considerations for Recursive CTEs

Limit recursion depth to avoid infinite loops.

OPTION (MAXRECURSION 5);

Index columns used in JOIN conditions for better performance.

Avoid unnecessary recursion; use non-recursive CTEs when possible.

Hands-on Exercises

 Create a CTE to simplify a complex query.

 Implement a recursive CTE to find employees under a manager.
 Write a recursive CTE to navigate a category hierarchy.
 Optimize recursive queries by setting a recursion depth limit.
Window Functions for Analytical Queries
What Are Window Functions?

A window function performs a calculation across a subset of rows related to the

current row.
Unlike GROUP BY, window functions keep individual row details while adding an
additional computed column.

Syntax of Window Functions:

function_name() OVER (

[PARTITION BY column_name]

[ORDER BY column_name]

PARTITION BY: Divides the data into separate groups (optional).

ORDER BY: Defines row ordering within each partition.

Difference Between Aggregate & Window Functions

Aggregate Functions Window Functions

Feature
(SUM, AVG, etc.) (SUM() OVER, etc.)

Collapses rows into a Keeps individual rows

Row Visibility
single value visible

Grouping Uses GROUP BY Uses PARTITION BY

Calculation Computes over entire Computes over a subset

 Aggregate Functions Window Functions
Feature
(SUM, AVG, etc.) (SUM() OVER, etc.)

Scope group of rows

Example: Aggregate vs. Window Function

Aggregate Function (Collapses Rows)

SELECT department, AVG(salary)

FROM employees

GROUP BY department;

Returns one row per department with the average salary.

Window Function (Keeps Rows & Adds Computation)

SELECT name, department, salary,

AVG(salary) OVER (PARTITION BY department) AS avg_salary

FROM employees;

Each row retains its details, while showing the department’s average
salary as an additional column.

1. ROW_NUMBER(), RANK(), DENSE_RANK()

These functions assign a ranking to each row based on ORDER BY criteria.

Example: Ranking Employees by Salary

SELECT name, department, salary,

 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_num

FROM employees;

name department salary row_num rank_num dense_rank_num

Alice IT 90000 1 1 1

Bob IT 85000 2 2 2

Carol IT 85000 3 2 2

Dave IT 80000 4 4 3

How They Differ?

 ROW_NUMBER(): Assigns unique numbers (no ties).

 RANK(): Assigns the same rank to duplicates, skipping numbers.

 DENSE_RANK(): Assigns the same rank to duplicates but without skipping.

2. LEAD() and LAG() – Accessing Previous/Next Rows

These functions help in comparing values across previous or next rows.

Example: Finding Salary Difference with Previous Employee

SELECT name, department, salary,

LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS

prev_salary,

LEAD(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS

next_salary

FROM employees;

name department salary prev_salary next_salary

Alice IT 80000 NULL 85000

Bob IT 85000 80000 90000

Carol IT 90000 85000 NULL

LAG() looks backward to fetch the previous row’s salary.

LEAD() looks forward to fetch the next row’s salary.

3. SUM(), AVG(), COUNT() – Running Totals & Moving Averages

Example: Running Total of Sales

SELECT sales_id, product, amount,

SUM(amount) OVER (ORDER BY sales_id ROWS BETWEEN UNBOUNDED

PRECEDING AND CURRENT ROW) AS running_total

FROM sales;
 sales_id product amount running_total

1 Laptop 500 500

2 Phone 300 800

3 Tablet 200 1000

Computes a cumulative total of sales without grouping data.

4. NTILE() – Dividing Data into Equal Parts

Splits rows into N equal groups, useful for percentile ranking.

Example: Dividing Employees into 4 Salary Groups

SELECT name, salary,

NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile

FROM employees;

name salary salary_quartile

Alice 90000 1

Bob 85000 1

Carol 80000 2

Dave 75000 2
 name salary salary_quartile

Eve 70000 3

NTILE(4) splits the dataset into 4 groups, ranking employees by salary

percentiles.

Performance Considerations

Indexes on ORDER BY columns improve performance.

Avoid unnecessary window functions in large datasets.

Use PARTITION BY wisely to optimize calculations.

Hands-on Exercises

Use ROW_NUMBER(), RANK(), DENSE_RANK() to rank employees.

Implement LAG() and LEAD() to compare sales trends.

Apply SUM() OVER() for running totals.

Use NTILE(4) to divide sales data into quartiles.

Query Optimization Techniques

1. Understanding Query Execution & Optimization

When a SQL query runs, the database engine follows these steps:

1. Parsing → Checks query syntax

 2. Optimization → Finds the best way to execute the query

3. Execution → Runs the optimized query

A slow query often results from:

Missing indexes

Poorly written joins

Fetching too much data

Inefficient filtering

Goal of Query Optimization: Reduce query execution time and minimize

resource usage.

2. Analyzing Queries with EXPLAIN & EXPLAIN ANALYZE

The EXPLAIN statement shows how the database plans to execute a query,
helping identify bottlenecks.

Example: Analyzing a Query Execution Plan

EXPLAIN SELECT * FROM employees WHERE department = 'IT';

Check for “Full Table Scan” (BAD for large tables).

Using EXPLAIN ANALYZE for Detailed Insights

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'IT';

Shows actual execution time instead of just the plan.

Key Metrics to Look For:

 Sequential Scan (Bad for large tables)

 Index Scan (Good for optimized queries)

  Filter Conditions (Ensure filtering is using indexes)

3. Indexing for Performance Improvement

Indexes speed up queries by reducing the number of rows scanned.

Types of Indexes

Index Type Description Best For

Primary Automatically created Unique row

Index on primary key identification

Composite Queries with multiple

Multi-column index
Index WHERE conditions

Full-text Optimized for text Searching inside large

Index search text fields

Unique Ensures uniqueness in

Enforcing uniqueness
Index columns

Example: Creating an Index for Faster Search

CREATE INDEX idx_department ON employees(department);

Now, WHERE department = 'IT' uses the index, making queries faster.

4. Optimizing Joins for Better Performance

Joins are often the slowest part of queries.

 Best Practices for Optimizing Joins

Use INNER JOIN instead of LEFT JOIN (when possible).

Ensure indexed columns are used in JOIN conditions.

Avoid joining unnecessary tables.

Example: Using Indexes in Joins

SELECT [Link], d.department_name

FROM employees e

JOIN departments d

ON e.department_id = [Link];

Ensure employees.department_id and [Link] have indexes.

5. Avoiding Full Table Scans

A full table scan occurs when:

No index is used

LIKE '%text%' is used (causes full scan)

Functions are applied on indexed columns

Example: Avoiding Function-Based Scans

SELECT * FROM users WHERE YEAR(created_at) = 2023; -- BAD

 The function YEAR() makes indexing useless.

Better Approach:

SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-

31'; -- GOOD

Now, the query can use indexing for faster results.

6. Using LIMIT & OFFSET Efficiently

Avoid using large OFFSET values (slow pagination).

Better: Use indexed WHERE conditions instead of OFFSET.

Example: Efficient Pagination

SELECT * FROM employees WHERE id > 100 LIMIT 10; -- GOOD

Uses indexed id column, reducing unnecessary scans.

7. Query Rewriting for Efficiency

Use EXISTS Instead of IN for Large Datasets

SELECT * FROM employees WHERE department_id IN (SELECT id FROM

departments); -- BAD

Better Approach (EXISTS is faster for large tables):

SELECT * FROM employees e WHERE EXISTS

(SELECT 1 FROM departments d WHERE [Link] = e.department_id); -- GOOD

Use UNION ALL Instead of UNION

SELECT name FROM employees

UNION

SELECT name FROM managers; -- BAD (Removes duplicates, slower)

Better Approach (No Duplicate Checking):

SELECT name FROM employees

UNION ALL

SELECT name FROM managers; -- GOOD

8. Caching & Materialized Views for Heavy Queries

Using Materialized Views (For Read-Heavy Queries)

Instead of running expensive queries repeatedly, store the results in a

materialized view.

CREATE MATERIALIZED VIEW top_salaries AS

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 10;

Improves reporting performance.

Hands-on Exercises
  Analyze slow queries using EXPLAIN ANALYZE.
 Create an index to improve a slow query.
 Optimize a JOIN to use indexed columns.
 Rewrite a subquery using EXISTS for efficiency.
 Implement pagination without using large OFFSET values.

Final Project Kickoff

1. Overview of the Final Project

Project Theme: Real-World SQL Database Development

Trainees will create a fully functional SQL database for a real-world application,
including:

Database schema design (tables, relationships, constraints)

Data normalization (avoiding redundancy & ensuring integrity)

Stored procedures, functions, and triggers

Optimized queries & performance tuning

Security best practices (user roles & permissions)

2. Defining the Project Scope & Requirements

Step 1: Choose a Project Domain

Trainees will select a business or real-world scenario. Examples:

 E-commerce Database (Products, Customers, Orders, Payments)

 Library Management System (Books, Members, Borrowing History)

  Employee Management System (Employees, Departments, Payroll)

 Hospital Database (Patients, Doctors, Appointments, Billing)

Step 2: Identify Key Functionalities

A structured SQL database project should support:

Data entry & validation (INSERT, UPDATE, DELETE operations)

Complex queries & reports (Aggregation, Joins, Window Functions)

Stored procedures & triggers for automation

User authentication & role-based access control

Step 3: Define the Database Schema

Example for an E-commerce Database:

Table Name Description Key Fields

Stores
user_id
users customer
(PK), name, email, password
details

Stores product product_id

products
information (PK), name, price, stock

Stores
order_id (PK), user_id
orders customer
(FK), order_date
orders

order_item Stores items order_item_id (PK), order_id

s in an order (FK), product_id (FK), quantity
Table Name Description Key Fields

Stores
payment_id (PK), order_id
payments payment
(FK), amount, status
details

3. Setting Up the Development Workflow

Step 1: Break Down the Project into Phases

Phase Tasks

Identify tables, relationships, primary

Phase 1: Schema Design
& foreign keys

Phase 2: Data Insertion Populate tables with test data

Phase 3: Query Write SELECT, JOIN, and aggregation

Development queries

Phase 4: Stored Procedures

Implement automation logic
& Triggers

Phase 5: Optimization & Add indexing, optimize queries,

Security implement security

Phase 6: Final Presentation Prepare documentation and present

& Review the project

Step 2: Assign Individual Tasks

If working in teams, tasks can be divided:

 One person handles schema design

 Another person writes queries and optimizations

 Another focuses on stored procedures and triggers

4. Best Practices for Database Development

Normalization to Avoid Data Redundancy

Ensure the database follows 3rd Normal Form (3NF) principles.

Indexing for Performance

Use INDEX on frequently queried columns (e.g., email, order_id).

Security Measures

 Role-based access control (RBAC) to restrict permissions.

 Avoid direct user input in queries to prevent SQL injection.

5. Hands-on Activities for Today

Define your project topic (E-commerce, Library, Employee Management,

etc.).

Create a database schema diagram (tables, relationships, constraints).

Plan and assign tasks (if working in a team).

Set up a GitHub repository or local SQL environment for development.

Project Title: E-Commerce Order Management System
This project requires you to design, implement, and optimize a database for
an E-Commerce system that manages users, products, orders, payments, and
reports.

Key Objectives:

 Design and create a normalized database schema

 Implement tables, constraints, and relationships
 Write SQL queries for data retrieval and reporting
 Optimize queries using indexing and performance techniques
 Create stored procedures, triggers, and transactions

Tasks to Complete:

 Database Schema Design: Create tables with appropriate constraints and

relationships.
 Data Insertion: Populate tables with test data.
 Data Retrieval & Reports: Write queries to fetch specific data.
 Stored Procedures & Functions: Automate common tasks using SQL
procedures.
 Performance Optimization: Apply indexing and query optimization
techniques.