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.