Langage Sql

Manipulation des donn ées avec données un langage de requête SQL 0021266950863

1

LL’ALGÈBRE ’ALGÈBRE RELATIONNELLE RELATIONNELLE 1. OPERATIONS DE BASE L’algèbre relationnelle a été inventée par E. Codd comme une collection d’opérations formelles qui agissent sur des relations et produisent des relations en résultats. Cette algèbre constitue un ensemble d’opérations élé­mentaires associées au modèle relationnel, est sans doute une des forces essentielles du modèle. Les opérations de base peuvent être classées en deux types: les opérations ensemblistes traditionnelles (une relation étant un ensemble de tuples, elle peut être traitée comme tel) et les opérations spécifiques. Les opérations ensemblistes sont des opérations binaires, c’est-à-dire qu’à partir de deux relations elles en construisent une troisième. Ce sont l’union, la différence et le produit cartésien. Les opérations spécifiques sont les opérations unaires de projection et restriction qui, à partir d’une relation, en construisent une autre, et l’opération binaire de jointure. Nous allons définir toutes ces opérations plus précisément

0021266950863

2

11 LES ÉRATIONS ENSEMBLISTES LES OP OPÉRATIONS ENSEMBLISTES 1.1.UNION L’union est l’opération classique de la théorie des ensembles adaptée aux relations de même schéma. Notion : Union (Union)

Opération portant sur deux relations de même schéma RELATION1 et RELAT10N2 consistant à construire une relation de même schéma RELAT10N3 ayant pour tuples ceux appartenant à RELATION1 ou RELATION2 ou aux deux relations. Plusieurs notations ont été introduitespour cette opération selon les auteurs : RELATION1 U RELATION2 UNION (RELATION1, RELATION2) A titre d’exemple, l’union des relations VINS 1 et VINS 2 APPEND (RELATION1, RELATION2) est représentée figure 2. (relation VINS) La notation graphique représentée figure 1 est aussi utilisée.

Vins 1 CruMill CHENAS TOKAY TAVEL

RÉSULTAT

Vins 2 CruMill

U

RELATION 1

Vins

RELATION 2

Figure 1: représentation graphique de l'union

Région 1983 1980 1986

BEAUJOLAIS ALSACE RHONE

Région

TOKAY CHABLIS

1980 1985

CruMill

Région

CHENAS TOKAY TAVEL CHABLIS

1983 1980 1986 1985

0021266950863

Couleur ROUGE BLANC ROSE

Couleur ALSACE BOURGOGNE

BLANC ROUGE

Couleur BEAUJOLAIS ALSACE RHONE BOURGOGNE

ROUGE BLANC ROSE ROUGE

Figure 2– Exemple d’union

3

11 LES ÉRATIONS ENSEMBLISTES LES OP OPÉRATIONS ENSEMBLISTES 1.2.DIFFERENCE La différence est également l’opération classique de la théorie des ensembles adaptée aux relations de même schéma.

Notion : Différence (Difference) Opération portant sur deux relations de même schéma RELATION1 et REL.AT10N2 consistant à construire une relation de même schéma RELAT10N3 ayant pour tuples ceux appartenant à RELATION1 et n’appartement pas à la RELATION2 La différence est un opérateur non commutatif : l’ordre des relations opérandes est donc important. Plusieurs notations ont été introduites pour cette opération selon les auteurs RELATION1 - RELATION2 DIFFERENCE (RELATION1, RELATION2) MINUS (RELATION1, RELATION2) La notation graphique représentée figure 3 est aussi utilisée.

À titre d’exemple, la différence des relations VINS 1 - VINS 2 est représentée figure 4 (VINS)

RESULTAT Vins 1 CruMill

Région

CHENAS TOKAY TAVEL

-

Vins 2 CruMill

Vins

1983 1980 1986 Région

TOKAY CHABLIS

1980 1985

CruMill

Région

CHENAS TAVEL

1983 1986

Couleur BEAUJOLAIS ALSACE RHONE

ROUGE BLANC ROSE

Couleur ALSACE BOURGOGNE

BLANC ROUGE

Couleur

RELATION 1 RELATION 2

Figure 3: Représentation graphique de la différence 0021266950863

BEAUJOLAIS RHONE

ROUGE ROSE

Figure 4 – Exemple de différence

4

11 LES ÉRATIONS ENSEMBLISTES LES OP OPÉRATIONS ENSEMBLISTES 1.3. PRODUIT CARTÉSIEN Le produit cartésien est l’opération ensembliste que nous avons rappelée ci-dessus pour définir le concept de relation. Elle est adaptée aux relations. Cette fois, les deux relations n’ont pas nécessité d’avoir le même schéma.

Notion : Produit cartésien (Cartesian product) Opération portant sur deux relations RELATION1 et RELATION2 consistant à construire une relation RELATION3 ayant pour schéma la concaténation de ceux des relations opérandes et pour tuples toutes les combinaisons des tuples des relations opérandes. Des notations possibles pour cette opération sont: RELATION1 X RELATION2 PRODUCT (RELATION1, RELATION2) TIMES (RELATION2, RELATION2)

À titre d’exemple, la relation VINS représentée figure 6 est le produit cartésien des deux relations CRUS et ANNEES de la même figure. Vins 1

La notation graphique représentée figure 5 est aussi utilisée. RESULTAT

Région

CHENAS BEAUJOLAIS TOKAY ALSACE TAVEL RHONE

Couleur ROUGE BLANC ROSE

X Année

Mill 1980 1985

X Vins

RELATION 1

Cru

RELATION 2

Cru

Région

Couleur

Mill

CHENAS TOKAY TAVEL CHENAS TOKAY TAVEL

BEAUJOLAIS ALSACE RHONE BEAUJOLAIS ALSACE RHONE

ROUGE BLANC ROSE ROUGE BLANC ROSE

1980 1980 1980 1985 1985 1985

Figure 5 : Exemple de produit cartésien 0021266950863Figure 6 – Exemple du produit cartesien5

2. ÉRATIONS SP ÉCIFIQUES 2. LES LES OP OPÉRATIONS SPÉCIFIQUES 2.1. PROJECTION La projection est une opération spécifique aux relations qui permet de supprimer des attributs d’une relation. Son nom provient du fait qu’elle permet de passer d’une relation n-aire à une relation p-aire avec p
Notion – Projection (Projection) Opération sur une relation RELATION 1 consistant à composer une relation RELATION2 en enlevant à la relation initiale tous les attributs non mentionnés en opérandes (aussi bien au niveau du schéma que des tuples) et en éliminant les tuples en double qui sont conservés une seule fois. Les notations suivantes sont utilisées pour cette opération, en désignant par Attributi, Attributj, Attributm les attributs de projection: Attributi, Attributj, ... Attributm (RELATION1) RELATION 1 [Attributi, Attributj, ... Attributm ] PROJECT (RELATION 1, Attributi, Attributj, ... Attributm)

Le trapèze horizontal signifie que l’on réduit le nombre de colonnes de la relation : partant du nombre représenté par la base, on passe au nombre représenté par l’anti-base.

La figure 8 donne un exemple de projection d’une relation VINS comportant aussi l’attribut QUALITE sur les attributs CRU et REGION.

RESULTAT Vins 1

Cru

Mill

VOLNAY 1983 VOLNAY 1979 CHENAS 1983 JULIENAS 1986 A1,A2,..An

