Exercices SQL sur les dates

79 downloads 515 Views 210KB Size Report
1 juil. 2003 ... Requêtes SQL avec la date et le groupement. Chapitre 7 ... Formulez une clause SQL pour répondre aux requêtes ci-dessous ***. 1- Trouvez le ...
2003-07-01

1

Requêtes SQL avec la date et le groupement Chapitre 7 série 3 Base de données AcciRoute SOLUTIONS

Voici MRD de la BD AcciRoute pour représenter les rapports d’accidents de la route. Le MRD de chaque relation est enrichi le type de l’attribut afin de vous permettre de formuler adéquatement les requêtes SQL. Personne (nas* : char(9), nom : varchar(35), villeP varchar(50)) Voiture (imma* : char(6), modele :varchar(20), annee : char(4), nas : char(9)) Accident (dateAc* : Date, nas* : char(9),dommage : number(7,2), villeAc : varchar(50), imma : char(6)) Notes : 1- Les types des attributs représentent les domaines syntaxiques. 2- Une personne est propriétaire d’une ou plusieurs voitures. 3- Une personne conduit qu’une voiture dont elle est propriétaire. 4- Il peut y avoir des homonymes dans la base différentiés par leur nas respectif. Rappel : La date externe est codée en interne (Oracle) d’une manière spéciale et comprend tous les éléments : année, mois, jour, heure et seconde. Si une valeur est doit être ajoutée dans la base avec des éléments absents, ces derniers sont pris par défaut en tenant compte des valeurs inscrites dans le fichier INIT.ora du système Oracle. Les éléments pris par défaut sont souvent dénués d’intérêt pour certaines applications et il faut les enlever au moment de l’exploitation ou au contraire les insérer explicitement lors d’un ajout. Les fonctions To_Char () et To_Date() sont utiles pour cette opération. *** Formulez une clause SQL pour répondre aux requêtes ci-dessous *** 1- Trouvez le nombre total de propriétaires de voitures impliquées dans un accident de la route entre 1998 et 2002, soit une période de 5 ans. Réponse : Les fonctions To_Char() et to_Number() permettent respectivement de conserver que la partie année de la date interne et ensuite de convertir cette année en un nombre au moyen de la fonction To_Number(). Select Count(DISTINCT nas) From Accident Where To_Number(To_char(dateAc, 'YYYY')) Between 1998 and 2002;

Source : http://www.ift.ulaval.ca/~agamache/pageperso/LivreBD/PageIntroLivreBD.html

 André Gamache

2

2- Trouvez le nom et le nas des propriétaires de voiture qui ont fait deux accidents dans un intervalle de 4 mois. Réponse : Select DISTINCT P.nas, P.nom From Accident AC1, Accident Ac2, Personne P Where Ac1.nas = Ac2.nas And Months_Between (Ac2.dateAc, Ac1.dateAc) 1000; 9- Affichez le nom des propriétaires de voiture qui résident dans une ville où il y a eu plus de 1000 accidents. Réponse : Après avoir sélectionné les villes où il y a eu plus de 1000 accidents par une sous-requête, il y a une sélection de premier niveau effectuée dans la table Personne avec un prédicat d’inclusion utilisant un ensemble énuméré calculé au préalable au tout début du traitement. Select nom From Personne Where Personne.villeP IN ( Select villeAc From Accident Group by villeAc Having Count (*) > 1000); La première requête de niveau 2 fournit les villes où il y a eu plus de 1000 accidents. Ces villes sont celles utilisées par le prédicat d’inclusion pour sélectionner les personnes qui résident dans ces villes.

 André Gamache

4

10- Affichez le nom des propriétaires d’une voiture accidentée qui résident dans une ville où il y a eu plus de 1000 accidents. Select nom From Personne P, Voiture V, Accident A Where P.villeP IN ( Select villeAc From Accident Group by villeAc Having Count (*) > 1000) and V.mma = A.imma And V.nas = P. nas ; 11- Affichez le ratio du nombre d’accidents par ville et cela par rapport au nombre total des accidents. Pour répondre à cette question, vous pouvez créer une vue relationnelle : NbAc ou encore utiliser le produit cartésien. Réponse : ??????? (Oracle) Select Count(*) / (Select Count( *) From Accident) From Accident A Group by A.villeAc; La version SQL implantée avec Oracle n’autorise pas une sous-requête dans la clause Select. Cela est conforme à l’algèbre relationnelle dont la projection n’autorise pas une sous-requête dans sa liste de projection. Solution qui fait appel à un produit cartésien et une vue relationnelle ou une table calculée par une sousrequête : Create view nbAc as Select count(*) nb From accident ; Select Count(*) / N.nb From Accident A, NbAc N Group by A.villeAc, N.nb; ou encore : Select Count(*) / N.nb From Accident A, (Select count(*) nb From Accident) N Group by A.villeAc, N.nb;

 André Gamache

5

