Should work

--SHOULD WORK

--Create countries
INSERT INTO countries VALUES('Sweden');
INSERT INTO countries VALUES('Norway');

--Update population of area
INSERT INTO countries VALUES('Sweden');
INSERT INTO areas VALUES('Sweden', 'Borås', 40000);
UPDATE areas SET population = 45000 WHERE country='Sweden' AND name='Borås';
SELECT assert ( ( SELECT population FROM areas WHERE country='Sweden' AND name='Borås') , 45000) ;

--Add city and town
INSERT INTO countries VALUES('Sweden');
INSERT INTO areas VALUES('Sweden', 'Gothenburg', 500000);
INSERT INTO areas VALUES('Sweden', 'Borås', 40000);
INSERT INTO towns VALUES('Sweden', 'Borås');
INSERT INTO cities VALUES('Sweden','Gothenburg',10000);

--Update visitbonus for city
INSERT INTO countries VALUES('Sweden');
INSERT INTO areas VALUES('Sweden', 'Gothenburg', 500000);
UPDATE cities SET visitbonus = 8000 WHERE name='Gothenburg';
SELECT assert ( ( SELECT visitbonus FROM cities WHERE name='Gothenburg') , 8000);

--Add persons
INSERT INTO countries VALUES('Sweden');
INSERT INTO countries VALUES('Norway');
INSERT INTO areas VALUES('Sweden', 'Gothenburg', 500000);
INSERT INTO areas VALUES('Norway','Oslo','400000');
INSERT INTO persons VALUES('Norway' ,'19950101-4444' ,'Kim' ,'Norway','Oslo',10000);
INSERT INTO persons VALUES('Sweden' ,'19931215-0879' ,'John' ,'Sweden','Borås',5000);

--Update persons locationarea
INSERT INTO countries VALUES('Sweden');
INSERT INTO areas VALUES('Sweden', 'Gothenburg', 500000);
INSERT INTO areas VALUES('Sweden', 'Borås', 40000);
INSERT INTO persons VALUES('Sweden' ,'19931215-0879' ,'John' ,'Sweden','Borås',5000);
SELECT assert ( ( SELECT locationarea FROM persons WHERE country = 'Sweden' AND personnumber='19931215-0879') , 'Gothenburg') ;

--Update budget
INSERT INTO countries VALUES('Sweden');
INSERT INTO areas VALUES('Sweden', 'Gothenburg', 500000);
INSERT INTO areas VALUES('Sweden', 'Borås', 40000);
INSERT INTO persons VALUES('Sweden' ,'19931215-0879' ,'John' ,'Sweden','Borås',5000);
UPDATE persons SET budget = 6000 WHERE personnumber='19931215-0879';
SELECT assert ( ( SELECT budget FROM persons WHERE country = 'Sweden' AND
personnumber='19931215-0879'), 6000);

