Practical SQL Examples for Database Management

Posted on Nov 26, 2025 in Computing and Communications

Student and Course Database Examples

Database Creation

It’s good practice to create a dedicated database for your tables.

-- Create a new database (optional, good practice)
CREATE DATABASE IF NOT EXISTS StudentDB;
USE StudentDB;

Table Creation

Here, we define the Student and Course tables. Note the use of PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK constraints.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Dept VARCHAR(50),
    Age INT,
    Email VARCHAR(100) UNIQUE NOT NULL,
    CHECK (Age > 16)
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(100) NOT NULL,
    Credits INT,
    StudentID INT,
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

Data Insertion

Populate the tables with sample data for students and their course enrollments.

-- Insert 5 students
INSERT INTO Student (Name, Dept, Age, Email) VALUES
('Alice Smith', 'CSE', 18, 'alice@email.com'),
('Bob Johnson', 'IT', 19, 'bob@email.com'),
('Charlie Brown', 'CSE', 17, 'charlie@email.com'),
('David Lee', 'ECE', 20, 'david@email.com'),
('Eve Davis', 'Mech', 18, 'eve@email.com');

-- Insert 5 course records (enrollments)
INSERT INTO Course (Title, Credits, StudentID) VALUES
('Data Structures', 4, 1),
('Database Systems', 3, 1),
('Networking', 3, 2),
('Algorithms', 4, 3),
('Thermodynamics', 3, 5);

SQL Queries and Operations

Display Enrolled Students with INNER JOIN

SELECT DISTINCT s.Name
FROM Student s
INNER JOIN Course c ON s.StudentID = c.StudentID;

Calculate Average Credits per Department

SELECT s.Dept, AVG(c.Credits) AS AverageCredits
FROM Student s
JOIN Course c ON s.StudentID = c.StudentID
GROUP BY s.Dept;

Update Course Credits for CSE Students

UPDATE Course
SET Credits = Credits + 1
WHERE StudentID IN (SELECT StudentID FROM Student WHERE Dept = 'CSE');

Create a Student-Course View

CREATE VIEW StudentCourseView AS
SELECT s.Name, s.Dept, c.Title, c.Credits
FROM Student s
JOIN Course c ON s.StudentID = c.StudentID;

-- To see the view:
-- SELECT * FROM StudentCourseView;

Library Book Issuing Database Examples

Database Creation

CREATE DATABASE IF NOT EXISTS LibraryDB;
USE LibraryDB;

Table Creation

Define the Book and Issue tables to track library inventory and borrowing records.

CREATE TABLE Book (
    BookID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    Author VARCHAR(100),
    Price DECIMAL(10, 2),
    CHECK (Price > 0)
);

CREATE TABLE Issue (
    IssueID INT PRIMARY KEY AUTO_INCREMENT,
    BookID INT,
    StudentName VARCHAR(100),
    IssueDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (BookID) REFERENCES Book(BookID)
);

Data Insertion

Insert sample data for books and issue records.

INSERT INTO Book (Title, Author, Price) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 150.00),
('1984', 'George Orwell', 200.00),
('To Kill a Mockingbird', 'Harper Lee', 175.50),
('Data Structures in C', 'Reema Thareja', 550.00),
('Database Management', 'Korth', 720.00);

INSERT INTO Issue (BookID, StudentName, IssueDate, ReturnDate) VALUES
(1, 'Alice Smith', '2025-10-01', '2025-10-15'),
(4, 'Bob Johnson', '2025-10-02', '2025-10-16'),
(5, 'Charlie Brown', '2025-10-05', '2025-10-20'),
(2, 'Alice Smith', '2025-10-06', '2025-10-21'),
(4, 'David Lee', '2025-10-08', '2025-10-23');

SQL Queries and Operations

List All Issued Books Using JOIN

SELECT b.Title, b.Author, i.StudentName, i.IssueDate
FROM Book b
JOIN Issue i ON b.BookID = i.BookID;

Find Total Books Issued per Student

SELECT StudentName, COUNT(IssueID) AS TotalBooksIssued
FROM Issue
GROUP BY StudentName;

Display Books with Price Greater Than ₹500