BOURGOGNE BOURGOGNE BEAUJOLAIS BEAUJOLAIS

Qualité A B A C

Project (RELATION 1, Cru, Région) Proj(Vins) Cru

RELATION

0021266950863

Figure 7 : Représentation graphique de la projection

Région

Région

VOLNAY BOURGOGNE CHENAS BEAUJOLAIS JULIENAS BEAUJOLAIS

Figure 8 : Exemple de projection

6

2. ÉRATIONS SP ÉCIFIQUES 2. LES LES OP OPÉRATIONS SPÉCIFIQUES 2.2. RESTRICTION La restriction est aussi une opération spécifique unaire qui produit une nouvelle relation en enlevant des tuples à la relation opérande selon un critère.

Notion : Restriction (Restriction) Opération sur une relation RELATION i produisant une relation RELATION2 de même schéma mais comportant les seuls tuples qui vérifient la condition précisée en argument. Les conditions possibles sont du type: où l’opérateur est un opérateur de comparaison choisi parmi {=, <, <, >, >, ≠}. L’attribut doit appartenir à la relation sur laquelle s’applique le critère. Les notations suivantes sont utilisées pour la restriction: condition (RELATION1) RELATION 1 [Condition] RESTRICT (RELATION1, Condition)

Le trapèze vertical signifie que l’on réduit le nombre de tuples de la relation : partant du nombre représenté par le côté gauche on passe au nombre représenté par le côté droit.

RESULTAT

La figure 10 représente la restriction d’une relation VINS enrichie d’un attribut QUALITE par la condition mill>1983 Vins

Cru

Mill

VOLNAY 1983 VOLNAY 1979 CHENAS 1983 JULIENAS 1986

Ai O Valeur

Région BOURGOGNE BOURGOGNE BEAUJOLAIS BEAUJOLAIS

Qualité A B A C

RESTRICT (RELATION1, cru > 1983) RELATION

Vins

Cru

Mill

JULIENAS 1986

Figure 9: Représentation graphique de la0021266950863 restriction

Région BEAUJOLAIS

Qualité C

Figure 10: Exemple de restriction 7

2.3. JOINTURE

2. ÉRATIONS SP ÉCIFIQUES 2. LES LES OP OPÉRATIONS SPÉCIFIQUES

La jointure est une des opérations essentielles de l’algèbre relationnelle, sans doute la plus difficile à réaliser dans les systèmes. La jointure permet de composer deux relations à l’aide d’un critère de jointure. Elle peut être vue comme une extension du produit cartésien avec une condition permettant de comparer des attributs. Nous la définirons comme suit:

Notion : Jointure (Join) Opération consistant à rapprocher selon une condition, les tuples de deux relations RELATION1 et RELATION2 afin de former une troisième relation RELATION3 qui contient l’ensemble de tous les tuples obtenus en concaténant un tuple de RELATION1 et un tuple de RELATION2 vérifiant la condition de rapprochement.

La jointure de deux relations produit donc une troisième relation qui contient toutes les combinaisons de tuples des deux relations initiales qui satisfont la condition spécifiée. La condition doit bien sûr permettre le rapprochement des deux relations, et donc être du type: où Attribut1 appartient à RELATION1 et Attribut2 à RELATION2. Vins

Cru

Mill

Qualité

VOLNAY 1983 VOLNAY 1979 CHABLIS 1983 JULIENAS 1986

A B A C

Jointure Localisation

Cru

Région VOLNAY CHABLIS CHABLIS

VinReg

Cru VOLNAY VOLNAY CHABLIS CHABLIS

Mill

QualMoy BOURGOGNE BOURGOGNE CALIFORNIE Région 1983 1979 1983 1983

A A B Qualité Moyenne BOURGOGNE BOURGOGNE BOURGOGNE CALIFORNIE

A B A A

0021266950863 Figure 12: Jointure naturelle des relation VINS et LOCALISATION

8

2. ÉRATIONS SP ÉCIFIQUES 2. LES LES OP OPÉRATIONS SPÉCIFIQUES 2.4. INTERSECTION L’intersection est l’opération classique de la théorie des ensembles adaptée aux rela­tions de même schéma.

Notion : Intersection (Intersection) Opération sur deux relations de même schéma RELATIONet RELATION2 consistant à construire une relation de même schéma RELATION3 ayant pour tuples ceux appartenant à la fois à RELATION1 et RELATION2

Plusieurs notations ont été introduites pour cette opération selon les auteurs • RELATION1 ∩ RELATION2 • INTERSECT (RELATION1, RELATION2) • AND (RELATION1, RELATION2) La notation graphique représentée figure 14 est aussi utilisée.

0021266950863

9

5.2 DIVISION

2. ÉRATIONS SP ÉCIFIQUES 2. LES LES OP OPÉRATIONS SPÉCIFIQUES

La division permet de rechercher dans une relation les sous-tuples qui sont complétés par tous ceux d’une autre relation. Elle permet ainsi d’élaborer la réponse à des ques­tions de la forme “quel que soit x, trouver y” de manière simple.

Notion: Division (Division)Opération consistant à construire le quotient de la relation D (A 1, A2... Ap, Ap+ 1, ... An) par la relation d(Ap+ 1, ... An) comme la relation Q(A 1, A2... Ap) dont les tuples sont ceux qui concaténés à tout tuple de d donnent un tuple de D.

Les notations possibles pour la division sont : D ÷ d DIVISION (D,d)

RESULTAT

÷

RELATION 1

RELATION 2 0021266950863

10

2. ÉRATIONS SP ÉCIFIQUES 2. LES LES OP OPÉRATIONS SPÉCIFIQUES 5.3. COMPLEMENT Le complément permet de trouver les tuples qui n’appartiennent pas à une relation. Il suppose à priori que les domaines sont finis (sinon on obtient des relations infinies).

Notion : Complément Ensemble des tuples du produit cartésien des domaines des attributs d’une relation n’apparte­nant pas à cette relation.

Le complément d’une relation RELATION i est noté au choix: • NOT (RELATIONi) • COMP (RELATIONi)

0021266950863

11

2. ÉRATIONS SP ÉCIFIQUES 2. LES LES OP OPÉRATIONS SPÉCIFIQUES 5.4. ÉCLATEMENT

L’éclatement [Fagin8O] est une opération qui n’appartient pas vraiment à l’algèbre relationnelle puisqu’elle donne deux relations en résultats, à partir d’une. Elle est cependant utile pour partitionner une relation horizontalement en deux sous-relations. À ce titre, elle est considérée comme une extension de l’algèbre.

Notion : Eclatement Opération consistant à créer deux relations à partir d'une relation RELATION1 et d'une condition, la première contenant les tuples de RELATION1 vérifiant lacondition et la deuxième ceux ne la vérifiant pas

Cet opérateur appliqué à la relation RELATIONi génère donc deux relations R1 et R2 qui seraient obtenues par restriction comme suit: • R1 = RESTRICT (RELATIONi, Condition) • R2 = RESTRICT (RELATIONi, Non Condition)

0021266950863

12

