Skip to content
Permalink
Branch: master
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
207 lines (184 sloc) 5.58 KB
-- create tables
create table collegas (
id number not null constraint collegas_id_pk primary key,
voornaam varchar2(100),
achternaam varchar2(100),
bedrijf varchar2(100),
registratie_datum timestamp default on null SYSTIMESTAMP
)
;
CREATE TABLE "SESSIONS"
( "ID" NUMBER,
"TIMESTAMP" VARCHAR2(30),
"SPREKERS" VARCHAR2(255),
"EMAIL" VARCHAR2(30),
"GESELECTEERD" VARCHAR2(1),
"BEDRIJF" VARCHAR2(255),
"TITEL" VARCHAR2(255),
"BESCHRIJVING" VARCHAR2(4000),
"TAGS" VARCHAR2(255),
"LENGTE" VARCHAR2(255),
"VORM" VARCHAR2(255),
"COACHING" VARCHAR2(30),
"OPMERKINGEN" VARCHAR2(4000),
start_slot number(2)
, room number(1)
, slot_count number (2),
CONSTRAINT "SESSIONS_PK" PRIMARY KEY ("ID")
USING INDEX ENABLE
)
/
alter TABLE "SESSIONS" add
( start_slot number(2)
, room number(1)
, slot_count number (2)
)
/
create table rooms
( id number(1)
, name varchar2(100)
, beschrijving varchar2(4000)
, seq number(2)
)
/
create table slots
( id number(2)
, start_time timestamp
)
CREATE OR REPLACE EDITIONABLE TRIGGER "bi_SESSIONS"
before insert on "SESSIONS"
for each row
begin
if :new."ID" is null then
select "SESSIONS_SEQ".nextval into :new."ID" from sys.dual;
end if;
end;
create table likes (
id number not null constraint likes_id_pk primary key,
collegas_id number
constraint likes_collegas_id_fk
references collegas on delete cascade,
sessions_id number
constraint likes_sessions_id_fk
references sessions on delete cascade,
the_like varchar2(2)
)
;
-- triggers
create or replace trigger collegas_biu
before insert or update
on collegas
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end collegas_biu;
/
create or replace trigger sessions_biu
before insert or update
on sessions
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end sessions_biu;
/
create or replace trigger likes_biu
before insert or update
on likes
for each row
begin
if :new.id is null then
:new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
end if;
end likes_biu;
/
-- indexes
create index likes_i1 on likes (collegas_id);
create index likes_i2 on likes (sessions_id);
-- load data
-- Generated by Quick SQL Sunday February 03, 2019 16:06:12
/*
collegas
voornaam vc100
achternaam vc100
bedrijf vc100
registratie_datum ts /default systimestamp
sessions
id /pk
titel vc200
sprekers vc200
beschrijving vc4000
likes
collegas id
like vc2
sessions id
# settings = { PK: "TRIG", language: "EN", APEX: true }
*/
create or replace
view v_sessie_likes
as
select l.id
, l.the_like
, case l.the_like when 'Y' then 'fa fa-thumbs-up fa-3x'
else 'fa fa-thumbs-o-up fa-2x' end like_icon
, s.titel titel
, s.beschrijving
, s.vorm
, s.lengte
, s.tags
, s.sprekers
, s.bedrijf
from likes l join sessions s on l.sessions_id = s.id
where s.GESELECTEERD is null
and l.collegas_id = APEX_UTIL.GET_SESSION_STATE ('P4_COLLEGA_ID')
order by substr(l.id,25,32)
/
create or replace
view like_summary
as
select count(l.id) like_count
, s.titel titel
, s.beschrijving
, s.sprekers
from likes l join sessions s on l.sessions_id = s.id
join collegas c on l.collegas_id = c.id
where s.GESELECTEERD is null
and l.the_like = 'Y'
group
by titel, beschrijving, sprekers
order by like_count desc
create or replace
view sessie_rooster
as
select s.start_slot, starttime
, r1_sessie_id r1_id
, r2_sessie_id r2_id
, r3_sessie_id r3_id
, r4_sessie_id r4_id
, r5_sessie_id r5_id
, (select titel||'- '||sprekers from sessions where id = r1_sessie_id) r1_sessie
, (select slot_count from sessions where id = r1_sessie_id) r1_count
, (select beschrijving from sessions where id = r1_sessie_id ) r1_abstract
, (select titel||'- '||sprekers from sessions where id = r2_sessie_id) r2_sessie
, (select slot_count from sessions where id = r2_sessie_id) r2_count
, (select beschrijving from sessions where id = r2_sessie_id ) r2_abstract
, (select titel||'- '||sprekers from sessions where id = r3_sessie_id) r3_sessie
, (select slot_count from sessions where id = r3_sessie_id) r3_count
, (select beschrijving from sessions where id = r3_sessie_id ) r3_abstract
, (select titel||'- '||sprekers from sessions where id = r4_sessie_id) r4_sessie
, (select slot_count from sessions where id = r4_sessie_id) r4_count
, (select beschrijving from sessions where id = r4_sessie_id ) r4_abstract
, (select titel||'- '||sprekers from sessions where id = r5_sessie_id) r5_sessie
, (select slot_count from sessions where id = r5_sessie_id) r5_count
, (select beschrijving from sessions where id = r5_sessie_id ) r5_abstract
from (
select * from (select s.id sessie_id, start_slot, r.seq room , to_char(sl.start_time,'HH24:MI') starttime from sessions s join rooms r on (s.room=r.id) join slots sl on (s.start_slot = sl.id ))
pivot (
max( sessie_id) sessie_id
for room in ( 1 as r1,2 as r2,3 as r3,4 as r4,5 as r5 )
)
) s
order by start_slot
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.