SELECT Title, Author, Price
FROM Book
WHERE Price > 500;

Create an Issued Books View

CREATE VIEW IssuedBooksView AS
SELECT b.Title, b.Author, i.StudentName, i.IssueDate
FROM Book b
JOIN Issue i ON b.BookID = i.BookID;

-- To see the view:
-- SELECT * FROM IssuedBooksView;

Employee and Department Database Examples

Database Creation

CREATE DATABASE IF NOT EXISTS EmployeeDB;
USE EmployeeDB;

Table Creation

Create Department and Employee tables to manage organizational data.

CREATE TABLE Department (
    DeptID INT PRIMARY KEY AUTO_INCREMENT,
    DeptName VARCHAR(50) UNIQUE NOT NULL,
    Location VARCHAR(100)
);

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY AUTO_INCREMENT,
    EmpName VARCHAR(100) NOT NULL,
    Salary DECIMAL(10, 2),
    DeptID INT,
    CHECK (Salary > 10000),
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

Data Insertion

Add data for various departments and employees.

INSERT INTO Department (DeptName, Location) VALUES
('HR', 'New York'),
('IT', 'Pune'),
('Finance', 'London'),
('Marketing', 'Pune'),
('Sales', 'New York');

INSERT INTO Employee (EmpName, Salary, DeptID) VALUES
('John Doe', 50000.00, 1),
('Jane Smith', 80000.00, 2),
('Mike Ross', 75000.00, 2),
('Rachel Zane', 65000.00, 3),
('Harvey Specter', 90000.00, 3),
('Donna Paulsen', 55000.00, 1),
('Louis Litt', 85000.00, 3),
('Sam Wheeler', 72000.00, 2),
('Katrina Bennett', 60000.00, 4),
('Alex Williams', 78000.00, 5);

SQL Queries and Operations

Display Total Salary per Department

SELECT d.DeptName, SUM(e.Salary) AS TotalSalary
FROM Department d
JOIN Employee e ON d.DeptID = e.DeptID
GROUP BY d.DeptName;

Update Salary by 10% for the IT Department

UPDATE Employee
SET Salary = Salary * 1.10
WHERE DeptID = (SELECT DeptID FROM Department WHERE DeptName = 'IT');

Show Employees in the HR Department

SELECT e.EmpName, e.Salary
FROM Employee e
INNER JOIN Department d ON e.DeptID = d.DeptID
WHERE d.DeptName = 'HR';

Create a Department-Employee View

CREATE VIEW DeptEmpView AS
SELECT e.EmpName, d.DeptName, d.Location
FROM Employee e
JOIN Department d ON e.DeptID = d.DeptID;

-- To see the view:
-- SELECT * FROM DeptEmpView;

Bank Account and Transaction Database Examples

Database Creation

CREATE DATABASE IF NOT EXISTS BankDB;
USE BankDB;

Table Creation

Define Account and Transaction tables for a simple banking system.

CREATE TABLE Account (
    AccountNo INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Balance DECIMAL(12, 2),
    CHECK (Balance >= 0)
);

CREATE TABLE Transaction (
    TransID INT PRIMARY KEY AUTO_INCREMENT,
    AccountNo INT,
    TransDate DATE,
    Amount DECIMAL(10, 2),
    `Type` VARCHAR(20), -- 'Deposit' or 'Withdrawal'
    FOREIGN KEY (AccountNo) REFERENCES Account(AccountNo)
);

Data Insertion

Insert sample account and transaction records.

INSERT INTO Account (AccountNo, CustomerName, Balance) VALUES
(1001, 'Anil Kumar', 5000.00),
(1002, 'Sunita Sharma', 12000.00),
(1003, 'Rajesh Gupta', 800.00),
(1004, 'Priya Singh', 25000.00),
(1005, 'Vikram Mehta', 1500.00);

