Computer Science and Engineering Department
Motilal Nehru National Institute of Technology Allahabad
B.Tech V Semester
DBMS Lab (CSN15401)
Assignment-7
1. A database is being constructed for storing sales information system. A product can be described
with a unique product number, product name, selling price, manufacturer name. The product can sale
to a particular client and each client have its own unique client number, client name, client
addresses, city, pin code, state and total balance to be required to paid. Each client order is to buy
product from the salesman. In the order, it has unique sales order number, sales order date, client
number, salesman number (unique), billed whole payment by the party or not and its delivery date.
The salesman has the name, addresses, city, pin code, state, salary of the sales man, delivery date,
total quantity ordered, product rate.
Consider the sales info system consisting of following schemas: -
Product(p_id, p_name, mfg_name, p_rate, sell_price, p_description)
Client(c_id, c_name, address, city, pin, state, balance_due)
Salesman(s_id, s_name, address, city, pin, state, salary)
Sales_order_details(so_no, so_date, c_id, s_id, p_id, bill_paid, delivery_date, p_rate, quantity,
cancel_date)
Product Table:
p_id p_name mfg_name p_rate sell_price p_description
101 Laptop Dell 45000 50000 Dell Inspiron 15
102 Mobile Samsung 15000 18000 Galaxy M13
103 Printer HP 8000 9500 LaserJet Pro
104 Tablet Apple 30000 35000 iPad 9th Gen
Client Table:
c_id c_name address city pin state balance_due
201 Rahul Sharma Sector 12, Noida Noida 201301 UP 10000
202 Priya Singh Park Street Kolkata 700016 WB 5000
203 Amit Verma Civil Lines Prayagraj 211002 UP 7000
204 Riya Mehta MG Road Bengaluru 560001 Karnataka 2000
Salesman Table:
s_id s_name address city pin state salary
301 Suresh Kumar Sec 10 Noida 201301 UP 35000
302 Anil Gupta Salt Lake Kolkata 700091 WB 40000
303 Neha Jain Indiranagar Bengaluru 560038 Karnataka 30000
Sales_order_details Table:
so_no so_date c_id s_id p_id bill_p delivery_d p_rate quantity cancel_
aid ate date
401 2025-01-15 201 301 101 YES 2025-01-20 50000 2 NULL
402 2025-01-18 202 302 102 NO 2025-01-25 18000 3 NULL
Page 1 of 3
403 2025-02-01 203 301 103 YES 2025-02-05 9500 1 NULL
404 2025-02-10 204 303 104 YES 2025-02-15 35000 2 NULL
For the sales information system schema provided above and the database created, write the SQL
queries for the following:
(a) List all sales orders with client details.
(b) List sales orders with salesman details.
(c) Find all salesmen who handled orders in ‘Prayagraj’.
(d) Find total sales amount handled by each salesman.
(e) Count number of orders placed by each client.
(f) Find average product rate ordered by each client.
(g) Show clients with total purchases above ‘5000’.
(h) Show highest and lowest salary of salesmen who handled orders.
(i) Find total sales done per city.
(j) Show clients who still have pending balance.
(k) Show yearly total sales.
(l) Find total monthly sales along with product name and id.
(m) Show salesmen who handled orders but live in a different city than client.
(n) Find top 3 clients with maximum purchase amounts.
(o) Find cancelled orders with client details.
(p) Find average salary of salesmen handling more than 2 orders.
(q) Show all orders along with salesman salaries sorted by salary.
(r) Find number of orders handled by each salesman per year.
2. Consider a database that is being constructed for a Company’s office organization where it stores the
value of Employees, their Bonus and Job title. The employee relation contains the details of id, first
name, last name, salary, joining date of office and the department in which they are working. The
Bonus relation contains the details of employee reference id, bonus date and amount of bonus. Title
relates to bonus table through the reference id and this table stores the value of employer designation
and their job affected from which date.
Consider the Company's Office System consisting of following schemas: -
Employee (E_id, First_Name, Last_Name, Salary, Joining_Date, Department)
Bonus (E_Ref_Id, Bonus_Date, Bonus_Amount)
Job_Title (E_Ref_Id, E_Title, Affected_From)
Employee Table:
E_id First_Name Last_Name Salary Joining_Date Department
101 Amit Sharma 50000 2019-06-15 IT
102 Neha Verma 60000 2020-01-20 HR
103 Rajesh Gupta 75000 2018-09-10 Finance
104 Priya Mehta 50000 2021-03-05 Marketing
105 Anil Yadav 45000 2022-07-12 Sales
Page 2 of 3
Bonus Table:
E_Ref_Id Bonus_Date Bonus_Amount
101 2022-12-25 5000
102 2021-11-15 7000
103 2022-03-10 10000
104 2023-01-05 4000
105 2022-08-20 3000
Job_Title Table:
E_Ref_Id E_Title Affected_From
101 Software Engineer 2019-06-15
102 HR Executive 2020-01-20
103 Finance Manager 2018-09-10
104 Marketing Officer 2021-03-05
105 Sales Associate 2022-07-12
For the company’s office system schema provided above and the database created, write the SQL
queries for the following:
(a) List all employee details along with their bonus details.
(b) Find all employees who got a bonus above ‘5000’.
(c) Find total bonus paid by the company department-wise.
(d) Find employee with minimum bonus.
(e) Find employees whose bonus amount is not NULL.
(f) Find all employees who have bonus > 5000 and salary > average salary.
(g) Find total salary expense department-wise.
(h) List employees with bonus and calculate their total compensation (salary + bonus).
(i) Find employees who did not receive any bonus.
(j) Count number of employees with each designation.
(k) Find employees with bonuses more than the average bonus.
(l) Show employees who joined in the same year they received a bonus.
(m) Find the difference in years between joining date and bonus date.
(n) Find department with total bonuses greater than Rs 10,000.
(o) Find all employees who received bonus in the month of ‘December’.
(p) Find all employees whose bonus is more than 10% of their salary.
******
Page 3 of 3
