-
Notifications
You must be signed in to change notification settings - Fork 0
/
Example
89 lines (69 loc) · 3.66 KB
/
Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
Thank you! Let's go through another example with a fresh scenario and different tables.
### Scenario:
We have two tables: `products` and `discounts`.
1. **`products` Table**: Contains product information, including the product name and price.
2. **`discounts` Table**: Contains discount ranges, mapping price ranges to different discount percentages.
### Data for Tables:
#### **`products` Table**:
| product_name | price |
|---------------|-------|
| Laptop | 1200 |
| Smartphone | 800 |
| Headphones | 150 |
| Monitor | 400 |
| Keyboard | 50 |
#### **`discounts` Table**:
| discount_rate | min_price | max_price |
|---------------|-----------|-----------|
| 25% | 1000 | 1500 |
| 15% | 500 | 999 |
| 10% | 100 | 499 |
| 5% | 0 | 99 |
### Query:
Now, let's write a query that:
- Joins the `products` and `discounts` tables based on the product prices and assigns the appropriate discount rate.
- Displays the `product_name`, `price`, and `discount_rate`.
- Sets the `product_name` to `NULL` if the discount is less than 15%.
- Orders the result by `discount_rate` in descending order and, within that, by the product price in ascending order.
```sql
SELECT
CASE
WHEN d.discount_rate >= '15%' THEN p.product_name
ELSE NULL
END AS product_name,
p.price,
d.discount_rate
FROM products p
JOIN discounts d
ON p.price >= d.min_price
AND p.price <= d.max_price
ORDER BY d.discount_rate DESC,
CASE WHEN d.discount_rate >= '15%' THEN p.product_name ELSE NULL END ASC,
p.price ASC;
```
### Explanation:
- **`JOIN`**: This joins the `products` table with the `discounts` table based on the price of the product falling within the price range (`min_price` to `max_price`).
- **`CASE` Statement**: If the discount rate is 15% or higher, the `product_name` is displayed; otherwise, `NULL` is shown.
- **`ORDER BY`**: First, the results are sorted by the `discount_rate` in descending order. Products with a discount rate of 15% or higher are sorted by their `product_name`. All products are then sorted by their `price` in ascending order.
### Joined Result:
| product_name | price | discount_rate |
|--------------|-------|---------------|
| Laptop | 1200 | 25% |
| NULL | 800 | 15% |
| NULL | 400 | 10% |
| NULL | 150 | 10% |
| NULL | 50 | 5% |
### Explanation of the Result:
1. **Laptop** (price: 1200) gets a `25%` discount (price range: 1000–1500).
- The `product_name` is displayed because the discount rate is higher than 15%.
2. **Smartphone** (price: 800) gets a `15%` discount (price range: 500–999).
- The `product_name` is set to `NULL` because the discount rate is exactly 15%.
3. **Monitor** (price: 400) gets a `10%` discount (price range: 100–499).
- The `product_name` is set to `NULL` because the discount rate is less than 15%.
4. **Headphones** (price: 150) also get a `10%` discount.
- The `product_name` is set to `NULL` for the same reason.
5. **Keyboard** (price: 50) gets a `5%` discount (price range: 0–99).
- The `product_name` is set to `NULL` because the discount is less than 15%.
### Conclusion:
This query shows how we can join two tables (products and discounts) based on ranges and use conditional logic to display or hide certain columns based on specific conditions (e.g., hiding the product name for lower discounts). The sorting ensures higher discounts are listed first, and products are ordered by price within the same discount range.
Let me know if you'd like further clarifications or adjustments!