-- kustutame olemasolevad tabelid
drop table hinne;
drop table aine;
drop table opilane;

-- loome tabelid
-- hinne
create table hinne(
id_hinne	serial not null primary key,
id_aine		serial not null,
tudengikood	numeric(6),
hinne		numeric(1)
);

do
$do$
begin
for i in 1..250 loop
insert into hinne(id_aine,tudengikood,hinne) values 
(
	(SELECT id_aine FROM aine ORDER BY RANDOM() LIMIT 1),	
	(SELECT tudengikood FROM opilane ORDER BY RANDOM() LIMIT 1),
	random() * 5
);
end loop;
end
$do$;

--aine
create table aine(
id_aine		serial not null primary key,
eap		numeric(1),
ainekood	varchar(7),
nimi		varchar(32), 
oppejoud	varchar(32)
);

insert into aine(eap,ainekood,nimi,oppejoud) values (6, 'aaa4199', 'Füüsika', 'Ivari Vesik');
insert into aine(eap,ainekood,nimi,oppejoud) values (5, 'sdf2344', 'Diskreetne matemaatika', 'Kalle Oja');
insert into aine(eap,ainekood,nimi,oppejoud) values (4, 'abb3443', 'Programmeerimine', 'Gustav Masing');
insert into aine(eap,ainekood,nimi,oppejoud) values (3, 'vbc3321', 'Digitaalsüsteemid', 'Evert Kross');
insert into aine(eap,ainekood,nimi,oppejoud) values (4, 'kks3004', 'Arvutiarhitektuurid', 'Arnold Viiding');
insert into aine(eap,ainekood,nimi,oppejoud) values (2, 'cyb3332', 'Krüptoloogia', 'Priidu Magi');
insert into aine(eap,ainekood,nimi,oppejoud) values (2, 'fin6064', 'Finantsjuhtimine', 'Luule Vaher');

--opilane
create table opilane
(
  tudengikood numeric(6,0) NOT NULL,
  eesnimi character varying(32),
  perekonnanimi character varying(32),
  synnikp date,
  kkh numeric(3,2)
);
  
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Sven', 'Keskula',    ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Reio', 'Aavik',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Paavo', 'Meri',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Kaur', 'Valk',       ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Leevi', 'Pihlak',    ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Veljo', 'Kuusk',     ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Kajar', 'Ruutel',    ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Anti', 'Pihlak',     ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Robin', 'Meri',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Kustav', 'Mark',     ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Ivari', 'Eenpalu',   ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Andrus', 'Kangro',   ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Kaspar', 'Masing',   ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Eedu', 'Mitt',       ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Aadu', 'Puhvel',     ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Mait', 'Lepmets',    ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Raimond', 'Kivi',    ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Heiki', 'Vosu',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Ahto', 'Koiv',       ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Riho', 'Vesik',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Marek', 'Harma',     ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Ago', 'Kallas',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Leevi', 'Jakobson',  ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Aadu', 'Orav',       ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Iisak', 'Valk',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Allan', 'Kross',     ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Roland', 'Valk',     ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Rihard', 'Saar',     ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Roland', 'Eskola',   ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Raiko', 'Mark',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Ulari', 'Janes',     ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Heiki', 'Ruutel',    ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Urmet', 'Rootare',   ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Aarne', 'Kivi',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Tonu', 'Kabin',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Innar', 'Kaljurand', ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Tanel', 'Tamm',      ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Ivo', 'Lepp',        ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
insert into opilane values ((select max(tudengikood) from opilane)+ trunc(random()*100), 'Runno', 'Teder',     ((TO_DATE('01-JAN-1990','DD-MON-YYYY') + trunc(random() * 365) * '1 day'::interval + trunc(random() * 5) * '1 year'::interval)), mod(trunc((random() * 10)::numeric, 2), 4) + 1);