6.1. LANGAGE ALGEBRIQUE En utilisant des expressions d’opérations de l’algèbre relationnelle, il est possible d’élaborer les réponses à la plupart des questions que l’on peut poser à une base de données relationnelle. Plus précisément, les opérations de base de l’algèbre relationnelle constitue un langage complet, c’est-à-dire ayant la puissance de la logique du premier ordre. Afin d’illustrer, nous exprimons quelques questions sur la base DEGUSTATION . Ces questions peuvent être exprimées comme des expressions d’opérations, ou comme des opérations successives appliquées sur des relations intermédiaires ou de base, générant des relations intermédiaires. Pour des raisons de clarté, nous avons choisi cette deuxième représentation. L’expression peut être obtenue simplement en supprimant les relations intermédiaires notées Ri.

(Q1) Donner les degrés des vins de crus MORGON et de MILLESIME 1978: R1 = RESTRICT (VINS, CRUS = “MORGON”) R2 = RESTRICT (VINS, MILLESIME = 1978) R3 = INTERSECT (R1, R2)RESULTAT = PROJECT (R3, DEGRE) (Q2) Donner les noms et prénoms des buveurs de MORGON ou CHENAS: Ri = RESTRICT (VINS, CRU = “MORGON”) R2 = (VINS, CRUS = “CHENAS”) R3 = UNION (R1, R2) R4 = JOIN (R3, ABUS) R5 = JOIN (R4, BUVEURS) RESULTAT = PROJECT (R5, NOM, PRENOM) (Q3) Donner les noms et adresses des buveurs ayant bu plus de 10 bouteilles de Chablis 1976 avec le degré de ce vin: R1 = RESTRICT (ABUS, QUANTITE>10) R2 = RESTRICT (VINS, CRU = “CHABLIS”) R3 = RESTRICT (VINS, MILLESIME = 1976) R4 = INTERSECT (R2, R3) R5 = JOIN (R1, R4) R6 = PROJECT (R5, NB, DEGRE) R7 = JOIN (R6, BUVEURS) RESULTAT = PROJECT (R7, NOM, ADRESSE, DEGRE) (Q4) Donner les noms des buveurs n’ayant bu que du Morgon: R1 = JOIN(BUVEURS,ABUS,VINS) R2 = RESTRICT(R1, CRU = “MORGON”) R3 = PROJECT(R2, NOM) R4 = RESTRICT(R1, CRU ≠ “MORGON”) R5 = PROJECT(R1, NOM) RESULTAT = MINUS(R3 - R5) 0021266950863

13

Introduction Introduction 1. De quoi s'agit-il Les bases d'un langage structuré d'interrogation de bases de données sont apparues à l'occasion de la publication d'un article de Mr CHAMBERLIN et Mr BOYCE en 1974. Ce premier langage s'appelait SEQUEL. SQUARE qui utilisait les expressions mathématiques vît le jour en 1975. SQL(Structured Query Language) apparaît en 1980 et continue à couler des jours heureux sur gros systèmes et micro-ordinateurs. De nombreux fournisseurs de logiciels proposent l'utilisation de ce langage. Parmi les principaux S.G.B.D.R. possédant l'interface S.Q.L.; nous pouvons citer : - ACCESS (sous Dos et Windows) - SQL Server sous Windows) - ORACLE (sous MS/DOS; MVS, ...) - DATABASE MANAGER (sous OS/2) - DB2/400 (sur AS/400) - SQL/DS (sous DOS/VSE, VM/CMS) - DB2 (sous MVS) - ... Le SQL standard a été défini par ANSI (American National Standard Institute) C'est un langage d'interrogation de bases de données relationnelles

0021266950863

14

