Chapter – 8 . 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	SenderAddress	senderCity
ND01 R	jain 2	ABC Appt,s	New Delhi
MU02 H	sinha 12	Newton	Mumbai
MU15	S haj 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 sing	2A,Andheri East	Mumbai
MU32	MU15	P K swamy	B5, CS Terminus	Mumbai
ND48	ND50	S Tripathi	13, B1 D,Mayur Vihar	New Delhi

(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;

(v) SELECT DISTINCT SenderCity FROM Sender;

Ans. senderCity New Delhi Mumbai

(vi) SELECT A.SenderName A, B.RecName FROM Sender A, Recipient B WHERE A.SenderID=B. SenderID AND B.RecCity=’Mumbai’;

Ans. SenderName RecName R.Jain H.Singh S.Jha P.K.Swamy

(vii) SELECT RecName,RecAddress FROM Recipient WHERE RecCity Not IN (‘Mumbai’, Kolkata’);

Ans.

RecName	RecAddress
S Mahajan	116, A Vihar
S Tripati	13, B1 D, Mayur Vihar

(viii) SELECT RecID, RecName FROM Recipient WHERE SenderID = ‘MU02’ OR SenderID = ‘ND50’;

Ans.

RecID	RecName
ND08	S Mahajan
ND48	S Tripathi