Outside Delhi 2007:
5.b) Consider the following tables Consignor and Consignee. Write SQL command for the statements(i)to(iv) And give outputs for the SQL quries (v) to ( viii). 6
TABLE : CONSIGNOR
| CnorID | CnorName | CnorAddress | City |
|---|---|---|---|
| ND01 | R singhal | 24,ABC Enclave | New Delhi |
| ND02 | AmitKumar | 123,Palm Avenue | New Delhi |
| MU15 | R Kohil | 5/A,South,Street | Mumbai |
| MU50 | S Kaur | 7-K,Westend | Mumbai |
TABLE : CONSIGNEE
| CneeID | CnorID | CneeName | CneeAddress | CneeCity |
|---|---|---|---|---|
| MU05 | ND01 | RahulKishore | 5,Park Avenue | Mumbai |
| ND08 | ND02 | P Dhingr a | 16/j,Moore Enclave | New Delhi |
| KO19 | MU15 | A P Roy | 2A,Central/ avenue | Kolkata |
| MU32 | ND0 2 | S mittal | P 245, AB Colony | Mumbai |
| ND48 | MU5 0 | B P jain | 13,Block d,a,viha | New Delhi |
(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’;
(vii) SELECT CneeName,CneeAddress FROM Consignee WHERE CneeCity Not IN (‘Mumbai’, ‘Kolkata’);
Ans:
(viii) SELECT CneeID, CneeName FROM Consignee WHERE CnorID = ‘MU15’ OR CnorID = ‘ND01’;
| senderCity |
|---|
| New Delhi |
| Mumbai |
Ans: CneeID
CneeName
MU05
Rahul Kishore
KO19 A P Roy
CBSE Computer Science Solved Revision Tour By Mr. Ravi Kiran ( [email protected] )
