quary4.sql

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

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
	)
	)