Mastering SQL: Practical Database Design and Query Examples

Posted on Nov 29, 2025 in Computers

SQL Database Management: Supplier and Product Inventory

Defining the Inventory Schema (DDL)

We begin by setting up the database structure for tracking suppliers and their products. Note the use of PRIMARY KEY, FOREIGN KEY, and CHECK constraints for ensuring data integrity.

Supplier Table Creation

CREATE TABLE Supplier (
    SupplierID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    City VARCHAR(50)
);

Product Table Creation

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    SupplierID INT,
    FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID),
    CHECK (Price > 0)
);

Populating the Tables (DML)

Inserting initial data into the Supplier and Product tables:

Inserting Supplier Data

INSERT INTO Supplier (SupplierID, Name, City) VALUES
(101, 'Agile Supplies', 'Mumbai'),
(102, 'TechCorp Distributors', 'Delhi'),
(103, 'Global Trading', 'Kolkata'),
(104, 'Rural Harvest', 'Pune'),
(105, 'Urban Gadgets', 'Delhi');

Inserting Product Data

INSERT INTO Product (ProductID, Name, Price, SupplierID) VALUES
(1, 'Laptop', 55000.00, 102),
(2, 'Mouse', 750.00, 102),
(3, 'Monitor', 12500.00, 105),
(4, 'Keyboard', 1200.00, 101),
(5, 'Webcam', 2500.00, 105),
(6, 'Printer', 8900.00, 104),
(7, 'Server Rack', 35000.00, 102),
(8, 'Desk Lamp', 1500.00, 101);

Advanced Inventory Queries and Updates

Calculating Average Product Price per Supplier

This query uses a JOIN and GROUP BY clause to determine the average price of products supplied by each vendor.

SELECT
    S.Name AS SupplierName,
    AVG(P.Price) AS AveragePrice
FROM
    Supplier S
JOIN
    Product P ON S.SupplierID = P.SupplierID
GROUP BY
    S.Name;

Identifying Suppliers with High-Value Products (Price > 2000)

Using DISTINCT ensures that each supplier name is listed only once.

SELECT DISTINCT
    S.Name AS SupplierName
FROM
    Supplier S
JOIN
    Product P ON S.SupplierID = P.SupplierID
WHERE
    P.Price > 2000.00;

Applying a Price Increase for Delhi Suppliers (DML Update)

We update the price of products by 5% (* 1.05) for suppliers located in ‘Delhi’, utilizing a subquery in the WHERE clause.

UPDATE
    Product
SET
    Price = Price * 1.05
WHERE
    SupplierID IN (
        SELECT SupplierID
        FROM Supplier
        WHERE City = 'Delhi'
    );

Creating a Supplier-Product Relationship View

A view simplifies access to combined supplier and product information.

CREATE VIEW SupplierProductView AS
SELECT
    S.Name AS SupplierName,
    P.Name AS ProductName,
    P.Price
FROM
    Supplier S
JOIN
    Product P ON S.SupplierID = P.SupplierID;

SQL Database Management: Student Attendance Tracking

Defining the Education Schema (DDL)

Setting up tables for student records and daily attendance logs.

Student Table Creation

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Class INT NOT NULL,
    Section CHAR(1)
);

Attendance Table Creation

The Attendance table includes a UNIQUE constraint on (StudentID, Date) to prevent duplicate entries for the same student on the same day.

CREATE TABLE Attendance (
    AttendID INT PRIMARY KEY,
    StudentID INT,
    Date DATE NOT NULL,
    Status VARCHAR(10) NOT NULL,
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    CHECK (Status IN ('Present', 'Absent')),
    UNIQUE (StudentID, Date)
);

Populating Student Data (DML)

Inserting a sample student record. (Note: The original input provided a snippet of the INSERT statement.)

INSERT INTO Student (StudentID, Name, Class, Section) VALUES
(101, 'Arjun Singh', 10, 'A');
-- Additional student data would follow here.

Attendance Reporting and Analysis

Calculating Detailed Attendance Percentage per Student

This complex query uses CASE statements within aggregate functions (SUM and COUNT) and CAST to calculate the precise attendance percentage.

SELECT
    S.Name,
    COUNT(A.Status) AS TotalDays,
    SUM(CASE WHEN A.Status = 'Present' THEN 1 ELSE 0 END) AS PresentDays,
    (CAST(SUM(CASE WHEN A.Status = 'Present' THEN 1 ELSE 0 END) AS DECIMAL) * 100) / COUNT(A.Status) AS AttendancePercentage
FROM
    Student S
JOIN
    Attendance A ON S.StudentID = A.StudentID
