queries.sql

本人的ssd7的练习3的答案

--1.
Select libId, fName, join_date
From MEMBER4
Where (libId like 'A#%B%' Escape '#' OR libId like 'A&B%')
	AND (join_date < '1997-11-30');


--2.
Select name, isbn, callnumber
From (TITLE4 Natural Join ORDERED4) Natural Join PURCHASE_ORDER4
Where callnumber IN (Select callnumber
		     From ORDERED4 Natural Join PURCHASE_ORDER4
		     Group by callnumber
		     Having sum(qty) > 10)
Group by name, isbn, callnumber
Having Count(*) > 1
Order by sum(qty) desc;


--3.
Select libId, fName
From MEMBER4 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 distinct fName, libId
From MEMBER4 Natural Join READ_BY4
Where gender = 'F' AND timesRead > 1
Order by fName;

--5.

Select name, avg(qty)
From (SUPPLIER4 Natural Join ORDERED4) Natural Join PURCHASE_ORDER4
Group by name, supplier_id
Having avg(qty) > (Select avg(qty)
		   From PURCHASE_ORDER4);


--6.
Select fName, libid
From MEMBER4 Natural Join READ_BY4
Where gender = 'M' And join_date < '1995-10-10'
     And libid NOT IN (Select distinct libid
		       From MEMBER4 Natural Join READ_BY4
		       Where READ_BY4.timesRead > 1)
Group by fName, libid
Having Count(*) <= 5
Order by fName;