WORK SHEET NETWORK I

Class 12 - Computer Science

1. Consider the following table STOCK. Write SQL commands for the following statements. [4]
Table: STOCK

ItemNo Item Dcode Qty UnitPrice StockDate

5005 Ball Pen 0.5 102 100 16 31-Mar-10

5003 Ball Pen 0.25 102 150 20 01-Jan-10

5002 Gel Pen Premium 101 125 14 14-Feb-10

5006 Gel Pen Classic 101 200 22 01-Jan-09

5001 Eraser Small 102 210 5 19-Mar-09

5004 Eraser Big 102 60 10 12-Dec-09

5009 Sharpener Classic 103 160 8 23-Jan-09

i. To display details of all Items in the Stock table in ascending order of StockDate.
ii. To display ItemNo and Item name of those items from Stock table whose UnitPrice is more than Rupees 10.
iii. To display the details of those items whose dealer code (Dcode) is 102 or Quantity in Stock (Qty) is more
than 100 from the table Stock.
iv. To display Maximum UnitPrice of items for each dealer individually as per Dcode from the table Stock.
2. Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of this question: [4]
Table: STORE

ItemNo Item Scode Qty Rate LastBuy

2005 Sharpener Classic 23 60 8 31-Jun-09

2003 Ball Pen 0.25 22 50 25 01-Feb-10

2002 Gel Pen Premium 21 150 12 24-Feb-10

2006 Gel Pen Classic 21 250 20 11-Mar-09

2001 Eraser Small 22 220 6 19-Jan-09

2004 Eraser Big 22 110 8 02-Dec-09

2009 Ball Pen 0.5 21 180 18 03-Nov-09

Table: SUPPLIERS

Scode Sname

1 / 40
 21 Premium Stationers

23 Soft Plastics

22 Tetra Supply

a. Write SQL commands for the following statements:

i. To display details of all the items in the STORE table in ascending order of LastBuy.
ii. To display ItemNo and Item name of those items from STORE table whose Rate is more than 15 Rupees.
iii. To display the details of those items whose supplier code (Scode) is 22 or Quantity in Store (Qty) is more
than 110 from the table Store.
iv. To display minimum Rate of items for each supplier individually as per Scode from the table STORE.
b. Give the output of the following SQL queries:
i. SELECT COUNT(DISTINCT Scode) FROM STORE;
ii. SELECT Rate* Qty FROM STORE WHERE ItemNo = 2004;
iii. SELECT Item, Sname FROM STORE S, Suppliers P WHERE S.Scode= P.Scode AND ItemNo = 2006;
iv. SELECT MAX(LastBuy) FROM STORE;
3. Consider the following tables STORE and answer the questions: [4]
TABLE: STORE

Item No Item Scode Qty Rate Last Buy

2005 Sharpener Classic 23 60 8 31-JUN-09

2003 Balls 22 50 25 01-FEB-10

2002 Gel Pen Premium 21 150 12 24-FEB-10

2006 Gel Pen Classic 21 250 20 11-MAR-09

2001 Eraser Small 22 220 6 19-JAN-09

2004 Eraser Big 22 110 8 02-DEC-09

2009 Ball Pen 0.5 21 180 18 03-NQV-09

Write SQL commands for the following statements:

i. To display details of all the items in the STORE table in ascending order of LastBuy.
ii. To display ItemNo and Item name of those items from STORE table, whose Rate is more than ₹15.
iii. To display the details of those items whose Supplier code (Scode) is 22 or Quantity in Store (Qty) is more
than 110 from the table STORE.
iv. To display minimum rate of items for each Supplier individually as per Scode from the table STORE.
v. To display the item with its quantity which include pen in their name.
4. Explain the Cartesian product of two relations. [4]
5. Give output for following SQL queries as per given table(s): [4]
Table: GRADUATE

S.NO. NAME STIPEND SUBJECT AVERAGE DIV

1. KARAN 400 PHYSICS 68 1

2. DIVAKAR 450 COMPUTER SC 68 1

2 / 40
 3. DIVYA 300 CHEMISTRY 62 2

4. ARUN 350 PHYSICS 63 1

5. SABINA 500 MATHEMATICS 70 1

6. JOHN 400 CHEMISTRY 55 2

7. ROBERT 250 PHYSICS 64 1

8. RUBINA 450 MATHEMATICS 68 1

9. VIKAS 500 COMPUTER SC 62 1

10. MOHAN 300 MATHEMATICS 57 2

i. SELECT MIN (AVERAGE) FROM GRADUATE

WHERE SUBJECT = "PHYSICS";
ii. SELECT SUM(STIPEND) FROM GRADUATE WHERE DIV = 2;
iii. SELECT AVG(STIPEND) FROM GRADUATE WHERE AVERAGE >= 65;
iv. SELECT COUNT (distinct SUBJECT) FROM GRADUATE;
6. Consider the following table STORE. Write SQL commands for the following statements. [4]
Table: STORE

ItemNo Item Scode Qty Rate LastBuy

2005 Sharpener Classic 23 60 8 31-Jun-09

2003 Ball Pen 0.25 22 50 25 01-Feb-10

2002 Gel Pen Premium 21 150 12 24-Feb-10

2006 Gel Pen Classic 21 250 20 11-Mar-09

2001 Eraser Small 22 220 6 19-Jan-09

2004 Eraser Big 22 110 8 02-Dec-09

2009 Ball Pen 0.5 21 180 18 03-Nov-09

i. To display details of all the items in the Store table in ascending order of LastBuy.
ii. To display ItemNo and Item name of those items from Store table whose Rate is more than 15 Rupees.
iii. To display the details of those items whose Suppliers code (Scode) is 22 or Quantity in Store (Qty) is more
than 110 from the table Store.
iv. To display the Minimum Rate of items for each Supplier individually as per Scode from the table store.
7. Write SQL queries for (a) to (d) based on the tables PASSENGER and FLIGHT given below: [4]
Table: PASSENGER

PNO NAME GENDER FNO

1001 Suresh MALE F101

1002 Anita FEMALE F104

1003 Harjas MALE F102

1004 Nita FEMALE F103

3 / 40
 Table: FLIGHT

FNO START END F_DATE FARE

F101 MUMBAI CHENNAI 2021-12-25 4500

F102 MUMBAI BENGALURU 2021-11-20 4000

F103 DELHI CHENNAI 2021-12-10 5500

F104 KOLKATA MUMBAI 2021-12-20 4500

F105 DELHI BENGALURU 2021-01-15 5000

a. Write a query to change the fare to 6000 of the flight whose FNO is F104.
b. Write a query to display the total number of MALE and FEMALE PASSENGERS.
c. Write a query to display the NAME, corresponding FARE and F_DATE of all PASSENGERS who have a
flight to START from DELHI.
d. Write a query to delete the records of flights which end at Mumbai.
8. Write SQL queries for (a) to (d) based on the tables CUSTOMER and TRANSACT given below: [4]
Table : CUSTOMER

CNO NAME GENDER ADDRESS PHONE

1001 Suresh MALE A-123, West Street 9310010010

1002 Anita FEMALE C-24, Court Lane 9121211212

1003 Harjas MALE T-1, Woods Avenue 9820021001

Table : TRANSACT

TNO CNO AMOUNT TTYPE TDATE

T1 1002 2000 DEBIT 2021-09-25

T2 1003 1500 CREDIT 2022-01-28

T3 1002 3500 CREDIT 2021-12-31

T4 1001 1000 DEBIT 2022-01-10

a. Write the SQL statements to delete the records from table TRANSACT whose amount is less than 1000.
b. Write a query to display the total AMOUNT of all DEBITs and all CREDITs.
c. Write a query to display the NAME and corresponding AMOUNT of all CUSTOMERs who made a
transaction type (TTYPE) of CREDIT.
d. Write the SQL statement to change the Phone number of customer whose CNO is 1002 to 9988117700 in the
table CUSTOMER.
9. Consider the following tables WORKER and PAYLEVEL and answer the following parts of this question : [4]
Table : WORKER

ECODE NAME DESIG PLEVEL DOJ DOB

11 Radhe Shyam Supervisor P001 13-Sep-2004 23-Aug-1981

12 Chander Nath Operator P003 22-Feb-2010 12-Jul-1987

13 Fizza Operator P003 14-Jun-2009 14-Oct-1983

4 / 40
 15 Ameen Ahmed Mechanic P002 21-Aug-2006 13-Mar-1984

18 Sanya Clerk P002 19-Dec-2005 09-Jun-1983

Table : PAYLEVEL

PLEVEL PAY ALLOWANCE

P001 26000 12000

P002 22000 10000

P003 12000 6000

Give the output of the following SQL queries:

