SET 1

(a) (i) select IName,price from Item order by price;
    (ii) select max(price),min(price) from item group by IName;
    (iii) select IName,price ,Sname from item,store where item.sno=store.sno;
(b) (i) 
	Area		count(*)
	GK II		1
	CP		2
	Nehru Place	2
   
     (ii) 3
---------------------------------------------------------------------------------
SET 2

(i) select first_name,last_name,subject from teacher where subject='physics';
(ii) select * from teacher order by last_name;
(iii) select TID,first_name,sal+bonus 'Total Salary' from teacher,salary where teacher.tid=salary.tid;

(b) (i) first_name
         Rohit
	 Seema
	 Manish
	 Ram
    (ii) AVG(Sal)
 	   7833.33
           6000
---------------------------------------------------------------------------------
SET 3

(i) select name,salary from employee,salgrade where employee.sgrade=salgrade=sgrade order by DOJ desc;
(ii) select name,desig from employee where sgrade='s02' or sgrade='s03';
(iii) select * from employee where doj between '2006-02-09' and  '2009-08-08';

(b)
(i) name		salary
    Abdul Ahmad		24000
    Ravi Chander	32000

(ii) SGRADE		SALARY+HRA
      S02		  44000

------------------------------------------------------------------------------------
set 4

(i)alter table books add primary key (BID);
			or
alter table issued add foreign key references books (bid);

(ii) update books set price=price + 70 where type='computer';

(iii) select books.bid,bname,qty_issued from books, issued where books.bid=issued.bid;

(iv) select books.bid, bname, auname, qty_issued from books, issued where

books.bid-issued.bid and price>=200 and price<=400; 

					OR

select books.bid, bname, auname, qty_issued from books, issued where books.bid=issued.bid and price between 200 and 400;

---------------------------------------------------------------------------------

set 5

(i) select customerid, customername, orderamt from customer, order where customer.customerid = order.customerid and city='ahmedabad';

(ii) select * from order order by orderamt desc;

(iii) select orderid, orderdate, customername, mobileno from customer, order where customer.customerid = order.customerid and (customername like '%k' or customername like '%h%';

(iv) select city, sum(orderamt) from customer, order where customer.customerid = order.customerid group city;





--------------------------------------------------------------------------------

set 6

(i)alter table books add primary key (BID);

			OR

alter table issued add foreign key references books (bid);

(ii) update books set price=price + 70 where type='computer';

(iii) select books.bid,bname,qty_issued from books, issued where books.bid=issued.bid;

(iv) select books.bid, bname, auname, qty_issued from books, issued where

books.bid-issued.bid and price>=200 and price<=400; 

				OR

select books.bid, bname, auname, qty_issued from books, issued where books.bid=issued.bid and price between 200 and 400;

-------------------------------------------------------------------------------------