					More programs at www.pranavthegreat.cjb.net


 1 >    select * from catalog where publisher_id='&publisher_id';


 2 >    select * from order_details where book_id in (select book_id from catalog                  where publisher_id='&publisher_id') and qty>25;

 3 >     

 4 >

 5 >    select * from catalog c,author a where c.author_id=a.author_id and a.name like         'a%'; 

1a>select count(*)
from car
where regno not in(select regno from participated);

1b>create view acci_vu
as select report_no,location
from accident
where adate between '01-jan-2004' and '31-dec-2004';

select * from acci_vu;

1c>select sum(damage_amt) "total amount"
from participated p,accident a
where p.report_no = a.report_no
and adate like '%04';

1d>select location,count(location)
from accident
group by location
having count(location) = (select max(count(location)) from accident
group by location);

1e>select name,driver_id
from person 
where 2 <all (select count(*) from owns group by driver_id)
and 3 >all (select count(*) from participated group by driver_id)
group by driver_id,name;

2a> select o.orderno,odate,custno,ord_amt
 from orders o,shipment s
 where o.orderno = s.orderno
 and shipdate - odate >= 15;

2b>select c.custno,cname
from customer c,orders o
where c.custno=o.custno
group by c.custno,cname,odate
having count(o.custno)=(select max(count(m.custno)) from customer m group by m.custno)
and odate between '17-jun-2000' and '17-jun-2005';

VARIATION
=======
2B>

2c>select i.itemno
from item i,order_item o
where i.itemno = i.itemno
group by i.itemno
 having count(i.itemno) > 12;

2e>select w.warehouseno,w.city
from warehouse w,shipment s
where w.warehouseno = s.warehouseno
group by w.warehouseno,w.city
having count(w.warehouseno)=(select min(count(m.warehouseno)) from shipment m
group by m.warehouseno);

4e>
 select book_id,a.author_id
 from catalog c,author a
 where c.author_id = a.author_id
 and title like 'f%' or title like 'j%'
 and name like 'a%' or name like 'j%';

3B>SELECT S.REGNO,NAME
FROM STUDENT S,ENROLL E
WHERE S.REGNO = E.REGNO
AND COURSENO <> '&COURSE';

					More programs at www.pranavthegreat.cjb.net
