Advanced

SQL
Simplify. Organize.
Reuse.
Session Overview & Agenda
Agenda:

1. Why Reusable SQL Matters

2. Common Table Expressions (CTEs)

3. Views

4. Stored Procedures

5. Triggers

6. Data Modelling & Normalization

Learning Objectives
By the end of this session, learners will be able to:

● Create and manage CTEs for modular query design.

● Build Views for reusable reporting and security.

● Write and execute Stored Procedures for automation.

● Implement Triggers for enforcing business logic.

● Understand Data Modelling & Normalization for database efficiency.

Why Reusable SQL Matters
✅ Efficiency — reduces repetitive work

✅ Maintainability — makes logic centralized

✅ Security — limits direct table access

✅ Collaboration — enables shared logic among teams

What Is a CTE?
● A Common Table Expression (CTE)
is a temporary result set defined
Syntax :
within a SQL statement.

● It improves readability and

modularity.

Concept: A CTE acts like a temporary

“named subquery” valid during that
execution.
Why Use CTEs?
● Simplifies complex nested queries

● Easier debugging and modular development

● Can be referenced multiple times in one query
● Improves code clarity
Example 1 — Total Sales per Customer (CTE Introduction)

Problem Statement

Your company’s sales team wants to analyze

total purchase amount by each customer.
The dataset contains individual
transactions. Instead of manually grouping
every time, you’ll create a CTE that
summarizes total sales per customer and
then filters customers whose total purchases
exceed ₹5,000.
Explanation

1. Inside the CTE (CustomerSales) → we

calculate each customer’s total sales.

2. The main query selects customers

whose total > ₹5,000.

3. The CTE makes the code clean and

modular — easy to modify or extend.
Example 2 — Multi-Step CTE (Chained Logic)
Problem Statement

The marketing team wants to find high-value customers who have spent more than ₹10,000 and show only
their recent orders.
To solve this, you will use two CTEs:

● First, calculate total sales per customer.

● Second, filter customers who are high-value spenders.

Use the same Orders table from Slide 8 or recreate

with new entries
Explanation

1. CTE 1 (SalesCTE): Aggregates each customer’s total spending.

2. CTE 2 (HighValueCustomers): Filters customers with TotalSales > 10000.

3. The final SELECT joins back to the main table to retrieve recent transactions for those
customers.
CTE vs Subquery
Feature CTE Subquery

Readability High Low

Reusability Reusable in query One-time use

Debugging Easier Harder

Scope One statement One statement

Tip: Use CTEs for cleaner logic in multi-step calculations.

What Is a View?
● A View is a virtual table created by saving a SQL query.

● It doesn’t store data, but references underlying tables.

Syntax:
High Value Customers View
The Sales team needs a reusable object that lists customers whose total purchases exceed
₹5,000 so analysts and reporting tools can query it directly (no need to re-run aggregation logic
each time).
Create the view Use the view

Explanation:

● This view encapsulates the aggregation (SUM + GROUP BY + HAVING) so analysts can simply SELECT from it.

● Because it uses GROUP BY/aggregate, most RDBMS treat it as read-only — you cannot UPDATE this view to
change underlying totals directly.

● Use this pattern for consistent reporting logic across widgets/dashboards.

Updatable vs Read-Only Views
Problem Statement:

The customer support team needs

quick access to update customer
contact details without editing the
main database tables directly.

At the same time, the sales

department uses a view that shows
total sales per customer, which should
remain read-only to prevent accidental
data changes.
Example A — Updatable view (single-base-table view)

Problem: Allow support staff to update Update via view (should work on most RDBMS
customer emails without direct table because the view maps 1:1 to base table):
access.

Output:
Example B — Read-only view (complex/aggregate view)

Problem: Try to update an aggregated view (HighValueCustomers) and see why it fails.
Managing Views & Summary of Views
● ALTER VIEW → modify logic
● DROP VIEW → remove
● INFORMATION_SCHEMA.VIEWS → inspect

Naming Tip: Use meaningful names (e.g., Sales_View, Finance_VW_Quarterly).

❖ Virtual tables for complex logic

❖ Security layer for restricted access
❖ Consistency across business teams
STORED PROCEDURES
What Is a Stored Procedure?
● A Stored Procedure is a compiled
set of SQL statements saved in the
database.

