-- Create Sales table
CREATE TABLE Sales (
sale_id INT PRIMARY KEY,
product_id INT,
quantity_sold INT,
sale_date DATE,
total_price DECIMAL(10, 2));
-- Insert sample data into Sales table
INSERT INTO Sales (sale_id, product_id, quantity_sold, sale_date,
total_price) VALUES
(1, 101, 5, '2024-01-01', 2500.00),
(2, 102, 3, '2024-01-02', 900.00),
(3, 103, 2, '2024-01-02', 60.00),
(4, 104, 4, '2024-01-03', 80.00),
(5, 105, 6, '2024-01-03', 90.00);
-- Create Products table
CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
unit_price DECIMAL(10, 2)
);
-- Insert sample data into Products table
INSERT INTO Products (product_id, product_name, category, unit_price)
VALUES
(101, 'Laptop', 'Electronics', 500.00),
(102, 'Smartphone', 'Electronics', 300.00),
(103, 'Headphones', 'Electronics', 30.00),
(104, 'Keyboard', 'Electronics', 20.00),
(105, 'Mouse', 'Electronics', 15.00);
Answer the following queries:
1. Retrieve all columns from the Sales table.
2. Retrieve the product_name and unit_price from the Products table.
3. Retrieve the sale_id and sale_date from the Sales table.
4. Filter the Sales table to show only sales with a total_price greater than $100.
5. Filter the Products table to show only products in the 'Electronics' category.
6. Retrieve the sale_id and total_price from the Sales table for sales made on January 3, 2024.
7. Retrieve the product_id and product_name from the Products table for products with a unit_price
greater than $100.
8. Calculate the total revenue generated from all sales in the Sales table.
9. Calculate the average unit_price of products in the Products table.
10. Calculate the total quantity_sold from the Sales table.
11. Count Sales Per Day from the Sales table
12. Retrieve product_name and unit_price from the Products table with the Highest Unit Price
13. Retrieve the sale_id, product_id, and total_price from the Sales table for sales with a
quantity_sold greater than 4.
14. Retrieve the product_name and unit_price from the Products table, ordering the results by
unit_price in descending order.
15. Retrieve the total_price of all sales, rounding the values to two decimal places.
16. Calculate the average total_price of sales in the Sales table.
17. Retrieve the sale_id and sale_date from the Sales table, formatting the sale_date as 'YYYY-
MM-DD'.
18. Calculate the total revenue generated from sales of products in the 'Electronics' category.
19. Retrieve the product_name and unit_price from the Products table, filtering the unit_price to
show only values between $20 and $600.
20. Retrieve the product_name and category from the Products table, ordering the results by
category in ascending order.