Practice Set 1 - TechShop, an Electronic Gadgets Shop
You are working as a database administrator for a fictional company named "TechShop," which sells electronic gadgets. TechShop maintains data related to their products, customers, and orders. Your task is to design and implement a database for TechShop based on the following requirements:
Database Tables:
1. Customers:
CustomerID (Primary Key)
FirstName
LastName
Email
Phone
Address
2. Products:
ProductID (Primary Key)
ProductName
Description
Price
3. Orders:
OrderID (Primary Key)
CustomerID (Foreign Key referencing Customers)
OrderDate
TotalAmount
4. OrderDetails:
OrderDetailID (Primary Key)
OrderID (Foreign Key referencing Orders)
ProductID (Foreign Key referencing Products)
Quantity
5. Inventory
InventoryID (Primary Key)
ProductID (Foreign Key referencing Products)
QuantityInStock
LastStockUpdate
Task:1. Database Design
Create the database named "TechShop"
Define the schema for the Customers, Products, Orders, OrderDetails and Inventory tables based on the provided schema.
Create an ERD (Entity Relationship Diagram) for the database.
Create appropriate Primary Key and Foreign Key constraints for referential integrity.
Insert at least 10 sample records into each of the following tables.
a. Customers
b. Products
c. Orders
d. OrderDetails
e. Inventory
Tasks 2: Select, Where, Between, AND, LIKE
Write an SQL query to retrieve the names and emails of all customers.
Write an SQL query to list all orders with their order dates and corresponding customer names.
Write an SQL query to insert a new customer record into the "Customers" table. Include customer information such as name, email, and address.
Write an SQL query to update the prices of all electronic gadgets in the "Products" table by increasing them by 10%.
Write an SQL query to delete a specific order and its associated order details from the "Orders" and "OrderDetails" tables. Allow users to input the order ID as a parameter.
Write an SQL query to insert a new order into the "Orders" table. Include the customer ID, order date, and any other necessary information.
Write an SQL query to update the contact information (e.g., email and address) of a specific customer in the "Customers" table. Allow users to input the customer ID and new contact information.
Write an SQL query to recalculate and update the total cost of each order in the "Orders" table based on the prices and quantities in the "OrderDetails" table.
Write an SQL query to delete all orders and their associated order details for a specific customer from the "Orders" and "OrderDetails" tables. Allow users to input the customer ID as a parameter.
Write an SQL query to insert a new electronic gadget product into the "Products" table, including product name, category, price, and any other relevant details.
Write an SQL query to update the status of a specific order in the "Orders" table (e.g., from "Pending" to "Shipped"). Allow users to input the order ID and the new status.
Write an SQL query to calculate and update the number of orders placed by each customer in the "Customers" table based on the data in the "Orders" table.
Task 3. Aggregate functions, Having, Order By, GroupBy and Joins
Write an SQL query to retrieve a list of all orders along with customer information (e.g., customer name) for each order.
Write an SQL query to find the total revenue generated by each electronic gadget product. Include the product name and the total revenue.
Write an SQL query to list all customers who have made at least one purchase. Include their names and contact information.
Write an SQL query to find the most popular electronic gadget, which is the one with the highest total quantity ordered. Include the product name and the total quantity ordered.
Write an SQL query to retrieve a list of electronic gadgets along with their corresponding categories.
Write an SQL query to calculate the average order value for each customer. Include the customer's name and their average order value.
Write an SQL query to find the order with the highest total revenue. Include the order ID, customer information, and the total revenue.
Write an SQL query to list electronic gadgets and the number of times each product has been ordered.
Write an SQL query to find customers who have purchased a specific electronic gadget product. Allow users to input the product name as a parameter.
Write an SQL query to calculate the total revenue generated by all orders placed within a specific time period. Allow users to input the start and end dates as parameters.
Task 4. Subquery and its type
Write an SQL query to find out which customers have not placed any orders.
Write an SQL query to find the total number of products available for sale.
Write an SQL query to calculate the total revenue generated by TechShop.
Write an SQL query to calculate the average quantity ordered for products in a specific category. Allow users to input the category name as a parameter.
Write an SQL query to calculate the total revenue generated by a specific customer. Allow users to input the customer ID as a parameter.
Write an SQL query to find the customers who have placed the most orders. List their names and the number of orders they've placed.
Write an SQL query to find the most popular product category, which is the one with the highest total quantity ordered across all orders.
Write an SQL query to find the customer who has spent the most money (highest total revenue) on electronic gadgets. List their name and total spending.
Write an SQL query to calculate the average order value (total revenue divided by the number of orders) for all customers.
Write an SQL query to find the total number of orders placed by each customer and list their names along with the order count.
Practice Set 2 - Student Information System (SIS)
In this task, you will work with a simplified Student Information System (SIS) database. The SIS database contains information about students, courses, and enrollments. Your task is to perform various SQL operations on this database to retrieve and manipulate data.
Database Tables:
The SIS database consists of the following tables:
1. Students
student_id (Primary Key)
first_name
last_name
date_of_birth
email
phone_number
2. Courses
course_id (Primary Key)
course_name
credits
teacher_id (Foreign Key)
3. Enrollments
enrollment_id (Primary Key)
student_id (Foreign Key)
course_id (Foreign Key)
enrollment_date
4. Teacher
teacher_id (Primary Key)
first_name
last_name
email
5. Payments
payment_id (Primary Key)
student_id (Foreign Key)
amount
payment_date
Task 1. Database Design
Create the database named "SISDB"
Define the schema for the Students, Courses, Enrollments, Teacher, and Payments tables based on the provided schema. Write SQL scripts to create the mentioned tables with appropriate data types, constraints, and relationships.
a. Students
b. Courses
c. Enrollments
d. Teacher
e. Payments
3. Create an ERD (Entity Relationship Diagram) for the database.
4. Create appropriate Primary Key and Foreign Key constraints for referential integrity. 5. Insert at least 10 sample records into each of the following tables.
i. Students
ii. Courses
iii. Enrollments
iv. Teacher
v. Payments
Tasks 2: Select, Where, Between, AND, LIKE
Write an SQL query to insert a new student into the "Students" table with the following details:
a. First Name: John
b. Last Name: Doe
           c. Date of Birth: 1995-08-15
           d. Email: john.doe@example.comÂ
           e. Phone Number: 1234567890