--Update roadtax
INSERT INTO countries VALUES('Sweden');
INSERT INTO areas VALUES('Sweden', 'Gothenburg', 500000);
INSERT INTO areas VALUES('Sweden', 'Borås', 40000);
INSERT INTO persons VALUES('Sweden' ,'19931215-0879' ,'John' ,'Sweden','Borås',5000);
--Create road
INSERT INTO roads VALUES('Sweden' ,'Göteborg' ,'Sweden' ,'Borås','Sweden','19931215-0879', getval('roadtax'));
UPDATE roads SET roadtax=500 WHERE ownercountry='Sweden' AND ownerpersonnumber='19931215-0879;
SELECT assert ( ( SELECT roadtax FROM roads WHERE ownercountry='Sweden' AND ownerpersonnumber='19931215-0879 ), 500 );

--Update locationarea and locationcountry;
INSERT INTO countries VALUES('Sweden');
INSERT INTO countries VALUES('Norway');
INSERT INTO areas VALUES('Norway','Oslo','400000');
INSERT INTO areas VALUES('Sweden', 'Borås', 40000);
INSERT INTO persons VALUES('Sweden' ,'19931215-0879' ,'John' ,'Sweden','Borås',5000);
--Changes locationcountry -and area of John
SELECT assert ((SELECT locationcountry AND locationarea FROM persons WHERE country = 'Sweden' AND personnumber='19931215-0879'), 'Norway','Oslo');


--Buying/selling hotel
--Add countries,areas and persons
INSERT INTO countries VALUES('Sweden');
INSERT INTO areas VALUES('Sweden', 'Gothenburg', 500000);
INSERT INTO areas VALUES('Norway','Oslo','400000');
INSERT INTO areas VALUES('Sweden', 'Borås', 40000);
INSERT INTO persons VALUES('Sweden' ,'19931215-0879' ,'John' ,'Sweden','Borås',5000);
INSERT INTO persons VALUES('Norway' ,'19950101-4444' ,'Kim' ,'Norway','Oslo',10000);
--John buys hotel
INSERT INTO hotels VALUES('Scandic' ,'Sweden' ,'Gothenburg' ,'Sweden','19931215-0879');
SELECT assert ( ( SELECT budget FROM persons WHERE country='Sweden' AND personnumber='19931215-0879' ), ( 5000 - (getval('hotelprice')) ) );
--Change owner of the hotel
SELECT assert ( ( SELECT ownerpersonnumber FROM hotels WHERE locationcountry = 'Sweden' AND locationname = 'Gothenburg' AND ownercountry = 'Sweden' ) , '19950101-4444') ;
--Assert Kims budget
SELECT assert ( ( SELECT budget FROM persons WHERE country='Sweden' AND personnumber='19950101-4444' ),  (10000 -(getval('hotelprice)) )  );
--Update Johns budget
UPDATE persons SET ( budget ( budget + (getval('hotelrefund')) ) )
SELECT assert ( ( SELECT budget FROM persons WHERE country='Sweden' AND personnumber='19931215-0879' ), ( budget ( budget + (getval('hotelrefund')) ) ) );
--Kim buys other hotel in different area
INSERT INTO hotels VALUES('Scandic' ,'Norway' ,'Oslo' ,'Sweden','19950101-4444');
SELECT assert ( ( SELECT budget FROM persons WHERE country='Sweden' AND personnumber='19950101-4444' ), ( budget = (budget -(getval('hotelprice)) ) ) );
--Show all hotels Kim owns
SELECT FROM hotels WHERE ownercountry='Sweden' AND ownerpersonnumber='19950101-4444'

--Roads
--Add countries,areas and persons
INSERT INTO countries VALUES('Sweden');
INSERT INTO areas VALUES('Sweden', 'Göteborg', 500000);
INSERT INTO areas VALUES('Sweden', 'Borås', 40000);
INSERT INTO persons VALUES('Sweden' ,'19931215-0879' ,'John' ,'Sweden','Göteborg',5000);
INSERT INTO persons VALUES('Sweden' ,'19950101-4444' ,'Kim' ,'Sweden','Göteborg',10000);
--John creates road
INSERT INTO roads VALUES('Sweden' ,'Göteborg' ,'Sweden' ,'Borås','Sweden','19931215-0879', getval('roadtax'));
--Update Johns budget
SELECT assert( ( SELECT budget FROM persons WHERE country='Sweden' AND personnummer='19931215-0879' ), ( 5000 - (getval('roadprice)) ) );
--Kim travels private owned road
SELECT assert( ( SELECT locationarea FROM persons WHERE country='Sweden' AND personnumber='19950101-4444' ), 'Borås' );
--Kim must pay roadtax
SELECT assert( ( SELECT budget FROM persons WHERE country='Sweden' AND personnumber='19950101-4444' ), ( 10000 - (getval('roadtax')) ) );
--John recieves roadtax
UPDATE persons SET budget = ( budget = ( budget + (getval('roadtax')) ) ) WHERE country='Sweden' AND personnumber='19931215-0879'
SELECT assert( (SELECT budget FROM persons WHERE country='Sweden' AND personnumber='19931215-0879'), ( budget = ( budget + (getval('roadtax')) ) ) );
--Göteborg gets a citybonus

--Cityvisit
--Add countries,areas and persons
INSERT INTO countries VALUES('Sweden');
INSERT INTO countries VALUES('Norway');
INSERT INTO areas VALUES('Sweden', 'Göteborg', 500000);
INSERT INTO areas VALUES('Sweden', 'Borås', 40000);
INSERT INTO areas VALUES('Norway', 'Oslo', 400000);
INSERT INTO areas VALUES('Norway', 'Svalbard', 100000);
INSERT INTO persons VALUES('Sweden' ,'19931215-0879' ,'John' ,'Sweden','Göteborg',5000);
INSERT INTO persons VALUES('Sweden' ,'19950101-4444' ,'Kim' ,'Sweden','Borås',10000);
INSERT INTO persons VALUES('Norway' ,19921234-5678' ,'Mats' ,'Norway' ,'Oslo' ,3500);
INSERT INTO persons VALUES('Norway' ,19954321-0987' ,'Erik' ,'Norway' ,'Svalbard' ,12500);
--Create several hotels
INSERT INTO hotels VALUES('Scandic' ,'Sweden' ,'Göteborg' ,'Sweden','19931215-0879');
INSERT INTO hotels VALUES('Hilton' ,'Sweden' ,'Göteborg' ,'Norway','19921234-5678');
INSERT INTO hotels VALUES('Scandic' ,'Sweden' ,'Göteborg' ,'Norway','19954321-0987');
--Assert owners budgets
--John
SELECT assert ( ( SELECT budget FROM persons WHERE country='Sweden' AND personnumber='19931215-0879' ), ( 5000 - (getval('hotelprice')) ) );
--Mats
SELECT assert ( ( SELECT budget FROM persons WHERE country='Norway' AND personnumber='19921234-5678' ), ( 3500 - (getval('hotelprice')) ) );
--Erik
SELECT assert ( ( SELECT budget FROM persons WHERE country='Norway' AND personnumber='19954321-0987' ), ( 12500 - (getval('hotelprice')) ) );
--Kim visits Göteborg
 --Takes public owned road(no roadtax)
SELECT assert( ( SELECT locationarea FROM persons WHERE country='Sweden' AND personnumber='19950101-4444' ), 'Göteborg' );
--Kim pays cityvisit fee
SELECT assert( ( SELECT budget FROM persons WHERE country='Sweden' AND personnumber='19950101-4444' ), 5000 - (getval('cityvisit')) );
--Owners get their share of the cityvisit fee
--John
SELECT assert ( ( SELECT budget FROM persons WHERE country='Sweden' AND personnumber='19931215-0879' ), ( budget = ( budget + (getval('cityvisit'))/3) ) );
--Mats
SELECT assert ( ( SELECT budget FROM persons WHERE country='Norway' AND personnumber='19921234-5678' ), ( budget = ( budget + (getval('cityvisit'))/3) ) );
--Erik
SELECT assert ( ( SELECT budget FROM persons WHERE country='Norway' AND personnumber='19954321-0987' ), ( budget = ( budget + (getval('cityvisit'))/3) ) );