Support de formation 769796370.doc Support de formation 769796370.doc SOMMAIRE INTRODUCTION ....................................................................................... 3 LE LANGAGE DE MANIPULATION DE DONNEES ................................ 4 La commande SELECT .......................................................................................................... 6 SELECT, FROM ................................................................................................................... 7 DISTINCT .............................................................................................................................. 7 ORDER BY............................................................................................................................ 8 WHERE.................................................................................................................................. 9 CONVERT ........................................................................................................................... 12 LIKE ...................................................................................................................................... 12 AND ...................................................................................................................................... 13 OR ........................................................................................................................................ 14 IN .......................................................................................................................................... 14 NOT IN ................................................................................................................................. 15 TRAVAUX PRATIQUES N° 1 ............................................................................................... 16 Utilisation des fonctions d'agrégation dans la liste de sélection ..................................... 17 COUNT(*) ............................................................................................................................ 18 COUNT(DISTINCT colonne) ............................................................................................ 18 AVG ...................................................................................................................................... 18 MAX ...................................................................................................................................... 18 SUM...................................................................................................................................... 18 Utilisation des fonctions de dates ........................................................................................ 19 Date du jour GETDATE() .................................................................................................. 19 DATEADD, DATEDIFF...................................................................................................... 19 DATEPART ......................................................................................................................... 19 MONTH ,YEAR ,DAY et WEEKDAY............................................................................... 19 TRAVAUX PRATIQUES N° 2 ............................................................................................... 21 Utilisation des fonctions d'agrégation et des regroupements sur des requêtes simples (1 seule table) ...................................................................................................... 22 GROUP BY ......................................................................................................................... 22 HAVING ............................................................................................................................... 23 TRAVAUX PRATIQUES N° 3 ............................................................................................... 25 Requêtes simples sur plusieurs tables ........................................................................... 26 JOINTURE .......................................................................................................................... 26 TRAVAUX PRATIQUES N° 4 ............................................................................................... 29 Requêtes complexes sur plusieurs tables ..................................................................... 30 TRAVAUX PRATIQUES N° 5 ............................................................................................... 35 La commande INSERT INTO ............................................................................................... 36 La commande UPDATE ........................................................................................................ 37 La commande DELETE ........................................................................................................ 37 TRAVAUX PRATIQUES N° 6 ............................................................................................... 38 LE LANGAGE DE DEFINITION DE DONNEES........................................ 1 Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 1 Support de formation 769796370.doc Création, modification et suppression d’une base de données ........................................ 1 CREATION, MODIFICATION ET SUPPRESSION D’UNE TABLE.................................. 5 Création, modification et suppression d’une vue................................................................. 8 TRAVAUX PRATIQUES N° 7 .................................................................................................. 9 ANNEXE 1 – SCHEMA DE LA BASE DE DONNES SKI ........................ 12 ANNEXE 2 – CONTENU DE LA BASE DE DONNES SKI ...................... 13 Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 2 Support de formation 769796370.doc INTRODUCTION Le langage de requête structuré, appelé S.Q.L. est la norme du langage des S.G.B.D. relationnels. Ce langage de données est formé de 2 parties : - Le langage de définition de données (LDD) - Le langage de manipulation de données (LMD) Le LDD Ce langage permet à l’administrateur de la base de données ou à un utilisateur autorisé de créer et de décrire les bases de données, tables, colonnes et index nécessaires à l’application ainsi que les contraintes d’intégrité et de sécurité associées. Le LMD Ce langage fournit un ensemble d’opérations qui assurent les opérations élémentaires de manipulation des données contenues dans la base de données. Les opérations de manipulation de données comprennent généralement : - L’insertion de nouvelles données dans la base de données, - La modification des données stockées dans la base de données, - La recherche et l’obtention de données présentes dans la base de données, - La suppression de données de la base de données. C’est l’algèbre relationnelle inventée par Codd (1971) qui a servi de base au langage de manipulation de données. C’est pourquoi avons débuté cette séance de formation par une brève présentation de l’algèbre relationnelle. Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 3 Support de formation 769796370.doc LE LANGAGE DE MANIPULATION DE DONNEES Pour cette séquence dont le but est d’apprendre le langage SQL standard, vous allez saisir directement les commandes SQL dans l’outil de requêtage de SQL SERVER 2005 express que vous pouvez activer à partir de Management studio . Vous utiliserez la base de données SKI (créée dans la séance N°1) que vous transfèrerez sur SQL SERVER à l’aide de la documentation SQL_INST.doc. Après avoir installé votre base de données sur SQL SERVER, afin de tester vos premières requêtes sur la base de données SKI, Allumez votre poste de travail, à partir du bouton démarrer lancez SQL server management studio Cliquez sur se connecter afin de se connecter au serveur de bases de données Vous devez voir l’écran suivant : Déroulez le dossier bases de données et cliquez sur la base de données SKI Puis Cliquez sur le bouton nouvelle requête en haut à gauche de l’écran Vous avez maintenant l’écran suivant : Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 4 Support de formation 769796370.doc C’est la base de données SKI qui est active Zone de saisie des requêtes Explorateur d’objets, pour que cette zone apparaisse appuyer sur l’icône explorateur d’objets Vous pouvez commencer à tester votre 1ère requête : Affichez les objets de la base de données ski en cliquant dessus dans la partie explorateur d’objets vous devez voir : Dans la partie zone de saisie écrivez « select * from skieur» et cliquez sur Exécuter le résultat de la requête apparaît alors en bas : Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 5 Support de formation 769796370.doc La commande SELECT C’est l’élément clé de toute question posée à une base de données. Quand on crée et exécute une commande SELECT, on interroge la base de données à l’aide d’une requête. La commande SELECT La commande SELECT extrait les lignes de la base de données et permet la sélection d'une ou de plusieurs lignes ou colonnes, dans une ou plusieurs tables. Syntaxe : La syntaxe complète de l'instruction SELECT est complexe mais en voici les principales clauses SELECT [Distinct] Liste de la ou des colonnes à extraire (séparées par des virgules ) [ INTO nouveau nom de table ] FROM Nom de la ou des tables sources (séparées par des virgules) [ WHERE Condition_restriction ] [ GROUP BY Expression_regroupement ] [ HAVING condition-regroupement ] [ ORDER BY expression_tri ou noms de colonnes [ ASC | DESC ] ] Le mot-clé SELECT précède l’indication des colonnes désirées dans le résultat final. La projection peut porter sur l’ensemble des colonnes (dans ce cas on utilise *) ou sur une liste spécifiée. Pour rejoindre la dimension ensembliste de l’algèbre relationnelle, l’option DISTINCT permet d’éliminer les doublons (attention : elle n’est pas implicite!). Le mot-clé FROM permet de préciser les noms des tables utilisées. Si plus d’une table est mise en oeuvre, SQL effectue le produit cartésien des tables ; bien que le traitement soit optimisé (avec plus ou moins de bonheur suivant les implémentations...), l’utilisation de plusieurs tables dans une même requête augmente considérablement les consommations de ressources (temps processeur, espace disque). Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 6 Support de formation 769796370.doc Testez les requêtes suivantes : SELECT, FROM On désire afficher les informations sur les skieurs Avant d’écrire la requête en SQL on la prépare : Table : Skieur Projection : Toutes les colonnes On écrira en SQL : SELECT * FROM skieur; Attention à ne pas oublier le point virgule qui termine la commande SQL L'astérisque (*) indique que la projection porte sur toutes les colonnes Vous devez voir l’écran de la page suivante : Affichez la liste des stations répertoriées Table : Station Projection : nomstat SELECT nomstat FROM station; DISTINCT L'emploi du mot clé DISTINCT permet de supprimer les doublons. Affichez la liste des stations ou une compétition a été organisée Table : Compétition Projection : nomstat (sans doublon) SELECT DISTINCT nomstat FROM competition; Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 7 Support de formation 769796370.doc ORDER BY La clause ORDER BY trie les résultats d'une requête sur une ou plusieurs colonnes de 8 060 octets maximum. Un tri peut être effectué dans un ordre croissant (ascendant, ASC) ou décroissant (descendant, DESC). Si l'ordre de tri n'est pas spécifié, le tri par défaut est ascendant. Affichez la liste des skieurs par ordre alphabétique Table : Skieur Projection : nomski Séquence (tri) : nomski SELECT DISTINCT nomski FROM skieur ORDER BY nomski; Il est inutile d’écrire ‘ORDER BY nomski asc’ car ‘asc’ est l’option par défaut. Par contre si on voulait un tri décroissant on écrirait : ‘ORDER BY nomski desc’ Classer les skieurs par station d'origine en croissant et par nom en décroissant SELECT nomstat,nomski FROM skieur ORDER BY nomstat, nomski desc; Remarque : On peut aussi écrire ORDER BY 1,2 où 1 représente le 1er champ de la projection et 2 le 2ème champ de la projection. Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 8 Support de formation 769796370.doc WHERE Critères de recherche Opérateurs de comparaison Les opérateurs de comparaison testent si oui ou non deux expressions sont identiques. Ils peuvent s'utiliser sur toutes les expressions, à l'exception des expressions de type de données memo ou image. Opérateur Description = (Égal à) Égal à > (Supérieur à) Supérieur à < (Inférieur à) Inférieur à >= (Supérieur ou égal à) Supérieur ou égal à <= (Inférieur ou égal à) Inférieur ou égal à <> (Différent de) Différent de != (Différent de) Différent de (hors norme SQL-92) !< (Non inférieur à) Non inférieur à (hors norme SQL-92) !> (Non supérieur à) Non supérieur à (hors norme SQL-92) Le résultat d'un opérateur de comparaison est de type booléen et peut prendre trois valeurs : TRUE, FALSE ou UNKNOWN. Les expressions qui retournent un type de données booléen sont dites expressions booléennes. Affichez la liste des stations d'altitude >= 2000 mètres Table : Station Projection : nomstat,altstat Restriction : altstat >=2000 SELECT nomstat,altstat FROM station WHERE altstat>=2000; Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 9 Support de formation 769796370.doc Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 10 Support de formation 769796370.doc Critères de recherche par intervalle Une recherche par intervalle renvoie toutes les valeurs comprises entre deux valeurs données. Les intervalles inclusifs renvoient toutes les valeurs comprises entre les deux valeurs données comprises. Les intervalles exclusifs ne renvoient aucune valeur correspondant aux deux valeurs spécifiées. Le mot clé BETWEEN spécifie la recherche d'un intervalle inclusif Affichez la liste des stations dont l'altitude est comprise entre 2000 et 3000 mètres SELECT nomstat, altstat FROM station WHERE altstat BETWEEN 2000 AND 3000; Pour spécifier un intervalle exclusif, employez les opérateurs « supérieur à » et « inférieur à » (> et <). La requête suivante, qui utilise les opérateurs « supérieur à » et « inférieur à », donne des résultats différents de l'exemple précédent car ces opérateurs n'incluent pas les lignes correspondant aux valeurs qui limitent l'intervalle. SELECT nomstat, altstat FROM station WHERE altstat > 2000 AND altstat <3000; Donner la liste des spécialistes de la descente Table : Skieur Projection : nomski,specialite Restriction : specialité=’Descente’ SELECT nomski,specialite FROM skieur WHERE specialite='Descente'; A quelle date la competition Comp2 est-t-elle programmée? Table : Competition Projection : datcomp Restriction : refcomp=’comp2’ SELECT datcomp FROM competition WHERE refcomp= 'Comp2'; On note que le format de date est surprenant : Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 11 Support de formation 769796370.doc CONVERT Pour obtenir un format plus lisible, on peut utiliser la fonction CONVERT que vous pouvez découvrir dans l’aide sur Transact SQL (Shift+F1 à partir de l’analyseur de requêtes) Select CONVERT (char(8),datcomp,3) as datcomp from competition where refcomp= 'Comp2'; LIKE Liste des stations qui commencent par 'A' Table : station Projection : nomstat Restriction : nomstat comme ‘A%’ Select nomstat from station where nomstat LIKE 'A%'; LIKE détermine si une chaîne de caractères données correspond à une chaîne spécifiée, on utilisera le caractère générique * pour remplacer une chaîne de zéro caractères ou plus . Si on avait écrit LIKE ‘%I%’ cela correspondrait à rechercher toutes les stations qui ont I dans leur nom. On peut aussi utiliser le [ : pour rechercher par exemple toutes les stations qui ont A ou N dans leur nom on écrira : Select nomstat from station where nomstat LIKE '%[AN]%'; Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 12 Support de formation 769796370.doc AND Liste des skieurs qui ont le slalom pour spécialité et qui habitent Tignes Table : Skieur Projection : nomski,specialite,nomstat Restriction : specialite=’slalom’ et nomstat=’Tignes’ SELECT nomski,specialite,nomstat FROM skieur WHERE specialite='Slalom ' AND nomstat = 'Tignes'; Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 13 Support de formation 769796370.doc OR Liste des skieurs qui sont originaires de Tignes ou de Meribel Table : Skieur Projection : nomski, nomstat Restriction : nomstat=’Tignes’ ou nomstat=’Meribel’ SELECT nomski,nomstat FROM skieur WHERE nomstat='Tignes' OR nomstat='Meribel'; IN IN Détermine si une valeur donnée correspond à la valeur d'une liste ou d'une sous-requête. Comparaison de OR et IN Cet exemple sélectionne la liste des noms des skieurs qui sont spécialistes en bosses ou en SuperG ou en slalom On peut écrire SELECT nomski,specialite FROM skieur WHERE specialite = 'Bosses' OR specialite='SuperG' OR specialite= 'Slalom'; Cependant, vous obtenez les mêmes résultats en utilisant IN : SELECT nomski,specialite FROM skieur WHERE specialite IN ( 'Bosses','SuperG', 'Slalom'); Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 14 Support de formation 769796370.doc NOT IN Liste des skieurs qui ne sont pas spécialistes de la descente On peut écrire Select nomski, specialite from skieur where specialite <>'Descente'; On peut aussi écrire Select nomski, specialite from skieur where specialite NOT IN ('Descente'); Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 15 Support de formation 769796370.doc TRAVAUX PRATIQUES N° 1 Requêtes simples sur une seule table Préparez, rédigez et testez les requêtes suivantes : Liste des stations françaises Liste des stations françaises d'altitude supérieure à 2000 mètres Référence et date des compétitions se déroulant à Tignes Liste des compétitions pour lesquelles le skieur N° 3 est arrivé premier ou deuxième Classement (du premier au dernier) de la compétition N°2 Liste des compétitions qui auront lieu entre le 01/02/07 et le 01/05/07 Liste des skieurs dont le nom contient "om" ou "au" Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 16 Support de formation 769796370.doc Utilisation des fonctions d'agrégation dans la liste de sélection Les fonctions d'agrégation (SUM, AVG, COUNT, COUNT(*), MAX et MIN) génèrent des valeurs de synthèse dans les jeux de résultats des requêtes. Une fonction d'agrégation (à l'exception de COUNT(*)) traite toutes les valeurs sélectionnées d'une même colonne pour produire une valeur de résultat unique. Les fonctions d'agrégation peuvent s'appliquer à toutes les lignes d'une table, à un sous-groupe d'une table spécifié dans une clause WHERE, ou à un ou plusieurs groupes de lignes dans une table. Une fonction d'agrégation génère une seule valeur pour chaque ensemble de lignes auquel elle s'applique. Le tableau suivant présente la syntaxe des fonctions d'agrégation et leurs résultats (expression est presque toujours un nom de colonne). Fonction d'agrégation Résultat SUM([ALL | DISTINCT] expression) Total des valeurs de l'expression numérique AVG([ALL | DISTINCT] expression) Moyenne des valeurs l'expression numérique COUNT([ALL | DISTINCT] expression) Nombre de valeurs figurant dans l'expression COUNT(*) Nombre de lignes sélectionnées MAX(expression) Valeur la plus grande de l'expression MIN(expression) Valeur la plus petite de l'expression dans SUM, AVG, COUNT, MAX et MIN ignorent les valeurs NULL ; COUNT(*) en tient compte. Le mot-clé facultatif DISTINCT peut s'utiliser avec SUM, AVG et COUNT pour supprimer les doublons avant l'application d'une fonction d'agrégation (la valeur par défaut est ALL). Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 17 Support de formation 769796370.doc COUNT(*) Affichez le nombre de skieurs originaires de Tignes SELECT count(*) as nb_skieur FROM skieur WHERE nomstat='Tignes'; Donnez le nombre de victoires du skieur N° 3 SELECT COUNT(*) as nb_victoire FROM classement WHERE numski=3 AND rang=1; COUNT(DISTINCT colonne) Affichez le nombre de spécialités répertoriées dans la table skieur SELECT COUNT(DISTINCT specialite) as nb_specialités FROM skieurs; . AVG Donnez la capacité moyenne des stations françaises SELECT AVG(capstat) as capacité_moy FROM station WHERE paystat='France'; MAX Quelle est capacité maximale d'accueil dans les stations ? SELECT MAX(capstat) as cap_max FROM station; SUM Quelle est la capacité totale des stations dont l’altitude est supérieure ou égale à 2000 mètres ? SELECT SUM(capstat) as cap_tot FROM station WHERE altstat>=2000 ; Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 18 Support de formation 769796370.doc Utilisation des fonctions de dates Quelles sont les compétitions programmées après le 20/08/07 SELECT refcomp,datcomp FROM competition WHERE datcomp>'20/08/07'; Date du jour GETDATE() Quelles sont les compétitions programmées après aujourd'hui SELECT refcomp,datcomp FROM competition WHERE datcomp>getdate(); DATEADD, DATEDIFF Les fonctions DATEADD et DATEDIFF permettent d’ajouter ou de soustraire 2 dates. Dans une fonction DATEDIFF, lorsque la première date est postérieure à la seconde date spécifiée, le résultat donne une valeur négative. La syntaxe de ces 2 fonctions est complexe, il est recommandé d’utiliser l’aide sur Transact SQL (Shift+F1 à partir de l’analyseur de requêtes). Quelles sont les compétitions qui sont prévues dans 10 jours ? SELECT refcomp,datcomp FROM competition WHERE DATEDIFF(dd,getdate(),datcomp)=10; DATEPART Renvoie un entier représentant l'élément de date précisé dans la date spécifiée. Quelles sont les compétitions qui auront lieu en mai 2007 SELECT * FROM competition WHERE DATEPART (yy,datcomp)= 07; DATEPART(mm,datcomp)=5 and MONTH ,YEAR ,DAY et WEEKDAY Renvoie un entier représentant l'élément de date précisé dans la date spécifiée. Quelles sont les compétitions qui auront lieu en mai 2007 SELECT * FROM competition WHERE MONTH(datcomp)=5 AND YEAR(datcomp)=2007; Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 19 Support de formation 769796370.doc Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 20 Support de formation 769796370.doc TRAVAUX PRATIQUES N° 2 Requêtes simples sur une seule table en utilisant les fonctions d’agrégat et les fonctions de dates Préparez, rédigez et testez les requêtes suivantes : Affichez le nombre de skieurs de ‘Meribel’ spécialisés en ‘SuperG’ Donnez l'altitude moyenne des stations françaises Donner le meilleur classement du skieur N°5 Quelles sont les compétitions programmées entre aujourd'hui et le 31/08/07 Quelles sont les compétitions qui auront lieu un lundi Donnez la date de la dernière compétition programmée Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 21 Support de formation 769796370.doc Utilisation des fonctions d'agrégation et des regroupements sur des requêtes simples (1 seule table) GROUP BY La Clause GROUP BY Indique les groupes dans lesquels les lignes de sortie doivent être placées et calcule une valeur de résumé pour chaque groupe lorsque des fonctions d'agrégation sont utilisées dans la clause SELECT. Lorsque GROUP BY est spécifiée, vous devez inclure dans la liste GROUP BY chaque colonne de toute expression de non-agrégation figurant dans la liste de sélection, ou l'expression GROUP BY doit correspondre exactement à l'expression figurant dans la liste de sélection. Remarque Si la clause ORDER BY n'est pas spécifiée, les groupes renvoyés à l'aide de la clause GROUP BY ne se trouvent pas dans un ordre particulier. Il est recommandé de toujours utiliser la clause ORDER BY pour définir un ordre de données particulier. Donnez le nombre de compétition programmées par station SELECT nomstat,count(*) as nb_competition FROM competition GROUP BY nomstat; Donnez le nombre de victoires pour chaque skieur SELECT numski, count(*) as nb_victoires FROM classement WHERE rang=1 GROUP BY numski; Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 22 Support de formation 769796370.doc Donnez le meilleur et le plus mauvais classement de chaque skieur figurant dans la table classement SELECT numski, min(rang) as meilleur,max(rang) as plus_mauvais FROM classement GROUP BY numski; HAVING La clause HAVING définit des critères de sélection pour la clause GROUP BY de la même façon que la clause WHERE le fait pour l'instruction SELECT. Les critères de recherche de la clause WHERE s'appliquent avant le regroupement, tandis que ceux de la clause HAVING s'appliquent après. La syntaxe de la clause HAVING est similaire à celle de la clause WHERE, à une exception près : elle peut contenir des fonctions d'agrégation. Les clauses HAVING peuvent faire référence à tous les éléments figurant dans la liste de sélection. Remarque Si la clause HAVING vient toujours après la clause GROUP BY. Donnez la liste des stations ou ont été programmées plus de 2 compétitions SELECT nomstat FROM competition GROUP BY nomstat HAVING count(*)>2; Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 23 Support de formation 769796370.doc Donnez la liste des skieurs ayant été classés premiers à au moins 2 compétitions SELECT numski,count(*) as nb_class FROM classement WHERE rang=1 GROUP BY numski HAVING count(*)>=2; Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 24 Support de formation 769796370.doc TRAVAUX PRATIQUES N° 3 Requêtes simples sur une seule table en utilisant les fonctions d’agrégat et les regroupements Préparez, rédigez et testez les requêtes suivantes : Affichez le nombre de skieurs par spécialité Donnez la liste des spécialités pour lesquelles il y a plus de 3 skieurs Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 25 Support de formation 769796370.doc Requêtes simples sur plusieurs tables JOINTURE Si nous voulons afficher des champ provenant de 2 (ou plusieurs) tables, il faut opérer une JOINTURE. Cette opération exploite pleinement l’aspect relationnel d’une base de données. La jointure entre 2 tables T1 et Té consiste à associer à chaque ligne de T1 la ligne de T2 qui lui correspond par l’égalité entre la clé étrangère et la clé primaire. Liste des compétitions programmées dans une station située à plus de 2000 mètres Produit cartésien : competition,station Projection : refcomp, nomstat, altstat Restriction de concordance station.nomstat=competition.nomstat Restriction utilisateur : altstat>2000 Commentaire : 1ère ligne : produit cartésien des deux tables. 2ème ligne : projection de colonnes issues de deux tables 3ème ligne : restriction de concordance 4ème ligne restriction utilisateur ; l’altitude doit être supérieure à 2000 SELECT refcomp FROM competition,station WHERE station.nomstat=competition.nomstat AND altstat>2000; Produit cartésien Restriction de concordance Restriction utilisateur On remarque que pour les champs ambigus (même nom de champ dans différentes tables) on précise le nom de la table devant le nom du champ. Exemple :Compétition.nomstat Afin de simplifier l’écriture, on peut utiliser un nom d’alias pour les tables. (Ce nom d’alias n’est valable que pour cette requête.) On écrira alors : SELECT S.refcomp,S.nomstat,S.altstat FROM competition C,station S WHERE S.nomstat=C.nomstat AND S.altstat>2000 Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Nom d’alias Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 26 Support de formation 769796370.doc Affichez le résultat (nom skieur, classement croissant) de la compétition N° 2 Produit cartésien : Skieur, classement Projection : nomski,rang Restriction de concordance : Skieur.numski=classement.numski Restriction utilisateur : refcomp=’comp2’ Tri : rang SELECT S.nomski,C.rang FROM skieur S ,classement C WHERE S.numski=C.numski AND C.refcomp='comp2' ORDER BY 2; Donner la liste des compétitions ou Laflêche a été classé SELECT C.refcomp FROM classement C,skieur S WHERE C.numski=S.numski AND nomski='Laflêche'; Affichez la liste des stations ou ‘La flèche’ a été classé premier ou deuxième SELECT CO.nomstat,CO.refcomp,C.rang FROM classement C,competition CO,skieur S WHERE C.refcomp=CO.refcomp AND C.numski=S.numski AND S.nomski='Laflêche' AND (rang=1 OR rang=2); Attention à la priorité des opérateurs AND et OR Lorsque vous exécutez une requête, elle évalue d'abord les clauses liées par AND, puis celles liées par OR. Remarque : L'opérateur NOT est prioritaire par rapport aux opérateurs AND et OR. Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 27 Support de formation 769796370.doc Pour ignorer la priorité par défaut de l'opérateur AND sur l'opérateur OR, placez les critères appropriés entre parenthèses dans le volet SQL. Les critères entre parenthèses sont toujours traités en premier. Conseil : Il est recommandé, dans un souci de clarté, de toujours insérer des parenthèses lorsque vous combinez les clauses AND et OR plutôt que de vous fier à la priorité par défaut attribuée par le système. Affichez la liste des skieurs (nom) ayant gagné une compétition dans leur station d'origine SELECT nomski FROM skieur S,classement CL,competition C WHERE CL.refcomp=C.refcomp AND rang=1 AND S.numski=CL.numski AND S.nomstat=C.nomstat; Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 28 Support de formation 769796370.doc TRAVAUX PRATIQUES N° 4 Requêtes simples sur plusieurs tables Préparez, rédigez et testez les requêtes suivantes : Affichez les référence,date,station, pays des compétitions classé par pays et par date décroissante Indiquez la station d'origine et l'altitude de cette station de tous les skieurs spécialistes des 'Bosses' Affichez l'altitude et la capacité des stations ou se déroulent 'Comp3' et 'Comp4' Affichez le nombre de skieurs originaires d'une station dont l'altitude est supérieure ou égale à 2000 mètres Affichez la liste des skieurs ayant été classés à plus de 2 compétitions Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 29 Support de formation 769796370.doc Requêtes complexes sur plusieurs tables L’auto-jointure Il est possible de joindre une table sur elle-même dans une auto-jointure. Par exemple, vous pouvez utiliser une auto-jointure pour trouver les skieurs qui sont originaires de la même station que le skieur ‘Lola’. Comme cette requête effectue une jointure de la table skieur avec elle-même, la table joue deux rôles. Vous avez 2 possibilités pour écrire cette requête : Soit en mode ensembliste : On écrira d’abord la requête qui affiche la station d’origine du skieur ‘Lola’ : Table : skieur Projection : nomstat Restriction : nomski=’Lola’ SELECT nomstat FROM skieur WHERE nomski=”Lola” Puis on écrira la requête qui affiche le nom des skieurs qui ont pour station d’origine celle trouvée dans le 1ère requête. Table : skieur Projection : nomski Restriction : nomstat=résultat 1ère requête On écrira : SELECT nomski FROM skieur WHERE nomstat=(SELECT nomstat from skieur where nomski=”lola”); Remarque1 : ‘Lola’ apparaît dans la liste. Pour ne pas la faire apparaître, il faut écrire : SELECT nomski FROM skieur WHERE nomstat=(SELECT nomstat from skieur where nomski='lola') and nomski<>'Lola'; Remarque2 : Ici la sous-requête ne renvoie qu’une seule valeur car Lola n’appartient qu’à une seule station. Si la sous requête devait renvoyer plusieurs valeurs, il faudrait utiliser IN avant le 2ème SELECT au lieu du ‘=’ Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 30 Support de formation 769796370.doc Soit en mode prédicatif : Comme cette requête effectue une jointure de la table skieur avec elle-même, la table joue deux rôles. Pour distinguer ces rôles, vous pouvez donner deux noms d'alias à la table skieur (SK1, SK2) dans la clause FROM. Ces noms d'alias qualifient les noms de colonne dans le reste de la requête. On écrira : SELECT SK2.nomski FROM skieur SK1, skieur SK2 WHERE SK2.nomstat=SK1.nomstat AND SK1.nomski=”Lola” AND SK2.nomski<> »Lola”; Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 31 Support de formation 769796370.doc La Clause INTO La clause INTO crée une nouvelle table et y insère les lignes résultant de la requête. Vous pouvez utiliser SELECT ... INTO pour créer une définition de table identique (portant un nom différent) sans aucune donnée en utilisant une condition FALSE dans la clause WHERE , cette clause sera aussi fréquemment utilisée pour créer des tables contenant des résultats temporaires. Exemple : On veut afficher le pourcentage de réussite du skieur ‘Laflêche’ (Nb de fois ou il est arrivé 1er /nb de compétitions auxquelles il a été classé) On peut stocker dans une première table le nombre de fois ou ‘Laflêche’ est arrivé premier SELECT count(*) as Nb_gain INTO temp1 FROM classement C,skieur S WHERE S.nomski='Laflêche' AND S.numski=C.numski AND C.rang=1; Puis dans une 2ème table le nombre de fois ou ‘Laflêche’ a été classé SELECT count(*) as NB_part INTO temp2 FROM classement C,skieur S WHERE S.nomski='Laflêche' AND S.numski=C.numski; On fera ensuite la division : SELECT nb_gain/(select nb_part from temp2)*100 as pourcent_reuss from temp1; Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 32 Support de formation 769796370.doc L’union L'opérateur UNION vous permet de combiner les résultats de deux ou plusieurs instructions SELECT en un seul jeu de résultats. Les jeux de résultats combinés à l'aide de UNION doivent tous avoir la même structure. Ils doivent posséder le même nombre de colonnes et les types de données des colonnes correspondantes du jeu de résultats doivent être compatibles. La syntaxe de UNION se présente ainsi : Instruction select UNION [ALL] Instruction select Par exemple, Table1 et Table2 ont la même structure à deux colonnes. Table1 Table2 Colonne A Colonne B Colonne A Colonne B char(4) int char(4) int abc 1 ghi 3 def 2 jkl 4 ghi 3 mno 5 La requête suivante crée une UNION entre les tables : SELECT * FROM Table1 UNION SELECT * FROM Table2 Voici le jeu de résultats obtenu : Colonne A abc def ghi jkl mno Colonne B 1 2 3 4 5 Les noms des colonnes de jeux de résultats d'une UNION sont identiques à ceux des colonnes du jeu de résultats de la première instruction SELECT de l'UNION. Les noms des colonnes de jeux de résultats des autres instructions SELECT sont ignorés. Par défaut, l'opérateur UNION supprime les lignes en double du jeu de résultats. Si vous employez l'option ALL, toutes les lignes apparaissent dans les résultats et les doublons ne sont pas supprimés. On peut aussi utiliser l’opérateur UNION à la place de OR Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 33 Support de formation 769796370.doc Exemple : On désire afficher la liste des skieurs spécialistes de la descente ou habitant à Tignes. On peut écrire : SELECT nomski,specialite,nomstat FROM skieur WHERE specialite='descente' UNION (SELECT nomstat='Tignes') nomski,specialite,nomstat FROM skieur WHERE En écrivant : SELECT nomski,specialite,nomstat from skieur WHERE specialite='descente' OR nomstat='Tignes'; On obtient le même résultat La différence La différence entre 2 tables contient les lignes qui appartiennent à la première moins celles qui appartiennent à la seconde. 2 opérateurs pourrant être utilisés pour faire une différence : NOT IN ou NOT EXIST Exemple : on veut établir la liste des stations qui n’ont jamais organisé de compétitions. Il s’agit des stations qui sont présentes dans la table station et qui ne sont pas présentes dans la table compétition On écrira : SELECT nomstat FROM station WHERE nomstat NOT IN (SELECT nomstat from competition); ou SELECT nomstat FROM station WHERE NOT EXISTS (SELECT * FROM competition WHERE station.nomstat=competition.nomstat); Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 34 Support de formation 769796370.doc TRAVAUX PRATIQUES N° 5 Requêtes complexes sur plusieurs tables Préparez, rédigez et testez les requêtes suivantes : Affichez la liste des skie0urs qui n’ont jamais été classés à une compétition Affichez la liste des skieurs qui ont été classés sur les mêmes compétitions que ‘Tomba’ Créez une nouvelle table qui ne contiendra que les skieurs dont la spécialité est la descente. Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 35 Support de formation 769796370.doc La commande INSERT INTO L'instruction INSERT permet d'ajouter une ou plusieurs nouvelles lignes à une table. Dans un traitement simplifié, le format de l'instruction INSERT est le suivant : INSERT [INTO] nom_de_table [(liste de colonne)] liste_de_valeurs Les valeurs de données fournies doivent correspondre à la liste de colonnes. Le nombre de valeurs de données doit être identique à celui des colonnes, et le type de données, la précision et l'échelle de chaque valeur de données doivent correspondre à ceux de la colonne correspondante. Il existe deux manières de spécifier les valeurs de données : Utilisation d'une clause VALUES pour spécifier les valeurs de données pour une ligne : Exemple : INSERT INTO skieur (numski,nomski, specialite,nomstat) VALUES (12, ‘Dupont’,’descente’,'Meribel’); Utilisation d'une sous-requête SELECT pour spécifier les valeurs de données pour une ou plusieurs lignes. INSERT INTO skieur (numski,nomski, specialite,nomstat) SELECT * from skieur2 (Toutes les lignes de skieur2 seront ajoutées à la table skieur en respectant évidemment l’unicité de la clé primaire) Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 36 Support de formation 769796370.doc La commande UPDATE L’instruction UPDATE modifie les données existantes d'une table. Dans un traitement simplifié, le format de l'instruction UPDATE est le suivant: UPDATE Nom_de_table SET nom_de colonne = { expression | DEFAULT | NULL } [ WHERE condition de recherche ] Exemples : Pour changer la station d’origine du skieur ‘Laflêche’ en mettant comme nouvelle station ‘Meribel’ On écrira : UPDATE skieur SET nomstat=’Meribel’ WHERE nomski=’Laflêche’ ; Pour augmenter de 10% la capacité de la station ‘Meribel’ On écrira : UPDATE station SET capstat=capstat*1.1 WHERE nomstat=’Meribel’ La commande DELETE L’instruction DELETE supprime des lignes dans une table. Dans un traitement simplifié, le format de l'instruction DELETE est le suivant : DELETE FROM Nom_de_table [ WHERE { < condition_de_recherche > ] exemple : DELETE FROM skieur where nomstat=’Meribel’ Supprime de la table skieur toutes les lignes correspondantes aux skieurs originaires de ‘Meribel’. Auteur Thérèse BLANCHARD Formation Séquence Séance Phase T.S.G.E.R.I. 8 2 2 Type de document Support de formation Date Page 17/04/2017 37 Support de formation 769796370.doc TRAVAUX PRATIQUES N° 6 Instructions UPDATE, INSERT INTO, DELETE Rédigez et testez les requêtes suivantes : Essayez d’insérer la ligne suivante dans la table compétition - ‘comp3’,24-03-2007,’Tignes’ Rencontrez vous un problème, expliquez. Même exercice avec la ligne - ‘comp1’, ‘bill’, 8 dans classement Insérez le skieur Alphand, originaire de Serres-Chevalier dans la base. Supprimez de la table compétition les compétitions achevées. Ajoutez 20 places à la capacité de la station ‘Tignes’ Auteur Formation Thérèse BLANCHARD T.S.G.R.I. Séquence Séance Phase 6 2 2 Type de document Date Support de formation 17/04/2017 Page 38 Support de formation 769796370.doc LE LANGAGE DE DEFINITION DE DONNEES Dans cette partie seuls des exemples commentés sont fournis, vous trouverez la syntaxe détaillée de chacune des commandes du L.D.D. dans l’aide en ligne TRANSACT SQL SERVER Création, modification et suppression d’une base de données CREATE DATABASE Crée une nouvelle base de données ainsi que les fichiers utilisés pour stocker la base de données. Microsoft® SQL Server™ 2000 mappe une base de données sur un ensemble de fichiers du système d'exploitation. Les données et les informations des journaux ne figurent jamais sur le même fichier ; par ailleurs, les fichiers individuels ne sont utilisés que par une base de données. Les bases de données SQL Server 2000 possèdent trois types de fichiers : Fichiers de données primaires Le fichier de données primaire constitue le point de départ de la base de données et il pointe vers les autres fichiers de la base de données. Chaque base de données comprend un fichier de données primaire. L'extension de fichier recommandée est .mdf. Fichiers de données secondaires Ces fichiers comprennent tous les fichiers de données autres que le fichier de données primaires. Certaines bases de données possèdent plusieurs fichiers de données secondaires, tandis que d'autres n'en possèdent aucun. L'extension de fichier recommandée est .ndf. Fichiers journaux Ces fichiers contiennent toutes les informations de suivi nécessaires à la récupération de la base de données. Il doit y avoir au moins un fichier journal par base de données. L'extension de fichier recommandée est .ldf. Remarque : SQL Server 2000 ne vous oblige pas à utiliser les extensions de fichier .mdf, .ndf et .ldf, mais celles-ci sont utiles pour vous aider à identifier les fichiers. Dans SQL Server 2000, les emplacements de tous les fichiers d'une base de données sont enregistrés à la fois dans la base de données master et dans le fichier primaire de la base de données. En général, le moteur de base de données Auteur Formation Thérèse BLANCHARD T.S.G.ER.I. Séquence Type de document 8 Support de formation Date 17/04/2017 Page 1 Support de formation 769796370.doc utilise les informations d'emplacement de fichier contenues dans la base de données master. Toutefois, pour certaines opérations, le moteur de base de données utilise les informations d'emplacement de fichier contenues dans le fichier primaire afin d'initialiser les entrées d'emplacement de fichier dans la base de données master : Exemple1 : création d’une base de données simple Cet exemple crée une base de données appelée Produits et spécifie un seul fichier. (Quand on ne met rien derrière ON, on ne crée que le fichier PRIMARY) Le fichier spécifié devient le fichier primaire et un fichier du journal des transactions de 1 Mo est créé automatiquement par défaut. Le paramètre SIZE n'étant spécifié ni en Mo ni en Ko pour le fichier primaire, celui-ci est alloué en Méga-octets. Puisqu'il n'y a pas de description pour le fichier du journal des transactions, ce fichier n'a pas de paramètre MAXSIZE et il peut croître jusqu'à occuper tout l'espace disque disponible. USE master GO CREATE DATABASE Produit Nom logique de la base de données ON ( NAME = produit_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\produit.mdf', SIZE = 4, MAXSIZE = 10, FILEGROWTH = 1 ) Nom physique de la GO base de données Résultat obtenu : Le processus CREATE DATABASE alloue 4.00 Mo sur le disque 'produit_dat'. Le processus CREATE DATABASE alloue 1.00 Mo sur le disque 'Produit_log'. Page 2 Auteur Thérèse BLANCHARD Formation T.S.G.E.R.I. Séquence 8 Type de document Support de formation Date 17/04/2017 Support de formation 769796370.doc Exemple2 : création d’une base de données avec des groupes de fichiers Cet exemple crée une base de données appelée Ventes avec deux groupes de fichiers : Le groupe de fichier primaire avec le fichier Ventes_dat. Les incréments FILEGROWTH de ce fichier est spécifié à 15 %. Un groupe de fichiers appelé VentesGroup avec les fichiers Vgrp1 et Vgrp2. CREATE DATABASE Ventes ON PRIMARY Création du groupe ( NAME = Ventes_dat, Primaire FILENAME = 'c:\program files\microsoft sql server\mssql\data\Ventes_dat.mdf', SIZE = 10, Création du groupe MAXSIZE = 50, secondaire FILEGROWTH = 15% ), FILEGROUP VentesGroup ( NAME = Vgrp1_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\Vgrp1.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ), ( NAME = Vgrp2_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\SVgrp2.ndf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) Création du journal des LOG ON transactions ( NAME = 'Ventes_log', FILENAME = 'c:\program files\microsoft sql server\mssql\data\Ventes_log.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO Résultat obtenu : Le processus CREATE DATABASE alloue 10.00 Mo sur le disque 'Ventes_dat'. Le processus CREATE DATABASE alloue 10.00 Mo sur le disque 'Vgrp1_dat'. Le processus CREATE DATABASE alloue 10.00 Mo sur le disque 'Vgrp2_dat'. Le processus CREATE DATABASE alloue 5.00 Mo sur le disque 'Ventes_log'. Auteur Formation Thérèse BLANCHARD T.S.G.ER.I. Séquence Type de document 8 Support de formation Date 17/04/2017 Page 3 Support de formation 769796370.doc ALTER DATABASE ALTER DATABASE Ajoute des fichiers ou groupes de fichiers à une base de données, ou les supprime. Cette instruction sert également à modifier les attributs de fichiers ou de groupes de fichiers, tels le nom ou la taille d'un fichier. ALTER DATABASE permet de modifier le nom de la base de données, les noms des groupes de fichiers et les noms logiques des fichiers de données et des fichiers journaux. Exemple1 : L’exemple suivant augmente la taille du fichier produit_dat de la base de données produit : ALTER DATABASE produit MODIFY FILE ( NAME=produit_dat,SIZE=7MB), Exemple2 : L 'exemple suivant ajoute un fichier journal de 2 Mo à la base de données Produit créée précédemment : ALTER DATABASE Produit ADD LOG FILE ( NAME = produitlog, FILENAME = 'c:\Program Files\Microsoft SQL server\MSSQL\Data\produit_log.ldf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) DROP DATABASE DROP DATABASE supprime une ou plusieurs bases de données de Microsoft® SQL Server™. La suppression d'une base de données supprime la base de données ainsi que les fichiers disque qu'elle utilise. Exemple : L'exemple suivant supprime la base de données Produit créée précédemment : DROP DATABASE Produit ; Page 4 Auteur Thérèse BLANCHARD Formation T.S.G.E.R.I. Séquence 8 Type de document Support de formation Date 17/04/2017 Support de formation 769796370.doc CREATION, MODIFICATION ET SUPPRESSION D’UNE TABLE CREATE TABLE CREATE TABLE crée une nouvelle table dans la base de données produit. Exemple1 : Création d’une table ARTICLE de 4 colonnes sans contraintes de valeurs ni clé primaire : USE produit; CREATE TABLE article( Ref_art char(4), Des_art varchar(20), Puht_art decimal(10,2), Code_cat char(2)) Résultat obtenu La ou les commandes ont réussi. Auteur Formation Thérèse BLANCHARD T.S.G.ER.I. Séquence Type de document 8 Support de formation Date 17/04/2017 Page 5 Support de formation 769796370.doc Exemple2 : Création d’une table ARTICLE de 4 colonnes avec contraintes de valeurs et clé primaire : CREATE TABLE article( Ref_art char(4) NOT NULL, Des_art varchar(20), Puht_art decimal(10,2), Code_cat char(2) Default '1' CONSTRAINT PK_ref_art PRIMARY KEY (Ref_art)); Exemple 3 : Création d’une table ARTICLE avec une colonne à numérotation automatique. Cette propriété peut être affectée à une colonne numérique entière et permet de faire générer, par le système des valeurs dans cette colonne en partant de 100 avec une incrémentation de 1. CREATE TABLE article( Ref_art int IDENTITY(100,1), Des_art varchar(20), Puht_art decimal(10,2), Code_cat char(2) Default '1') ; Page 6 Auteur Thérèse BLANCHARD Formation T.S.G.E.R.I. Séquence 8 Type de document Support de formation Date 17/04/2017 Support de formation 769796370.doc ALTER TABLE ALTER TABLE modifie la définition d'une table en changeant, en ajoutant ou en supprimant des colonnes ou des contraintes, ou en désactivant ou activant des contraintes Exemple1 : Ajout d’une colonne cod_fou de type entier dans la table article ALTER TABLE article ADD cod_fou int; Exemple 2 : Ajout d’une contrainte de clé étrangère entre le champ code_cat de la table article et la clé primaire code_cat de la table catégorie. ALTER TABLE article ADD CONSTRAINT Fk_art_cat FOREIGN KEY (code_cat) REFERENCES categorie(Code_cat); Exemple3 : Ajout d’une valeur par défaut pour le nom du client dans la table client ALTER TABLE client ADD CONSTRAINT DF_nom DEFAULT ‘Anonyme’ FOR nom ; DROP TABLE DROP TABLE supprime une définition de table ainsi que toutes les données, index, contraintes et spécifications d'autorisations se rapportant à celle-ci. Exemple : DROP TABLE article Auteur Formation Thérèse BLANCHARD T.S.G.ER.I. Séquence Type de document 8 Support de formation Date 17/04/2017 Page 7 Support de formation 769796370.doc Création, modification et suppression d’une vue CREATE VIEW CREATE VIEW crée une table virtuelle qui représente les données dans une ou plusieurs tables de manière alternative. Une vue n’occupe pas d’espace disque pour les données, elle ne ‘stocke’ que la requête correspondant à l’extraction. Exemple : Création d’une vue des articles de moins de 1000 euros en précisant la catégorie. CREATE VIEW vue1 as SELECT ref-art,des_art,code_cat,des_cat FROM article,catégorie WHERE article.code_cat=categorie.code_cat AND puht_art<1000; DROP VIEW DROP VIEW supprime une vue existante. Exemple : Drop VIEW vue1 supprime la vue créée précédemment. Page 8 Auteur Thérèse BLANCHARD Formation T.S.G.E.R.I. Séquence 8 Type de document Support de formation Date 17/04/2017 Support de formation 769796370.doc TRAVAUX PRATIQUES N° 7 Rédigez et testez les commandes suivantes : 1. Créez une base de donnée appelée GESTPERS en utilisant les données suivantes File Group PRIMARY File GESTPERS_Data Initial Size 5MB Max Size 20MB File Group File Initial Size Max Size GESTPERS_INDEX GESTPERS_Index 5MB 7MB Log File Initial Size Max Size GESTPERS_Log 3MB 5MB Essayez de modifier la base de données en augmentant la taille initiale de GESTPERS_Data à 10MB Créez une table Employe dans la base de données GESTPERS, ayant les propriétés suivantes : Nom de colonne Employe_ID Prenom nom Titre Date_nais Date_embau Caractéristiques Nombre entier compris entre 100 et 999. La valeur doit être générée par le système, débutant à 100 et s’incrémentant de 1. Champ obligatoire 30 caractères de type variable. Champ optionnel 30 caractères de type variable. Champ obligatoire 30 caractères de type variable. Champ optionnel Utilisation d’une colonne de type DATE. Champ optionnel Utilisation d’une colonne de type DATE. Champ obligatoire Ajoutez une contrainte de clé primaire pour faire de Employe_ID la clé primaire de la table des employés Ajouter une contrainte de type CHECK sur la colonne DATE_EMBAU de la table employé pour être sûr que cette date est supérieure à la date du jour. Auteur Formation Thérèse BLANCHARD T.S.G.ER.I. Séquence Type de document 8 Support de formation Date 17/04/2017 Page 9 Support de formation 769796370.doc Page 10 Auteur Thérèse BLANCHARD Formation T.S.G.E.R.I. Séquence 8 Type de document Support de formation Date 17/04/2017 Support de formation 769796370.doc Auteur Formation Thérèse BLANCHARD T.S.G.ER.I. Séquence Type de document 8 Support de formation Date 17/04/2017 Page 11 Support de formation 769796370.doc ANNEXE 1 – SCHEMA DE LA BASE DE DONNES SKI Page 12 Auteur Thérèse BLANCHARD Formation T.S.G.E.R.I. Séquence 8 Type de document Support de formation Date 17/04/2017 Support de formation 769796370.doc ANNEXE 2 – CONTENU DE LA BASE DE DONNES SKI Auteur Formation Thérèse BLANCHARD T.S.G.ER.I. Séquence Type de document 8 Support de formation Date 17/04/2017 Page 13 Support de formation 769796370.doc Page 14 Auteur Thérèse BLANCHARD Formation T.S.G.E.R.I. Séquence 8 Type de document Support de formation Date 17/04/2017 Support de formation 769796370.doc TRAVAUX PRATIQUES - PROPOSITION DE SOLUTIONS TRAVAUX PRATIQUES N° 1 1 - Liste des stations françaises SELECT nomstat FROM station WHERE paystat='France'; 2 - Liste des stations françaises d'altitude supérieure à 2000 mètres SELECT nomstat,altstat FROM stationWHERE paystat='France' AND altstat>2000; 3 - Référence et date des compétitions se déroulant à Tignes SELECT refcomp,datcomp FROM competition WHERE nomstat='Tignes'; 4 - Liste des competitions pour lesquelles le skieur N° 3 est arrivé premier ou deuxieme SELECT refcomp,rang FROM classement WHERE numski=3 and (rang=1 or rang=2); 5 - Classement (du premier au dernier) de la compétition N°2 SELECT numski,rang FROM classement WHERE refcomp='Comp2' ORDER BY rang; 6 - Liste des compétitions qui auront lieu entre le 01/02/07 et le 01/05/07 SELECT * FROM competition WHERE datcomp BETWEEN '01/02/07' AND '01/05/07'; 7 - Liste des skieurs dont le nom contient "om" ou "au" SELECT nomski FROM skieur WHERE nomski LIKE '%om%'OR nomski LIKE '%au%'; TRAVAUX PRATIQUES N° 2 1 - Compter le nombre de skieurs de Meribel specialisés en SuperG SELECT count(*) as nb_skieur FROM skieur WHERE specialite='SuperG' AND nomstat='Meribel'; 2 - Donner l'altitude moyenne des stations françaises SELECT avg(altstat)as altitude_moy FROM station WHERE paystat='France'; 3 - Donner le meilleur classement du skieur N°5 SELECT min(rang) as rang FROM classement WHERE numski=5; 4 - Quelles sont les compétitions programmées entre aujourd'hui et le 31/08/07 SELECT refcomp,datcomp FROM competition WHERE datcomp BETWEEN getdate() AND '31/08/07'; 5 - Quelles sont les compétitions qui auront lieu un lundi SELECT * FROM competition WHERE DATEPART(weekday,datcomp)=1; 6 - La date de la dernière compétition programmée SELECT max(datcomp) FROM competition; TRAVAUX PRATIQUES N° 3 1 - Donner le nombre de skieurs par spécialité SELECT specialite,count(*) as nb_skieur FROM skieur GROUP BY specialite; 2 - Donner la listes des spécialités pour lesquelles il y a plus de 3 skieurs Auteur Formation Thérèse BLANCHARD T.S.G.ER.I. Séquence Type de document 8 Support de formation Date 17/04/2017 Page 15 Support de formation 769796370.doc SELECT specialite as nb_skieur FROM skieur GROUP BY specialite HAVING count(*)>3; TRAVAUX PRATIQUES N° 4 1 - Référence,date,station, pays des compétitions classé par pays et par date décroisssante SELECT C.refcomp,C.datcomp,C.nomstat,ST.paystat FROM competition C, Station ST WHERE C.nomstat=ST.nomstat ORDER BY 4,2 desc; 2 -Indiquez la station d'origne et l'altitude de cette station de tous les skieurs spécialistes des 'Bosses' SELECT S.nomski,S.nomstat,ST.altstat FROM skieur S,Station ST WHERE S.nomstat=ST.nomstat AND S.specialite='Bosses'; 3 - Donnez l'altitude et la capacité des stations ou se déroulent 'Comp3' et 'Comp4' SELECT C.refcomp,ST.altstat,ST.capstat FROM Station ST,competition C WHERE C.nomstat=ST.nomstat AND (C.refcomp='comp3' OR C.refcomp='comp4'); 4 - Donnez le nombre de skieurs originaires d'une station dont l'altitude est supérieure ou égale à 2000 mètres SELECT count(*) as nb_skieurs FROM skieur S,station ST WHERE S.nomstat=ST.nomstat AND ST.altstat>=2000; 5 - Donnez la liste des skieurs ayant été classés à plus de 2 compétitions SELECT nomski FROM skieur S,classement CL WHERE S.numski=CL.numski GROUP BY nomski HAVING count(*)>2; TRAVAUX PRATIQUES N° 5 1 - Liste des skieurs qui n'ont jamais étés classés à une compétition SELECT nomski FROM skieur WHERE numski NOT IN (SELECT numski FROM classement); ou SELECT nomski FROM skieur WHERE NOT EXISTS (SELECT * FROM classement WHERE classement.numski=skieur.numski); 2 -Liste des skieurs qui ont étés classés aux mêmes compétitions que 'Tomba' SELECT nomski,numski FROM skieur WHERE numski IN (SELECT numski from classement C WHERE refcomp IN (SELECTrefcomp FROM classement WHERE numski IN (SELCT numski FROM skieur WHERE nomski='tomba')) Page 16 Auteur Thérèse BLANCHARD Formation T.S.G.E.R.I. Séquence 8 Type de document Support de formation Date 17/04/2017 Support de formation 769796370.doc AND nomski <>'Tomba'); 3 - Créez une nouvelle table qui ne contiendra que les skieurs dont la spécialité est la descente SELECT * INTO skieur_desc FROM skieur WHERE specialite='descente'; TRAVAUX PRATIQUES N° 6 1 -Insertion de la ligne 'Comp3',24/03/2007,'Tignes'dans la table compétition INSERT INTO competition values ('comp3',24/03/07,'Tignes'); Serveur : Msg 2627, Niveau 14, État 1, Ligne 1 Violation de la contrainte PRIMARY KEY 'PK_competition'. Impossible d'insérer une clé en double dans l'objet 'competition'. L'instruction a été arrêtée. Insertion de la ligne 'comp1',30,8 dans la table compétition INSERT INTO classement values ('comp1',30,8) Serveur : Msg 547, Niveau 16, État 1, Ligne 1 Conflit entre l'instruction INSERT et la contrainte COLUMN FOREIGN KEY 'FK_classement_skieur'. Le conflit est survenu dans la base de données 'SKI', table 'skieur', column 'numski'. L'instruction a été arrêtée. 2- Insertion du skieur Alphand de Serres Chevalier INSERT INTO skieur (nomski,nomstat) values('Alphand','Serres chevalier'); Serveur : Msg 515, Niveau 16, État 2, Ligne 1 Impossible d'insérer la valeur NULL dans la colonne 'numski', table 'SKI.dbo.skieur'. Cette colonne n'accepte pas les valeurs NULL. INSERT a échoué. L'instruction a été arrêtée. 3- Suppression dans la table compétition des compétitions achevées DELETE f rom competition WHERE datcomp<getdate(); 4- Ajout de 20 places à la capacité de la station Tignes UPDATE station SET capstat=capstat+20 WHERE nomstat='Tignes'; TRAVAUX PRATIQUES N° 7 1 – Création base de données Gestpers USE master; CREATE DATABASE Gestpers ON PRIMARY (NAME=gestpers_data, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\gestpers_data.mdf', SIZE=5MB,MAXSIZE=20MB), FILEGROUP Gestpers_index (NAME=gestpers_index, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\gestpers_index.ndf', SIZE=5MB,MAXSIZE=7MB) Auteur Formation Thérèse BLANCHARD T.S.G.ER.I. Séquence Type de document 8 Support de formation Date 17/04/2017 Page 17 Support de formation 769796370.doc LOG ON (NAME=gestpers_log, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\Data\gestpers_log.ldf', SIZE=3MB,MAXSIZE=5MB) 2 – modification taille gestpers_Data ALTER DATABASE GESTPERS MODIFY FILE (NAME=gestpers_data,size=10MB) 3 – création de la table employé use gestpers; CREATE TABLE employe (employe_id int identity(100,1) check(employe_id between 100 and 999),prenom varchar(30), nom varchar(30)not null,titre varchar(30),date_nais datetime,date_embau datetime not null) 4 – Ajout clé primaire dans la table employé ALTER TABLE employe add constraint PK_employe primary key(employe_id) 5 – Ajout d’une contrainte dans la table employé. ALTER TABLE employe add constraint CK_embauche check(date_embau>getdate()) Page 18 Auteur Thérèse BLANCHARD Formation T.S.G.E.R.I. Séquence 8 Type de document Support de formation Date 17/04/2017