Certainly! Let's go through the steps necessary to address all parts of the given problem systematically.
### Step 1: Creating the Database and Tables
We start by creating a database named `B_Sons_Reg` and then create two tables within this database: `Products` and `Sales`.
Tables structure:
1. Products:
- product_name: TEXT
- category: TEXT
- unit_price: REAL
- product_id: INTEGER PRIMARY KEY
2. Sales:
- sale_id: INTEGER PRIMARY KEY
- product_id: INTEGER (foreign key from Products table)
- quantity_sold: INTEGER
- sale_date: TEXT
- total_price: REAL
### Step 2: Inserting Data into the Tables
We will insert the data provided into the respective tables.
Products Table:
| product_name | category | unit_price | product_id |
|--------------|-------------|------------|------------|
| Laptop | Electronics | 500.00 | 101 |
| Smartphone | Electronics | 300.00 | 102 |
| Headphones | Electronics | 30.00 | 103 |
| Keyboard | Electronics | 20.00 | 104 |
| Mouse | Electronics | 15.00 | 105 |
Sales Table:
| sale_id | product_id | quantity_sold | sale_date | total_price |
|---------|------------|---------------|-------------|-------------|
| 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 |
### Step 3: Executing the Queries
#### Query 1: Retrieve Sale ID, Product ID, and Total Price from the Sales Table for Sales with a Quantity Sold Greater Than 4
SQL Query:
```sql
SELECT sale_id, product_id, total_price
FROM Sales
WHERE quantity_sold > 4;
```
Result:
```
sale_id | product_id | total_price
--------|------------|-------------
1 | 101 | 2500.00
5 | 105 | 90.00
```
#### Query 2: Retrieve the Product Name and Unit Price from the Products Table, Ordering the Result by Unit Price in Descending Order
SQL Query:
```sql
SELECT product_name, unit_price
FROM Products
ORDER BY unit_price DESC;
```
Result:
```
product_name | unit_price
-------------|------------
Laptop | 500.00
Smartphone | 300.00
Headphones | 30.00
Keyboard | 20.00
Mouse | 15.00
```
#### Query 3: Retrieve the Product Name and Unit Price from the Products Table, Filtering the Unit Price to Show Only Values Between \[tex]$20 and \$[/tex]600
SQL Query:
```sql
SELECT product_name, unit_price
FROM Products
WHERE unit_price BETWEEN 20 AND 600;
```
Result:
```
product_name | unit_price
-------------|------------
Laptop | 500.00
Smartphone | 300.00
Headphones | 30.00
Keyboard | 20.00
```
#### Query 4: Determine the Average Quantity Sold for Products with a Unit Price Greater Than \[tex]$100
SQL Query:
```sql
SELECT AVG(Sales.quantity_sold)
FROM Sales
JOIN Products ON Sales.product_id = Products.product_id
WHERE Products.unit_price > 100;
```
Result:
```
avg_quantity_sold
-----------------
4.0
```
#### Query 5: Identify Products with Total Sales Exceeding \$[/tex]500
SQL Query:
```sql
SELECT Products.product_name
FROM Products
JOIN Sales ON Products.product_id = Sales.product_id
GROUP BY Products.product_name
HAVING SUM(Sales.total_price) > 500;
```
Result:
```
product_name
------------
Laptop
Smartphone
```
### Summary of the Results
1. Sales with a quantity sold greater than 4:
- Sale ID 1, Product ID 101, Total Price [tex]$2500.00
- Sale ID 5, Product ID 105, Total Price $[/tex]90.00
2. Products ordered by unit price in descending order:
- Laptop ([tex]$500.00)
- Smartphone ($[/tex]300.00)
- Headphones ([tex]$30.00)
- Keyboard ($[/tex]20.00)
- Mouse ([tex]$15.00)
3. Products with unit price between $[/tex]20 and [tex]$600:
- Laptop ($[/tex]500.00)
- Smartphone ([tex]$300.00)
- Headphones ($[/tex]30.00)
- Keyboard ([tex]$20.00)
4. Average quantity sold for products with a unit price greater than $[/tex]100: 4.0
5. Products with total sales exceeding $500:
- Laptop
- Smartphone
