This LAB tutorial is to practice ORDERBY clause and AGGREGATE Functions

### Sample Database:

Consider a database for a bookstore with the following tables:

`books`

:- Columns:
`book_id`

,`title`

,`author`

,`price`

,`quantity`

.

- Columns:
`customers`

:- Columns:
`customer_id`

,`name`

,`email`

,`phone`

.

- Columns:
`orders`

:- Columns:
`order_id`

,`customer_id`

,`order_date`

.

- Columns:
`order_items`

:- Columns:
`order_item_id`

,`order_id`

,`book_id`

,`quantity`

,`unit_price`

.

- Columns:

## QUERIES

1.**List all books in the bookstore, sorted by title in ascending order.**

This query retrieves all columns from the `books`

table and sorts the result by the `title`

column in ascending order.

`SELECT * FROM books ORDER BY title ASC;`

**2. Find the total number of books in stock.**

This query calculates the sum of the `quantity`

column in the `books`

table, giving the total number of books in stock.

`SELECT SUM(quantity) AS total_books_in_stock FROM books;`

3. **List the names and emails of customers who have placed orders, sorted alphabetically by name.**

This query selects the `name`

and `email`

columns from the `customers`

table for customers who have placed orders (determined by matching `customer_id`

in the `orders`

table) and sorts the result by `name`

in ascending order.

`SELECT name, email FROM customers WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders) ORDER BY name ASC;`

4. **Calculate the total revenue generated from book sales.**

This query calculates the total revenue generated from book sales by multiplying the `quantity`

and `unit_price`

columns from the `order_items`

table and summing up the results.

`SELECT SUM(quantity * unit_price) AS total_revenue FROM order_items;`

5. **List the top 5 bestselling books by the quantity sold.**

This query joins the `books`

and `order_items`

tables on `book_id`

to calculate the total quantity sold for each book. It then selects the `title`

of the book and the total quantity sold, groups the results by `book_id`

, orders them by total quantity sold in descending order, and limits the result to the top 5 rows.

`SELECT b.title, SUM(oi.quantity) AS total_quantity_sold FROM books b JOIN order_items oi ON b.book_id = oi.book_id GROUP BY b.book_id ORDER BY total_quantity_sold DESC LIMIT 5;`

**6. Find the average price of books in the store.**

This query calculates the average value of the `price`

column in the `books`

table, giving the average price of books in the store.

`SELECT AVG(price) AS average_price FROM books;`

**7. List the authors along with the total number of books they have authored, sorted by the number of books in descending order.**

This query groups the books by `author`

and counts the number of books for each author. It then orders the result by the total number of books authored in descending order.

`SELECT author, COUNT(*) AS total_books_authored FROM books GROUP BY author ORDER BY total_books_authored DESC;`

8. **Find the customer who has spent the most money on books.**

This query joins the `customers`

, `orders`

, and `order_items`

tables to calculate the total amount spent by each customer. It then selects the `name`

of the customer and the total amount spent, groups the results by `customer_id`

, orders them by total amount spent in descending order, and limits the result to the top 1 row.

`SELECT c.name, SUM(oi.quantity * oi.unit_price) AS total_spent FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON o.order_id = oi.order_id GROUP BY c.customer_id ORDER BY total_spent DESC LIMIT 1;`

9. **List the customers who have not placed any orders yet.**

This query selects the `name`

and `email`

columns from the `customers`

table for customers who do not have a corresponding `customer_id`

in the `orders`

table.

`SELECT name, email FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);`

10. **Find the book with the highest price.**

This query selects all columns from the `books`

table and orders the result by `price`

in descending order, then limits the result to the top 1 row, giving the book with the highest price.

`SELECT * FROM books ORDER BY price DESC LIMIT 1;`