sql.sql

这是卡耐基-梅泷大学ssd7

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=any(
                     select CallNumber
                     from book
                     where BorrowerMemNo=any(
                                             select BorrowerMemNo
                                             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) as book
                     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"));


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;


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