create table Tagplats
(Id integer not null primary key);

create table Station
(Id integer not null primary key references Tagplats(Id),
Namn char(20) not null);

create table Stracka
(Id integer not null primary key references Tagplats(Id),
Fran integer not null references Station(Id),
Till integer not null references Station(Id));

create table Lok
(Nummer integer not null primary key,
Vikt integer);

create table Tag
(Id integer not null primary key,
Lok integer references Lok(Nummer),
Placering integer references Tagplats(Id));

create table Vagn
(Id integer not null primary key,
Tomvikt integer,
Lastvikt integer,
Tillhor integer references Tag(Id));

insert into Tagplats values (1);
insert into Tagplats values (2);
insert into Tagplats values (3);
insert into Tagplats values (4);
insert into Tagplats values (5);
insert into Tagplats values (6);
insert into Tagplats values (7);

insert into Station values (1, 'Örebro Central');
insert into Station values (2, 'Örebro Södra');
insert into Station values (3, 'Kumla');
insert into Station values (4, 'Hallsberg');

insert into Stracka values (5, 4, 1);
insert into Stracka values (6, 1, 2);
insert into Stracka values (7, 2, 3);

insert into Lok values (1, 100);
insert into Lok values (2, 200);
insert into Lok values (3, 300);
insert into Lok values (4, 400);
insert into Lok values (14, 1400);

insert into Tag values (1, 1, 2);
insert into Tag values (2, 2, 1);
insert into Tag values (3, 3, 1);

insert into Vagn values (1, 10, 100, 1);
insert into Vagn values (2, 20, 200, 1);
insert into Vagn values (3, 30, 300, 1);
insert into Vagn values (4, 40, 400, 2);
insert into Vagn values (5, 50, 500, 2);
insert into Vagn values (6, 60, 600, 3);
insert into Vagn values (7, 70, 700, 3);
insert into Vagn values (8, 80, 800, 3);
insert into Vagn values (9, 90, 900, null);
insert into Vagn values (10, 100, 1000, null);


/* a */

select Vikt from Lok where Nummer = 14;

/* b */

select Tag.Lok
from Tag, Station
where Tag.Placering = Station.Id
and Station.Namn = 'Örebro Södra';

/* c */

create view Tagvikt as
select Tag.Id as Tag, sum(Vagn.Tomvikt + Vagn.Lastvikt) as Vagnviktssumma
from Vagn, Tag
where Vagn.Tillhor = Tag.Id
group by Tag.Id;

select * from Tagvikt;

select Lok.Vikt + Tagvikt.Vagnviktssumma
from Lok, Tag, Tagvikt, Station
where Tag.Lok = Lok.Nummer
and Tag.Id = Tagvikt.Tag
and Tag.Placering = Station.Id
and Station.Namn = 'Örebro Södra';

/* c, som en enda fråga */

select Lok.Vikt +
(select sum(Vagn.Tomvikt + Vagn.Lastvikt)
from Vagn, Tag, Station
where Vagn.Tillhor = Tag.Id
and Tag.Placering = Station.Id
and Station.Namn = 'Örebro Södra')
from Lok, Tag, Station
where Tag.Lok = Lok.Nummer
and Tag.Placering = Station.Id
and Station.Namn = 'Örebro Södra';

/* d */

create view Taglangd as
select Tag.Id as Tag, count(Vagn.Id) as AntalVagnar
from Vagn, Tag
where Vagn.Tillhor = Tag.Id
group by Tag.Id;

select * from Taglangd;

select max(AntalVagnar)
from Taglangd, Tag, Station
where Taglangd.Tag = Tag.Id
and Tag.Placering = Station.Id
and Station.Namn = 'Örebro Central';

/* e */

select andra.Namn
from Stracka, Station as orebro_c, Station as andra
where Stracka.Fran = orebro_c.Id
and Stracka.Till = andra.Id
and orebro_c.Namn = 'Örebro Central'
union
select andra.Namn
from Stracka, Station as orebro_c, Station as andra
where Stracka.Till = orebro_c.Id
and Stracka.Fran = andra.Id
and orebro_c.Namn = 'Örebro Central';

/* 4 */

a:

Lok.Nummer

b:

Tag.Placering
Station.Id
Station.Namn

c:

Vagn.Tillhor
Tag.Id
Tag.Lok
Lok.Nummer
Tag.Placering
Station.Id
Station.Namn

Dvs:

Lok.Nummer
Station.Id
Station.Namn
Tag.Id
Tag.Lok
Tag.Placering
Vagn.Tillhor