Class 12: SQL Questions 4

Question:

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

 

Queries

Q.1.
To display the names of all the consignors from Mumbai

Ans:

Select cnorname From consignor Where city=’Mumbai’;

Q.2.
To display the cneeid, cnorname,cnoradress, cneename, cneeaddress for every consignee

Ans:

Select cneeid,cnoraddress,cneename,cneeaddess
From consignor, consignee
Where consignor.cnorid=consignee.cnorid;

Q.3.
To display consignee details in ascending order of cneename.

Ans:

Select * From consignee Order by cneename;

Q.4.
To display number of considnors from each city.

Ans:

Select city,count(city) From consignor Group by cneecity

Q.5.
To display number of consignee from each city.

Ans:

Select cneecity,count(cneecity) From consignee Group by cneecity;

Q.6.
Select distinct city from consignee;
Correct query
Select distinct cneecity from consignee;

Ans:

Mumbai
New delhi
Kolkata

Q.7.
Select distinct city from consignor;

Ans:

Mumbai
New Delhi

Q.8.
Select a.cnorname,b.cneename from consignor A, consignee B
Where A.cnorid=B.cnorid and b.cneecity=’Mimbai’;

Ans:

R.Singhal Rahul kishore
Amit Kumar S. Mittal

Q.9.
Select cneename , cneeaddress
from consignee
where cneecity not in(‘Mumbai’,’Kolkata’);

Ans:

P Dhingra 16/J Morre Enclave
BP jain 13, Block D, A Vihar

Q.10.
Select cneeid,cneename From consignee Where cnorid=’MU15’ or cnorid=’ND01’;

Ans:

MU05 Rahul Kishore
K019 A P Roy