Consider an e-commerce company like Amazon that holds the data of a wide variety of products. On such platforms, users often search for products by appling multiple conditions at once.
For example, user could ask for shoes from Puma brand, that have ratings greater than 4.0 (and price less than 5000).
With logical operators, we can perform queries based on multiple conditions.
Similar to the e-commerce scenario, we have a database that contains a range of products with details like the name of the product, category it belongs to, price, brand and rating. Help the user get the desired products by writing SQL queries satisfying user requirements.
Note: Expected output format for all the queries, unless specified.
| name | category | price | brand | rating |
|---|---|---|---|---|
| Black Shirt | Clothing | 900 | Puma | 4.8 |
| --- | --- | --- | --- | --- |
1. Get all the details of products that belong to "Clothing" category and price less than 700.
2. Get all the details of products that belong to "Denim" brand with rating greater than 4.
3. Get all the best-rated products with price less than or equal to 1000.
Note: Assume that the products with rating greater than 4.0 as best rated products.
4. Get all the products from the product table whose
rating is above 3.6 and
price is less than 1000 and
Belongs to "Puma" brand
5. Get all the products from "Puma", "Denim" or "Nike" brands.
6. Get all the details of products whose
brand is "Redmi" with rating greater than 4 or
products that belong to "OnePlus" brand.
7. Get only cakes from "Cadbury" and "Britannia" brands that have rating greater than 4.0.
Note: Consider the products that have "Cake" innameas cakes.
8. Get all the details of the products from the product table whose
brand is "Puma" and rating greater than 3.5 or
brand is "Denim" and rating greater than 4.0
9. Get all the shirts available in "Puma" , "Nike" or "Levi's" brands, excluding the black colour shirts.
Note:
You can assume that:
1. Products containing “Shirt” in name are considered as shirts.
2. Black colour shirts contain "Black" in their names.
| name | category | price | brand | rating |
|---|---|---|---|---|
| Blue Shirt | Clothing | 750 | Denim | 3.8 |
| Blue Jeans | Clothing | 800 | Puma | 3.6 |
| Black Jeans | Clothing | 750 | Denim | 4.5 |
| Blue Shirt | Clothing | 1000 | Puma | 4.3 |
| Chocolate Cake | Food | 25 | Britannia | 3.7 |
| Strawberry Cake | Food | 60 | Cadbury | 4.1 |
| Chocolate Cake | Food | 60 | Cadbury | 2.5 |
| Strawberry Cake | Food | 10 | Britannia | 4.6 |
| Smart Watch | Gadgets | 17000 | Apple | 4.9 |
| Smart Cam | Gadgets | 2600 | Realme | 4.7 |
| Smart TV | Gadgets | 40000 | Sony | 4 |
| Bourbon Small | Food | 10 | Britannia | 3.9 |
| Bourbon Special | Food | 15 | Britannia | 4.6 |
| Bourbon With Extra Cookies | Food | 30 | Britannia | 4.4 |
| White Shirt | Clothing | 700 | Denim | 4.3 |
| Black Shirt | Clothing | 600 | Puma | 4.8 |
| Black T-Shirt | Clothing | 600 | Roadster | 4.2 |
| White T-Shirt | Clothing | 700 | Levi's | 4 |
| Blue T-Shirt | Clothing | 600 | Nike | 4.7 |
| Realme Smart Band | Gadgets | 3000 | Realme | 4.6 |
| Raw Cashew | Food | 370 | Absa | 3.9 |
| Cashew Nuts | Food | 550 | Upcrop | 4.3 |
| Chocolate Cashew | Food | 670 | Urban Platter | 3.5 |
| Potato Chips India’s Magic Masala | Food | 42 | Lay's | 4.4 |
| Banana Chips | Food | 550 | Calicut Kerala | 4.3 |
| Potato Chips Cream & onion | Food | 63 | Lay's | 4.5 |
| Potato Chips Classic Salted | Food | 45 | Lay's | 4 |
| Harry Potter and the Philosopher's Stone | Novel | 222 | 4.7 | |
| Harry Potter and the Chamber of Secrets | Novel | 343 | 4.4 | |
| Harry Potter and the Prisoner of Azkaban | Novel | 284 | 4.2 | |
| Harry Potter and the Goblet of Fire | Novel | 431 | 4.6 | |
| OnePlus 6T | Smartphone | 32990 | OnePlus | 4.5 |
| Redmi K20 | Smartphone | 24999 | Redmi | 4.1 |