.headers ON
.mode column

--select Battles.name as battleName, date,  count(distinct classes.country) as numberOfCountries
--from Battles
--join outcomes on Battles.name = outcomes.battle
--join Ships on Ships.name = outcomes.ship or Ships.name = outcomes.sunkBy
--join classes on classes.class = ships.class
--group by battleName;

--select battle, count(distinct country)
--from classes,ships,outcomes
--where (name=ship or name = sunkBy) and ships.class = classes.class
--group by battle;

--select avg(numGuns)
--from ships
--natural join classes
--where name not in (select ship from outcomes where result is 'sunk');

--Lausn 1 f. daemi c
--create temp view classesMoreThan2 as
----select class, count(class) as numberOfShips
----from ships
----group by class
----having numberOfShips >= 2;

--select ships.class, count(ships.class) as shipsSunk
--from ships
--natural join classesMoreThan2
--join outcomes on outcomes.sunkBy = ships.name
--group by ships.class;

--Lausn 2 f daemi c
--create temp view sc as
----select classes.class, count(ships.name)
----from classes,ships --
----where classes.class=ships.class
----group by classes.class
----having count(ships.name) >= 2;

--select sc.class, count(outcomes.sunkby)
--from sc, ships, outcomes
--where sc.class=ships.class and outcomes.ship = ships.name
--group by sc.class;

--select sunkBy as sinker from outcomes where sunkBy is not NULL and sunkBy is not 'NULL';