sql.sql

开耐饥梅陇大学 网上教程 ssd7 exercise2答案。 绝对 正确。 满分的答案。

Exercise Two SQL statements:


Query 1: 

use library;
select Name,Year
from title
where CallNumber=( select CallNumber
                   from author
                   where Lname="Churchill");


Query 2:

use library;
select Name
from title
where CallNumber IN(
                     select CallNumber
                     from book
                     where BorrowerMemNo IN(
                                             select MemNo
                                             from member
                                             where Fname="John" or Fname="Susan"));

Query 3:

use library;
select Fname,MI,Lname,MemNo as ID
from member
where MemNo=any(
                select BorrowerMemNo
                from book
                where CallNumber=(
                                  select CallNumber
                                  from title
                                  where Name="Odyssey"))
               and MemNo in (
                                  select BorrowerMemNo 
                                  from book where CallNumber=(
  select CallNumber 
   from title 
  where Name="Iliad");
   


Query 4:
select member.Fname,member.MI,member.Lname,book.BorrowerMemNo as ID,count(distinct author.CallNumber) as Collinsbook
from author join book on author.CallNumber=book.CallNumber,member
where (author.CallNumber=book.CallNumber and author.Lname="Collins")and book.BorrowerMemNo=member.MemNo
group by book.BorrowerMemNo
having Collinsbook=(
                     select count(distinct CallNumber)
                     from author
                     where author.Lname="Collins");


select member.MemNo as ID, member.Fname,member.Lname
from member,book,author
where member.MemNo = book.BorrowerMemNo and book.CallNumber = author.CallNumber and author.Lname = "collins"
group by book.BorrowerMemNo
having count(distinct author.CallNumber)=(
										 select count(distinct CallNumber)
										 from author
										 where author.Lname="Collins");
;

Query 5:
select PhoneNumber
from member
where exists(
             select *
             from book
             where BorrowerMemNo=member.MemNo and exists(
                                                          select *
                                                          from author
                                                          where CallNumber=book.CallNumber and Lname="Tanenbaum"));
                                                          
    select PhoneNumber
    from member
    where member.MemNo in (
							select BorrowerMemNo
							from book
							where CallNumber in(
													select CallNumber
													from author
													where author.Lname = "Tanenbaum"));



Query 6:
select Fname,MI,Lname,MemNo,count(*) as books
from member join book on member.MemNo=book.BorrowerMemNo
where member.MemNo=book.BorrowerMemNo
group by MemNo
having books>3
order by books desc;

select Fname,Lname,MemNo as ID,count(*) as numberOfBooks
from member , book 
where member.MemNo=book.BorrowerMemNo
group by MemNo
having numberOFBooks>3
order by numberOfBooks desc;


Query 7:
select *
from member
where not exists(
                 select *
                 from book
                 where BorrowerMemNo=member.MemNo);



Query 8:
select *
from member
where (PhoneNumber like "412%")and not exists(
                                               select *
                                               from book
                                               where BorrowerMemNo=member.MemNo and exists (
                                                                                             select *
                                                                                             from title
                                                                                             where CallNumber=book.CallNumber and Name="Pitt Roads"));