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);

SELECT navn, blokk
FROM elevKurs ek, person p
WHERE kursid = (SELECT kursid
                FROM kurs
                WHERE navn='Datakommunikasjon')
AND ek.elevid = p.personid;

SELECT DISTINCT navn
FROM kurs k, elevKurs e
WHERE e.kursid <> (SELECT DISTINCT kursid
                   FROM laererKurs l, person p
                   WHERE navn = 'Janne Elise Julsen'
                   AND (blokk = '1-02' OR blokk = '2-02' OR blokk = '3-02')
                   AND l.laererid = p.personid)
AND (blokk = '1-02' OR blokk = '2-02' OR blokk = '3-02')
AND e.kursid = k.kursid;

SELECT DISTINCT navn 
FROM person p 
WHERE personid IN (SELECT elevid
                  FROM elevKurs
                  WHERE kursid = 1);

SELECT DISTINCT navn 
FROM person p 
WHERE personid NOT IN (SELECT elevid
                  FROM elevKurs
                  WHERE kursid = 1);


SELECT navn, COUNT(kursid) AS "Fulgt antall kurs"
FROM person p, elevKurs ek
WHERE p.personid = ek.elevid
GROUP BY(navn)
HAVING COUNT(ek.kursid) >= ALL(SELECT COUNT(kursid)
                        FROM elevKurs
                        GROUP BY(elevid));

SELECT navn, COUNT(kursid) AS "Fulgt antall kurs"
FROM person p, elevKurs ek
WHERE p.personid = ek.elevid
GROUP BY(navn)
HAVING COUNT(ek.kursid) > ANY(SELECT COUNT(kursid)
                        FROM elevKurs
                        WHERE elevid IN (SELECT elevid
                                         FROM elevKurs
                                         WHERE kursid = 1)
                        GROUP BY(elevid));

SELECT navn, COUNT(kursid) AS "Fulgt antall kurs"
FROM person p, elevKurs ek
WHERE p.personid = ek.elevid
GROUP BY(navn)
HAVING COUNT(ek.kursid) > SOME(SELECT COUNT(kursid)
                        FROM elevKurs
                        WHERE elevid IN (SELECT elevid
                                         FROM elevKurs
                                         WHERE kursid = 1)
                        GROUP BY(elevid));

SELECT p.navn AS elev, k.navn AS kurs
FROM person p, elevKurs e, kurs k
WHERE EXISTS (SELECT *
              FROM laererKurs l
              WHERE l.laererid = e.elevid)
AND p.personid = e.elevid
AND k.kursid = e.kursid;

SELECT navn
FROM kurs k
WHERE NOT EXISTS (SELECT *
                  FROM elevKurs e
                  WHERE e.kursid = k.kursid
                  AND (blokk = '1-02' OR blokk = '2-02' OR blokk = '3-02'));

--diverse eksempler
SELECT DISTINCT kursid, navn
FROM kurs
WHERE kursid IN(SELECT kursid
                FROM laererKurs
                WHERE laererid IS NULL);

SELECT DISTINCT k.kursid, navn
FROM kurs k, elevKurs e
WHERE k.kursid <> ALL(SELECT kursid
                      FROM elevKurs);

SELECT p.navn AS 'Navn, person', k.navn AS 'Kursnavn', e.blokk
FROM person p, kurs k, elevKurs e
WHERE e.elevid IN (SELECT DISTINCT kursid
                   FROM elevKurs
                   WHERE kursid <> (SELECT DISTINCT kursid
                                    FROM laererKurs l, person p
                                    WHERE navn = 'Janne Elise Julsen'
                                    AND (blokk = '1-02' OR blokk = '2-02' OR blokk = '3-02')
                                    AND l.laererid = p.personid)
                   AND (blokk = '1-02' OR blokk = '2-02' OR blokk = '3-02'))
AND (blokk = '1-02' OR blokk = '2-02' OR blokk = '3-02')
AND p.personid = e.elevid
AND k.kursid = e.kursid;