-- Exercice 2 --

SELECT * FROM personne ;

SELECT prenom FROM personne ORDER BY prenom ;

SELECT DISTINCT prenom FROM personne ;

SELECT nom FROM personne WHERE prenom='John' ;

SELECT * FROM personne, film ;

SELECT titre FROM film WHERE genre='Drame' ;

-- Exercice 3 --

SELECT DISTINCT prenom,nom
FROM personne JOIN film
ON idPers = idRealisateur ;

SELECT prenom,nom,titre
FROM personne JOIN film
ON idPers = idRealisateur ;

SELECT DISTINCT titre
FROM film AS F JOIN projection AS P
ON F.idFilm = P.idFilm
WHERE date > "2001-12-31" ;

SELECT titre
FROM personne JOIN film
ON idPers = idRealisateur
WHERE prenom='Lars' AND nom='von Trier' ;

SELECT prenom,nom
FROM film JOIN personne JOIN jouer
ON idRealisateur = idPers AND idAct = idPers ;

SELECT DISTINCT titre
FROM cinema AS C JOIN projection AS P JOIN film AS F
ON C.idCinema = P.idCinema AND P.idFilm = F.idFilm
WHERE nom='UGC SQY' ;

SELECT DISTINCT titre 
FROM cinema AS C JOIN projection AS Pr JOIN film AS F
JOIN jouer AS J JOIN personne AS P
ON C.idCinema = Pr.idCinema AND Pr.idFilm = F.idFilm
AND F.idFilm = J.idFilm AND J.idAct = P.idPers
WHERE P.prenom='Nicole' AND P.nom='Kidman'
AND C.nom='UGC SQY' ;

-- Exercice 4 --

SELECT prenom, nom
FROM personne
LEFT JOIN jouer
ON idAct = idPers
WHERE idAct is NULL ;;

SELECT prenom, nom
FROM personne
LEFT JOIN film
ON idRealisateur = idPers
WHERE idRealisateur is NULL ;;

SELECT prenom, nom
FROM personne
LEFT JOIN jouer
ON idAct = idPers
WHERE idAct is NULL
INTERSECT
SELECT prenom, nom
FROM personne
LEFT JOIN film
ON idRealisateur = idPers
WHERE idRealisateur is NULL ;;

SELECT titre
FROM film
LEFT JOIN jouer
ON film.idFilm = jouer.idFilm
WHERE jouer.idFilm is NULL ;;

SELECT prenom, nom
FROM personne
JOIN (
  SELECT *
  FROM jouer
  LEFT JOIN film
  ON film.idFilm = jouer.idFilm
  WHERE film.idFilm is NULL
) ON idPers = idAct ;;

-- Exercice 5 --

SELECT idCinema,COUNT(*)
FROM projection
GROUP BY idCinema ;

SELECT nom,COUNT(*) AS nb_proj
FROM projection JOIN cinema
ON projection.idCinema = cinema.idCinema
GROUP BY cinema.idCinema ;

SELECT prenom,nom,COUNT(*)
FROM personne JOIN film
ON idPers=idRealisateur
GROUP BY idPers
ORDER BY nom ;

SELECT prenom,nom,COUNT(*),MIN(annee),MAX(annee)
FROM personne JOIN film
ON idPers=idRealisateur
GROUP BY idPers
ORDER BY nom ;

SELECT prenom,nom,COUNT(DISTINCT film.idFilm)
FROM personne JOIN film JOIN jouer
ON idPers=idAct AND jouer.idFilm=film.idFilm
GROUP BY idPers
ORDER BY nom ;

SELECT prenom,nom
FROM personne JOIN film JOIN jouer
ON idPers=idAct AND jouer.idFilm=film.idFilm
GROUP BY idPers
HAVING COUNT(DISTINCT film.idFilm) >= 2 ;

SELECT titre,nbAct,P.prenom,P.nom
FROM personne AS P JOIN (
  SELECT titre,COUNT(DISTINCT idAct) AS nbAct,idRealisateur 
  FROM film JOIN jouer JOIN personne
  ON film.idFilm=jouer.idFilm
  AND personne.idPers=jouer.idAct
  GROUP BY film.idFilm
)
ON idRealisateur=P.idPers ;

