PostgreSQL

Dans ce TP, vous devrez utiliser l'environnement PostgrSQL de l'université pour gérer deux bases de données. Pour utiliser cet environnement, vous devez suivre les instructions suivantes :

• Activer votre base PostgreSQL à l'adresse http://etudiant.univ-mlv.fr/activpostgresql.php
• Accéder à cette base à l'adresse http://etudiant.univ-mlv.fr/phppgadmin/
• Cliquer sur l'icone PostgreSQL en haut à gauche, puis entrer votre login et votre mot de passe étudiant.
• Créer une base de données à laquelle vous donnerez un nom (par exemple : votre-login+Exo1)
• Créer vos tables en entrant le contenu SQL à partir du fichier fourni.

Remarque : Il est préférable de créer une deuxième BDD pour l'exercice 2 de la série.

Cet exercice consistera à écrire des requêtes SQL sur une base de données conçue sur le modèle suivant :

Le schéma relationnel brut :

				CENTRE (n_centre, nomc, ca, benef, dateouv)
				FP (n_fp, nomfp)
				Distribue (n_centre, n_fp)
				Est_rattaché_à (n_centre, siège)
			

Le schéma relationnel optimisé :

				CENTRE (n_centre, nomc, ca, benef, dateouv, n_fp, siège)
				FP (n_fp, nomfp)
			

Note : Au niveau logique, on a partitionné la table Centre en deux tables:

• une, de nom Centre, représente les centres en France
• une autre, de nom Centre_HF, représente les centres hors France.

Le fichier de chargement des tables se trouve ici.
Ecrire en SQL les requêtes suivantes:

