1) To create a table Watches and write SQL queries for (i) to (v) based on the table.
Table: Watches
Watchid Watch_Name Price Type Qty_Store
W001 High Time 10000 Unisex 100
W002 Life Time 15000 Ladies 150
W003 Wave 20000 Gents 200
W004 High Fashion 7000 Unisex 250
W005 Golden Time 25000 Gents 100
i) Increase price by 20% if the watch name ends with “Time”.
ii) TO DELETE A RECORD WHOSE Qty_Store IS NOT BETWEEN 100 AND 200
iii) TO DISPLAY TOTAL QUANTITY IN STORE OF UNISEX TYPE WATCHES
iv) Display Watch Name, Type, Total Amount which is calculated as Price*Qty_Store.
v) Set a primary key for the above table.
2) To create a table CD and write SQL queries for (i) to (v) based on the table.
Table: CD
Code Title Duration Singer Category
101 Sufi Singer 50 mins Zakir Faiz 12
102 Eureka 45 mins Shyam Mukharjee 12
103 Nagmey 23 mins Sonvi Kumar 77
104 Dosti 35 mins Bobby 1
1. Increase the singer column width by 30.
2. Display the details of CD table whose category between 1 and 12.
(Both inclusive)
3. Delete a column Duration from the table CD.
4. Display the singer names whose name contains the letter ‘m’.
5. Delete the primary key from the table CD
3) To create the tables Customers and Purchases and write SQL queries for (i) to (v), based
on the two tables CUSTOMERS and PURCHASES:
Table: Customers Table: PURCHASES
CNO CNAME CITIES PNO CNO PRODUCT QUANTITY
C1 SANYAM DELHI P1 C1 PEN 11
C2 SHRUTI DELHI P2 C2 BOOK 20
C3 MEHER MUMBAI P3 C3 PENCIL 15
C4 SAKSHI CHENNAI P4 C4 ERASER 25
C5 RITESH INDORE P5 C5 PEN 30
C6 RAHUL DELHI P6 C6 BOOK 12
(i) Display details of all customers whose cities are neither Delhi nor Mumbai.
(ii) Display the CNAME and CITIES of all customers in alphabetical order of their
CNAME.
(iii) Display the number of customers along with their respective cities in each city.
(iv) Modify the purchase table by adding a CHECK constraint to ensure that the
quantity is greater than 10.
(v) Display CNO, CNAME, and PRODUCT as product name and Total quantity from
both the tables for each product whose total quantity greater than 50.
4) To create the tables the Clients and Order and write SQL queries for (i) to (v), based on
the two tables CLIENTS and ORDER
Table: CLIENTS Table: ORDER
CID CNAME CITY OrdNO CID ITEM QUANTITY
CL1 ARJUN DELHI O1 CL1 PEN 12
CL2 NEHA MUMBAI O2 CL2 BOOK 25
CL3 ROHAN CHENNAI O3 CL3 PENCIL NULL
CL4 PRIYA DELHI O4 CL4 ERASER 30
CL5 KABIR PUNE O5 CL5 PEN 18
CL6 ISHA MUMBAI O6 CL6 BOOK 10
i) Display total number of clients in each city.
ii) Display client names along with items they ordered.
iii) Display the details of the ORDER table for those items which doesn’t have quantity.
iv) Add an attribute DOJ with data type Date in Client Table.
v) Display CNAME and CITY in descending order of CNAME and ascending order of
City.
5) To create the tables Activity and Coach and write SQL queries for (i) to (v), based on the
two tables ACTIVITY and COACH.
Table: ACTIVITY
Acode ActivityName Stadium ParticipantsNum PrizeMoney ScheduleDate
1001 Relay 100 x 4 Star Annex 16 10000 23-Jan-04
1002 High Jump Star Annex 10 12000 12-Dec-03
1003 Shot Put Super Power 12 8000 14-Feb-04
1005 Long Jump Star Annex 12 9000 01-Jan-04
1008 Discuss Throw Super Power 10 15000 19-Mar-04
Table: COACH
Pcode Name Acode
1 Ahmad Hussain 1001
2 Ravinder 1008
3 Janila 1001
4 Naaz 1003
i) Display number of unique participants in activity table
ii) Display earliest and latest scheduled date in the activity table
iii) List the Acode, coacher name and corresponding activity for participant with ID
is 10 which has scheduled in the month of January.
iv) Display all activities along with average of ParticipantsNum done in each
stadium.
v) Modify the Prizemoney by increasing Rs 500 to the activities High Jump and
Long Jump.