---
tags: databases, mysql, northwind, solutions
---

# Northwind Practical - Some solutions

> ### Please note: :information_source:
> These are *some* solutions to the questions, but not the only solutions.
> If you have another soloution, **double check that your solution and ours yield the same result and answer**. If it does, then _congratulations :confetti_ball:!!!_ You found another solution. It doesn't mean your solution isn't right. SQL is a very expressive and allowing language, thus it makes it possible to solve the same problem in many ways.

___

### Table of contents

[TOC]

___

### Q1: Show list of all discontinued products

SELECT 
    *
FROM
    products
WHERE
    discontinued > 0;

:information_source: You can query the metadata too... so for example if the question had asked for discounted products and I wanted to see if there are any columns with discount in them in northwind and which table they are in etc. then I can run ...

SELECT 
    *
FROM
    information_schema.columns
WHERE
	COLUMN_NAME LIKE '%discontinued%'

___

### Q2: Show a list of products more expensive than 10$, order from most expensive to cheapest

SELECT 
    product_name, list_price
FROM
    products p
WHERE
    list_price > 10
ORDER BY list_price DESC;

___

### Q3: What is the most expensive product listed

You can use order by desc and limit 1 but just wanted to show you that you can use a 'sub-query' in your WHERE clause if you want to.

SELECT 
    product_name
FROM
    products
WHERE
    list_price = (SELECT 
            MAX(list_price)
        FROM
            products);

___

### Q4: What is the least expensive product listed

SELECT 
    product_name
FROM
    products
WHERE
    list_price = (SELECT 
            MIN(list_price)
        FROM
            products);

___

### Q5: How much are the most expensive products in each category

SELECT 
    MAX(list_price) AS max_price, category
FROM
    products
GROUP BY category
ORDER BY max_price;

___

### Q6: What is the company name of the supplier of the cheapest product
Here we are using a subquery in the filter.

SELECT DISTINCT
    s.company
FROM
    products p
        JOIN
    purchase_orders po ON p.supplier_id = po.supplier_id
        JOIN
    suppliers s ON s.id = po.supplier_id
WHERE
    p.list_price = (SELECT MIN(list_price) FROM products);

___

### Q7: Show a list of employees who work in Sales

SELECT 
    *
FROM
    employees e
WHERE
    LOWER(job_title) LIKE '%sales%';

___

### Q8: How many employees exist with job roles below Sales Manager

Have a look at the distinct job titles

SELECT DISTINCT job_title FROM employees;

Use that to form your query

SELECT 
    COUNT(*)
FROM
    employees
WHERE
    job_title NOT IN ('Vice President, Sales' , 'Sales Manager');

___

### Q9: Show number of purchase orders created in the last month

SELECT 
    COUNT(*)
FROM
    purchase_orders
WHERE
    creation_date >= NOW() - INTERVAL '1 MONTH';

___

### Q10: Show the total paid to suppliers in the last month

SELECT 
    COALESCE(SUM(COALESCE(payment_amount, 0)), 0) as total_paymount_amount
FROM
    purchase_orders
WHERE

    payment_date >=NOW() - INTERVAL '1 MONTH';