SQL PRACTICE - 2 (SELF PRACTICE)

      1.     Write SQL queries for (a) to (f) and write the output for the SQL queries mentioned in (g) parts (i) to (iv) on the basis of tables ITEMS and TRADERS.

TABLE: ITEMS

code	IName	Qty	Price	Company	TCode
1001	DIGITAQL PAD 121	120	11000	XENITA	T01
1006	LED SCREEN 40	70	38000	SANTORA	T02
1004	CAR GPS SYSTEM	50	2150	GEOKNOW	T01
1003	DIGITAL CAMERA 12X	160	8000	DIGICLICK	T02
1005	PEN DRIVE 32 GB	600	1200	STOREHOME	T03

                             TABLE: TRADERS

TCode	TName	City
T01	ELECTRONIC SALES	MUMBAI
T03	BUSY STORE CORP	DELHI
T02	DISP HOUSE INC	CHENNAI

(a)    To display the details of all the item in ascending order of item names (i.e INAME).

(b)    To display item name and price of all those items, whose price is in the range of 10000 and 22000 (both values inclusive).

(c)    To display the number of items, which are traded by each trader. The expected output of this query should be:

T01       2

T03       1

T02       2

(d)    To display the Price, item name (i.e. IName) and quantity (i.e. Qty) of those items, which have quantity more than 150.

(e)    To display the names of those traders, who are either from DELHI or from MUMBAI.

(f)    To display the name of the companies and the name of the items in descending order of company names.

(g)    Obtain the output of the following SQL queries based on the data given in tables ITEMS and TRADERS names.

                  (i)     SELECT MAX(Price), MIN (Price) FROM ITEMS;

                (ii)     SELECT Price * Qty AMOUNT

FROM ITEMS WHERE Code=1004;

              (iii)     SELECT DISINCT TCode FROM ITEMS;

                (iv)     SELECT IName TName

FROM ITEMS I, TRADERS T

WHERE I. TCode = T.TCode AND Qty < 100;