Monday, 9 September 2013

Is there a better way to structure this SQL Query?

Is there a better way to structure this SQL Query?

I have devised an answer to this problem (which I'll post at the end) but
I'm just wondering if there is a 'cleaner' way to structure the query.
The question is as follows:
What is the title and year published of the newest Philip Roth book? You
cannot use the ORDER BY clause. Write a single SQL statement. Use
Sub-queries. Do not assume that you already know the AuthorId for Philip
Roth, the BookId for his latest book or the year the latest book was
released.
The relevant tables from the Database:
Books (BookId, Title, YearPublished)
Authors (AuthorId, FirstName, LastName)
WrittenBy (AuthorId, BookId)
My solution:
SELECT Title, YearPublished
FROM Books NATURAL JOIN Authors NATURAL JOIN WrittenBy
WHERE YearPublished = (SELECT MAX(YearPublished)
FROM Books NATURAL JOIN Authors NATURAL JOIN WrittenBy
WHERE AuthorId = (SELECT AuthorId
FROM Authors
WHERE FirstName = 'Philip'
AND LastName = 'Roth'))
AND FirstName = 'Philip'
AND LastName = 'Roth';
I can't figure out a way to not specify the First and Last names again.
Otherwise it just lists all the books published in the same year as Philip
Roth's latest publication.
This query works perfectly. But, is there a cleaner way to query this?
Thanks :)
(as for which DBMS I am using: These are some review exercises for an
exam. We have to use one created and supplied by the university. It's very
basic SQL. Simpler than Access 2010)

No comments:

Post a Comment