INSERT INTO Transaction (AccountNo, TransDate, Amount, `Type`) VALUES
(1001, '2025-10-01', 2000.00, 'Deposit'),
(1002, '2025-10-01', 1000.00, 'Withdrawal'),
(1001, '2025-10-02', 500.00, 'Withdrawal'),
(1003, '2025-10-03', 300.00, 'Deposit'),
(1004, '2025-10-03', 5000.00, 'Deposit'),
(1005, '2025-10-04', 200.00, 'Withdrawal'),
(1002, '2025-10-05', 3000.00, 'Deposit'),
(1001, '2025-10-06', 1000.00, 'Deposit'),
(1004, '2025-10-07', 1500.00, 'Withdrawal'),
(1003, '2025-10-08', 100.00, 'Withdrawal');

SQL Queries and Operations

Display Total Deposits and Withdrawals per Account

SELECT
    a.CustomerName,
    t.AccountNo,
    SUM(CASE WHEN t.`Type` = 'Deposit' THEN t.Amount ELSE 0 END) AS TotalDeposits,
    SUM(CASE WHEN t.`Type` = 'Withdrawal' THEN t.Amount ELSE 0 END) AS TotalWithdrawals
FROM Account a
JOIN Transaction t ON a.AccountNo = t.AccountNo
GROUP BY a.CustomerName, t.AccountNo;

List Customers with Their Last Transaction Date

SELECT a.CustomerName, MAX(t.TransDate) AS LastTransactionDate
FROM Account a
JOIN Transaction t ON a.AccountNo = t.AccountNo
GROUP BY a.CustomerName;

Update Balance After a Deposit

-- Note: In a real system, you would INSERT into Transaction first.
UPDATE Account
SET Balance = Balance + 1000
WHERE AccountNo = 1001;

Create an Account Summary View

CREATE VIEW AccountSummaryView AS
SELECT
    a.CustomerName,
    SUM(CASE WHEN t.`Type` = 'Deposit' THEN t.Amount ELSE 0 END) AS TotalDeposits,
    SUM(CASE WHEN t.`Type` = 'Withdrawal' THEN t.Amount ELSE 0 END) AS TotalWithdrawals
FROM Account a
JOIN Transaction t ON a.AccountNo = t.AccountNo
GROUP BY a.CustomerName, a.AccountNo;

-- To see the view:
-- SELECT * FROM AccountSummaryView;

Hospital Doctor-Patient Database Examples

Database Creation

CREATE DATABASE IF NOT EXISTS HospitalDB;
USE HospitalDB;

Table Creation

Create Doctor and Patient tables for managing hospital records.

CREATE TABLE Doctor (
    DoctorID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Specialization VARCHAR(100),
    Fees DECIMAL(8, 2),
    CHECK (Fees > 0)
);

CREATE TABLE Patient (
    PatientID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Disease VARCHAR(100),
    DoctorID INT,
    FOREIGN KEY (DoctorID) REFERENCES Doctor(DoctorID)
);

Data Insertion

Populate the tables with doctor and patient information.

INSERT INTO Doctor (Name, Specialization, Fees) VALUES
('Dr. Sharma', 'Cardiologist', 1500.00),
('Dr. Verma', 'Neurologist', 2000.00),
('Dr. Gupta', 'Pediatrician', 800.00),
('Dr. Reddy', 'Cardiologist', 1600.00),
('Dr. Patel', 'Dermatologist', 1000.00);

INSERT INTO Patient (Name, Disease, DoctorID) VALUES
('Rohan Singh', 'Hypertension', 1),
('Meena Kumari', 'Migraine', 2),
('Amit Das', 'Fever', 3),
('Priya Nair', 'Acne', 5),
('Suresh Kumar', 'Arrhythmia', 1),
('Anjali Bose', 'Eczema', 5),
('Vikram Rao', 'Stroke', 2),
('Sunil Jain', 'Chest Pain', 4),
('Anita Desai', 'Cold', 3),
('Raj Malhotra', 'Tachycardia', 1);

SQL Queries and Operations

Display Doctors and Their Patients Using JOIN

SELECT d.Name AS DoctorName, p.Name AS PatientName, p.Disease
FROM Doctor d
JOIN Patient p ON d.DoctorID = p.DoctorID;

Show Total Patients per Doctor