Write an SQL query to enroll a student in a course. Choose an existing student and course and insert a record into the "Enrollments" table with the enrollment date.
Update the email address of a specific teacher in the "Teacher" table. Choose any teacher and modify their email address.
Write an SQL query to delete a specific enrollment record from the "Enrollments" table. Select an enrollment record based on the student and course.
Update the "Courses" table to assign a specific teacher to a course. Choose any course and teacher from the respective tables.
Delete a specific student from the "Students" table and remove all their enrollment records from the "Enrollments" table. Be sure to maintain referential integrity.
Update the payment amount for a specific payment record in the "Payments" table. Choose any payment record and modify the payment amount.
Task 3. Aggregate functions, Having, Order By, GroupBy and Joins
Write an SQL query to calculate the total payments made by a specific student. You will need to join the "Payments" table with the "Students" table based on the student's ID.
Write an SQL query to retrieve a list of courses along with the count of students enrolled in each course. Use a JOIN operation between the "Courses" table and the "Enrollments" table.
Write an SQL query to find the names of students who have not enrolled in any course. Use a LEFT JOIN between the "Students" table and the "Enrollments" table to identify students without enrollments.
Write an SQL query to retrieve the first name, last name of students, and the names of the courses they are enrolled in. Use JOIN operations between the "Students" table and the "Enrollments" and "Courses" tables.
Create a query to list the names of teachers and the courses they are assigned to. Join the "Teacher" table with the "Courses" table.
Retrieve a list of students and their enrollment dates for a specific course. You'll need to join the "Students" table with the "Enrollments" and "Courses" tables.
Find the names of students who have not made any payments. Use a LEFT JOIN between the "Students" table and the "Payments" table and filter for students with NULL payment records.
Write a query to identify courses that have no enrollments. You'll need to use a LEFT JOIN between the "Courses" table and the "Enrollments" table and filter for courses with NULL enrollment records.
Identify students who are enrolled in more than one course. Use a self-join on the "Enrollments" table to find students with multiple enrollment records.
Find teachers who are not assigned to any courses. Use a LEFT JOIN between the "Teacher" table and the "Courses" table and filter for teachers with NULL course assignments.
Task 4. Subquery and its type
Write an SQL query to calculate the average number of students enrolled in each course. Use aggregate functions and subqueries to achieve this.
Identify the student(s) who made the highest payment. Use a subquery to find the maximum payment amount and then retrieve the student(s) associated with that amount.
Retrieve a list of courses with the highest number of enrollments. Use subqueries to find the course(s) with the maximum enrollment count.
Calculate the total payments made to courses taught by each teacher. Use subqueries to sum payments for each teacher's courses.
Identify students who are enrolled in all available courses. Use subqueries to compare a student's enrollments with the total number of courses.
Retrieve the names of teachers who have not been assigned to any courses. Use subqueries to find teachers with no course assignments.
Calculate the average age of all students. Use subqueries to calculate the age of each student based on their date of birth.
Identify courses with no enrollments. Use subqueries to find courses without enrollment records.
Calculate the total payments made by each student for each course they are enrolled in. Use subqueries and aggregate functions to sum payments.
Identify students who have made more than one payment. Use subqueries and aggregate functions to count payments per student and filter for those with counts greater than one.
Write an SQL query to calculate the total payments made by each student. Join the "Students" table with the "Payments" table and use GROUP BY to calculate the sum of payments for each student.
Retrieve a list of course names along with the count of students enrolled in each course. Use JOIN operations between the "Courses" table and the "Enrollments" table and GROUP BY to count enrollments.
Calculate the average payment amount made by students. Use JOIN operations between the "Students" table and the "Payments" table and GROUP BY to calculate the average.
Practice Set 3 - Banking System
You are tasked with creating an advanced banking system that includes various types of accounts, such as savings and current accounts. The system should support account creation, deposits, withdrawals, and interest calculations.
Database Tables
1. Customers:
customer_id (Primary Key)
first_name
last_name
DOB (Date of Birth)
email
phone_number
address
2. Accounts:
account_id (Primary Key)
customer_id (Foreign Key)
account_type (e.g., savings, current, zero_balance)
balance
3. Transactions:
transaction_id (Primary Key)
account_id (Foreign Key)
transaction_type (e.g., deposit, withdrawal, transfer)
amount
transaction_date
Tasks 1: Database Design
Create the database named "HMBank"
Define the schema for the Customers, Accounts, and Transactions tables based on the provided schema.
Create an ERD (Entity Relationship Diagram) for the database.
Create appropriate Primary Key and Foreign Key constraints for referential integrity.
Write SQL scripts to create the mentioned tables with appropriate data types, constraints, and relationships.
Customers
Accounts
Transactions
Tasks 2: Select, Where, Between, AND, LIKE
1. Insert at least 10 sample records into each of the following tables.
• Customers
• Accounts
• Transactions
2. Write SQL queries for the following tasks:
Write a SQL query to retrieve the name, account type and email of all customers.
Write a SQL query to list all transaction corresponding customer.
Write a SQL query to increase the balance of a specific account by a certain amount.
Write a SQL query to Combine first and last names of customers as a full_name.
Write a SQL query to remove accounts with a balance of zero where the account type is savings.
Write a SQL query to Find customers living in a specific city.
Write a SQL query to Get the account balance for a specific account.
Write a SQL query to List all current accounts with a balance greater than $1,000.
Write a SQL query to Retrieve all transactions for a specific account
Write a SQL query to Calculate the interest accrued on savings accounts based on a given interest rate.
Write a SQL query to Identify accounts where the balance is less than a specified overdraft limit.
Write a SQL query to Find customers not living in a specific city.
Tasks 3: Aggregate functions, Having, Order By, GroupBy and Joins
Write a SQL query to Find the average account balance for all customers.
Write a SQL query to Retrieve the top 10 highest account balances.
Write a SQL query to Calculate Total Deposits for All Customers in specific date.
Write a SQL query to Find the Oldest and Newest Customers.
Write a SQL query to Retrieve transaction details along with the account type.
Write a SQL query to Get a list of customers along with their account details.
Write a SQL query to Retrieve transaction details along with customer information for a specific account.
Write a SQL query to Identify customers who have more than one account.
Write a SQL query to Calculate the difference in transaction amounts between deposits and withdrawals.
Write a SQL query to Calculate the average daily balance for each account over a specified period.
Calculate the total balance for each account type.
Identify accounts with the highest number of transactions order by descending order.
List customers with high aggregate account balances, along with their account types.
Identify and list duplicate transactions based on transaction amount, date, and account.
Tasks 4: Subquery and its type
Retrieve the customer(s) with the highest account balance.
Calculate the average account balance for customers who have more than one account.
Retrieve accounts with transactions whose amounts exceed the average transaction amount.
Identify customers who have no recorded transactions.
Calculate the total balance of accounts with no recorded transactions.
Retrieve transactions for accounts with the lowest balance.
Identify customers who have accounts of multiple types.
Calculate the percentage of each account type out of the total number of accounts.
Retrieve all transactions for a customer with a given customer_id.
Calculate the total balance for each account type, including a subquery within the SELECT clause.
Practice Set 4 - Courier Management System
Task1 Database Design
Design a SQL schema for a Courier Management System with tables for Customers, Couriers, Orders, and Parcels. Define the relationships between these tables using appropriate foreign keys.
Requirements:
Define the Database Schema
Create SQL tables for entities such as User, Courier, Employee, Location,Payment
Define relationships between these tables (one-to-many, many-to-many, etc.).
Populate Sample Data
Insert sample data into the tables to simulate real-world scenarios.
User Table:
User
(UserID INT PRIMARY KEY,
Name VARCHAR(255),
Email VARCHAR(255) UNIQUE,
Password VARCHAR(255),
ContactNumber VARCHAR(20),
Address TEXT );
Courier
(CourierID INT PRIMARY KEY,
SenderName VARCHAR(255),
SenderAddress TEXT,
ReceiverName VARCHAR(255),
ReceiverAddress TEXT,
Weight DECIMAL(5, 2),
Status VARCHAR(50),
TrackingNumber VARCHAR(20) UNIQUE,
DeliveryDate DATE);
CourierServices
(ServiceID INT PRIMARY KEY,
ServiceName VARCHAR(100),
Cost DECIMAL(8, 2));
Employee Table:
(EmployeeID INT PRIMARY KEY,
Name VARCHAR(255),
Email VARCHAR(255) UNIQUE,
ContactNumber VARCHAR(20),
Role VARCHAR(50),
Salary DECIMAL(10, 2));
Location Table:
(LocationID INT PRIMARY KEY,
LocationName VARCHAR(100),
Address TEXT);
Payment Table:
(PaymentID INT PRIMARY KEY,
CourierID INT,
LocationId INT,
Amount DECIMAL(10, 2),
PaymentDate DATE,
FOREIGN KEY (CourierID) REFERENCES Couriers(CourierID),
FOREIGN KEY (LocationID) REFERENCES Location(LocationID));
Task 2: Select,Where
Solve the following queries in the Schema that you have created above
List all customers:
List all orders for a specific customer:
List all couriers:
List all packages for a specific order:
List all deliveries for a specific courier:
List all undelivered packages:
List all packages that are scheduled for delivery today:
List all packages with a specific status:
Calculate the total number of packages for each courier.
Find the average delivery time for each courier
List all packages with a specific weight range:
Retrieve employees whose names contain 'John'
Retrieve all courier records with payments greater than $50.
Task 3: GroupBy, Aggregate Functions, Having, Order By, where
Find the total number of couriers handled by each employee.
Calculate the total revenue generated by each location
Find the total number of couriers delivered to each location.
Find the courier with the highest average delivery time:
Find Locations with Total Payments Less Than a Certain Amount
Calculate Total Payments per Location
Retrieve couriers who have received payments totaling more than $1000 in a specific location (LocationID = X):
Retrieve couriers who have received payments totaling more than $1000 after a certain date (PaymentDate > 'YYYY-MM-DD'):
Retrieve locations where the total amount received is more than $5000 before a certain date (PaymentDate > 'YYYY-MM-DD')
Task 4: Inner Join,Full Outer Join, Cross Join, Left Outer Join,Right Outer Join
Retrieve Payments with Courier Information
Retrieve Payments with Location Information
Retrieve Payments with Courier and Location Information
List all payments with courier details
Total payments received for each courier
List payments made on a specific date
Get Courier Information for Each Payment
Get Payment Details with Location
Calculating Total Payments for Each Courier
List Payments Within a Date Range
Retrieve a list of all users and their corresponding courier records, including cases where there are no matches on either side
Retrieve a list of all couriers and their corresponding services, including cases where there are no matches on either side
Retrieve a list of all employees and their corresponding payments, including cases where there are no matches on either side
List all users and all courier services, showing all possible combinations.
List all employees and all locations, showing all possible combinations:
Retrieve a list of couriers and their corresponding sender information (if available) 39. Retrieve a list of couriers and their corresponding receiver information (if available): 40. Retrieve a list of couriers along with the courier service details (if available):
41. Retrieve a list of employees and the number of couriers assigned to each employee: 42. Retrieve a list of locations and the total payment amount received at each location: 43. Retrieve all couriers sent by the same sender (based on SenderName).
List all employees who share the same role.
Retrieve all payments made for couriers sent from the same location.
Retrieve all couriers sent from the same location (based on SenderAddress).
List employees and the number of couriers they have delivered:
Find couriers that were paid an amount greater than the cost of their respective courier services
Scope: Inner Queries, Non Equi Joins, Equi joins,Exist,Any,All
Find couriers that have a weight greater than the average weight of all couriers
Find the names of all employees who have a salary greater than the average salary:
Find the total cost of all courier services where the cost is less than the maximum cost
Find all couriers that have been paid for
Find the locations where the maximum payment amount was made
Find all couriers whose weight is greater than the weight of all couriers sent by a specific sender (e.g., 'SenderName'):
Practice Set 5 - Ticket Booking System
You are tasked with creating a ticket booking system for a Event. The system should support booking tickets for different types of events, such as movies, concerts, and plays. Each event has its own pricing strategy, and the system should also track available seats and customer bookings.
Database Tables
1. Venu Table
venue_id (Primary Key)
venue_name,
address
2. Event Table
event_id (Primary Key)
event_name,
event_date DATE,
event_time TIME,
venue_id (Foreign Key),
total_seats,
available_seats,
ticket_price DECIMAL,
event_type ('Movie', 'Sports', 'Concert')
booking_id (Foreign Key)
3. Customer Table
customer_id (Primary key)
customer_name,
email,
phone_number,
booking_id (Foreign Key)
4. Booking Table
booking_id (Primary Key),
customer_id (Foreign Key),
event_id (Foreign Key),
num_tickets,
total_cost,
booking_date,
Tasks 1: Database Design:
1. Create the database named "TicketBookingSystem"
2. Write SQL scripts to create the mentioned tables with appropriate data types, constraints, and relationships.
Venu
Event
Customers
Booking
3. Create an ERD (Entity Relationship Diagram) for the database.
4. Create appropriate Primary Key and Foreign Key constraints for referential integrity.
Tasks 2: Select, Where, Between, AND, LIKE
Write a SQL query to insert at least 10 sample records into each table.
Write a SQL query to list all Events.
Write a SQL query to select events with available tickets.
Write a SQL query to select events name partial match with ‘cup’.
Write a SQL query to select events with ticket price range is between 1000 to 2500.
Write a SQL query to retrieve events with dates falling within a specific range.
Write a SQL query to retrieve events with available tickets that also have "Concert" in their name.
Write a SQL query to retrieve users in batches of 5, starting from the 6th user.
Write a SQL query to retrieve bookings details contains booked no of ticket more than 4.
Write a SQL query to retrieve customer information whose phone number end with ‘000’
Write a SQL query to retrieve the events in order whose seat capacity more than 15000.
Write a SQL query to select events name not start with ‘x’, ‘y’, ‘z’
Tasks 3: Aggregate functions, Having, Order By, GroupBy and Joins:
Write a SQL query to List Events and Their Average Ticket Prices.
Write a SQL query to Calculate the Total Revenue Generated by Events.
Write a SQL query to find the event with the highest ticket sales.
Write a SQL query to Calculate the Total Number of Tickets Sold for Each Event.
Write a SQL query to Find Events with No Ticket Sales.
Write a SQL query to Find the User Who Has Booked the Most Tickets.
Write a SQL query to List Events and the total number of tickets sold for each month.
Write a SQL query to calculate the average Ticket Price for Events in Each Venue.
Write a SQL query to calculate the total Number of Tickets Sold for Each Event Type.
Write a SQL query to calculate the total Revenue Generated by Events in Each Year.
Write a SQL query to list users who have booked tickets for multiple events.
Write a SQL query to calculate the Total Revenue Generated by Events for Each User.
Write a SQL query to calculate the Average Ticket Price for Events in Each Category and Venue.
Write a SQL query to list Users and the Total Number of Tickets They've Purchased in the Last 30 Days.
Tasks 4: Subquery and its types
Calculate the Average Ticket Price for Events in Each Venue Using a Subquery.
Find Events with More Than 50% of Tickets Sold using subquery.
Calculate the Total Number of Tickets Sold for Each Event.
Find Users Who Have Not Booked Any Tickets Using a NOT EXISTS Subquery.
List Events with No Ticket Sales Using a NOT IN Subquery.
Calculate the Total Number of Tickets Sold for Each Event Type Using a Subquery in the FROM Clause.
Find Events with Ticket Prices Higher Than the Average Ticket Price Using a Subquery in the WHERE Clause.
Calculate the Total Revenue Generated by Events for Each User Using a Correlated Subquery.
List Users Who Have Booked Tickets for Events in a Given Venue Using a Subquery in the WHERE Clause.
Calculate the Total Number of Tickets Sold for Each Event Category Using a Subquery with GROUP BY.
Find Users Who Have Booked Tickets for Events in each Month Using a Subquery with DATE_FORMAT.
Calculate the Average Ticket Price for Events in Each Venue Using a Subquery