GROUP BY
    S.Name
ORDER BY
    AttendancePercentage DESC;

Identifying Students with Low Attendance (Below 75%)

The HAVING clause filters the results based on the calculated aggregate percentage.

SELECT
    S.Name,
    (CAST(SUM(CASE WHEN A.Status = 'Present' THEN 1 ELSE 0 END) AS DECIMAL) * 100) / COUNT(A.Status) AS AttendancePercentage
FROM
    Student S
JOIN
    Attendance A ON S.StudentID = A.StudentID
GROUP BY
    S.Name
HAVING
    (CAST(SUM(CASE WHEN A.Status = 'Present' THEN 1 ELSE 0 END) AS DECIMAL) * 100) / COUNT(A.Status) < 75;

Correcting an Attendance Record (DML Update)

Updating a specific attendance entry from ‘Absent’ to ‘Present’ based on Student ID and Date.

UPDATE
    Attendance
SET
    Status = 'Present'
WHERE
    StudentID = 102
    AND Date = '2025-11-04'
    AND Status = 'Absent';

Creating an Attendance Summary View

CREATE VIEW AttendanceReport AS
SELECT
    S.Name,
    COUNT(A.Status) AS TotalDays,
    SUM(CASE WHEN A.Status = 'Present' THEN 1 ELSE 0 END) AS PresentDays,
    (CAST(SUM(CASE WHEN A.Status = 'Present' THEN 1 ELSE 0 END) AS DECIMAL) * 100) / COUNT(A.Status) AS Percentage
FROM
    Student S
JOIN
    Attendance A ON S.StudentID = A.StudentID
GROUP BY
    S.Name;

SQL Database Management: Movie Ticketing System

Defining the Entertainment Schema (DDL)

Setting up tables for movie details and customer bookings.

Movie Table Creation

CREATE TABLE Movie (
    MovieID INT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Genre VARCHAR(50),
    TicketPrice DECIMAL(10, 2) NOT NULL
);

Booking Table Creation

CREATE TABLE Booking (
    BookingID INT PRIMARY KEY,
    MovieID INT,
    CustomerName VARCHAR(100) NOT NULL,
    SeatsBooked INT NOT NULL,
    FOREIGN KEY (MovieID) REFERENCES Movie(MovieID),
    CHECK (SeatsBooked > 0)
);

Populating Movie and Booking Data (DML)

Inserting Movie Data

INSERT INTO Movie (MovieID, Title, Genre, TicketPrice) VALUES
(1, 'The Galactic War', 'Action', 300.00),
(2, 'Love in London', 'Romance', 250.00),
(3, 'Silent Night Mystery', 'Thriller', 350.00),
(4, 'The Lost City', 'Adventure', 320.00),
(5, 'Future Shock', 'Sci-Fi', 400.00);

Inserting Booking Data

INSERT INTO Booking (BookingID, MovieID, CustomerName, SeatsBooked) VALUES
(101, 1, 'Amit Sharma', 4),
(102, 2, 'Priya Verma', 2),
(103, 1, 'Ravi Kumar', 5),
(104, 4, 'Sneha Reddy', 3),
(105, 3, 'Vikram Kulkarni', 1),
(106, 5, 'Kriti Singh', 6),
(107, 2, 'Rahul Jain', 4),
(108, 1, 'Geeta Patel', 2),
(109, 4, 'Anil Dutt', 5),
(110, 5, 'Deepak Shah', 3);

Ticketing System Queries and Revenue Management

Calculating Total Seats Booked per Movie

This query aggregates booking data to show which movies are most popular, ordered descendingly.

SELECT
    M.Title AS MovieTitle,
    SUM(B.SeatsBooked) AS TotalSeatsBooked
FROM
    Movie M
JOIN
    Booking B ON M.MovieID = B.MovieID
GROUP BY
    M.Title
ORDER BY
    TotalSeatsBooked DESC;

Adjusting Ticket Prices for Action Movies (DML Update)

Increasing the TicketPrice by 50.00 for all movies categorized under the ‘Action’ genre.

UPDATE
    Movie
SET
    TicketPrice = TicketPrice + 50.00
WHERE
    Genre = 'Action';

Creating a Total Revenue Calculation View

This view calculates the total revenue generated by each movie based on current ticket prices and booked seats.

CREATE VIEW RevenueView AS
SELECT
    M.Title AS MovieTitle,
    SUM(B.SeatsBooked) AS TotalSeats,
    SUM(M.TicketPrice * B.SeatsBooked) AS TotalRevenue
FROM
    Movie M
JOIN
    Booking B ON M.MovieID = B.MovieID
GROUP BY
    M.Title;