Lab 1 - Requêtes SQL

Queries to design on ecommerce database

  1. List all the suppliers with their details
  2. List all the products with their prices and stock quantities
  3. Find all the customers in a specific city (for example, “DC”)
  4. Display all the orders made in the last 30 days
  5. Find the details of a specific product by its ID (for example, “dc8a6d03-4a68-4cf2-be1c-4a06607fbf99”)
  6. Find the average price of all products
  7. Find the total stock quantity for a specific product (for example, “Product A”)
  8. List product name, product category and supplier name
  9. Count all the products that have never been ordered
  10. List customers who have never placed an order
  11. Display the average price of products by category
  12. Calculate the total revenue for each month in the current year
  13. Find the product with the highest price in each category
  14. Find the products whose price is above the average price of their own category
  15. List the customers who have placed at least one order for a product priced at over 500
  16. List the suppliers who have supplied products that have never been ordered
  17. Calculate the rank of each product by price within its category (using RANK())
  18. Display the top 5 customers who have spent the most money (using LIMIT)
  19. Find the customers who have made the most orders
  20. Find the suppliers who have supplied the most products

Solutions

  1. List all the suppliers with their details
SELECT * FROM suppliers;
  1. List all the products with their prices and stock quantities
SELECT name, price, stock_quantity FROM products;
  1. Find all the customers in a specific city (for example, “DC”)
SELECT * FROM customers WHERE address LIKE '%DC%';
  1. Display all the orders made in the last 30 days
SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL '30 DAY';
  1. Find the details of a specific product by its ID
SELECT * FROM products WHERE id = 'dc8a6d03-4a68-4cf2-be1c-4a06607fbf99';
  1. Find the average price of all products
SELECT AVG(price) FROM products;
  1. Find the total stock quantity for a specific product (for example, “Level Town”)
SELECT SUM(stock_quantity) FROM products WHERE name = 'Level Town';
  1. List product name, product category and supplier name
SELECT p.name AS product_name, p.category, s.name AS supplier_name
FROM products p
JOIN suppliers s ON p.supplier_id = s.id;
  1. Count all the products that have never been ordered
SELECT COUNT(*)
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.id IS NULL;
  1. List customers who have never placed an order
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
  1. Display the average price of products by category
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;
  1. Calculate the total revenue for each month in the current year
SELECT EXTRACT(MONTH FROM order_date) AS month, SUM(total_price) AS monthly_revenue
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY EXTRACT(MONTH FROM order_date);
  1. Find the product with the highest price in each category
SELECT category, name, MAX(price) AS max_price
FROM products
GROUP BY category;
  1. Find the products whose price is above the average price of their own category
SELECT name, price, category
FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);
  1. List the customers who have placed at least one order for a product priced at over 500
SELECT DISTINCT c.name, c.email
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id
WHERE p.price > 500;
  1. Calculate the rank of each product by price within its category (using RANK())
SELECT name, category, price,
       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank    
FROM products;
  1. Display the top 5 customers who have spent the most money (using LIMIT)
SELECT c.name, SUM(o.total_price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
ORDER BY total_spent DESC
LIMIT 5;
  1. Find the suppliers who have supplied the most products
SELECT s.name, COUNT(p.id) AS product_count
FROM suppliers s
JOIN products p ON s.id = p.supplier_id
GROUP BY s.id
ORDER BY product_count DESC
LIMIT 1;
  1. Find the customers who have made the most orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
ORDER BY order_count DESC
LIMIT 1;
  1. Find the suppliers who have supplied the most products
SELECT s.name, COUNT(p.id) AS product_count
FROM suppliers s
JOIN products p ON s.id = p.supplier_id
GROUP BY s.id
ORDER BY product_count DESC
LIMIT 1;