/*10
*/
select SUM(worth)
from object, tile, tileType
where tile.id = tileType.id AND description = "Forest";

/*11
*/
select edescription, COUNT(enemyType.id)
from enemyType, enemy
where enemytype = enemyType.id
GROUP BY edescription;

/*12
*/
select edescription, COUNT(enemyType.id)
from enemyType, enemy
where enemytype = enemyType.id
GROUP BY edescription
HAVING COUNT(*)>1;

/*1 TASK 2
*/
select xcoordinate, ycoordinate
from player, tile
where ptile_id = tile.id;

/*2
*/
select description
from player, tile, tileType
where ptile_id = tile.id AND tileType_id = tileType.ID;

/*3
*/
select movement
from player, tile, tileType
where ptile_id = tile.id AND tileType_id = tileType.ID;

/*4
*/
select object.name
from object, player
where owner = player.id;

/*5
*/
select xcoordinate, ycoordinate
from object, tile
where object.tile = tile.id;

/*6
*/
select enemy.name
from player, enemy
where ptile_id = enemy.tile;

/*7
*/
select AVG(enemy.hp)
from player, enemy
where ptile_id = enemy.tile;

/*8
*/
select COUNT(*)
from enemy, enemyType
where enemytype = enemyType.id AND enemyType.edescription = "Dragon";



/*Assignment 4
Task 1
1
*/
UPDATE enemy
SET hp = hp * 0.90;

/*2
*/
UPDATE tile
SET tileType_id = (SELECT id from tileType where description = "Swamp")
where tileType_id in (SELECT id from tileType where description = "Road");

/*3
*/
DELETE
FROM enemy
where enemytype = (SELECT id from enemyType where edescription = "Orc");

/*4
*/
UPDATE player
SET ptile_id = ptile_id+1;

/*5
*/
UPDATE object
SET owner = (SELECT id from player where name = "avatar")
where id in (SELECT id from object where name = "Shield");

/*
UPDATE object
SET owner = NULL
where id >= 2 AND id <= 3;
*/

/*6
*/
UPDATE object
SET tile = (SELECT ptile_id from player where name = "avatar")
where id in (SELECT id from object where name = "Sword");