Page 134 - C++

105 GPS SYSTEM S03 60 12000 SONY
Table : SUPPLIERS
SUPCODE SNAME CITY

S01 PERFECT COMPUTER SYSTEMS KOLKATA

S02 RAGHAV ENTERPRISES DELHI

S03 BIG VIEW CHENNAI

(b1) Write SQL commands for the following statements: 4 x1=4
(i) To display details of all the products in ascending order of product

name(pname).

Ans: select * from products order by pname;
(ii) To display product name and price of those products whose price is in

the range of 10000 and 15000.

Ans: select pname, price from products where price between 10000 and 15000;
(iii) To display the number of products which are supplied by each supplier
i.e, the expected output should be :

S01 2

S02 2
S03 1

Ans: select supcode, count(*) from products group by supcode;

(iv) To display product name and quantity of those products which have
quantity more than 100.

Ans: select pname, qty from products where qty >100;
(b2) Give the output of the following SQL queries: 4x ½ =2

i) SELECT DISTINCT SUPCODE FROM PRODUCTS;
Ans: DISTINCT(SUPCODE)

S01
S02
S03

ii) SELECT MAX(PRICE), MIN(PRICE) FROM PRODUCTS;
Ans: MAX(PRICE) MIN(PRICE)

28000 1100

XII / Comp. Sc. Page 13 of 17