Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Query question, Need to write a query that returns only the students that have read all books by an author.

Re: Query question, Need to write a query that returns only the students that have read all books by an author.

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 09 Jun 2007 15:09:01 +0200
Message-ID: <466AA66D.2010601@gmail.com>


Charles Hooper schrieb:
> On Jun 8, 5:56 pm, Albright.Br..._at_gmail.com wrote:

>> I need to write a query that returns only the students that have read
>> all books by an author.  I have these tables set up so far....
>>
>> create table Books (
>>         BookTitle varchar2(20) PRIMARY KEY,
>>         author varchar2(20)
>> );
>>
>> create table BookCamp (
>>         MemberName varchar2(20),
>>         BookTitle varchar2(20),
>>         CONSTRAINT fk_BookTitle
>>           FOREIGN KEY (BookTitle)
>>           REFERENCES Books(BookTitle)
>> );
>>
>> insert into Books values ('Psycho', 'Brian');
>> insert into Books values ('Happy Rotter', 'Rocksteady');
>> insert into Books values ('Goblet', 'J.K Rowling');
>> insert into Books values ('Prisoner', 'J.K Rowling');
>>
>> insert into BookCamp values ('Bob', 'Psycho');
>> insert into BookCamp values ('Chuck', 'Goblet');
>> insert into BookCamp values ('Chuck', 'Prisoner');
>> insert into BookCamp values ('Mike', 'Psycho');
>> insert into BookCamp values ('Mike', 'Goblet');
>> insert into BookCamp values ('Mike', 'Prisoner');
>> insert into BookCamp values ('Mary', 'Goblet');
>>
>> So basically, if I inputted "J.K Rowling" the names "Chuck" and "Mike"
>> should come up.  If the author is "Brian" then the names "Bob" and
>> "Mike" should come up.  I've tried several things like...
>> select membername from BookCamp where BookTitle in(select BookTitle
>> from Books where (author = 'J.K Rowling')); but this obviously isn't
>> quite there....Any Help?

