/* 
Section 1
*/
drop table t;

create table t(
	nom text, 
	age int
);
-- on aurait aussi pu utiliser varchar(40) par exemple pour nom
alter table t add constraint pk_nom PRIMARY KEY(nom);

insert into t values('Marie',10);
insert into t values('Henri',5);
insert into t values('Alix',5);
insert into t values('Marie',4);
insert into t values('Foy',14);

/* on observe que la 2nde insertion de Marie échoue car elle viole la clé primaire.
Les autres insertions ne posent pas de problème parce que aucune contrainte n'interdit à 2 personnes d'avoir le même âge.
*/
update t set age = age+2 where nom = 'Henri';
update t set age = age+3 where nom = 'Henri';
-- au final, age est augmenté de 5.
delete from t where age >= 6 and age < 11;



delete from customers where customer_id = 'FISSA'
-- pas d'erreur malgré la clé étrangère car aucune ligne ne fais référence à cette ligne.

delete from customers where customer_id = 'LONEP'
-- la mise à jour échoue car elle viole une clé étrangère: une ligne de Order y fait référence.

/* 
Section 2
*/
-- Q1
select firstname, lastname, city
from employees
where country='UK';


/* attention aux majuscules, nécessaires dans la chaîne de caractère. 
*/


-- Q2
select distinct city from employees
;



-- Q3
select company_name
from suppliers s, products p
where s.supplier_id = p.supplier_id
	and product_name = 'Chartreuse verte'
;

select company_name
from suppliers s
join products p on s.supplier_id = p.supplier_id 
where product_name = 'Chartreuse verte'
;

-- Q4
select distinct company_name, category_name
from suppliers s, products p, categories c
where s.supplier_id = p.supplier_id 
	and c.category_id = p.category_id
order by category_name desc, company_name
;


-- Q5
select distinct company_name
from suppliers s, products p, categories c
where s.supplier_id = p.supplier_id 
	and c.category_id = p.category_id
	and (category_name = 'Seafood' OR category_name = 'Beverages')
order by company_name
;

/* Avec la syntaxe JOIN... ON ... on a moins souvent à se soucier de priorité des opérations
pour les conditions dans le WHERE : */
select distinct company_name
from suppliers s
join products p on s.supplier_id = p.supplier_id 
join categories c on c.category_id = p.category_id
where category_name = 'Seafood' OR category_name = 'Beverages'
order by company_name
;

-- Q6
select company_name
from suppliers s
join products p on s.supplier_id = p.supplier_id 
join categories c on c.category_id = p.category_id
where category_name = 'Seafood'
except
select company_name
from suppliers s
join products p on s.supplier_id = p.supplier_id 
join categories c on c.category_id = p.category_id
where category_name = 'Beverages'
order by company_name
;


-- Q7
select company_name
from suppliers s
join products p on s.supplier_id = p.supplier_id 
join categories c on c.category_id = p.category_id
where category_name = 'Seafood'
intersect
select company_name
from suppliers s
join products p on s.supplier_id = p.supplier_id 
join categories c on c.category_id = p.category_id
where category_name = 'Beverages'
order by company_name
;

select company_name
from suppliers s
join products p on s.supplier_id = p.supplier_id 
join categories c on c.category_id = p.category_id
join products p2 on s.supplier_id = p2.supplier_id 
join categories c2 on c2.category_id = p2.category_id
where c.category_name = 'Seafood'
	and c2.category_name = 'Beverages'
order by company_name
;



-- Q8
select company_name, p.unit_price + p2.unit_price amount
from suppliers s
join products p on s.supplier_id = p.supplier_id 
join categories c on c.category_id = p.category_id
join products p2 on s.supplier_id = p2.supplier_id 
join categories c2 on c2.category_id = p2.category_id
where c.category_name = 'Seafood'
	and c2.category_name = 'Beverages'
order by company_name
;

-- Q9
select count(*) nb_employes from employees
;
-- Q10
select count(distinct city) nb_villes, 
	count(distinct country) nb_pays 
from employees
;

-- Q11
select COUNTRY, count(*) nb_employes, 
	count(REGION) nb_employes_region 
from EMPLOYEES
group by country
;

-- Q12
select max(unit_price) prix from products
;

-- Q13
select  p1.product_name, p1.unit_price prix
from products p1, 
	(select max(unit_price) maxprice from products) p2
where p1.unit_price = p2.maxprice
;

select product_name, unit_price prix
from products 
order by unit_price desc limit 1
;

-- Q14
select sum(od.unit_price * (1-discount) * quantity) montant
from order_details od
;


-- Q15
select cu.country customer_country, 
	s.country supplier_country, 
	c.category_name category, 
	count(distinct o.order_id) nb_commandes, 
	sum(od.unit_price * (1-discount) * quantity) montant
from suppliers s
join products p on s.supplier_id = p.supplier_id 
join categories c on c.category_id = p.category_id
join order_details od on p.product_id = od.product_id
join orders o on o.order_id = od.order_id
join customers cu on cu.customer_id = o.customer_id
group by cu.country, s.country, c.category_name 
;

-- Q16
select count(fax) from customers
;

-- Q17
select 'fax' fax, count(*) nb from customers where fax is not null
union
select 'no fax' fax, count(*) nb from customers where fax is null
;


select case when fax is not null then 'fax' else 'no fax' end fax, count(*) 
from customers
group by case when fax is not null then 'fax' else 'no fax' end
;

select 'fax' fax, count(fax) nb from customers
union
select 'no fax' fax, count(*)-count(fax) nb
;

-- Q18
select contact_name 
from customers 
where country='France' and postal_code like '44%' and fax is not null;
;

-- Q19
select lastname, count(*) nb
from employees e
join orders o on e.employee_id = o.employee_id
group by e.employee_id, e.lastname
having count(*) >= 50
order by lastname, nb desc
;

-- Q20
select PRODUCTS.PRODUCT_NAME, count(distinct ORDER_ID) nb 
from PRODUCTS 
left outer join ORDER_DETAILS on PRODUCTS.PRODUCT_ID = ORDER_DETAILS.PRODUCT_ID
group by PRODUCTS.PRODUCT_ID, PRODUCTS.PRODUCT_NAME
order by PRODUCTS.PRODUCT_NAME

