Sure, let's walk through the solution step by step.
### 1. Create the Database and Tables
a. Create Database: Initially, you need to create a database named `B_Sons_Reg`.
b. Products Table: You will create a `Products` table that includes columns for `product_name`, `category`, and `unit_price`.
c. Sales Table: Create a `Sales` table that includes `sale_id`, `product_id`, `quantity_sold`, `sale_date`, and `total_price`.
### SQL Commands to Create Tables and Insert Data
Create the Tables:
```sql
CREATE DATABASE B_Sons_Reg;
USE B_Sons_Reg;
CREATE TABLE Products (
product_name VARCHAR(50),
category VARCHAR(50),
unit_price FLOAT
);
CREATE TABLE Sales (
sale_id INT,
product_id INT,
quantity_sold INT,
sale_date DATE,
total_price FLOAT
);
```
Insert the Data:
```sql
-- Insert data into Products table
INSERT INTO Products (product_name, category, unit_price) VALUES
('Laptop', 'Electronics', 500.00),
('Smartphone', 'Electronics', 300.00),
('Headphones', 'Electronics', 30.00),
('Mouse', 'Electronics', 15.00);
-- Insert 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);
```
### 2. Queries to Retrieve Data
a. Retrieve `sale_id`, `product_id`, and `total_price` for sales with `quantity_sold` greater than 4:
```sql
SELECT sale_id, product_id, total_price
FROM Sales
WHERE quantity_sold > 4;
```
b. Retrieve `product_name` and `unit_price` from the `Products` table, ordering the results by `unit_price` in descending order:
```sql
SELECT product_name, unit_price
FROM Products
ORDER BY unit_price DESC;
```
c. Retrieve `product_name` and `unit_price` from the `Products` table, filtering the `unit_price` to show only values between [tex]$20 and $[/tex]600:
```sql
SELECT product_name, unit_price
FROM Products
WHERE unit_price BETWEEN 20 AND 600;
```
d. Determine the average `quantity_sold` for products with a unit price greater than [tex]$100:
First, keep in mind that for this computation, it refers to the sales and the reference needs to be the `unit_price`. The incorrect use of `total_price` should be adjusted:
```sql
SELECT AVG(Sales.quantity_sold)
FROM Sales
JOIN Products ON Sales.product_id = Products.product_id
WHERE Products.unit_price > 100;
```
e. Identify products with total sales exceeding $[/tex]500:
```sql
SELECT product_id, SUM(total_price)
FROM Sales
GROUP BY product_id
HAVING SUM(total_price) > 500;
```
### Explanation:
- Creating Database and Tables: SQL commands to create the database and the tables are listed above.
- Inserting Data: Necessary data is inserted into the tables.
- Query Explanations:
- Query (a): Retrieves relevant sales information where the number of items sold is more than 4.
- Query (b): Lists the products ordered by their unit price in descending order.
- Query (c): Filters the products to show only those with unit prices between [tex]$20 and $[/tex]600.
- Query (d): Calculates the average quantity sold for products priced above [tex]$100.
- Query (e): Identifies products where the total sales exceed $[/tex]500.
This process collectively ensures the creation of the required database schema, population of data, and extraction of specific information based on the given conditions.
