use master
if exists(select * from sys.databases where name='fødselsrate_2017')
drop database fødselsrate_2017
go
create database fødselsrate_2017
go
use fødselsrate_2017
create role AdminRole
grant alter any role to AdminRole with grant option
grant alter any user,select, insert, update on database::fødselsrate_2017 to AdminRole with grant option
create role SuperRole
grant select, insert, update on database::fødselsrate_2017 to SuperRole
create role UserRole
grant select on database::fødselsrate_2017 to UserRole
go

create table Verdensdel
(
VerdenID int primary key,
Verdensdel nvarchar(30) unique
)
create table Land
(
LandID int identity(1,1) primary key,
Land nvarchar(30) unique,
Verdensdel1 int foreign key references Verdensdel(VerdenID),
Verdensdel2 int foreign key references Verdensdel(VerdenID)
)

create table Rang
(
LandID int identity(1,1) foreign key references Land(LandID),
Rang int,
Fødselsrate decimal(8,2)
)

insert into Verdensdel values
(1,'Europa'),
(2,'Nordamerika'),
(3,'Sydamerika'),
(4,'Oceanien'),
(5,'Asien'),
(6,'Afrika'),
(7,'Antarktis')

insert into Land values
('Danmark',1,null),
('Canada',2,null),
('Niger',6,null),
('Indien',5,null),
('Brasilien',3,null),
('New Zealand',4,null),
('USA',2,null),
('Norge',1,null),
('Tyrkiet',1,5),
('Ukraine',1,5)

insert into Rang values
(169,10.27),
(168,10.28),
(1,45.45),
(85,19.55),
(129,14.46),
(139,13.33),
(145,12.49),
(149,12.14),
(114,16.33),
(162,10.72)

select Land.Land,Verdensdel.Verdensdel, Rang.Rang, Rang.Fødselsrate from Land inner join Verdensdel on Land.Verdensdel1 = Verdensdel.VerdenID inner join Rang on Rang.LandID = Land.LandID order by Land.Land