Real World Projects on

SQL
S.No Name of the Project
1 Bank Accounts Management System
2 Billing System Documentation
3 Blood Bank Management System
4 Electricity Billing System
5 Employee Management System
6 Hospital Management System
7 Library Management System
8 Online Job Portal
9 Payroll Management System
10 Personal Finance Tracker System
11 Restaurant Delivery Management System
12 Voice-Based Transport Enquiry System
13 Where is My Professor
14 Wholesale Management System
15 Digital Learning Management System
16 Sales Analysis: Walmart Sales Data Analysis
17 Social Media Analytics: Creating a Social Media Database
18 Customer Feedback Management System for Sentiment Analysis
19 Centralized College Database System
20 Hotel Reservation System
21 International Debt Analysis Project
22 Flight Booking System
23 Railway System Database
24 RPA Customer Segmentation Project
25 Fraud Detection System
26 Statistical Analysis and Data Mining with SQL
27 SMS-based Remote Server Monitoring System
28 Voice Commands Transport Enquiry System
29 Online Book Store
30 Budget Tracking System
1. Project Title: Bank Accounts Management System

Problem Definition This project aims to develop a comprehensive system for managing bank
accounts, transactions, and customer information. The system facilitates secure operations like
deposits, withdrawals, and account inquiries.

Features

 Customer registration and account creation

 Deposit, withdrawal, and transfer operations
 Transaction history and mini-statement
 Account balance checks
 Admin panel for customer management

Required Tables

Customers

Field Name Data Type Description

customer_id INT (PK) Unique ID for each customer
name VARCHAR Full name of the customer
email VARCHAR Email address
phone VARCHAR Contact number
address VARCHAR Residential address

Accounts

Field Name Data Type Description

account_id INT (PK) Unique ID for each account
customer_id INT (FK) Owner of the account
account_type VARCHAR Type (Savings/Current)
balance DECIMAL Current balance

Transactions

Field Name Data Type Description

transaction_id INT (PK) Unique ID for each transaction
account_id INT (FK) Account involved
type VARCHAR Deposit/Withdrawal/Transfer
amount DECIMAL Transaction amount
transaction_date DATE Date of transaction

Admins

Field Name Data Type Description

admin_id INT (PK) Unique ID for admin
username VARCHAR Login name
password VARCHAR Encrypted password

Relationships Summary

 A Customer can have multiple Accounts

 An Account can have many Transactions
 Admins manage Customers and Accounts

Sample Data

Customers

customer_id name email phone address

1 Ravi ravi@mail.com 9876543210 Guntur

Accounts

account_id customer_id account_type balance

1001 1 Savings 25000.00

Transactions

transaction_id account_id type amount transaction_date

501 1001 Deposit 5000.00 2025-03-01
502 1001 Withdrawal 2000.00 2025-03-05

Admins

admin_id username password

1 admin1 encryptedpwd

SQL Components

Functions and Procedures

1. Procedure to deposit/withdraw amount from account

2. Function to check account balance
3. Trigger for maintaining transaction log

Views

4. Recent transactions per account

5. Customer account summary
6. Admin activity report
Nested Queries

7. Customers with balance above a threshold

8. Accounts with more than 5 transactions in a month
9. Top 3 customers by account balance

Join-Based Queries

10. Transaction history with customer name

11. Accounts and their associated customers
12. Admins managing the highest number of accounts
2. Project Title: Billing System Documentation

Problem Definition The Billing System project is developed to automate and manage the billing
process for various services and products. It enables the generation of bills, storing customer
information, item details, and transaction records.

Features

 Customer information management

 Item listing and pricing
 Invoice generation and tracking
 Payment records and transaction history
 Report generation for sales and revenue

Required Tables

Customers

Field Name Data Type Description

customer_id INT (PK) Unique ID for each customer
name VARCHAR Customer name
email VARCHAR Email address
phone VARCHAR Contact number

Items

Field Name Data Type Description

item_id INT (PK) Unique ID for item
name VARCHAR Item name
price DECIMAL Price of item

Invoices

Field Name Data Type Description

invoice_id INT (PK) Unique invoice ID
customer_id INT (FK) Linked customer ID
date DATE Billing date
total_amount DECIMAL Total billed amount

Invoice_Items

Field Name Data Type Description

invoice_item_id INT (PK) Unique ID for record
invoice_id INT (FK) Related invoice ID
item_id INT (FK) Purchased item ID
quantity INT Number of items purchased
Payments

Field Name Data Type Description

payment_id INT (PK) Unique ID for payment
invoice_id INT (FK) Invoice related to payment
payment_date DATE Date of payment
amount_paid DECIMAL Amount paid by customer

Relationships Summary

 A Customer can have many Invoices

 An Invoice can have multiple Invoice_Items
 An Invoice is associated with a Payment
 Items are listed in Invoice_Items

Sample Data

Customers

customer_id name email phone

1 Ravi ravi@mail.com 9876543210

Items

item_id name price

101 Keyboard 1200.00
102 Mouse 500.00

Invoices

invoice_id customer_id date total_amount

1 1 2025-04-01 1700.00

Invoice_Items

invoice_item_id invoice_id item_id quantity

1 1 101 1
2 1 102 1

Payments

payment_id invoice_id payment_date amount_paid

201 1 2025-04-02 1700.00

SQL Components

Functions and Procedures

 1. Procedure to create a new invoice
2. Function to calculate total amount from invoice items
3. Trigger to auto-update stock when invoice is generated

Views

4. Invoice summary per customer

5. Daily revenue reports
6. Payment status of invoices

Nested Queries

7. Customers with total purchases over a certain amount

8. Items sold more than 10 times
9. Invoices with delayed payments

Join-Based Queries

10. Invoices with item and customer details

11. Payments linked with invoice and customer info
12. Items with highest sales volume
3. Project Title: Blood Bank Management System

Problem Definition The Blood Bank Management System project is designed to handle donor
records, blood stock levels, and hospital requirements. It automates the process of storing,
retrieving, and updating information related to blood donation and distribution.

Features

 Donor registration and information management

 Blood inventory tracking
 Blood requests and issue records
 Hospital and recipient database
 Report generation for blood availability and usage

Required Tables

Donors

Field Name Data Type Description

donor_id INT (PK) Unique donor ID
name VARCHAR Donor name
blood_type VARCHAR Blood group
contact VARCHAR Contact number
last_donation DATE Last donation date

Blood_Stock

Field Name Data Type Description

stock_id INT (PK) Unique ID for blood stock record
blood_type VARCHAR Blood group
quantity INT Available units

Hospitals

Field Name Data Type Description

hospital_id INT (PK) Unique hospital ID
name VARCHAR Hospital name
location VARCHAR Address or location of hospital
contact VARCHAR Hospital contact number

Requests

Field Name Data Type Description

request_id INT (PK) Unique request ID
hospital_id INT (FK) Requesting hospital ID
blood_type VARCHAR Required blood group
quantity INT Required units
request_date DATE Date of request
status VARCHAR Status (Pending/Issued)

Issues

Field Name Data Type Description

issue_id INT (PK) Unique issue ID
request_id INT (FK) Linked request ID
issue_date DATE Date blood was issued
units_issued INT Units of blood issued

Relationships Summary

 A Donor donates to Blood_Stock

 A Hospital can make multiple Requests
 Requests lead to Issues once processed
 Blood_Stock is updated when an issue is processed

Sample Data

Donors

donor_id name blood_type contact last_donation

1 Ramesh B+ 9876543210 2025-03-20

Blood_Stock

stock_id blood_type quantity

1 B+ 12

Hospitals

hospital_id name location contact

101 Apollo Hyderabad 9999888877

Requests

request_id hospital_id blood_type quantity request_date status

501 101 B+ 2 2025-04-01 Pending

Issues

issue_id request_id issue_date units_issued

701 501 2025-04-02 2
SQL Components

Functions and Procedures

1. Procedure to record new donor entry

2. Trigger to update stock on donation
3. Procedure to fulfill a hospital request

Views

4. Available blood stock by type

5. Donor list with recent donations
6. Blood request status overview

Nested Queries

7. Donors who haven’t donated in last 6 months

8. Hospitals with most requests
9. Blood types in low stock

Join-Based Queries

10. Requests with hospital details

11. Issues linked to requests and hospitals
12. Donor contribution summary
4. Project Title: Electricity Billing System

Problem Definition The Electricity Billing System is designed to manage customer electricity
usage records, generate monthly bills, and handle payment tracking. It automates the billing
cycle for utility providers and provides consumers with transparency in consumption and
charges.

Features

 Customer account management

 Meter reading entry and tracking
 Automated bill generation
 Payment history and due records
 Reports on usage and revenue

Required Tables

Customers

Field Name Data Type Description

customer_id INT (PK) Unique customer ID
name VARCHAR Customer name
address VARCHAR Customer address
contact VARCHAR Contact number

Meters

Field Name Data Type Description