11 Concepts ées relationnelles Concepts base base de de donn données relationnelles 1- Domaine, attribut & relation Tout au long de ce chapitre, nous imagerons notre discours par un cas dont le thème sera le cinéma. Nous allons créer et faire vivre une « base de données » que nous nommerons « CINEMA » et qui va contenir un certain nombre d’informations sur les acteurs, leurs nationalité, les films dans lesquels ils ont joué, et les personnes « non acteurs » qui ont participé à l’élaboration des films (type d'intervention : metteur en scène, habilleuse, script ; ....)

Certaines données qualifient l’acteur : son nom , son prénom, sa date de naissance (chut, faut pas le dire) et sa nationalité. D’autres qualifient ou caractérisent le film : le titre, la date de sortie, le nombre de spectateurs l’ayant déjà vu

Par contre, il faut indiquer la participation de l’acteur à un film : ceci caractérise un lien entre un acteur et un f

0021266950863

15

Nous pouvons représenter ces informations avec la structure suivante Les flèches mettent en évidence des informations que l’on retrouve d’une table à l’autre :

0021266950863

16

Quelles sont les flèches qui manquent ? Exemple de requête que nous pourrons faire avec SQL : retrouver tous les films ayant eu des acteurs de nationalité «française». Tout d’abord, on recherche le code de la nationalité dont le libellé est «française» soit "033". Puis, on recherche dans la table acteurs, les acteurs ayant pour code nationalité «033» Ensuite par la consultation de ROLES, on recherche tous les films de cet acteur On répète ceci pour chaque acteur sélectionné

La base de données relationnelle CINEMAest constituée de tables: Acteurs Rôles Nationalités Films Intervenants Taches Cette base de données est «relationnelle» car des éléments permettent de passer d’une table à l’autre: (on appelle souvent cela la navigation dans la base de données). Dans la table ACTEURS, nous avons des colonnes: nom prénom date de naissance code nationalité. Dans cette même table, on trouvera une ligne (enregistrement) par acteur: Une table est donc un ensemble de lignes et de colonnes.

0021266950863

17

Il est donc possible de représenter une table sous forme de tableau avec un contenu :

ACTEURS

Nom

Prénom

Date Nais.

Cod Nat.é

Classement

Béart

Emmanuelle

15/01/1960

033

250

Blanc

Michel

20/04/1952

033

560

Douglas

Kirk

12/01/1928

100

50

Douglas

Michael

16/05/1955

100

3

Durand

Al

01/04/1915

033

Maintenant, nous allons introduire une notion nouvelle: le domaine C'est l'ensemble des valeurs caractérisant un nom de donnée. Dans notre exemple, si nous regardons le code nationalité, il prend des valeurs entières (symbolisées par n) Comprises entre 001 et 999, 033 étant la valeur pour la France. 0021266950863

18

2- La notion de contraintes

Cohérence : toute valeur prise par une colonne doit appartenir à son domaine de définition. Atomicité : une colonne ne doit pas pouvoir se décomposer. Unicité : chaque ligne d'une table doit être unique afin de pouvoir l'isoler. Clef primaire ou identifiant pour pouvoir identifier une ligne parmi toutes les lignes de la tables.

0021266950863

19

Quelques opérations relationnelles 1- Restriction ou Sélection :

0021266950863

20

2- Projection

0021266950863

21

3- Jointure

0021266950863

22

4- Produit cartésien

0021266950863

23

5- Union

0021266950863

24

6- Différence

0021266950863

25

Un peu de mise en pratique: À partir de la base "Cinéma" décrite dans le chapitre précédant, indiquer quelles sont les opérations à effectuer sur les tables pour répondre aux questions suivantes : Quels sont les acteurs de nationalité française.? Quels acteurs ont été aussi metteurs en scène ? Quels sont les films ayant eu un metteur en scène de nationalité "hongroise" ?

0021266950863

26

3- Le langage S.Q.L & ses composantes Pour le dictionnaire, le langage est la manière de s'exprimer au moyen de symboles. Parmi les symboles de SQL, nous trouvons des verbes qui peuvent se classer en quatre chapitres : DDL Data Définition Language permet de définir une base de données et ses éléments. DML Data Management Language permet de gérer, manipuler les éléments d'une base de données. DCL Data Control language permet d'effectuer des contrôles sur les données. QUERY permet d'interroger une base de données en les sélectionnant.

Autre particularité de ce langage : ses mots clés.

0021266950863

27

A

Alter view Alter Database Alter procedure Alter Table Alter trigger

B

Begin Break

C

Case Close Continue Create Database Create Index Create Procedure Create Table Create trigger Create view

D

Deallocate Declare cursor Declare variable Delete Drop Database Drop procedure Drop Table Drop trigger Drop view

E Else End F Fetch G Goto I If Insert into O Open R Return S Set variable U Update W While

0021266950863

28

3- Découverte de SqlServer

0021266950863

29

0021266950863

30

2- Définition d une base de données relationnelle 2-1 Création d'une base de données

0021266950863

31

0021266950863

32

0021266950863

33

0021266950863

34

0021266950863

35

0021266950863

36

0021266950863

37

0021266950863

38

0021266950863

39

0021266950863

40

0021266950863

41

0021266950863

42

0021266950863

43

0021266950863

44

0021266950863

45

0021266950863

46

0021266950863

47

Alimentation d'une base de données relationnelle

0021266950863

48

0021266950863

49

0021266950863

50

Modification des valeurs dans les colonnes UPDATE

0021266950863

51

0021266950863

52

Suppression dans une table DELETE

0021266950863

53

Ajoutez la colonne trésor à la table monarque puis créez un script permettant de charger cette table avec les données ci-dessous

0021266950863

54

Extraction d'informations : SELECT

0021266950863

55

0021266950863

56

0021266950863

57

0021266950863

58

0021266950863

59

0021266950863

60

0021266950863

61

0021266950863

62

0021266950863

63

0021266950863

64

6 Les fonctions scalaires

0021266950863

65

0021266950863

66

0021266950863

67

0021266950863

68

0021266950863

69

0021266950863

70

0021266950863

71

0021266950863

72

Fonctions sur chaînes de caractères

0021266950863

73

SELECT DISTINCT

0021266950863

74

Fonctions scalaires

0021266950863

75

0021266950863

76

Fonctions conversion

0021266950863

77

Expressions calculées

0021266950863

78

Expressions calculées

0021266950863

79

Expressions calculées : concaténation

0021266950863

80

Les fonctions récapitulatives sur les colonnes

0021266950863

81

La clause GROUP BY

0021266950863

82

La clause HAVING

0021266950863

83

0021266950863

84

Activité n° 3 : à la recherche du temps des rois Réfléchissez sur papier avant de vous lancer sur écran Passez les ordres SQL vous permettant de répondre aux demandes ci-dessous. Conservez votre script dans un fichier se trouvant dans votre dossier de travail. à sauvegarder votre base.

Pensez

1 Sélection de tous les rois capétiens 2 Sélection de tous les rois carolingiens affichés par montant de trésor 3 Sélection des épouses par ordre décroissant de date de mariage 4 Sélection de tous les rois ayant eu plus d'une épouse 5 Combien y-a-t-il en moyenne d'enfants par dynastie ? (vérifiez bien le résultat obtenu) 6 Quel est le montant total du trésor des rois ayant eu plus d'une épouse ? 7 Augmentez le trésor des carolingiens de 70 % 8 Remettez le trésor des rois capétiens à zéro.

0021266950863

85

En résumé

SELECT DISTINCT liste des attributs résultats FROM liste des tables WHERE conditions de prise en compte GROUP BY critères de regroupement HAVING conditions sur regroupements ORDER BY critères de tri des résultats UNION SELECT ....... 0021266950863

86

0021266950863

87

Remplissage de colonnes par SELECT

0021266950863

88

Création de vue CREATE VIEW

0021266950863

89

Modification de vue ALTER VIEW

0021266950863

90

Création d'index CREATE INDEX

0021266950863

91

8- Entreprise Manager Jusqu'à maintenant nous avons fait utiliser l'analyseur de requêtes car lorsque vous programmez il vous est nécessaire d'écrire les requêtes. Il existe un outil vous permettant d'utiliser de nombreux assistants : c'est Enterprise Manager. Pour y accéder, cliquez sur "Enterprise manager" : vous accédez à une fenêtre comportant dans sa partie gauche une liste à développer (cliquez sur +). Un écran pour authentification peut apparaître : indiquez le mot de passe de la connexion.

0021266950863

92

Vous obtiendrez un niveau supplémentaire dans l'arborescence.

En cliquant sur "Bases de données" vous développerez un niveau supplémentaire faisant apparaître toutes les bases de données actuellement répertoriées.

0021266950863

93

Lorsque vous êtes sur la liste des bases de données, en cliquant sur l'icône "Nouvelle", vous accédez à un assistant qui va réaliser une "create database".

0021266950863

94

Lorsque vous êtes sur une base de données précise, , en cliquant sur l'icône "Nouvelle", vous accédez à un assistant qui va réaliser une "create table".

0021266950863

95

Un tableau apparaît où il est possible de saisir les noms de colonnes, leur type, le fait qu'elles soient cle primaire, …

Vous pouvez passer par les options du menu action pour réaliser la même opération.

0021266950863

96

Par un clic doit sur un nom de table, vous obtiendrez un menu permettant en autre d'entrer et de modifier le contenu des colonnes vous évitant ainsi les Insert into, Update et Delete.

0021266950863

97

1 Création de type de données

9 Programmation en SQL

Lorsque l'on crée une base de données, on utilise les types de données standards, mais il est fréquent que certaines données est la même structure (par exemple, toutes les données montant sont numériques de longueur 11 dont 2 décimales et peuvent ne pas être définies). Dans ce cas, il est pratique d'ajouter aux types de données standard des types de données "utilisateur" spécifiques à notre base. Ceci est possible par la procédure sp-addtype.

Exemple de commande Exec sp_addtype Montant , ' numeric (11 2)' ,

'NULL'

Create table STATIST (CodNationalite char (3) NOT NULL, TotalN1 Montant, TotalN2 Montant)

0021266950863

98

2 Création de TRIGGER Un Trigger est une procédure stockée dans les objets de la base et qui se déclenche automatiquement dès qu'une action de mise à jour est lancée sur la table (Insert, Update, Delete). Ils sont très utiles pour renforcer la sécurité et l'intégrité des données. On peut avoir plusieurs triggers par table.

CREATE TRIGGER <nom-déclencheur> ON <nomtable> FOR (INSERT, UPDATE, DELETE) AS

conditions de déclenchement instructions SQL

0021266950863

99

Présentation de la base de données du Cas Pers :

Schema Logique de la base en modele relationnel : UNITE ( Code Unité, Nom Unité, Unité mère, Budget ) QUALIFICATION ( Code Qualification, Intitulé ) EMPLOYE ( N°Matricule, Nom, Code Unité, Niveau, Titre, Sexe, Date de Naissance, Qualification Principale, Salaire ) QUALIFICATION SECONDAIRE ( N°Matricule, Code Qualification ) POSTE BUDGETAIRE ( Code Qualification, Code Unité, Quantité, Salaire autorisé )

0021266950863

100

ƒ Modèle physique de la base de données : EMPLOYE EMPCOD char(5) UNITCOD char(4) NOM char(20) JOBCODE char(4) NIVEAU char(4) TITRE char(30) SEXE char(1) DATNAISS datetime QUALIFPR char(4) SALAIRE numeric

UNITE UNITCOD char(4) UNITE char(20) UMERE char(4) BUDGET money

UNITCOD = UNITCOD

UNITCOD = UNITCOD

POSTES CODQUAL char(4) UNITCOD char(4) NOMBRE numeric(2,2) BUDGETPOSTE numeric

EMPCOD = EMPCOD CODQUAL = CODQUAL

EMPSQUAL CODQUAL char(4) EMPCOD char(5)

CODQUAL = CODQUAL

QUALIF CODQUAL char(4) QUALIBEL char(20)

0021266950863

101

ƒ Script SQL de la création des Tables de la Base de données du Cas Pers

/* ============================================================ /* Table : UNITE */ /* ============================================================ create table UNITE ( UNITCOD char(4) not null, UNITE char(20) null , UMERE char(4) null , BUDGET money null , constraint PK_UNITE primary key (UNITCOD) ) go /* ============================================================ /* Table : QUALIF */ /* ============================================================ create table QUALIF ( CODQUAL char(4) not null, QUALIBEL char(20) null , constraint PK_QUALIF primary key (CODQUAL) ) go 0021266950863

102

*/ */

*/ */

/* ============================================================ /* Table : EMPLOYE */ /* ============================================================ create table EMPLOYE ( EMPCOD char(5) not null, UNITCOD char(4) not null, NOM char(20) null , JOBCODE char(4) null , NIVEAU char(4) null , TITRE char(30) null , SEXE char(1) null , DATNAISS datetime null , QUALIFPR char(4) null , SALAIRE numeric null , constraint PK_EMPLOYE primary key (EMPCOD) ) go /* ============================================================ /* Table : EMPSQUAL */ /* ============================================================ create table EMPSQUAL ( CODQUAL char(4) not null, EMPCOD char(5) not null, constraint PK_EMPSQUAL primary key (CODQUAL, EMPCOD) ) 0021266950863 go

*/ */

*/ */

103

/* ============================================================ */ /* Table : POSTES */ /* ============================================================ */ create table POSTES ( CODQUAL char(4) not null, UNITCOD char(4) not null, NOMBRE numeric(2,2) null , BUDGETPOSTE numeric null , constraint PK_POSTES primary key (CODQUAL, UNITCOD) ) go

0021266950863

104

Cas pers : Exercices Exercice 1

Liste des responsables des unités 2100 et 2300. unitcod nom ------- -------------------2100 CARR, P.I. 2300 SONNENFELDT, W.R. (2 ligne(s) affectée(s)) Exercice 2

Noms et dates de naissance des employés qui ne sont pas responsables d'unité et qui sont nés avant 1940. nom datnaiss -------------------- --------------------------ARTHUR, P.J. 1930-01-09 00:00:00.000 BUSTER, A.B. 1938-07-19 00:00:00.000 COMPTON, D.R. 1929-03-28 00:00:00.000 CURTIS, K.R. 1936-03-04 00:00:00.000 DIETERICH, L.J. 1925-04-23 00:00:00.000 FLETCHER, M.W. 1932-07-29 00:00:00.000 FRANCIS, G.C. 1932-11-11 00:00:00.000 GRAY, S.R. 1923-07-03 00:00:00.000 HENDERSON 1934-01-21 00:00:00.000 HUSHES, J.W. 1935-01-20 00:00:00.000 KENT, M.J. 1933-05-17 00:00:00.000 LEGGETT, P.F. 1926-01-25 00:00:00.000 LITTLE, E.F. 1938-03-02 00:00:00.000 MELCHERT, F.F. 1935-08-13 00:00:00.000 NORMAN, H.R. 1933-11-01 00:00:00.000 RAYMOND, H.F. 1934-12-16 00:00:00.000 SCARBOROUGH, J.B. 1914-09-14 00:00:00.000 SILCOTT, D.N. 1920-10-10 00:00:00.000 (18 ligne(s) affectée(s))

0021266950863

105

Exercice 3

Noms et titres des employés ayant leur nom commençant par un ‘a’.

nom titre ------------------------------------------------ANDERSON, R.E. Chef Service Méthodes Systèmes ARNETTE, L.J. Ingénieur Electronicien ARTHUR, P.J. Ingénieur (3 ligne(s) affectée(s)) Exercice 4

Noms et date de naissance des employés ayant au moins deux A dans leur nom. nom titre -------------------- -----------------------------CALLAGAN, R.F. Secrétaire FINERMAN, A.B. Dessinateur MACCARTHY, J.K. Chef Service Qualité B MASSARD, L.R. Ingénieur Electronicien STADERMAN, P.K. Ingénieur Electronicien (5 ligne(s) affectée(s)) Exercice 5

Noms et unités des employés ayant un salaire supérieur à 250000 nom unitcod -------------------- ------CARR, P.I. 2100 CHANDLER, W.R. 2130 DIETERICH, L.J. 2190 FRIEDMAN, J.M. 2112 GODDARD, D.H. 2120 GUTTMAN, G.J. 2110 MAILLETT, J.R. 2310 PETERSON, N.M. 2000 SONNENFELDT, W.R. 2300 WALTERS, R.J. 2111 (10 ligne(s) affectée(s))

0021266950863

106

Exercice 6

Nom, unité, titre et date de naissance de tous les employés triés par numéro d'unité croissant (les plus jeunes d'abord)

