Skip to content

Latest commit

 

History

History
80 lines (70 loc) · 4.51 KB

README.md

File metadata and controls

80 lines (70 loc) · 4.51 KB

Northwind Database - MySQL version

Description

The Northwind database is a sample database that was originally created by Microsoft and used as the basis for their tutorials in a variety of database products. The database contains the sales data for a fictitious company called 'Northwind Traders', which imports and exports specialty foods from around the world. The Northwind is an excellent tutorial schema for a small-business ERP, with customers, orders, inventory, purchasing, suppliers, shipping, employees, and single-entry accounting.

I created this sample Northwind Database for beginners who want learn about MySQL from W3Schools. Since I work with MySQL, so I modify the syntax accordingly. I work with MySQL version 8.0.27.

There are several differences:

  • in W3Schools, CustomerID column in Customers table uses SMALLINT as datatype, but here I use CHAR(5) following the original database.
  • there are 2 tables I didn't include, customer_customer_demo and customer_demographics, because both tables are empty and contain no data, so in my opinion, there is no need to add them into the database.

ER Diagram

Query Examples

  • Top 5 employees (first_name, last_name) with highest amount of orders they managed.

    • SELECT e.first_name, e.last_name, COUNT(o.order_id) AS total_orders
FROM employees e
INNER JOIN orders o ON e.employee_id = o.employee_id
GROUP BY o.employee_id
ORDER BY total_orders DESC
LIMIT 5;`



  • Same with query above, but divide employees based on their performance. 'Great' if total orders >= 100; 'Average' if total orders between 50 to 99; 'Poor' if total orders < 50.

    • SELECT first_name, last_name, total_orders,
CASE
    WHEN total_orders >= 100 THEN 'Great'
    WHEN total_orders < 100 AND total_orders >= 50 THEN 'Average'
    WHEN total_orders < 50 THEN 'Poor'
END AS performance
FROM (
	SELECT e.first_name, e.last_name, COUNT(o.order_id) AS total_orders
	FROM employees e
	INNER JOIN orders o ON e.employee_id = o.employee_id
	GROUP BY o.employee_id) AS new_table
ORDER BY total_orders desc;



  • Customers' details (id, name, contact, address, city, country) whose orders managed by employee Laura Callahan.

    • SELECT DISTINCT c.customer_id, c.customer_name, c.contact_name, c.address, c.city, c.country
FROM ((customers c
INNER JOIN orders o ON c.customer_id = o.customer_id)
INNER JOIN employees e ON o.employee_id = e.employee_id and e.last_name='Callahan' AND e.first_name='Laura')
ORDER BY c.customer_id;



  • Details of products (id, name, quantity per unit, unit price) ordered on August 1996 sort by unit price descending.

    • SELECT DISTINCT p.product_id, p.product_name, p.quantity_per_unit, p.unit_price
FROM ((products p
INNER JOIN order_details od on p.product_id = od.product_id)
INNER JOIN orders o ON od.order_id = o.order_id AND o.order_date BETWEEN '1996-08-01' AND '1996-08-30')
ORDER BY p.unit_price DESC;



  • Sales and Net-Sales of product Ipoh Coffee sold from September 1996 to December 1996

    • SELECT o.order_id, o.order_date, ROUND((od.unit_price * od.quantity), 2) as sales, ROUND(((od.unit_price * od.quantity) - (od.discount * od.unit_price * od.quantity)), 2) as net_sales
FROM ((order_details od
INNER JOIN products p ON od.product_id = p.product_id AND p.product_name = 'Ipoh Coffee')
INNER JOIN orders o ON od.order_id = o.order_id AND o.order_date BETWEEN '1996-09-01' AND '1996-12-31');



  • Total Net-Sales of query above

    • SELECT p.product_name, ROUND(SUM((od.unit_price * od.quantity) - (od.discount * od.unit_price * od.quantity)), 2) as total_sales
FROM ((order_details od
INNER JOIN products p ON od.product_id = p.product_id AND p.product_name = 'Ipoh Coffee')
INNER JOIN orders o ON od.order_id = o.order_id AND o.order_date BETWEEN '1996-09-01' AND '1996-12-31');