SQL aide

Je propose de répondre a toute les question que vous pourriez avoir en sql je posterai bientôt les réponses aux tds de sql

c'est sympa ca merci!

Je commence,

Pourrait tu donner les réponses des questions du TP2 pour lesquelles la prof à fait des remarques dans son mail? ( en gros en ayant modifier les réponses en tenant compte de ce qu'elle à dit).

Seulement tu l'as fait bien sur ^^

Voila le td 6
1)
select *
from personne ;


2)
select prenom
from personne ;

3)
select *
from personne
order by asc ;

4)
select *
from personne
order by desc ;
/*Angelina
Bruce
Clint
David
Emily
Grace
Holly
James
Jeremy
John
Kevin
Lars
Nicole
Paul
Quentin
Rosanna
Sam
Samuel
Stellan
(19 lignes)
*/
5)
select *
from personne
where prenom = 'John' ;
/* 5 | Travolta | John
12 | Wayne | John
18 | Glen | John
(3 lignes)

*/
6)
select nom
from personne
where prenom = 'John' ;
/* nom
----------
Travolta
Wayne
Glen
(3 lignes)

*/
7)
select *
from personne, film ;
/* | Drame | 1999-10-23
1 | Kidman | Nicole | 11 | | L'Echange | | 2008-10-23
2 | Bettany | Paul | 1 | 15 | Crash | Drame | 1996-10-23
2 | Bettany | Paul | 2 | 15 | Faux-Semblants | Epouvante | 1988-10-23
2 | Bettany | Paul | 3 | 14 | Pulp Fiction | Policier | 1994-10-23
2 | Bettany | Paul | 4 | 13 | Breaking the waves | Drame | 1996-10-23
2 | Bettany | Paul | 5 | 13 | Dogville | Drame | 2002-10-23
2 | Bettany | Paul | 6 | 12 | Alamo | Western | 1960-10-23
2 | Bettany | Paul | 7 | 18 | Dangereusement vôtre | Espionnage | 1985-10-23
2 | Bettany | Paul | 8 | 19 | Chasseur blanc, coeur noir | Drame | 1989-10-23
2 | Bettany | Paul | 9 | 19 | Minuit dans le jardin du bien et du mal | Policier |
2 | Bettany | Paul | 10 | 21 | American Beauty | Drame | 1999-10-23
(242 lignes)'
*/

select nom, prenom, id_personne
from personne, jouer
where id_personne = id_acteur ;
/* nom | prenom | id_personne
------------+---------+-------------
Kidman | Nicole | 1
Bettany | Paul | 2
Watson | Emily | 3
Skarsgard | Stellan | 4
Travolta | John | 5
L. Jackson | Samuel | 6
Willis | Bruce | 7
Irons | Jeremy | 8
Spader | James | 9
Hunter | Holly | 10
Arquette | Rosanna | 11
Skarsgard | Stellan | 4
Mazursky | Paul | 16
Eastwood | Clint | 19
Spacey | Kevin | 20
Spacey | Kevin | 20
(16 lignes)
*/
9)
select nom, prenom
from personne, jouer
where personne.id_personne = jouer.id_acteur ;

/* nom | prenom
------------+---------
Kidman | Nicole
Bettany | Paul
Watson | Emily
Skarsgard | Stellan
Travolta | John
L. Jackson | Samuel
Willis | Bruce
Irons | Jeremy
Spader | James
Hunter | Holly
Arquette | Rosanna
Skarsgard | Stellan
Mazursky | Paul
Eastwood | Clint
Spacey | Kevin
Spacey | Kevin
(16 lignes)
*/
10)
select prenom, nom, role, titre
from personne, jouer, film
where personne.id_personne=jouer.id_acteur
and jouer.id_film=film.id_film;

