La Solution d'Exercice SQL Corrigé : En outre SQL Base de données "Bus Dépôts" 2







/* Question 1 sous-requête) - Lister les nettoyants par nom qui appartiennent à la même dépôt que Betty - ne comprennent pas Betty dans votre réponse.*/

Select  cName
from Cleaner
where cName <> 'Betty'
and dno in
      (select dNo
      from Cleaner
      where cName = 'Betty');


CNAME
Doug
Jay
Vince


/* Question 2 auto rejoindre - Lister les nettoyants par nom qui appartiennent à la même dépôt que Betty - ne comprennent pas Betty dans votre réponse*/


Select  c2.cName
from Cleaner c1, Cleaner c2
where c1.cName = 'Betty'
and c1.dNo=c2.dNo
and c2.cName <> 'Betty';

CNAME
Vince
Jay
Doug



/* Question 3a Trouver les nettoyants par nom (une seule fois) qui sont responsables de tous les bus qui a le même type que le nettoyant Betty est responsable.*/

Select distinct c2.cName
From Cleaner c1, Cleaner c2,  Bus b1, Bus b2
Where c1.cName=  'Betty'
And c1.cNo=b1.cNo
And b1.tNo=b2.tNo
And c2.cName<> 'Betty';


CNAME
Jean
John
Jay
Vince
Geeta
Doug
6 rows selected.

/* Question 3b Trouver des chauffeurs d'autobus qui sont qualifiés pour conduire tout type de bus qui peut fonctionner sur une route que Jack Jones peut fonctionner sur. Vous devriez trouver que Jack Jones est pas dans le tableau résultant même si vous avez pas explicitement demandé pour cette - expliquer pourquoi.

Select distinct bd2.bdName
From BusDriver bd1, Training t,
restriction res, Ability a, BusDriver bd2
Where bd1.bdName= 'Jack Jones'
And bd1.bdNo=a.bdNo
And a.rNo=res.rNo
And res.tNo=t.tNo
And t.bdNo=bd2.bdNo;


BDNAME
Maggie May
Jane Brown
Sally Smith
James Bond
John Peel


Notez que nous pouvons éviter des jointures supplémentaires en ne comprenant itinéraire et BusType dans le chemin d'accès que ceux-ci ne sont pas nécessaires. Aussi Jack Jones ne figure pas dans la sortie. Pourquoi?


(parce que bien que JJ est autorisé à opérer ces routes (route 7), il est pas qualifié pour conduire les autobus qui peuvent fonctionner sur ces routes.)
/* Question 4 sous-requête Lister tous les chauffeurs de bus qui gagnent plus d'argent que Sally Smith */

Select bdNo, bdName
from BusDriver
where bdSalary >
      (select bdSalary
      from BusDriver
      where bdName = 'Sally Smith');


BDNO
BDNAME
001
Jane Brown
008
Maggie May
010
Peter Piper
011
John Peel


/* Question 5 Lister tous les chauffeurs qui gagnent plus que l'everage de leurs depot. */

Select bdName
from BusDriver bd1
where bdSalary >
   (select  avg(bdSalary)
   from BusDriver bd2
   where bd1.dNo =bd2.dNo);

BDNAME
Jane Brown
Maggie May
John Peel


/* Question 6a Créer une requête qui fusionne deux instructions select ensemble: l'un qui donne les noms de tous les chauffeurs qui travaillent dans des dépôts dans la route désignée Tottenham / Angel; l'autre donne les noms de tous les chauffeurs qui sont autorisés à opérer sur la route Tottenham / Angel*/


Select  bdName
from BusDriver bd, Depot d, Route r
where bd.dNo = d.dNo
and d.dNo = r.dNo
and rDescript = ‘Tottenham/Angel’;



BDNAME
James Bond
Maggie May
John Peel


Select  bdName
from BusDriver bd, Ability a, Route r
where bd.bdNo = a.bdNo
and a.rNo = r.rNo
and rDescript = ‘Tottenham/Angel’;

BDNAME
James Bond
Maggie May