1. Quels sont les centres qui distribuent le produit n°1 ou qui sont rattachés au siège n°2
2. Quels sont les numéros des centres qui ont un CA supérieur à 200 et qui distribuent le produit n° 2
3. Quels sont les numéros des centres qui ont un CA supérieur à 100 et qui distribuent le produit n°2 ou le produit n°3
4. Indiquer par produit (N° produits) quels sont les numéros des centres qui les distribuent. Trier par produits.
5. Indiquer par centre (numéro de centre), quels sont les numéros de produits distribués. Trier par centre.
6. Pour les produits réellement distribués, donner le numéros de produits et les numéros des centres qui les distribuent, triés par produit.
7. Donner par produits (nom, numéros), les numéros des centres qui les distribuent avec leur CA
8. Pour les centres qui ont ouvert avant le 1er janvier 1986, donner le nom du centre, avec le numéro et le nom du produit distribué
9. Donner le nom des produits distribués simultanément en France et à l'étranger.
10. Donner le nom des produits distribués seulement en France.
11. Donner le nom des produits distribués en France ou à l'étranger.
12. Insertion d'un produit distribué seulement en France.
13. Par numéros de produit, donner le plus grand CA réalisé.
14. Par numéro de produit, donner le plus grand CA réalisé, sans afficher les lignes pour lesquelles le CA n'est pas renseigné.
15. Dire par numéro de produits, combien de centres le distribuent.
16. Dire par numéro de produits, combien de centres le distribuent, sans afficher de lignes pour les produits non renseignés.
17. Dire par numéro de produits, combien de centres le distribuent. Prendre en compte tous les produits, même ceux qui ne sont pas distribués, sans afficher de lignes pour les produits non renseignés.
18. Combien sont les produits distribués ( comptage avec doublons) ?
19. Combien sont les produits effectivement distribués ? (comptage sans doublons)
20. Dire par numéro de produit, combien de centres le distribuent (produits réellement renseignés)
21. Dire par numéro de produit, combien de centres le distribuent (afficher tous les centres, même si le produit n'est pas renseigné)
22. Dire par numéro de produit, combien de centres le distribuent (afficher tous les produits, même ceux qui ne sont pas distribués)
23. Donner les numéros de produits distribués par plus de 2 centres. Résultat attendu sur deux colonnes: numéro_de_produit et nombre_de_centres
24. Avec et sans utilisation de vues, donner le(s) numéros de produit(s) les moins distribués.
25. Indiquer par produit le nom du centre qui réalise le plus grand chiffres d'affaires.

Cet exercice consistera à écrire des requêtes SQL sur une base de données conçue sur le modèle suivant :

Le schéma relationnel :

				Client (n_client, nomc, prenom1, ville, sexe, prenom2)
				Compte (n_compte, solde, dateouv, datederop, interet, n_type, n_agence)
				Typecompte (n_type, libelle)
				Agence (n_agence, ville, tel, n_dir)
				Directeur (n_dir, nom, prenom, telprof, telpers, ville)
				Client_compte (n_client, n_compte)
			

Le fichier de chargement des tables se trouve ici.
Ecrire en SQL les requêtes suivantes:

1. Opérations de Sélection
   • Sélectionner tous les numéros d'agence et les villes de toutes les agences (table agence)
   • Sélectionner le numéro client, le nom et les prénoms de tous les clients habitant à Lyon et de sexe masculin (t. client).
   • Donner les numéros des comptes ayant un solde négatif (t. compte).
   • Quels sont les noms des clients commençant par les lettres C ou D? (t. client)
   • Donner les numéros de clients qui ont des comptes (t. client_compte)
   • Donner les numéros de clients qui n'ont pas de compte (t. client, client_compte)
2. Opérations de Tri - Fonctions arithmétiques et de groupe
   • Donner le numéro client et le nom des clients, triés en ordre croissant sur le numéro de client (t. client).
   • Combien sont les clients habitant à Lyon ? (t. client)
   • Combien de comptes ont un solde négatif ? (t. compte)
   • Quelle est, par type de compte, la moyenne des soldes ? (t. compte)
   • Donner, par type de compte, la plus récente date d'ouverture (t. compte).
   • Donner le nombre de clients de sexe masculin et de sexe féminin de la Banque (t. client).
   • Donner par client le numéro du client, le nom et le solde moyen de ses comptes. Trier le résultat sur le numéro du client.
   • Donner les types de compte pour lesquels il y a 3 comptes ou plus.
   • Donner les types de comptes pour lesquels la moyenne des soldes est supérieure à 1000 euros
   • Donner les numéros et les noms des clients pour lesquels la moyenne des soldes de leurs comptes est supérieure à 400 euros. Trier le résultat sur le numéro de client.
3. Jointures
   • Pour chaque compte, donner le numéro de compte et le libellé du type de compte. Trier le résultat sur le libellé du type de compte (t. compte, typecompte).
   • Pour chaque agence, donner la ville de l'agence et le nom du directeur. Trier le résultat sur le numéro d'agence (t. agence, directeur).
   • Pour chaque compte, ayant un solde supérieur à 1 000, donner le numéro et le nom du ou des titulaires et le libellé du type de compte. Trier le résultat sur le numéro de compte (t. compte, typecompte, client, client_compte).
4. Insertion, Suppression, Mise à jour
   • Insérer dans la table directeur un tuple avec les valeurs suivantes : n_dir= 2, nom='BRABAN' ;
   • Supprimer dans la table directeur le (ou les) tuple(s) ayant ville = 'PARIS' ;
   • Quelle est la réponse du système pour ces deux opérations ?
   • Créer une table directeur1 en sélectionnant tous les tuples de la table directeur.
   • Insérer dans la table directeur1 un tuple avec les valeurs suivantes : n_dir = 2, nom= 'BRABAN'.
   • Supprimer dans la table directeur1 le (ou les) tuple(s) ayant ville = 'PARIS'.
   • Mettre à jour, dans la table directeur, les informations du directeur numéro 2 (Prénom : Patrick, Téléphone personnel : 0123456789, Ville : Champs Sur Marne)
   • Ajouter une nouvelle agence (Numéro 6, Ville : Champs Sur Marne)
   • Affecter l'agence numéro 6 au directeur : Patrick BRABAN. Indiquer les limites de cette requête.
   • Mettre à jour le solde, du compte numéro : 3817, en l'augmentant de 49 euros. Mettre à jour également la date de la dernière opération avec la date du jour.
5. Requêtes imbriquées
   • Quel est le type de compte le plus répandu dans la banque (t. compte) ?
6. Encore quelques Opérations de Jointure
   • Pour chaque agence, donner son numéro d'identification, son numéro de téléphone et le nom du directeur. Trier le résultat sur le numéro d'agence.
   • Pour chaque directeur, donner son numéro et son nom, ainsi que le numéro de tous les comptes de son agence. Trier le résultat sur le numéro du directeur
   • Indiquer, pour chaque directeur, son numéro, son nom et le numéro de tous les comptes de son agence, ouverts après le 10 février 2000. Trier le résultat sur le numéro du directeur.
   • Pour chaque directeur, donner son numéro, son nom, le numéro de tous les Comptes de chèques, ouverts dans son agence après le 10 février 2000.
   • Déterminer, pour les agences n° 1 et 2, le numéro, le nom, les prénoms et le sexe des clients ayant un compte dans l'agence. Trier le résultat sur le numéro de client.
   • Déterminer, pour chaque agence, combien d'hommes et combien de femmes ont un compte chez elle. Trier le résultat d'abord sur le numéro d'agence et ensuite sur le sexe.
7. Création et manipulation de vues
   • Créer une vue, nommée "client_paris", en sélectionnant les attributs numéro client, nom et prénoms des clients habitant à PARIS (t. client).
   • Sélectionner tous les tuples définis par la vue "client_paris".
   • Créer une vue, nommée "client_compte_paris", en sélectionnant les attributs numéro client et numéro de compte des clients ayant des comptes dans l'agence de PARIS (t. client_compte, compte, agence).
   • Sélectionner tous les tuples définis par la vue "client_compte_paris".
   • Avec et sans utilisation des vues que vous venez de créer, donner, sans répétitions, le numéro, nom et prénoms des clients habitant à Paris et ayant un compte à Paris (vues : client_paris, client_compte_paris, ou bien tables : client, client_compte, compte, agence).
   • Insérer dans la vue client_paris un enregistrement avec les valeurs : n_client = 1111, nomc = SIMMON, prenom1 = MARC. Comment interprétez-vous le message du système ?
   • Sélectionner tous les tuples de la table client. Détruire la vue client_paris ; Créer une table client1 en recopiant la table client.
     
     A partir de votre table client1, créer une nouvelle vue client_paris en sélectionnant le numéro de client, le nom et les prénoms des clients habitant à PARIS. Utiliser la vue client_paris pour insérer un enregistrement de valeurs : n_client = 1111, nomc = SIMMON, prenom1 = MARC Sélectionner tous les tuples de la table client1. Le client SIMMON apparaît ? Sélectionner tous les tuples de la vue client_paris. Le client SIMMON n'apparaît pas. Pourquoi ? Comment faire pour le faire apparaître ?