MY SQL PROGGRESSION

Mes compétences en SQL & CODE MEMORY

*********************************************************************************************************************************
- SQL - Fundamentals
- SQL - Advanced
- SQL - Ludicrous
- [Basics] Injections SQL
- NoSQL: Agréger avec MapReduce
- NoSQL: MongoDB et Documents
- Python / SQL : languages speak to each other
**********************************************************************************************************************************

--- extraire le mois de la derniere datede paiement ---
SELECT extract(month from max(payment_date)) FROM payment;

SELECT extract(month from payment_date) as "month", 
count(*),
sum(amount),
count(case when staff_id = 1 then 1 end) as "mike_count",
count(case when staff_id = 2 then 2 end) as "jon_count",
sum(case staff_id when 1 then amount else 0 end) as "mike_amount",
sum(case staff_id when 2 then amount else 0 end) as "jon_amount"
FROM payment
GROUP BY month
ORDER BY month;


--- créer une viewclient à partir de deuxtables ---
CREATE VIEW infos_clients as
(SELECT first_name, last_name, address, phone
FROM customer c
JOIN address a 
ON a.address_id = c.address_id);


SELECT concat(last_name, " ",first_name) AS NOM_ACTORS, Count(film.title)
FROM actor
JOIN film_actor ON film_actor.actor_id = actor.actor_id
JOIN film ON film.film_id = film_actor.film_id
GROUP BY NOM_ACTORS
order by Count(title) Desc;


SELECT concat(last_name, "  ",first_name) AS totalname, sum(amount) as total
FROM customer
JOIN payment ON payment.customer_id = customer.customer_id
GROUP BY totalname
ORDER BY sum(amount) DESC;


--- utiliser_using pour jointure si il y a la colonne ---
SELECT film_id, title, inventory_id
FROM film
JOIN inventory USING(film_id)
ORDER BY title;


--- utiliser_using pour jointure  ---
SELECT customer_id, first_name, last_name, avg(amount)
FROM customer
JOIN payment USING(customer_id)
group by customer_id;


--- nombre de mots dans une colonne---
SELECT title, description, length(description) - length(replace(description, " ", ""))+1 as nbrmots
FROM film;


--- somme des montants par staff---
SELECT sum(amount), staff_id
FROM payment
JOIN staff USING(staff_id )
GROUP BY staff_id;


--- créer unetable à partir dune autre ---
CREATE TABLE history_customer AS SELECT * FROM customer;



--- créer unetable à partir dune ou plusieurs colonnes duneautreautretable ---
CREATE TABLE history1colonne_customer AS SELECT last_name FROM customer;


--- inserer des valeurs dans une ligne ---
INSERT INTO testbricecustomer (customer_id, store_id, first_name, last_name, email, address_id, active, create_date, last_update)
VALUES (DEFAULT, "1", "BRICE","DENICE","BRICE.DENICE@sakilacustomer.org", last_insert_id(address_id), '1', '2006-02-14 22:04:36', '2006-12-19 08:43:20');


--- inserer des valeurs dans plusieurs lignes en fonction dune autretable ---
INSERT INTO "tatable" 
select * from "autretable"
where first_name = "briceparexemple";


--- modifiacation toute la colonne --- ATTENTION CELA MODIFIE TOUTE LA COL DE TOUT LES ID ---
UPDATE testbricecustomer
SET last_name = "TWONICE";