Advice needed on SQL Querying

Question

I'm studying for an exam that's in a couple of weeks and came across an SQL querying problem I still can't figure out. I was wondering if anyone could advise me.

Relational Database:

Books(**ISBN**, Title, Genre, Price, Publisher, PublicationYear)

Author(**AuthorNum**, Name)

Write(**ISBN**, AuthorNum)

Problem: Find the most expensive book from each publisher, along with the name of the author, arranged alphabetically by book title.

I've tried many things, with this one being the one I think is closest to the solution but it's not correct:

SELECT Title, Name
FROM Author AS a, Books AS b, Write AS w
WHERE a.AuthorNum = w.AuthorNum AND b.ISBN = w.ISBN
GROUP BY Publisher
HAVING MAX(Price)
ORDER BY Title

Which variant of SQL are you using? (Or is this class limited to the standard - and if so, which one?) — Sean Vieira, Apr 27 '13 at 13:39
What should happen in the case of ties: when *two* (or more) books from a publisher have *the same* highest price? — wildplasser, Apr 27 '13 at 13:41
The question is a past paper exam question and it doesn't specify what to do in such a case but good comment. I suppose all books of that price should be returned? — McGuile, Apr 27 '13 at 13:54
The whole idea is that you can construct your query in such a way, that it can be adopted to support both options. The cleanest / easiest way is by using analytic functions (row_number() vs rank()), if your DBMS supports them. http://stackoverflow.com/q/16119906/905902 — wildplasser, Apr 27 '13 at 14:06
Are you allowed to use `correlated sub-queries`? Then it's rather simple. — PM 77-1, Apr 27 '13 at 14:08
@ConnorMcGuile "*..along with the name of the author..*" The problem here is that there is nothing that implies that this book will have only one author. Indeed, the design of the tables specifically implies that some books have multiple authors. — RBarryYoung, Apr 27 '13 at 14:18
... and that some authors have multiple books. The Write table is clearly a bridge table. — scottb, Apr 27 '13 at 15:02

score 4 · Answer 1 · edited Jun 20 '20 at 09:12

Inline views often perform quite well on a variety of databases. Don't prematurely optimize.

You can get the top price per publisher so:

1

    select publisher, max(price) as MaxPublisherPrice
    from books
    group by publisher

You can find out which book(s) from each publisher have a price that equals the MaxPublisherPrice by joining against the set returned by the statement above like this:

2

    select books.title, P.MaxPublisherPrice as bookprice
    from books
    inner join
    (
      select publisher, max(price) as MaxPublisherPrice
      from books
      group by publisher
    ) as P
    on books.publisher = P.publisher
    and books.price = P.maxpublisherprice

and you can then pull in the author name so:

3

    select books.title, P.MaxPublisherPrice as bookprice, author.name
    from books
    inner join
    (
      select publisher, max(price) as MaxPublisherPrice
      from books
      group by publisher
    ) as P
    on books.publisher = P.publisher
    and books.price = P.maxpublisherprice
    inner join write
    on write.isbn = books.isbn
    inner join author 
    on write.authornum = author.authornum
    order by books.title

last join on author table is via write.authornum, not books.authornum ;) — Nenad Zivkovic, Apr 27 '13 at 14:28
+1: good explanation of the progression/evolution to meet the requirements. — RBarryYoung, Apr 27 '13 at 15:47
I liked how this was explained. Can you tell me what method you would prefer over the method given by RBarryYoung or even the differences? Both are similar from what I can tell. — McGuile, Apr 27 '13 at 15:58
You'd have to run them against a real dataset and decide for yourself. I would recommend a composite index on (publisher,price) when testing mine. — Tim, Apr 27 '13 at 21:10

RBarryYoung · Accepted Answer · 2013-04-27T15:12:27.613

1

This is how I would do it:

SELECT  b.Title, b.Name, b.Publisher, a.Author

FROM        Books  b
LEFT JOIN   Write  w    ON w.ISBN       = b.ISBN
INNER JOIN  Author a    ON a.AuthorNum  = w.AuthorNum  

WHERE   b.Price = (SELECT MAX(bb.Price) FROM Books bb
                   WHERE b.Publisher = bb.Publisher)

ORDER BY Title
;

Note some of the finer points:

uses only standard SQL syntax, no vendor-specific nor deprecated syntax
Accommodates the possibility that multiple books may have the hihgest price from one publisher
Accommodates the possibility that books may have more than one Author
Accommodates the possibility that a book may not have any known authors
Avoids the unnecessary use of GROUP BY which studies have shown is likely to be slower than either joins or subqueries

edited Apr 27 '13 at 15:12

answered Apr 27 '13 at 14:31

RBarryYoung

55,398
14
96
137

I very much like this solution. I hadn't thought to use joins at all. The top line should read 'SELECT b.Title, b.Publisher, a.Name' but I realised that easily enough. – McGuile Apr 27 '13 at 15:38
I have corrected it. And joins are generally the best way to address possible cardinality issues in SQL. – RBarryYoung Apr 27 '13 at 15:40

score 0 · Answer 3 · answered Apr 27 '13 at 13:38

you need to group on the publisher, title and name of the author of the book. not only publisher.

Grouping permits to split (or breakdown) the rows. (having max price is useless since you order desc the price)

I won't write the query here cause i won't do your homework :D :D

Advice needed on SQL Querying

3 Answers3

1

2

3