DAV PUBLIC SCHOOL,POKHARIPUT-20

CHAPTER – “DATABASE CONCEPT AND SQL”

STD-XII
WORKSHEET

Answer the following questions.

Q1.(a) Consider the following tables Client and Bill. Write SQL commands for the statements (i)
to (iv) and give outputs for SQL queries (v) to (viii)
TABLE : CLIENT
Cust_I Cust_Name Address Phone_no City
d
C007 Pritam Sharma 12,M.G Road 71274250 Bangalore
C008 Sutopa 14/1 PritamPura 41206819 Delhi
C010 AnuragBasu 15A, Park Road 61281921 Kolkata
C012 Hrithik 7/2 VasantKunj 26121949 Delhi
C013 Firoz Shah 2, Servamali road 25014192 Bangalore
C025 VinodNagpal 46-a Navi Mumbai 64104944 Mumbai
C027 Sameer 41,Dwarka 42101619 Delhi
C002 Pasunjit Bose 16/A K.G Marg 27220012 Bangalore
C035 Aamina Begum 13/A Versova 41612181 Mumbai
TABLE :BILL

Ord_i Cust_id Item Ord_dat Qty Price

d e
7002 C007 Pizza 20-11-07 1 249.50
7003 C013 Garlic Bread 24-10-05 3 75.75
7004 C012 Pasta 03-03-07 4 173.00
7005 C010 Ice Cream 01-01-08 30 195.75
7006 C035 Pizza 02-03-06 4 249.50
7009 C035 Garlic Bread 02-03-08 2 75.75
7010 C013 Brownie 04-05-07 4 40.50
7011 C014 Ice Cream 02-06-08 5 195.75
7012 C002 Pizza 01-02-08 7 249.50
(i) Display a report containing cust_id, cust_name, Item, qty, price and bill amount.
Bill
amount is calculated as the sum of qty*price.
(ii) Display how many customers have ordered Pizza in the month of March.
iii) Count the number of customer who have ordered item worth more than 1700.
Total amount = sum of qty* price
iv)Display the names of customer along with their city in alphabetical order of city
v)selectCust_name , City, ord_date from Client A, Bill b Where A.Cust_id =b.Cust_id;
vi) selectCust_name from Client where Cust_id=( Select Cust_id from Bill where Year(ord_date)
=2008)
vii)select city, count(*) from Client group by City;
Viii)select Distinct (Item) from Bill;

Q2 a)Study the following tables DOCTOR and SALARY and write SQL commands for
the questions (i) to (iv) and give outputs for SQL queries (v) to (viii). 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
 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 2000
105 18900 1690 300
130 21700 2600 300
(i) Display NAME of all doctors who are in “MEDICINE” having more than 10
years of experience for 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) SELECT COUNT(*) FROM DOCTOR WHERE SEX=”F”;
(vi) SELECT NAME,DEPT,BASIC FROM DOCTOR,SALARY
WHERE DEPT=”ENT” AND DOCTOR.ID=SALARY.ID;
(vii) SELECT DISTINCT DEPT from DOCTOR;
(viii) SELECT DEPT,COUNT(DEPT) FROM DOCTOR
GROUP BY DEPT
HAVING EXPERIENCE>5;

Q3.A) Differentiate between ‘where’ and ‘having’ clause (with example).

B) Consider the following tables SENDER and RECEIVER. Write SQL commands for
the statements (i) to (iv) and give outputs for SQL queries (v) to (viii).
TABLE : SENDER
SenderlD SenderName SenderAddress City
ND01 R Singhal 24, ABC New Delhi
Enclave
ND02 Amit Kumar 123, Palm New Delhi
Avenue
MU15 R Kohli 5/A, South Mumbai
Street
MU50 S Kaur 27-K, Westend Mumbai
TABLE : RECEIVER

Receiver SenderI ReceiverNa ReceiverAddress ReceiverCi

