recherche TP SQL

Noob Inscrit le : 27 Sep 2007 Messages : 11 Localisation : Dinan

Salut !

quelqu'un aurait-il gardé les bases SQL des TPs ( GEF, Asterix, vin,..... ) ?
si quelqu'un peut les déposer sur le forum ou me les envoyer par mail.

Merki

Et Bonnes vavcances à tous !!

Cyril

salut cyril , j'ai ce qu'il te faut Smile

La flemme de faire un .rar jte fait un copier/coller (avec réponses Smile

)

Vins

• Liste alphabétique des vins de 1979 et après.
SELECT * FROM vin
WHERE milvin >= 1979
ORDER BY nomvin;

• Liste alphabétique des acheteurs de Nantes et de Laval.
SELECT noach, nomach, vilach
FROM acheteur
WHERE vilach = 'Nantes'
or vilach = 'Laval'
ORDER BY nomach;

• Lister les achats de Madiran et de Riesling effectués par Chombier Marcel.
SELECT *
FROM achete AS a, acheteur AS r, vin AS v
WHERE v.novin = a.novin
and a.noach = r.noach
and nomach = 'Chombier Marcel'
and (nomvin = 'Madiran' or nomvin = 'Riesling');

• Lister les achats d’une quantité supérieure ou égale à 10 litres, sans afficher ceux de l’acheteur numéro 10.
SELECT *
FROM achete
WHERE qte >=10
and noach <> 10;

• Lister le nom et la ville des acheteurs ayant fait au moins un achat d’au moins 30 litres. (On ne listera qu’une seule fois chaque acheteur concerné).
SELECT DISTINCT nomach, vilach
FROM acheteur AS r, achete AS a
WHERE r.noach = a.noach
and qte >= 30;

• Lister le nom acheteur, le numéro du vin, le nom du vin et la quantité pour les achats concernant du Chablis 1980 ou un millésime antérieur à 1978.
SELECT nomach, v.novin, nomvin, qte
FROM achete AS a, vin AS v, acheteur AS r
WHERE a.novin = v.novin
and a.noach = r.noach
and (milvin < 1978 or (nomvin = 'Chablis' and milvin = 1980));

• Donner la quantité globale de vin achetée.
SELECT sum(qte) AS [Quantité globale] FROM achete;

• Lister le nom des vins, le millésime des vins avec la moyenne de quantité achetée, et le nombre d’acheteurs.
SELECT nomvin, milvin, avg(qte) AS [Qte moyenne achetée]
FROM vin AS v, achete AS a
WHERE a.novin = v.novin
GROUP BY nomvin, milvin;

