Exam 2: MySQL

Question 1

List all genres of books available in the database.

Answer 1

SELECT DISTINCT genre FROM books;

Question 2

Find the total number of books sold per genre.

Answer 2

SELECT b.Genre,SUM(s.quantity) AS sale_total FROM books b LEFT JOIN sales s ON b.BookID = s.BookID GROUP BY b.Genre;

Question 2

Find genres that have sold more than 100 books in total

Answer 2

SELECT b.Genre,SUM(s.quantity) AS sale_total FROM books b LEFT JOIN sales s ON b.BookID = s.BookID GROUP BY b.Genre HAVING SUM(s.quantity)>100;

Question 3

Find the book(s) with the highest sales

Answer 3

SELECT b.title, SUM(s.quantity) AS sales_total FROM books b JOIN sales s ON b.BookID = s.BookID GROUP BY b.BookID,b.title ORDER BY sales_total DESC LIMIT 1;

Question 4

List all books along with the author name, sorted by price in descending order

Answer 4

SELECT b.title,a.Name,b.price FROM books b JOIN authors a ON b.AuthorID = a.AuthorID ORDER BY b.Price DESC;

Question 5

f List the total sales for each author (in terms of number of books sold).

Answer 5

SELECT a.Name, SUM(s.quantity) AS sale_total FROM authors a JOIN books b ON a.AuthorID = b.AuthorID JOIN sales s ON b.BookID = s.BookID GROUP BY a.Name;

Question 6

List the books whose price exceeds the average price of books in their genre.

Answer 6

SELECT b.title,b.price,b.genre FROM books b WHERE b.price>(SELECT AVG(price) FROM books WHERE genre = b.Genre);

Question 7

List the books whose price exceeds the average price of all books.

Answer 7

SELECT b.title,b.price FROM books b WHERE b.price>(SELECT AVG(price) FROM books);

Question 8

SELECT Title FROM books WHERE AuthorID = (SELECT AuthorID FROM authors WHERE Name = ‘J.K. Rowling’);

Answer 8

SELECT title FROM books JOIN authors ON books.AuthorID = authors.AuthorID WHERE authors.name = ‘J.K. Rowling’;

Question 9

SELECT Title, Price FROM Books WHERE Price > (SELECT AVG(Price) FROM Books);

Answer 9

SELECT title, price FROM books JOIN(SELECT AVG(price) AS AvgPrice FROM books) AvgPrice ON books.price > AvgPrice.AvgPrice;

Question 10

SELECT Title, Price, Genre FROM Books b WHERE Price > (SELECT AVG(b2.Price) FROM Books b2 WHERE b2.Genre = b.Genre);

Answer 10

SELECT b.title,b.price,b.genre FROM books b JOIN (SELECT genre,AVG(price) AS AvgPrice FROM books GROUP BY genre) AvgPrices ON b.genre = AvgPrices.genre WHERE b.price > AvgPrices.AvgPrice;

Question 11

Aggregates

Answer 11

-COUNT returns number of values in specified
column.
-SUM returns sum of values in specified column.
-AVG returns average of values in specified
column.
-MIN returns smallest value in specified column.
-MAX returns largest value in specified column.

Question 12

SELECT Statement

Answer 12

-FROM
Specifies table(s) to be used.
-WHERE Filtersrows.
-GROUP BY Formsgroups of rows with same
column value.
-HAVING Filters groups subject to some
condition.
-SELECT
Specifies which columns are to
appear in output.
-ORDER BY Specifies the order of the output.

Question 13

Observation in EXPLAIN Output

Answer 13

High rows Value: The rows column shows a large number of
rows being examined.
Extra Column Shows Using temporary or Using filesort:
Indicates that the query is processing a large dataset, possibly
due to retrieving unnecessary columns.