Databases

Practical Database Querying

Lecture 5: Real-world Practice with Northwind

Querying an unknown database, optimization & best practices

Training with Northwind and MySQL

For this session, work with a MySQL-compatible tool

Option 1: DBeaver (supports MySQL)
Option 2: MySQL Workbench (official MySQL tool)
Option 3: Command line: mysql -u root -p
Download a MySQL-compatible Northwind database script
The script needs to be adapted for MySQL syntax (remove PostgreSQL-specific features)

Importation in DBeaver

1.Once the tool is started, create a new connection
2.Then choose PostgreSQL

connection button

connection using pg

Importation in DBeaver (2)

3. then create the connection to the postgres db

Importation in DBeaver (3)

4. Then right click on postgres connection > create > Database
5. Then fill the required information

Importation in DBeaver (4)

Then open an SQL Console from the northwind connection and copy-paste the init script downloaded
Open an SQL Script to type a query
```
SELECT * FROM categories c
```
Check the results

Data extraction - 1


-- 1. list of customers
-- 2. number of different products?
-- 3. count of employees
-- 4. total overall revenue
-- 5. total revenue for one specific year
-- 6. list of countries covered by delivery
-- 7. list of available transporters
-- 8. number of customer per countries
-- 9. number of orders which are "ordered" but not shipped
-- 10. all the orders from france and belgium

Data extraction - 2


-- 11. most expensive products
-- 12. list of discontinued products
-- 13. count of product per category
-- 14. average order price
-- 15. revenue per category
-- 16. number of orders per shipper
-- 17. number of orders per employee
-- 18. total revenue per supplier

Data extraction - 3


-- 19. top 5 customers by total order value
-- 20. list all products that have never been ordered
-- 21. average order value per employee
-- 22. products with stock below reorder level
-- 23. customers who haven't placed orders in 1997
-- 24. list of employees with their manager names

Data extraction - 4


-- 25. most profitable product (highest total revenue)
-- 26. average delivery time (shipped_date - order_date) per shipper
-- 27. products that are out of stock (units_in_stock = 0)
-- 28. total quantity sold per product
-- 29. customers with the most orders
-- 30. revenue breakdown by year and quarter

Data insertions


-- 31. insert a product with its category
-- 32. create an order (what is required?)
-- 33. change the shipped delivery date
-- 34. add a new customer
-- 35. create an order with multiple order details

MySQL-Specific Features to Practice

Transactions: BEGIN, COMMIT, ROLLBACK
Stored Procedures: CREATE PROCEDURE
Triggers: CREATE TRIGGER for audit trails
Views: CREATE VIEW for frequently used queries
Indexes: CREATE INDEX for performance

-- Example: Create a view
CREATE VIEW customer_orders_summary AS
SELECT 
    c.customer_id,
    c.company_name,
    COUNT(o.order_id) as total_orders,
    SUM(od.unit_price * od.quantity) as total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.company_name;

Performance Optimization Exercise

Analyze and optimize the following query:

-- Before optimization
SELECT * FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
WHERE YEAR(o.order_date) = 1997;

Tasks:

Run EXPLAIN to see the execution plan
Identify missing indexes
Rewrite to select only needed columns
Consider adding appropriate indexes
Compare performance before/after

Final Challenge: Complex Report

Create a comprehensive sales report showing:

Monthly sales totals for each year
Year-over-year growth percentage
Top 3 products each month by revenue
Employee performance rankings
Customer retention rate

Requirements:

Use window functions (if MySQL 8.0+)
Include proper date handling
Handle NULL values appropriately
Format currency values
Add comments explaining your logic