Database: battle_death
Schema: CREATE TABLE "battle" ( "id" int, "name" text, "date" text, "bulgarian_commander" text, "latin_commander" text, "result" text, primary key("id") ); CREATE TABLE "ship" ( "lost_in_battle" int, "id" int, "name" text, "tonnage" text, "ship_type" text, "location" text, "disposition_of_ship" text, primary key("id"), foreign key (`lost_in_battle`) references `battle`("id")  ); CREATE TABLE "death" ( "caused_by_ship_id" int, "id" int, "note" text, "killed" int, "injured" int, primary key("id"), foreign key ("caused_by_ship_id") references `ship`("id")  );

Question (easy): How many ships ended up being 'Captured'?
SQL: SELECT count(*) FROM ship WHERE disposition_of_ship  =  'Captured'

Question (medium): List the name and tonnage ordered by in descending alphaetical order for the names.
SQL: SELECT name ,  tonnage FROM ship ORDER BY name DESC

Question (medium): List the name, date and result of each battle.
SQL: SELECT name ,  date FROM battle

Question (medium): What is maximum and minimum death toll caused each time?
SQL: SELECT max(killed) ,  min(killed) FROM death

Question (easy): What is the average number of injuries caused each time?
SQL: SELECT avg(injured) FROM death

Question (medium): What are the death and injury situations caused by the ship with tonnage 't'?
SQL: SELECT T1.killed ,  T1.injured FROM death AS T1 JOIN ship AS t2 ON T1.caused_by_ship_id  =  T2.id WHERE T2.tonnage  =  't'

Question (medium): What are the name and results of the battles when the bulgarian commander is not 'Boril'
SQL: SELECT name ,  RESULT FROM battle WHERE bulgarian_commander != 'Boril'

Question (medium): What are the different ids and names of the battles that lost any 'Brig' type shipes?
SQL: SELECT DISTINCT T1.id ,  T1.name FROM battle AS T1 JOIN ship AS T2 ON T1.id  =  T2.lost_in_battle WHERE T2.ship_type  =  'Brig'

Question (hard): What are the ids and names of the battles that led to more than 10 people killed in total.
SQL: SELECT T1.id ,  T1.name FROM battle AS T1 JOIN ship AS T2 ON T1.id  =  T2.lost_in_battle JOIN death AS T3 ON T2.id  =  T3.caused_by_ship_id GROUP BY T1.id HAVING sum(T3.killed)  >  10

Question (extra): What is the ship id and name that caused most total injuries?
SQL: SELECT T2.id ,  T2.name FROM death AS T1 JOIN ship AS t2 ON T1.caused_by_ship_id  =  T2.id GROUP BY T2.id ORDER BY count(*) DESC LIMIT 1

Question (medium): What are the distinct battle names which are between bulgarian commander 'Kaloyan' and latin commander 'Baldwin I'?
SQL: SELECT name FROM battle WHERE bulgarian_commander  =  'Kaloyan' AND latin_commander  =  'Baldwin I'

Question (easy): How many different results are there for the battles?
SQL: SELECT count(DISTINCT RESULT) FROM battle

Question (extra): How many battles did not lose any ship with tonnage '225'?
SQL: SELECT count(*) FROM battle WHERE id NOT IN ( SELECT lost_in_battle FROM ship WHERE tonnage  =  '225' );

Question (extra): List the name and date the battle that has lost the ship named 'Lettice' and the ship named 'HMS Atalanta'
SQL: SELECT T1.name ,  T1.date FROM battle AS T1 JOIN ship AS T2 ON T1.id  =  T2.lost_in_battle WHERE T2.name  =  'Lettice' INTERSECT SELECT T1.name ,  T1.date FROM battle AS T1 JOIN ship AS T2 ON T1.id  =  T2.lost_in_battle WHERE T2.name  =  'HMS Atalanta'

Question (extra): Show names, results and bulgarian commanders of the battles with no ships lost in the 'English Channel'.
SQL: SELECT name ,  RESULT ,  bulgarian_commander FROM battle EXCEPT SELECT T1.name ,  T1.result ,  T1.bulgarian_commander FROM battle AS T1 JOIN ship AS T2 ON T1.id  =  T2.lost_in_battle WHERE T2.location  =  'English Channel'

Question (medium): What are the notes of the death events which has substring 'East'?
SQL: SELECT note FROM death WHERE note LIKE '%East%'