/*
Correction des exercices sur la base  de données chinook
*/

--1 les attributs des clients non aux USA
select FirstName, 
    LastName, 
    CustomerId, 
    Country 
from customers 
where country not in('USA');

--------------------------------------------------------

--2 les attributs des clients au Brésil
select FirstName, 
    LastName, 
    CustomerId, 
    Country 
from customers 
where country='Brazil';

---------------------------------------------------------

--3 les clients au Brésil et leurs factures 
select c.FirstName, 
    c.LastName, 
    i.InvoiceId, 
    i.InvoiceDate, 
    i.BillingCountry 
from customers c 
    join invoices i 
        on i.CustomerId=c.CustomerId 
            and c.Country='Brazil';
            
-- ou bien :
select c.FirstName, 
    c.LastName, 
    i.InvoiceId, 
    i.InvoiceDate, 
    i.BillingCountry 
from customers c 
    join invoices i 
        on i.CustomerId=c.CustomerId 
where c.Country='Brazil';

-- ou encore
select c.FirstName, 
    c.LastName, 
    i.InvoiceId, 
    i.InvoiceDate, 
    i.BillingCountry 
from customers c, invoices i 
where i.CustomerId=c.CustomerId 
    and c.Country='Brazil';
    
---------------------------------------------------------

--4 les employés du département des ventes
select * 
from employees 
where title like 'Sales%';

---------------------------------------------------------

--5 les différents pays où des factures ont été émises
select distinct BillingCountry 
from invoices;

---------------------------------------------------------

--6 les clients du Brésil et leurs factures
select * 
from customers c 
	join invoices i 
        on i.CustomerId=c.CustomerId 
            and c.Country='Brazil';

---------------------------------------------------------

--7 Les factures associées à chaque agent commercial.
-- Malheureusement, on ne peut pas utiliser un GROUP BY
-- Tout ce qu'on peut faire est d'ordonner au mieux ...

select e.firstname, 
    e.lastname, 
    i.*
from customer as c
    join invoices as i
        on c.customerid = i.customerid
    join employees as e
        on e.employeeid = c.supportrepid
order by e.employeeid;

---------------------------------------------------------

--8 total de facture, nom client, pays, agent commercial  pour tous les (facture+client)


select e.firstname as 'prenom_employe', 
    e.lastname as 'nom_employe', 
    c.firstname as 'client_prenom', 
    c.lastname as 'client_nom', 
    c.country, 
    i.total
from employees as e
	join customers as c 
        on e.employeeid = c.supportrepid
	join invoices as i 
        on c.customerid = i.customerid

---------------------------------------------------------

--9 How many Invoices were there in 2009 and 2011? 
--  What are the respective total sales for each of those years?
select strftime('%Y',InvoiceDate) as annee ,
	count(*), 
	sum(Total) 
from invoices 
where annee in('2009','2011') 
group by annee;

-- ou bien 

select count(i.invoiceid), sum(i.total)
from invoice as i
where i.invoicedate between datetime('2011-01-01 00:00:00') and datetime('2011-12-31 00:00:00');

select count(i.invoiceid), sum(i.total)
from invoice as i
where i.invoicedate between datetime('2009-01-01 00:00:00') and datetime('2009-12-31 00:00:00');


---------------------------------------------------------

--10  Le nombre de lignes de facture pour la facture 37
select count(*) 
from invoice_items 
where InvoiceId=37;

---------------------------------------------------------

--11  le nombre de lignes de factures pour chaque  facture
select InvoiceId,
	count(*) 
from invoice_items 
group by InvoiceId;

---------------------------------------------------------

--12  Rajouter à chaque ligne de commande, le nom de la piste
select i.*,
	t.Name 
from invoice_items i 
	join tracks t 
        on i.TrackId=t.TrackId 
order by i.InvoiceLineId;

---------------------------------------------------------

--13 Même chose, plus le nom de l'artiste
select i.*,
	t.Name,
	ar.Name 
from invoice_items i 
	join tracks t 
        on i.TrackId=t.TrackId 
    join albums a 
        on t.AlbumId=a.albumId 
    join artists ar 
        on a.ArtistId=ar.ArtistId 
order by i.InvoiceLineId;

---------------------------------------------------------

--14  Le nombre de factures par pays
select BillingCountry , 
	count(*) 
from invoices 
group by BillingCountry;

---------------------------------------------------------

--15 pour chaque playlist, son nom et son nombre de pistes
-- on notera l'utilisation d'une jointure externe à gauche
-- pour tenir compte des playlists vides
select p.Name, 
	count(pt.PLaylistId) 
