L?sningsforslag til ?vingsoppgaver INF1300 uke 22.-26. oktober
--------------------------------------------------------------

Oppg 1
------

a)

Opprette tabbeller og legge inn data.

create table student (
fnr varchar(11) primary key,
navn varchar(200) not null,
adresse varchar(300),
institutt varchar(100) not null,
niv? varchar(5) not null
);

create table ansatt (
fnr varchar(11) primary key,
navn varchar(200) not null,
adresse varchar(300),
institutt varchar(100) not null,
l?nn int,
kontor varchar(50),
telefon varchar(50)
);

insert into student values ('04128112345', 'Kari', 'Heia 4, Oslo', 'Ifi', 'MA');
insert into student values ('23027812345', 'Ole', NULL, 'Ifi', 'BA');
insert into student values ('01067754356', 'Cathrine', NULL, 'Ifi', 'BA');
insert into student values ('30088162469', 'Per', 'Kringsj?, Oslo', 'Fys', 'BA');
insert into student values ('30088193456', 'Adrian', 'Sognsveien 65, Oslo', 'Fys', 'BA');
insert into student values ('12097492534', 'Emma', NULL, 'Mat', 'MA');
insert into student values ('10036672345', 'Dag', NULL, 'Mat', 'MA');

insert into ansatt values ('10036672345', 'Dag', 'Gaustadalleen 23, Oslo', 'Ifi', 390000, '1612 Ifi', NULL);
insert into ansatt values ('12097492534', 'Emma', NULL, 'Mat', 430000, '214', '22859847');
insert into ansatt values ('05034547134', 'Ragnar', NULL, 'Ifi', 530000, '3214', '22859847');
insert into ansatt values ('12095424617', 'Stein', '?rvollveien 54, Oslo', 'Mat', 430000, '1001', 'Mob: 92054261');
insert into ansatt values ('12086676284', 'Arne', 'Storov. 12, Oslo', 'Mat', 380000, '1203 NHA', '22859347');
insert into ansatt values ('15055575534', 'Ellen', 'Gaustadalleen 23, Oslo', 'Ifi', 580000, '2815 Ifi', '22852413');
insert into ansatt values ('23027812345', 'Ole', NULL, 'Ifi', 220000, NULL, NULL);
insert into ansatt values ('01067754356', 'Cathrine', NULL, 'Ifi', 230000, NULL, NULL);


b) 

select * from ansatt a 
where lower(a.institutt) like 'ifi';

lower er en funksjon i postgres som tar en streng som parameter, og
omgj?r innholdet i strengen til kun sm? bokstaver. Kan v?re greit ?
bruke i streng-testing der man ikke vet om innholdet best?r av store,
sm? eller en kombinasjon av begge typer bokstaver. upper gj?r det
samme, men til store bokstaver.


c)

select * from ansatt a 
where lower(a.institutt) not like 'ifi';


d)

select * from ansatt a
where l?nn > 400000;


e)

select a.navn, a.kontor from ansatt a
where kontor is not NULL;


f)

select s.fnr, s.navn, s.institutt, a.institutt from student s, ansatt a
where s.fnr = a.fnr
and s.institutt = a.institutt;


g) 

Vi skal alts? ha mengden av alle institutter som er representert i
Student-tabellen pluss mengden av alle institutter i
Ansatt-tabellen. M? bruke UNION: 

(select s.institutt from student s)
union
(select a.institutt from ansatt a);

Husk at attributtene i de to relasjonene i en mengdeoperasjon m? v?re
unionkompatible.


h)

Igjen skal vi ha summen av to mengder (ansatte ved ifi + studenter ved
ifi), og bruker da UNION.

* UNION ALL er s?kalt bag-union og fjerner ikke duplikater:

(select s.fnr, s.navn from student s
where s.institutt like 'Ifi')
union all
(select a.fnr, a.navn from ansatt a
where a.institutt like 'Ifi');


* UNION gir oss et set, dvs. den sorterer, sammenligner og fjerner
  duplikater fra resultatmengden. Dette er en langt dyrere (mindre
  effektiv) operasjon enn UNION ALL.

(select s.fnr, s.navn from student s
where s.institutt like 'Ifi')
union all
(select a.fnr, a.navn from ansatt a
where a.institutt like 'Ifi');


i)

Her m? vi igjen h?ndtere to mengder, mengden av ansatte og mengden av
studenter. Det vi ?nsker oss er mengden av studenter MINUS de
studentene som ogs? er ansatte. Bruker derfor EXCEPT (dvs. vi kan like
gjerne bruke EXCEPT ALL siden vi vet det ikke finnes duplikater i
student-tabellen):

(select s.fnr, s.navn from student s)
except all
(select a.fnr, a.navn from ansatt a);


j)

Det vi her er ute etter er en gjeng med studenter som ogs? er ansatte,
dvs. snittet av mengden ansatte og studenter, men med et passende
utplukk (de som er masterstudenter og tjener med en 300000). Oppgaven
kan l?ses med INTERSECT:

(select s.fnr, s.navn from student s
where niv? like 'MA')
intersect
(select a.fnr, a.navn from ansatt a
where l?nn > 300000);


Vi kan ogs? bruke en vanlig join der vi kombinerer tabellene Ansatt og
Student, for deretter ? bruke where-klausuler til ? plukke ut de som
er master, har l?nn over 300000, samt forekommer i b?de student- og
ansatt-tabellen (s.fnr = a.fnr):

select distinct s.fnr, s.navn from student s, ansatt a
where s.fnr = a.fnr
and s.niv? like 'MA'
and a.l?nn > 300000;



Oppg 2
------

a)

"Har bodd" tolkes bokstavelig, derfor velges kun de overnattingene som
har fraDato f?r dagen i dag.

select g.gjestnr, g.gjestnavn, b.fradato from Gjest g, Bestilling b, Hotell h
where h.by like 'Trondheim'
and  h.hotellnr = b.hotellnr
and b.fradato < current_date
and b.gjestnr = g.gjestnr;


b)

select distinct h1.hotellnavn, h1.by from hotell h1, hotell h2 
where h1.by != h2.by 
and h1.hotellnavn = h2.hotellnavn;


c) 

Her kan det v?re fristende ? bare velge ut de rom fra Bestilling som
har tilDato tidligere en current_date eller fraDato senere enn
current_date. Men hva med de rommene som aldri har v?rt bebodd? De er
ogs? ledige, men har ingen entry i Bestillings-tabellen.

L?sningen er ? f?rst finne alle forekoster av rom p? Oslo Plaza (det
gj?r vi i den f?rste select-setningen), deretter fjerner vi - med
except all - alle rom som det i ?yeblikket bor noen p?. Da sitter vi
igjen med alle rom som i dag st?r ledige.

select r.romnr from Rom r, Hotell h
where h.hotellnavn like 'Oslo Plaza'
and h.hotellnr = r.hotellnr
except all
(select b.romnr from Bestilling b, Hotell h
where h.hotellnavn like 'Oslo Plaza'
and h.hotellnr = b.hotellnr
and (b.fraDato <= current_date and b.tilDato > current_date)); 


d)

Resonnementet blir det samme som i oppg c).

select h.hotellnavn, r.romnr from hotell h, rom r
where h.by like 'Trondheim'
and h.hotellnr = r.hotellnr
except all
(select h.hotellnavn, b.romnr from hotell h, bestilling b
where h.by like 'Trondheim'
and h.hotellnr = b.hotellnr
and (b.fraDato <= current_date and b.tilDato > current_date));