12- Listez le nom des personnes qui ont eu un ou plusieurs accidents dans la ville de leur résidence. Réponse : Il faut prendre en compte que les personnes de même nom peuvent avoir des nas différents et avoir eu des accidents. La jointure est faite avec une table de base et une table calculée par une sous-requête. Select P.nom From Personne PP, (Select Distinct From Personne P, Accident A Where P.nas = A.nas And P.villep = A.villeAc ) PA Where PP.nas = Pa.nas; 13- Diminuez de 5% les dommages à chaque véhicule dont les dommages dépassent les 5000.00. Réponse : Le prédicat de sélection peut contenir des expressions évaluées pour chaque tuple examiné. Notez cependant que ce test ne change en rien les valeurs du tuple testé et qui peut être sélectionné. UPDATE Accident Set dommage = dommage* .95 Where dommage > 5000.00; 14- Affichez l’heure (incluant les minutes) des accidents dans lesquels sont impliqués le véhicule immatriculé QCV123 au cours du mois de juin. Réponse : Plusieurs formulations sont possibles selon le type utilisé pour faire la sélection. Dans la première, la date de l’accident est convertie en une chaîne de caractères pour être comparée avec une constante de chaîne. Select To_Char(dateAc,HH:MI’) From Accident Where imma = ‘QCV123’ And To_Char(dateAC, ‘MM’)= ’06’; Dans la deuxième formulation, le mois de juin est codé comme un nombre, et la conversion du nombre en une chaîne est automatique. Select To_Char(dateAc, ‘HH:MI’) From Accident Where imma = ‘QCV123’ And To_Char(dateAC, ‘MM’)= 6; Dans la clause ci-dessous, le mois de juin est codé en clair et en français. Comme le développeur peut ignorer le format par défaut, il peut indiquer que le mois est en français au moyen du 3e argument. Select To_Char(dateAc, ‘HH:MI’)

 André Gamache

6

From Accident Where imma = ‘QCV123’ And To_Char(dateAC, ‘MONTH’, ‘NLS_DATE_LANGUAGE = french’)= ‘JUIN’; 15- Combien d’accidents sont survenus le 24 juin 2000 peu importe l’heure ? Réponse :Le format interne code toujours la date avec tous ses éléments, y compris l’heure. Il faut donc indiquer dans le masque de la fonction To_Date() quels sont les éléments de la date format interne qui sont à utiliser dans le calcul de la requête. Select Count(*) From Accident Where imma = ‘QCV123’ And To_Char(dateAC, ‘DD/MONTH/YYYY’)= ‘24/JUIN/2000’; Select Count(*) From Accident Where imma = ‘QCV123’ And To_Char(dateAC,‘DD/MONTH/YYYY’) = To_Char(‘24/JUIN/2000’, ‘DD/MONTH/YYYY’; Select Count(*) From Accident Where imma = ‘QCV123’ And Trunc(dateAC) = To_Date(‘24/JUIN/2000’, ‘DD/MONTH/YYYY’; 16- Quel est le premier accident qui a fait l’objet d’un enregistrement ? Réponse : Une fonction imbriquée est évaluée en premier. Select To_Char(Min(dateAc), ‘DD-MM-YYYY’) FromAccident ; 17- Comptez le nombre d’accidents survenus à Montréal il y a dix jours. Réponse : Select Count(*) From Accident Where villeAc = ‘Montréal’ and Trunc(dateAc) = Sysdate –10; 18- Affichez la date de l’accident le plus récent à Québec impliquant un modèle TGLuxe. Réponse : Select Sysdate – Max(dateAc) From Accident Ac, Voiture V

 André Gamache

7

Where Ac.imma = V.imma And V.modele = ‘TGLuxe’ And villeAc = ‘Québec’; 19- Affichez pour chaque ville le nas et le nom des personnes impliquées dans les accidents survenus entre 22 :00 et 24 :00 peu importe le jour. Réponse : Select P.nas, P.nom From Personne P, Accident Ac Where P.nas = Ac.nas And To_Number( To_Char(Ac.dateAc, ‘HH24’) Between 22 and 24) Group By Ac.villeAc; 20- Voici une table formée de trois colonnes : Production :

dateProd*

matricule*

qte

2002/12/2

12345

135

2002/12/3

12345

125

2002/11/23

23456

76

2002/11/24

23456

110

2002/11/23

78904

105

Il faut afficher la date de production la plus récente pour chaque matricule et fournir la quantité enregistrée pour cette date. Réponse : La réponse peut être calculée sans utilisation d’une vue relationnelle. La sous-requête calcule une table P(maxDateProd, matricule) fournissant la date la plus récente pour chaque matricule. Le Group By permet de calculer la plus grande date de production pour chaque matricule. Il ne permet pas cependant d’afficher d’autres attributs que ceux inclus dans le groupement. Pour contourner cette difficulté, il s’agit de faire appel à une sous-requête pour calculer une table temporaire ré-utilisée dans une jointure. Select P.estampille,P1.matricule, P1.qte From Production P1, (Select Max(dateProd) maxDateProd, matricule From Production Group by matricule) P Where P1.matricule = P.matricule And P.maxDateProd = P1.dateProd;

 André Gamache