ex2.sql

来自「SSD7 EX2 createLibraryPostgreSQL.sql E」· SQL 代码 · 共 104 行

1.
SELECT name,year
FROM title
WHERE title.callnumber IN
(SELECT callnumber 
 FROM author
 WHERE author.lname ='Churchill'
)



2.
SELECT name
FROM title
WHERE title.callnumber IN
(SELECT callnumber
 FROM book AS b, member AS m
 WHERE b.borrowermemno = m.memno AND (m.fname = 'John' OR m.fname = 'Susan')
)



3.
SELECT m.fname, m.mi, m.lname, m.memno
FROM member AS m
WHERE memno IN
(SELECT b.borrowermemno
 FROM book AS b,title AS t
 WHERE b.callnumber = t.callnumber AND t.name = 'Iliad'

INTERSECT

SELECT b.borrowermemno
 FROM book AS b, title AS t
 WHERE b.callnumber = t.callnumber AND t.name = 'Odyssey'
)  



4.
SELECT m.fname, m.mi, m.lname, m.memno
FROM member AS m
WHERE m.memno IN
(SELECT borrowermemno
 FROM book AS b, author AS a
 WHERE b.callnumber = a.callnumber AND a.lname = 'Collins'
 GROUP BY b.borrowermemno, b.callnumber
 HAVING COUNT(DISTINCT b.borrowermemno) >= COUNT(b.callnumber)
)



5.
SELECT phonenumber
FROM member
WHERE memno IN
(SELECT borrowermemno
 FROM book AS b,author AS a
 WHERE a.callnumber = b.callnumber AND a.lname = 'Tanenbaum'
)



6.
SELECT m.fname, m.mi, m.lname, m.memno, count(*)
FROM member AS m, book AS b
WHERE m.memno = b.borrowermemno
GROUP BY m.fname, m.mi, m.lname, m.memno
HAVING COUNT(m.memno) > 3
ORDER BY count(*) DESC



7.
SELECT *
FROM member
WHERE memno IN
((SELECT memno
  FROM member
)
EXCEPT
(SELECT DISTINCT borrowermemno
 FROM book
)
)



8.
SELECT fname
FROM member
WHERE member.memno IN
((SELECT memno
 FROM member
 WHERE member.phonenumber LIKE '412%'
)

EXCEPT

(SELECT memno
 FROM member, book, title
 WHERE member.memno = book.borrowermemno AND book.callnumber = title.callnumber AND title.name = 'Pitt Roads'
))
ORDER BY member.fname