Select  bdName
from BusDriver bd, Depot d, Route r
where bd.dNo = d.dNo
and d.dNo = r.dNo
and rDescript = 'Tottenham/Angel'
union
Select  bdName
from BusDriver bd, Ability a, Route r
where bd.bdNo = a.bdNo
and a.rNo = r.rNo
and rDescript = ‘Tottenham/Angel’;

BDNAME
James Bond
John Peel
Maggie May

/* Question 6b Intersection Comme question 6a mais cette fois l'intersection. État en anglais forment ce que la requête vise à faire */

Select  bdName
from BusDriver bd, Depot d, Route r
where bd.dNo = d.dNo
and d.dNo = r.dNo
and rDescript = 'Tottenham/Angel'
intersect
Select  bdName
from BusDriver bd, Ability a, Route r
where bd.bdNo = a.bdNo
and a.rNo = r.rNo
and rDescript = 'Tottenham/Angel';
BDNAME
James Bond
Maggie May

/* Question 6c Différence Comme question 6a mais cette fois la différence. État en anglais forment ce que la requête vise à faire. */

Select  bdName
from BusDriver bd, Depot d, Route r
where bd.dNo = d.dNo
and d.dNo = r.dNo
and rDescript = 'Tottenham/Angel'
minus
Select  bdName
from BusDriver bd, Ability a, Route r
where bd.bdNo = a.bdNo
and a.rNo = r.rNo
and rDescript = 'Tottenham/Angel';

BDNAME
John Peel

/* Question 7 Lister tous les chauffeurs de bus qui ont été affectés à un dépôt */

select  bdName
from BusDriver bd
where exists
   (select  *
   from Depot d
   where bd.dNo=d.dNo);



BDNAME
Jane Brown
James Bond
Maggie May
Jack Jones
Peter Piper
John Peel
6 rows selected.


 /* Question 8 Lister tous les nettoyeurs (nom et numéro) qui sont sur le système, mais ne sont pas encore responsable des autobus. */

select cNo, cName
from Cleaner c
where not exists
   (select  *
   from Bus b
   where c.cNo=b.cNo);

CNO
CNAME
111
Jean
115
Doug
116
Geeta



/* Question 9  - lister toutes les routes où tous les types de bus dans la base de données sont autorisés à voyager sur une route*/

select rNo
from Route r
where not exists
   (select *
   from BusType bt
   where not exists
      (select *
      from Restriction res
      where r.rNo = res.rNo
      and res.tNo = bt.tNo));

no rows selected
/* Question 10 Créer une vue appelée HendonDrivers qui indique au conducteur numéros, les noms et les salaires des chaufeur qui sont qualifiés pour conduire des autobus qui peuvent fonctionner sur la route Hendon / Muswell Hill. Maintenant, utilisez la vue de trouver les numéros de chauffeurs, les noms des chauffeurs qui sont qualifiés pour conduire des autobus qui peuvent fonctionner sur la route Hendon / Muswell Hill, mais qui gagnent moins de 2000.

create view HendonDrivers
as select distinct bd.bdNo, bdName, bdsalary
from BusDriver bd, Training t,
Restriction res, Route r
where bd.bdNo = t.bdNo
and t.Tno = res.Tno
and res.rNo = r.rNo
and rDescript = 'Hendon/Muswell Hill';

View created.


select * from HendonDrivers

BDNO
BDNAME
BDSALARY
008
Maggie May
2420
009
Jack Jones
1400
011
John Peel
2000
007
James Bond
1500

select distinct bdNo,bdName
from HendonDrivers
where bdSalary < 2000;

BDNO
BDNAME
009
Jack Jones
007
James Bond



/* Question 11 mis en place une requête interactive à la liste des chauffeurs par nom qui gagnent plus qu'un montant spécifié. Essayez d'exécuter la requête avec des valeurs différentes.

select bdName
from BusDriver
where bdSalary > &bdSalary;
old 3: where bdSalary > &bdSalary
new 3: where bdSalary > 1500


BDNAME
Jane Brown
Sally Smith
Maggie May
Peter Piper
John Peel


old 3: where bdSalary > &bdSalary
new 3: where bdSalary > 2000


BDNAME
Maggie May
Peter Piper


►Retour à l'Exercice
Cours Et Exercices