ORACLE: SQL JA PLSQL 12 (0)

SQL> SPOOL
currently spooling to ülesanne_12.lst SQL> CREATE TABLE koduylesanded AS ( SELECT * FROM scott .koduylesanded);
Table created.
SQL> COLUMN yliopilase_id FORMAT 999
SQL> COLUMN kursusekood FORMAT A8
SQL> COLUMN too_nr FORMAT 999
SQL> COLUMN yliopilase_id FORMAT 999
SQL> COLUMN punktid FORMAT 999
SQL> COLUMN failinimi FORMAT A8
SQL>
SQL> SELECT * FROM koduylesanded;
YLIOPILASE_ID KURSUSEK KUUPAEV TOO_NR FAILINIM PUNKTID
------------------------ -------- --------------- ------ -------- -------
10020014 I319 01-JUN-03 1 t7.txt 10
10020015 I319 11-JUL-03 1 t8.txt 10
10020011 I319 11-JUN-04 2 t2.txt 7
10020013 I319 11-JUN-04 3 t3.txt 8
10020011 Iad307 11-JUN-03 1 t1.txt 5
10020011 Id310 12-JUN-03 1 t2.txt 7
10020011 ID218 17-JUN-03 1 t3.txt 8
10020014 Iad307 01-JUN-03 1 t7.txt 10
10020015 Iad307 11-JUL-03 1 t8.txt 10
10020011 Iad307 21-JUN-03 2 t4.txt 7
10020015 Id309 22-JUN-03 3 T5.txt 6
YLIOPILASE_ID KURSUSEK KUUPAEV TOO_NR FAILINIM PUNKTID
------------------------ -------- --------------- ------ -------- -------
10020013 ID218 23-JUN-03 1 t6.txt 10
10020014 Iad307 11-AUG-03 2 t9 3
10020011 I319 25-MAY-03 1 01.txt 5
10020011 I319 12-MAR-03 3 01.txt 6
15 rows selected.
SQL> SELECT k.yliopilase_id, k.kursusekood, k.too_nr, COUNT (*) FROM koduylesanded k 2 GROUP BY yliopilase_id, kursusekood, too_nr 3 HAVING COUNT(*) > 1;
no rows selected
SQL> INSERT INTO koduylesanded VALUES (10020014, 'I319', '11-JUN-03', 1, 'dup1.txt', 9);
1 row created.
SQL> INSERT INTO koduylesanded VALUES (10020015, 'Iad307', '06-JUN-03', 1, 'dup2.txt', 9);
1 row created.
SQL> INSERT INTO koduylesanded VALUES (10020011, 'Iad307', '01-JUN-03', 3, 'dup3.txt', 8);
1 row created.
SQL> INSERT INTO koduylesanded VALUES (10020013, 'ID218', '03-JUN-03', 1, 'dup4.txt', 8);
1 row created.
SQL> SELECT k.yliopilase_id, k.kursusekood, k.too_nr, COUNT(*) FROM koduylesanded k 2 GROUP BY yliopilase_id, kursusekood, too_nr 3 HAVING COUNT(*) > 1;
YLIOPILASE_ID KURSUSEK TOO_NR COUNT(*)
------------------------ -------- ------ ----------
10020015 Iad307 1 2
10020013 ID218 1 2
10020014 I319 1 2
SQL> VARIABLE parim NUMBER; SQL> DECLARE CURSOR korduvad IS 2 SELECT k.yliopilase_id, k.kursusekood, k.too_nr, COUNT(*) FROM koduylesanded k 3 GROUP BY yliopilase_id, kursusekood, too_nr 4 HAVING COUNT(*) > 1; 5 BEGIN 6 FOR korduv IN korduvad LOOP 7 SELECT max(punktid) INTO:parim FROM koduylesanded WHERE yliopilase_id = korduv.yliopilase_id AND kursusekood = korduv.kursusekood AND too_nr = korduv.too_nr; 8 DELETE FROM koduylesanded WHERE yliopilase_id = korduv.yliopilase_id AND kursusekood = korduv.kursusekood AND too_nr = korduv.too_nr AND punktid PL/SQL procedure successfully completed.
SQL> SELECT k.yliopilase_id, k.kursusekood, k.too_nr, COUNT(*) FROM koduylesanded k 2 GROUP BY yliopilase_id, kursusekood, too_nr 3 HAVING COUNT(*) > 1;
no rows selected
SQL> SELECT * FROM koduylesanded;
YLIOPILASE_ID KURSUSEK KUUPAEV TOO_NR FAILINIM PUNKTID
------------------------ -------- --------------- ------ -------- -------
10020014 I319 01-JUN-03 1 t7.txt 10
10020015 I319 11-JUL-03 1 t8.txt 10
10020011 I319 11-JUN-04 2 t2.txt 7
10020013 I319 11-JUN-04 3 t3.txt 8
10020011 Iad307 11-JUN-03 1 t1.txt 5
10020011 Id310 12-JUN-03 1 t2.txt 7
10020011 ID218 17-JUN-03 1 t3.txt 8
10020014 Iad307 01-JUN-03 1 t7.txt 10
10020015 Iad307 11-JUL-03 1 t8.txt 10
10020011 Iad307 21-JUN-03 2 t4.txt 7
10020015 Id309 22-JUN-03 3 T5.txt 6
10020013 ID218 23-JUN-03 1 t6.txt 10
10020014 Iad307 11-AUG-03 2 t9 3
10020011 I319 25-MAY-03 1 01.txt 5
10020011 I319 12-MAR-03 3 01.txt 6
10020011 Iad307 01-JUN-03 3 dup3.txt 8
16 rows selected.
SQL> SET serveroutput ON SQL> DECLARE CURSOR esitamata (id scott. yliopilased .id%type) IS 2 SELECT nimetus FROM scott. oppeained WHERE kood NOT IN 3 (SELECT kursusekood FROM scott.koduylesanded WHERE yliopilase_id = id); 4 BEGIN FOR y IN (SELECT * FROM scott.yliopilased) LOOP 5 dbms_output.put_line('→ '||y.eesnimi||' '||y. perenimi ||' kodutoo puudub:'); 6 FOR aine IN esitamata (y.id) LOOP 7 dbms_output.put_line(' • '||aine.nimetus); 8 END LOOP; 9 END LOOP; 10 END; 11 /
→ Katrin Kask kodutoo puudub: • Digitaalne heli- ja pildit ””tlus
→ Tarmo Kuusk kodutoo puudub: • Riistvaral„ hedane programmeerimine • Digitaalne heli- ja pildit””tlus • Digitaalkommunikatsioon
→ Kairi M„nd kodutoo puudub: • Riistvaral„hedane programmeerimine • Digitaalne heli- ja pildit””tlus → Salme Saar kodutoo puudub: • Operatsioonissteemide tehnoloogia • Riistvaral„hedane programmeerimine
→ Liina Vaher kodutoo puudub: • Operatsioonissteemide tehnoloogia • Digitaalne heli- ja pildit””tlus • Digitaalkommunikatsioon
PL/SQL procedure successfully completed.
SQL> SPOOL OFF