meter_id INT (PK) Unique meter ID
customer_id INT (FK) Associated customer ID
installation_date DATE Date of meter installation

Readings

Field Name Data Type Description

reading_id INT (PK) Unique reading ID
meter_id INT (FK) Associated meter ID
reading_date DATE Date of reading
units_consumed INT Units of electricity consumed

Bills

Field Name Data Type Description

bill_id INT (PK) Unique bill ID
customer_id INT (FK) Billed customer ID
billing_period VARCHAR Month and year of billing
units_billed INT Total units billed
amount DECIMAL Total amount
due_date DATE Due date for payment

Payments

Field Name Data Type Description

payment_id INT (PK) Unique payment ID
bill_id INT (FK) Paid bill ID
payment_date DATE Date of payment
amount_paid DECIMAL Amount paid

Relationships Summary

 A Customer has one or more Meters

 Each Meter records multiple Readings
 Bills are generated for Customers based on Readings
 Payments are linked to Bills

Sample Data

Customers

customer_id name address contact

201 Sita Guntur 9845678923

Meters

meter_id customer_id installation_date

301 201 2023-06-10

Readings

reading_id meter_id reading_date units_consumed

401 301 2025-03-31 150

Bills

bill_id customer_id billing_period units_billed amount due_date

601 201 March 2025 150 1200.00 2025-04-15

Payments

payment_id bill_id payment_date amount_paid

701 601 2025-04-10 1200.00
SQL Components

Functions and Procedures

1. Procedure to calculate and generate monthly bill

2. Trigger to insert bill after new reading is entered
3. Procedure to record payments and update bill status

Views

4. Monthly billing summary for each customer

5. Pending payments report
6. Consumption trend for a customer

Nested Queries

7. Customers with bills over 1000 units

8. Meters with highest usage this month
9. Payments made in the last 7 days

Join-Based Queries

10. Bill details along with customer and payment info

11. Monthly readings with customer and meter details
12. Overdue payments with contact information
5. Project Title: Employee Management System

Problem Definition The Employee Management System aims to streamline HR processes by

managing employee records, roles, departments, attendance, and payroll. It facilitates efficient
tracking of workforce data and organizational hierarchy.

Features

 Employee profile creation and updates

 Department and role assignments
 Attendance tracking and reporting
 Payroll calculation and disbursement
 Leaves and approvals management

Required Tables

Employees

Field Name Data Type Description

emp_id INT (PK) Unique employee ID
name VARCHAR Employee name
dob DATE Date of birth
contact VARCHAR Contact number
department_id INT (FK) Associated department
role_id INT (FK) Associated role

Departments

Field Name Data Type Description

department_id INT (PK) Unique department ID
department_name VARCHAR Name of the department

Roles

Field Name Data Type Description

role_id INT (PK) Unique role ID
role_name VARCHAR Title of the role
salary DECIMAL Salary for the role

Attendance

Field Name Data Type Description

attendance_id INT (PK) Unique attendance record
emp_id INT (FK) Associated employee ID
date DATE Attendance date
status VARCHAR Present/Absent/Leave
Payroll

Field Name Data Type Description

payroll_id INT (PK) Unique payroll ID
emp_id INT (FK) Employee ID
month VARCHAR Payroll month
salary_paid DECIMAL Amount paid
payment_date DATE Date of payment

Relationships Summary

 An Employee belongs to a Department and a Role

 Each Employee has multiple Attendance entries
 Payroll is calculated based on Role and attendance data

Sample Data

Employees

emp_id name dob contact department_id role_id

101 Kiran 1998-08-14 9999988888 10 501

Departments

department_id department_name
10 IT

Roles

role_id role_name salary

501 Developer 55000.00

Attendance

attendance_id emp_id date status

301 101 2025-04-01 Present

Payroll

payroll_id emp_id month salary_paid payment_date

401 101 April 2025 55000.00 2025-04-30
SQL Components

Functions and Procedures

1. Procedure to calculate salary based on attendance and role

2. Trigger to auto-fill payroll on month-end
3. Procedure to insert/update employee records

Views

4. Employee details by department and role

5. Monthly attendance summary
6. Salary disbursement overview

Nested Queries

7. Employees with attendance below 80%

8. Departments with highest payroll
9. Late payroll payments in the last 3 months

Join-Based Queries

10. Employee salary details by joining employees, roles, and payroll

11. Attendance logs with department and employee name
12. Monthly payroll with department-wise segregation
6. Project Title: Hospital Management System

Problem Definition The Hospital Management System is designed to streamline patient care,
hospital operations, and administration by managing appointments, doctor-patient interactions,
billing, and medical records efficiently.

Features

 Patient registration and history tracking

 Doctor scheduling and appointment management
 Billing and invoice generation
 Medical records maintenance
 Department and staff management

Required Tables

Patients

Field Name Data Type Description

patient_id INT (PK) Unique patient ID
name VARCHAR Patient's name
dob DATE Date of birth
contact VARCHAR Contact number
address VARCHAR Residential address

Doctors

Field Name Data Type Description

doctor_id INT (PK) Unique doctor ID
name VARCHAR Doctor's name
specialization VARCHAR Medical specialty
department_id INT (FK) Associated department

Appointments

Field Name Data Type Description

appointment_id INT (PK) Unique appointment ID
patient_id INT (FK) Patient's ID
doctor_id INT (FK) Doctor's ID
appointment_date DATE Date of appointment
status VARCHAR Status of appointment (Scheduled/Completed/Cancelled)

Billing

Field Name Data Type Description

bill_id INT (PK) Unique billing ID
patient_id INT (FK) Patient's ID
amount DECIMAL Total billed amount
billing_date DATE Date of billing

Departments

Field Name Data Type Description

department_id INT (PK) Unique department ID
department_name VARCHAR Department name

Relationships Summary

 A Patient can book multiple Appointments with Doctors

 Doctors belong to specific Departments
 Each Patient may have multiple Bills associated with treatments

Sample Data

Patients

patient_id name dob contact address

201 Ramesh 1985-06-12 9876543210 Hyderabad

Doctors

doctor_id name specialization department_id

301 Dr. Meena Cardiology 10

Appointments

appointment_id patient_id doctor_id appointment_date status

401 201 301 2025-04-01 Completed

Billing

bill_id patient_id amount billing_date

501 201 1500.00 2025-04-01

Departments

department_id department_name
10 Cardiology
SQL Components

Functions and Procedures

1. Procedure to schedule appointments and notify doctor

2. Trigger to auto-generate billing after appointment
3. Procedure to retrieve patient medical history

Views

4. Doctor-wise appointment summary

5. Department-wise patient list
6. Daily billing report

Nested Queries

7. Patients who visited more than twice in a month

8. Departments with the most appointments
9. Highest bill paid by each patient

Join-Based Queries

10. Appointment details with doctor and patient info

11. Patient billing history with department-wise split
12. Daily report of appointments and revenue
7. Project Title: Library Management System

Problem Definition The Library Management System helps manage book inventories, member
registrations, book lending/returns, and overdue fine collections efficiently, ensuring streamlined
library operations.

Features

 Book inventory management

 Member registration and profile management
 Book lending and return tracking
 Fine calculation for overdue books
 Categorization of books by genre, author, etc.

Required Tables

Books

Field Name Data Type Description

book_id INT (PK) Unique book ID
title VARCHAR Book title
author VARCHAR Author's name
genre VARCHAR Genre of the book
copies_available INT Number of available copies

Members

Field Name Data Type Description

member_id INT (PK) Unique member ID
name VARCHAR Member's name
contact VARCHAR Contact number
address VARCHAR Residential address

Lending

Field Name Data Type Description

lending_id INT (PK) Unique lending transaction ID
book_id INT (FK) Borrowed book ID
member_id INT (FK) Borrowing member ID
lend_date DATE Date of borrowing
return_date DATE Date of return

Fines

Field Name Data Type Description

fine_id INT (PK) Unique fine ID
member_id INT (FK) Member ID associated with fine
amount DECIMAL Fine amount
reason VARCHAR Reason for fine (e.g. Overdue)

Relationships Summary

 A Book can be lent to multiple Members over time

 Members can borrow multiple Books
 Each Lending may result in a Fine if returned late

Sample Data

Books

book_id title author genre copies_available

101 SQL Basics John Doe Education 5

Members

member_id name contact address

201 Priya 9876543210 Guntur

Lending

lending_id book_id member_id lend_date return_date

301 101 201 2025-03-20 2025-03-27

Fines

fine_id member_id amount reason

401 201 50.00 Overdue

SQL Components

Functions and Procedures

1. Procedure to issue a book and update inventory

2. Trigger to calculate fine on late return
3. Procedure to get member lending history

Views

4. Available books with genre

5. Members with most books borrowed
6. Daily lending report
Nested Queries

7. Books not returned within due date

8. Members with more than 3 books issued in a month
9. Highest fine paid per member

Join-Based Queries

10. Book lending details with member and book info

11. Member-wise fine details with reason
12. Genre-wise lending report
8. Project Title: Online Job Portal