SELECT AVG(nbAct)
FROM (
  SELECT titre,nbAct,P.prenom,P.nom
  FROM personne AS P JOIN (
    SELECT titre,COUNT(idAct) AS nbAct,idRealisateur 
    FROM film JOIN jouer JOIN personne
    ON film.idFilm=jouer.idFilm
    AND personne.idPers=jouer.idAct
    GROUP BY film.idFilm
  )
  ON idRealisateur=P.idPers) ;

SELECT prenom,nom,10000000+200000*AVG(nbAct) AS cost
FROM (
  SELECT titre,nbAct,P.prenom,P.nom,idRealisateur
  FROM personne AS P JOIN (
    SELECT titre,COUNT(idAct) AS nbAct,idRealisateur 
    FROM film JOIN jouer JOIN personne
    WHERE film.idFilm=jouer.idFilm
    AND personne.idPers=jouer.idAct
    GROUP BY titre
  )
  ON idRealisateur=P.idPers
)
GROUP BY idRealisateur ;

-- Exercice 6 --

SELECT prenom,nom
FROM personne
WHERE idPers IN (SELECT idRealisateur FROM film) ;

SELECT prenom,nom
FROM personne
WHERE idPers IN (SELECT idRealisateur FROM film WHERE genre='Drame')
UNION
SELECT prenom,nom
FROM personne
WHERE idPers IN (SELECT idRealisateur FROM film WHERE genre='Epouvante') ;

SELECT prenom,nom
FROM personne
WHERE NOT(idPers IN (SELECT idAct FROM jouer NATURAL JOIN film WHERE genre='Drame'))
AND idPers IN (SELECT idAct FROM jouer,film) ;

-- Exercice 7 --

SELECT prenom,nom
FROM personne
WHERE prenom LIKE '%s%' ;

SELECT prenom,nom
FROM personne
WHERE prenom LIKE '%s' ;

SELECT prenom,nom
FROM personne
WHERE NOT(prenom LIKE '%e%') ;

SELECT nom
FROM personne
WHERE nom LIKE '%a%l%' ;

SELECT nom
FROM personne
WHERE nom LIKE '%a%' AND nom LIKE '%l%' ;

SELECT nom
FROM personne
WHERE nom LIKE '%an%' OR nom LIKE '%on%' ;

SELECT titre
FROM film
WHERE titre LIKE '%e%e%e%' ;

SELECT titre
FROM film
WHERE titre LIKE '%b%' AND titre LIKE '%e%' AND NOT(titre LIKE '%e') ;

-- Exercice 8 --

SELECT *
FROM crime_scene_report
WHERE type="murder" AND city="SQL City" ;

SELECT transcript,max(person.address_number)
FROM person JOIN interview
ON person.id = interview.person_id
WHERE person.address_street_name = "Northwestern Dr" ;

SELECT *
FROM get_fit_now_member AS gf JOIN drivers_license AS dr JOIN person AS p
ON gf.person_id = p.id AND dr.id = p.license_id
WHERE gf.membership_status = "gold" AND gf.id LIKE '48Z%' ;

SELECT transcript
FROM person JOIN interview
ON person.id = interview.person_id
WHERE person.address_street_name = "Franklin Ave"
AND person.name LIKE '%Annabel%' ;

SELECT *
FROM get_fit_now_check_in AS ch JOIN get_fit_now_member AS gf
ON ch.membership_id = gf.id 
WHERE check_in_date = 20180109 AND gf.membership_status = "gold"
AND gf.id LIKE '48Z%' ;

INSERT INTO solution VALUES (1,"Jeremy Bowers") ;

SELECT value FROM solution ;

SELECT transcript
FROM interview AS i JOIN get_fit_now_member AS gf JOIN drivers_license AS dr JOIN person AS p
ON gf.person_id = p.id AND dr.id = p.license_id AND i.person_id = p.id 
WHERE gf.membership_status = "gold" AND gf.id LIKE '48Z%' ;

SELECT *
FROM income AS i JOIN person AS p JOIN drivers_license AS dr
ON i.ssn = p.ssn AND dr.id = p.license_id
WHERE i.annual_income>100000 AND height=65 AND dr.gender="female" ;

SELECT *
FROM income AS i JOIN person AS p JOIN drivers_license AS dr JOIN facebook_event_checkin AS fb
ON i.ssn = p.ssn AND dr.id = p.license_id AND p.id = fb.person_id
WHERE i.annual_income>100000 AND height>=65 AND height<=67 AND dr.gender="female" ;

INSERT INTO solution VALUES (1,"Miranda Priestly") ;

SELECT value FROM solution ;

