Udemy 1 - SQL PosteGre SQL

Conseil : 
creer un doc ou apparait toutes les tables avec les clés et les variables
Cette plateforme est super pour cela :
https://www.xmind.net/fr/

Diverses requetes SQL pour apprendre

--Select
SELECT * FROM actor;

SELECT title FROM film;

SELECT first_name, last_name FROM actor;

SELECT customer_id,last_name,first_name, email FROM customer;

--cdt avec where
SELECT * FROM actor
WHERE first_name = 'Nick' AND last_name='Stallone'; 

SELECT amount FROM payment 
WHERE amount > 3;

SELECT email, first_name,last_name 
FROM customer
WHERE store_id = 2;


SELECT email 
FROM customer
WHERE first_name = 'Maria' ;


--trie avec order by
SELECT first_name, last_name
FROM  customer
ORDER BY first_name ASC, last_name DESC;


--limit le nbr de resultat
SELECT first_name,last_name
FROM customer
LIMIT 3;

--valeur unique (no doublon) distinct
SELECT DISTINCT rental_rate
FROM film;


SELECT DISTINCT rating
FROM film;


--COUNT
SELECT COUNT (DISTINCT rating)
FROM film;

SELECT COUNT (rental_rate)
FROM film
WHERE rental_rate =4.99;



SELECT COUNT (rental_rate)
FROM film
WHERE rental_rate > 1;


SELECT rental_id, customer_id,return_date
FROM rental
WHERE customer_id IN (3,6 , 1,2)
ORDER BY return_date;


SELECT title , rental_rate
FROM film
WHERE rental_rate IN (0.99,4.99);

-- Retourne tout les cellules qui commmence par li
SELECT first_name 
FROM customer
WHERE first_name LIKE 'Li%';


-- retourne les cellules qui commence par li et qui a 2 lettres apres
SELECT first_name 
FROM customer
WHERE first_name LIKE 'li__';



SELECT COUNT (first_name)  
FROM actor
WHERE first_name LIKE 'P%';


--like retourne les cellules qui comprend truman dans la chaine de car
SELECT COUNT (title )
FROM film
WHERE title LIKE '%Truman%';


--between retourne les results qui sont compris dans les bornes inclusees
SELECT customer_id, amount
FROM payment
WHERE amount NOT BETWEEN 7 AND 8;


--retourne les resultats dont les datessont comprises dans les bornes
SELECT customer_id,amount,payment_date
FROM payment 
WHERE payment_date BETWEEN '2007-02-15' AND '2017-02-20';



-- retourne le min
SELECT MIN ( amount )
FROM payment;

-- retourne le max
SELECT MAX ( amount )
FROM payment;

--retourne la moyenne 
SELECT ROUND ( AVG ( amount ), 1) AS AVG_amount
FROM payment;


SELECT COUNT (  amount ) 
FROM payment
WHERE amount !=0.0 ;

--retourne la somme
SELECT SUM (amount)
FROM payment ; 



-- order by retourne une table groupée avec les fct d aggregations
SELECT customer_id , COUNT (*) as loc
FROM rental
GROUP BY customer_id 
ORDER BY loc DESC;




SELECT customer_id , SUM (amount) as amount
FROM payment
GROUP BY customer_id 
ORDER BY amount DESC
LIMIT 10;



SELECT rating , ROUND (AVG (rental_rate), 2) AS note 
FROM film
GROUP BY rating
ORDER BY note DESC;



SELECT staff_id, COUNT(amount) AS nbre, SUM (amount) AS somme
FROM payment 
GROUP BY staff_id
ORDER BY somme;

SELECT customer_id , SUM (amount) AS somme , COUNT (amount) AS nbre
FROM payment
GROUP BY customer_id
ORDER BY somme DESC
LIMIT 3 ;


-- having : cdt sur les lignes groupés
SELECT customer_id , SUM (amount) AS somme , COUNT (amount) AS nbre
FROM payment
GROUP BY customer_id
HAVING SUM (amount) > 160 ;




SELECT rating, ROUND (AVG (rental_rate), 2) AS note
FROM film
WHERE rating IN ('R', 'G','PG' )
GROUP BY rating
HAVING ROUND (AVG (rental_rate), 2) > 2.9 ;



-- inner join retourne une table a partir de plusieurs tables
SELECT SUM(amount) , ROUND (AVG (payment.customer_id),0) as id_, first_name, last_name
FROM payment 
INNER JOIN customer
ON payment.customer_id = customer.customer_id
GROUP BY first_name , last_name
ORDER BY last_name , first_name ;