/* prenom | nom | role | titre
---------+------------+-------------------------+-----------------------------------------
Nicole | Kidman | Grace | Dogville
Paul | Bettany | Tom Edison | Dogville
Emily | Watson | Bess | Breaking the waves
Stellan | Skarsgard | Jan | Breaking the waves
John | Travolta | Vincent Vega | Pulp Fiction
Samuel | L. Jackson | Jules Winnfield | Pulp Fiction
Bruce | Willis | Butch Coolidge | Pulp Fiction
Jeremy | Irons | Beverly & Elliot Mantle | Faux-Semblants
James | Spader | James Ballard | Crash
Holly | Hunter | Helen Remington | Crash
Rosanna | Arquette | Gabrielle | Crash
Stellan | Skarsgard | Chuck | Dogville
Paul | Mazursky | May Day | Dangereusement vôtre
Clint | Eastwood | John Wilson | Chasseur blanc, coeur noir
Kevin | Spacey | Jim Williams | Minuit dans le jardin du bien et du mal
Kevin | Spacey | Lester Burnham | American Beauty
(16 lignes)
*/
11)
select ''||prenom||' '||nom||' a joue le role de '||role||' dans '||film||''
from personne, jouer, film
where personne.id_personne=jouer.id_acteur
and jouer.id_film=film.id_film;
/* ?column?
-------------------------------------------------------------------------------------------------------------
Nicole Kidman a joue le role de Grace dans (5,13,Dogville,Drame,2002-10-23)
Paul Bettany a joue le role de Tom Edison dans (5,13,Dogville,Drame,2002-10-23)
Emily Watson a joue le role de Bess dans (4,13,"Breaking the waves",Drame,1996-10-23)
Stellan Skarsgard a joue le role de Jan dans (4,13,"Breaking the waves",Drame,1996-10-23)
John Travolta a joue le role de Vincent Vega dans (3,14,"Pulp Fiction",Policier,1994-10-23)
Samuel L. Jackson a joue le role de Jules Winnfield dans (3,14,"Pulp Fiction",Policier,1994-10-23)
Bruce Willis a joue le role de Butch Coolidge dans (3,14,"Pulp Fiction",Policier,1994-10-23)
Jeremy Irons a joue le role de Beverly & Elliot Mantle dans (2,15,Faux-Semblants,Epouvante,1988-10-23)
James Spader a joue le role de James Ballard dans (1,15,Crash,Drame,1996-10-23)
Holly Hunter a joue le role de Helen Remington dans (1,15,Crash,Drame,1996-10-23)
Rosanna Arquette a joue le role de Gabrielle dans (1,15,Crash,Drame,1996-10-23)
Stellan Skarsgard a joue le role de Chuck dans (5,13,Dogville,Drame,2002-10-23)
Paul Mazursky a joue le role de May Day dans (7,18,"Dangereusement vôtre",Espionnage,1985-10-23)
Clint Eastwood a joue le role de John Wilson dans (8,19,"Chasseur blanc, coeur noir",Drame,1989-10-23)
Kevin Spacey a joue le role de Jim Williams dans (9,19,"Minuit dans le jardin du bien et du mal",Policier,)
Kevin Spacey a joue le role de Lester Burnham dans (10,21,"American Beauty",Drame,1999-10-23)
(16 lignes)
*/
12)
select titre
from film
where genre='Drame';
/* titre
----------------------------
Crash
Breaking the waves
Dogville
Chasseur blanc, coeur noir
American Beauty
(5 lignes)
*/
13)
select titre
from film,projection
where film.id_film=projection.id_film
and projection.jour>='01/01/2002'
and projection.jour<='12/31/2002';
/* titre
----------
Dogville
Dogville
Dogville
Alamo
(4 lignes)
*/
14)

SELECT film.titre FROM film JOIN personne ON personne.id_personne =
personne.id_realisateur
WHERE LOWER(personne.nom) = 'von trier' AND LOWER(personne.prenom) = 'lars';

/* titre
--------------------
Breaking the waves
Dogville
(2 lignes)
*/



15)
SELECT titre FROM film NATURAL JOIN projection
WHERE id_cinema =
(
SELECT id_cinema FROM cinema
WHERE LOWER(nom) = 'le fontenelle';
);
/* titre
-----------------------------------------
Alamo
American Beauty
Breaking the waves
Chasseur blanc, coeur noir
Crash
Dangereusement vôtre
Dogville
Faux-Semblants
L'Echange
Minuit dans le jardin du bien et du mal
Pulp Fiction
(11 lignes)
*/



