La Solution d'Exercice SQL Corrigé : En outre SQL Base de données "Bus Dépôts"
/*Question 1 Trouvez le maximum, minimum et le salaire moyen du chaufeur */
Select max(bdSalary),min(bdSalary), avg(bdSalary)
from BusDriver;
|
BDSALARY)
|
MIN(BDSALARY)
|
AVG(BDSALARY)
|
|
3500
|
1400
|
2052.85714
|
/* Question 2 Compter le nombre de chaufeurs qui travaillent pour Middlesex Transport pour le moment */
Select count(*) "driver count"
from BusDriver;
|
driver count
|
|
7
|
/* Question 3 Trouver des informations d'itinéraire (numéro de route et description) pour toutes les voies qui relient le Holloway Depot.
*/
*/
select rNo, rDescript
from Route
where dNo in
(select dNo
from Depot
where dName = 'Holloway');
|
RNO
|
RDESCRIPT
|
|
6
|
Camden/Golders Green
|
|
7
|
Finchley/Tottenham
|
|
8
|
Hendon/Muswell Hill
|
/* Question 4 même chose avec une jointure. */
select rNo, rDescript
from Route r, Depot d
where r.dNo = d.dNo
and dName = 'Holloway';
RNO RDESC
|
RNO
|
RDESCRIPT
|
|
6
|
Camden/Golders Green
|
|
7
|
Finchley/Tottenham
|
|
8
|
Hendon/Muswell Hill
|
/* Question 5 Liste des détails de bus pour tous les bus qui n'a pas été affecté à un dépôt */
select *
from Bus
where dNo is null;
|
REG_NO
|
MODEL
|
TNO
|
DNO
|
CNO
|
|
R678FDS
|
Daf SB220
|
1
|
|
110
|
/* Question 6 Liste tous les chauffeurs (nom et numéro) qui sont sur le système, mais ne sont pas encore responsable d'un itinéraire */
Select bdNo,bdName
from BusDriver
where bdNo not in
(select bdNo from Ability);
|
BDNO
|
BDNAME
|
|
006
|
Sally Smith
|
|
010
|
Peter Piper
|
|
011
|
John Peel
|
/* Question 7 Inscrivez le nom de chaque dépôt et le salaire moyen pour les conducteurs de travail au dépôt */
select dName, avg(bdSalary)
from Depot d, BusDriver bd
where d.dNo = bd.dNo
group by d.dNo, dName;
|
DNAME
|
AVG(BDSALARY)
|
|
Islington
|
3500
|
|
Holloway
|
1600
|
|
Hornsey
|
1973.33333
|
/* Question 8 inscrit chaque dépôt par nom et compter le nombre de chauffeurs de bus qui sont affectés à chacun, pour les dépôts avec plus d'un chauffeur. */
select dName, count(bdNo)
from Depot d, BusDriver bd
where d.dNo = bd.dNo
group by d.dNo,dName
having count(bdNo) > 1;
|
DNAME
|
COUNT(BDNO)
|
|
Holloway
|
2
|
|
Hornsey
|
3
|
/* Question 9 Pour chaque propre responsable de bus de bus de type doubledecker ou minibus, une liste de sa / son nom et le numéro et trouver le nombre total pour lequel chaque nettoyeur est responsable */
select cName, c.cNo, count(bt.tNo) as buscount
from Cleaner c, Bus b, busType bt
where c.cNo = b.cNo
and b.tNo = bt.tNo
and (tDescript = 'doubledecker'
or tDescript = 'minibus')
group by c.cNo,cName;
|
CNAME
|
CNO
|
BUSCOUNT
|
|
John
|
110
|
2
|
|
Betty
|
112
|
1
|
/* Question 10a Liste de tous les chauffeurs (nom et numéro) et leurs itinéraires (nombre et description, classer par numéro de chauffeur */
Select bd.dNo, bdName, r.rNo, rDescript
from busDriver bd, Ability a, Route r
where bd.bdNo = a.bdNo
and a.rNo =r.rNo
order by bd.bdNo;
|
DNO
|
BDNAME
|
RNO
|
RDESCRIPT
|
|
101
|
Jane Brown
|
6
|
Camden/Golders Green
|
|
101
|
Jane Brown
|
7
|
Finchley/Tottenham
|
|
101
|
Jane Brown
|
8
|
Hendon/Muswell Hill
|
|
102
|
James Bond
|
10
|
Tottenham/Angel
|
|
102
|
James Bond
|
6
|
Camden/Golders Green
|
|
102
|
Maggie May
|
10
|
Tottenham/Angel
|
|
102
|
Maggie May
|
11
|
Islington/Highgate
|
|
101
|
Jack Jones
|
7
|
Finchley/Tottenham
|
8 lignes sélectionnées.
/* Question 10b classer par description de la route au sein de numéro de chauffeur */
Select bd.dNo, bdName, r.rNo, rDescript
from BusDriver bd, Ability a, Route r
where bd.bdNo = a.bdNo
and a.rNo =r.rNo
order by bd.bdNo, r.rDescript;
|
DNO
|
BDNAME
|
RNO
|
RDESCRIPT
|
|
101
|
Jane Brown
|
6
|
Camden/Golders Green
|
|
101
|
Jane Brown
|
7
|
Finchley/Tottenham
|
|
101
|
Jane Brown
|
8
|
Hendon/Muswell Hill
|
|
102
|
James Bond
|
6
|
Camden/Golders Green
|
|
102
|
James Bond
|
10
|
Tottenham/Angel
|
|
102
|
Maggie May
|
11
|
Islington/Highgate
|
|
102
|
Maggie May
|
10
|
Tottenham/Angel
|
|
101
|
Jack Jones
|
7
|
Finchley/Tottenham
|
8 lignes sélectionnées.