Problem Definition The Online Job Portal is a platform designed to bridge the gap between
employers and job seekers. It enables job seekers to search and apply for jobs while allowing
employers to post job openings and review applicants.

Features

 User registration and authentication

 Job postings by employers
 Job search and filtering options
 Resume submission and application tracking
 Employer review and candidate shortlisting

Required Tables

Users

Field Name Data Type Description

user_id INT (PK) Unique user ID
Name VARCHAR User's full name
Email VARCHAR User's email address
password VARCHAR User's password (encrypted)
Role VARCHAR Role (Employer/Job Seeker)

Jobs

Field Name Data Type Description

job_id INT (PK) Unique job ID
employer_id INT (FK) Employer's user ID
Title VARCHAR Job title
description TEXT Detailed job description
location VARCHAR Job location
posted_date DATE Date of job posting

Applications

Field Name Data Type Description

app_id INT (PK) Unique application ID
job_id INT (FK) Applied job ID
seeker_id INT (FK) Job seeker's user ID
resume VARCHAR Path to resume document
apply_date DATE Date of application

Relationships Summary

 A User can be an Employer or Job Seeker

 Employers post multiple Jobs
  Job Seekers apply to multiple Jobs through Applications

Sample Data

Users

user_id name email role

1 Ravi ravi@email.com Job Seeker
2 Infosys hr@infosys.com Employer

Jobs

job_id employer_id title location posted_date

101 2 Software Engineer Hyderabad 2025-04-01

Applications

app_id job_id seeker_id apply_date

201 101 1 2025-04-02

SQL Components

Functions and Procedures

1. Procedure to insert a new job post

2. Procedure to apply for a job
3. Procedure to shortlist candidates

Views

4. List of all active jobs

5. Applications received for a job
6. Jobs applied by a job seeker

Nested Queries
7. Job seekers who applied to more than 5 jobs
8. Jobs posted in the last 7 days
9. Employers with the most job listings

Join-Based Queries
10. Job applications with seeker and job details
11. Job listings with employer details
12. Application tracking for a given user
9. Project Title: Payroll Management System

Problem Definition The Payroll Management System is developed to automate salary

calculations and manage employee compensation effectively. It ensures accurate payroll
processing and maintains records of employee salaries, bonuses, and deductions.

Features

 Employee salary calculation

 Tax and deduction handling
 Monthly payroll generation
 Payslip generation
 Record keeping of bonuses and leaves

Required Tables

Employees

Field Name Data Type Description

emp_id INT (PK) Unique employee ID
name VARCHAR Employee's full name
department VARCHAR Department of the employee
join_date DATE Joining date of the employee

Salary

Field Name Data Type Description

salary_id INT (PK) Unique salary ID
emp_id INT (FK) Reference to employee ID
base_salary DECIMAL Basic salary amount
bonus DECIMAL Bonus amount
deductions DECIMAL Deduction amount
net_salary DECIMAL Final calculated salary
pay_date DATE Date of salary payment

Leave

Field Name Data Type Description

leave_id INT (PK) Unique leave ID
emp_id INT (FK) Reference to employee ID
leave_type VARCHAR Type of leave taken
leave_days INT Number of leave days taken
leave_date DATE Date of leave

Relationships Summary

 An Employee can have multiple Salary and Leave records

 Salary records include base salary, deductions, and bonuses to compute net salary
Sample Data

Employees

emp_id name department join_date

1 Ravi IT 2023-06-15

Salary

salary_id emp_id base_salary bonus deductions net_salary pay_date

1001 1 40000 2000 1000 41000 2025-04-01

Leave

leave_id emp_id leave_type leave_days leave_date

301 1 Sick 2 2025-03-28

SQL Components

Functions and Procedures

1. Procedure to calculate and insert monthly salary

2. Procedure to generate payslip for an employee
3. Procedure to update deductions based on leave records

Views

4. Monthly payroll summary

5. List of employees with net salary
6. Leave summary for all employees

Nested Queries
7. Employees with more than 5 leave days in a month
8. Employees with salary above department average
9. Salaries paid in the current financial year

Join-Based Queries
10. Salary report with employee details
11. Leave and salary report of employees
12. Department-wise salary expenditure report
10. Project Title: Personal Finance Tracker System

Problem Definition This system helps users manage and track their personal finances by
recording income and expenses, setting budgets, and generating spending reports. It improves
financial awareness and helps with better money management.

Features

 Record income and expenses

 Categorize transactions
 Monthly budget setting
 Spending analysis reports
 Track savings goals

Required Tables

Users

Field Name Data Type Description

user_id INT (PK) Unique user ID
name VARCHAR Full name of the user
email VARCHAR Email ID
created_date DATE Account creation date

Transactions

Field Name Data Type Description

trans_id INT (PK) Unique transaction ID
user_id INT (FK) Reference to user
amount DECIMAL Transaction amount
type VARCHAR Income or Expense
category VARCHAR Category like food, travel, etc.
date DATE Date of transaction

Budget

Field Name Data Type Description

budget_id INT (PK) Unique budget ID
user_id INT (FK) Reference to user
category VARCHAR Category for the budget
amount DECIMAL Budgeted amount
month VARCHAR Month of the budget

Relationships Summary

 One User has multiple Transactions

 One User has multiple Budgets
Sample Data

Users

user_id name email created_date

1 Ravi ravi@email.com 2025-01-01

Transactions

trans_id user_id amount type category date

101 1 500 Expense Food 2025-04-01
102 1 2000 Income Salary 2025-04-01

Budget

budget_id user_id category amount month

1 1 Food 3000 April

SQL Components

Functions and Procedures

1. Procedure to insert transaction

2. Function to calculate monthly savings
3. Procedure to update budget

Views
4. Monthly spending report
5. Category-wise expense summary
6. Budget vs Actual spending

Nested Queries
7. Users who exceeded budget
8. Highest spending category
9. Transactions above average expense

Join-Based Queries
10. Income and expenses of user
11. Budget utilization report
12. Monthly finance summary
11. Project Title: Restaurant Delivery Management System

Problem Definition This system manages the delivery operations of a restaurant, including
order tracking, delivery personnel assignments, and real-time status updates. It ensures timely
deliveries and enhances customer satisfaction.

Features

 Manage restaurant orders

 Assign deliveries to staff
 Track delivery status
 Customer feedback on delivery
 Delivery time analysis

Required Tables

Customers

Field Name Data Type Description

customer_id INT (PK) Unique customer ID
name VARCHAR Full name of the customer
phone VARCHAR Contact number
address VARCHAR Delivery address

Orders

Field Name Data Type Description

order_id INT (PK) Unique order ID
customer_id INT (FK) Reference to customer
order_date DATE Date of order
status VARCHAR Current status (Pending, Delivered)

DeliveryStaff

Field Name Data Type Description

staff_id INT (PK) Unique delivery staff ID
name VARCHAR Staff name
phone VARCHAR Contact number

Delivery

Field Name Data Type Description

delivery_id INT (PK) Unique delivery ID
order_id INT (FK) Reference to order
staff_id INT (FK) Assigned staff
delivery_time TIME Time taken to deliver
delivery_date DATE Date of delivery
Relationships Summary

 One Customer has multiple Orders

 One Order has one Delivery
 One DeliveryStaff handles multiple Deliveries

Sample Data

Customers

customer_id name phone address

1 Ravi 9123456789 Guntur

Orders

order_id customer_id order_date status

101 1 2025-04-07 Delivered

DeliveryStaff

staff_id name phone

1 Ajay 9876543210

Delivery

delivery_id order_id staff_id delivery_time delivery_date

1 101 1 00:30:00 2025-04-07

SQL Components

Functions and Procedures

1. Procedure to assign delivery staff

2. Function to calculate average delivery time
3. Procedure to update order status

Views

4. Daily delivery report

5. Staff-wise delivery performance
6. Orders with delayed delivery

Nested Queries

7. Orders not delivered within 30 minutes

8. Staff with most deliveries
9. Customer with highest orders
Join-Based Queries

10. Order and delivery status

11. Delivery summary by date
12. Staff-wise delivery count
12. Project Title: Voice-Based Transport Enquiry System

Problem Definition This system uses voice commands to help users inquire about transportation
options, timings, and availability. It simplifies the process of finding transport details, especially
for visually impaired or technically challenged users.

Features

 Voice recognition interface

 Query transport schedules
 Route information retrieval
 User-friendly and accessible
 Real-time updates

Required Tables

Users

Field Name Data Type Description

user_id INT (PK) Unique user ID
name VARCHAR User name
contact VARCHAR Contact number

Transport

Field Name Data Type Description

transport_id INT (PK) Unique transport ID
type VARCHAR Type (Bus, Train)
route VARCHAR Route details
departure_time TIME Departure time
arrival_time TIME Arrival time

Queries

Field Name Data Type Description

query_id INT (PK) Unique query ID
user_id INT (FK) Reference to user
transport_id INT (FK) Reference to transport
query_time TIMESTAMP Time of voice query