ID D me ty
MU05 ND01 Rahul Kishore 5, Park Avenue Mumbai
ND08 ND02 P Dhingra 16/J, Moore New Delhi
Enclave
KO19 MU15 A P Roy 2A , Central Kolkata
Avenue
MU32 ND02 S Mittal P 245, AB Colony Mumbai
ND48 MU50 B P Jain 13, Block D A Vihar New Delhi
(i) To display the names of all the Senders from Delhi.
(ii) To display the ReceiverID, SenderName, SenderAddress, ReceiverName,
ReceiverAddress for every Receiver.
(iii) To display Receiver details in ascending order of ReceiverName.
(iv) To display number of Senders from each city.
(v) SELECT DISTINCT ReceiverCity FROM RECEIVER;
(vi) SELECT A.SenderName, B.ReceiverName
FROM Sender A, Receiver B
 WHERE A.SenderID=B.SenderID AND B.ReceiverCity =‘Mumbai’;
(vii) SELECT ReceiverName, ReceiverAddress
FROM Receiver
WHERE ReceiverCity NOT IN (‘Mumbai’, ‘Kolkata’);
(viii) SELECT ReceiverID, ReceiverName
FROM Receiver
WHERE SenderID IN (’MU15’,’ND01’);

Q4.a) Write the advantages of Database. Explain it.

b) Consider the following table GAMES and PLAYER. Write SQL commands for the statements
(i) to (iv) and give outputs for SQL queries (v) to (viii).
Table : GAMES
GCO GAMENAME NUMBER PRZMONE SCHDATE
DE Y
101 Chess 5 25000 23 Jan 2010
102 Badminton 3 38000 12 Nov
2008
103 Carrom 6 18000 18 Mar
2010
105 Table Tennis 3 30000 09 Jan 2009
108 Basketball 5 40000 29 Apr 2009

Table : PLAYER
PCODE NAME GCODE
1 RakeshSrivastava 101
2 Nilesh Mishra 102
3 Vandana 108
4 Ravi Jindal 105
(i) To display the details of those games which are having prize money less than 30000 and organized
before 2009.

(ii) To display the name of PLAYERS in reverse alphabetical order.

(iii) To increase the prize money by 1000 for those games which name starts with ‘B’.

(iv) Insert an additional attribute namely DOB for entering date of birth in table PLAYER.

(v) SELECT GAMENAME,NAME FROM GAMES G,PLAYER P WHERE G.GCODE=P.GCODE;

(vi) SELECT MIN(SCHDATE), MAX(PRZMONEY) FROM GAMES ;

(vii) SELECT AVG(PRZMONEY) FROM GAMES WHERE SCHDATE<’01-JAN-2009’;

(viii) SELECT COUNT(DISTINCT NUMBER) FROM GAMES;

Q5.(a) Explain Primary key and Foreign Key, Candidate key, Alternate key, in context of RDBMS.
Give Suitable example.
For Ex:-
ANS:PRIMARY KEY : It is a set of one or more attributes that can uniquely identify tuples within the
relation.
ALTERNATE KEY : A candidate key that is not the primary key is known as an alternate key.
For ex. Relation: Data
EmpNo
Name
Designation
MobileNo
PANCardNo
Salary
BankAccountNo
Here in above table EmpNo, MobileNo, PANCardNo&BankAccountNo are candidate keys.
If EmpNo is made the primary key then remaining will automatically become alternate keys.
Explain the DDL and DML
Explain the table, cardinality, tuple, domain, relation, DBA, End user, Relational Data model
 (b)