from playlists p 
	left join playlist_track pt 
        on p.PlaylistId=pt.PLaylistId 
group by p.PlaylistId;

---------------------------------------------------------

--16 pour chaque piste, toutes ses info, plus le titre de l'album,
-- le genre, et le type de média

select t.Name as 'track', 
    t.Composer, 
    t.milliseconds, 
    t.Bytes, 
    t.UnitPrice, 
    a.title as 'album', 
    g.Name as 'genre', 
    m.Name as 'media type'
from tracks as t
	join albums as a 
        on a.albumid = t.albumid
	join genres as g 
        on g.genreid = t.genreid
	join media_types as m 
        on m.mediatypeid = t.mediatypeid 
order by t.TrackId;

---------------------------------------------------------

--17 Totes les factures détaillées, avec leur nb de lignes de commande

select i.*, 
    count(InvoiceLineId)
from invoices i 
    join invoice_items it 
        on i.InvoiceId=it.InvoiceId 
group by i.InvoiceId;

---------------------------------------------------------

--18  le total des ventes effectuées par chaque agent commercial

select e.EmployeeId, 
    e.FirstName, 
    e.LastName, 
    sum(i.Total) 
from employees  e 
    join customers c 
        on e.EmployeeId=c.SupportRepId 
    join invoices i 
        on i.CustomerId=c.CustomerId 
group by e.EmployeeId;

---------------------------------------------------------

--19 l'agent commercial qui a fait le plus gros chiffre d'affaire en 2009

select e.EmployeeId, 
    e.FirstName, 
    e.LastName, 
    sum(i.Total) as totalsales 
from employees  e 
    join customers c on e.EmployeeId=c.SupportRepId 
    join invoices i on i.CustomerId=c.CustomerId 
where strftime('%Y',i.InvoiceDate)='2009'  
-- ou bien
-- where i.invoicedate between '2009-01-00' and '2009-12-31'
group by e.EmployeeId 
order by totalsales DESC 
limit 1;

---------------------------------------------------------

--20 idem en 2010   (intérêt ???)

select e.EmployeeId, 
    e.FirstName, 
    e.LastName, 
    sum(i.Total) as totalsales 
from employees  e 
    join customers c 
        on e.EmployeeId=c.SupportRepId 
    join invoices i 
        on i.CustomerId=c.CustomerId 
where strftime('%Y',i.InvoiceDate)='2010'  
group by e.EmployeeId 
order by totalsales DESC 
limit 1;

---------------------------------------------------------

--21 l'agent commercial qui a fait globalement le plus gros chiffre d'affaire

select e.EmployeeId, 
    e.FirstName, 
    e.LastName, 
    sum(i.Total) as totalsales 
from employees  e 
    join customers c 
        on e.EmployeeId=c.SupportRepId 
    join invoices i 
        on i.CustomerId=c.CustomerId 
group by e.EmployeeId 
order by totalsales DESC 
limit 1;

---------------------------------------------------------

--22 nb de clients assignés à chaque agent

select SupportRepId,
    count(*) 
from customers 
group by SupportRepId;

---------------------------------------------------------

--23 le pays qui a dépensé le plus 

select BillingCountry, 
    sum(Total) as somme  
from invoices 
group by BillingCountry 
order by somme desc 
limit 1;

---------------------------------------------------------

--24 le morceau le plus vendu en 2013

select t.Name, 
    sum(InvoiceLineId) as nombre  
from invoice_items ii 
    join tracks t 
        on t.TrackId=ii.TrackId 
    join invoices i
        on i.InvoiceId=ii.InvoiceId 
where strftime('%Y',i.InvoiceDate)='2010' 
group by t.trackId 
order by nombre desc 
limit 1;

---------------------------------------------------------

--25 les 5 morceaux les plus vendus de l'histoire

select t.Name, 
    sum(InvoiceLineId) as nombre  
from invoice_items i 
    join tracks t 
        on t.TrackId=i.TrackId 
group by t.trackId 
order by nombre desc 
limit 5;

---------------------------------------------------------

--26 Les 3 artistes les plus vendeurs

select artists.name, 
    sum(i.Quantity * i.UnitPrice) as total
from artists, 
    albums, 
    tracks, 
    invoice_items i
Where artists.ArtistId = albums.ArtistId
    and albums.AlbumId = tracks.AlbumId
    and tracks.TrackId = invoice_items.TrackId
group by artists.ArtistId
order by total desc
limit 3;
