CORRIGE REVISIONS-2 – CAS CAFE

Manipulation des données et langage de définition de données

Création des tables :

create table conso (

numcons               integer                    primary key ,

libcons                   varchar(30)          not null,

prixcons                numeric(6,2)         not null check(prixcons between 1 and 10)

);

create table           tables(

numtable              integer                    primary key,

nomtable              varchar(30)          not null,

nbplaces                smallint  not null

);

create table           serveur (

numserv                integer                    primary key,

nomserv                varchar(30)          not null,

rueserv                  varchar(30),

cpserv                    char(5),

villeserv                 varchar(30),

datenaiss              date

);

create table           facture (

numfact                integer                    primary key,

numtable              integer                    references tables(numtable) ,

numserv                integer                    references serveur(numserv),

datefact                 date        not null

);

create table           ligne_fact (

numfact                integer                    references facture(numfact) on delete cascade,

numcons               integer                    references conso(numcons),

qte                          smallint not null,

primary key(numfact,numcons)

);

Ecrire et exécuter les requêtes ci-dessous : 

1) Ajouter un attribut commentaire, d'une longueur de 80, dans la table des serveurs.

Alter table serveur

Add commentaire char(80);

2) Ajouter la contrainte "quantité facturée obligatoirement positive" dans la table des lignes de facture.

Alter table ligne_fact

Add constraint ck_qte check(qte > 0);

3) Dans la table des serveurs, faire passer la longueur de la ville à 35 caractères.

Alter table serveur

alter column villeserv varchar(35);

4) Interdire la création de doublons sur les noms des serveurs.

Alter table serveur

add constraint ck_un unique(nomserv);

5) Obtenir la liste des factures avec pour chaque facture le numéro de la facture, le nom de la table et la date de la facture.

select numfact,nomtable,datefact

from facture f

inner join tables t on f.numtable=t.numtable;

6) Obtenir les noms des consommations servies le 22/02/2006.                 

select distinct libcons

from conso c

inner join ligne_fact f

on c.numcons = l.numcons

inner join facture f

on f.numfact = l.numfact

and datefact = '2006-02-22';

7) Connaître le nombre de serveurs.

select count(*)

from serveur;

8) Ajouter le serveur n°54, il s'appelle Lulu, habite 35, rue des Lilas 69000 Lyon et est né le 20/06/1986.

insert into serveur

values (54, 'Lulu', '35, rue des Lilas', 69000, 'Lyon', '1986-06-20');

9) Obtenir les numéros et nombre de places des tables qui n'ont pas été occupées.

select numtable, nbplaces

from tables

where numtable not in

                (select numtable

                from facture);

10) Connaître le nom et le prix de la consommation la moins chère.

select libcons, prixcons

from conso

where prixcons =

                (select min(prixcons)

 from conso);

11) Obtenir le numéro, la date et le montant total de chaque facture.

select f.numfact, datefact, sum(prixcons * qte)  

from conso c

inner join ligne_fact f

on c.numcons = l.numcons

inner join facture f

on f.numfact = l.numfact

group by f.numfact, datefact;

12) Augmenter de 10% le prix du café, café double et café crème.

update conso

set prixcons=prixcons*1.1

where libcons in ('Café', ‘Café double’, ‘Café crème’);

Ou bien (si on considère qu’il n’y a pas d’autres consommations dont le nom commence par café) :

update conso

set prixcons=prixcons*1.1

where libcons like 'Café%';

13) Connaître les noms des serveurs ayant effectué plus de 15 € de chiffre d'affaires.

select nomserv

from conso c

inner join ligne_fact f

on c.numcons = l.numcons

inner join facture f

on f.numfact = l.numfact

inner join serveur s

on s.numserv = f.numserv

group by nomserv

having sum(prixcons * qte) > 15;

14) Dans la facture 1207, la consommation 108 a été saisie par erreur, faire le nécessaire.

delete from ligne_fact

where numfact=1207    

and numcons=108;

15) Connaître le nom et l'âge de chaque serveur. Pour extraire l'année, il faudra utiliser la fonction YEAR.

La date du jour est contenue dans CURRENT_TIMESTAMP.

Select nomserv, date_part('year', CURRENT_DATE) - date_part('year', datenaiss) 

From serveur;