ICA0016 Oracle programmeerimiskeeled SQL ja PLSQL 8. ülesanne (0)

8. Ülesanne    1. Delegeerige endale scott-i rollis olles tabelite lepikult.yliopilased, lepikult.koduylesanded ja 
lepikult.oppeained lugemisõigused. Logige andmebaasi enda kasutajatunnuse abil.   GRANT SELECT ON lepikult.yliopilased to MinuKasutajaNimi;   
 GRANT SELECT ON lepikult.koduylesanded to MinuKasutajaNimi;   
 GRANT SELECT ON lepikult.oppeained to MinuKasutajaNimi;  2. Moodustage tabelite lepikult.yliopilased, lepikult.koduylesanded ja lepikult.oppeained abil kursuse 
„Oracle….” kohta tabel, kus on andmed kodutööde kohta – tudengite nimed, ID-koodid ja punktid kolme 
kodutöö eest (kui ühe kodutöö kohta on samalt tudengilt mitu saadetist, läheb kirja parim tulemus). Eraldi 
veerus on kolme töö eest saadud punktide summa. Arvestuse tingimuseks on, et ülesannete punktide summa 
peab olema vähemalt 14. Ülesande lahendamisel kasutage nii DDL kui ka DML-lauseid.  // Loo tabel  CREATE TABLE Oracle_tulemused  (opilase_id CHAR(8), Eesnimi VARCHAR2(20), Perenimi VARCHAR2(20),  yl_1 NUMBER(4,1), yl_2 NUMBER(4,1), yl_3 NUMBER(4,1),  Kokku NUMBER(4,1), Arvestus CHAR(2));    // Lisa õpilased tabelisse  INSERT INTO Oracle_tulemused (opilase_id, Eesnimi, Perenimi)  (SELECT y.id, y.eesnimi, y.perenimi FROM lepikult.yliopilased y);    // Lisa õpilaste ülesannete tulemused tabelisse  UPDATE Oracle_tulemused o SET o.yl_1 =  (SELECT max(k.punktid) FROM lepikult.koduylesanded k WHERE k.yliopilase_id = o.opilase_id AND k.too_nr = 1 
AND k.kursusekood = (SELECT kood FROM lepikult.oppeained WHERE nimetus like 'Oracle%'))  WHERE EXISTS (SELECT k.punktid FROM lepikult.koduylesanded k WHERE k.yliopilase_id = o.opilase_id AND 
k.too_nr = 1 AND k.kursusekood = (SELECT kood FROM lepikult.oppeained WHERE nimetus like 'Oracle%'));    UPDATE Oracle_tulemused o SET o.yl_2 =  (SELECT max(k.punktid) FROM lepikult.koduylesanded k WHERE k.yliopilase_id = o.opilase_id AND k.too_nr = 2 
AND k.kursusekood = (SELECT kood FROM lepikult.oppeained WHERE nimetus like 'Oracle%'))  WHERE EXISTS (SELECT k.punktid FROM lepikult.koduylesanded k WHERE k.yliopilase_id = o.opilase_id AND 
k.too_nr = 2 AND k.kursusekood = (SELECT kood FROM lepikult.oppeained WHERE nimetus like 'Oracle%')); 


UPDATE Oracle_tulemused o SET o.yl_3 =  (SELECT max(k.punktid) FROM lepikult.koduylesanded k WHERE k.yliopilase_id = o.opilase_id AND k.too_nr = 3 
AND k.kursusekood = (SELECT kood FROM lepikult.oppeained WHERE nimetus like 'Oracle%'))  WHERE EXISTS (SELECT k.punktid FROM lepikult.koduylesanded k WHERE k.yliopilase_id = o.opilase_id AND 
k.too_nr = 3 AND k.kursusekood = (SELECT kood FROM lepikult.oppeained WHERE nimetus like 'Oracle%'));    // Ülesannete punktide summad  UPDATE Oracle_tulemused o SET o.Kokku =  (SELECT sum(nvl(o.yl_1,0) + nvl(o.yl_2,0) + nvl(o.yl_3,0)) FROM Oracle_tulemused ot WHERE o.opilase_id = 
ot.opilase_id);    // Arvestatud / Mittearvestatud  UPDATE Oracle_tulemused o SET o.Arvestus = (SELECT (CASE WHEN o.Kokku >= 14 THEN 'A' ELSE 'MA' END) FROM 
Oracle_tulemused ot WHERE o.opilase_id = ot.opilase_id);    SELECT * FROM Oracle_tulemused;      3. Moodustage kolm tabelit, mis kajastavad raamatukogu andmeid:   1) Registreerunud lugejate andmete tabel (nimed, isikukoodid, aadress, telefon, e-mail);  CREATE TABLE lugejad(isikukood NUMBER(11), nimi VARCHAR2(30),   aadress VARCHAR2(100), telefon VARCHAR2(20), email VARCHAR2(30),  CONSTRAINT isikukood_pk PRIMARY KEY (isikukood));    2) Raamatukogus olevate raamatute tabel (shiffer, autor(id), pealkiri, eksemplari hind, eksemplaride arv);  CREATE TABLE raamatud(shiffer VARCHAR2(50), autorid VARCHAR2(100),  pealkiri VARCHAR2(100), hind NUMBER(4,2), kogus NUMBER(3,0),  CONSTRAINT raamatud_pk PRIMARY KEY (shiffer));     


3) Laenutuste tabel (laenutaja isikukood, raamatu shiffer, laenutamise kuupäev, tagastamise kuupäev).  CREATE TABLE laenutused(  laenutaja_isikukood NUMBER(11), raamatu_shiffer VARCHAR2(50),  laenutatud DATE, tagastatud DATE);    ALTER TABLE laenutused ADD CONSTRAINT lugeja_fk FOREIGN KEY (laenutaja_isikukood)   REFERENCES lugejad(isikukood);  ALTER TABLE laenutused ADD CONSTRAINT raamat_fk FOREIGN KEY (raamatu_shiffer)   REFERENCES raamatud(shiffer);    4. Koostage päring, mis leiab „võlglased”. Võlaks loeme laenutatud, kuid tagastamata raamatu, kui 
laenutamisest on möödunud üle 2 nädala. Päring peab näitama võlglaste nimed ja tagastamata 
raamatud.  // Dummy data  INSERT INTO lugejad VALUES ('12345678901', 'Chuck Norris', 'Iirise 25-6', '+372123223', '[email protected]');  INSERT INTO lugejad VALUES ('23456789012', 'Mihkel Jordaan', 'Kaseke 15-10', '+372223123', '[email protected]');  INSERT INTO raamatud VALUES ('159753', 'Uuno', 'Lasteraamat', '15', '20');  INSERT INTO raamatud VALUES ('112233', 'Trump', 'Trumpi autobiograafia', '25', '10');  INSERT INTO raamatud VALUES ('556677', 'Taavi', 'Teine Lasteraamat', '10', '10');  INSERT INTO laenutused VALUES ('12345678901', '159753', sysdate-20, sysdate-2);  INSERT INTO laenutused VALUES ('23456789012', '112233', sysdate-20, sysdate-2);  INSERT INTO laenutused VALUES ('23456789012', '556677', sysdate-20, NULL);    SELECT l.nimi AS "Võlglane", r.pealkiri AS "Raamat", v.laenutatud AS "Laenutatud kuupäeval"   FROM lugejad l, raamatud r, laenutused v  WHERE l.isikukood = v.laenutaja_isikukood AND r.shiffer = v.raamatu_shiffer   AND v.laenutatud < sysdate-14 AND v.tagastatud IS NULL;