ssd7 execise3-queries

ssd7 exercise3 答案

/*1.Display the ID, first name, and joining date of each member whose ID begins with the letters "A%B" or "A&B" and who joined the library no later than Nov. 30, 1997.*/
SELECT libid,fname,Join_date
FROM MEMBER4
where libid LIKE 'A\%B%' or libid LIKE 'A&B%' AND Join_date < '1997-11-30';
/*2.List the name, ISBN, and call number of each title, provided it has been ordered from more than one supplier and the total quantity ordered is more than 10. The list should be arranged in non-increasing order of the total quantity ordered.*/
SELECT name,ISBN,TITLE4.CallNumber,SUM(Qty) as total_quantity
FROM TITLE4 , ORDERED4,PURCHASE_ORDER4
where PURCHASE_ORDER4.PoNum = ORDERED4.PoNum and TITLE4.CallNumber = ORDERED4.CallNumber
GROUP BY name,ISBN,TITLE4.CallNumber
HAVING COUNT(Supplier_Id) > 1 AND SUM(Qty) > 10
ORDER BY SUM(Qty) ASC
/*3.List the IDs and first names of all the members who have read less than 5% of the total number of books read from the library, in non-decreasing order of the number of books read by each.*/

SELECT member4.libid,fname,SUM(TimesRead) AS TotalTimesRead
FROM MEMBER4 ,READ_BY4
where MEMBER4.libid = READ_BY4.libid
GROUP BY MEMBER4.libid,fname
HAVING SUM(TimesRead) < (SELECT 0.05 * SUM(TimesRead) FROM READ_BY4)
ORDER BY SUM(TimesRead) DESC
/*4.List in alphabetical order the first names and IDs of all female members of the library who have read at least one title more than once.*/
select fname ,Gender
from member4
where Gender='F'AND member4.libid in
( select read_by4.libid from read_by4 where TimesRead > 1 )
ORDER BY fname ASC;
/*5.List the names of the suppliers and their respective average quantities of title orders received, whose average quantities of title orders received is more than the average quantity of an order in the system.*/
SELECT http://www.wendangwang.com,AVG(Qty) AS Avg_qty
FROM SUPPLIER4 , ORDERED4, PURCHASE_ORDER4
where PURCHASE_ORDER4.PoNum = ORDERED4.PoNum and SUPPLIER4.Supplier_Id = ORDERED4.Supplier_Id
GROUP BY http://www.wendangwang.com,ORDERED4.Supplier_Id
HAVING AVG(Qty) > (SELECT AVG(Qty) FROM PURCHASE_ORDER4);
/*6.List alphabetically the first names and IDs of all the male members of the library who joined before Oct. 10, 1995, who have read only five or fewer different titles, and who have not read the same title twice or more.*/
SELECT MEMBER4.fname,MEMBER4.libid,READ_BY4.TimesRead,COUNT (DISTINCT CallNumber)
FROM MEMBER4, READ_BY4
WHERE MEMBER4.libid = READ_BY4.libid
AND MEMBER4.Gender = 'M' AND Join_


date < '1995-10-10'AND TimesRead < 2
GROUP BY MEMBER4.fname,MEMBER4.libid,READ_BY4.TimesRead
HAVING COUNT (DISTINCT CallNumber) <= 5;

Word文档免费下载Word文档免费下载:ssd7 execise3-queries (共1页,当前第1页)

ssd7 execise3 queries相关文档

最新文档

返回顶部