You will be using the following relational schema: Book(Title,Price,Year) Author
ID: 3544987 • Letter: Y
Question
You will be using the following relational schema:
Book(Title,Price,Year)
Author(Name,Booktitle,Position)
Write the following queries in SQL2, dening appropriate views if necessary:
Productivity: For each author, return the maximum number of books published in a single
year, in descending order.
Slackers: Return every author who was in the second or third position in all of his/her books.
Moneymakers: Return all the authors whose average book price was at least twice the average
book price of every other author.
Histogram: Assume you are given another table Ranges(Low,High), containing price ranges.
For every such range, return the number of books in it. You can assume that the prices are positive
integers in the range [0; 100] and that the Low value of a range is equal to the High value of the
previous range1 plus 1. Also, the smallest Low value is 0 and the largest High value is 100. An
example of price ranges:
[0; 10]; [11; 15]; [16; 50]; [51; 100]:
Crowd pleasers: Make the same assumptions as in Histogram. Return all the authors that
had a book in every price range.
Explanation / Answer
1)
SELECT A.Name, COUNT( B.Title)
FROM Author A,
Book B
WHERE
A.Booktitle=B.Title
GROUP BY A.Author,B.Year
ORDER BY DESC
2)
SELECT A.Name
FROM Author A
WHERE
A.Position IN ('SECCOND','THIRD')
3)
SELECT DISTINCT A.Name
FROM Author A,
Book B
WHERE
A.Booktitle=B.Title
AND EXISTS ( SELECT 1 FROM BOOK C WHERE B.Price > 2*C.Price )
4)
SELECT count(a.title) "Count", b.low "Low", b.high "High"
FROM Book a INNER JOIN Ragesn b
ON b.low <= a.price AND b.high >= a.price
GROUP BY b.low, b.high;
5)
SELECT c.name "Author Name", b.low "Low", b.high "High"
FROM book a INNER JOIN ranges b
ON b.low <= a.price AND b.high >= a.price
INNER JOIN author c
ON c.booktitle = a.title;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.