MySQL Northwind database, Products table: Display Product list where current products cost less than $20
MySQL Northwind database: Exercise-5 with Solution
5. Write a query to get Product list (id, name, unit price) where current products cost less than $20.
Code:
SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE (((UnitPrice)<20) AND ((Discontinued)=False))
ORDER BY UnitPrice DESC;
Relational Algebra Expression:
Relational Algebra Tree:
Structure of Products table:
Sample records of Products Table:
+-----------+---------------------------------+------------+------------+---------------------+-----------+--------------+--------------+--------------+--------------+ | ProductID | ProductName | SupplierID | CategoryID | QuantityPerUnit | UnitPrice | UnitsInStock | UnitsOnOrder | ReorderLevel | Discontinued | +-----------+---------------------------------+------------+------------+---------------------+-----------+--------------+--------------+--------------+--------------+ | 1 | Chai | 1 | 1 | 10 boxes x 20 bags | 18.0000 | 39 | 0 | 10 | | | 2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19.0000 | 17 | 40 | 25 | | | 3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10.0000 | 13 | 70 | 25 | | | 4 | Chef Anton's Cajun Seasoning | 2 | 2 | 48 - 6 oz jars | 22.0000 | 53 | 0 | 0 | | | 5 | Chef Anton's Gumbo Mix | 2 | 2 | 36 boxes | 21.3500 | 0 | 0 | 0 | ☺ | | 6 | Grandma's Boysenberry Spread | 3 | 2 | 12 - 8 oz jars | 25.0000 | 120 | 0 | 25 | | | 7 | Uncle Bob's Organic Dried Pears | 3 | 7 | 12 - 1 lb pkgs. | 30.0000 | 15 | 0 | 10 | | | 8 | Northwoods Cranberry Sauce | 3 | 2 | 12 - 12 oz jars | 40.0000 | 6 | 0 | 0 | | | 9 | Mishi Kobe Niku | 4 | 6 | 18 - 500 g pkgs. | 97.0000 | 29 | 0 | 0 | ☺ | | 10 | Ikura | 4 | 8 | 12 - 200 ml jars | 31.0000 | 31 | 0 | 0 | | | 11 | Queso Cabrales | 5 | 4 | 1 kg pkg. | 21.0000 | 22 | 30 | 30 | | | 12 | Queso Manchego La Pastora | 5 | 4 | 10 - 500 g pkgs. | 38.0000 | 86 | 0 | 0 | | | 13 | Konbu | 6 | 8 | 2 kg box | 6.0000 | 24 | 0 | 5 | | | 14 | Tofu | 6 | 7 | 40 - 100 g pkgs. | 23.2500 | 35 | 0 | 0 | | | 15 | Genen Shouyu | 6 | 2 | 24 - 250 ml bottles | 15.5000 | 39 | 0 | 5 | | .... +-----------+---------------------------------+------------+------------+---------------------+-----------+--------------+--------------+--------------+--------------+
Sample Output:
ProductID ProductName UnitPrice 57 Ravioli Angelo 19.5000 44 Gula Malacca 19.4500 2 Chang 19.0000 36 Inlagd Sill 19.0000 40 Boston Crab Meat 18.4000 76 Lakkalikri 18.0000 1 Chai 18.0000 39 Chartreuse verte 18.0000 35 Steeleye Stout 18.0000 16 Pavlova 17.4500 66 Louisiana Hot Spiced Okra 17.0000 50 Valkoinen suklaa 16.2500 15 Genen Shouyu 15.5000 73 Rd Kaviar 15.0000 70 Outback Lager 15.0000 67 Laughing Lumberjack Lager 14.0000 25 NuNuCa Nu-Nougat-Creme 14.0000 34 Sasquatch Ale 14.0000 58 Escargots de Bourgogne 13.2500 77 Original Frankfurter grne Soe 13.0000 48 Chocolade 12.7500 31 Gorgonzola Telino 12.5000 68 Scottish Longbreads 12.5000 46 Spegesild 12.0000 3 Aniseed Syrup 10.0000 21 Sir Rodney's Scones 10.0000 74 Longlife Tofu 10.0000 41 Jack's New England Clam Chowder 9.6500 45 Rogede sild 9.5000 47 Zaanse koeken 9.5000 19 Teatime Chocolate Biscuits 9.2000 23 Tunnbrd 9.0000 75 Rhnbru Klosterbier 7.7500 54 Tourtire 7.4500 52 Filo Mix 7.0000 13 Konbu 6.0000 33 Geitost 2.5000
MySQL Online Editor
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework