Databases : understand how to organize and to use data

Querying an unknown database to extract useful data

Training with Northwind and DBeaver

For this session, it will be necessary to train with DBeaver, as it will be the tool used during the exam

download it and choose your preferred installation - portable zip works perfectly on Windows
Download this script Northwind database

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 insertions - 1


-- 19. insert a product with its category
-- 20. create an order (what is required?)
-- 21. change the shipped delivery date