Relationships Summary

 One User can make multiple Queries

 Each Query refers to a specific Transport
Sample Data

Users

user_id name contact

1 Sita 9876543210

Transport

transport_id type route departure_time arrival_time

101 Bus Guntur to Vijayawada 08:00:00 10:30:00

Queries

query_id user_id transport_id query_time

1 1 101 2025-04-07 07:45:00

SQL Components

Functions and Procedures

1. Function to fetch next available transport

2. Procedure to log user queries
3. Function to calculate journey time

Views

4. Daily user queries summary

5. Transport usage analytics
6. Frequently queried routes

Nested Queries

7. Most queried transport type

8. Users with multiple queries in a day
9. Delayed transport schedules

Join-Based Queries

10. User transport query history

11. Query and transport details
12. Transport schedule by route
13. Project Title: Where is My Professor

Problem Definition This project tracks and provides real-time information on the availability
and location of professors within a campus. It aims to reduce the time students spend searching
for faculty and enhances faculty-student interaction.

Features

 Real-time professor location updates

 Office hours scheduling
 Student-professor interaction history
 Notifications for availability
 Admin interface to update faculty details

Required Tables

Professors

Field Name Data Type Description

prof_id INT (PK) Unique professor ID
name VARCHAR Professor name
department VARCHAR Department name
email VARCHAR Email address

Locations

Field Name Data Type Description

location_id INT (PK) Unique location ID
building VARCHAR Building name
room VARCHAR Room number

Availability

Field Name Data Type Description

avail_id INT (PK) Unique availability ID
prof_id INT (FK) Reference to professor
location_id INT (FK) Reference to location
date DATE Date of availability
start_time TIME Start time
end_time TIME End time

Relationships Summary

 One Professor can have multiple Availability entries

 Each Availability entry links to a Location
Sample Data

Professors

prof_id name department email

1 Dr. Ramesh CSE ramesh@college.edu

Locations

location_id building room

1 Academic Block A 202

Availability

avail_id prof_id location_id date start_time end_time

1 1 1 2025-04-07 10:00:00 12:00:00

SQL Components

Functions and Procedures

1. Function to fetch professor availability

2. Procedure to insert availability entry
3. Function to check current professor location

Views

4. Today's available professors

5. Room-wise faculty presence
6. Department-wise availability list

Nested Queries

7. Professors available after a specific time

8. Frequently available rooms
9. Professors with consistent availability

Join-Based Queries

10. Availability details with location info

11. Professor-wise location history
12. Upcoming availability schedule
14. Project Title: Wholesale Management System

Problem Definition The project is designed to manage and streamline wholesale business
operations. It focuses on bulk transactions, inventory tracking, customer management, and sales
analytics.

Features

 Product inventory tracking

 Wholesale customer records
 Order and invoice generation
 Sales reporting and analytics
 Supplier management

Required Tables

Products

Field Name Data Type Description

product_id INT (PK) Unique product ID
name VARCHAR Product name
category VARCHAR Product category
price DECIMAL Price per unit
stock_quantity INT Current stock available

Customers

Field Name Data Type Description

customer_id INT (PK) Unique customer ID
name VARCHAR Customer name
contact VARCHAR Contact number
address VARCHAR Address details

Orders

Field Name Data Type Description

order_id INT (PK) Unique order ID
customer_id INT (FK) Reference to Customers
order_date DATE Date of the order
total_amount DECIMAL Total value of order

Order_Items

Field Name Data Type Description

order_item_id INT (PK) Unique item ID
order_id INT (FK) Reference to Orders
product_id INT (FK) Reference to Products
quantity INT Quantity ordered
price DECIMAL Price at the time of order

Suppliers

Field Name Data Type Description

supplier_id INT (PK) Unique supplier ID
name VARCHAR Supplier name
contact VARCHAR Contact info
product_supplied VARCHAR Products they supply

Relationships Summary

 One Customer can place multiple Orders

 One Order can have multiple Order_Items
 One Order_Item is linked to one Product
 Multiple Products can be linked to multiple Suppliers

Sample Data

Products

product_id name category price stock_quantity

1 Rice Grains 40.00 1000

Customers

customer_id name contact address

1 ABC Retailers 9876543210 Main Bazar

Orders

order_id customer_id order_date total_amount

1 1 2025-04-07 4000.00

Order_Items

order_item_id order_id product_id quantity price

1 1 1 100 40.00

Suppliers

supplier_id name contact product_supplied

1 XYZ Traders 9123456780 Rice
SQL Components

Functions and Procedures

1. Function to calculate total stock value

2. Procedure to insert new order and update stock
3. Function to retrieve top-selling products

Views

4. Customer order summary

5. Product-wise sales view
6. Supplier-product mapping

Nested Queries

7. Top 5 customers by order value

8. Products with low stock
9. Orders above average value

Join-Based Queries

10. Order details with customer and product info

11. Supplier-wise product list
12. Monthly sales report
15. Project Title: Digital Learning Management System

Problem Definition This project is based on real-world education sector needs. The goal is to
develop a digital learning management system that stores and encodes student, course, and
faculty data, tracks course enrollments, and manages user/employee files. It supports
administrative access to monitor course progress and academic records.

Features

 Encode and store student and faculty information

 Track course enrollments and progress
 Provide administrative control and monitoring
 Manage user and faculty profiles
 Generate reports for academic and enrollment history

Required Tables

Students

Field Name Data Type Description

student_id INT (PK) Unique ID
Name VARCHAR Full Name
Email VARCHAR (UNIQUE) Email
Phone VARCHAR Contact
department VARCHAR Department Name
enrolled_at DATETIME Enrollment Date

Faculty
Field Name Data Type Description
faculty_id INT (PK) Unique ID
Name VARCHAR Full Name
Email VARCHAR Email Address
department VARCHAR Department
joined_at DATETIME Joining Date

Courses
Field Name Data Type Description
course_id INT (PK) Unique Course ID
faculty_id INT (FK) Linked Faculty
course_name VARCHAR Course Title
Credits INT Number of Credits
Semester VARCHAR Semester Details

Enrollments
Field Name Data Type Description
enrollment_id INT (PK) Unique ID
student_id INT (FK) Linked Student
course_id INT (FK) Linked Course
enrolled_on DATETIME Date of Enrollment
Progress VARCHAR Completed/Ongoing

Relationships Summary

 One Faculty → many Courses

 One Student → many Enrollments
 One Course → many Enrollments

Sample Data

Students

student_id name email phone department enrolled_at

1 Alice alice@example.com 9876543210 CSE 2023-08-01 09:00:00
2 Bob bob@example.com 9876500000 ECE 2023-08-10 10:30:00

Faculty
faculty_id name email department joined_at
1 Dr. Smith smith@univ.com CSE 2020-06-15 09:00:00
2 Dr. Clara clara@univ.com ECE 2021-09-01 11:15:00

Courses
course_id faculty_id course_name credits semester
1 1 DBMS 4 III
2 2 Networks 3 IV

Enrollments
enrollment_id student_id course_id enrolled_on progress
1 1 1 2023-08-05 11:00:00 Ongoing
2 2 2 2023-08-15 12:00:00 Completed

SQL Components

Triggers

1. Trigger to update course status upon completion

2. Trigger to notify students when new course is added
3. Trigger to log course dropouts
Stored Procedures

4. Add new student and enroll in course

5. Assign faculty to course
6. Update student progress

Views

7. Enrollment summaries per student

8. Top performing students by department
9. Course-wise student enrollment

Nested Queries

10. Students enrolled in more than 3 courses

11. Courses without any enrollment
12. Faculties handling more than 2 courses

Join-Based Queries

13. List of students with their enrolled course names

14. Faculty-wise course list and student count
15. Course progress per student
16. Project Title: Sales Analysis: Walmart Sales Data Analysis

Problem Definition This project involves building a sales analysis system using Walmart’s sales
dataset. The objective is to organize data across multiple stores and departments, perform
detailed analysis on sales performance, and provide meaningful insights into trends, patterns, and
profitability across dates and seasons.

Features

 Store-wise sales monitoring

 Weekly and seasonal trend analysis
 Department-wise sales breakdown
 Sales prediction based on historical data
 Insightful dashboards using SQL views

Required Tables

Stores

Field Name Data Type Description

store_id INT (PK) Unique Store ID
store_type VARCHAR Type of Store (A, B, C)
Location VARCHAR City or Region

Departments
Field Name Data Type Description
dept_id INT (PK) Unique Department ID
dept_name VARCHAR Department Name

Sales
Field Name Data Type Description
sale_id INT (PK) Unique Sale ID
store_id INT (FK) Linked Store ID
dept_id INT (FK) Linked Department ID
sale_date DATE Date of Sale
weekly_sales FLOAT Sales Amount
holiday_flag BOOLEAN Holiday Indicator

Relationships Summary

 One Store → many Sales

 One Department → many Sales
 One Sale is uniquely associated with one store and one department