16)
select distinct nom,prenom
from personne, film
where personne.id_personne=film.id_realisateur ;

/* nom | prenom
------------+---------
Cronenberg | David
Eastwood | Clint
Glen | John
Mendes | Sam
Tarantino | Quentin
von Trier | Lars
Wayne | John
(7 lignes)
*/




17)
select distinct nom, prenom
from personne, jouer
where personne.id_personne=jouer.id_acteur;

/* nom | prenom
------------+---------
Arquette | Rosanna
Bettany | Paul
Eastwood | Clint
Hunter | Holly
Irons | Jeremy
Kidman | Nicole
L. Jackson | Samuel
Mazursky | Paul
Skarsgard | Stellan
Spacey | Kevin
Spader | James
Travolta | John
Watson | Emily
Willis | Bruce
(14 lignes)
*/




18)
select distinct nom, prenom
from personne, jouer, film
where personne.id_personne=film.id_realisateur
and personne.id_personne=jouer.id_acteur ;

/* nom | prenom
----------+--------
Eastwood | Clint
(1 ligne)
*/




19)
select distinct personne.nom, prenom
from personne, jouer, cinema, projection
where personne.id_personne=jouer.id_acteur
and projection.id_cinema=cinema.id_cinema
and cinema.nom='le Fontenelle'
and projection.jour>'01/01/2000'
and jouer.id_film=projection.id_film;
/* nom | prenom
-----------+---------
Bettany | Paul
Kidman | Nicole
Skarsgard | Stellan
Spacey | Kevin
(4 lignes)
*/




20)
select distinct titre
from personne, film, jouer, projection, cinema
where film.id_film=jouer.id_film
and jouer.id_acteur=personne.id_personne
and personne.nom='Kidman'
and film.id_film=projection.id_film
and projection.id_cinema=cinema.id_cinema
and cinema.nom='le Fontenelle';
/* titre
----------
Dogville
(1 ligne)
*/




21)
select distinct prenom
from personne
where prenom like '%s%';
/* prenom
---------
James
Rosanna
Lars
(3 lignes)
*/




22)

select distinct prenom
from personne
where prenom ~* 's';
/* prenom
---------
Stellan
Samuel
James
Rosanna
Lars
Sam
(6 lignes)
*/




23)
select distinct prenom
from personne
where prenom ~* 's$';
/* prenom
---------
Stellan
Samuel
Sam
(3 lignes)
*/




24)
select distinct prenom
from personne
where prenom ~* '^s';
/* prenom
--------
James
Lars
(2 lignes)
*/




25)
select distinct prenom
from personne
where prenom ~* '^[^e].*e.*[^e]$';

/* prenom
----------
Stellan
Samuel
Jeremy
James
Quentin
Kevin
Angelina
(7 lignes)
*/




26)
select distinct prenom
from personne
where prenom !~* 'e';
/* prenom
---------
Paul
John
Holly
Rosanna
John
Lars
David
Paul
John
Clint
Sam
(11 lignes)
*/




27)
select distinct prenom
from personne
where prenom ~* 'a'
and prenom ~* 'l';
/* prenom
----------
Paul
Stellan
Samuel
Lars
Paul
Angelina
(6 lignes)
*/




27)
select distinct prenom
from personne
where prenom ~* 'a'
and prenom ~* 'l';
/* prenom
----------
Paul
Stellan
Samuel
Lars
Paul
Angelina
(6 lignes)
*/





28)
select distinct nom
from personne
where nom ~* 'an'
or nom ~* 'on';

select distinct nom
from personne
where nom ~* '[ao]n';

select distinct nom
from personne
where nom ~* 'an' || nom ~* 'on';

/* nom
------------
Kidman
Bettany
Watson
L. Jackson
Irons
von Trier
Tarantino
Cronenberg
Jones
(9 lignes)
*/