SELECT amount  , p.customer_id , first_name, last_name
FROM payment AS p
INNER JOIN customer AS c
ON p.customer_id = c.customer_id
WHERE p.customer_id = 7 ;



 SELECT title, store_id, COUNT (title)
 FROM inventory AS i 
 INNER JOIN film AS f 
 ON i.film_id = f.film_id
 GROUP BY store_id, title;
 
 
SELECT r.customer_id, title, store_id , return_date
FROM inventory AS i 
INNER JOIN film AS f 
ON i.film_id = f.film_id
INNER JOIN rental AS r
ON i.inventory_id = r.inventory_id ;
 
 
 
 
 
 
 SELECT f.film_id, title, first_name, last_name 
 FROM film AS f
 INNER JOIN film_actor AS fa 
 ON f.film_id = fa.film_id
 INNER JOIN actor AS a 
 ON a.actor_id = fa.actor_id
 WHERE f.title = 'Academy Dinosaur';
 
 
 
 SELECT f.film_id, title,inventory_id,store_id
 FROM film AS f 
 LEFT OUTER JOIN inventory AS i  
 ON i.film_id = f.film_id
 WHERE title = 'Academy Dinosaur';
 
 
 SELECT title ,  c.name AS categorie , l.name as Langue
 FROM film AS f 
 INNER JOIN film_category AS fc
 ON f.film_id = fc.film_id
 
 INNER JOIN category AS c
 ON c.category_id = fc.category_id
 
 INNER JOIN language AS l
 ON f.language_id = l.language_id
 ;
 
 
 
 


 
 
 SELECT  ROUND (AVG (fa.actor_id)) AS id,first_name,last_name, COUNT (fa.actor_id) AS nbr
 FROM film AS f 
 INNER JOIN film_actor AS fa 
 ON fa.film_id = f.film_id
 INNER JOIN actor AS a
 ON a.actor_id = fa.actor_id  
 GROUP BY first_name,last_name 
 ORDER BY nBr DESC;
 
 
 
 
 SELECT first_name,last_name, COUNT (title) as nbr
 FROM film_actor AS fa
 INNER JOIN actor AS a
 ON fa.actor_id = a.actor_id
  INNER JOIN film AS f
  ON f.film_id = fa.film_id
 GROUP BY first_name,last_name
 ORDER BY nbr DESC ;
 
 
 SELECT first_name,last_name, COUNT (title) AS nb
 FROM    (SELECT title, i.film_id 
 FROM inventory AS i
 INNER JOIN film AS f
 ON i.film_id = f.film_id
 WHERE store_id = 1) AS ta
 
 INNER JOIN  (SELECT first_name,last_name, film_id
 FROM film_actor AS fa
 INNER JOIN actor AS a
 ON fa.actor_id = a.actor_id ) AS tb
 
 ON ta.film_id = tb.film_id
 GROUP BY first_name,last_name 
 ORDER BY nb desc
 ;
 
 
 SELECT title, COUNT (i.film_id) AS nb 
 FROM inventory AS i
 INNER JOIN film AS f
 ON i.film_id = f.film_id
 WHERE store_id = 1
 GROUP BY title
 HAVING COUNT (i.film_id) > 3
 ORDER BY nb DESC