Sample Data

Stores

store_id store_type location

1 A Dallas
2 B Austin

Departments
dept_id dept_name
101 Grocery
102 Electronics

Sales
sale_id store_id dept_id sale_date weekly_sales holiday_flag
1 1 101 2023-08-01 12500.50 FALSE
2 1 102 2023-08-08 10000.75 TRUE
3 2 101 2023-08-01 11300.00 FALSE

SQL Components

Triggers

1. Trigger to log high-sales events

2. Trigger to validate non-negative sales input
3. Trigger to flag holiday sales

Stored Procedures

4. Calculate total sales by store and date range

5. Insert new department sales data
6. Predict sales for next week based on trends

Views

7. Monthly sales per store

8. Top performing departments
9. Holiday vs non-holiday sales comparison

Nested Queries

10. Departments with highest sales in last month

11. Stores with declining weekly sales trend
12. Sales contribution by each department per store
Join-Based Queries

13. Store-wise department sales summary

14. Weekly sales and department details
15. Comparison of sales during holidays vs regular weeks
17. Project Title: Social Media Analytics - Creating a Social Media Database

Problem Definition The project focuses on developing a comprehensive database system to

manage and analyze data from various social media platforms. It helps track user interactions,
engagement metrics, post performance, and trends to assist businesses in making informed
marketing decisions.

Features

 User and post tracking

 Engagement metrics analysis (likes, comments, shares)
 Hashtag usage trends
 Follower growth tracking
 Visualization-ready data using SQL views

Required Tables

Users

Field Name Data Type Description

user_id INT (PK) Unique User ID
Username VARCHAR Social Media Handle
joined_date DATE Account Creation Date
Country VARCHAR User’s Country

Posts
Field Name Data Type Description
post_id INT (PK) Unique Post ID
user_id INT (FK) Linked User ID
Content TEXT Post Content
post_date DATE Date of Post
Hashtags VARCHAR Used Hashtags

Engagements
Field Name Data Type Description
engagement_id INT (PK) Unique Engagement ID
post_id INT (FK) Linked Post ID
Likes INT Number of Likes
Comments INT Number of Comments
Shares INT Number of Shares

Relationships Summary

 One User → many Posts

 One Post → many Engagements
 Each Engagement is associated with a specific post
Sample Data

Users

user_id username joined_date country

1 @marktech 2021-05-12 USA
2 @traveldiaries 2022-01-01 UK

Posts
post_id user_id content post_date hashtags
101 1 Exciting updates ahead 2023-01-15 #update,#tech
102 2 Exploring the Alps 2023-02-20 #travel,#alps

Engagements
engagement_id post_id likes comments shares
1 101 250 45 30
2 102 320 65 50

SQL Components

Triggers

1. Trigger to notify on high-performing posts

2. Trigger to limit hashtags per post
3. Trigger to auto-update engagement summary

Stored Procedures

4. Fetch top 5 posts by engagement

5. Insert new post with hashtags
6. Calculate monthly follower growth

Views

7. Daily post engagement statistics

8. Trending hashtags of the week
9. User-wise engagement summary

Nested Queries

10. Users with most engaging posts

11. Hashtags with highest average likes
12. Posts with engagement above average
Join-Based Queries

13. Engagement details with post content

14. User-wise post and engagement data
15. Country-wise social media engagement trend
18. Project Title: Customer Feedback Management System for Sentiment
Analysis
Problem Definition This project aims to develop a SQL-based database system for storing and
analyzing customer feedback data. It facilitates sentiment classification, trend analysis, and
reporting to help businesses understand customer opinions and improve services/products.

Features

 Storage of structured customer feedback

 Sentiment labeling and categorization
 Product and service-level feedback tracking
 Time-based feedback trend analysis
 Integration with sentiment analysis tools

Required Tables

Customers

Field Name Data Type Description

customer_id INT (PK) Unique ID for the customer
name VARCHAR Customer Name
email VARCHAR Email Address
country VARCHAR Customer’s Country

Products
Field Name Data Type Description
product_id INT (PK) Unique Product ID
name VARCHAR Product Name
category VARCHAR Product Category

Feedback
Field Name Data Type Description
feedback_id INT (PK) Unique Feedback ID
customer_id INT (FK) Linked Customer ID
product_id INT (FK) Linked Product ID
feedback_text TEXT Customer’s Feedback
feedback_date DATE Date of Feedback
sentiment VARCHAR Classified Sentiment (Positive/Negative/Neutral)

Relationships Summary

 One Customer → many Feedback

 One Product → many Feedback
 Each Feedback connects a Customer to a Product
Sample Data

Customers

customer_id name email country

1 John Doe john@example.com USA
2 Aditi Sharma aditi@example.com India

Products
product_id name category
101 AlphaPhone Electronics
102 QuickMeal Food

Feedback
feedback_id customer_id product_id feedback_text feedback_date sentiment
1 1 101 Great performance 2024-11-01 Positive
and battery
2 2 102 Packaging was poor 2024-11-05 Negative

SQL Components

Triggers

1. Trigger to flag negative feedback

2. Trigger to auto-categorize feedback sentiment
3. Trigger to update product sentiment score

Stored Procedures

4. Insert new feedback and assign sentiment

5. Fetch product-wise sentiment breakdown
6. Retrieve top 5 customers by feedback count

Views

7. Daily feedback sentiment report

8. Product-wise sentiment analysis
9. Country-wise feedback distribution

Nested Queries
10. Products with highest positive feedback
11. Customers giving most feedback
12. Average sentiment per product category

Join-Based Queries

13. Feedback details with customer and product info

14. Sentiment trend over time per product
15. Feedback sentiment by country
19. Project Title: Centralized College Database System

Problem Definition This project focuses on creating a centralized database system for managing
all data related to a college. It enables efficient handling of student records, course details,
faculty information, attendance, and results.

Features

 Centralized storage of academic and administrative information

 Role-based access for students, faculty, and admin
 Efficient attendance and result tracking
 Supports multiple departments and courses
 Simplifies reporting and analytics

Required Tables

Students

Field Name Data Type Description

student_id INT (PK) Unique Student ID
name VARCHAR Student Name
dob DATE Date of Birth
email VARCHAR Email Address
department VARCHAR Department Name

Faculty
Field Name Data Type Description
faculty_id INT (PK) Unique Faculty ID
name VARCHAR Faculty Name
email VARCHAR Email Address
department VARCHAR Department Name

Courses
Field Name Data Type Description
course_id INT (PK) Unique Course ID
name VARCHAR Course Name
department VARCHAR Department Name
credits INT Number of Credits

Enrollment
Field Name Data Type Description
enrollment_id INT (PK) Enrollment ID
student_id INT (FK) Linked Student ID
course_id INT (FK) Linked Course ID
semester VARCHAR Semester Info
Attendance
Field Name Data Type Description
attendance_id INT (PK) Unique Attendance ID
student_id INT (FK) Linked Student ID
course_id INT (FK) Linked Course ID
date DATE Date of Attendance
status VARCHAR Present/Absent

Results
Field Name Data Type Description
result_id INT (PK) Unique Result ID
student_id INT (FK) Linked Student ID
course_id INT (FK) Linked Course ID
marks INT Marks Obtained
grade VARCHAR Grade Assigned

Relationships Summary

 One Student can enroll in many Courses

 One Course can be taught by many Faculty
 One Student can have multiple Attendance and Results

Sample Data

Students

student_id name Dob email department

1 Ravi Kumar 2002-04-10 ravi@college.edu IT
2 Sneha Rani 2001-08-15 sneha@college.edu CSE

Courses
course_id name department credits
101 DBMS IT 4
102 OOP CSE 3

Results
result_id student_id course_id marks grade
1 1 101 85 A
2 2 102 78 B

SQL Components

Triggers

1. Trigger to update grade when marks are entered

2. Trigger to notify low attendance
3. Trigger to validate enrollment before result entry
Stored Procedures

4. Insert new student with default values

5. Calculate GPA for a student
6. Generate course-wise attendance report

Views
7. Department-wise student report
8. Semester-wise result summary
9. Course enrollment statistics

Nested Queries
10. Students with highest marks in each course
11. Average marks per department
12. Students with perfect attendance

Join-Based Queries
13. List of students with course and result details
14. Faculty-wise course and student list
15. Attendance report by student and course
20. Project Title: Hotel Reservation System

Problem Definition The Hotel Reservation System is designed to simplify the process of
booking and managing hotel room reservations. It enables efficient management of room
availability, guest information, and billing.

Features

 Online booking of rooms with availability check

 Customer profile management
 Room categorization and pricing
 Automated billing and payment tracking
 Reporting for occupancy and revenue

Required Tables

Guests

Field Name Data Type Description

guest_id INT (PK) Unique Guest ID
name VARCHAR Guest Name
email VARCHAR Email Address
phone VARCHAR Contact Number

Rooms
Field Name Data Type Description
room_id INT (PK) Unique Room ID
room_type VARCHAR Type of Room (Single, Double, Suite)
price DECIMAL Price per Night
status VARCHAR Available/Booked

