CREATE TABLE ny_sted (
 postnr   CHAR(4),
 poststed VARCHAR(20) NOT NULL,
 CONSTRAINT ny_sted_pk PRIMARY KEY(postnr));

CREATE table STUDENT (
 sno     CHAR(3),
 f_dato  DATE,
 enavn   VARCHAR(20) NOT NULL,
 fnavn   VARCHAR(20) NOT NULL,
 adresse VARCHAR(25),
 postnr  CHAR(4),
 CONSTRAINT student_pk PRIMARY KEY(sno),
 CONSTRAINT student_fk FOREIGN KEY(postnr)REFERENCES sted(postnr));

CREATE TABLE fag (
 fakode CHAR(7),
 fanavn VARCHAR(30) NOT NULL,
 v_tall INTEGER NOT NULL,
 CONSTRAINT fag_pk PRIMARY KEY(fakode),
 CONSTRAINT fag_un UNIQUE(fanavn));

CREATE TABLE studfag (
 sno      CHAR(3),
 fakode   CHAR(7),
 aar      CHAR(4),
 karakter FLOAT,
 CONSTRAINT studfag_pk PRIMARY KEY(sno, fakode),
 CONSTRAINT studfag_fk1 FOREIGN KEY(sno)REFERENCES student(sno),
 CONSTRAINT studfag_fk2 FOREIGN KEY(fakode)REFERENCES fag(fakode));

INSERT INTO sted (postnr,poststed) VALUES('7018','TRONDHEIM');
INSERT INTO sted (postnr,poststed) VALUES('6408','AUREOSEN');
INSERT INTO sted (postnr,poststed) VALUES('7863','OVERHALLA');
INSERT INTO sted (postnr,poststed) VALUES('7033','TRONDHEIM');
INSERT INTO sted (postnr,poststed) VALUES('7042','TRONDHEIM');
INSERT INTO sted (postnr,poststed) VALUES('7029','TRONDHEIM');
INSERT INTO sted (postnr,poststed) VALUES('8010','BODØ');
INSERT INTO sted (postnr,poststed) VALUES('7011','TRONDHEIM');
INSERT INTO sted (postnr,poststed) VALUES('2020','SKEDSMOKORSET');
INSERT INTO sted (postnr,poststed) VALUES('7022','TRONDHEIM');
INSERT INTO sted (postnr,poststed) VALUES('0313','OSLO');
INSERT INTO sted (postnr,poststed) VALUES('7080','HEIMDAL');
INSERT INTO sted (postnr,poststed) VALUES('7075','TILLER');
INSERT INTO sted (postnr,poststed) VALUES('7700','STEINKJER');
INSERT INTO sted (postnr,poststed) VALUES('7041','TRONDHEIM');
INSERT INTO sted (postnr,poststed) VALUES('4473','KVINLOG');

INSERT INTO ny_sted (postnr,poststed) VALUES('7350','BUVIKA');
INSERT INTO ny_sted (postnr,poststed) VALUES('7084','MELHUS');
INSERT INTO ny_sted (postnr,poststed) VALUES('0313','OSLO');

INSERT INTO student VALUES('001','01.12.78','OLSEN','PER','ILEVOLLEN 22','7018');
INSERT INTO student VALUES('002','31.01.82','JENSEN','NINA','SVARLIA','6408');
INSERT INTO student VALUES('003','14.10.79','TRULSEN','KÅRE','ELGLIA','7863');
INSERT INTO student VALUES('004','30.06.69','OVESEN','OVE','ARNE BERGSGÅRDSVEI','7033');

INSERT INTO fag VALUES('LO187D','PROGRAMMERING',3);
INSERT INTO fag VALUES('LV338D','DATABASEADMINISTRASJON',2);
INSERT INTO fag VALUES('LO213D','DATAMASKINTEKNIKK',3);
INSERT INTO fag VALUES('LO244D','DATATEKNIKK',4);
INSERT INTO fag VALUES('LO323D','DATABASER',2);
INSERT INTO fag VALUES('LO336D','DATABASER OG SYSTEMERING',3);
INSERT INTO fag VALUES('LO335D','SYSTEMERING OG DATABASER',4);
INSERT INTO fag VALUES('BO328D','APPLIKASJONSUTVIKLING',2);
INSERT INTO fag VALUES('LO117D','ALGORITMISKE METODER',2);
INSERT INTO fag VALUES('SO326D','PROGRAMUTVIKLINGSMETODER',3);

INSERT INTO studfag VALUES('001','LO323D','1998',1.8);
INSERT INTO studfag VALUES('001','LO213D','1997',2.1);
INSERT INTO studfag VALUES('001','LO244D','1998',2.3);
INSERT INTO studfag VALUES('002','LO323D','1998',3.1);
INSERT INTO studfag VALUES('002','LO213D','1998',1.4);
INSERT INTO studfag VALUES('003','LO323D','1998',NULL);