Discussion :

[etudesup]

Bonjour ,
j'ai des requetes SQL a faire mais j'ai besoin d'aide!!!!
Voila le sujet :


Exercice:

client (NumCl, NomCl, AdrCL, CodePostalCl, VilleCl)
Commercial (NumCo, NomCo, DateEmbauche)
résidence (NumR, DateConstruction, AdrR, CodePostalR, VilleR)
Logement (NumR#, NumL, NumEtage, NbrPiéces, Superficie)
Transaction, (NumT, DateD, DateF, Etat, NumCl#, NumCo#, NumR#, NumL#, NumP)

Ecrire les requêtes suivantes:
1) Donnez les numéros des transactions ne portant pas sur un parking.

2) Les commerciaux sont en concurrence. Pour chaque résidence commercialisée, celui qui vend le plus d'appartements gagne une prime. Pour mettre à jour l'affichage du tableau d'honneur de la société, vous devez donnez le nopbre maximal d'appartements vendus par un commercial au sein d'une même résidence, toutes résidences confondues (il faudra sans doute deux requêtes)

3) Donnez les clients ayant effectué au maximum trois transactions

4) donnez la résidence qui contient autant de logement que la résidence 4

5) Aprés une réservation, le client a 30 jours pour annuler ou acheter. Au-delà, la réservation est automatiquement annulée pas l'application. Ecrivez la requête réalisant les annulation des réservation concernées

6) Donnez le nombre d'appartements à chaque étage de chaque résidence construite à caen

7) Quel est le numéro du logement le plus grand (superficie) dans la résidence 23?

8) Donnez les logements (numéro de résidence et de logement) qui ont été successivement réservés trois fois, dont les réservations ont toutes été annulées et qui sont toujours en vente.

9) Donnez la liste des clients pour lesquels plus aucune réservation ne sera prise. Ce sont ceux ayant effectué au moin cinq réservation (en cours ou annulées) n'ayant pas abouti à un achat

10)La réservation faite par le commercial 4 le 12/02/2007 donne lieu aujourd'hui à un achat. Seul el logement est acheté, le client renonce au parking numéro 17 qu'il avait réservé avec le logement. Mettez les tables à jour

11) Donnez, pour chaque résidence le nombre de clients possédant plusieurs appartements.

12) donnez la superficie moyenne de tous les appartements en ne prenant en compte que les résidence de plus de deux étages

13)Donnez les résidences ayant autant d'appartements que l'une des résidences construites en 2007.

14) Le logement 12 de la résidence 16 est acheté le 17/04/2007. Vous devez metre à jour la réservation correspondante (à vous de la déterminer) et annuler toutes les autres réservations en cours sur cet appartement (la date d'achat sera utilisée comme dat d'annulation).


Merci pour votre aide

[etudesup]

J'ai avancer un peu et j'ai trouver sa pour le moment :


1)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Select NumT
from Transaction
where NumP is null;

pour le 3)
je pense a

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Select NumCl, NomCl, count(*) as [nbr de transaction]
from client cl, transaction T
where cl.NumCl = T.NumCL
group by NumCl, NomCl
having count(*) 3;

la 6)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Select NumR, NumE, count(*) as [nbr d'appart]
from Résidence R, Logement L, transaction T
where L.NumR = R.NumR and
T.NumR = R.NumR and
L.NumL = T.NumL and
NumR = [Numero residence] and
NumE = [numero Etage];

la 7)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Select NumL, max superficie
from Logement L, Residence R, transaction T
where L.NumR = R.NumR and
T.NumR = R.NumR and
L.NumL = T.NumL and
NumR = "23";

la 9)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select NumCl, NomCl, count(Etat) as [nbr de transaction]
from Client Cl, Transaction T
where Cl.NumCl = T.NumCl and
etat = "R"
group by NumCl, NomCl
having count(Etat) 5;

la 10)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
update Transaction 
set Etat = A
NumP is null
where NumCo = "4" and
DateD= #12/02/2007# and
NumP = "17";

