Base de données relationnelles

Postgre

Postgre est un système de gestion de base de données relationnelle et objet (SGBDRO) libre,

disponible sous Windows et sous linux.

Utilisation sous Windows

Trouver le dossier (par défaut dans « Programmes »)

Via la console :

>

Via une interface graphique :

> > >

Se connecter à une base de données

Quelques commandes utiles

Commande Action

\c nomRépertoire

Se déplace vers un autre répertoire, exemple :

\d

Affiche l’ensemble des relations de la base de données courante, exemple : Schema | Name | Type | Owner

-------+-----------+-------+--------

public | nomTable1 | table | utili1

public | nomTable2 | table | utili1

(2 rows)

\d nomDeTable

Affiche la structure de la table spécifiée, exemple : Table "public. nomDeTable "

Column | Type | Modifiers

-------+-----------------------+-----------

nomC1 | integer | not null

nomC2 | character varying(30) | not null

....

\q Ferme PostgreSQL.

Nom du serveur, obligatoire.

Nom de la base (facultatif, si on ne met rien

on se retrouve à la racine et on doit se

déplacer dans le bon répertoire avec un \c).

Requêtes SQL

Soit la base de données suivante :

Les marques devront être identifiées par leur nom, classe et pays; et les sociétés devront être

identifiées par leur nom et pays.

1. Les marques (avec nom/classe/pays), classées par pays puis par classe : SELECT nom, classe, pays FROM marque ORDER BY pays, classe;

2. Les marques de chaque propriétaire (ne pas sortir les sociétés sans marques) : SELECT societe.nom, marque.nom FROM societe INNER JOIN marque ON societe.id=marque.prop;

3. Les sociétés qui ne possèdent aucune marque : SELECT societe.nom FROM societe EXCEPT SELECT societe.nom FROM societe INNER JOIN marque ON societe.id=marque.prop;

4. Les marques vendues avant leur enregistrement (ce qui est incorrect!) : SELECT marque.nom FROM marque INNER JOIN enr ON marque.id=enr.num INNER JOIN vente ON vente.marque=enr.marque WHERE vente.date_vente<enr.date_enr;

Mais si on a une des tables vide cette requête ne détectera pas les enregistrements

donc il faudra opter pour une autre solution pour pallier à ce problème : SELECT m.nom FROM marque m WHERE m.id IN (SELECT e.marque FROM enr e WHERE e.marque IN (SELECT v.marque FROM vente v WHERE v.date_vente<v.date_enr));

5. Les marques non enregistrées, classées par pays : SELECT marque.nom FROM marque WHERE marque.id NOT IN (SELECT enr.marque FROM enr) ORDER BY marque.pays;

6. Les propriétaires avec leur nombre de marques : SELECT societe.nom, COUNT(marque.id) FROM societe INNER JOIN marque ON societe.id=marque.prop GROUP BY societe.nom, societe.id;

7. Les marques homonymes (même nom et même classe) dans des pays différents et avec

des propriétaires différents :

Il faut une relation d'ordre (sur une seule colonne) pour éliminer la symétrie (une

seule des deux, laquelle n'a pas d'importance mais pas les deux sinon on a plus de

résultats) : SELECT m1.nom, m1.classe, m1.pays, m1.prop FROM marque m1, marque m2 WHERE m1.nom=m2.nom AND m1.classe=m2.classe AND m1.pays<m2.pays AND m1.prop<>m2.prop;

8. Les propriétaires dont les marques déposées (i.e., qui sont dans la table marque) ne

sont pas toutes enregistrées : SELECT societe.nom, societe.pays FROM societe WHERE societe.id IN (SELECT marque.prop FROM marque WHERE marque.id NOT IN (SELECT enr.marque FROM enr));

9. Les propriétaires dont les marques (i.e., qui sont dans la table marque) sont toutes

enregistrées : SELECT societe.nom, societe.pays FROM societe WHERE societe.id IN (SELECT marque.prop FROM marque EXCEPT SELECT marque.prop FROM marque WHERE marque.id NOT IN (SELECT enr.marque FROM enr));

10. Dans chacune des classes, le/les propriétaires ayant le plus grand nombre de marque

dans cette classe :

Méthode 1 (sans vue) : SELECT marque.classe, marque.prop, COUNT(*) FROM marque GROUP BY marque.classe, marque.prop HAVING COUNT(*) >= ALL (

SELECT COUNT(*) FROM marque AS marque2 WHERE marque2.classe=marque.classe GROUP BY marque2.prop)

ORDER BY marque.classe ASC;

Méthode 2 (avec vue) : CREATE VIEW vue_question10 AS SELECT marque.classe, marque.prop, COUNT(*) AS cnt FROM marque GROUP BY marque.classe, marque.prop;

SELECT vue_question10.classe, MAX(vue_question10.cnt) FROM vue_question10 GROUP BY vue_question10.classe ORDER BY vue_question10.classe ASC;

11. Vérifier la contrainte suivante :

Le vendeur d’une marque est soit le déposant (et c’est la première vente de cette marque)

soit l’acquéreur de la vente précédente. SELECT vente.vendeur, vente.marque, vente.date_vente FROM vente WHERE NOT EXISTS ( SELECT * FROM vente AS v2 WHERE vente.marque=v2.marque AND v2.date_vente<vente.date_vente) AND NOT EXISTS ( SELECT * FROM enr WHERE enr.marque=vente.marque AND enr.deposant=vente.vendeur);