i. SELECT COUNT (PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;
ii. SELECT MAX(DOB), MIN(DOJ) FROM WORKER;
iii. SELECT Name, Pay FROM WORKER W, PAYLEVEL P
WHERE W.PLEVEL = P.PLEVEL AND W.ECODE < 13;
iv. SELECT PLEVEL, PAY + ALLOWANCE FROM PAYLEVEL WHERE PLEVEL = 'P003';
10. Consider the following table named "SOFTDRINK". Write commands of SQL for (i) to (iv). [4]
Table : SOFTDRINK

DRINKCODE DNAME PRICE CALORIES

101 Lime and Lemon 20.00 120

102 Apple Drink 18.00 120

103 Nature Nectar 15.00 115

104 Green Mango 15.00 140

105 Aam Panna 20.00 135

106 Mango Juice Bahaar 12.00 150

i. To display names and drink codes of those drinks that have more than 120 calories.
ii. To display drink codes, names and calories of all drinks, in descending order of calories.
iii. To display names and price of drinks that have price in the range 12 to 18 (both 12 and 18 included).
iv. Increase the price of all drinks in the given table by 10%.
11. Consider the following tables GAMES and PLAYER and answer the following parts of this question : [4]
Table: GAMES

GCode GameName Type Number PrizeMoney ScheduleDate

101 Carom Board Indoor 2 5000 23-Jan-2004

102 Badminton Outdoor 2 12000 12-Dec-2003

103 Table Tennis Indoor 4 8000 14-Feb-2004

105 Chess Indoor 2 9000 01-Jan-2004

108 Lawn Tennis Outdoor 4 25000 19-Mar-2004

Table : PLAYER

5 / 40
 PCode Name GCode

1 Nabi Ahmad 101

2 Ravi Sahai 108

3 Jatin 101

4 Nazneen 103

Give the output of the following SQL queries :

i. SELECT COUNT (DISTINCT Number) FROM GAMES ;
ii. SELECT MAX(ScheduleDate), MIN(ScheduleDate) FROM GAMES ;
iii. SELECT Name, GameName FROM GAMES G, PLAYER P
WHERE G.GCode = P.GCode AND G.PrizeMoney > 10000 ;
iv. SELECT DISTINCT GCode FROM PLAYER;
12. Write SQL commands for (i) to (v) on the basis of table COLLEGE [4]
TABLE: COLLEGE

No Name Age Department Date of Join Basic Sex

1 Shalaz 45 Biology 13/02/88 10500 M

2 Sameera 54 Biology 10/01/90 9500 F

3 Yagyen 43 Physics 27/02/98 8500 M

4 Pratyush 34 Chemistry 11/01/93 7500 M

5 Aren 51 Mathematics 22/01/91 8500 M

6 Reeta 27 Chemistry 14/02/94 9000 F

7 Urvashi 29 Biology 10/02/93 8500 F

8 Teena 35 Mathematics 02/02/89 10500 F

9 Viren 49 Mathematics 03/01/88 9000 M

10 Prakash 22 Physics 17/02/92 8000 M

i. Write a query to change the Basic salary to 10500 of all those teachers from COLLEGE, who joined the
COLLEGE after 01/02/89 and are above the age of 50.
ii. Write a query to display Name, Age and Basic of all those from COLLEGE, who belong to Physics and
Chemistry department only.
iii. Which command will be used to delete a row from table COLLEGE, in which NAME is VIREN?
iv. Insert the following data in the given table COLLEGE
11, ‘Saurav’, 50, ‘Chemistry’, ‘18/05/93’, 12000, ‘M’
v. Identify the attribute best suitable to be declared as a primary key.
13. Give output for following SQL queries as per given table(s) : [4]
Table: SENDER

SenderlD SenderName senderAddress SenderCity

ND01 | R Jain 2, ABC Appts New Delhi

6 / 40
 MU02 H Sinha 12, Newtown Mumbai

MU15 S Jha 27/A, Park Street Mumbai

ND50 T Prasad 122-K, SDA New Delhi

Table : RECIPIENT

RecID SenderlD RecName RecAddress RecCity

KO05 ND01 R Bajpayee 5, Central Avenue Kolkata

ND08 MU02 S Mahajan 116, A Vihar New Delhi

MU19 ND01 H Singh 2A, Andheri East Mumbai

MU32 MU15 P K Swamy B5, C S Terminus Mumbai

13, B1 D, Mayur
ND48 ND50 S Tripathi New Delhi
Vihar

i. SELECT DISTINCT SenderCity FROM Sender;

ii. SELECT A.SenderName, B.RecName
FROM Sender A, Recipient B
WHERE A.SenderlD = B.SenderlD AND B.RecCity = 'Mumbai';
iii. SELECT RecName, RecAddress
FROM Recipient
WHERE RecCity NOT IN ('Mumbai', 'Kolkata');
iv. SELECT RecID, RecName
FROM Recipient
WHERE SenderlD = 'MU02' OR SenderlD - 'ND50'
14. Consider the following tables STUDENT and STREAM. Write SQL commands for the statements (i) to (v). [4]
TABLE: STUDENT

SCODE NAME AGE STRODE POINTS GRADE

101 Amit 16 1 6 NULL

102 Arjun 13 3 4 NULL

103 Zaheer 14 2 1 NULL

105 Gagan 15 5 2 NULL

108 Kumar 13 6 8 NULL

109 Rajesh 17 5 8 NULL

110 Naveen 13 3 9 NULL

113 Ajay 16 2 3 NULL

115 Kapil 14 3 2 NULL

120 Gurdeep 15 2 6 NULL

TABLE: STREAM

7 / 40
 STRCDE STRNAME

1 SCIENCE+COMP

2 SCIENCE+BIO

3 SCIENCE+ECO

4 COMMERCE+MATHS

5 COMMERCE+SOCIO

6 ARTS+MATHS

7 ARTS+SOCIO

i. To display the name of streams in alphabetical order from table STREAM.

ii. To display the number of students whose POINTS are more than 5.
iii. To update GRADE to ‘A’ for all those students, who are getting more than 8 as POINTS.
iv. ARTS+MATHS stream is no more available. Make necessary change in table STREAM.
v. To display student’s name whose stream name is science and computer.
15. Write SQL commands for (i) to (v) on the basis of table STUDENT [4]
TABLE: STUDENT

SNO NAME STREAM FEES AGE SEX

1 ARUN KUMAR COMPUTER 750.00 17 M

2 DIVYAJENEJA COMPUTER 750.00 18 F

3 KESHAR MEHRA BIOLOGY 500.00 16 M

4 HARISH SINGH ENG. DR 350.00 18 M

5 PRACHI ECONOMICS 300.00 19 F

6 NISHA ARORA COMPUTER 750.00 15 F

7 DEEPAK KUMAR ECONOMICS 300.00 16 M

8 SARIKA VASWANI BIOLOGY 500.00 15 F

i. List the name of all the students, who have taken stream as COMPUTER.
ii. To count the number of female students.
iii. To display the number of students stream wise.
iv. To display all the records in sorted order of name.
v. To display the stream of student whose name is Harish.
16. Give output for following SQL queries as per given table(s) : [4]
Table: DRESS

DCODE DESCRIPTION PRICE MCODE LAUNCH DATE

10001 FORMAL SHIRT 1250 M001 12-JAN-08

10020 FROCK 750 M004 09-SEP-07

10012 INFORMAL SHIRT 1450 M002 06-JUN-08

8 / 40
 10019 EVENING GOWN 850 M003 06-JUN-08

10090 TULIP SKIRT 850 M002 31-MAR-07

10023 PENCIL SKIRT 1250 M003 19-DEC-08

10089 SLACKS 850 M003 20-OCT-08

10007 FORMAL PANT 1450 M001 09-MAR-08

10009 INFORMAL PANT 1400 M002 20-OCT-08

10024 BABY TOP 650 M003 07-APR-07

Table:: MATERIAL

MCODE TYPE

M001 TERELENE

M002 COTTON

M004 POLYESTER

M003 SILK

i. SELECT SUM (PRICE) FROM DRESS WHERE

MCODE = 'M00P ;
ii. SELECT DESCRIPTION, TYPE FROM DRESS, MATERIAL
WHERE DRESS.MCODE = MATERIAL.MCODE
AND DRESS.PRICE >= 1250;
iii. SELECT MAX(MCODE) FROM MATERIAL ;
iv. SELECT COUNT(DISTINCT PRICE) FROM DRESS;
17. Consider the following tables GAMES and PLAYER and answer (b) and (c) parts of this question: [4]
Table: GAMES

GCode Game Name Type Number Prize Money Schedule Date

101 Carom Board Indoor 2 5000 23-Jan-2004

102 Badminton Outdoor 2 12000 12-Dec-2003

103 Table Tennis Indoor 4 8000 14-Feb-2004

105 Chess Indoor 2 9000 01-Jan-2004

108 Lawn Tennis Outdoor 4 25000 19-Mar-2004

Table: PLAYER

PCode Name GCode

1 Nabi Ahmad 101

2 Ravi Sahai 108

3 Jatin 101

4 Nazneen 103

9 / 40
 a. What do you understand by primary key and candidate keys?
b. Write SQL commands for the following statements:
i. To display the name of all GAMES with their GCodes.
ii. To display details of those GAMES which are having PrizeMoney more than 7000.
iii. To display the content of the GAMES table in ascending order of Schedule Date.
iv. To display sum of PrizeMoney for each type of GAMES.
c. Give the output of the following SQL queries:
i. SELECT COUNT (DISTINCT Number) FROM GAMES;
ii. SELECT MAX(ScheduleDate), MIN (ScheduleDate) FROM GAMES;
iii. SELECT Name, GameName FROM GAMES G, PLAYER P WHERE (G.Gcode= P.Gcode AND
G.PrizeMoney>10000);
iv. SELECT DISTINCT Gcode FROM PLAYER;
18. Answer the questions (i) to (v) on the basis of the following tables SHOPPE and ACCESSORIES. [4]
TABLE: SHOPPE

Id SName Area

S001 ABC Computeronics CP

S002 All Infotech Media GK II

S003 Tech Shoppe CP

S004 Geeks Tecno Soft Nehru Place

S005 Hitech Tech Store Nehru Place

TABLE: ACCESSORIES

No Name Price Id

A01 Mother Board 12000 S01

A02 Hard Disk 5000 S01

A03 Keyboard 500 S02

A04 Mouse 300 S01

A05 Mother Board 13000 S02

A06 Keyboard 400 S03

A07 LCD 6000 S04

T08 LCD 5500 S05

T09 Mouse 350 S05

T10 Hard Disk 4500 S03

i. To display Name and Price of all the Accessories in ascending order of their Price.
ii. To display Id and SName of all Shoppe located in Nehru Place.
iii. To display Minimum and Maximum Price of each Name of Accessories.
iv. To display Name, Price of all Accessories and their respective SName, where they are available.

10 / 40
 v. To display name of accessories whose price is greater than 1000.
19. Give output for following SQL queries as per given table(s) : [4]
Table: Books

Book_Id Book_Name Author_Name Publishers Price Type Qty.

C0001 Fast Cook Lata Kapoor EPB 355 Cookery 5

F0001 The Tears William Hopkins First Publ. 650 Fiction 20

T0001 My First C++ Brian and Brooke EPB 350 Text 10

T0002 C++ Brainworks A.W. Rossaine TDH 350 Text 15

F0002 Thunderbolts Anna Roberts First Publ. 750 Fiction 50

Table: Issued

Book_Id Quantity_Issued

T0001 4

C0001 5

F0001 2

i. SELECT COUNT() FROM Books;

ii. SELECT MAX(Price) FROM Books WHERE Quantity >= 15;
iii. SELECT Book_Name, Author_Name FROM Books WHERE Publishers = "EPB";
iv. SELECT COUNT (DISTINCT Publishers) FROM Books WHERE Price >= 400;
20. Consider the following table named "GARMENT". Write command of SQL for (i) to (iv). [4]
Table : GARMENT

GCODE GNAME SIZE COLOUR PRICE

111 TShirt XL Red 1400.00

112 Jeans L Blue 1600.00

113 Skirt M Black 1100.00

114 Ladies Jacket XL Blue 4000.00

115 Trousers L Brown 1500.00

116 Ladies Top L Pink 1200.00

i. To display names of those garments that are available in 'XL' size.

ii. To display codes and names of those garments that have their names starting with 'Ladies'.
iii. To display garment names, codes and prices of those garments that have price in the range 1000.00 to
1500.00 (both 1000.00 and 1500.00 included).
iv. To change the colour of garment with code as 116 to "Orange".
21. Consider the following tables SCHOOL and ADMIN and answer this question: [4]
Table: SCHOOL

CODE TEACHER NAME SUBJECT DOJ PERIODS EXPERIENCE

1001 Ravi Shankar English 12/3/2000 24 10

11 / 40
 1009 Priya Rai Physics 03/09/1998 26 12

1203 Lisa Anand English 09/04/2000 27 5

1045 Yashraj Maths 24/08/2000 24 15

1123 Ganan Physics 16/07/1999 28 3

1167 Harish B Chemistry 19/10/1999 27 5

1215 Umesh Physics 11/05/1998 22 16

Table: Admin

Code Gender Designation

1001 Male Vice Principal

1009 Female Co-ordinator

1203 Female Co-ordinator

1045 Male HOD

1123 Male Senior Teacher

1167 Male Senior Teacher

1215 Male HOD

Write SQL statements for the following:

i. To display TEACHERNAME, PERIODS of all teachers whose periods are more than 25.
ii. To display all the information from the table SCHOOL in descending order of experience.
iii. To display DESIGNATION without duplicate entries from the table ADMIN.
iv. To display TEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and
ADMIN of Male teachers.
22. Consider the following tables. Write SQL commands for the statements (i) to (v). [4]
TABLE: SENDER

SenderlD SenderName SenderAddress SenderCity

ND01 R Jair 2, ABC Appts New Delhi

MU02 H Sinha 12, Newtown Mumbai

MU15 S Jha 27/A, Park Street Mumbai

ND50 T Prasad 122-K, SDA New Delhi

TABLE: RECIPIENT

RecID SenderlD RecName RecAddress RecCity

KO05 ND01 R Bajpayee 5, Central Avenue Kolkata

ND08 MU02 S Mahajan 116, A Vihar New Delhi

MU19 ND01 H Singh 2A, Andheri East Mumbai

MU32 MU15 P K Swamy B5, CS Terminus Mumbai

12 / 40
 ND48 ND50 S Tripathi 13, B1 D, Mayur Vihar New Delhi

i. To display the names of all Senders from Mumbai.

ii. To display the RecID, SenderName, SenderAddress, RecName, RecAddress for every Recipient.
iii. To display Recipient details in ascending order of RecName.
iv. To display number of Recipients from each City.
v. To display the detail of recipients who are in Mumbai.
23. Consider the following table DRESS. Write SQL commands for the following statements. [4]
Table: DRESS

D_CODE DESCRIPTION PRICE MCODE LAUNCH DATE

10001 FORMAL SHIRT 1250 M001 12-JAN-08

10020 FROCK 750 M004 09-SEP-07

10012 INFORMAL SHIRT 1450 M002 06-JUN-08

10019 EVENING GOWN 850 M003 06-JUN-08

10090 TULIP SKIRT 850 M002 31-MAR-07

10023 PENCIL SKIRT 1250 M003 19-DEC-08

10089 SLACKS 850 M003 20-OCT-08

10007 FORMAL PANT 1450 M001 09-MAR-08

10009 INFORMAL PANT 1400 M002 20-OCT-08

10024 BABY TOP 650 M003 07-APR-07

i. To display DCODE and DESCRIPTION of each dress in ascending order of DCODE.

ii. To display the details of all the dresses which have LAUNCHDATE in between 05-DEC-07 and 20-JUN-08
(inclusive of both the dates).
iii. To display the average PRICE of all the dresses which are made up of material with MCODE as M003.
iv. To display materialwise highest and lowest price of dresses from DRESS table. (Display MCODE of each
dress along with highest and lowest price)
24. Study the following tables DOCTOR and SALARY and write SQL commands for the questions (i) to (v). [4]
TABLE: DOCTOR

ID NAME DEPT SEX EXPERIENCE

101 John ENT M 12

104 Smith ORTHOPEDIC M 5

107 George CARDIOLOGY M 10

114 Lara SKIN F 3

109 K George MEDICINE F 9

105 Johnson ORTHOPEDIC M 10

117 Lucy ENT F 3

13 / 40
 111 Bill MEDICINE F 12

130 Morphy ORTHOPEDIC M 15

TABLE: SALARY

ID BASIC ALLOWANCE CONSULTATION

101 12000 1000 300

104 23000 2300 500

107 32000 4000 500

114 12000 5200 100

109 42000 1700 200

105 18900 1690 300

130 21700 2600 300

i. Display NAME of all doctors who are in MEDICINE department having more than 10 yrs experience from
the table DOCTOR.
ii. Display the average salary of all doctors working in ENT department using the tables DOCTOR and
SALARY.SALARY = BASIC + ALLOWANCE.
iii. Display the minimum ALLOWANCE of female doctors.
iv. Display the highest consultation fee among all male doctors.
v. To display the detail of doctor who have experience more than 12 years.
25. Consider the following table GARMENT. Write SQL commands for the following statements. [4]
Table: GARMENT

GCODE Description Price FCODE READY DATE

10023 PENCIL SKIRT 1150 F03 19-DEC-08 j

10001 FORMAL SHIRT 1250 F01 12-JAN-08

10012 INFORMAL SHIRT 1550 F02 06-JUN-08

10024 BABY TOP 750 F03 07-APR-07

10090 TULIP SKIRT 850 F02 31-MAR-07

10019 EVENING GOWN 850 F03. 06JUN-08

10009 INFORMAL PANT 1500 F02 20OCT-08

10017 FORMAL PANT 1350 F01 09-MAR-08

10020 FROCK 850 F04 09-SEP-07

10089 SLACKS 750 F03 31OCT-08

i. To display GCODE and DESCRIPTION of each GARMENT in descending order of GCODE.

ii. To display the details of all the GARMENTS, which have READY DATE in between 08-DEC-07 and 16-
JUN-08 (inclusive of both the dates).
iii. To display the average PRICE of all the GARMENTS, which are made up of FABRIC with FCODE as F03.

14 / 40
 iv. To display FABRIC wise highest and lowest price of GARMENTS from GARMENT table. (Display
FCODE of each GARMENT along with highest and lowest price)

26. i. What possible output(s) are expected to be displayed on screen at the time of execution of the following [4]
code?

import random
S=["Pen","Pencil","Eraser","Bag","Book"]
for i in range (1,2):
f=random.randint(i,3)
s=random.randint(i+1,4)
print(S[f],S[s],sep=":")

Options :
I. Pencil:Book
II. Pencil:Book
Eraser:Bag
III. Pen:Book
Bag:Book
IV. Bag:Eraser
ii. The table Bookshop in MySQL contains the following attributes:
B_code - Integer
B_name - String
Qty - Integer
Price - Integer
Note the following to establish connectivity between Python and MySQL on a 'localhost' :
Username is 'shop'
Password is 'Book'
The table exists in a MySQL database named Bstore.
The code given below updates the records from the table Bookshop in MySQL.
Statement 1 - to form the cursor object.
Statement 2 - to execute the query that updates the Qty to 20 of the records whose B_code is 105 in the table.
Statement 3 - to make the changes permanent in the database.

import mysql.connector as mysql

def update_book():

mydb=mysql.connect(host="localhost",
user="shop",passwd="Book",database="Bstore")
mycursor=__________ # Statement 1
qry= "update Bookshop set Qty=20 where
B_code=105"
___________________ # Statement 2
___________________ # Statement 3

15 / 40
27. Consider the following tables PRODUCT and CLIENT. Write SQL commands for the following statements. [4]
Table: PRODUCT

P_ID ProductName Manufacturer Price

TP01 Talcum Powder LAK 40

FW05 Face Wash ABC 45

BS01 Bath Soap ABC 55

SH06 Shampoo XYZ 120

FW12 Face Wash XYZ 95

Table: CLIENT

C_ID ClientName City P_ID

01 Cosmetic Shop Delhi FW05

06 Total Health Mumbai BS01

12 Live Life Delhi SH06

15 Pretty Woman Delhi FW12

16 Dreams Banglore TP01

i. To display the details of those Clients whose City is Delhi.

ii. To display the details of Products whose Price is in the range of 50 to 100 (Both values included).
iii. To display the ClientName, City from table Client, and ProductName and Price from table Product, with
their corresponding matching P_ID.
iv. To increase the Price of all Products by 10.
28. Give output for following SQL queries as per given table(s) : [4]
Table : ITEM

I_ID ItemName Manufacturer Price

PC01 Personal Computer ABC 35000

LC05 Laptop ABC 55000

PC03 Personal Computer XYZ 32000

PC06 Personal Computer COMP 37000

LC03 Laptop PQR 57000

Table : CUSTOMER

C_ID CustomerName City IJD

01 N Roy Delhi LC03

06 H Singh Mumbai PC03

12 R Pandey Delhi PC06

15 C Sharma Delhi LC03

16 / 40
 16 K Agarwal Banglore PC01

i. SELECT DISTINCT City FROM Customer ;

ii. SELECT ItemName, MAX(Price), Count(*)
FROM Item GROUP BY ItemName ;
iii. SELECT CustomerName, Manufacturer
FROM Item, Customer
WHERE Item.I_ID = Customer.I_ID ;
iv. SELECT ItemName, Price * 100
FROM Item WHERE Manufacturer = 'ABC';
29. Consider the following tables CABHUB and CUSTOMER and answer the following parts of this question : [4]
Table: CABHUB

Vcode VehicleName Make Color Capacity Charges

100 Innova Toyota WHITE 7 15

102 SX4 Suzuki BLUE 4 14

104 C Class Mercedes RED 4 35

105 A-Star Suzuki WHITE 3 14

108 Indigo Tata SILVER 3 12

Table: CUSTOMER

CCode CName Vcode

1 Hemant Sahu 101

2 Raj Lai 108

3 Feroza Shah 105

4 Ketan Dhal 104

Give the output of the following SQL queries :

i. SELECT COUNT (DISTINCT Make) FROM CABHUB ;
ii. SELECT MAX(Charges), MIN(Charges) FROM CABHUB ;
iii. SELECT COUNT(*), Make FROM CABHUB ;
iv. SELECT VehicleName FROM CABHUB WHERE Capacity = 4;
30. Study the following tables FLIGHTS and FARES and write SQL commands for the questions (i) to (iv). [4]
TABLE: FLIGHTS

FL_NO STARTING ENDING NO_FLIGHT NO_STOPS

IC301 MUMBAI DELHI 8 0

IC799 BENGALURU DELHI 2 1

MC101 INDORE MUMBAI 3 0

IC302 DELHI MUMBAI 8 0

AM812 KANPUR BENGALURU 3 1

17 / 40
 IC899 MUMBAI KOCHI 1 4

AM501 DELHI TRIVANDRUM 1 5

MU499 MUMBAI MADRAS 3 3

IC701 DELHI AHMEDABAD 4 0

TABLE: FARES

FL_NO AIRLINES FARE TAX%

IC701 INDIAN AIRLINES 6500 10

MU499 SAHARA 9400 5

AM501 JET AIRWAYS 13450 8

IC899 INDIAN AIRLINES 8300 4

IC302 INDIAN AIRLINES 4300 10

IC799 INDIAN AIRLINES 10500 10

MC101 DECCAN AIRLINES 3500 4

i. Display FL_NO and NO_FLIGHT from KANPUR to BENGALURU from the table FLIGHTS.
ii. Arrange the contents of the table FLIGHTS in the ascending order of FL_NO.
iii. Display the FL_NO and fare to be paid for the flights from DELHI to MUMBAI using the tables FLIGHTS
and FARES, where the fare to be paid = FARE + FARE * TAX % 100.
iv. Display the minimum fare INDIAN AIRLINES is offering from the table FARES.
v. To display the detail fares of Indian airlines.
31. Consider the following tables WORKER and PAYLEVEL and answer (a) and (b) parts of this question: [4]
Table: WORKER

ECODE NAME DESIGN PLEVEL DOJ DOB

11 Radhe Shyam Supervisor P001 13-Sep-2004 23-Aug-1981

12 Chander Nath Operator P003 22-Feb-2010 12-Jul-1987

13 Fizza Operator P003 14-Jun-2009 14-Oct-1983

15 Ameen Ahmed Mechanic P002 21-Aug-2006 13-Mar-1984

18 Sanya Clerk P002 19-Dec-2005 09-Jun-1983

Table: PAYLEVEL

PLEVEL PAY ALLOWANCE

P001 26000 12000

P002 22000 10000

P003 12000 6000

a. Write SQL commands for the following statements:

i. To display the name of all Workers in descending order of DOB.
ii. To display NAME and DESIGN of those Workers, whose PLEVEL is either P001 or P002.

18 / 40
 iii. To display the content of all the workers table, whose DOB is in between '19- JAN-1984' and '18-JAN-
1987'.
iv. To add a new row with the following: 19, 'DayaKishore', 'Operator', 'P003', '19- Sep-2008', 17-Jul-1984'
b. Give the output of the following SQL queries:
i. SELECT COUNT (PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;
ii. SELECT MAX(DOB), MIN(DOJ) FROM WORKER;
iii. SELECT Name, PAY FROM WORKER W, PAYLEVEL P WHERE W.PLEVEL = P.PLEVEL AND
W.ECODE < 13;
iv. SELECT PLEVEL, PAY+ ALLOWANCE FROM PAYLEVEL WHERE PLEVEL= "P003 ";
32. Consider the following tables ACTIVITY and COACH and answer (a) and (b) parts of this question: [4]
Table: ACTIVITY

ACode ActivityName Stadium ParticipantsNum PrizeMoney ScheduleDate

1001 Relay 100 × 4 Star Annex 16 10000 23-Jan-2004

1002 High Jump Star Annex 10 12000 12-Dec-2003

1003 Shot Put Super Power 12 8000 14-Feb-2004

1005 Long Jump Star Annex 12 9000 01-Jan-2004

1008 Discuss Throw Super Power 10 15000 19-Mar-2004

Table: COACH

PCode Name ACode

1 Ahmad Hussain 1001

2 Ravinder 1008

3 Janila 1001

4 Naaz 1003

a. Write SQL commands for the following statements:

i. To display the names of all activities with their Acodes in descending order.
ii. To display sum of PrizeMoney for the Activities played in each of the Stadium separately.
iii. To display the coach's name and Acodes in ascending order of Acode from the table Coach.
iv. To display the content of the Activity table whose schedule date earlier than 01-01-2004 in ascending
order of Participants Num.
b. Give the output of the following SQL queries:
i. SELECT COUNT (DISTINCT Participants Num) FROM ACTIVITY;
ii. SELECT MAX (Schedule Date), Min (Schedule Date) FROM ACTIVITY;
iii. SELECT Name, Activity Name FROM ACTIVITY A, COACH C WHERE A. Acode=C. Acode AND
A.Participants Num=10;
iv. SELECT DISTINCT Acode FROM COACH;
33. Write SQL commands for (i) to (v) on the basis of table INTERIORS. [4]
TABLE: INTERIORS

No. ITEM NAME TYPE DATE OF STOCK PRICE DISCOUNT

19 / 40
 1 Red rose Double Bed 23/02/02 32000 15

2 Soft touch Baby cot 20/01/02 9000 10

3 Jerry’s home Baby cot 19/02/02 8500 10

4 Rough wood Office Table 01/01/02 20000 20

5 Comfort zone Double Bed 12/01/02 15000 20

6 Jerry look Baby cot 24/02/02 7000 19

7 Lion king Office Table 20/02/02 16000 20

8 Royal tiger Sofa 22/02/02 30000 25

9 Park sitting Sofa 13/12/01 9000 15

10 Dine Paradise Dining Table 19/02/02 11000 15

11 White Wood Double Bed 23/03/03 20000 20

12 James 007 Sofa 20/02/03 15000 15

13 Tom look Baby cot 21/02/03 7000 10

i. To show all information about the Sofa from the INTERIORS table.
ii. To list the ITEMNAME, which are priced at more than 10000 from the INTERIORS table.
iii. To list ITEMNAME and TYPE of those items, in which DATEOFSTOCK is before 22/01/02 from the
INTERIORS table in descending order of ITEMNAME.
iv. To insert a new row in the INTERIORS table with the following data
{14, 'Truelndian' , 'Office Table', '25/03/03', 15000, 20}
v. To display the name of item with their price which have discount more than 20.
34. Write SQL commands for the queries (i) to (iv) and output for (v) to (viii) based on a table COMPANY and [4]
CUSTOMER.
COMPANY

CID NAME CITY PRODUCTNAME

111 SONY DELHI TV

222 NOKIA MUMBAI MOBILE

333 ONIDA DELHI TV

444 SONY MUMBAI MOBILE

555 BLACKBERRY MADRAS MOBILE

666 DELL DELHI LAPTOP

CUSTOMER

CUSTID NAME PRICE QTY CID

101 ROHAN SHARMA 70,000 20 222

102 DEEPAK KUMAR 50,000 10 666

20 / 40
 103 MOHAN KUMAR 30,000 5 111

104 SAHIL BANSAL 35,000 3 333

105 NEHA SONI 25,000 7 444

106 SONAL AGGARWAL 20,000 5 333

107 ARUN SINGH 50,000 15 666

i. To display those company name which are having price less than 30000.
ii. To display the name of the companies in reverse alphabetical order.
iii. To increase the price by 1000 for those customer whose name starts with S?
iv. To add one more column t_price with decimal(10, 2) to the table customer
v. SELECT COUNT(*), CITY FROM COMPANY GROUP BY CITY
vi. SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY>10
vii. SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE "%r%"
viii. SELECT PRODUCTNAME, CITY, PRICE FROM COMPANY, CUSTOMER WHERE COMPANY. CID =
CUSTOMER.CID AND PRODUCTNAME= "MOBILE"
35. Give output for following SQL queries as per given table : [4]
Table : LAB

No. ItemName CostPerltem Quantity DateofPurchase Warranty Operational

1. Computer 60000 9 21/5/96 2 7

2. Printer 15000 3 21/5/97 4 2

3. Scanner 18000 1 29/8/98 3 1

4. camera 21000 2 13/6/96 1 2

5. Hub 8000 1 31/10/99 2 1

6. UPS 5000 5 21/5/96 1 4

7. Plotter 25000 2 11/1/2000 2 2

i. SELECT MIN(DISTINCT Quantity) FROM LAB;

ii. SELECT MIN(Warranty) FROM LAB WHERE Quantity = 2;
iii. SELECT SUM(CostPerltem) FROM LAB WHERE Quantity > 2;
iv. SELECT AVG(CostPerltem) FROM LAB WHERE DateofPurchase < {1/1/99};
36. Consider the following tables ACTIVITY and COACH and answer the following parts of this question : [4]
Table: ACTIVITY

Acode ActivityName Stadium ParticipantsNum PrizeMoney ScheduteDate

1001 Relay 100 × 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

21 / 40
 Table : COACH

Pcode Name Acode

1 Ahmad Hussain 1001

2 Ravinder 1008

3 Janila 1001

4 Naaz 1003

Give the output of the following SQL queries :

i. SELECT COUNT (DISTINCT ParticipantsNum) FROM ACTIVITY;
ii. SELECT MAX(ScheduleDate), MIN(ScheduleDate) FROM ACTIVITY;
iii. SELECT Name, ActivityName FROM ACTIVITY A, COACH C
WHERE A.Acode = C.Acode AND A.ParticipantsNum = 10;
iv. SELECT DISTINCT ParticipantsNum FROM ACTIVITY;
37. Consider the following tables RESORT and OWNEDBY and answer the questions (a) and (b) parts of this [4]
question:
Table: RESORT

RCODE PLACE RENT TYPE STARTDATE

R001 GOA 15000 5 STAR 12-Jan-02

R002 HIMACHAL 9000 4 STAR 20-Dec-07

R003 KERALA 12500 5 STAR 10-Mar-06

R004 HIMACHAL 10500 2 STAR 25-Nov-05

R005 GUJARAT 8000 4 STAR 01-Jan-03

R006 GOA 18000 7 STAR 30-Mar-08

R007 ORISSA 7500 2 STAR 12-Apr-99

R008 KERALA 11000 5 STAR 03-Mar-03

R009 HIMACHAL 9000 2 STAR 15-Oct-08

R010 GOA 13000 5 STAR 12-APR-06

Table: OWNEDBY

Place C OWNER

GOA RAJ RESORTS

KERALA KTDC

HIMACHAL HTDC

GUJARAT MAHINDRA RESORTS

ORISSA OTDC

a. Write SQL commands for the following statements:

22 / 40
 i. To display the RCODE and PLACE of all '5 STAR' resorts in the alphabetical order of the place from
table RESORT.
ii. To display the maximum and minimum rent for each type of resort from table RESORT.
iii. To display the details of all resorts which were started after 31-DEC-05 from table RESORT.
iv. Display the OWNER of all '5 STAR' resorts from tables RESORT and OWNEDBY.
b. Give output for the following SQL queries:
i. SELECT MIN(RENT) FROM RESORT WHERE PLACE = KERALA';
ii. SELECT TYPE, START DATE FROM RESORT WHERE TYPE = 2 STAR' ORDERBY STARTDATE;
iii. SELECT PLACE, OWNER FROM OWNEDBY Where PLACE LIKE "%A";
iv. SELECT RCODE, RENT FROM RESORT, OWNEDBY WHERE (RESORT. PLACE = OWNEDBY.
PLACE AND TYPE = '3 STAR );
38. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the table. [4]
TABLE: ACCOUNT

ANO ANAME ADDRESS

101 Nirja Singh Bangalore

102 Rohan Gupta Chennai

103 Ali Reza Hyderabad

104 Rishabh Jain Chennai

105 Simian Kaur Chandigarh

TABLE: TRANSACT

TRNO ANO AMOUNT TYPE DOT

T001 101 2500 Withdraw 2017-12-21

T002 103 3000 Deposit 2017-06-01

T003 102 2000 Withdraw 2017-06-12

T004 103 1000 Deposit 2017-10-22

T005 101 12000 Deposit 2017-11-06

i. To display details of all transactions of TYPE Deposit from Table TRANSACT

ii. To display the ANO and AMOUNT of all Deposits and Withdrawals done in the month of October 2017
from table TRANSACT
iii. To display the last date of transaction (DOT) from the table TRANSACT for the Accounts having ANO as
103.
iv. To display all ANO, ANAME and DOT of those persons from tables ACCOUNT and TRANSACT who
have done transactions less than or equal to 3000.
v. SELECT ANO, ANAME FROM ACCOUNT WHERE ADDRESS NOT IN (CHENNAI BANGALORE);
vi. SELECT DISTINCT ANO FROM TRANSACT
vii. SELECT ANO. COUNT (*), MIN (AMOUNT) FROM TRANSACT GROUP BY ANO HAVING COUNT
(*) > 1
viii. SELECT COUNT (*), SUM (AMOUNT) FROM TRANSACT WHERE DOT < = '2017-06-01'

23 / 40
39. What do you understand by Candidate Keys in a table? Give a suitable example of Candidate keys from a table [4]
containing some meaningful data.
40. Consider the following tables CABHUB and CUSTOMER. Write SQL commands for the following statements. [4]
Table: CABHUB

Vcode VehicleName Make Color Capacity Charges

100 Innova Toyota WHITE 7 15

102 SX4 Suzuki BLUE 4 14

104 C Class Mercedes RED 4 35

105 A-Star Suzuki WHITE 3 14

108 Indigo Tata SILVER 3 12

Table: CUSTOMER

CCode CName Vcode

1 Hemant Sahu 101

2 Raj Lai 108

3 Feroza Shah 105

4 Ketan Dhal 104

i. To display the names of all the white colored vehicles.

ii. To display name of vehicle, make and capacity of vehicles in ascending order of their seating capacity.
iii. To display the highest charges at which a vehicle can be hired from CABHUB.
iv. To display the customer name and the corresponding name of the vehicle hired by them
41. Write SQL statements for the following queries (i) to (v) based on the relations CUSTOMER and [4]
TRANSACTION given below:
Table : CUSTOMER

ACNO NAME GENDER BALANCE

C1 RISHABH M 15000

C2 AAKASH M 12500

C3 INDIRA F 9750

C4 TUSHAR M 14600

C5 ANKITA F 22000

Table : TRANSACTION

ACNO TDATE AMOUNT TYPE

C1 2020-07-21 1000 DEBIT

C2 2019-12-31 1500 CREDIT

C3 2020-01-01 2000 CREDIT

a. To display all information about the CUSTOMERs whose NAME starts with 'A'.

24 / 40
 b. To display the NAME and BALANCE of Female CUSTOMERs (with GENDER as 'F') whose
TRANSACTION Date (TDATE) is in the year 2019.
c. To display the total number of CUSTOMERs for each GENDER.
d. To display the CUSTOMER NAME and BALANCE in ascending order of GENDER.
e. To display CUSTOMER NAME and their respective INTEREST for all CUSTOMERs where
INTEREST is calculated as 8% of BALANCE.
42. Give output for following SQL queries as per given table(s) for (i) to (v) and write sql commands for point (vi) [4]
and (vii).
Table: GARMENT

GCODE Description Price FCODE READYDATE

10023 PENCIL SKIRT 1150 F03 19-DEC-08

10001 FORMAL SHIRT 1250 F01 12-JAN-08

10012 INFORMAL SHIRT 1550 F02 06-JUN-08

10024 BABY TOP 750 F03 07-APR-07

10090 TULIP SKIRT 850 F02 31-MAR-07

10019 EVENING GOWN 850 F03 06-JUN-08

10009 INFORMAL PANT 1500 F02 20-OCT-08

10017 FORMAL PANT 1350 F01 09-MAR-08

10020 FROCK 850 F04 09-SEP-07

10089 SLACKS 750 F03 31-OCT-08

Table: FABRIC

FCODE TYPE

F04 POLYSTER

F02 COTTON

F03 SILK

F01 TERELENE

i. SELECT SUM (PRICE) FROM GARMENT WHERE FCODE = 'F01';

ii. SELECT DESCRIPTION, TYPE FROM GARMENT, FABRIC
WHERE GARMENT.FCODE = FABRIC.FCODE AND GARMENT.PRICE >= 1260;
iii. SELECT MAX (FCODE) FROM FABRIC;
iv. SELECT COUNT (DISTINCT PRICE) FROM GARMENT;
v. To display GCODE and DESCRIPTION of each GARMENT in descending order of GCODE
vi. To display the details of all the GARMENTs, which have READYDATE in between 08-DEC-07 and 16-
JUN-08 (inclusive of both the dates).
43. Consider the following tables STOCK and DEALERS and answer (a) and (b) parts of this question: [4]
Table: STOCK

25 / 40
 ItemNo Item Dcode Qty UnitPrice StockDate

5005 Ball Pen 0.5 102 100 16 31-Mar-10

5003 Ball Pen 0.25 102 150 20 01-Jan-10

5002 Gel Pen Premium 101 125 14 14-Feb-10

5006 Gel Pen Classic 101 200 22 01-Jan-09

5001 Eraser Small 102 210 5 19-Mar-09

5004 Eraser Big 102 60 10 12-Dec-09

5009 Sharpener Classic 103 160 8 23-Jan-09

Table: DEALERS

Dcode Dname

101 Reliable Stationers

103 Classic Plastics

102 Clear Deals

a. Write SQL commands for the following statements:

i. To display the details of all Items in the STOCK table in ascending order of StockDate.
ii. To display ItemNo and Item name of those items from STOCK table whose UnitPrice is more than
Rupees 10.
iii. To display the details of those items whose dealer code (Dcode) is 102 or quantity in STOCK (Qty) is
more than 100 from the table Stock.
iv. To display maximum UnitPrice of items for each dealer individually as per Dcode from the table
STOCK.
b. Give the output of the following SQL queries:
i. SELECT COUNT (DISTINCT Dcode) FROM STOCK;
ii. SELECT Qty* UnitPrice FROM STOCK WHERE ItemNo=5006;
iii. SELECT Item, Dname FROM STOCK S, Dealers D WHERE S.Dcode=D.Dcode AND ItemNo = 5004;
iv. SELECT MIN (StockDate) FROM STOCK;
44. Give output for following SQL queries as per given table(s): [4]
Table: WORKERS

W_ID FIRSTNAME LASTNAME ADDRESS CITY

102 Sam Tones 33 Elm St. Paris

105 Sarah Ackerman ‘ 440 U.S. 110 New York

144 Manila Sengupta 24 Friends Street New Delhi

210 George Smith 83 First Street Howard

255 Mary Jones 842 Vine Ave. Losantiville

300 Robert Samuel 9 Fifth Cross Washington

26 / 40
 335 Henry Williams 12 Moore Street Boston

403 Ronny Lee 121 Harrison St. New York

451 Pat Thompson 11 Red Road Paris

Table: DESIG

W_ID SALARY BENEFITS DESIGNATION

102 75000 15000 Manager

105 85000 25000 Director

144 70000 15000 Manager

210 75000 12500 Manager

255 50000 12000 Clerk

300 45000 10000 Clerk

335 40000 10000 Clerk

400 32000 7500 Salesman

451 28000 7500 Salesman

i. SELECT FIRSTNAME, SALARY

FROM WORKERS, DESIG
WHERE DESIGNATION = 'Manager' AND
EMPLOYEE.WJD = DESIG.W.ID;
ii. SELECT COUNT (DISTINCT DESIGNATION) FROM DESIG;
iii. SELECT DESIGNATION, SUM(SALARY)
FROM DESIG
GROUP BY DESIGNATION HAVING COUNT(*) < 3;
iv. SELECT SUM(BENEFITS)
FROM WORKERS
WHERE DESIGNATION = 'Salesman';
45. Consider the following tables EMPLOYEE and DEPARTMENT and answer (a) and (b) parts of this question. [4]
Table: EMPLOYEE

TCode TName DepCde Salary Age JoinDate

15 Sameer Sharma 123 75000 39 01-Apr-2007

21 Raguvinder K 101 86000 29 11-Nov-2005

34 Rama Gupta 119 52500 43 03-Mar-2010

46 C R Menon 103 67000 38 12-Jul-2004

77 Mohan Kumar 103 63000 55 25-Nov-2000

81 Rajesh Kumar 119 74500 48 11-Dec-2008

89 Sanjeev P 101 92600 54 12-Jan-2009

27 / 40
 93 Pragya Jain 123 32000 29 05-Aug-2006

Table: DEPARTMENT

DepCde DepName DepHead

101 ACCOUNTS Rajiv Kumar

103 HR P K Singh

119 IT Yogesh Kumar

123 RESEARCH Ajay Dutta

a. Write SQL commands for the following statements:

i. To display all DepName along with the DepCde in descending order of DepCde.
ii. To display the average age of Employees in DepCde as 103.
iii. To display the name of DepHead of the Employee named "Sanjeev P"
iv. To display the details of all employees who have joined before 2007 from the EMPLOYEE table.
b. Give the output of the following SQL queries:
i. SELECT COUNT (DISTINCT DepCde) FROM EMPLOYEE;
ii. SELECT MAX(JoinDate), MIN (JointDate) FROM EMPLOYEE;
iii. SELECT TName, DepHead FROM EMPLOYEE E, DEPARTMENT D WHERE E.DepCde =
D.DepCde;
iv. SELECT COUNT (*) FROM EMPLOYEE WHERE Salary > 60000 AND Age > 30;
46. Consider the following tables ACTIVITY and COACH. Write SQL commands for the following statements [4]
Table: ACTIVITY

Acode ActivityName Stadium ParticipantsNum PrizeMoney ScheduleDate

1001 Relay 100 × 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. To display the names of all activities with their Acodes in descending order.
ii. To display sum of PrizeMoney for the Activities played in each of the Stadium separately.
iii. To display the coach's names and Acodes in ascending order of Acode from the table COACH.

28 / 40
 iv. To display the content of all activities for which ScheduleDate is earlier than 01-01-2004 in ascending order
of ParticipantsNum.
47. Give output for following SQL queries as per given table(s) : [4]
Table: PRODUCT

P_ID ProductName Manufacturer Price

TP01 Talcum Powder LAK 40

FW05 Face Wash ABC 45

BS01 Bath Soap ABC 55

SH06 Shampoo XYZ 120

FW12 Face Wash XYZ 95

Table : CLIENT

C_ID ClientName City PJD

01 Cosmetic Shop Delhi FW05

06 Total Health Mumbai BS01

12 Live Life Delhi SH06

15 Pretty Woman Delhi FW12

16 Dreams Banglore TP01

SELECT DISTINCT City FROM Client;

i. SELECT DISTINCT City FROM Client ;
ii. SELECT Manufacturer, MAX(Price), Min(Price), Count(*)
FROM Product GROUP BY Manufacturer ;
iii. SELECT ClientName, ProductName
FROM Product, Client
WHERE Client.P_Id = Product.PJd ;
iv. SELECT ProductName, Price * 4 FROM Product;
48. Give output for following SQL queries as per given table(s); [4]
Table: CONSIGNOR

CnorlD CnorName CnorAddress City

ND01 R Singhal 24, ABC Enclave New Delhi

ND02 Amit Kumar 123, Palm Avenue New Delhi

MU15 R Kohli 5/A, South Street Mumbai

MU50 S Kaur 27-K, Westend Mumbai

Table: CONSIGNEE

CneelD CnorlD CneeName CneeAddress CneeCity

MU05 ND01 Rahul Kishore 5, Park Avenue Mumbai

29 / 40
 ND08 ND02 P Dhingra 16/J, Moore Enclave New Delhi

KOI 9 MU15 A P Roy 2A, Central Avenue Kolkata

MU32 ND02 S Mittal P 245, AB Colony Mumbai

ND48 MU50 B P Jain 13, Block D, A Vihar New Delhi

i. SELECT DISTINCT City FROM CONSIGNEE;

ii. SELECT A.CnorName, B.CneeName
FROM Consignor A, Consignee B
WHERE A.CnorlD = B.CnorlD AND B.CneeCity = 'Mumbai';
iii. SELECT CneeName, CneeAddress
FROM Consignee
WHERE CneeCity NOT IN('Mumbai', 'Kolkata');
iv. SELECT CneelD, CneeName
FROM Consignee
WHERE CnorlD = 'MU15' OR CnorlD - 'ND01';
49. Give a suitable example of a table with sample data and illustrate Primary and Candidate Keys in it. [4]
50. Give output for following SQL queries as per given table(s) : [4]
Table: Teacher

No. Name Department Dateofjoining Salary Sex

1. Raja Computer 21/5/98 8000 M

2. Sangita History 21/5/97 9000 F

3. Ritu Sociology 29/8/98 8000 F

4. Kumar Linguistis 13/6/96 10000 M

5. Venkatraman History 31/10/99 8000 M

6. Sidhu Computer 21/5/86 14000 M

7. Aishwarya Sociology 11/1/1988 12000 F

i. SELECT MIN(DISTINCT Salary) FROM Teacher;

ii. SELECT MIN(Salary) FROM Teacher WHERE Sex = "M";
iii. SELECT SUM(Salary) FROM Teacher WHERE Department = "History";
iv. SELECT AVG(Salary) FROM Teacher WHERE Dateofjoining < {1/1/98};
51. Consider the following table GAMES. Write SQL commands for the following statements. [4]
Table: GAMES

GCode GameName Type Number PrizeMoney ScheduleDate

101 Carom Board Indoor 2 5000 23-Jan-2004

102 Badminton Outdoor 2 12000 12-Dec-2003

103 Table Tennis Indoor 4 8000 14-Feb-2004

105 Chess Indoor 2 9000 01-Jan-2004

30 / 40
 108 Lawn Tennis Outdoor 4 25000 19-Mar-2004

i. To display the name of all GAMES with their GCodes.

ii. To display details of those GAMES which are having PrizeMoney more than 7000.
iii. To display the content of the GAMES table in ascending order of ScheduleDate.
iv. To display sum of PrizeMoney for each Type of GAMES.

52. i. Predict the output of the code given below: [4]

text="LearningCS"
L=len(text)
ntext=""
for i in range (0,L):
if text[i].islower():
ntext=ntext+text[i].upper()
elif text [i].isalnum():
ntext=ntext+text[i 1]
else:
ntext=ntext+'&&'
print(ntext)

ii. The table Bookshop in MySQL contains the following attributes:

B_code - Integer
B_name - String
Qty - Integer
Price - Integer
Note the following to establish connectivity between Python and MySQL on a 'localhost' :
Username is 'shop'
Password is 'Book'
The table exists in a MySQL database named Bstore.
The code given below reads the records from the table Bookshop and displays all the records :
Statement 1 - to form the cursor object.
Statement 2 - to write the query to display all the records from the table.
Statement 3 - to read the complete result of the query into the object named B_Details, from the table Bookshop
in the database.

import mysql.connector as mysql

def Display_book():

mydb=mysql.connect(host="localhost",
user="shop",passwd="Book",database="Bstore")
mycursor=___________ # Statement 1
mycursor.execute("_________") # Statement 2
B_Details=__________ # Statement 3
for i in B_Details:

31 / 40
 print(i)

53. Give output for following SQL queries as per given table(s) : [4]
Table : Books

Book_Id Book_Name Author_Name Publishers Price Type Qty.

F0001 The Tears William Hopkins First publ. 750 Fiction 10

F0002 Thunderbolts : Anna Roberts i Fist Publ. 700 Fiction 5

T0001 My first C++ Brian and Brooke EPB 250 Text 10

T0002 C++ Brainworks A.W. Rossaine TDH 325 Text 5

C0001 Fast cook Lata Kapoor EPB 350 Cookery 8

Table : Issued

Book_Id Quantity_Issued

F0001 3

T0001 1

C0001 5

i. SELECT COUNT (DISTINCT Publishers) FROM Books.

ii. SELECT SUM(Price) FROM Books WHERE Quantity > 5.
iii. SELECT Book_Name, Author_Name FROM Books WHERE Price < 500.
iv. SELECT COUNT (*) FROM Books.
54. Given the following tables for a database LIBRARY [4]
TABLE: BOOKS

Book_Id Book_ Name Author_Name Publishers Price Type Qty

F0001 The Tears William Hopkins First Publ 750 Fiction 10

F0002 Thunder bolts Anna Roberts First Publ 700 Fiction 5

T0001 My First C++ Brain & Brooke EPB 250 Text 10

T0002 C++ Brainworks A.W.Rossaine TDH 325 Text 5

C0001 Fast Cook Lata Kapoor EPB 350 Cookery 8

TABLE: ISSUED

Book_Id Quantity_Issued

F0001 3

T0001 1

C0001 5

Write SQL queries for (i) to (v).

i. To show Book name, Author name and Price of books of EPB Publishers.
ii. To list the names from books of Fiction type.

32 / 40
 iii. To display the names and price of the books in descending order of their price.
iv. To increase the price of all books of First Publ Publishers by 50.
v. To display the detail of book whose quantity less than 10.
55. Give output for following SQL queries as per given table(s): [4]
Table : CLUB

COACH-ID COACHNAME AGE SPORTS DATEOFAPP PAY SEX

1. KUKREJA 35 KARATE 27/03/1996 1000 M

2. RAVINA 34 KARATE 20/01/1998 1200 F

3. KARAN 34 SQUASH 19/02/1998 2000 M

4. TARUN 33 BASKETBALL 01/01/1998 1500 M

5. ZUBIN 36 SWIMMING 12/01/1998 750 M

6. KETAKI 36 SWIMMING 24/02/1998 800 F

7. ANKITA 39 SQUASH 22/02/1998 2200 F

8. ZAREEN 37 KARATE 22/02/1998 1100 F

9. KUSH 41 SWIMMING 13/01/1998 900 M

10. SHAILYA 37 BASKETBALL 19/02/1998 1700 M

i. SELECT COUNT (DISTINCT SPORTS) FROM CLUB;

ii. SELECT MIN(AGE) FROM CLUB WHERE SEX = "F";
iii. SELECT AVG(PAY) FROM CLUB WHERE SPORTS = "KARATE";
iv. SELECT SUM(PAY) FROM CLUB WHERE DATEOFAPP > {31/01/98};
56. Consider the following tables CARDEN and CUSTOMER. Write SQL commands for the following statements. [4]
Table: CARDEN

Ccode CarName Make Color Capacity Charges

501 A-Star Suzuki RED 3 14

503 Indigo Tata SILVER 3 12

502 Innova Toyota WHITE 7 15

509 SX4 Suzuki SILVER 4 14

510 C Class Mercedes RED 4 35

Table: CUSTOMER

CCode Cname Ccode

1001 Hemant Sahu 501

1002 Raj Lai 509

1003 Feroza Shah 503

1004 Ketan Dhal 502

33 / 40
 i. To display the names of all the silver colored Cars.
ii. To display the name of the car, make and capacity of cars in descending order of their seating capacity.
iii. To display the highest charges at which a vehicle can be hired from CARDEN.
iv. To display the customer name and the corresponding name of the cars hired by them.
57. Consider the following tables SCHOOL and ADMIN and answer this question: [4]
TABLE: SCHOOL

CODE TEACHER SUBJECT DOJ PERIODS EXPERIENCE

1001 RAVI SHANKAR ENGLISH 12/3/2000 24 10

1009 PRIYA RAI PHYSICS 03/09/1998 26 12

1203 LIS ANAND ENGLISH 09/04/2000 27 5

1045 YASHRAJ MATHS 24/8/2000 24 15

1123 GANAN PHYSICS 16/7/1999 28 3

1167 HARISHB CHEMISTRY 19/10/1999 27 5

1215 UMESH PHYSICS 11/05/1998 22 16

TABLE: ADMIN

CODE GENDER DESIGNATION

1001 MALE VICE PRINCIPAL

1009 FEMALE COORDINATOR

1203 FEMALE COORDINATOR

1045 MALE HOD

1123 MALE SENIOR TEACHER

1167 MALE SENIOR TEACHER

1215 MALE HOD

Give the output of the following SQL queries:

i. Select Designation Count (*) From Admin Group By Designation Having Count (*) <2;
ii. SELECT max (EXPERIENCE) FROM SCHOOL;
iii. SELECT TEACHER FROM SCHOOL WHERE EXPERIENCE >12 ORDER BY TEACHER;
iv. SELECT COUNT (*), GENDER FROM ADMIN GROUP BY GENDER;
58. Consider the following tables CARDEN and CUSTOMER and answer the following parts of this question : [4]
Table: CARDEN

Ccode CarName Make Color Capacity Charges

501 A-Star Suzuki RED 3 14

503 Indigo Tata SILVER 3 12

502 Innova Toyota WHITE 7 15

509 SX4 Suzuki SILVER 4 14

34 / 40
 510 C Class Mercedes RED 4 35

Table: CUSTOMER

CCode Cname Ccode

1001 Hemant Sahu 501

1002 Raj Lai 509

1003 Feroza Shah 503

1004 Ketan Dhal 502

Give the output of the following SQL queries :

i. SELECT COUNT (DISTINCT Make) FROM CARDEN;
ii. SELECT MAX(Charges), MIN(Charges) FROM CARDEN;
iii. SELECT COUNT(*), Make FROM CARDEN;
iv. SELECT CarName FROM CARDEN WHERE Capadty = 4;
59. Consider the following tables EMPLOYEE and SALGRADE and answer the following parts of this question : [4]
Table : EMPLOYEE

ECODE NAME DESIG SGRADE DOJ DOB

101 Abdul Ahmad EXECUTIVE S03 23-Mar-2003 13-Jan-1980

102 Ravi Chander HEAD-IT S02 12-Feb-2010 22-Jul-1987

103 John Ken RECEPTIONIST S03 24-Jun-2009 24-Feb-1983

105 Nazar Ameen GM S02 11-Aug-2006 03-Mar-1984

108 Priyam Sen CEO SOI 29-Dec-2004 19-Jan-1982

Table : SALGRADE

SGRADE SALARY HRA

S0I 56000 18000

S02 32000 12000

S03 24000 8000

Give the output of the following SQL queries :

i. SELECT COUNT (SGRADE), SGRADE FROM EMPLOYEE GROUP BY SGRADE;
ii. SELECT MIN(DOB), MAX(DOJ) FROM EMPLOYEE;
iii. SELECT NAME, SALARY FROM EMPLOYEE E, SALGRADE S
WHERE E.SGRADE = S.SGRADE AND E.ECODE < 103 ;
iv. SELECT SGRADE, SALARY + HRA FROM SALGRADE WHERE SGRADE = 'S02';
60. Consider the following tables GARMENT and FABRIC. Write SQL commands for the statements (i) to (v). [4]
TABLE: GARMENT

GCODE DESCRIPTION PRICE FCODE READYDATE

10023 PENCIL SKIRT 1150 F03 19-DEC-08

10001 FORMAL SHIRT 1250 F01 12-JAN-08

35 / 40
 10012 INFORMAL SHIRT 1550 F02 06-JUN-08

10024 BABY TOP 750 F03 07-APR-07

10090 TULIP SKIRT 850 F02 31-MAR-07

10019 EVENING GOWN 850 F03 06-JUN-08

10009 INFORMAL PANT 1500 F02 20-OCT-08

10007 FORMAL PANT 1350 F01 09-MAR-08

10020 FROCK 850 F04 09-SEP-07

10089 SLACKS 750 F03 20-OCT-08

TABLE: FABRIC

FCODE TYPE

F04 POLYSTER

F02 COTTON

F03 SILK

F01 TERELENE

i. To display GCODE and DESCRIPTION of each GARMENT in descending order of GCODE.

ii. To display the details of all the GARMENTS, which have READYDATE in between 08-DEC-07 and 16-
JUN-08 (inclusive of both the dates).
iii. To display the average PRICE of all the GARMENTS. Which are made up of FABRIC with FCODE as F03.
iv. To display FABRIC wise highest and lowest price of GARMENTS from GARMENT table. (Display
FCODE of each GARMENT alongwith highest and lowest price.)
v. To display garment’s description with their price whose fabric is silk.
61. Write the SQL commands for (i) to (v) on the basis of the table HOSPITAL [4]
TABLE: HOSPITAL

No. Name Age Department Dateofadm Charges Sex

1 Sandeep 65 Surgery 23/02/98 300 M

2 Ravina 24 Orthopaedic 20/01/98 200 F

3 Karan 45 Orthopaedic 19/02/98 200 M

4 Tarun 12 Surgery 01/01/98 300 M

5 Zubin 36 ENT 12/01/98 250 M

6 Ketaki 16 ENT 24/02/98 300 F

7 Ankita 29 Cardiology 20/02/98 800 F

8 Zareen 45 Gynaecology 22/02/98 300 F

9 Kush 19 Cardiology 13/01/98 800 M

10 Shailya 31 Nuclear Medicine 19/02/98 400 M

36 / 40
 i. To show all information about the patients of Cardiology Department.
ii. To list the name of female patients, who are in Orthopaedic Department.
iii. To list names of all patients with their date of admission in ascending order.
iv. To display Patient’s Name, Charges, Age for male patients only.
v. To display name of doctor are older than 30 years and charges for consultation fee is more than 500.
62. Give output for following SQL queries as per given table(s): [4]
Table: HOSPITAL

No. Name Age Department Dateofadm Charges Sex

1. Arpit 62 Surgery 21/01/98 300 M

2. Zarina 22 ENT 12/12/97 250 F

3. Kareem 32 Orthopedic 19/02/98 200 M

4. Arun 12 Surgery 11/01/98 300 M

5. Zubin 30 ENT 12/01/98 250 M

6. Ketaki 16 ENT 24/02/98 250 F

7. Ankita 29 Cardiology 20/2/98 800 F

8. Zareen 45 Gynecology 22/02/98 300 F

9. Kush 19 Cardiology 13/01/98 800 M

10. Shilpa 23 Nuclear Medicine 21/02/98 400 F

i. SELECT COUNT (DISTINCT Charges) FROM HOSPITAL;

ii. SELECT MIN (Age) FROM HOSPITAL WHERE Sex = "F";
iii. SELECT SUM (Charges) FROM HOSPITAL WHERE Department = "ENT";
iv. SELECT AVG (Charges) FROM HOSPITAL WHERE Dateofadm< {12/08/98};
63. Consider the following table WORKER. Write SQL commands for the following statements. [4]
Table: WORKER

ECODE NAME DESIG PLEVEL DOJ DOB

11 Radhe Shyam Supervisor P001 13-Sep-2004 23-Aug-1981

12 Chander Nath Operator P003 22-Feb-2010 12-Jul-1987

13 Fizza Operator P003 14-Jun-2009 14-Oct-1983

15 Ameen Ahmed Mechanic P002 21-Aug-2006 13-Mar-1984

18 Sanya Clerk P002 19-Dec-2005 09-Jun-1983

i. To display the details of all WORKERS in descending order of DOB.

ii. To display NAME and DESIG of those WORKERS, whose PLEVEL is either P001 or P002.
iii. To display the content of all the WORKERS table, whose DOB is in between '19-JAN-1984' and ‘18-JAN-
1987’.
iv. To add a new row with the following :
19, 'Daya Kishore', 'Operator', 'P003', '19-Jun-2008', '11-Jul-1984'

37 / 40
64. Given the following family relation. Write SQL commands for questions (i) to (v) based on the table FAMILY [4]
TABLE: FAMILY

No. Name Female Members Male Members Income Occupation

1 Mishra 3 2 7000 Service

2 Gupta 4 1 50000 Business

3 Khan 6 3 8000 Mixed

4 Chaddha 2 2 25000 Business

5 Yadav 7 2 20000 Mixed

6 Joshi 3 2 14000 Service

7 Maurya 6 3 5000 Farming

8 Rao 5 2 10000 Service

i. To select all the information of family, whose Occupation is Service.

ii. To list the name of family, where female members are more than 3.
iii. To list all names of family with income in ascending order.
iv. To count the number of family, whose income is less than 10000.
v. To display the detail of family whose income is more than 10000 and occupation is mixed type.
65. Write SQL commands for (i) to (v) on the basis of table EMPLOYEE [4]
TABLE: EMPLOYEE

S NO NAME BASIC DEPARTMENT DATO FAPP AGE SEX

1 KARAN 8000 PERSONNEL 27/03/97 35 M

2 DIVAKAR 9500 COMPUTER 20/01/98 34 M

3 DIVYA 7300 ACCOUNTS 19/02/97 34 F

4 ARUN 8350 PERSONNEL 01/01/95 33 M

5 SABINA 9500 ACCOUNTS 12/01/96 36 F

6 JOHN 7400 FINANCE 24/02/97 36 M

7 ROBERT 8250 PERSONNEL 20/02/97 39 M

8 RUBINA 9450 MAINTENANCE 22/02/98 37 F

9 VIKAS 7500 COMPUTER 13/01/94 41 M

10 MOHAN 9300 MAINTENANCE 19/02/98 37 M

i. Which command will be used to list the names of the employees, who are more than 34 years old sorted by
NAME.
a. SELECT NAME FROM EMPLOYEE WHERE AGE>34 ORDER BY NAME;
b. SELECT * FROM EMPLOYEE WHERE AGE>34 ORDER BY NAME;
c. SELECT NAME FROM EMPLOYEE WHERE AGE>34;
d. SELECT NAME FROM EMPLOYEE AGE>34 ORDER BY NAME;

38 / 40
 ii. Write a query to display a report, listing NAME, BASIC, DEPARTMENT and annual salary. Annual salary
equals to BASIC * 12.
iii. Insert the following data in the EMPLOYEE table
11, 'VIJAY', 9300, 'FINANCE', '13/7/98', 35, "M"
iv. Write a query to count the number of employees, who are either working in PERSONNEL or COMPUTER
department.
v. Write the degree and cardinality of the table EMPLOYEE.
66. Write SQL commands for (i) to (v) on the basis of the table SPORTS [4]
TABLE: SPORTS

STUDENTNO CLASS NAME GAME1 GRADE GAME2 GRADE1

10 7 Sameer Cricket B Swimming A

11 8 Sujit Tennis A Skating C

12 7 Kamal Swimming B Football B

13 7 Veena Tennis C Tennis A

14 9 Archana Basketball A Cricket A

15 10 Arpit Cricket A Athletics C

i. Display the games taken up by the students, whose name starts with 'A'.
ii. Write a query to add a new column named MARKS.
iii. Write a query to assign a value 200 for Marks for all those, who are getting grade ‘B’ or grade 'A' in both
GAME1 and GAME2.
iv. Which command will be used to arrange the whole table in the alphabetical order of NAME?
a. SELECT FROM SPORTS ORDER BY NAME;
b. SELECT * SPORTS ORDER BY NAME;
c. SELECT * FROM SPORTS ORDER NAME;
d. SELECT * FROM SPORTS ORDER BY NAME;
v. Identify the attribute best suitable to be declared as a primary key.
67. Give output for following SQL queries as per given table(s) : [4]
relation Teacher

No. Name Age Department Date of join Salary Sex

1. Jugal 34 Computer 10/01/97 12000 M

2. Sharmila 31 History 24/03/98 20000 F

3. Sandeep 32 Maths 12/12/96 30000 M

4. Sangeeta 35 History 01/07/99 40000 F

5. Rakesh 42 Maths 05/09/97 25000 M

6. Shyam 50 History 27/06/98 30000 M

7. Shiv Om 44 Computer 25/02/97 21000 M

8. Shalakha 33 Maths 31/07/97 20000 F

39 / 40
 i. SELECT COUNT (distinct department) FROM TEACHER;
ii. SELECT MAX (Age) FROM TEACHER WHERE Sex = "F";
iii. SELECT AVG(Salary) FROM TEACHER WHERE Dateofjoin< {12/07/96};
iv. SELECT SUM (Salary) FROM TEACHER WHERE Dateofjoin < {12/07/96}
68. Consider the following table EMPLOYEE. Write SQL commands for the following statements. [4]
Table: EMPLOYEE

ECODE NAME DESIG SGRADE DOJ DOB

101 Abdul Ahmad EXECUTIVE S03 23-Mar-2003 13-Jan-1980

102 Ravi Chander HEAD-IT S02 12-Feb-2010 22-Jul-1987

103 John Ken RECEPTIONIST S03 24-Jun-2009 24-Feb-1983

105 Nazar Ameen GM S02 ll-Aug-2006 03-Mar-1984

108 Priyam Sen CEO SOI 29-Dec-2004 19-Jan-1982

i. To display the details of all EMPLOYEES in descending order of DOJ.

ii. To display NAME and DESIG of those EMPLOYEES, whose SALGRADE is either S02 or S03
iii. To display the content of all the EMPLOYEES table, whose DOJ is in between W-Feb-2006' and '08-Aug-
2009'.
iv. To add a new row with the following : 19, 'Harish Roy', 'HEAD-IT', 'S02', '09-Sep-2007', '21-Apr-1983'

40 / 40