29)
select distinct titre
from film
where titre ~* 'e.*e.*e';
/* titre
-----------------------------------------
Breaking the waves
Dangereusement vôtre
Minuit dans le jardin du bien et du mal
(3 lignes)*/

td 8
/*1)*/
select nom, prenom, role, titre
from personne cross join jouer, film
where personne.id_personne = jouer.id_acteur
and jouer.id_film = film.id_film
group by nom, prenom, role, titre
order by nom ASC;

/*nom |prenom | role | titre
------------+-------------+---------------------+-----------------------------------------
Arquette | Rosanna | Gabrielle | Crash
Bettany | Paul | Tom Edison | Dogville
Eastwood| Clint | John Wilson | Chasseur blanc, coeur noir
Hunter | Holly | Helen Remington | Crash
Irons | Jeremy | Beverly & Elliot Mantle | Faux-Semblants
Kidman | Nicole | Grace | Dogville
L. Jackson | Samuel | Jules Winnfield | Pulp Fiction
Mazursky | Paul | May Day | Dangereusement vôtre
Skarsgard | Stellan | Jan | Breaking the waves
Skarsgard | Stellan | Chuck | Dogville
Spacey | Kevin | Lester Burnham | American Beauty
Spacey | Kevin | Jim Williams | Minuit dans le jardin du bien et du mal
Spader | James | James Ballard | Crash
Travolta | John | Vincent Vega | Pulp Fiction
Watson | Emily | Bess | Breaking the waves
Willis | Bruce | Butch Coolidge | Pulp Fiction
(16 lignes)


2)*/

select nom, prenom, count(film.titre)
from (personne JOIN jouer on personne.id_personne=jouer.id_acteur) natural join film
group by personne.nom, personne.prenom
order by prenom ASC;

/*
nom | prenom | count
--------------+------------+-------
Willis | Bruce | 1
Eastwood | Clint | 1
Watson | Emily | 1
Hunter | Holly | 1
Spader | James | 1
Irons | Jeremy | 1
Travolta | John | 1
Spacey | Kevin | 2
Kidman | Nicole | 1
Bettany | Paul | 1
Mazursky | Paul | 1
Arquette | Rosanna | 1
L. Jackson | Samuel | 1
Skarsgard | Stellan | 2
*/

3)*/
select nom, prenom, count(film.titre) as nombre_film, min (film.annee) as premier_film, max (film.annee) as dernier_film
from (personne JOIN jouer on personne.id_personne=jouer.id_acteur) natural join film
group by personne.nom, personne.prenom
order by nom ASC;

/*
nom |prenom | nombre_film | premier_film | dernier_film
------------+-----------+-----------------+----------------+--------------
Arquette |Rosanna| 1| 1996 | 1996
Bettany | Paul | 1 | 2002 | 2002
Eastwood| Clint | 1 | 1989 | 1989
Hunter | Holly | 1 | 1996 | 1996
Irons | Jeremy | 1 | 1988 | 1988
Kidman | Nicole | 1 | 2002 | 2002
L. Jackson | Samuel | 1 | 1994 | 1994
Mazursky | Paul | 1 | 1985 | 1985
Skarsgard | Stellan | 2 | 1996 | 2002
Spacey | Kevin | 2 | 1999 | 1999
Spader | James | 1 | 1996 | 1996
Travolta | John | 1 | 1994 | 1994
Watson | Emily | 1 | 1996 | 1996
Willis | Bruce | 1 | 1994 | 1994


4)*/

/*
nom | prenom | nombre_film | premier_film | dernier_film
-----------+---------+-------------+--------------+--------------
Skarsgard | Stellan | 2 | 1996 | 2002
Spaceyselect nom, prenom, count(film.titre) as nombre_film, min (film.annee) as premier_film, max (film.annee) as dernier_film
from (personne JOIN jouer on personne.id_personne=jouer.id_acteur) natural join film
group by personne.nom, personne.prenom
having count (film.titre)>=2
order by nom ASC; | Kevin | 2 | 1999 | 1999
*/

