Bookstore-SQL-Assigment

assignment_1.sql

SELECT
	editions.isbn	AS "ISBN"
FROM editions
	INNER JOIN books		ON (books.id = editions.book_id)
	INNER JOIN publishers	ON (editions.publisher_id = publishers.id)
WHERE publishers.name = 'Random House';

assignment_10.sql

SELECT
	authors.id AS "Author ID",
	concat(authors.first_name, ' ', authors.last_name) AS "Author name",
	count(books.author_id = authors.id) AS "No. of books"
FROM
	books
INNER JOIN
	authors ON (books.author_id = authors.id)
GROUP BY
	authors.id
ORDER BY
	count(books.author_id = authors.id) DESC
;

assignment_11.sql

SELECT
	authors.id AS "Author ID",
	concat(authors.first_name, ' ', authors.last_name) AS "Author name",
	count(books.author_id = authors.id) AS "No. of books"
FROM
	books
INNER JOIN
	authors ON (books.author_id = authors.id)
GROUP BY
	authors.id
ORDER BY
	count(books.author_id = authors.id) DESC
;

assignment_12.sql

SELECT
	hardcopy.title
FROM
	(
		SELECT
			books.title
		FROM
			editions
		INNER JOIN
			books ON (editions.book_id = books.id)
		WHERE
			editions.type = 'h' 
	) AS hardcopy
INNER JOIN
	(
		SELECT
			books.title
		FROM
			editions
		INNER JOIN
			books ON (editions.book_id = books.id)
		WHERE
			editions.type = 'p'
	) AS paperback
	ON (hardcopy.title = paperback.title)
;

assignment_13.sql

SELECT
	publishers.name	AS "Publisher",
	avg(stock.retail)	AS "Average retail price",
	count(editions.edition)
FROM
	publishers
LEFT OUTER JOIN
	editions ON (publishers.id = editions.publisher_id)
LEFT OUTER JOIN
	stock ON (editions.isbn = stock.isbn)
GROUP BY
	publishers.name
ORDER BY
	count(editions.edition) DESC
;

assignment_2.sql

SELECT
	editions.isbn	AS "ISBN",
	books.title	AS "Title"
FROM editions
	INNER JOIN books		ON (books.id = editions.book_id)
	INNER JOIN publishers	ON (editions.publisher_id = publishers.id)
WHERE publishers.name = 'Random House';

assignment_3.sql

SELECT
	editions.isbn	AS "ISBN",
	books.title	AS "Title",
	stock.stock    AS "Item is in stock",
	stock.retail	 AS "Item's retail value"
FROM editions
	INNER JOIN books		ON (books.id = editions.book_id)
	INNER JOIN publishers	ON (editions.publisher_id = publishers.id)
	INNER JOIN stock		ON (editions.isbn = stock.isbn)
WHERE publishers.name = 'Random House';

assignment_4.sql

SELECT
	editions.isbn	AS "ISBN",
	books.title	AS "Title",
	stock.stock    AS "Item is in stock",
	stock.retail	 AS "Item's retail value"
FROM editions
	INNER JOIN books		ON (books.id = editions.book_id)
	INNER JOIN publishers	ON (editions.publisher_id = publishers.id)
	INNER JOIN stock		ON (editions.isbn = stock.isbn)
WHERE publishers.name = 'Random House' AND
	(stock.stock) > 0

assignment_5.sql

SELECT
	
	books.title		AS "Title",
	editions.publication 	AS "Date of Publication"
	
FROM books
	LEFT OUTER JOIN editions		ON (books.id = editions.book_id);

assignment_6.sql

SELECT
	books.title			AS	"Title",
	editions.publication	AS	"Date of publication"
FROM books
	LEFT OUTER JOIN editions on (books.id = editions.book_id)
;

assignment_7.sql

SELECT
	sum (stock.stock)
FROM
	stock
;

assignment_8.sql

SELECT
	avg(stock.cost)	AS "Average cost",
	avg(stock.retail)	AS	"Average retail price",
	avg(stock.retail / stock.cost * 100 - 100) AS	"Average % profit"
FROM
	stock
;

assignment_9.sql

SELECT
	books.title,
	stock.stock
FROM
	stock,
	editions,
	books
WHERE
	(stock.isbn = editions.isbn) AND
	(editions.book_id = books.id)
ORDER BY
	stock.stock DESC
LIMIT
	1
;

Hi Shino, here are some comments on your sql code:

• your formatting is a bit odd, you have tabs in the middle of lines of code without making everything line up (for example assignment 1 and assignment 8). Either just use single spaces and don't worry about making things line up, or use tabs and make sure everything lines up.
• generally there is no need to put parenthesis around your ON statements or your WHERE clauses: '(stock.isbn = editions.isbn)' can just be 'stock.isbn = editions.isbn'. They are needed to specify the expected logic when you have multiple ANDs and ORs in WHERE clauses - but otherwise they aren't necessary.
• in assignment_7 you have 'sum (stock.stock)' - never put a space between a function name and the first parenthesis! It doesn't change the meaning (in most cases!) but it does make it harder for you or someone else to read code.

Give me a shout if you have any questions!
-Scott