Consider the following table DRESS and MATERIAL. Write SQL commands for the
statements (i) to (iv) and give outputs for SQL quarries (v) to (viii).
Table: DRESS
DCODE DESCRIPTION PRICE MCODE LAUNCHDATE
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
10090 PENCIL, SKIRT 1250 M003 19-DEC-08
10023 SLACKS 850 M003 20-OCT-08
10089 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) 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 material wise highest and lowest price of dresses from DRESS table.
(Display MCODE of each dress along with highest and lowest price).
(v) SELECT SUM( PRICE) FROM DRESS WHERE MCODE= ‘M001’;
(vi) SELECT DESCRIPTION , TYPE FROM DRESS MATERIAL WHERE
DRESS.DCODE>=1250;
(vii) SELECT MAX(MCODE) FROM MATERIAL ;
SELECT COUNT (DISTINCT PRICE) FROM DRESS. State and prove the Demorgan’s Law
with TT and Algebraically?
Q6. a) What do you understand by Selection and Projection operation in relational algebra .Write
with suitable example..
(b) What is a relation? What is the difference between a tuple and an attribute? 2
(c) Consider the following tables EMPLOYEEand DESIG. Write SQL commands for the statements
(i) to (iv) and give outputs for SQL queries (v) to (viii) EMPLOYEE
W_ID FIRSTNAME LASTNAME CITY
102 SAM TONES PARIS
105 SARAH ACKERMAN NEW YORK
144 MANILA SENGUPTA NEW DELHI
210 GEORGE SMITH HOWARD
255 MARY JONES HUSTON
300 ROBERT SAMUEL WASHINGTON
335 HENRY WILLIAMS BOSTON
400 RONNY LEE NEW YORK
 451 PAT THOMPSON PARIS
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). Display FirstName and City of Employee having salary between 50,000 and 90,000
ii). Display details of Employees who are from “PARIS” city.
iii). Increase the benefits of employee having W_ID = 210 by 500.
iv). Count number of employees whose name starts from character ‘S’.
v). Select MAX(salary) from desig;
vi). Select FirstName from employee, design where designation = ‘MANAGER’ AND
employee.W_ID = desig.W_ID;
vii). Select COUNT (DISTINCT designation) from desig;
viii). Select designation, SUM(salary) from desig
Group by designation
Having count (*) > 2;
Q7. Consider the following tables ACTIVITY and COACH. Write SQL commands for the
statements (i) to (iv) and give outputs for SQL queries (v) to (viii)
Table: ACTIVITY
ACode ActivityName ParticipantsNu PrizeMoney ScheduleDate
m
1001 Relay 100x4 16 10000 23-Jan-2004
1002 High jump 10 12000 12-Dec-2003
1003 Shot Put 12 8000 14-Feb-2004
1005 Long Jump 12 9000 01-Jan-2004
1008 Discuss Throw 10 15000 19-Mar-2004

Table: COACH
PCode Name ACode
1 Ahmad Hussain 1001
2 Ravinder 1008
3 Janila 1001
4 Naaz 1003

(i) To display the name of all activities with their Acodes in descending order.
(ii) To display sum of PrizeMoney for each of the Number of participants groupings (as shown
in column ParticipantsNum 10,12,16)
(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 whoseScheduleDate earlier than 01/01/2004
in ascending order of ParticipantsNum.

v)SELECT COUNT(DISTINCT ParticipantsNum) FROM ACTIVITY;

vi)SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM ACTIVITY;
 (vii) SELECT SUM(PrizeMoney) FROM ACTIVITY;
(viii) SELECT DISTINCT ParticipantsNum FROM ACTIVITY;
EXAMPLE :-

Ans: SELECT ActivityName, ACode FROM ACTIVITY ORDER BY Acode DESC;

Ans SELECT SUM(PrizeMoney),ParticipantsNum FROM ACTIVITY GROUP BY ParticipantsNum;
Ans SELECT Name, ACode FROM COACH ORDER BY ACode;
Ans: SELECT * FROM ACTIVITY WHERE ScheduleDate<’01-Jan-2004’ ORDER BY
ParticipantsNum;
Answer: 16
10
12
Answer:54000
Answer:19-Mar-2004 12-Dec-2003

Q8.A) Consider the following DEPT and WORKER tables. Write SQL queries for (i) to (iv) and find
outputs for SQL queries (v) to (viii) :
Table : DEPT

DCODE DEPARTMENT CITY

D01 MEDIA DELHI
D02 MARKETING DELHI
D03 INFRASTRUCTURE MUMBAI
D05 FINANCE KOLKATA
D04 HUMAN RESOURCE MUMBAI

Table : WORKER

WNO NAME DOJ DOB GENDER DCODE

1001 George K 2013-09-02 1991-09-01 MALE D01
1002 RymaSen 2012-12-11 1990-12-15 FEMALE D03
1003 Mohitesh 2013-02-03 1987-09-04 MALE D05
1007 Anil Jha 2014-01-17 1984-10-19 MALE D04
1004 Manila Sahai 2012-12-09 1986-11-14 FEMALE D01
1005 R SAHAY 2013-11-18 1987-03-31 MALE D02
1006 Jaya Priya 2014-06-09 1985-06-23 FEMALE D05

