Chapter – 8 . STRUCTURED QUERY LANGUAGE

TABLE : CONSIGNOR

CnorID	CnorName	CnorAddress	City
ND01	R singhal	24,ABC Enclave	New Delhi
ND02	Amit Kumar	123,Palm Avenue	New Delhi
MU15	R Kohil	5/A,South,Street	Mumbai
MU50	S Kaur	27-K,Westend	Mumbai

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

Ans. Select CnorName from Consignor where city=”Mumbai”;

(ii) To display the cneeID, cnorName, cnorAddress, CneeName, CneeAddress for every Consignee.

Ans. Select CneeId, CnorName, CnorAddress, CneeName, CneeAddress from Consignor,Consignee where Consignor.CnorId=Consignee.CnorId;

(iii) To display the consignee details in ascending order of CneeName.

Ans. Select * from Consignee Orderby CneeName Asc;

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

Ans. Select city, count(*) from Consignors group by city;

(v) SELECT DISTINCT City FROM CONSIGNEE;

Ans. CneeCity
Mumbai
New Delhi
Kolkata

(vi) SELECT A.CnorName A, B.CneeName B FROM Consignor A, Consignee B WHERE A.CnorID=B.CnorID AND B.CneeCity=’Mumbai’;

Ans. CnorName
R singhal, Amit Kumar

CneeName
Rahul Kishore, S mittal

(vii). SELECT CneeName,CneeAddress FROM Consignee WHERE CneeCity Not IN (‘Mumbai’, ‘Kolkata’);

Ans.
CneeName
P Dhingra , B P jain

CneeAddress 16/j,Moore Enclave 13, Block d,a,viha.

(viii) SELECT CneeID, CneeName FROM Consignee WHERE CnorID = ‘MU15’ OR CnorID = ‘ND01’;

Ans.
CneeID - MU05 , KO19
CneeName: Rahul Kishore A P Roy