Question 5a.
1.
What
is alternate key? [2] (D-06)
2.
What
are DDL and DML? [2] (O-06)
3.
Differentiate
between primary and alternate key. [2](D-07)
4.
What
is the importance of a primary key in a table? Explain with a suitable
example. [2](O-07)
5.
Differentiate
between Candidate Key and Primary Key in context of RDBMS.
[2](D-08)
6.
Differentiate
between Candidate Key and Alternate Key in context of RDBMS.
[2](O-08)
7.
What
do you understand by candidate keys in a table? Give a suitable example of
candidate keys from a table containing some meaningful data. [2](D-10)
8.
What
do you understand by primary key in a table? Give a suitable example of
candidate key from a table containing some meaningful data. [2](D-10)
Question 5b.
1.
Study
the following tables DOCTOR and SALARY and write the SQL commands for the
questions(i) to (iv) and give outputs for SQL queries (v) and (vi): [6] (D-06)
TABLE:DOCTOR
|
ID
|
NAME
|
DEPT
|
SEX
|
EXPERIENCE
|
|
101
|
John
|
ENT
|
M
|
12
|
|
104
|
Smith
|
ORTHOPAEDIC
|
M
|
5
|
|
107
|
George
|
CARDIOLOGY
|
M
|
10
|
|
114
|
Lara
|
SKIN
|
F
|
3
|
|
109
|
K George
|
MEDICINE
|
F
|
9
|
|
105
|
Johnson
|
ORTHOPAEDIC
|
M
|
10
|
|
117
|
Lucy
|
ENT
|
F
|
3
|
|
111
|
Bill
|
MEDICINE
|
F
|
12
|
|
130
|
Morphy
|
ORTHOPAEDIC
|
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’ having more than 10 years experience
(ii)
Display
the average salary of all doctors who work 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
2.
Study
the following tables FLIGHTS and FARES and write the SQL commands for the
questions(i) to (iv) and give outputs for SQL queries (v) and (vi): [6] (O-06)
TABLE:FLIGHTS
|
FL_NO
|
STARTING
|
ENDING
|
NO_FLIGHTS
|
NO_STOPS
|
|
IC301
|
MUMBAI
|
DELHI
|
8
|
0
|
|
IC799
|
BANGLORE
|
DELHI
|
2
|
1
|
|
MC101
|
INDORE
|
MUMBAI
|
3
|
0
|
|
IC302
|
DELHI
|
MUMBAI
|
8
|
0
|
|
AM812
|
KANPUR
|
BANGLORE
|
3
|
1
|
|
IC899
|
MUMBAI
|
KOCHI
|
1
|
4
|
|
AM501
|
DELHI
|
TRIVANDRUM
|
1
|
5
|
|
MU499
|
MUMBAI
|
CHENNAI
|
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_FLIGHTS from ‘KANPUR’ TO ‘BANGLORE’ 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 fare to be paid =FARE+FARE*TAX%100
(iv)
Display
the minimum fare ‘Indian Airlines’ is offering from the table FARES.
(v)
Select
FL_NO, NO_FLIGHTS, AIRLINES from FLIGHTS, FARES where STARTING=’DELHI’ AND
FLIGHT.FL_NO=FARES.FL_NO
(vi)
Select
count(distinct(ENDING)) from FLIGHTS
3.
Consider
the following tables: SENDER and RECIPIENT. Write the SQL commands for the
questions(i) to (iv) and give outputs for SQL queries (v) and (viii): [6] (D-07)
TABLE:SENDER
|
SenderID
|
SenderName
|
SenderAddress
|
SenderCity
|
|
ND01
|
R Jain
|
2, ABC Aprts
|
New Delhi
|
|
MU02
|
H Sinha
|
12, Newtown
|
Mumbai
|
|
MU15
|
S Jha
|
27/A, Park Street
|
New Delhi
|
|
ND50
|
T Prasad
|
122-K, SDA
|
Mumbai
|
TABLE:RECIPIENT
|
RecID
|
SenderID
|
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
|
|
ND48
|
ND50
|
S Tripathi
|
13, B1 D, Mayur Vihar
|
New Delhi
|
(i)
To
display the name of all Senders from Mumbai
(ii)
To
display the RecID, SenderName, SenderAddress, RecName, RecAddress for every
Recipient.
(iii)
To
display recipient details in the ascending order of RecName
(iv)
To
display number of recipients from each city.
(v)
Select
DISTINCT SenderCity from Sender;
(vi)
Select
A.SenderName, B.RecName from Sender A, Recipient B where A.SenderID=B.SenderID
and B.RecCity=’Mumbai’;
(vii)
Select
RecName, RecAddress from Recipient where RecCity NOT IN(‘Mumbai’,’Kolkata’);
(viii)
Select
RecID, RecName from Recipient where SenderID=’MU02’ or SenderID=’ND50’
4.
Consider
the following tables CONSIGNOR and CONSIGNEE. Write the SQL commands for the
questions(i) to (iv) and give outputs for SQL queries (v) and (viii): [6] (O-07)
TABLE:CONSIGNOR
|
CNorID
|
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
|
CNeeID
|
CNorID
|
CNeeName
|
CNeeAddress
|
CNeeCity
|
|
MU05
|
ND01
|
Rahul Kishore
|
5, Park Avenue
|
Mumbai
|
|
ND08
|
ND02
|
P Dhingra
|
16/J, Moore Enclave
|
New Delhi
|
|
KO19
|
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)
To
display the names of all consignors from Mumbai
(ii)
To
display the CNeeID, CNorName, CNorAddress, CNeeName, CNeeAddress for every
Consignee.
(iii)
To
display consignee details in ascending order of CNeeName
(iv)
To
display number of consignes from each city
(v)
Select
distinct City from Consignor
(vi)
Select
A.CnorName, B.CneeName from Consigner A, Consignee B where A.CNorID=B.CNorID
and B.CNeeCity=’Mumbai’;
(vii)
Select
CNeeName, CNeeAddress from Consignee where CNeeCity NOT IN(‘Mumbai’,’Kolkata’);
(viii)
Select
CNeeID, CNeeName from Consignee where
CNorID=’MU15’ or CNorID=’ND01’
5.
Consider
the following tables: PRODUCT and CLIENT. Write the SQL commands for the
questions(i) to (iv) and give outputs for SQL queries (v) and (viii): [6] (D-08)
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-100(both
values included)
(iii)
To
display the client name, city, product name and price with their corresponding
matching P_ID
(iv)
To
increase the price of all products by 10
(v)
Select
distinct address from Client
(vi)
Select
manufacture, max(price), min(price), count(*) from product group by
manufacturer
(vii)
Select
clientName, ManufactureName from product, client where client.p_id=
product.p_id
(viii)
Select
product name, price *4 from product
6.
Consider
the following tables: ITEM and CUSTOMER. Write the SQL commands for the
questions(i) to (iv) and give outputs for SQL queries (v) and (viii): [6] (O-08)
0 comments:
Post a Comment