# SQL - PostgreSQL

## Binôme:

* Bunel Maxime
* Molinares Diogenes

## Import de données

>sudo -u postgres psql

>CREATE ROLE chinookuser LOGIN PASSWORD ‘password’;
CREATE DATABASE chinook OWNER chinookuser ;

>gunzip -c chinook.sql.gz | psql -U chinookuser --password -h localhost chinook

## Jointure latérale

>Pour chaque employé (2 colonnes, nom et prénom), vous fournirez les données de son•a supérieur•e direct•e (nom et prénom aussi, sur 2 colonnes).

select 
    e.last_name as "Nom", 
    e.first_name as "Prenom", 
    s.last_name as "Nom superieur",
    s.first_name as "Prenom superieur"
from employees e ,
lateral 
    (select * from employees e2 
     where e.reports_to = e2.id)
     as s;

#### Resultat

   Nom    |  Prenom  | Nom superieur | Prenom superieur 
----------+----------+---------------+------------------
 Edwards  | Nancy    | Adams         | Andrew
 Peacock  | Jane     | Edwards       | Nancy
 Park     | Margaret | Edwards       | Nancy
 Johnson  | Steve    | Edwards       | Nancy
 Mitchell | Michael  | Adams         | Andrew
 King     | Robert   | Mitchell      | Michael
 Callahan | Laura    | Mitchell      | Michael

## Aggrégations avancées

#### Grouping sets

> Calculer le total de chaque facture
>

select i.id, sum(i.total) from invoices i group by i.id;

#### Resultat

 id  |  sum  
-----+-------
 384 |  0.99
 351 |  1.98
 184 |  3.96
 116 |  8.91
  87 |  6.94
 273 |  1.98
 394 |  3.96
  51 |  3.96
 272 |  0.99
  70 |  1.98

>Calculer le total de toutes les factures pour chaque mois de l’année 2012

select 
	extract (MONTH from i.invoice_date) as month,
	sum(i.total)
	from invoices i
where 
	i.invoice_date::date >= '2012-01-01'
	and 
	i.invoice_date::date <= '2012-12-31'
group by grouping sets ((month ),())
order by month;

#### Resultat

 month |  sum   
-------+--------
     1 |  37.62
     2 |  37.62
     3 |  37.62
     4 |  37.62
     5 |  37.62
     6 |  37.62
     7 |  39.62
     8 |  47.62
     9 |  46.71
    10 |  42.62
    11 |  37.62
    12 |  37.62
       | 477.53
(13 rows)

#### Window functions
> Récupérer en une seule requête pour chaque client son nom, son prénom ainsi que le nombre total de clients.

select distinct 
	c.first_name ,
	c.last_name ,
	count(*) 
over()
from customers c 

#### Resultat

first_name |  last_name   | count  |
------------+--------------+------- 
Kathy      | Chase        |    59 
Edward     | Francis      |    59 
Enrique    | Muñoz        |    59 
Victor     | Stevens      |    59 
Heather    | Leacock      |    59 
Ellie      | Sullivan     |    59 
Puja       | Srivastava   |    59 
Julia      | Barnett      |    59 
Wyatt      | Girard       |    59 
Dominique  | Lefebvre     |    59 
Frank      | Harris       |    59 
Hannah     | Schneider    |    59 
Jennifer   | Peterson     |    59 
Astrid     | Gruber       |    59 
Emma       | Jones        |    59 
Johannes   | Van der Berg |    59 
Dan        | Miller       |    59

### Recursive CTE
>Trouver tous les employés qui sont sous les ordres de “Andrew” (FirstName) “Adams” (LastName) directement ou indirectement.
>

with recursive rec(id,last_name,first_name,report_to) as (
select 
	e.id as id ,
	e.last_name as last_name  ,
	e.first_name as first_name  ,
	e.reports_to as reports_to 
from employees e 
where e.last_name  = 'Adams' and e.first_name  = 'Andrew'
union 
	select 
		e2.id as id ,
		e2.last_name as last_name  ,
		e2.first_name as first_name  ,
		e2.reports_to as reports_to 
	from rec r join employees e2 on e2.reports_to = r.id
)
select * from rec

#### Resultat

 id | last_name | first_name | report_to 
----+-----------+------------+-----------
  1 | Adams     | Andrew     |          
  2 | Edwards   | Nancy      |         1
  6 | Mitchell  | Michael    |         1
  3 | Peacock   | Jane       |         2
  4 | Park      | Margaret   |         2
  5 | Johnson   | Steve      |         2
  7 | King      | Robert     |         6
  8 | Callahan  | Laura      |         6
(8 rows)