Using MySQL DB : Northwind 1. List of Products whose listprice between 15 and 20

Question

Using MySQL

DB : Northwind

1. List of Products whose listprice between 15 and 20 sort by category

2. Display the shipping address (address, city, state, zip) of Sales Representative (Table Hint: Employees, Orders)

3. Display list of Product Names along with 'WhenToOrder' based on following condition.
    If difference between TargetLevel and ReorderLevel is greater than 50 units display WhenToOrder as 'After 50 Days'
    If difference between TargetLevel and ReorderLevel is greater than 30 units display WhenToOrder as 'After 30 Days'
    If difference between TargetLevel and ReorderLevel is greater than 20 units display WhenToOrder as 'After 20 Days'
    If difference between TargetLevel and ReorderLevel is less than 20 units display WhenToOrder as 'Order now'

4.Display Orders which has New status. (Hint : Orders and Order_status table)

5.Display unique categories in Products table.

6. Display customer details sorted by job title in following order

Owner
Purchasing Manager
Accounting Manager
Purchasing Representative
Purchasing Assistant
Accounting Assistant

7. In orders table display Order date in place of Shipped date when shipped date is not available

8. In orders table display "No Payment Type entered" when Payment type is missing

9. display orders which took more than 2 days to get shipped. (Hint: date function discussed in lab)

10. Query to find total orders.

Explanation / Answer

Hi friend, According to Chegg guidelicence, please post only 4 parts of a Big Queston in one post.

I have answered first 4 questions.

Please repost others in separate post.

Solution:

1)

SELECT productID FROM Products WHERE listPrice BETWEEN 15 AND 20

2)

SELECT shippingAddress FROM Orders INNER JOIN Employee WHERE salesRepresentative = 101

4)

SELECT orderNo FROM Orders INNER JOIN Order_status ON Order.orderNo = Order_status.odrderNo WHERE status = 1 (here we can consider that all the new orders are set to 1 to show the new status)

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.