Bookings
Field Name Data Type Description
booking_id INT (PK) Unique Booking ID
guest_id INT (FK) Linked Guest ID
room_id INT (FK) Linked Room ID
check_in DATE Check-in Date
check_out DATE Check-out Date

Payments
Field Name Data Type Description
payment_id INT (PK) Unique Payment ID
booking_id INT (FK) Linked Booking ID
amount DECIMAL Payment Amount
payment_date DATE Date of Payment
method VARCHAR Payment Method
Relationships Summary

 One Guest can have multiple Bookings

 One Room can be associated with multiple Bookings
 One Booking can have one Payment

Sample Data

Guests

guest_id name Email phone

1 John Doe john@example.com 9876543210
2 Alice Ray alice@example.com 8765432109

Rooms
room_id room_type price status
101 Single 1500.00 Available
102 Double 2500.00 Booked

Bookings
booking_id guest_id room_id check_in check_out
1 1 101 2024-03-10 2024-03-12
2 2 102 2024-03-15 2024-03-17

SQL Components

Triggers

1. Trigger to update room status upon booking

2. Trigger to prevent overbooking
3. Trigger to generate payment due alert

Stored Procedures

4. Add new room with initial status

5. Calculate total amount for a booking
6. Retrieve available rooms for a date range

Views

7. Booking history per guest

8. Daily room occupancy report
9. Monthly revenue summary

Nested Queries
10. Guests with more than three bookings
11. Average stay duration per guest
12. Rooms with highest occupancy rate
Join-Based Queries
13. Booking details with guest and room info
14. Payment details with guest name
15. List of currently available rooms
21. Project Title: International Debt Analysis Project

Problem Definition The International Debt Analysis Project focuses on creating a system to
analyze international debts across countries. The goal is to manage and evaluate economic debt
statistics to aid financial institutions, economists, and analysts in understanding trends and
making decisions.

Features

 Country-wise debt recording and categorization

 Debt type and sector management
 Time-series analysis of debt accumulation
 Support for multi-currency and conversion rates
 Summary reports and visualization-ready data

Required Tables

Countries

Field Name Data Type Description

country_id INT (PK) Unique Country ID
country_name VARCHAR Name of the Country
region VARCHAR Geographical Region

Debt_Types

Field Name Data Type Description

debt_type_id INT (PK) Unique Debt Type ID
type_name VARCHAR Type of Debt (External, Internal, Short-term, etc.)

Debt_Records

Field Name Data Type Description

debt_id INT (PK) Unique Debt ID
country_id INT (FK) Linked Country ID
debt_type_id INT (FK) Linked Debt Type ID
year YEAR Year of Record
amount DECIMAL Debt Amount
currency VARCHAR Currency Code

Exchange_Rates
Field Name Data Type Description
currency VARCHAR (PK) Currency Code
rate_to_usd DECIMAL Conversion Rate to USD
updated_on DATE Date of Last Update

Relationships Summary
  One Country can have multiple Debt_Records
 One Debt_Type can be associated with multiple Debt_Records
 One Debt_Record uses one Currency for conversion reference

Sample Data

Countries

country_id country_name region

1 India Asia
2 USA North America

Debt_Types
debt_type_id type_name
1 External Debt
2 Internal Debt

Debt_Records
debt_id country_id debt_type_id year amount currency
1 1 1 2020 450000.00 INR
2 2 2 2021 300000.00 USD

SQL Components

Triggers

1. Trigger to log changes in debt records

2. Trigger to convert amounts on currency update
3. Trigger to alert if debt exceeds threshold

Stored Procedures

4. Add new debt record

5. Calculate total debt for a country in USD
6. Generate yearly debt trend report

Views

7. Total debt by country

8. Debt distribution by type
9. Year-wise comparative debt chart

Nested Queries

10. Countries with increasing debt trends

11. Average debt over 5 years
12. Debt-to-GDP ratio estimation (assuming GDP table exists)
Join-Based Queries

13. Debt details with country and type info

14. Debt in USD with exchange rates
15. High debt countries by year
22. Project Title: Flight Booking System

Problem Definition The Flight Booking System project aims to streamline the booking,
scheduling, and management of airline flights. It facilitates customer reservations, tracks flight
availability, and manages airline schedules efficiently.

Features

 Search flights by date, route, or airline

 Real-time seat availability
 Customer registration and booking history
 Flight scheduling and status tracking
 Invoice generation and ticket issuance

Required Tables

Airlines

Field Name Data Type Description

airline_id INT (PK) Unique Airline ID
name VARCHAR Airline Name
country VARCHAR Operating Country

Flights
Field Name Data Type Description
flight_id INT (PK) Unique Flight ID
airline_id INT (FK) Linked Airline ID
departure VARCHAR Departure City
arrival VARCHAR Arrival City
departure_time DATETIME Scheduled Departure Time
arrival_time DATETIME Scheduled Arrival Time
status VARCHAR Flight Status (On-time, Delayed, etc.)

Customers
Field Name Data Type Description
customer_id INT (PK) Unique Customer ID
name VARCHAR Customer Name
email VARCHAR Email Address
phone VARCHAR Contact Number

Bookings
Field Name Data Type Description
booking_id INT (PK) Unique Booking ID
customer_id INT (FK) Linked Customer ID
flight_id INT (FK) Linked Flight ID
booking_date DATE Date of Booking
seat_number VARCHAR Seat Allocated
status VARCHAR Booking Status (Confirmed, Cancelled)

Relationships Summary

 One Airline operates multiple Flights

 One Flight can have many Bookings
 One Customer can make multiple Bookings

Sample Data

Airlines

airline_id name country

1 Air India India
2 Delta Air USA

Flights
flight_id airline_id departure arrival departure_time arrival_time status
1 1 Delhi Mumbai 2025-05-10 2025-05-10 On-time
08:00:00 10:00:00
2 2 New York Chicago 2025-05-11 2025-05-11 Delayed
09:30:00 11:15:00

Customers
customer_id name Email phone
1 Ravi ravi@gmail.com 9999988888
2 Sneha sneha@yahoo.com 8888877777

Bookings
booking_id customer_id flight_id booking_date seat_number status
1 1 1 2025-05-01 A12 Confirmed
2 2 2 2025-05-02 B5 Cancelled

SQL Components

Triggers

1. Trigger to update seat availability on booking

2. Trigger to notify customer on booking status change
3. Trigger to log changes in flight schedule

Stored Procedures

4. Add new flight booking

5. Fetch booking history for a customer
6. Cancel a booking and update seats
Views
7. Daily flight schedule
8. Confirmed bookings by customer
9. Flight occupancy summary

Nested Queries
10. Flights with highest number of bookings
11. Customers with more than 5 bookings
12. Flights cancelled in the last 7 days

Join-Based Queries
13. Booking details with customer and flight info
14. Upcoming flights for a specific airline
15. Flights booked by customer with email
23. Project Title: Railway System Database

Problem Definition The Railway System Database project is designed to manage the
scheduling, ticketing, and operations of trains. It ensures efficient handling of train schedules,
seat reservations, passenger details, and ticket transactions.

Features

 Train schedule management

 Passenger information management
 Ticket booking and cancellation
 Real-time seat availability
 Fare calculation and receipt generation

Required Tables

Trains

Field Name Data Type Description

train_id INT (PK) Unique Train ID
name VARCHAR Train Name
source VARCHAR Starting Station
destination VARCHAR Ending Station
departure_time DATETIME Scheduled Departure Time
arrival_time DATETIME Scheduled Arrival Time

Passengers

Field Name Data Type Description

passenger_id INT (PK) Unique Passenger ID
name VARCHAR Passenger Name
email VARCHAR Email Address
phone VARCHAR Contact Number

Tickets

Field Name Data Type Description

ticket_id INT (PK) Unique Ticket ID
passenger_id INT (FK) Linked Passenger ID
train_id INT (FK) Linked Train ID
booking_date DATE Date of Booking
seat_number VARCHAR Seat Number
status VARCHAR Booking Status (Booked, Cancelled)

Relationships Summary

 One Train has multiple Tickets

 One Passenger can have multiple Tickets
Sample Data

Trains

train_id name source destination departure_time arrival_time

1 Shatabdi Delhi Bhopal 2025-06-01 06:00:00 2025-06-01 12:00:00
2 Duronto Mumbai Pune 2025-06-02 07:00:00 2025-06-02 10:00:00

Passengers
passenger_id name Email phone
1 Aryan aryan@mail.com 9999911111
2 Meera meera@mail.com 8888822222

Tickets
ticket_id passenger_id train_id booking_date seat_number status
1 1 1 2025-05-25 A1 Booked
2 2 2 2025-05-26 B3 Cancelled

SQL Components

Triggers

1. Trigger to check seat availability before booking

2. Trigger to send confirmation upon ticket booking
3. Trigger to log ticket cancellation

Stored Procedures

