exercise2.txt

数据库查询语言,便于新手的使用

1.
SELECT name as "TITLE", year as "YEAR"
FROM title
WHERE callnumber IN
		(
		 SELECT callnumber
		 FROM author
		 WHERE lname = 'Churchill'
		);

2.
SELECT name as "TITLE" 
FROM title
where callnumber IN
		(
		 SELECT callnumber
		 FROM book
		 WHERE borrowermemno IN
				(
				 SELECT memno
				 FROM member
				 WHERE fname = 'John' OR fname = 'Susan'
				)
		)

3.
SELECT fname as "First Name", lname as "Last Name", memno as "ID"
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.
(1)
	 SELECT distinct borrowermemno ,callnumber
	 FROM book
	 WHERE callnumber IN
			 (
			  SELECT callnumber
			  FROM author
			  WHERE lname = 'Collins'
			 )



(2)
SELECT borrowermemno 
FROM (
	SELECT distinct borrowermemno ,callnumber
		 FROM book
		 WHERE callnumber IN
				 (
				  SELECT callnumber
				  FROM author
				  WHERE lname = 'Collins'
				 )
) as aa

GROUP BY borrowermemno
HAVING COUNT(borrowermemno) = 
(
SELECT COUNT(lname)
FROM author
GROUP BY lname;
)


(3)
SELECT borrowermemno 
FROM (
	SELECT distinct borrowermemno ,callnumber
		 FROM book
		 WHERE callnumber IN
				 (
				  SELECT callnumber
				  FROM author
				  WHERE lname = 'Collins'
				 )
) as aa

GROUP BY borrowermemno
HAVING COUNT(borrowermemno) = 
(
SELECT COUNT(lname)
FROM author
WHERE lname = 'Collins'
GROUP BY lname
)
	

(4)
SELECT fname as "First Name", lname as "Last Name", memno as "ID"
FROM member
WHERE memno IN 
	(
	SELECT borrowermemno 
	FROM (
		SELECT distinct borrowermemno ,callnumber
			 FROM book
			 WHERE callnumber IN
					 (
					  SELECT callnumber
					  FROM author
					  WHERE lname = 'Collins'
					 )
	) as aa

	GROUP BY borrowermemno
	HAVING COUNT(borrowermemno) = 
	(
	SELECT COUNT(lname)
	FROM author
	WHERE lname = 'Collins'
	GROUP BY lname
	)
	)
	

5.
SELECT phonenumber
FROM member	
WHERE memno IN	 
	(
	 SELECT borrowermemno 
	 FROM book
	 WHERE callnumber IN
			 (
			  SELECT callnumber
			  FROM author
			  WHERE lname = 'Tanenbaum'
			 )
	)

6.
(1)
SELECT fname as "First Name", lname as "Last Name", memno as "ID",COUNT(member.memno) AS "SUM"
FROM member LEFT  JOIN book ON (member.memno = book.borrowermemno)
GROUP BY member.memno
HAVING COUNT(member.memno) > 3


(2)
SELECT information.fname as "First Name", information.lname as "Last Name", information.memno as "ID",COUNT(information.memno)
FROM (
	SELECT fname , lname, memno
	FROM member LEFT  JOIN book ON (member.memno = book.borrowermemno)
) AS information
GROUP BY information.memno,information.fname,information.lname
HAVING COUNT(information.memno) > 3
ORDER BY COUNT(information.memno)  DESC

7.
SELECT information.fname as "First Name", information.lname as "Last Name",information.memno AS "ID"
FROM (
	SELECT fname , lname, memno,callnumber
		FROM member LEFT OUTER JOIN book ON (member.memno = book.borrowermemno)
) AS information
WHERE information.callnumber IS NULL;