;
 
 
 SELECT country , city_id
 FROM city AS ci
 INNER JOIN country AS cou 
 ON cou.country_id = ci.country_id ;
 
 
 
 SELECT customer_id , city_id 
 FROM customer AS c
 INNER JOIN address AS a
 ON c.address_id = a.address_id ;
 
 
 
 -- retourne le nombre de client / pays
 SELECT country , COUNT ( customer_id) as nb
 
 FROM ( SELECT country , city_id
 FROM city AS ci
 INNER JOIN country AS cou 
 ON cou.country_id = ci.country_id ) AS ta
 
 INNER JOIN ( SELECT customer_id , city_id 
 FROM customer AS c
 INNER JOIN address AS a
 ON c.address_id = a.address_id) AS tb
 ON ta.city_id = tb.city_id 
 GROUP BY country
 ORDER BY nb DESC
 ;
 
 
 
 
 SELECT  country, COUNT (customer_id) AS nb
 FROM customer AS c
 INNER JOIN address AS a
 ON c.address_id = a.address_id
 INNER JOIN city AS ci
 ON ci.city_id = a.city_id
 INNER JOIN country AS cou 
 ON cou.country_id = ci.country_id
 GROUP BY country
 ORDER BY nb DESC
 LIMIT 3;
 
 
 
 
 SELECT first_name,last_name,email, COUNT ( rental_id) AS nb
 FROM customer AS c

 INNER JOIN rental AS r
 ON c.customer_id = r.customer_id 
 GROUP BY c.customer_id
 HAVING COUNT ( r.rental_id) > 37
 ;
 
 
 
 
 SELECT store_id , COUNT (rental_id)
 FROM staff AS s
 INNER JOIN rental AS r
 ON s.staff_id = r.staff_id
 GROUP BY store_id;
 
 
 SELECT store_id , SUM (p.amount)
 FROM staff AS s
 INNER JOIN rental AS r
 ON s.staff_id = r.staff_id
  INNER JOIN payment AS p
 ON p.customer_id = r.customer_id
 GROUP BY store_id
 ORDER BY SUM (p.amount)DESC ;
 
 SELECT i.store_id, SUM (p.amount)
 FROM   inventory AS i
 INNER JOIN  rental  AS r ON i.inventory_id = r.inventory_id
 INNER JOIN  payment  AS p ON p.customer_id = r.customer_id
 GROUP BY i.store_id;
 
 
 SELECT store_id, SUM (p.amount) AS revenue
 FROM  inventory AS i
 INNER JOIN rental  AS r ON r.inventory_id = i.inventory_id
 INNER JOIN payment AS p ON p.rental_id = r.rental_id
 GROUP BY i.store_id;
 
 
 
 
 
 
 --CBm de loc /cat de film (exclure comedy et animation)
 -- quelle est la meilleure categ
 -- afficher le total de loc avec la categorie de film
 
 SELECT cat.name, COUNT (r.rental_id) AS nb
 FROM film_category AS fc
 INNER JOIN category AS cat ON fc.category_id = cat.category_id
 INNER JOIN film AS f       ON fc.film_id = f.film_id 
 INNER JOIN inventory AS i  ON f.film_id = i.film_id 
 INNER JOIN rental AS r     ON i.inventory_id = r.inventory_id  
 WHERE cat.name NOT IN ('Animation', 'Comedy')
 GROUP BY cat.name
 ORDER BY COUNT (r.rental_id) DESC;
 
 -- category la + rentable
 -- afficher category, montant
 
 SELECT cat.name, SUM (p.amount)
 FROM film_category  AS fc 
 INNER JOIN category AS cat ON fc.category_id = cat.category_id
 INNER JOIN film AS f       ON fc.film_id = f.film_id
 INNER JOIN inventory AS i  ON f.film_id = i.film_id
 INNER JOIN rental AS r     ON i.inventory_id = r.inventory_id
 INNER JOIN payment AS p    ON r.rental_id = p.rental_id
 GROUP BY cat.name
 ORDER BY SUM (p.amount) DESC;
 
 
 
 
 
--  afficher film en langue anglaise qui ont + de 30 location

 SELECT f.title , COUNT (r.rental_id) AS nb  
 FROM film AS f
 INNER JOIN language AS l  ON f.language_id = l.language_id 
 INNER JOIN inventory AS i  ON f.film_id = i.film_id
 INNER JOIN rental AS r  ON i.inventory_id = r.inventory_id
 WHERE l.name IN ('English')
 GROUP BY f.title
 HAVING COUNT (r.rental_id) > 30
 ORDER BY  COUNT (r.rental_id) DESC ;
 
 
-- Trouver les film qui rapportent le +
-- show all film , nbr loc, revenu degages /film
SELECT f.title , SUM ( amount )
FROM film AS f
INNER JOIN inventory AS i ON f.film_id = i.film_id
INNER JOIN rental AS r    ON i.inventory_id = r.inventory_id
INNER JOIN payment AS p   ON p.rental_id = r.rental_id
GROUP BY f.title
ORDER BY SUM ( amount ) DESC;


SELECT f.title, COUNT (r.rental_id) , f.rental_rate * COUNT (r.rental_id) AS ca
FROM film AS f
INNER JOIN inventory AS i ON f.film_id = i.film_id
INNER JOIN rental AS r    ON i.inventory_id = r.inventory_id
GROUP BY f.title , rental_rate
ORDER BY rental_rate * COUNT (r.rental_id)DESC;