>
> Posting the table definitions and insert statements is a significant
> help (note to future posters).
>
> Maxim has provided a solution that produces the desired list. Let's
> see if we can develop another method to solve this problem. First, a
> simple experiment using the analytical version of COUNT:
> SELECT
> B.BOOKTITLE,
> B.AUTHOR,
> COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) NUM_BOOKS
> FROM
> BOOKS B
> WHERE
> B.AUTHOR='J.K Rowling';
>
> BOOKTITLE AUTHOR NUM_BOOKS
> -------------------- -------------------- ----------
> Prisoner J.K Rowling 2
> Goblet J.K Rowling 2
>
> Not too impressive yet, but let's add in the second table:
> SELECT
> BC.MEMBERNAME,
> B.BOOKTITLE,
> B.AUTHOR,
> COUNT(DISTINCT B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR)
> AUTHOR_NUM_BOOKS,
> COUNT(BC.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME, B.AUTHOR)
> MEMBER_NUM_BOOKS
> FROM
> BOOKS B,
> BOOKCAMP BC
> WHERE
> B.AUTHOR='J.K Rowling'
> AND B.BOOKTITLE=BC.BOOKTITLE(+);
>
> Note that I changed the original COUNT(B.BOOKTITLE) to COUNT(DISTINCT
> B.BOOKTITLE) and changed the alias to AUTHOR_NUM_BOOKS:
> MEMBERNAME AUTHOR_NUM_BOOKS MEMBER_NUM_BOOKS
> -------------------- ---------------- ----------------
> Chuck 2 2
> Mary 2 1
> Mike 2 2
> Mike 2 2
> Chuck 2 2
>
> Now, we need a way to first eliminate all rows where AUTHOR_NUM_BOOKS
> is not equal to MEMBER_NUM_BOOKS, and then return a list of names
> without duplicates. This can be accomplished by sliding the above SQL
> statement into an inline view:
>
> SELECT DISTINCT
> MEMBERNAME
> FROM
> (SELECT
> BC.MEMBERNAME,
> COUNT(DISTINCT B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR)
> AUTHOR_NUM_BOOKS,
> COUNT(BC.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME, B.AUTHOR)
> MEMBER_NUM_BOOKS
> FROM
> BOOKS B,
> BOOKCAMP BC
> WHERE
> B.AUTHOR='J.K Rowling'
> AND B.BOOKTITLE=BC.BOOKTITLE(+))
> WHERE
> AUTHOR_NUM_BOOKS=MEMBER_NUM_BOOKS;
>
> MEMBERNAME
> --------------------
> Chuck
> Mike
>
> Let's try again, this time without analytical functions. First, let's
> find out how many of the author's books were read by each membername:
> SELECT
> BC.MEMBERNAME,
> B.AUTHOR,
> COUNT(*) MEMBER_NUM_BOOKS
> FROM
> BOOKS B,
> BOOKCAMP BC
> WHERE
> B.AUTHOR='J.K Rowling'
> AND BC.BOOKTITLE=B.BOOKTITLE
> GROUP BY
> BC.MEMBERNAME,
> B.AUTHOR;
>
> MEMBERNAME AUTHOR MEMBER_NUM_BOOKS
> -------------------- -------------------- ----------------
> Mike J.K Rowling 2
> Chuck J.K Rowling 2
> Mary J.K Rowling 1
>
> Now, let's determine the number of books written by each author:
> SELECT
> AUTHOR,
> COUNT(*) AUTHOR_NUM_BOOKS
> FROM
> BOOKS
> GROUP BY
> AUTHOR;
>
> AUTHOR AUTHOR_NUM_BOOKS
> -------------------- ----------------
> Rocksteady 1
> Brian 1
> J.K Rowling 2
>
> Let's put each into an inline view and pull out the membernames of
> interest:
> SELECT DISTINCT
> BC.MEMBERNAME
> FROM
> (SELECT
> BC.MEMBERNAME,
> B.AUTHOR,
> COUNT(*) MEMBER_NUM_BOOKS
> FROM
> BOOKS B,
> BOOKCAMP BC
> WHERE
> B.AUTHOR='J.K Rowling'
> AND BC.BOOKTITLE=B.BOOKTITLE
> GROUP BY
> BC.MEMBERNAME,
> B.AUTHOR) BC,
> (SELECT
> AUTHOR,
> COUNT(*) AUTHOR_NUM_BOOKS
> FROM
> BOOKS
> GROUP BY
> AUTHOR) B
> WHERE
> B.AUTHOR=BC.AUTHOR
> AND B.AUTHOR_NUM_BOOKS=BC.MEMBER_NUM_BOOKS;
>
> MEMBERNAME
> --------------------
> Chuck
> Mike
>
> Let's try one more time, A simple starting point:
> SELECT
> BC.MEMBERNAME,
> COUNT(BC.BOOKTITLE)
> FROM
> BOOKS B,
> BOOKCAMP BC
> WHERE
> B.AUTHOR='J.K Rowling'
> AND B.BOOKTITLE=BC.BOOKTITLE(+)
> GROUP BY
> BC.MEMBERNAME;
>
> MEMBERNAME COUNT(BC.BOOKTITLE)
> -------------------- -------------------
> Chuck 2
> Mary 1
> Mike 2
>
> [The above does not need to be an outer join]
> Now, let's add an inline view to retrieve the total number of books
> written by the author:
> SELECT
> BC.MEMBERNAME,
> COUNT(BC.BOOKTITLE) MEMBER_NUM_BOOKS,
> NB.AUTHOR_NUM_BOOKS
> FROM
> BOOKS B,
> BOOKCAMP BC,
> (SELECT
> COUNT(*) AUTHOR_NUM_BOOKS
> FROM
> BOOKS
> WHERE
> AUTHOR='J.K Rowling') NB
> WHERE
> B.AUTHOR='J.K Rowling'
> AND B.BOOKTITLE=BC.BOOKTITLE(+)
> GROUP BY
> BC.MEMBERNAME,
> NB.AUTHOR_NUM_BOOKS;
>
> MEMBERNAME MEMBER_NUM_BOOKS AUTHOR_NUM_BOOKS
> -------------------- ---------------- ----------------
> Chuck 2 2
> Mike 2 2
> Mary 1 2
>
> The final clean up is accomplished with a HAVING clause:
> SELECT
> BC.MEMBERNAME
> FROM
> BOOKS B,
> BOOKCAMP BC,
> (SELECT
> COUNT(*) AUTHOR_NUM_BOOKS
> FROM
> BOOKS
> WHERE
> AUTHOR='J.K Rowling') NB
> WHERE
> B.AUTHOR='J.K Rowling'
> AND B.BOOKTITLE=BC.BOOKTITLE(+)
> GROUP BY
> BC.MEMBERNAME,
> NB.AUTHOR_NUM_BOOKS
> HAVING
> COUNT(BC.BOOKTITLE)=NB.AUTHOR_NUM_BOOKS;
>
> MEMBERNAME
> --------------------
> Chuck
> Mike
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
>

Nice examples.
Just for fun, yet another one:

SELECT MEMBERNAME
FROM (SELECT B.MEMBERNAME,CAST(COLLECT(booktitle) AS SYS.dbms_debug_vc2coll) BOOKLIST

         FROM   BOOKCAMP B
         GROUP  BY MEMBERNAME) M,
        (SELECT AUTHOR,CAST(COLLECT(booktitle) AS 
SYS.dbms_debug_vc2coll) BOOKLIST
         FROM   BOOKS B
         GROUP  BY AUTHOR) A

WHERE A.BOOKLIST SUBMULTISET OF M.BOOKLIST AND AUTHOR = 'J.K Rowling'
/

Best regards

Maxim Received on Sat Jun 09 2007 - 08:09:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US