nom unitcod titre datnaiss -------------------- ------- ------------------------------ ------------LYNN, K.R. 2000 Secrétaire 1953-01-21 PETERSON, N.M. 2000 Responsable de Division 1928-06-07 CALLAGAN, R.F. 2100 Secrétaire 1955-05-17 CARR, P.I. 2100 Responsable Dept. Etudes 1935-07-02 HARRIS, D.L. 2110 Secrétaire 1955-05-17 GUTTMAN, G.J. 2110 Responsable Groupe Système 1930-11-10 GARBER, R.E. 2111 Ingénieur Electronicien 1944-07-07 HENDERSON 2111 Ingénieur Electronicien 1934-01-21 COMPTON, D.R. 2111 Responsable des Couts 1929-03-28 WALTERS, R.J. 2111 Chef du Service Developpement 1926-02-02 SCARBOROUGH, J.B. 2111 Ingénieur Mécanicien 1914-09-14 TOTO 2112 Ingénieur Electronicien 1950-07-15 BLANK, L.F. 2112 Dessinateur 1949-10-10 FAULKNER, W.M. 2112 Ingénieur Mécanicien 1940-06-21 FRIEDMAN, J.M. 2112 Chef du Service Etudes 1936-03-17 FRANCIS, G.C. 2112 Ingénieur Systèmes 1932-11-11 . . LEVITT, P.S. 2311 Régleur 1944-04-22 LITTLE, E.F. 2311 Régleur 1938-03-02 PAYNE, W.F. 2311 Chef Serv. Fabrication Export 1933-08-21 (56 ligne(s) affectée(s)) 0021266950863 107

Exercice 7

Nom et unité des employés ayant une qualification principale de secrétaire. nom unitcod -------------------- ------BERGMAN, R.I. 2130 CALLAGAN, R.F. 2100 FRESCOTT, W.C. 2300 GERRISH, C.S. 2120 HARRIS, D.L. 2110 LYNN, K.R. 2000 MELCHERT, F.F. 2310 (7 ligne(s) affectée(s)) Exercice 8

