Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TYPE person AS OBJECT(id NUMBER(5), name VARCHAR2(30), telefonnummer VARCHAR2(30)) NOT FINAL;
- CREATE TYPE consultant UNDER person(handynummer VARCHAR2(30), ref_calls refTab_calls);
- CREATE TYPE client UNDER person(account VARCHAR2(30), ref_calls refTab_calls);
- CREATE TABLE tab_consultant OF consultant NESTED TABLE ref_calls STORE AS ntab_con_calls;
- CREATE TABLE tab_client OF client NESTED TABLE ref_calls STORE AS ntab_cli_calls;
- CREATE TYPE refTab_client AS TABLE OF REF client;
- CREATE TYPE refTab_consultant AS TABLE OF REF consultant;
- -------------------------------------------------------------------------------------------------------------
- INSERT INTO tab_consultant VALUES(1, 'Maria', '76543457', '0141543453', refTab_calls());
- INSERT INTO tab_consultant VALUES(2, 'Albert', '456767', '017456788', refTab_calls());
- INSERT INTO tab_consultant VALUES(3, 'Christina', '1209788', '01123432345', refTab_calls());
- INSERT INTO tab_consultant VALUES(4, 'Jessica', '7975461', '019087609', refTab_calls());
- INSERT INTO tab_consultant VALUES(5, 'Max', '554779', '01678345678', refTab_calls());
- INSERT INTO tab_consultant VALUES(6, 'Moritz', '123422', '0112245534', refTab_calls());
- INSERT INTO tab_client VALUES(7, 'Josefina', '76543457', '014567846', refTab_calls());
- INSERT INTO tab_client VALUES(8, 'Denise', '76543457', '0100098765456', refTab_calls());
- INSERT INTO tab_client VALUES(9, 'Vanessa', '76543457', '0114676567', refTab_calls());
- INSERT INTO tab_client VALUES(10, 'Julia', '76543457', '01087658987', refTab_calls());
- INSERT INTO tab_client VALUES(11, 'Dennis', '76543457', '01666676546', refTab_calls());
- INSERT INTO tab_client VALUES(12, 'Laura', '76543457', '01097655678', refTab_calls());
- -------------------------------------------------------------------------------------------------------------
- CREATE OR REPLACE TYPE ref_calls AS TABLE OF REF calls;
- CREATE TYPE calls AS OBJECT(id NUMBER, day VARCHAR2(10), time VARCHAR2(10), duration NUMBER,
- ref_consultant REF consultant, ref_client REF client);
- CREATE TABLE tab_calls OF calls;
- INSERT INTO tab_calls VALUES(1, '10.12.2017', '12:10', 20, (SELECT REF(t) FROM tab_consultant t WHERE t.id=1),
- (SELECT REF(c) FROM tab_client c WHERE c.id=7));
- INSERT INTO tab_calls VALUES(2, '16.10.2017', '13:50', 40, (SELECT REF(t) FROM tab_consultant t WHERE t.id=2),
- (SELECT REF(c) FROM tab_client c WHERE c.id=8));
- INSERT INTO tab_calls VALUES(3, '20.11.2017', '17:25', 10, (SELECT REF(t) FROM tab_consultant t WHERE t.id=3),
- (SELECT REF(c) FROM tab_client c WHERE c.id=9));
- INSERT INTO tab_calls VALUES(4, '22.11.2017', '10:40', 5, (SELECT REF(t) FROM tab_consultant t WHERE t.id=4),
- (SELECT REF(c) FROM tab_client c WHERE c.id=10));
- INSERT INTO tab_calls VALUES(5, '09.06.2017', '11:11', 30, (SELECT REF(t) FROM tab_consultant t WHERE t.id=5),
- (SELECT REF(c) FROM tab_client c WHERE c.id=11));
- INSERT INTO tab_calls VALUES(6, '07.07.2017', '19:30', 25, (SELECT REF(t) FROM tab_consultant t WHERE t.id=6),
- (SELECT REF(c) FROM tab_client c WHERE c.id=12));
- -------------------------------------------------------------------------------------------------------------
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_client c WHERE c.id=7) SELECT REF(t) FROM tab_calls t WHERE t.id=1;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_consultant c WHERE c.id=1) SELECT REF(t) FROM tab_calls t WHERE t.id=1;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_client c WHERE c.id=8) SELECT REF(t) FROM tab_calls t WHERE t.id=2;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_consultant c WHERE c.id=2) SELECT REF(t) FROM tab_calls t WHERE t.id=2;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_client c WHERE c.id=9) SELECT REF(t) FROM tab_calls t WHERE t.id=3;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_consultant c WHERE c.id=3) SELECT REF(t) FROM tab_calls t WHERE t.id=3;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_client c WHERE c.id=10) SELECT REF(t) FROM tab_calls t WHERE t.id=4;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_consultant c WHERE c.id=4) SELECT REF(t) FROM tab_calls t WHERE t.id=4;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_client c WHERE c.id=11) SELECT REF(t) FROM tab_calls t WHERE t.id=5;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_consultant c WHERE c.id=5) SELECT REF(t) FROM tab_calls t WHERE t.id=5;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_client c WHERE c.id=12) SELECT REF(t) FROM tab_calls t WHERE t.id=6;
- INSERT INTO TABLE(SELECT c.ref_calls FROM tab_consultant c WHERE c.id=6) SELECT REF(t) FROM tab_calls t WHERE t.id=6;
- -------------------------------------------------------------------------------------------------------------
- 3. AUFGABE:
- SELECT tab_client.NAME, ref_calls.duration, 'CLIENT' AS TYP FROM tab_client tab_client
- INNER JOIN(SELECT t.*, value(t).client.id AS cli_id, value(t).consultant.id AS con_id FROM tab_call t)
- ref_calls ON calls.cli_id=tab_client.id
- UNION
- SELECT tab_consultant.NAME, calls.duration, 'CONSULTANT' AS TYP FROM tab_consultant tab_consultant
- INNER JOIN(SELECT t.*, value(t).client.id AS cli_id, value(t).consultant.id AS con_id FROM tab_call t)
- calls ON calls.con_id=tab_consultant.id;
- SELECT DEREF(t.client).name AS client, DEREF(t.consultant).name AS consultant, t.duration FROM Tab_Call t;
- SELECT DISTINCT DEREF(t.consultant).name consultant, SUM(t.duration) FROM Tab_Call t GROUP BY DEREF(t.consultant).name;
- SELECT * FROM tab_call;
- SELECT t.*, value(t).client.id as cli_id, value(t).consultant.id AS con_id FROM tab_call t;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
