Question

Write SQL commands for the queries (i) to (iv) and output for (v) to (viii) based on the tables ‘Watches’ and Sale given below.

Watches

Watchid	Watch_Name	Price	Type	Qty_Store
W001	HighTime	10000	Unisex	100
W002	LifeTime	15000	Ladies	150
W003	Wave	20000	Gents	200
W004	HighFashion	7000	Unisex	250
W005	GoldenTime	25000	Gents	100

Sale

Watchid	Qty_Sold	Quarter
W001	10	1
W003	5	1
W002	20	2
W003	10	2
W001	15	3
W002	20	3
W005	10	3
W003	15	4

(i) To display all the details of those watches whose name ends with ‘Time’

(ii) To display watch’s name and price of those watches which have price range in between 5000-15000.

(iii) To display total quantity in store of Unisex type watches.

(iv) To display watch name and their quantity sold in first quarter.

(v) select max(price), min(qty_store) from watches;

(vi) select quarter, sum(qty_sold) from sale group by quarter;

(vii) select watch_name,price,type from watches w, sale s where w.watchid!=s.watchid;

(viii) select watch_name, qty_store, sum(qty_sold), qty_storesum(qty_sold) “Stock” from watches w, sale s where w.watchid=s.watchid group by s.watchid;

Answer 1

(i) select * from watches where watch_name like ‘%Time’

(ii) select watch_name, price from watches where price between 5000 and 15000;

(iii) select sum(qty_store) from watches where type like ’Unisex’;

(iv) select watch_name,qty_sold from watches w,sale s where w.watchid=s.watchid and quarter=1;

(v) 

max(price)	min(qty_store)
25000	100

(vi) 

(vii)

watch_name	price	type
HighFashion	7000	Unisex

(viii)