la 11)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select NumR, NumCl, count(NumCl) as [nbr de client]
from residence R, Logement L, transaction T
where L.NumR = R.NumR and
T.NumR = R.NumR and
L.NumL = T.NumL 
group by NumR, NumCl
having count (numCl) > 1;

[etudesup]

je suis quasi-certaine que les requetes que j'ai deja faites contiennent des erreurs mais je n'arrive pas trop a voir ça me semble juste mais bizarre en meme temps

Je suis en train de bosser sur les autres mais j'ai un peu de mal surtout la 2 , 8 , 14 , et la 12 .
Merci de votre aide

[Waldar]

Quel est le SGBD derrière, SQL Server ? Access ? Quelle version ?
De manière générale, dès que vous attaquez plus d'une table, utilisez toujours vos alias de tables sur toutes les colonnes, ça facilite la relecture, ça évite au SGBD d'aller interroger les vues systèmes bref tout le monde est content.

Quand une colonne représente un nombre, pas besoin de quote, écrivez simplement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

macolnum = 2

Quand une colonne représente une chaîne de caractère, on utilise l'apostrophe simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

macolstr = 'A'

Pour les dates, il faut convertir vos chaîne de caractère en date.
La fonction de conversion dépend du SGBD.


La requête 1 me semble correcte.

Dans la 3 il manque l'opérateur entre count(*) et 3, mais je pense que c'est plus une erreur de recopie.

Dans la requête 6 vous n'avez pas besoin des deux derniers filtres, puisque qu'on vous demande le total. Il manque par contre le filtre sur Caen.

Dans la requête 7 vous n'avez besoin que de la table logement.
Il manque le group by, et encore une autre opération pour ne conserver que le plus grand (celle-ci dépend du SGBD).

Requête 9, comment savoir si une transaction est devenue un achat ou pas ?

La 10, aux apostrophes et virgule près ça me paraît correct.

La 11, c'est plus complexe et ce que vous avez écrit n'est pas correct.
Dans le group by, vous n'avez besoin que du NumR.
Il faudra aussi changer votre HAVING.

[etudesup]

le SGBD est Access .
Mais c'est un exercice sur papier.........je n'ai pas la base de données donc impossible de les tester !!!

J'ai oublier de donner les informations données en plus
je vous les donne donc maintenant :$
" 1) l'identifiant de logement s'explique par le fait qu'au niveau MCD, l'entité logement est une entité faible de résidence

2) la table transaction permet de stocker les réservation et les ventes de logements. toute transaction débute a une date (champ dataD) et se finit a une autre (dateF). elle est conclue par un commercial (NumCo). Elle concerne un client (NumCl) qui s'interesse, au sein d'une même résidence (NumR), à un logement (NumL) et à un parking (NumP). Quatre remarque importantes:
_ une transaction est soir un réservation (le champ etat vaut alors le caractere R) , soit un achat (Etat vaut A)
_on suppose que toute transaction est initialement une réservation (la date ets stockée dans dateF et dateD n'est pas renseignée):
- si le client annule sa réservation, DateF ets renseignée à la date d'annulation et l'occurence de transaction demeure
- si la réservation donne lieu à un achat, Etat passe de R à A et dateF est la date d'achat. En d'autre termes, lorsqu'un achat est effectué, l'occurence de transaction correspondante passe de réservation à achat.
_ comme l'annulation est possible plusieurs réservation peuvent être prises pour un même logement. La réservation la plus ancienne est prioritaire. Si le client annule, la suivante chronologiquement (et non annulée) est alors prise en compte
_ une transaction porte toujours sur un logemebt et parfois sur un parking : si le client en souhaite un, le champ NumP ets renseignée, sinon il ne contient aucune valeur. Une réservation peut porter sur un logement et un parking mais l'achat correspondant ne peut concerner que le logement. NumP ets alors mis à jour.""

Concernant la 9 , on sait donc si une transaction est devenue achat ou pas car ETAT passe de R à A (A si il y a un achat) .

Dans la 7 , je ne vois pas ce que je peux mettre avec group by , ni quelle autre operation je pourais rajouter.....

Pour la 11 je ne comprends pas pourquoi le having n'est pas juste.........

merci bcp de votre aide