Note : DOJ refers to date of joining and DOB refers to date of Birth of workers.
(i) To display Wno, Name, Gender from the table WORKER in descending order of Wno.
(ii) To display the Name of all the FEMALE workers from the table WORKER.
(iii) To display the Wno and Name of those workers from the table WORKER who are born between
‘1987-01-01’ and ‘1991-12-01’.
(iv) To count and display MALE workers who have joined after ‘1986-01-01’.
(v) SELECT COUNT(*), DCODE FROM WORKER GROUP BY DCODE HAVING COUNT(*)>1;
(vi) SELECT DISTINCT DEPARTMENT FROM DEPT;
(vii) SELECT NAME, DEPARTMENT, CITY FROM WORKER W,DEPT D WHERE
W.DCODE=D.DCODE AND WNO<1003;
(viii) SELECT MAX(DOJ), MIN(DOB) FROM WORKER;
B) What do you understand by Cartesian Product in relational algebra .Write with a suitable
example.C) How many types of users work on database system? 2
D) Write different levels of Database Implementation?
Q9. a)Define the following term [1x3]=3
i. Logical data Independence and Physical data Independence
ii. View
iii. Domain
b) Consider the following tablesBooksandIssued. Write SQL commands for the following statements.
TABLE -Books
Book_I Book_Name Author_Name Publisher Price Type Qty
d
F0001 The Tears William First Publ 750 Fiction 10
Hopkins
 F0002 Thunderbolts Anna Roberts First Publ 700 Fiction 5
T0001 My First C++ Brain and EPB 250 Text 10
Brooke
T0002 C++ A.W. Rossaire TDH 325 Text 5
Brainworks
C0001 Fast cook LataKappor EPB 350 Cookery 8

TABLE -Issued
Book_Id Quantity Issued
F0001 3
T0001 1
C0001 5

i. To display the names and price of the books in descending order of their price.
ii. To increase the price of all books of first publishers by 50
iii. To display the Book_id, Book_Name and quantity_ issued for all books which have
been issued.
iv. Select sum(price) from Books where Quantity >5;
v. Select Publishers, sum(Qty) from Books group by publishers having count (*)>1;

Q10.Consider the following tables TEACHER and TEACHSALARY and answer (b1) and (b2) parts of this
question. Table: TEACHER
TID FIRSTNAME LASTNAME ADDRESS SUBJECT
010 Rohit Sharma 83 Lokvihar English
105 Meena Rathi 842 Rajouri Garden Physics
152 Seema Verma 33 Safdarjung Maths
215 Sarad Singh 440 Ashok Vihar Physics
144 Manish Sengupta 24 New Street Maths
300 Ram Gupta 9 Fifth Road Chemistry
335 Heena Jain 12 Friends Street Computer
400 Rachit Sharma 12 PachimVihar Computer
441 Puneet Jain 11 Roshni Chemistry

Table: TEACHSALARY
TID SALARY BONUS DESIGNATION
010 7500 1500 PGT
105 8500 1500 PGT
152 600 1200 TGT
215 7500 1500 TGT
144 5000 1000 PRT
300 4500 1000 PRT
335 4000 1000 PRT
400 6500 1200 TGT
441 7800 1500 PGT

(b1) Write SQL commands for the statements (i) to (iv).

i. To display Firstname, Lastname and Subject of all teachers having subject Physics.
ii. To display the content of Teachers table in ascending order of LASTNAME.
iii. To display the TID, Firstname and Total Salary of all PGT from table TEACHER and
TEACHSALARY, where Total Salary is calculated as Salary + Bonus.
iv. To display the sum of salary of all the PRT Teachers.

(b2) Give the output of the following SQL queries v to viii:

v. SELECT FIRSTNAME, SALARY FROM TEACHER, TEACHSALARY WHERE
DESIGNATION =”PGT” AND TEAHER.TID=TEACHSALARY.TID;
 vi. SELECT DISTINCT DESIGNATION FROM TEACHSALRY;
vii. SELECT DESIGNATION, MAX(SALRY) FROM TEACSALRY GROUP BY DESIGNATION;
viii.SELECT SUM(BONUS) FROM TEACHSALRY WHERE DESIGNATION=”PGT”;

************************************************************************************