SELECT d.Name AS DoctorName, d.Specialization, COUNT(p.PatientID) AS TotalPatients
FROM Doctor d
LEFT JOIN Patient p ON d.DoctorID = p.DoctorID -- Use LEFT JOIN to include doctors with 0 patients
GROUP BY d.DoctorID, d.Name, d.Specialization;

Update Fees for Cardiologists

UPDATE Doctor
SET Fees = Fees + 200
WHERE Specialization = 'Cardiologist';

Create a Doctor-Patient View

CREATE VIEW DoctorPatientView AS
SELECT d.Name AS DoctorName, d.Specialization, p.Name AS PatientName
FROM Doctor d
JOIN Patient p ON d.DoctorID = p.DoctorID;

-- To see the view:
-- SELECT * FROM DoctorPatientView;

Customer Order Database Examples

Database Creation

CREATE DATABASE IF NOT EXISTS OrderDB;
USE OrderDB;

Table Creation

Define Customer and OrderTable to manage sales data.

CREATE TABLE Customer (
    CustID INT PRIMARY KEY AUTO_INCREMENT,
    CustName VARCHAR(100) NOT NULL,
    City VARCHAR(50)
);

CREATE TABLE OrderTable (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    CustID INT,
    OrderDate DATE,
    Amount DECIMAL(10, 2),
    CHECK (Amount > 0),
    FOREIGN KEY (CustID) REFERENCES Customer(CustID)
);

Data Insertion

Insert sample data for customers and their orders.

INSERT INTO Customer (CustName, City) VALUES
('Keya Shah', 'Mumbai'),
('Ravi Kumar', 'Delhi'),
('Aditya Rao', 'Pune'),
('Nina Gupta', 'Pune'),
('Samir Jain', 'Bangalore');

INSERT INTO OrderTable (CustID, OrderDate, Amount) VALUES
(1, '2025-10-01', 1500.00),
(2, '2025-10-01', 8000.00),
(3, '2025-10-02', 3000.00),
(4, '2025-10-03', 1200.00),
(1, '2025-10-04', 4500.00),
(5, '2025-10-04', 6000.00),
(2, '2025-10-05', 500.00),
(3, '2025-10-06', 2500.00),
(1, '2025-10-07', 1000.00),
(4, '2025-10-08', 500.00);

SQL Queries and Operations

Display Total Purchase Amount per Customer

SELECT c.CustName, SUM(o.Amount) AS TotalPurchaseAmount
FROM Customer c
JOIN OrderTable o ON c.CustID = o.CustID
GROUP BY c.CustID, c.CustName;

Retrieve Customers with Total Orders > ₹5000

SELECT c.CustName, SUM(o.Amount) AS TotalPurchaseAmount
FROM Customer c
JOIN OrderTable o ON c.CustID = o.CustID
GROUP BY c.CustID, c.CustName
HAVING SUM(o.Amount) > 5000;

Update Customer City from ‘Pune’ to ‘Mumbai’

UPDATE Customer
SET City = 'Mumbai'
WHERE City = 'Pune';

Create a Customer Order View

CREATE VIEW CustomerOrderView AS
SELECT c.CustName, c.City, SUM(o.Amount) AS TotalAmount
FROM Customer c
JOIN OrderTable o ON c.CustID = o.CustID
GROUP BY c.CustID, c.CustName, c.City;

-- To see the view:
-- SELECT * FROM CustomerOrderView;

University Faculty-Subject Database Examples

Database Creation

CREATE DATABASE IF NOT EXISTS UniversityDB;
USE UniversityDB;

Table Creation

Create Faculty and Subject tables for a university management system.

CREATE TABLE Faculty (
    FacultyID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Qualification VARCHAR(50),
    Experience INT
);

CREATE TABLE Subject (
    SubID VARCHAR(10) PRIMARY KEY,
    SubName VARCHAR(100),
    FacultyID INT,
    Credits INT,
    CHECK (Credits > 0),
    FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID)
);

Data Insertion

Add records for faculty members and the subjects they teach.

INSERT INTO Faculty (Name, Qualification, Experience) VALUES
('Dr. Alok Verma', 'PhD', 10),
('Prof. Meena Iyer', 'MSc', 6),
('Dr. S. K. Jain', 'PhD', 15),
('Prof. R. Singh', 'MSc', 4),
('Dr. P. Sharma', 'MTech', 7);