Nom, unité et date de naissance des employés plus agés que leur responsable, avec nom, unité et date de naissance de celui-ci. ( Classé par unité ) nom datnaiss unitcod nom datnaiss unitcod -------------------- ---------- ------- -------------------- ---------- -------SCARBOROUGH, J.B. 1914-09-14 2111 WALTERS, R.J. 1926-02-02 2111 FRANCIS, G.C. 1932-11-11 2112 FRIEDMAN, J.M. 1936-03-17 2112 ARTHUR, P.J. 1930-01-09 2115 BRIGGS, G.R. 1940-05-08 2115 HUSHES, J.W. 1935-01-20 2123 SMITH, R.E. 1940-11-29 2123 RAYMOND, H.F. 1934-12-16 2123 SMITH, R.E. 1940-11-29 2123 MASSARD, L.R. 1943-02-23 2132 MACCARTHY, J.K. 1943-10-20 2132 SILCOTT, D.N. 1920-10-10 2132 MACCARTHY, J.K. 1943-10-20 2132 FLETCHER, M.W. 1932-07-29 2133 SCHRADER, F.G. 1942-09-09 2133 KENT, M.J. 1933-05-17 2133 SCHRADER, F.G. 1942-09-09 2133 LEGGETT, P.F. 1926-01-25 2133 SCHRADER, F.G. 1942-09-09 2133 (10 ligne(s) affectée(s))

0021266950863

108

Exercice 9

Nom et titre des employés plus agés que le responsable de l'unité 2000. nom titre -------------------- -----------------------------SILCOTT, D.N. Ingénieur Electronicien SCARBOROUGH, J.B. Ingénieur Mécanicien WALTERS, R.J. Chef du Service Developpement HAMMER, C.P. Chef Serv. Qualité Production LEGGETT, P.F. Ingénieur Production GRAY, S.R. Outilleur SONNENFELDT, W.R. Responsable Département Export DIETERICH, L.J. Resp. Groupe Recherche Ext. (8 ligne(s) affectée(s)) Exercice 10

Codes unités n'ayant pas d'unité rattachée. unitcod ------2111 2112 2115 2122 2123 2131 2132 2133 2135 2190 2311 2315 2316 (13 ligne(s) affectée(s))

0021266950863

109

Exercice 11

Nom des employés appartenant à la même unité que 'LEVITT, P.S.' nom -------------------LITTLE, E.F. PAYNE, W.F. (2 ligne(s) affectée(s)) Exercice 12

Code et nom des unités dépendant de la même unité que l'unité 2130.

unitcod unite ------- -------------------2110 Groupe Systèmes 2120 Groupe Méthodes 2190 Groupe Recherche Ext (3 ligne(s) affectée(s)) Exercice 13

Nom et date de naissance des employés plus agés que tous les employés de l'unité 2131. nom datnaiss -------------------- --------------------------DIETERICH, L.J. 1925-04-23 00:00:00.000 GRAY, S.R. 1923-07-03 00:00:00.000 HAMMER, C.P. 1920-06-13 00:00:00.000 LEGGETT, P.F. 1926-01-25 00:00:00.000 PETERSON, N.M. 1928-06-07 00:00:00.000 SCARBOROUGH, J.B. 1914-09-14 00:00:00.000 SILCOTT, D.N. 1920-10-10 00:00:00.000 SONNENFELDT, W.R. 1923-05-01 00:00:00.000 WALTERS, R.J. 1926-02-02 00:00:00.000 0021266950863 (9 ligne(s) affectée(s))

110

Exercice 14

Nom des employés sur le même poste budgétaire que 'LEVITT'.

nom -------------------LITTLE, E.F. PAYNE, W.F. (2 ligne(s) affectée(s)) Exercice 15

Code emploi et nom des employés de l'unité 2122 ayant même emploi que quelqu'un de l'unité 2132.

jobcode nom ------- -------------------1130 STADERMAN, P.K. (1 ligne(s) affectée(s)) Exercice 16

Code qualification et nom des employés de l'unité 2111 ayant même qualification principale ou secondaire que quelqu'un de l'unité 2123. nom qualifpr -------------------- -------GARBER, R.E. 1330 COMPTON, D.R. 1350 WALTERS, R.J. 1110 SCARBOROUGH, J.B. 1120 HENDERSON 1130 (5 ligne(s) affectée(s))

0021266950863

111

Exercice 17

Même demande, mais avec le libellé de la qualification en clair. nom qualifpr qualibel -------------------- -------- -------------------WALTERS, R.J. 1110 Ingénieur Systèmes SCARBOROUGH, J.B. 1120 Ingénieur Mécanicien HENDERSON 1130 Ingénieur Electronic GARBER, R.E. 1330 Dessinateur COMPTON, D.R. 1350 Respons Coût (5 ligne(s) affectée(s)) Exercice 18

Nom et âge des employés de l'unité 2111 par ordre alphabétique. nom age -------------------- ----------COMPTON, D.R. 71 GARBER, R.E. 56 HENDERSON 66 SCARBOROUGH, J.B. 86 WALTERS, R.J. 74 (5 ligne(s) affectée(s))

Exercice 19 Nom et âge des employés de l'unité 2111 par ordre alphabétique avec « ans » nom age -------------------- ----------- ---COMPTON, D.R. 71 ans GARBER, R.E. 56 ans HENDERSON 66 ans SCARBOROUGH, J.B. 86 ans WALTERS, R.J. 74 ans (5 ligne(s) affectée(s))

0021266950863

112

Exercice 20

Salaire moyen de l'entreprise

salaire_moyen ------------216321 (1 ligne(s) affectée(s)) Exercice 21

Code unité, nom et nombre d'employés de chaque unité. unitcod unite nbr_employe ------- -----------------------------2000 Division Commutation 2100 Département Etudes 2110 Groupe Systèmes 2 2111 Service Développemt 2112 Service Etudes 5 2115 Service Prototypes 3 2120 Groupe Méthodes 2 2122 Méthodes Systèmes 2123 Méthodes Protos 5 2130 Groupe Qualité 2 2131 Service Qualité A 4 2132 Service Qualité B 4 2133 Qualité Protos 5 2135 Qualité Production 4 2190 Groupe Recherche Ext 2300 Département Export 2310 Groupe Prod. Export 2311 Fabrication Export 3 (18 ligne(s) affectée(s)) Exercice 22

2 2 5

3

1 2 2

Code, libellé et salaire moyen de l'emploi ayant le salaire moyen le plus bas jobcode qualibel salaire_moyen ------- -------------------- ------------5210 Secrétaire 97428 (1 ligne(s) affectée(s))

0021266950863

113

Cas pers : Résultats des exercices Exercice 1

Liste des responsables des unités 2100 et 2300.

SELECT DISTINCT employe.unitcod,employe.nom from employe where ((employe.unitcod="2100") or (employe.unitcod="2300")) and employe.niveau="CHEF"; unitcod nom ------- -------------------2100 CARR, P.I. 2300 SONNENFELDT, W.R. (2 ligne(s) affectée(s))

Exercice 2

Noms et dates de naissance des employés qui ne sont pas responsables d'unité et qui sont nés avant 1940.