• Lister le nom des acheteurs ayant réalisé au moins quatre achats.
SELECT nomach, count(*) AS [Nombre d'achats]
FROM achete AS a, acheteur AS r
WHERE a.noach = r.noach
GROUP BY nomach
HAVING count(*) >=4;

• Lister le nom des acheteurs avec la quantité totale achetée, le nombre d’achats. La liste classera les acheteurs du plus « gros » au plus « petit » en fonction de la quantité.
SELECT nomach, sum(qte) AS [Quantité globale], count(*) AS [Nb achats]
FROM achete AS a, acheteur AS r
WHERE r.noach = a.noach
GROUP BY nomach
ORDER BY sum(qte) DESC;

• Donner la moyenne de la quantité achetée pour le Santenay de 1976.
SELECT avg(qte) AS [Quantité moyenne Santenay]
FROM achete AS a, vin AS v
WHERE v.novin = a.novin
and nomvin = 'Santenay'
and milvin = 1976;

• Lister le nom et la ville des acheteurs ayant acheté du Madiran, quel que soit le millésime.
SELECT nomach, vilach
FROM acheteur AS r, achete AS a, vin AS v
WHERE a.noach = r.noach
and v.novin = a.novin
and nomvin = 'Madiran';

• Donner pour chaque vin, le nombre de millésimes différents.
SELECT nomvin, count(milvin) AS [Nombre de millésimes]
FROM vin
GROUP BY nomvin;

GEF

• Nom du ou des auteurs du livre dont le titre est Correspondance.
SELECT nomauteur
FROM auteur AS a, livre AS l, ecritpar AS e
WHERE a.noauteur = e.noauteur
and l.nolivre = e.nolivre
and titre = 'Correspondance';

• Liste des auteurs décédés qui ont fait partie de l’ Académie Française.
SELECT nomauteur
FROM auteur AS a, membrede AS m
WHERE a.noauteur = m.noauteur
and academie = 'Académie Française'
and datedeces is not null;

• Lister le nom de chaque académie avec le nombre de membres vivants.
SELECT Academie, count(*) AS [Nombre de membres vivants]
FROM membrede AS m, auteur AS a
WHERE a.noauteur = m.noauteur
and datedeces is null
GROUP BY Academie;

• Titre et titre du livre suite pour les livres ayant une suite.
SELECT l.titre AS [Titre du livre], ls.titre AS [Titre du livre suite]
FROM livre AS l, livre AS ls
WHERE l.livresuite = ls.nolivre;

• Nom des auteurs ayant obtenu le prix Goncourt classés par ordre chronologique d’obtention.
SELECT nomauteur, année
FROM auteur AS a, livre AS l, ecritpar AS e, obtientprix AS o
WHERE a.noauteur = e.noauteur
and l.nolivre = e.nolivre
and l.nolivre = o.nolivre
and nomprix = 'Goncourt'
ORDER BY année;

• Titre des livres ayant obtenu plusieurs prix.
SELECT titre, count(*) AS [Nombre de prix]
FROM livre AS l, obtientprix AS o
WHERE o.nolivre = l.nolivre
GROUP BY titre
HAVING count(*) >1;

• Titre des livres ayant obtenu plusieurs prix la même année.
SELECT titre, année, count(*) AS [Nombre de prix]
FROM livre AS l, obtientprix AS o
WHERE o.nolivre = l.nolivre
GROUP BY titre, année
HAVING count(*) >1;

• Nom des auteurs décédés n’ayant jamais été édités chez Flammarion.
SELECT nomauteur
FROM auteur
WHERE datedeces is not null
and noauteur not in
(select a.noauteur from auteur a, livre l, ecritpar e, editeur ed
where a.noauteur = e.noauteur
and l.nolivre = e.nolivre
and l.noediteur = ed.noediteur
and nomediteur = 'Flammarion');

• Nom des auteurs membres d’au moins une académie n’ayant encore jamais reçu de prix.
SELECT nomauteur
FROM auteur AS a, membrede AS m
WHERE a.noauteur = m.noauteur
and a.noauteur not in
(select a.noauteur from auteur a, livre l, ecritpar e, obtientprix o
where a.noauteur = e.noauteur
and l.nolivre = e.nolivre
and o.nolivre = l.nolivre);

• Nom des auteurs nés avant la fin de la dernière guerre (1939-1945) avec le nombre de livres écrits.
SELECT nomauteur, count(*) AS [Nombre de livres]
FROM auteur AS a, ecritpar AS e
WHERE a.noauteur = e.noauteur
and datenaiss <= #08-05-1945#
GROUP BY nomauteur;

LOCVEHIC

• Marque et modèle du (ou des) véhicule(s) loué(s) par Marcel Chombier pour une durée d’au moins trois jours.
SELECT marque, modèle
FROM vehicule AS v, contrat AS t, client AS c
WHERE t.noimmat = v.noimmat
and t.noclient = c.noclient
and duréeloc >= 3
and nomcli = "Chombier"
and prencli = "Marcel";

• Nom et prénom des clients qui ont loué un (ou plusieurs) véhicule(s) de marque Citroën ou de marque Peugeot.
SELECT DISTINCT nomcli, prencli
FROM client AS c, contrat AS t, vehicule AS v
WHERE v.noimmat = t.noimmat
and t.noclient = c.noclient
and (marque = "Citroën"
or marque = "Peugeot");

• Donner le nombre de véhicules.
SELECT count(*) AS [Nombre de véhicules]
FROM vehicule;

• Donner la liste des véhicules libres (numéro immatriculation, marque, modèle).
SELECT noimmat, marque, modèle
FROM VEHICULE
WHERE libre = 'oui';

• Donner le nombre de véhicules libres.
SELECT count(*) AS [Nombre de véhicules libres]
FROM vehicule
WHERE libre = 'oui';

Ou bien en utilisant la requête 4

SELECT count(*) AS [Nombre de véhicules libres]
FROM Requête04;

• Donner le nombre de véhicules libres de marque Renault pour chaque catégorie. On listera le nom de la catégorie et le nombre de véhicules libres.
SELECT nomcateg, count(*) AS [Nombre de véhicules libres]
FROM vehicule, categorie
WHERE nocateg = nocategorie
and marque = 'Renault'
and libre = 'oui'
GROUP BY nomcateg;

• Calculer et afficher le prix total dû pour le contrat numéro 5389.
SELECT (prixjour * duréeloc) + (kmparcourus * prixkm) AS [Contrat 5389]
FROM contrat AS t, vehicule AS v, categorie AS c
WHERE nocategorie = nocateg
and v.noimmat = t.noimmat
and nocontrat = 5389;

• Donner le kilométrage moyen d’un véhicule Renault.
SELECT avg(kms) AS [Kilométrage moyen d'un véhicule Renault]
FROM vehicule
WHERE marque = 'Renault';

• Lister les véhicules (numéro d’immatriculation, marque modèle et kilomètres) en ordre décroissant du nombre de kilomètres au compteur.
SELECT noimmat, marque, modèle, kms
FROM VEHICULE
ORDER BY kms DESC;

• Lister les véhicules (numéro d’immatriculation, marque modèle et kilomètres) en ordre croissant du nombre de kilomètres au compteur. NB : on ne s’intéresse ici qu’aux véhicules ayant été loués au moins une fois.
SELECT DISTINCT v.noimmat, marque, modèle, kms
FROM VEHICULE AS v, contrat AS t
WHERE v.noimmat = t.noimmat
ORDER BY kms;

Ou bien

SELECT noimmat, marque, modèle, kms
FROM VEHICULE
WHERE noimmat IN (Select noimmat from contrat)
ORDER BY kms;

• Donner la durée moyenne et le nombre moyen de kilomètres parcourus pour un contrat de 1999 concernant des véhicules Peugeot.
SELECT avg(duréeloc) AS [Durée moyenne], avg(kmparcourus) AS [Kilométrage moyen]
FROM contrat AS t, vehicule AS v
WHERE v.noimmat = t.noimmat
and datec >=#01-01-1999#
and datec <= #31-12-1999#
and marque = "Peugeot";

Ou bien

SELECT avg(duréeloc) AS [Durée moyenne], avg(kmparcourus) AS [Kilométrage moyen]
FROM contrat AS t, vehicule AS v
WHERE v.noimmat = t.noimmat
and datec between #01-01-1999# and #31-12-1999#
and marque = "Peugeot";

ASTÉRIX

• Donner la liste des personnages ayant un chef (nom et nom du chef).
SELECT p.nompers AS [Nom du personnage], c.nompers AS [Nom du chef]
FROM person AS p, person AS c
WHERE p.chef = c.codp;

• Donner le nom des personnages qui donnent des baffes à Cétautomatix.
SELECT d.nompers
FROM person AS d, baffes, person AS p
WHERE d.codp = persdonne
and persprend = p.codp
and p.nompers = "Cétautomatix";

• Donner le nom des personnages qui reçoivent des baffes de Cétautomatix.
SELECT p.nompers
FROM person AS d, baffes, person AS p
WHERE d.codp = persdonne
and persprend = p.codp
and d.nompers = "Cétautomatix";

• Donner le nom des personnages qui ne donnent jamais de baffes.
SELECT nompers
FROM person
WHERE codp not in
(select persdonne from baffes);

• Donner les qualités communes à un guerrier gaulois et à un guerrier breton.
SELECT qqualite
FROM qualites
WHERE qnation = 'Gaulois'
and qemploi = 'Guerrier'
and qqualite in
(SELECT qqualite from qualites
where qnation = 'Breton'
and qemploi = 'Guerrier');

• Pour chaque album, nombre total de baffes données par les Gaulois (titre, nombre de baffes).
SELECT titre, count(*) AS nbbafgau
FROM album AS a, baffes AS b, person
WHERE a.noalbum= b.noalbum
and codp = persdonne
and nation = 'Gaulois'
GROUP BY titre;

• Donner le nombre moyen de baffes données par les Gaulois dans chaque album (cf. requête précédente que l’on a appelée requête06).

Create view requête06(
SELECT titre, count(*) AS nbbafgau
FROM album AS a, baffes AS b, person
WHERE a.noalbum= b.noalbum
and codp = persdonne
and nation = 'Gaulois'
GROUP BY titre);

SELECT avg(nbbafgau) AS NbBG
FROM requête06;

• Donner le titre des albums où le nombre de baffes données par les Gaulois est supérieur ou égal à la moyenne (cf. les deux requêtes précédentes qu’on a respectivement appelées requête06 et requête07).
SELECT titre, count(*) AS nbbafgau
FROM album AS a, baffes AS b, person
WHERE a.noalbum= b.noalbum
and codp = persdonne
and nation = 'Gaulois'
GROUP BY titre
HAVING count(*) >= (select nbbg from requête07);

• Titre des albums où n’apparaît pas Jules César.
SELECT DISTINCT titre
FROM album
WHERE noalbum not in
(select noalbum from apparait, album, person
where anoalbum = noalbum
and acodpers = codp
and nompers = 'Jules César');

• Nombre moyen de personnages apparaissant par album (en deux requêtes, la première s’appelant requête10a).
SELECT anoalbum, count(*) AS nbpers
FROM apparait
GROUP BY anoalbum;

Puis

SELECT avg(nbpers)
FROM Requête10a;

• Numéro moyen des premières pages où apparaît Panoramix.
SELECT avg(prempage) AS [Première page moyenne de Panoramix]
FROM apparait, person
WHERE acodpers = codp
and nompers = 'Panoramix';

• Classer les albums (titre de l’album, année de sortie, nombre de sangliers) par ordre décroissant du nombre de sangliers qui y sont mangés.
SELECT titre, annee, sum(sangmange) AS [Total des sangliers mangés]
FROM album, apparait
WHERE anoalbum = noalbum
GROUP BY titre, annee
ORDER BY sum(sangmange) DESC;

• Lister les nations avec leur nombre de ressortissants (nom de la nation, nombre de personnages).
SELECT nation, count(*) AS [Nombre de ressortissants]
FROM person
GROUP BY nation;

• Lister le nombre de personnages par nations et couleurs de cheveux (nom de la nation, couleur de cheveux, nombre de personnages).
SELECT nation, chev, count(*) AS [Nombre de personnages]
FROM person
GROUP BY nation, chev;

• Lister le nombre de personnages par nations, couleurs de cheveux et taille (nom de la nation, couleur de cheveux, taille, nombre de personnages).
SELECT nation, chev, taille, count(*) AS [Nombre de personnages]
FROM person
GROUP BY nation, chev, taille;

Remarque : Pour les trois dernières requêtes, on peut éviter de tenir compte d’Idéfix (personnage apatride) en y ajoutant la clause where suivante :

Noob Inscrit le : 27 Sep 2007 Messages : 11 Localisation : Dinan

Salut !

en faite c'est pas tout à fait ce que je veux. Je cherche les bases de données. (.sql) ce qu'on copiait sous postgres.

Je sais pas si tu les as ??

Merci quand meme

Bonne vacs.

Cyril

au pire je te les files sur clé usb a la rentré Smile

_________________
La Philosophie du Lamer

Je lame donc je suis.
La réthorique du lamer est plus q'une passion, c'est un art, savoir utilisé les faiblesses de sa victime pour faire de votre phrase, un puit de colère, qui menera votre proie dans les méandres du langage texto pour se défendre difficilement avec son Q.I de protozoaire.