4. Book a new ticket

5. View passenger booking history
6. Cancel a ticket and update seat status

Views

7. Train schedules by date

8. Active bookings of passengers
9. Seat occupancy per train

Nested Queries
10. Trains with maximum tickets booked
11. Passengers with more than 3 bookings
12. Tickets cancelled in the last month

Join-Based Queries
13. Ticket details with passenger and train info
14. Trains departing from a specific station
15. Booked tickets for a specific passenger
24. Project Title: RPA Customer Segmentation Project

Problem Definition This project aims to segment customers using Robotic Process Automation
(RPA) to automate the classification of customers based on their buying behavior, demographic
information, and transaction history. It helps businesses target marketing strategies effectively.

Features

 Automated customer classification

 Transaction pattern analysis
 Demographic clustering
 Marketing strategy suggestions
 Improved customer retention insights

Required Tables

Customers

Field Name Data Type Description

customer_id INT (PK) Unique Customer ID
name VARCHAR Customer Name
age INT Age of Customer
gender VARCHAR Gender
income FLOAT Monthly Income
location VARCHAR City or Region

Transactions
Field Name Data Type Description
transaction_id INT (PK) Unique Transaction ID
customer_id INT (FK) Linked Customer ID
amount FLOAT Purchase Amount
transaction_date DATE Date of Purchase

Segments
Field Name Data Type Description
segment_id INT (PK) Unique Segment ID
name VARCHAR Segment Name (e.g., High Value, Occasional Buyer)
description TEXT Description of Segment

Relationships Summary

 One Customer can have multiple Transactions

 Customers are assigned to one Segment based on their data
Sample Data

Customers

customer_id name age gender income location

1 Raj 30 Male 50000 Delhi
2 Anu 25 Female 60000 Mumbai

Transactions
transaction_id customer_id amount transaction_date
101 1 2500 2025-04-01
102 2 3000 2025-04-02

Segments
segment_id name description
1 High Value Frequent high spenders
2 Low Engagement Rare buyers with low spending

SQL Components

Triggers

1. Trigger to update segment on customer data change

2. Trigger to log significant transactions
3. Trigger to notify on first purchase

Stored Procedures

4. Assign customer to a segment

5. Retrieve customer segment data
6. Generate segmentation report

Views

7. High-value customers
8. Recent active customers
9. Segment-wise customer distribution

Nested Queries
10. Customers with above average spending
11. Top 5 recent buyers
12. Customers not transacting in the last 6 months

Join-Based Queries

13. Customer details with transaction summary

14. Segment-wise average transaction
15. Customers by location and segment
25. Project Title: Fraud Detection System

Problem Definition This project focuses on detecting fraudulent activities using transactional
data analysis. It helps financial institutions and businesses identify suspicious patterns and take
proactive measures to reduce financial risk and maintain trust.

Features

 Real-time fraud alerts

 Risk scoring system
 Transaction analysis for anomalies
 Suspicious account flagging
 Historical fraud pattern comparison

Required Tables

Users

Field Name Data Type Description

user_id INT (PK) Unique User ID
name VARCHAR Name of User
email VARCHAR Email Address
phone VARCHAR Contact Number
registration_date DATE Date of Registration

Transactions
Field Name Data Type Description
transaction_id INT (PK) Unique Transaction ID
user_id INT (FK) Linked User ID
amount FLOAT Transaction Amount
location VARCHAR Transaction Location
transaction_time TIMESTAMP Date and Time of Transaction
status VARCHAR Transaction Status (Valid/Fraud)

Fraud Alerts
Field Name Data Type Description
alert_id INT (PK) Unique Alert ID
transaction_id INT (FK) Linked Transaction ID
reason TEXT Reason for Flagging
alert_time TIMESTAMP Time of Alert Generation

Relationships Summary

 One User can make multiple Transactions

 Fraud Alerts are associated with flagged Transactions
Sample Data

Users

user_id name email phone registration_date

1 Neha neha@gmail.com 9876543210 2022-01-01
2 Ravi ravi@gmail.com 9876501234 2022-02-01

Transactions
transaction_id user_id amount location transaction_time status
1001 1 20000 Delhi 2025-04-01 09:00:00 Valid
1002 2 75000 Russia 2025-04-01 09:10:00 Fraud

Fraud Alerts
alert_id transaction_id reason alert_time
1 1002 Unusual location 2025-04-01 09:15:00

SQL Components

Triggers

1. Trigger to generate fraud alert based on amount threshold

2. Trigger to monitor unusual transaction times
3. Trigger to flag transactions from blacklisted regions

Stored Procedures
4. Generate fraud report
5. Fetch all flagged transactions
6. Analyze transaction history for a user

Views
7. Fraudulent transactions view
8. High-value suspicious activity
9. Daily fraud alert summary

Nested Queries
10. Transactions above average amount in non-local regions
11. Users with more than one fraud alert
12. Latest flagged transactions per user

Join-Based Queries
13. User details with fraud history
14. Location-wise fraud analysis
15. Transaction and alert summary
26. Project Title: Statistical Analysis and Data Mining with SQL

Problem Definition This project aims to perform statistical analysis and data mining operations
on large datasets using SQL. It supports deriving meaningful insights, discovering hidden
patterns, and making data-driven decisions efficiently.

Features

 Data aggregation and summarization

 Statistical computations (mean, median, mode)
 Pattern discovery using SQL
 Trend analysis and reporting
 Data filtering and grouping operations

Required Tables

Dataset

Field Name Data Type Description

record_id INT (PK) Unique Record Identifier
category VARCHAR Data Category
value FLOAT Numeric Value
timestamp TIMESTAMP Time of Entry
label VARCHAR Classification Label

Users
Field Name Data Type Description
user_id INT (PK) Unique User ID
name VARCHAR Name of User
role VARCHAR User Role (Analyst/Admin)

Analysis Logs
Field Name Data Type Description
log_id INT (PK) Unique Log ID
user_id INT (FK) Linked User ID
operation TEXT Analysis Performed
log_time TIMESTAMP Time of Operation

Relationships Summary

 A User can generate multiple Analysis Logs

 The Dataset is used in various analyses performed and logged in Analysis Logs
Sample Data

Dataset

record_id category value timestamp label

1 Sales 1200.5 2025-03-15 10:00:00 High
2 Sales 300.0 2025-03-15 10:05:00 Low

Users
user_id name Role
1 Asha Analyst
2 Vikram Admin

Analysis Logs
log_id user_id operation log_time
1 1 Average Value Computation 2025-04-01 08:30:00

SQL Components

Functions and Procedures

1. Compute average, median, and standard deviation

2. Generate category-wise summaries
3. Perform outlier detection

Views

4. Summary view of dataset by category

5. Recent operations log
6. Trendline analysis over time

Nested Queries
7. Identify top 5 highest values by category
8. Compare mean values between categories
9. Find frequent labels per category

Join-Based Queries
10. Log of analysis with user details
11. Daily activity of analysts
12. Category vs. label distribution summary
27. Project Title: SMS-based Remote Server Monitoring System

Problem Definition This project involves building an SMS-based system to monitor and control
remote servers using SQL. It aims to provide real-time alerts and logs to administrators via SMS
for proactive system management.

Features

 SMS alert on server status

 Logging of server status
 Historical reports and downtime tracking
 User authentication and role-based access
 Remote server status checks

Required Tables

Servers

Field Name Data Type Description

server_id INT (PK) Unique Server ID
server_name VARCHAR Name of Server
location VARCHAR Server Location
ip_address VARCHAR IP Address of Server

Status Logs
Field Name Data Type Description
log_id INT (PK) Unique Log ID
server_id INT (FK) Server Reference
status VARCHAR Server Status (Up/Down)
timestamp TIMESTAMP Time of Status Check

SMS Alerts
Field Name Data Type Description
alert_id INT (PK) Alert Identifier
server_id INT (FK) Server Monitored
message TEXT SMS Alert Message
sent_time TIMESTAMP Time Sent

Admins
Field Name Data Type Description
admin_id INT (PK) Administrator ID
name VARCHAR Admin Name
phone_number VARCHAR Phone Number for SMS
role VARCHAR Access Level (Viewer/Editor)
Relationships Summary

 A Server can have multiple Status Logs

 A Server can send multiple SMS Alerts
 Each SMS Alert is sent to Admins

Sample Data

Servers

server_id server_name location ip_address

1 ServerAlpha Delhi 192.168.1.2
2 ServerBeta Mumbai 192.168.1.3

Status Logs
log_id server_id status timestamp
101 1 Up 2025-04-07 10:00:00
102 2 Down 2025-04-07 10:05:00

SMS Alerts

alert_id server_id message sent_time

501 2 ServerBeta is Down 2025-04-07 10:06:00

Admins

admin_id name phone_number role

1 Ravi 9876543210 Editor

SQL Components

Functions and Procedures

1. Procedure to insert server status

2. Function to check server downtime frequency
3. Trigger to send alert if server goes Down

Views

4. View to monitor current server status

