Lab Tutorial 4 : ORDER BY & AGGREGATE Functions

This LAB tutorial is to practice ORDERBY clause and AGGREGATE Functions

Sample Database:

Consider a database for a bookstore with the following tables:

1. books:
   • Columns: book_id, title, author, price, quantity.
2. customers:
   • Columns: customer_id, name, email, phone.
3. orders:
   • Columns: order_id, customer_id, order_date.
4. order_items:
   • Columns: order_item_id, order_id, book_id, quantity, unit_price.

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;