● Used for automation and reusability.

Executing a Procedure:

Output:
Advantages of Stored Procedures
● Improves performance

● Reduces network load

● Reusable & maintainable logic
● Secure — controlled EXECUTE rights
Common Pitfalls
● Naming conflicts

● Permission mismanagement

● Debugging complexity

● Versioning challenges

Tip: Use version naming like Proc_Sales_v2.

TRIGGERS
What Is a Trigger?
A Trigger is a block of SQL that executes automatically when an event occurs
(INSERT, UPDATE, DELETE).

Use Case: Audit changes or enforce business rules automatically.

Trigger Example: Log Salary Updates
Problem Statement

The HR department wants to

automatically record any salary
changes made to employee records.
Whenever an employee’s salary is
updated, the system should log the old
and new salary details along with the
date of change — without requiring
manual entry.
Output

Explanation:

● Runs after any update on the Employees table.

● Executes once for each updated row.

● Inserts old and new salary details with timestamp

into SalaryLog.

● Used to automatically track salary changes for

audit purposes.
Types of Triggers

Type Description Common Use

BEFORE Trigger Before Validate data

modification

AFTER Trigger After Audit/logging

modification

INSTEAD OF Replaces event Custom logic for

Trigger views
Real-World Examples
● Audit salary updates

● Validate input (no salary < ₹30,000)

● Update totals automatically after inserts

● Log deletions for data recovery

What Is Data Modelling?
● Data Modelling is the process of designing the structure and organization of
data within a database.

● It defines what data is stored, how different pieces of data relate, and the rules
governing them.

● Essentially, it’s the blueprint of a database — just like an architect’s plan before
building a house.
Types of Data Models:
● Conceptual Model: High-level — focuses on business concepts and
relationships (e.g., ER diagram).

● Logical Model: Details attributes, primary/foreign keys, and relationships —

platform-independent.

● Physical Model: Translates the logical model into an actual database

structure with tables, data types, and indexes.
Benefits of Data Modelling
○ Helps in understanding data flow and dependencies before
implementation.

○ Reduces future redesigns and improves performance.

○ Ensures data integrity (accurate and consistent information across tables).

○ Makes communication between business analysts, developers, and database

administrators smoother.
Main Components:
● Entities: These are objects or concepts about which data is stored (e.g., Customer,
Product, Order).

● Attributes: Characteristics or details about each entity (e.g., Customer → Name, Email,
City).

● Relationships: How entities are related (e.g., A Customer places many Orders).

Think of entities as nouns, attributes as adjectives, and relationships as verbs.

ER Diagram
ER Diagram: Sales Management System
Entities and Attributes 4. Payment
○ Attribute: PaymentID (Primary Key,
1. Customer
implied)
○ Attribute: CustomerID (Primary Key)
○ Contains details about the
○ Represents the buyers who place orders.
payments made for each order.
2. Order
5. Employee
○ Attribute: OrderID (Primary Key)
○ Attribute: EmployeeID (Primary Key,
○ Stores details of each transaction made
implied)
by customers.
○ Represents employees who process
3. Product
the orders.
○ Attribute: ProductID (Primary Key,
6. Category
implied)
○ Attribute: CategoryID (Primary Key,
○ Represents the items available for
implied)
purchase.
○ Groups products under specific
categories.
Relationships
1. Customer – Order
○ Relationship: places
○ 1:N → One customer can place multiple orders, 4. Order – Employee
but each order belongs to only one customer. ○ Relationship: processed
○ 1:1 → Each order is processed by
2. Order – Product a specific employee.
○ Relationship: contains
○ N:N → One order can contain multiple products, 5. Product – Category
and a product can appear in many orders. ○ Relationship: belongs to
○ N:1 → Many products can
3. Order – Payment belong to a single category.
○ Relationship: contains
○ N:N → Each order can have multiple payments
(e.g., partial payments), and each payment may
relate to multiple orders (in some cases).
Summary
● CTEs — Simplify complex logic

● Views — Virtual and reusable

● Procedures — Automate workflows
● Triggers — Maintain integrity
● Data Modelling — Structured and efficient
Thank You for Attending the Session!!
Reusable SQL is the backbone of maintainable analytics.

Keep Learning, Keep Upskilling!