With this practice set, you'll get thorough with:
- How to filter based on multiple conditions and how to apply filters within a specified range.
- Sort and limit the entries of the output making it easier to fetch meaningful insights.
Here, the database consists of a
product
table that stores thename
,category
,price
,rating
andbrand
of the products. Let's go!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 products from "Roadster", "Levi's" or "Puma" brands.
Note: Output must contain rows in the ascending order ofpriceof the product.
2.
Get all the products that are in the price range of 750 to 1000.
Note:
- Include the products with price 750 and 1000 as well.
- Output must contain rows in the ascending order ofpriceof the product. If two products are of same price, then sort bynamein the alphabetical order.
3.
User wants to purchase clothes that have high-rating and low-cost.
Get the products from the "Clothing" category as per the user requirements, i.e.,
Keep highly-rated products at the top
Sort the price from low to high
4.
List all the brands available in the ascending order.
Note: Output must contain rows in the alphabetical order of name of thebrand
Expected Output Format:
brand
...
5.
User has checked first 10 products keeping highly-rated products at the top. Help the user check the details of next 10 products.
Note: If two products have the same rating, then sort bynamein the alphabetical order.
6.
Get all the details of the products from producttable whose
brand is Puma or Denim and
rating greater than 4
Get products with lowest price first
| 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 |