INSERT INTO Subject (SubID, SubName, FacultyID, Credits) VALUES
('CS101', 'Intro to CS', 1, 4),
('MA201', 'Calculus', 2, 3),
('CS305', 'Databases', 1, 4),
('PH100', 'Physics', 3, 3),
('CH202', 'Chemistry', 5, 4),
('MA310', 'Linear Algebra', 2, 3);

SQL Queries and Operations

Display Subjects Taught by Experienced Faculty

SELECT s.SubName, s.Credits, f.Name AS FacultyName, f.Experience
FROM Subject s
JOIN Faculty f ON s.FacultyID = f.FacultyID
WHERE f.Experience > 5;

Find Total Credits Handled per Faculty

SELECT f.Name, SUM(s.Credits) AS TotalCredits
FROM Faculty f
JOIN Subject s ON f.FacultyID = s.FacultyID
GROUP BY f.FacultyID, f.Name;

Update Faculty Qualification from ‘MSc’ to ‘PhD’

UPDATE Faculty
SET Qualification = 'PhD'
WHERE Qualification = 'MSc';

Create a Faculty-Subject View

CREATE VIEW FacultySubjectView AS
SELECT f.Name AS FacultyName, f.Qualification, s.SubName, s.Credits
FROM Faculty f
JOIN Subject s ON f.FacultyID = s.FacultyID;

-- To see the view:
-- SELECT * FROM FacultySubjectView;

Movie Booking Database Examples

Database Creation

CREATE DATABASE IF NOT EXISTS MovieDB;
USE MovieDB;

Table Creation

Define Movie and Booking tables for a cinema booking system.

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

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

Data Insertion

Insert data for movies and customer bookings.

INSERT INTO Movie (Title, Genre, TicketPrice) VALUES
('The Matrix', 'Action', 250.00),
('Inception', 'Sci-Fi', 300.00),
('Dune', 'Sci-Fi', 350.00),
('John Wick', 'Action', 320.00),
('The Hangover', 'Comedy', 200.00);

INSERT INTO Booking (MovieID, CustomerName, SeatsBooked) VALUES
(1, 'Rohan', 4),
(2, 'Priya', 2),
(1, 'Amit', 5),
(3, 'Sunita', 10),
(4, 'Vikram', 2),
(5, 'Meera', 4),
(2, 'Rohan', 3),
(3, 'John', 20),
(4, 'Priya', 6),
(1, 'Suresh', 2);

SQL Queries and Operations

Display Total Seats Booked per Movie

SELECT m.Title, m.Genre, SUM(b.SeatsBooked) AS TotalSeatsBooked
FROM Movie m
JOIN Booking b ON m.MovieID = b.MovieID
GROUP BY m.MovieID, m.Title, m.Genre;

Find Movies with Total Revenue > ₹10,000

SELECT m.Title, SUM(m.TicketPrice * b.SeatsBooked) AS TotalRevenue
FROM Movie m
JOIN Booking b ON m.MovieID = b.MovieID
GROUP BY m.MovieID, m.Title
HAVING TotalRevenue > 10000;

Update Ticket Price for Action Movies

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

Create a Movie Revenue View

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.MovieID, m.Title;

-- To see the view:
-- SELECT * FROM RevenueView;

School Attendance Database Examples

Database Creation

CREATE DATABASE IF NOT EXISTS SchoolDB;
USE SchoolDB;

Table Creation

Create Student and Attendance tables to track student presence.

CREATE TABLE Student (
    StudentID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Class VARCHAR(10),
    Section VARCHAR(2)
);

CREATE TABLE Attendance (
    AttendID INT PRIMARY KEY AUTO_INCREMENT,
    StudentID INT,
    `Date` DATE,
    `Status` VARCHAR(10), -- 'Present' or 'Absent'
    CHECK (`Status` IN ('Present', 'Absent')),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);

Data Insertion

Add student records and their attendance for a five-day period.