5. Admin alert log summary
6. Daily uptime summary

Nested Queries
7. List servers down more than 3 times in 24 hours
8. Latest SMS alerts per server
9. Server with longest uptime streak
Join-Based Queries
10. Get alert logs with admin details
11. Map servers to location and uptime stats
12. Match SMS alerts with status logs
28. Project Title: Voice Commands Transport Enquiry System

Problem Definition

This project aims to simplify public transport inquiries using voice-based interaction. It enables
users to speak natural language queries to check bus/train schedules, fare details, and route
availability. The system provides easy access to transport information for all users, including the
visually impaired or elderly, using a structured database to deliver instant and relevant results.

Features

 Understand voice commands and convert them into text-based transport queries
 Search for transport options between source and destination
 Display available routes with departure and arrival timings
 Show fare details and route duration
 Maintain station, schedule, and transport type data
 Enable admin-side operations like schedule/fare updates

Required Tables

TransportTypes

Field Name Data Type Description

type_id INT (PK) Unique ID for transport type
type_name VARCHAR 'Bus', 'Train', etc.

Stations

Field Name Data Type Description

station_id INT (PK) Unique station ID
name VARCHAR Station/Stop name
location VARCHAR Area or City

Routes

Field Name Data Type Description

route_id INT (PK) Unique route ID
type_id INT (FK) Linked to TransportTypes
origin_id INT (FK) Starting station
destination_id INT (FK) Ending station
distance_km DECIMAL Route distance in km

Schedule

Field Name Data Type Description

schedule_id INT (PK) Unique schedule ID
route_id INT (FK) Linked route ID
departure_time TIME Departure time
arrival_time TIME Arrival time
days_available VARCHAR Days of operation (e.g., 'Mon,Tue')

Fares

Field Name Data Type Description

fare_id INT (PK) Unique fare ID
route_id INT (FK) Linked route
fare DECIMAL Ticket fare amount

Relationships Summary

 One TransportType → many Routes

 One Route → many Schedules
 One Route → one Fare
 One Route → connects two Stations

Sample Data

TransportTypes

type_id type_name
1 Bus
2 Train

Stations

station_id name location

1 Central Bus Stop Hyderabad
2 Railway Station Secunderabad
3 Uppal Depot Hyderabad

Routes

route_id type_id origin_id destination_id distance_km

1 1 1 2 15.5
2 2 2 3 23.0

Schedule

schedule_id route_id departure_time arrival_time days_available

1 1 08:00:00 08:45:00 Mon,Tue,Wed
2 2 10:00:00 10:50:00 Daily
Fares

fare_id route_id fare

1 1 25.00
2 2 35.50

SQL Components

Triggers

1. Trigger to auto-log schedule updates

2. Trigger to notify admin if no schedule exists for a new route
3. Trigger to archive deleted route details

Stored Procedures

4. Add new route and schedule

5. Get next available transport from source to destination
6. Update fare for a specific route

Views

7. View of all available transports today

8. Route-wise summary with fare and schedule
9. List of stations served by each transport type

Nested Queries

10. Routes with more than 3 daily schedules

11. Stations not having any outgoing transport
12. Routes with highest fare

Join-Based Queries

13. All routes between two stations with full details

14. Transport type-wise route count
15. Station-wise first and last departure time
 29. Project Title: Online Book Store

Problem Definition
The Online Book Store is a platform designed to provide users with the ability to browse,
purchase, and review books online. It allows book buyers to search for books by various
filters such as author, genre, price range, and publication date. The store also allows book
reviews and ratings to help customers make informed decisions.

Features
• User registration and authentication
• Search books by title, author, genre, or price
• Book listings with detailed descriptions and prices
• Ability to add books to a shopping cart
• Order management and checkout process
• Book reviews and ratings
• Admin panel to manage books, orders, and users
• Payment gateway integration for online payments

1. Required Tables

Users

Field Name Data Type Description

user_id INT (PK) Unique user ID
name VARCHAR User's full name
email VARCHAR User's email address
password VARCHAR User's password (encrypted)
role VARCHAR Role (Customer/Admin)

Books

Field Name Data Type Description

book_id INT (PK) Unique book ID
title VARCHAR Title of the book
author VARCHAR Author of the book
genre VARCHAR Genre of the book
description TEXT Detailed book description
price DECIMAL Price of the book
stock_quantity INT Number of copies available in stock
publication_date DATE Date of publication

Orders

Field Name Data Type Description

order_id INT (PK) Unique order ID
user_id INT (FK) Customer's user ID
order_date DATE Date of order
total_amount DECIMAL Total order amount
status VARCHAR Status of the order (Pending/Completed/Cancelled)

Order_Items

Field Name Data Type Description

order_item_id INT (PK) Unique order item ID
order_id INT (FK) Order ID
book_id INT (FK) Book ID
quantity INT Quantity of the book ordered
price DECIMAL Price of the book at the time of order

Reviews

Field Name Data Type Description

review_id INT (PK) Unique review ID
book_id INT (FK) Book ID
user_id INT (FK) User's ID who wrote the review
rating INT Rating (1-5)
review_text TEXT Review description

Relationships Summary

• A User can be a Customer or Admin

• A Customer can place multiple Orders
• An Order can have multiple Order Items
• A Book can have multiple Reviews
• A User can write multiple Reviews

Sample Data

Users

user_id name email role

1 Alice alice@email.com Customer
2 Bob bob@email.com Admin

Books

book_id title author genre price stock_quantity publication_date

101 The Great F. Scott Fiction 10.99 50 1925-04-10
Gatsby Fitzgerald
102 To Kill a Harper Lee Fiction 8.99 40 1960-07-11
Mockingbird
Orders

order_id user_id order_date total_amount status

201 1 2025-04-06 19.98 Completed

Order_Items

order_item_id order_id book_id quantity price

301 201 101 1 10.99
302 201 102 1 8.99

Reviews

review_id book_id user_id rating review_text

401 101 1 5 "An amazing read, truly a classic."
402 102 1 4 "Great story, but a bit slow at times."

SQL Components

Functions and Procedures

1. Procedure to add a new book to the store

2. Procedure to process a new order
3. Procedure to update stock after an order is placed
4. Procedure to add a book review

Views

5. List of all available books

6. List of books in a specific genre
7. List of all orders placed by a user

Nested Queries

8. Books with an average rating greater than 4

9. Customers who have purchased more than 5 books
10. Books published in the last 6 months

Join-Based Queries

11. Orders with customer details

12. Book reviews with customer details
13. Books and their associated reviews and ratings
30. Project Title: Budget Tracking System

Problem Definition This project focuses on developing a database to manage and track personal
or organizational budgets. The system allows users to record income and expenses, categorize
financial activities, and analyze spending patterns using SQL queries.

Features

 Income and expense entry and categorization

 Monthly budget goals
 Financial summary dashboard
 Alerts for overspending
 Reporting and analysis features

Required Tables

Users

Field Name Data Type Description

user_id INT (PK) Unique ID for each user
name VARCHAR Name of the user
email VARCHAR Email ID
password VARCHAR Encrypted password

Categories
Field Name Data Type Description
category_id INT (PK) Unique ID for the category
name VARCHAR Category name (e.g., Food, Rent)
type VARCHAR 'Income' or 'Expense'

Transactions
Field Name Data Type Description
transaction_id INT (PK) Unique ID for each transaction
user_id INT (FK) User who made the transaction
category_id INT (FK) Linked category
amount DECIMAL Amount of the transaction
transaction_date DATE Date of the transaction
description VARCHAR Notes about the transaction

Budgets
Field Name Data Type Description
budget_id INT (PK) Unique ID for budget entry
user_id INT (FK) User linked to the budget
category_id INT (FK) Category being budgeted
limit_amount DECIMAL Monthly limit set
month VARCHAR Month of budget period
Relationships Summary

 A User can create multiple Transactions and Budgets

 Each Transaction is linked to a Category
 Each Budget is associated with a User and a Category

Sample Data

Users

user_id name Email password

1 Ravi ravi@example.com encryptedpwd

Categories
category_id name Type
1 Salary Income
2 Rent Expense
3 Food Expense

Transactions
transaction_id user_id category_id amount transaction_date description
101 1 1 30000 2025-04-01 Monthly Salary
102 1 2 8000 2025-04-02 April Rent
103 1 3 1500 2025-04-03 Groceries

Budgets
budget_id user_id category_id limit_amount month
1 1 2 8000 April
2 1 3 5000 April

SQL Components

Functions and Procedures

1. Procedure to calculate monthly spending per category

2. Function to check budget limit exceeded
3. Trigger to auto-update summary table upon transaction insert

Views

4. Monthly summary of income and expenses

5. Budget vs actual expenses
6. Category-wise transaction history
Nested Queries

7. Highest expense category in a month

8. Users with expenses exceeding income
9. Top 3 categories by expense

Join-Based Queries

10. Transaction details with category names

11. Budget utilization per category
12. User financial overview with income and expenses