/*5)*/
select prenom, nom, count('Drame') as nombre_films_dramatiques
from personne cross join jouer, film
where personne.id_personne=jouer.id_acteur
and jouer.id_film=film.id_film
and film.genre='Drame'
group by nom, prenom
having count('Drame')>=0
order by nom ASC;
/* (Ayoub: je propose une façon plus simple pour ceux qui révise:
select nom, prenom, count(film.titre) as nombre_film
from (personne inner JOIN jouer on personne.id_personne=jouer.id_acteur) natural join film
where genre='Drame'
group by personne.nom, personne.prenom
=>meme resultat)

prenom | nom | nombre_films_dramatiques
---------+-----------+--------------------------
Rosanna | Arquette | 1
Paul | Bettany | 1
Clint | Eastwood | 1
Holly | Hunter | 1
Nicole | Kidman | 1
Stellan | Skarsgard | 2
Kevin | Spacey | 1
James | Spader | 1
Emily | Watson | 1
(9 lignes)


*/

/*6)*/
select nom, prenom, count(DISTINCT id_film) as nombre_films
from(select id_personne as id_acteur, nom, prenom
from (personne JOIN jouer on personne.id_personne=jouer.id_acteur) natural join film
where film.genre = 'Drame') AS DRAMACTEUR
NATURAL JOIN JOUER
GROUP BY id_acteur,nom, prenom;

/* prenom | nom | nombre_films
---------+-----------+-------
Rosanna | Arquette | 1
Paul | Bettany | 1
Clint | Eastwood | 1
Holly | Hunter | 1
Nicole | Kidman | 1
Stellan | Skarsgard | 1
Stellan | Skarsgard | 1
Kevin | Spacey | 1
James | Spader | 1
Emily | Watson | 1
(10 lignes)

*/

/*7)modif ayoub*/
select nom,prenom from personne join jouer on id_personne=id_acteur
intersect
select nom,prenom from personne join film on id_personne=id_realisateur;

nom | prenom
----------+--------
Eastwood | Clint
(1 ligne)


/**/
select distinct prenom, nom
from personne
join film
on personne.id_personne=film.id_realisateur
and genre='Drame'
intersect
select distinct prenom, nom
from personne
join film
on personne.id_personne=film.id_realisateur
and genre='Epouvante';

/* prenom | nom
--------+------------
David | Cronenberg
(1 ligne)
*/

/*9)*/
select prenom, nom
from personne
join jouer on id_personne=id_acteur
except
select prenom, nom
from personne
join jouer on id_personne=id_acteur
join film on jouer.id_film=film.id_film
and genre ='Drame';

/* prenom | nom
--------+------------
Bruce | Willis
Jeremy | Irons
John | Travolta
Paul | Mazursky
Samuel | L. Jackson
(5 lignes)
*/


/*10*/
select distinct *
from cinema
where not exists(Select id_film
from film
Where not exists(select id_film, id_cinema
from projection
where projection.id_film = film.id_film
And projection.id_cinema = cinema.id_cinema
)
);

/* id_cinema | nom | adresse
-----------+-----------+--------------------
2 | le Fontenelle | 78160 Marly-le-Roi
(1 ligne)
*/

/*11*/

select distinct *
from personne
where not exists(select id_film
from film
where not exists(select id_acteur, id_film
from jouer
where jouer.id_film = film.id_film
and jouer.id_acteur = personne.id_personne
)
);

select nom,prenom
from personne join jouer on (id_acteur=id_personne) join projection using(id_film)
group by nom,prenom,id_acteur
having count (distinct id_cinema)=
(
select count (id_cinema)
from cinema
);


/*12*/
select nom, prenom, count(id_realisateur)
from personne join film
on personne.id_personne = film.id_realisateur
group by nom, prenom;

nom | prenom | count
-------------------+-----------+-------
Glen | John | 1
Eastwood | Clint | 2
von Trier | Lars | 2
Wayne | John | 1
Mendes | Sam | 1
Tarantino | Quentin | 1
Cronenberg | David | 2
(7 lignes)

Encore mieux que ce que j'espérais! merci

ba j'ai essayer de rajouter le maximum de modification possible de la prof mais bon y'en a qui s'applique en général donc faut les relire pour le controle