homework_10.txt

CREATE TABLE Authors (
	author_Id		INTEGER 	PRIMARY KEY		AUTOINCREMENT,
	name			TEXT		NOT NULL,
	country			TEXT,
	year_of_birth	INTEGER,
	phone_number	INTEGER,
	email_address	TEXT,
	CHECK (phone_number > 0
			AND year_of_birth > 0)
);

CREATE TABLE Books (
	book_Id			INTEGER 	PRIMARY KEY		AUTOINCREMENT,
	title			TEXT		NOT NULL,
	author			INTEGER,
	publisher 		TEXT,
	description 	TEXT 		NOT NULL,
	edition 		INTEGER,
	"year" 			INTEGER 	NOT NULL,
	quantity 		INTEGER 	NOT NULL,
	price 			REAL,
	FOREIGN KEY(author) REFERENCES Authors(author_Id),
	CHECK (edition > 0 
			AND "year" > 0 
			AND quantity > 0 
			AND price > 0),
	CHECK (publisher = 'Penguin House'
			OR publisher = 'HarperCollins'
			OR publisher = 'MacMillan'
			OR publisher = 'Scholastic')
--	CHECK (WHEN publisher = 'MacMillan' THEN "year" > 2003)
);

--2
--a.
SELECT b.title, a.name 
FROM Books b 
INNER JOIN Authors a ON a.author_Id = b.author 
ORDER BY a.name

--b.
SELECT b.title, b.price 
FROM Books b 
INNER JOIN Authors a ON b.author = a.author_Id 
WHERE (2020 - a.year_of_birth) > 30
ORDER BY a.year_of_birth

--c.
SELECT MAX(b.price), a.name 
FROM Books b 
INNER JOIN Authors a ON b.author = a.author_Id

--d.
SELECT a.country, AVG(b.price) AS average_book_price
FROM Authors a
INNER JOIN Books b ON a.author_Id = b.author
GROUP BY a.country
ORDER BY average_book_price

--e.
SELECT a.country, COUNT(b."year") AS publication
FROM Authors a
INNER JOIN Books b ON a.author_Id = b.author 
WHERE a.country = "United States
GROUP BY b."year"

--f.
SELECT b.title, a.name 
FROM Books b 
INNER JOIN Authors a ON b.author = a.author_Id 
WHERE 30 > (2020 - a.year_of_birth)
	AND b.quantity > 0

--g.
SELECT MAX(b.price), b.edition as max_price_edition
FROM Books b 
GROUP BY max_price_edition 

--h.
SELECT COUNT(b.author), b.title as count_author
FROM Books b 
INNER JOIN Authors a ON b.author = a.author_Id 
GROUP BY  b.author