| 
Soit les tables suivantes : COURS K cours char(10) nom du cours K module char(10) module de cours texte char(50) texte du cours sujet char(10) sujet motcl1 char(10) 1er mot-clé associé à ce cours motcl2 char(10) 2eme mot-clé associé à ce cours ... motcl5 char(10) 5eme mot-clé associé à ce cours datref dec(7, 0) date de création ou de dernière modif il s'agit du fichier des cours disponibles chaque cours est enregistré sous un module de cours MODULES K module char(10) modtxt char(50) datcreation date |
|
CERTAINS cours sont de type cursus (la présence dans ce fichier l'indique) CURSUS K module char(10) but char(100) but du cursus duree dec(4,2) temps estimé pour suivre ce cursus et enfin à chaque cours sont rattachés entre 0 et x exemples associés EXEMPLES K cours K module K noex dec(3,0) n° de l'exemple fichier char(10) fichier source biblio char(10) " membre char(10) membre source la relation cours <-> module est (n,1) module <-> cursus est (0,1) cours <-> exemples est (0,n) |
|
liste de tous les cours
SELECT * FROM COURS
liste des cours enregistrés sous le sujet SQL
SELECT * FROM COURS WHERE SUJET = 'SQL'
liste des cours ayant comme sujet SQL ou FICHIER ou QUERY
SELECT * FROM COURS WHERE SUJET IN ('SQL', 'FICHIER', 'QUERY')
ou
SELECT * FROM COURS WHERE ucase(SUJET) IN ('SQL', 'FICHIER', 'QUERY')
|
|
liste des cours modifiés cette année Select * from cours where year( date(substr(digits(datref), 6, 2) concat '/' concat substr(digits(datref), 4, 2) concat '/' concat substr(digits(datref), 2, 2)) ) =year(now()) liste des cours modifiés lors des douze mois précédents Select * from cours where days(current date) - days( date(substr(digits(datref), 6, 2) concat '/' concat substr(digits(datref), 4, 2) concat '/' concat substr(digits(datref), 2, 2)) < 366 |
|
liste des modules de type cursus (le nom, le texte, le but) SELECT module, modtxt, but from MODULES M, CURSUS C WHERE m.module = c.module ou bien SELECT module, modtxt, but from MODULE M JOIN CURSUS C ON m.module = c.module liste de tous les modules avec, pour les cursus, la durée. SELECT module, modtxt, duree from MODULE M LEFT OUTER JOIN CURSUS C ON m.module = c.module |
|
liste de tous les modules ayant au moins un cours sous le sujet SQL. (avec toujours la durée pour les cursus). SELECT module, modtxt, duree FROM MODULE M LEFT OUTER JOIN CURSUS C ON m.module = c.module WHERE EXISTS (SELECT * FROM COURS WHERE module = m.module and sujet = 'SQL') MODULE MODTXT DUREE £CURSUS02 Base de données integrée et requêtes 15,02 £CURSUS220 OS/400 - V2R20 9,02 £CURSUS3PR Préparation à la V3R10 (études/système) 9,52 £CURSUS3PX Préparation V3R10 (orienté exploitation) 5,42 £CURSUS310 OS/400 - V3R10 19,52 COURS Tous les cours AF400 - OUTILS Outils AF400 (objets et sources) - PERF Perfectionnement et suivi des nouveautés - V2R20 Nouveautés V2R20 de l'OS/400 - V3R10 Nouveautés V3R10 de l'OS/400 - |
|
refaite le même exercice en passant par une vue. CREATE VIEW v1 AS SELECT module, modtxt, duree FROM MODULE M LEFT OUTER JOIN CURSUS C ON m.module = c.module Select * from v1 WHERE EXISTS (SELECT * FROM COURS WHERE module = m.module and sujet = 'SQL') quel est le nombre de cours par module SELECT module, COUNT(*) from cours group by module |
|
quel est le nombre de cours et la date de dernière révision par module SELECT module, COUNT(*), max(datref) from cours group by module quel est le nombre de cours et la date de dernière révision par module (uniquement les modules ayant plus de dix cours) SELECT module, COUNT(*), max(datref) from cours group by module having count(*) > 10 |
|
quel est le nombre de cours et la date de dernière révision par module (uniquement les modules ayant un cours parlant de SQL, pour être certain on considère comme traitant de SQL un cours ayant "SQL" dans son texte) MODULE COUNT ( * ) MAX ( DATREF ) £CURSUSTCP 13 16/07/98 ................................ £CURSUS02 21 16/09/98 : SELECT module, COUNT(*), : £CURSUS220 18 16/06/95 : max(datref) : £CURSUS3PR 18 16/01/95 : : £CURSUS3PX 9 16/01/95 : FROM COURS c : £CURSUS310 58 24/06/98 : : £CURSUS320 19 13/11/97 : WHERE : £CURSUS370 24 12/02/98 : : COURS 352 16/09/98 : texte like '%SQL%' : OUTILS 59 30/10/97 : : PERF 269 16/09/98 : : V2R20 18 09/06/95 : GROUP BY module : V3R10 65 31/03/98 : : V3R20 18 15/07/97 : : V3R70 16 16/07/98 :..............................: |
|
créer une vue transformant datref en date create view formation0/coursv1 as SELECT COURS, MODULE, TEXTE, SUJET, MOTCL1, DATE( substr(digits(datref), 6, 2) concat '/' concat substr(digits(datref), 4, 2) concat '/' concat substr(digits(datref), 2, 2) ) as datref FROM cours donner la liste des cours modifiés aujourd'hui. select * from cours where datref = current date donner la liste des MODULES (nom, texte) dont un cours a été modifié aujourd'hui select * from modules M where exists (select * from cours where module = m.module and datref = current date) |
|
donner la liste des MODULES (nom, texte) dont un cours a été modifié ce mois ci select * from modules M where exists (select * from cours where module = m.module and month(datref) = month(current date) and year(datref) = year(current date) ) donner la liste des cours modifiés cette année en ajoutant un libellé SELECT COURS, MODULE, TEXTE, DATREF, case when week(datref) = week(current date) then 'modifié cette semaine' when month(datref) = month(current date) then 'modifié ce mois-ci' when quarter(datref) = quarter(current date) then 'modifié ce trimestre' else 'modifié cette année' end from coursv1 where year(current date) = year (datref) |
|
et pour finir, donner le nombre d'exemples par module. (0 s'il n'y en a pas) SELECT c.module, count(*) FROM cours c join exemples e on c.cours = e.cours and c.module = e.module GROUP BY c.module UNION SELECT module , 0 FROM cours c2 WHERE not exists (SELECT * FROM exemples where module = c2.module) |
|
MODULE COUNT ( * ) £CURSUSRP4 13 £CURSUSTCP 12 £CURSUS00 0 £CURSUS01 5 £CURSUS02 13 £CURSUS03 28 £CURSUS04 15 £CURSUS05 6 £CURSUS06 25 £CURSUS220 42 £CURSUS230 66 £CURSUS3PR 15 £CURSUS3PX 10 £CURSUS310 47 £CURSUS320 17 £CURSUS370 24 |
|
Modification de données : a/ passer la transaction en mode UR (SET TRANSACTION) SET TRANSACTION ISOLATION LEVEL UR b/ mettez la date du jour dans les cours portant le nom '£SQL400' update cours set datref = current date where cours = '£SQL400' c/ demandez la liste des cours modifiés aujourd'hui select * from cours where datref = current date d/ invalidez la transaction ROLLBACK e/ demandez la liste des cours modifiés aujourd'hui (aucun cours) |
|
a/ passer la transaction en mode UR (SET TRANSACTION) b/ Supprimez le module '£CURSUS00' dans le fichier modules DELETE FROM modules where module = '£CURSUS00' dans le fichier cours DELETE FROM cours where module = '£CURSUS00' dans le fichier exemples DELETE FROM exemples where module = '£CURSUS00' validez COMMIT |
|
passer la transaction en mode validation immédiate
SET TRANSACTION ISOLATION LEVEL NC
Insérez un cours du module '£CURSUS00' dans le fichier des cours
INSERT INTO TPSQL/COURS VALUES('essai', '£CURSUS00', 'texte',
'SQL', ' ', ' ', ' ', ' ', ' ', current date)
Insérez les modules manquant dans le fichiers des modules
(le texte sera 'inséré automatiquement')
INSERT INTO TPSQL/MODULES (MODULE, MODTXT) SELECT DISTINCT module,
'inséré automatiquement' FROM tpsql/cours c WHERE not exists
(SELECT * FROM modules WHERE module = c.module)
|
|
a/ passer la transaction en mode ALL b/ mettez comme date de création dans le fichier des modules , la date du cours le plus ancien pour ce module. update modules M set datcreation = (select min(datref) from coursv1 where module= m.module) c/ invalidez refaite la même chose (b/) mais uniquement si la date de création du module est inférieure à la date que vous allez placer. (pour vérifier vous attribuez au module '£CURSUSTCP' la date du jour) update modules set datcreation = current date where module = '£CURSUSTCP' update modules M set datcreation = (select min(datref) from coursv1 where module= m.module) where datcreation < (select min(datref) from coursv1 where module= m.module) |
