Book ( Title ,Price Year) Author (Name, booktitle, position ) Booktitle in Autho
ID: 3544861 • Letter: B
Question
Book (Title,Price Year)
Author (Name, booktitle, position)
Booktitle in Author references Book( Title )
Write a SQL query which :
2) 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]:
3)Make the same assumptions as in Histogram. Return all the authors that had a book in every price range.
Explanation / Answer
Here is your complete solution, I have also included the DDL commands for your reference, Please review and rate. Thanks:
DROP TABLE IF EXISTS author;
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS ranges;
CREATE TABLE book
(
title VARCHAR(30),
price INT(3),
year CHAR(4)
);
CREATE TABLE author
(
name VARCHAR(30),
booktitle VARCHAR(30),
position INT(3),
CONSTRAINT fr_key FOREIGN KEY(booktitle) REFERENCES book(title)
);
CREATE TABLE ranges
(
low INT(3),
high INT(3)
);
INSERT INTO book VALUES('Alpha', 33, '1998');
INSERT INTO book VALUES('Beta', 45, '1998');
INSERT INTO book VALUES('Gamma', 57, '1998');
INSERT INTO book VALUES('Zeta', 11, '1998');
INSERT INTO book VALUES('Theta', 7, '1998');
INSERT INTO book VALUES('Omega', 80, '1998');
INSERT INTO book VALUES('Zones', 92, '1998');
INSERT INTO author VALUES('Amy', 'Alpha', 1);
INSERT INTO author VALUES('Barnedette', 'Beta', 2);
INSERT INTO author VALUES('Raj', 'Gamma', 3);
INSERT INTO author VALUES('Penny', 'Zeta', 4);
INSERT INTO author VALUES('Sheldon', 'Theta', 5);
INSERT INTO author VALUES('Leonard', 'Omega', 6);
INSERT INTO author VALUES('Howard', 'Zones', 7);
INSERT INTO ranges VALUES(0, 10);
INSERT INTO ranges VALUES(11, 15);
INSERT INTO ranges VALUES(16, 50);
INSERT INTO ranges VALUES(51, 100);
-- Answer for question 2
SELECT count(a.title) "Count", b.low "Low", b.high "High"
FROM book a INNER JOIN ranges b
ON b.low <= a.price AND b.high >= a.price
GROUP BY b.low, b.high;
-- Answer for question 3
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;