SELECT DISTINCT employe.nom,employe.datnaiss from employe where (employe.datnaiss<'1940') and (employe.niveau <> "chef"); nom datnaiss -------------------- --------------------------ARTHUR, P.J. 1930-01-09 00:00:00.000 BUSTER, A.B. 1938-07-19 00:00:00.000 COMPTON, D.R. 1929-03-28 00:00:00.000 CURTIS, K.R. 1936-03-04 00:00:00.000 DIETERICH, L.J. 1925-04-23 00:00:00.000 FLETCHER, M.W. 1932-07-29 00:00:00.000 FRANCIS, G.C. 1932-11-11 00:00:00.000 GRAY, S.R. 1923-07-03 00:00:00.000 HENDERSON 1934-01-21 00:00:00.000 HUSHES, J.W. 1935-01-20 00:00:00.000 KENT, M.J. 1933-05-17 00:00:00.000 LEGGETT, P.F. 1926-01-25 00:00:00.000 LITTLE, E.F. 1938-03-02 00:00:00.000 MELCHERT, F.F. 1935-08-13 00:00:00.000 NORMAN, H.R. 1933-11-01 00:00:00.000 RAYMOND, H.F. 1934-12-16 00:00:00.000 SCARBOROUGH, J.B. 1914-09-14 00:00:00.000 SILCOTT, D.N. 1920-10-10 00:00:00.000 (18 ligne(s) affectée(s))

0021266950863

114

Exercice 3

Noms et titres des employés ayant leur nom commençant par un ‘a’. SELECT DISTINCT employe.nom,employe.titre from employe where ((employe.nom like"a%")); nom titre ------------------------------------------------ANDERSON, R.E. Chef Service Méthodes Systèmes ARNETTE, L.J. Ingénieur Electronicien ARTHUR, P.J. Ingénieur (3 ligne(s) affectée(s)) Exercice 4

Noms et date de naissance des employés ayant au moins deux A dans leur nom. SELECT DISTINCT employe.nom,employe.datnaiss from employe where ((employe.nom like "%a%a%")); nom titre -------------------- -----------------------------CALLAGAN, R.F. Secrétaire FINERMAN, A.B. Dessinateur MACCARTHY, J.K. Chef Service Qualité B MASSARD, L.R. Ingénieur Electronicien STADERMAN, P.K. Ingénieur Electronicien (5 ligne(s) affectée(s))

0021266950863

115

Exercice 5

Noms et unités des employés ayant un salaire supérieur à 250000 SELECT DISTINCT employe.nom,employe.unitcod from employe where ((employe.salaire>250000)); nom unitcod -------------------- ------CARR, P.I. 2100 CHANDLER, W.R. 2130 DIETERICH, L.J. 2190 FRIEDMAN, J.M. 2112 GODDARD, D.H. 2120 GUTTMAN, G.J. 2110 MAILLETT, J.R. 2310 PETERSON, N.M. 2000 SONNENFELDT, W.R. 2300 WALTERS, R.J. 2111 (10 ligne(s) affectée(s)) Exercice 6

Nom, unité, titre et date de naissance de tous les employés triés par numéro d'unité croissant (les plus jeunes d'abord) SELECT DISTINCT nom,unitcod,titre,datnaiss from employe order by unitcod asc,datnaiss desc; nom unitcod titre datnaiss -------------------- ------- ------------------------------ --------------------LYNN, K.R. PETERSON, N.M. 2000 Responsable de Division 1928-06-07 CALLAGAN, R.F. 2100 Secrétaire 1955-05-17 CARR, P.I. 2100 Responsable Dept. Etudes 1935-07-02 HARRIS, D.L. 2110 Secrétaire 1955-05-17

0021266950863

2000

Secrétaire

1953-01-21

116

Exercice 7

Nom et unité des employés ayant une qualification principale de secrétaire.

SELECT DISTINCT employe.nom,employe.unitcod from employe,qualif where (employe.qualifpr=qualif.codqual and qualif.qualibel='Secrétaire') ; nom unitcod -------------------- ------BERGMAN, R.I. 2130 CALLAGAN, R.F. 2100 FRESCOTT, W.C. 2300 GERRISH, C.S. 2120 HARRIS, D.L. 2110 LYNN, K.R. 2000 MELCHERT, F.F. 2310 (7 ligne(s) affectée(s)) Exercice 8 Nom, unité et date de naissance des employés plus agés que leur responsable, avec nom, unité et date de naissance de celui-ci. ( Classé par unité ) SELECT DISTINCT employe.nom,employe.datnaiss,employe.unitcod,employe_2.nom, employe_2.datnaiss,employe_2.unitcod from employe, employe as employe_2 where ((employe.unitcod=employe_2.unitcod) and (employe.datnaiss<employe_2.datnaiss)and (employe_2.niveau like "chef")) order by employe.unitcod nom datnaiss unitcod nom datnaiss unitcod -------------------- ---------- ------- -------------------- ---------- -------SCARBOROUGH, J.B. 1914-09-14 2111 WALTERS, R.J. 1926-02-02 2111 FRANCIS, G.C. 1932-11-11 2112 FRIEDMAN, J.M. 1936-03-17 2112 ARTHUR, P.J. 1930-01-09 2115 BRIGGS, G.R. 1940-05-08 2115 HUSHES, J.W. 1935-01-20 2123 SMITH, R.E. 1940-11-29 2123 RAYMOND, H.F. 1934-12-16 2123 SMITH, R.E. 1940-11-29 2123 MASSARD, L.R. 1943-02-23 2132 MACCARTHY, J.K. 1943-10-20 2132 SILCOTT, D.N. 1920-10-10 2132 MACCARTHY, J.K. 1943-10-20 2132 FLETCHER, M.W. 1932-07-29 2133 SCHRADER, F.G. 1942-09-09 2133 KENT, M.J. 1933-05-17 2133 SCHRADER, F.G. 1942-09-09 2133 0021266950863 117 LEGGETT, P.F. 1926-01-25 2133 SCHRADER, F.G. 1942-09-09 2133 (10 ligne(s) affectée(s))

Exercice 9

Nom et titre des employés plus agés que le responsable de l'unité 2000.

SELECT employe.nom,employe.titre from employe, employe as employe_2 where (employe_2.unitcod='2000') and (employe_2.niveau='CHEF') and (employe.datnaiss<employe_2.datnaiss) nom titre -------------------- -----------------------------SILCOTT, D.N. Ingénieur Electronicien SCARBOROUGH, J.B. Ingénieur Mécanicien WALTERS, R.J. Chef du Service Developpement HAMMER, C.P. Chef Serv. Qualité Production LEGGETT, P.F. Ingénieur Production GRAY, S.R. Outilleur SONNENFELDT, W.R. Responsable Département Export DIETERICH, L.J. Resp. Groupe Recherche Ext. (8 ligne(s) affectée(s))

0021266950863

118

Exercice 10

Codes unités n'ayant pas d'unité rattachée.

SELECT unite.unitcod from unite where unite.unitcod not in (select unite.umere from unite) unitcod ------2111 2112 2115 2122 2123 2131 2132 2133 2135 2190 2311 2315 2316 (13 ligne(s) affectée(s)) Exercice 11

Nom des employés appartenant à la même unité que 'LEVITT, P.S.'

SELECT DISTINCT employe.nom from employe where employe.unitcod = (select employe.unitcod from employe where employe.nom="LEVITT, P.S.") and employe.nom<>"LEVITT, P.S." nom -------------------LITTLE, E.F. PAYNE, W.F. (2 ligne(s) affectée(s))

0021266950863

