queries.sql

ssd7数据库的联系一到练习三

1.
select libid,fname,join_date
from member4
where (libid like 'Ax%B%' escape 'x' or libid like 'A&B%')
  and join_date < to_date('1997/11/30', 'YYYY/MM/DD');

2.
SELECT f.name,f.isbn,f.callnumber      
FROM   (SELECT t.name,t.isbn,t.callnumber,count(o.supplier_id) as sucount,sum(p.qty) as qty
        FROM title4 t,ordered4 o,purchase_order4 p
        WHERE t.callnumber=o.callnumber and o.ponum=p.ponum 
        GROUP BY t.name,t.isbn,t.callnumber
        ORDER BY qty) as f
WHERE f.sucount>1 and f.qty>10;

3.
select m.libid,m.fname,sum(timesread)
from member4 m natural join read_by4
group by libid,fname
having sum(timesread) < (select 0.05*sum(timesread)
                         from read_by4)
order by sum(timesread);

4.
select m.fname,m.libid
from member4 m
where m.gender='F' 
and m.libid in (select libid
                from read_by4
                where timesread>1)
order by fname;

5.
SELECT a.name,a.avgqty1
FROM (SELECT s.supplier_id,s.name,avg(qty) as avgqty1
      FROM supplier4 s,ordered4 o,purchase_order4 p
      WHERE s.supplier_id=o.supplier_id and o.ponum=p.ponum
      GROUP BY s.supplier_id,s.name) as a,
     (SELECT avg(qty) as avgqty2
      FROM purchase_order4) as b
WHERE a.avgqty1>b.avgqty2;

6.
SELECT f.fname,f.libid
FROM (SELECT m.libid,m.fname,r.callnumber,r.timesread
       FROM member4 m,read_by4 r
       WHERE m.libid=r.libid and m.gender='M' and m.join_date<to_date('1995/10/10', 'YYYY/MM/DD')
      ) as f
group by f.fname,f.libid,f.timesread
having count(f.callnumber)<=5 and f.timesread<2
order by f.fname;