exercise2.sql

来自「ssd7 exercise 2 ,绝对有用~欢迎下载」· SQL 代码 · 共 63 行

1.
SELECT t.name, t.year
FROM title t, author a
WHERE t.callnumber = a.callnumber AND a.lname = 'Churchill'

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

3.
SELECT fname, lname, memno
FROM member
WHERE memno IN ((SELECT borrowermemno
		FROM book
		WHERE callnumber IN (SELECT callnumber
				     FROM title
				     WHERE name = 'Iliad'))
		INTERSECT
		(SELECT borrowermemno
		 FROM book
		 WHERE callnumber IN (SELECT callnumber
				      FROM title
				      WHERE name = 'Odyssey')));


4,
SELECT memno, fname, lname
FROM member
WHERE memno IN (SELECT borrowermemno
		FROM book
		WHERE NOT EXISTS (SELECT *
				  FROM author
				  WHERE lname = 'Collins' AND NOT EXISTS (SELECT *
									  FROM book
									  WHERE borrowermemno = member.memno AND callnumber = author.callnumber)));
5.
SELECT m.phonenumber
FROM member m, book b, title t, author a
WHERE m.memno = b.borrowermemno AND b.callnumber = t.callnumber AND t.callnumber = a.callnumber AND a.lname = 'Tanenbaum'

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

7.
SELECT DISTINCT m.*
FROM member m, book b
WHERE m.memno NOT IN (SELECT DISTINCT b1.borrowermemno 
		      FROM book b1
		      WHERE borrowermemno IS NOT NULL )

8.
SELECT fname, memno
FROM member
WHERE phonenumber LIKE '412%' AND memno NOT IN(SELECT borrowermemno
					 FROM book b, title t
					 WHERE b.callnumber = t.callnumber AND t.name<>'Pitt Roads' AND borrowermemno IS NOT NULL)
ORDER BY fname