119

Exercice 12

Code et nom des unités dépendant de la même unité que l'unité 2130.

SELECT DISTINCT unite.unitcod,unite.unite from unite where unite.umere=( select unite.umere from unite where unite.unitcod='2130' ) and unite.unitcod <>'2130' unitcod unite ------- -------------------2110 Groupe Systèmes 2120 Groupe Méthodes 2190 Groupe Recherche Ext (3 ligne(s) affectée(s)) Exercice 13

Nom et date de naissance des employés plus agés que tous les employés de l'unité 2131. SELECT DISTINCT employe.nom,employe.datnaiss from employe where employe.datnaiss < ( select min(employe.datnaiss) from employe where employe.unitcod="2131") nom datnaiss -------------------- --------------------------DIETERICH, L.J. 1925-04-23 00:00:00.000 GRAY, S.R. 1923-07-03 00:00:00.000 HAMMER, C.P. 1920-06-13 00:00:00.000 LEGGETT, P.F. 1926-01-25 00:00:00.000 PETERSON, N.M. 1928-06-07 00:00:00.000 SCARBOROUGH, J.B. 1914-09-14 00:00:00.000 SILCOTT, D.N. 1920-10-10 00:00:00.000 SONNENFELDT, W.R. 1923-05-01 00:00:00.000 WALTERS, R.J. 1926-02-02 00:00:00.000 0021266950863 (9 ligne(s) affectée(s))

120

Exercice 14

Nom des employés sur le même poste budgétaire que 'LEVITT'.

Select distinct employe.nom from employe,empsqual where employe.unitcod=( select employe.unitcod from employe where employe.nom="levitt, p.s.") and empsqual.empcod in (select distinct empsqual.empcod from empsqual where empsqual.empcod=( select distinct employe.empcod from employe where employe.nom="levitt, p.s.")) and employe.nom<>"levitt, p.s." nom -------------------LITTLE, E.F. PAYNE, W.F. (2 ligne(s) affectée(s)) Exercice 15

Code emploi et nom des employés de l'unité 2122 ayant même emploi que quelqu'un de l'unité 2132. SELECT DISTINCT employe.jobcode,employe.nom from employe as employe_2,employe where ((employe.unitcod="2122") and (employe_2.unitcod="2132") and (employe_2.jobcode=employe.jobcode)); jobcode nom ------- -------------------1130 STADERMAN, P.K. (1 ligne(s) affectée(s))

0021266950863

121

Exercice 16

Code qualification et nom des employés de l'unité 2111 ayant même qualification principale ou secondaire que quelqu'un de l'unité 2123. select distinct employe.nom, employe.qualifpr from employe, qualif,empsqual where employe.unitcod=2111 and ((employe.qualifpr in (select employe.qualifpr from employe where employe.unitcod=2123)) or (employe.qualifpr in (select empsqual.codqual from empsqual where employe.unitcod=2123)) or (empsqual.codqual in (select employe.qualifpr from employe where employe.unitcod=2123)) or (empsqual.codqual in (select empsqual.codqual from empsqual where employe.unitcod=2123)) ) /* ou */ select distinct employe.nom, employe.qualifpr from employe as employe_2, employe, empsqual as empsqual_2, empsqual where employe.unitcod=2111 and (employe.qualifpr=employe_2.qualifpr or empsqual.codqual=empsqual_2.codqual) and employe_2.unitcod=2123 nom qualifpr -------------------- -------GARBER, R.E. 1330 COMPTON, D.R. 1350 WALTERS, R.J. 1110 SCARBOROUGH, J.B. 1120 HENDERSON 1130 (5 ligne(s) affectée(s))

0021266950863

122

Exercice 17

Même demande, mais avec le libellé de la qualification en clair.

select distinct employe.nom, employe.qualifpr,qualif.qualibel from employe as employe_2, employe, empsqual as empsqual_2, empsqual,qualif, qualif as qualif_2 where employe.unitcod=2111 and (employe.qualifpr=employe_2.qualifpr or empsqual.codqual=empsqual_2.codqual) and employe_2.unitcod=2123 and employe.qualifpr=qualif.codqual and qualif.qualibel=qualif_2.qualibel nom qualifpr qualibel -------------------- -------- -------------------WALTERS, R.J. 1110 Ingénieur Systèmes SCARBOROUGH, J.B. 1120 Ingénieur Mécanicien HENDERSON 1130 Ingénieur Electronic GARBER, R.E. 1330 Dessinateur COMPTON, D.R. 1350 Respons Coût (5 ligne(s) affectée(s)) Exercice 18

Nom et âge des employés de l'unité 2111 par ordre alphabétique.

SELECT DISTINCT employe.nom ,( datediff (year,employe.datnaiss,current_timestamp))as age, from employe where ((employe.unitcod=2111) ) order by employe.nom asc; nom age -------------------- ----------COMPTON, D.R. 71 GARBER, R.E. 56 HENDERSON 66 SCARBOROUGH, J.B. 86 WALTERS, R.J. 74 (5 ligne(s) affectée(s))

0021266950863

123

Exercice 19

Nom et âge des employés de l'unité 2111 par ordre alphabétique avec « ans »

SELECT DISTINCT employe.nom ,( datediff (year,employe.datnaiss,current_timestamp))as age, "ans" from employe where ((employe.unitcod=2111) ) order by employe.nom asc; nom age -------------------- ----------- ---COMPTON, D.R. 71 ans GARBER, R.E. 56 ans HENDERSON 66 ans SCARBOROUGH, J.B. 86 ans WALTERS, R.J. 74 ans (5 ligne(s) affectée(s)) Exercice 20

Salaire moyen de l'entreprise

select distinct avg (employe.salaire) as salaire_moyen from employe salaire_moyen ------------216321 (1 ligne(s) affectée(s))

0021266950863

124

Exercice 21

Code unité, nom et nombre d'employés de chaque unité.

select distinct unite.unitcod,unite.unite, count (employe.unitcod) as nbr_employe from employe , unite where((unite.unitcod = employe.unitcod)) group by unite.unitcod , unite.unite unitcod unite nbr_employe ------- -----------------------------2000 Division Commutation 2 2100 Département Etudes 2 2110 Groupe Systèmes 2 2111 Service Développemt 5 2112 Service Etudes 5 2115 Service Prototypes 3 2120 Groupe Méthodes 2 2122 Méthodes Systèmes 3 2123 Méthodes Protos 5 2130 Groupe Qualité 2 2131 Service Qualité A 4 2132 Service Qualité B 4 2133 Qualité Protos 5 2135 Qualité Production 4 2190 Groupe Recherche Ext 1 2300 Département Export 2 2310 Groupe Prod. Export 2 2311 Fabrication Export 3 0021266950863 (18 ligne(s) affectée(s))

125

Exercice 22

Code, libellé et salaire moyen de l'emploi ayant le salaire moyen le plus bas select employe.jobcode,qualif.qualibel,avg(employe.salaire)as salaire_moyen from employe,qualif where (qualif.codqual=employe.jobcode) group by EMPLOYE.jobcode,qualif.qualibel having avg (employe.salaire) <= all ( select avg (employe.salaire) from employe group by employe.jobcode) jobcode qualibel salaire_moyen ------- -------------------- ------------5210 Secrétaire 97428 (1 ligne(s) affectée(s))

0021266950863

126

Pour plus d’information veuillez me contacter

0021266950863

127