SELECT | tvo_nom||' '||voi_nom AS rue, voi_cp||' '||vil_nom AS ville | |
FROM | ville, voirie, typevoirie | |
WHERE | vil_cp=voi_cp | |
AND | voi_type=tvo_num | |
ORDER | BY vil_nom ; |
SELECT | etu_nom,etu_prenom,etu_adressenum,tvo_nom,voi_nom,voi_cp,vil_nom | |
FROM | etudiant, voirie, ville, typevoirie | |
WHERE | etu_voirie=voi_num | |
AND | voi_type=tvo_num | |
AND | voi_cp=vil_cp | |
ORDER BY | 1,2 ; |
SELECT | etu_nom,etu_prenom | |
FROM | etudiant, voirie, ville | |
WHERE | etu_voirie=voi_num | |
AND | voi_cp=vil_cp | |
AND | LOWER(vil_nom)='lille' | |
ORDER BY | etu_nom,etu_prenom ; |
SELECT | sea_jour||'/'||sea_mois||'/'||sea_annee AS date, | |
sea_debut_h||'h'||sea_debut_m AS horaire, | ||
sal_nom AS salle, UPPER(mon_nom)||' '||mon_prenom AS moniteur | ||
FROM | seance,salle,moniteur | |
WHERE | sal_num=sea_salle | |
AND | mon_num=sea_moniteur | |
ORDER BY | sea_annee,sea_mois,sea_jour,sea_debut_h,sea_debut_m ; |
SELECT | e.etu_nom,e.etu_prenom,e.etu_carte | |
FROM | etudiant e,etudiant a,inscription ie,inscription ia | |
WHERE | a.etu_nom='Age' | |
AND | a.etu_prenom='Karl' | |
AND | a.etu_carte=ia.ins_etudiant | |
AND | e.etu_carte=ie.ins_etudiant | |
AND | ie.ins_seance=ia.ins_seance | |
ORDER BY | 1,2,3 ; |
SELECT | etu_carte,etu_nom,etu_prenom | |
FROM | etudiant | |
WHERE | etu_carte | |
NOT IN (SELECT ins_etudiant FROM inscription) | ||
ORDER BY | etu_nom,etu_prenom ; |
SELECT TO_CHAR(NOW(),'DD/MM/YYYY'), TO_CHAR(CURRENT_DATE,'DD/MM/YYYY') ; |
SELECT TO_CHAR(NOW()::DATE - 1,'DD/MM/YYYY'), TO_CHAR(CURRENT_DATE - 1,'DD/MM/YYYY') ; |
SELECT CURRENT_DATE - TO_CHAR(CURRENT_DATE,'D')::INTEGER + 1 ; |
SELECT | CASE | |||
WHEN TO_CHAR(CURRENT_DATE,'D')='1' THEN 'dimanche' | ||||
WHEN TO_CHAR(CURRENT_DATE,'D')='2' THEN 'lundi' | ||||
WHEN TO_CHAR(CURRENT_DATE,'D')='3' THEN 'mardi' | ||||
WHEN TO_CHAR(CURRENT_DATE,'D')='4' THEN 'mercredi' | ||||
WHEN TO_CHAR(CURRENT_DATE,'D')='5' THEN 'jeudi' | ||||
WHEN TO_CHAR(CURRENT_DATE,'D')='6' THEN 'vendredi' | ||||
ELSE 'samedi' | ||||
END | AS "jour d'aujourd'hui" ; |
SELECT TO_CHAR(NOW(),'HH24:MI:SS') ; |
SELECT | DISTINCT sal_nom | |
FROM | salle,seance | |
WHERE | sal_num=sea_salle | |
AND | sea_mois=TO_CHAR(NOW(),'MM')::INTEGER | |
AND | sea_debut_h<12 ; |
SELECT | DISTINCT mon_nom,mon_prenom | |
FROM | moniteur,seance,inscription,etudiant,voirie,ville | |
WHERE | mon_num=sea_moniteur | |
AND | sea_num=ins_seance | |
AND | ins_etudiant=etu_carte | |
AND | etu_voirie=voi_num | |
AND | voi_cp=vil_cp | |
AND | vil_nom='Lille' ; |
SELECT | ufr_nom AS ufr, COUNT(*) AS nbre | |
FROM | etudiant, ufr | |
WHERE | etu_ufr=ufr_num | |
GROUP BY | ufr_nom ; |
SELECT | ufr_nom AS ufr, COUNT(*) AS nbre | |
FROM | etudiant, ufr, inscription | |
WHERE | etu_ufr=ufr_num | |
AND | etu_carte=ins_etudiant | |
GROUP BY | ufr_nom ; |
SELECT | COUNT(*) AS "nombre de salles",SUM(sal_taille) AS "nombre de places" | |
FROM | salle ; |
SELECT | vil_nom,count(*) | |
FROM | etudiant, voirie, ville | |
WHERE | vil_cp=voi_cp | |
AND | voi_num=etu_voirie | |
GROUP BY | vil_nom | |
ORDER BY | vil_nom ; |
SELECT | mon_nom,mon_prenom,COUNT(*) | |
FROM | moniteur, seance | |
WHERE | mon_num=sea_moniteur | |
GROUP BY | mon_num,mon_nom,mon_prenom | |
ORDER BY | COUNT(*) DESC | |
LIMIT | 3 ; |
SELECT | sal_nom,COUNT(*) | |
FROM | salle, seance | |
WHERE | sal_num=sea_salle | |
GROUP BY | sal_nom | |
ORDER BY | COUNT(*),sal_nom DESC | |
LIMIT | 5 ; |
SELECT | etu_nom,COUNT(*) | |
FROM | etudiant | |
GROUP BY | etu_nom | |
ORDER BY | COUNT(*) DESC,etu_nom | |
LIMIT | 3 ; |
SELECT | etu_nom,etu_prenom,etu_carte,COUNT(*) | |
FROM | etudiant, inscription | |
WHERE | etu_carte=ins_etudiant | |
GROUP BY | etu_nom,etu_prenom,etu_carte | |
HAVING | COUNT(*)>1 | |
ORDER BY | etu_nom,etu_prenom,etu_carte ; |
SELECT | etu_nom,etu_prenom,COUNT(*) | |
FROM | etudiant | |
GROUP BY | etu_nom,etu_prenom | |
HAVING | COUNT(*)>1 | |
ORDER BY | etu_nom,etu_prenom ; |
SELECT | sea_jour||'/'||sea_mois||'/'||sea_annee AS date, | |
sea_debut_h||'h'||sea_debut_m AS horaire, | ||
sal_nom AS salle, | ||
COUNT(*) AS nombre | ||
FROM | seance,salle,inscription | |
WHERE | sal_num=sea_salle | |
AND | ins_seance=sea_num | |
GROUP BY | sea_jour||'/'||sea_mois||'/'||sea_annee, | |
sea_debut_h||'h'||sea_debut_m, | ||
sal_nom | ||
ORDER BY | COUNT(*) DESC | |
LIMIT | 5 ; |
SELECT | vil_nom,AVG(nbre) | |||||
FROM | ( | |||||
SELECT | vil_nom,voi_num,COUNT(*) AS nbre | |||||
FROM | ville,voirie,etudiant | |||||
WHERE | etu_voirie=voi_num | |||||
AND | vil_cp=voi_cp | |||||
GROUP BY | vil_nom,voi_num | |||||
) XX | ||||||
GROUP BY | vil_nom | |||||
ORDER BY | AVG(nbre) DESC,vil_nom ; |
SELECT | vil_nom | |||||
FROM | ville,voirie,etudiant | |||||
WHERE | etu_voirie=voi_num | |||||
AND | vil_cp=voi_cp | |||||
GROUP | BY vil_nom | |||||
HAVING | COUNT(*) = (SELECT MAX(n) FROM | (SELECT | COUNT(*) AS n | |||
FROM | ville,voirie,etudiant | |||||
WHERE | etu_voirie=voi_num | |||||
AND | vil_cp=voi_cp | |||||
GROUP BY | vil_nom) XX | |||||
) ; |
SELECT | AVG(nbre) | |||
FROM | ( | |||
SELECT | COUNT(*) AS nbre | |||
FROM | inscription | |||
GROUP BY | ins_seance) x ; |
( | ||||||||
SELECT | (sea_mois | |||||||
||'/'||sea_jour | ||||||||
||'/'||sea_annee | ||||||||
||' '||sea_debut_h | ||||||||
||':'||sea_debut_m | ||||||||
)::TIMESTAMP AS horaire, | ||||||||
sal_nom AS salle, | ||||||||
COUNT(*) AS nombre | ||||||||
FROM | salle, | |||||||
seance, | ||||||||
inscription, | ||||||||
etudiant, | ||||||||
niveau | ||||||||
WHERE | sea_num=ins_seance | |||||||
AND | sea_salle=sal_num | |||||||
AND | ins_etudiant=etu_carte | |||||||
AND | etu_niveau=niv_num | |||||||
AND | niv_nom='Doctorat' | |||||||
GROUP BY | sea_num, | |||||||
sea_jour, | ||||||||
sea_mois, | ||||||||
sea_annee, | ||||||||
sea_debut_h, | ||||||||
sea_debut_m, | ||||||||
sal_nom | ||||||||
) | ||||||||
UNION | ||||||||
( | ||||||||
SELECT | (sea_mois | |||||||
||'/'||sea_jour | ||||||||
||'/'||sea_annee | ||||||||
||' '||sea_debut_h | ||||||||
||':'||sea_debut_m | ||||||||
)::TIMESTAMP AS horaire, | ||||||||
sal_nom AS salle, | ||||||||
0 AS nombre | ||||||||
FROM | salle, | |||||||
seance | ||||||||
WHERE | sea_salle=sal_num | |||||||
AND | sea_num NOT IN | ( | ||||||
SELECT | ins_seance | |||||||
FROM | inscription,etudiant,niveau | |||||||
WHERE | ins_etudiant=etu_carte | |||||||
AND | etu_niveau=niv_num | |||||||
AND | niv_nom='Doctorat' | |||||||
) | ||||||||
) | ||||||||
ORDER BY | 1 ; |