COMPUTER SCIENCE STRUCTURED QUERY LANGUAGE

Delhi 2007:

5.b) Consider the following tables. Write SQL command for the statements (i)to(iv)and give outputs for the SQL quries (v) to (viii). 6

TABLE : SENDER

SenderID SenderName Sender Address Sender City
ND01 R jain 2,ABC Appts New Delhi
MU02 H sinha 12, Newton Mumbai
MU1 5 S haj 27/ A,Park Street New Delhi
ND5 0 T Prasad 122-K,SDA Mumbai

TABLE :RECIPIENT

RecID SenderID ReCName RecAddress ReCCity
KO05 ND01 RBajpayee 5,Central Avenue Kolkata
ND08 MU0 2 S Mahajan 116, A Vihar NewDelhi
MU19 ND01 H sing 2A,Andheri East Mumbai
MU32 MU1 5 PK Swamy B5, CS erminus Mumbai
ND48 ND50 S Tripathi 13, B1 D,Mayur Vihar NewDelhi

(i) To display the names of all senders from Mumbai.

Ans: Select * from Sender where SenderCity =’Mumbai’;

(ii) To display the recID, senderName, senderAddress, RecName, RecAddress for every recipt.

Ans: Select recID, SenderName, SenderAddress, RecName, RecAddress from Sender, Recipient where Sender.Senderid=Recipient.RenderId;

(iii) To display the sender details in ascending order of SenderName.

Ans: Select * from Sender order by SenderName;

(iv) To display number of Recipients from each city.

Ans: Select RecCity,Count(*) from Recipient group by RecCity;

CnorName

R singhal

Amit Kumar

 

CneeName

Rahul Kishore

S mittal

 

CneeName

CneeAddress

P Dhingra

16/j,Moore Enclave

B P jain

13,Block d,a,viha

 

(v) SELECT DISTINCT SenderCity FROM Sender;

Ans:

 

CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( [email protected] )