SQL PRACTICE - 1 (SELF PRACTICE)

1.     Answer the questions (a) and (b) on the basis of the following tables SHOPPE and ACCESSORIES.

TABLE:SHOPPE

Id	SName	Area
S001	ABC computeronics	CP
S002	All Infotech Media	GK II
S003	Tech Shape	CP
S004	Geeks Tecno Soft	Nehru Place
S005	Hitech Tech Store	Nehru Place

TABLE:ACCESSORIES

Id	Name	Price	Id
A01	Mother Board	12000	S01
A02	Hard Disk	5000	S01
A03	Keyboard	500	S02
A04	Mouse	300	S01
A05	Mother Board	13000	S02
A06	Keyboard	400	S03
A07	LCD	6000	S04
T08	LCD	5500	S05
T09	Mouse	350	S05
T10	Hard Disk	4500	S03

(a)         Write the SQL queries:

(i)    To display Name and Price of all the Accessories in ascending order of their Price.

(ii)  To display Id and SName of all Shoppe located in Nehru Place.

(iii)          To display Minimum and Maximum Price of each Name of Accessories.

(iv)  To display Name, Price of all Accessories and their respective SName, where they are available.

(b)         Write the output of the following SQL commands:

(i)    SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE>=5000;

(ii)  SELECT AREA, COUNT(*) FROM SHOPPE GROUP BY AREA;

(iii)          SELECT COUNT (DISTINCT AREA) FROM SHOPPE;

(iv)  SELECT NAME, PRICE*0.05 DISCOUNT FROM ACCESSORIES WHERE SNO IN (‘S02’,’S03’);