Page 12 - IPP-12-2025

34. A book keeper maintains a database to track its sales. The database includes a table named SALES with the
following attributes: [4]
SALES_ID: Shows the unique identifier for each sale.
CUSTOMER_NAME: Indicates the name of the customer who made the sale.

BOOK_TITLE: Specifies the name of the book.
SALE_DATE: Indicates the date when the sale was made.
AMOUNT: Lists the total amount of the sale.
Table: SALES

SALES_ID CUSTOMER_NAME BOOK_TITLE SALE_DATE AMOUNT
S1001 Amit Khanna DBMS Guide 2025-01-10 650
S1002 Raj Sinha Python Programming 2025-04-11 720
S1003 Neha Malhotra Web Development 2025-02-12 580
S1004 Ankit Sharma Data Science 2024-05-14 820
S1005 Sneha Rathi DBMS Guide 2025-03-15 650
(a) Write an SQL query to display the total sales amount done before 2025-05-10.
(b) Write an SQL query to find the book with the highest price.
(c) Write an SQL query to display the names of customers who purchased ‘DBMS Guide’.
(d) Write an SQL query to calculate the total revenue from all the books priced over 600.
Or
A university maintains a database of teaching staff. The database includes a table named TEACHING_STAFF
with the following attributes:
Table: TEACHING_STAFF

ID NAME SUBJECT EXPERIENCE SALARY
TS001 Dr. Meena Rao Data Science 12 92000
TS002 Mr. Arjun Yadav Python Programming 9 70000
TS003 Ms. Kirti Jain Data Science 6 56000
TS004 Mr. Nilesh Roy Python Programming 8 64000
TS005 Dr. Isha Mehta AI 5 60000
Write the outputs of the following SQL queries:
(a) SELECT NAME FROM TEACHING_STAFF WHERE ID <= TS004;
(b) SELECT UPPER(SUBJECT) FROM TEACHING_STAFF WHERE SALARY > 64000;
(c) SELECT SUBJECT, AVG(SALARY) FROM TEACHING_STAFF GROUP BY SUBJECT;
(d) SELECT LENGTH(NAME) FROM TEACHING_STAFF WHERE SUBJECT = 'AI';
Ans. (a) SELECT SUM(AMOUNT) FROM SALES WHERE SALE_DATE < '2025-05-10';
(b) SELECT BOOK_TITLE FROM SALES WHERE AMOUNT = (SELECT MAX(AMOUNT) FROM
SALES);
(c) SELECT CUSTOMER_NAME FROM SALES WHERE BOOK_TITLE = 'DBMS Guide';
(d) SELECT SUM(AMOUNT) FROM SALES WHERE AMOUNT > 600;
Or
(a) NAME
Dr. Meena Rao
Mr. Arjun Yadav
Ms. Kirti Jain
Mr. Nilesh Roy

Model Test Paper M.9