drop table laererKurs;
drop table elevKurs;
drop table kurs;
drop table person;

create table person(
  personid int not null,
  navn varchar(20),
  primary key (personid));

insert into person
values(1, 'Per Olsen');
insert into person
values(2, 'Anne Evensen');
insert into person
values(3, 'Hans Jensen');
insert into person
values(4, 'Inger Karlsen');
insert into person
values(5, 'Hans Nilsen');
insert into person
values(6, 'Janne Elise Julsen');

create table kurs(
  kursid int not null,
  navn varchar(25),
  primary key (kursid)
);

insert into kurs
values(1, 'Matematikk');
insert into kurs
values(2, 'Datakommunikasjon');
insert into kurs
values(3, 'Databaser');
insert into kurs
values(4, 'Matematikk II');
insert into kurs
values(5, 'Programmering for web');
insert into kurs
values(6, 'Engelsk fonetikk');

create table elevKurs(
  kursid int not null,
  blokk varchar(4) not null,
  elevid int not null,
  primary key(kursid, blokk, elevid),
  foreign key (kursid) references kurs,
  foreign key (elevid) references person
);

insert into elevKurs
values(1, '1-02', 1);
insert into elevKurs
values(1, '1-02', 2);
insert into elevKurs
values(1, '1-03', 4);
insert into elevKurs
values(2, '2-02', 2);
insert into elevKurs
values(2, '4-03', 3);
insert into elevKurs
values(2, '4-03', 4);
insert into elevKurs
values(3, '6-03', 3);
insert into elevKurs
values(4, '5-03', 4);
insert into elevKurs
values(5, '2-03', 2);
insert into elevKurs
values(5, '2-03', 4);

create table laererKurs(
  kursid int not null,
  blokk varchar(4) not null,
  laererid int,
  primary key(kursid, blokk),
  foreign key (kursid) references kurs,
  foreign key (laererid) references person
);

insert into laererKurs
values(1, '1-02', 5);
insert into laererKurs
values(1, '1-03', 1);
insert into laererKurs
values(2, '1-02', 6);
insert into laererKurs
values(2, '4-03', 6);
insert into laererKurs
values(3, '6-03', 1);
insert into laererKurs
values(4, '5-03', 5);
insert into laererKurs
values(5, '2-03', 6);
insert into laererKurs
values(5, '6-04', NULL);

--equi-join
SELECT distinct navn 
FROM person p, elevKurs ek
WHERE p.personid = ek.elevid;

SELECT distinct navn 
FROM person p 
INNER JOIN elevKurs ek
  ON p.personid = ek.elevid;

--theta-join
SELECT personid, elevid, navn 
FROM person p, elevKurs ek
WHERE p.personid > ek.elevid
AND kursid=1;

SELECT personid, elevid, navn 
FROM person p INNER JOIN elevKurs ek
ON p.personid > ek.elevid
WHERE kursid=1;

--natural join
SELECT personid, elevid, navn 
FROM person p JOIN elevKurs ek
ON p.personid = ek.elevid;

SELECT personid, elevid, navn 
FROM person p, elevKurs ek
WHERE p.personid = ek.elevid;

--cross join
SELECT kursid, elevid, navn
FROM person p, elevKurs ek;

SELECT kursid, elevid, navn
FROM person CROSS JOIN elevKurs;

--self-join
SELECT DISTINCT e1.*
FROM elevKurs e1, elevkurs e2
WHERE e1.kursid = e2.kursid
and e1.blokk = e2.blokk
and e1.elevid != e2.elevid;

--left outer join
SELECT kursid, blokk, p.*
FROM laererKurs l
LEFT OUTER JOIN person p
  ON p.personid = l.laererid;

SELECT kursid, blokk, p.*
FROM laererKurs l, person p
WHERE l.laererid *= p.personid;

--right outer join
SELECT kursid, blokk, p.*
FROM laererKurs l
RIGHT OUTER JOIN person p
  ON p.personid = l.laererid;

SELECT kursid, blokk, p.*
FROM laererKurs l, person p
WHERE l.laererid =* p.personid;

--full outer join
SELECT kursid, blokk, p.*
FROM laererKurs l
FULL OUTER JOIN person p
  ON p.personid = l.laererid;

(SELECT kursid, blokk, p.*
FROM laererKurs l, person p
WHERE l.laererid *= p.personid)
UNION
(SELECT  kursid, blokk, p.*
FROM laererKurs l, person p
WHERE l.laererid =* p.personid)
ORDER BY personid, kursid;

