queries.sql

来自「卡耐基梅隆大学数据库教程ssd7」· SQL 代码 · 共 81 行

TITLE4(Name, ISBN, CallNumber, Year, AuthorFName, AuthorLName);
PK(CallNumber)

MEMBER4(LibId, Fname, Lname, Address, PhoneNumber,Join_date,Gender);
PK(LibId)

BOOK4(Book_Id, Edition, Status, BorrowerId, BorrowerDueDate, CallNumber);
PK(Book_Id)
FK(BorrowerId) -> MEMBER4(LibId)
FK(CallNumber) -> TITLE4(CallNumber)

PURCHASE_ORDER4(PoNum, Qty, OrderDate, DueDate, ReceivedDate);
PK(PoNum)

SUPPLIER4(Supplier_Id, Name, Address);
PK(Supplier_Id)

READ_BY4(CallNumber, LibId, TimesRead);
PK(CallNumber, LibId)
FK(CallNumber) -> TITLE4(CallNumber)
FK(LibId) -> MEMBER4(LibId)

ORDERED4(CallNumber, PoNum, Supplier_Id);
PK(CallNumber, PoNum, Supplier_Id)
FK(CallNumber) -> TITLE4(CallNumber)
FK(PoNum) -> PURCHASE_ORDER4(PoNum)
FK(Supplier_Id) -> SUPPLIER4(Supplier_Id)


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

2.
select distinct t.name,t.isbn,t.callnumber,sum(qty)
from title4 t natural join ordered4 o natural join purchase_order4 p
where t.callnumber in (select callnumber
                       from purchase_order4 natural join ordered4
                       group by callnumber
                       having sum(qty)>10)
group by name,isbn,callnumber
having count(Supplier_Id)>1
order by sum(qty) asc;

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.libid,m.fname
from member4 m
where m.gender='F'
and m.libid in (select libid
                from read_by4
                where timesread>1)
order by fname;

5.
select s.fname, s.supplier_id,avg(qty)  
from supplier4 s natural join ordered4 o natural join purchase_order4 p
group by s.supplier_id,s.fname
having avg(qty)>(select avg(qty) 
                 from purchase_order4)
order by 3;

6.
select m.fname,m.libid,count(distinct isbn),r.timesread
from member4 m natural join title4 t natural join read_by4 r
where m.Gender='M' 
and Join_date<=to_date('1995/10/01', 'YYYY/MM/DD') 
and timesread<2
group by libid,fname,timesread
having count(distinct isbn)<=5;