INSERT INTO Student (Name, Class, Section) VALUES
('Riya Sharma', '10', 'A'),
('Aarav Singh', '10', 'B'),
('Sameer Khan', '10', 'A'),
('Divya Patel', '10', 'B'),
('Karan Verma', '10', 'A');

-- Insert attendance (5 students * 5 days = 25 records)
-- Student 3 (Sameer) will have < 75% (2/5 = 40%)
INSERT INTO Attendance (StudentID, `Date`, `Status`) VALUES
(1, '2025-10-01', 'Present'), (1, '2025-10-02', 'Present'), (1, '2025-10-03', 'Present'), (1, '2025-10-04', 'Absent'), (1, '2025-10-05', 'Present'),
(2, '2025-10-01', 'Present'), (2, '2025-10-02', 'Present'), (2, '2025-10-03', 'Present'), (2, '2025-10-04', 'Present'), (2, '2025-10-05', 'Present'),
(3, '2025-10-01', 'Absent'), (3, '2025-10-02', 'Present'), (3, '2025-10-03', 'Absent'), (3, '2025-10-04', 'Present'), (3, '2025-10-05', 'Absent'),
(4, '2025-10-01', 'Present'), (4, '2025-10-02', 'Present'), (4, '2025-10-03', 'Absent'), (4, '2025-10-04', 'Present'), (4, '2025-10-05', 'Present'),
(5, '2025-10-01', 'Present'), (5, '2025-10-02', 'Present'), (5, '2025-10-03', 'Present'), (5, '2025-10-04', 'Present'), (5, '2025-10-05', 'Absent');

SQL Queries and Operations

Calculate Attendance Percentage

SELECT
    s.Name,
    COUNT(a.AttendID) AS TotalDays,
    SUM(CASE WHEN a.`Status` = 'Present' THEN 1 ELSE 0 END) AS PresentDays,
    (SUM(CASE WHEN a.`Status` = 'Present' THEN 1 ELSE 0 END) / COUNT(a.AttendID)) * 100 AS Percentage
FROM Student s
JOIN Attendance a ON s.StudentID = a.StudentID
GROUP BY s.StudentID, s.Name;

List Students with Attendance Below 75%

SELECT
    s.Name,
    (SUM(CASE WHEN a.`Status` = 'Present' THEN 1 ELSE 0 END) / COUNT(a.AttendID)) * 100 AS Percentage
FROM Student s
JOIN Attendance a ON s.StudentID = a.StudentID
GROUP BY s.StudentID, s.Name
HAVING Percentage < 75;

Update a Student’s Attendance Status

UPDATE Attendance
SET `Status` = 'Present'
WHERE StudentID = 3 AND `Date` = '2025-10-05';

Create an Attendance Report View

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

-- To see the view:
-- SELECT * FROM AttendanceReport;

Inventory and Supplier Database Examples

Database Creation

CREATE DATABASE IF NOT EXISTS InventoryDB;
USE InventoryDB;

Table Creation

Define Supplier and Product tables for an inventory management system.

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

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

Data Insertion

Populate the tables with supplier and product data.

INSERT INTO Supplier (Name, City) VALUES
('TechPro', 'Delhi'),
('OfficeNeeds', 'Mumbai'),
('GreenFarms', 'Pune'),
('MegaCorp', 'Delhi'),
('GadgetWorld', 'Bangalore');

INSERT INTO Product (Name, Price, SupplierID) VALUES
('Laptop', 80000.00, 1),
('Printer', 15000.00, 1),
('Office Chair', 7000.00, 2),
('Organic Apples', 300.00, 3),
('Keyboard', 2500.00, 4),
('Mouse', 1200.00, 4),
('Headphones', 3000.00, 5),
('Monitor', 22000.00, 1);

SQL Queries and Operations

Find Average Product Price per Supplier

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

Display Suppliers of High-Priced Products

SELECT DISTINCT s.Name AS SupplierName
FROM Supplier s
JOIN Product p ON s.SupplierID = p.SupplierID
WHERE p.Price > 2000;

Update Prices for Suppliers in ‘Delhi’

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

Create a Supplier-Product View

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;

-- To see the view:
-- SELECT * FROM SupplierProductView;