 | Idx | Table      | Column | Primary Key | Foreign Key | 
 | ----------- | ----------- | ----------- | ----------- | ----------- | 
  | 0 |  | * |   |   | 
 | 1 | **battle** | id | + |   | 
 | 2 |   | name |   |   | 
 | 3 |   | date |   |   | 
 | 4 |   | bulgarian_commander |   |   | 
 | 5 |   | latin_commander |   |   | 
 | 6 |   | result |   |   | 
 | 7 | **ship** | lost_in_battle |   | --> 1 | 
 | 8 |   | id | + |   | 
 | 9 |   | name |   |   | 
 | 10 |   | tonnage |   |   | 
 | 11 |   | ship_type |   |   | 
 | 12 |   | location |   |   | 
 | 13 |   | disposition_of_ship |   |   | 
 | 14 | **death** | caused_by_ship_id |   | --> 8 | 
 | 15 |   | id | + |   | 
 | 16 |   | note |   |   | 
 | 17 |   | killed |   |   | 
 | 18 |   | injured |   |   | 
 
  | Index | Question  | SQL | gold QDMR | pred QDMR | Exec | SQL hardness |
  | ----------- | ----------- | ----------- |  ----------- | ----------- | ----------- | ----------- | 
 | SPIDER_dev_491 | How many ships ended up being 'Captured'? | SELECT count(*) FROM ship WHERE disposition_of_ship  =  'Captured' | 1. SELECT[tbl:​ship] <br>2. FILTER[#1, comparative:​=:​Captured:​col:​ship:​disposition_of_ship] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[tbl:​ship] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Captured:​col:​ship:​disposition_of_ship] <br>3. AGGREGATE[count, #2] <br> | + | easy | 
  | SPIDER_dev_492 | List the name and tonnage ordered by in descending alphabetical order for the names. | SELECT name ,  tonnage FROM ship ORDER BY name DESC | 1. SELECT[col:​ship:​name] <br>2. SELECT[col:​ship:​tonnage] <br>3. UNION[#1, #2] <br>4. SORT[#3, #1, sortdir:​descending] <br> | 1. SELECT[col:​ship:​name] <br>2. PROJECT[col:​ship:​name, #1] <br>3. PROJECT[col:​ship:​tonnage, #1] <br>4. UNION[#2, #3] <br>5. SORT[#4, #3, sortdir:​descending] <br> | - | medium | 
  | SPIDER_dev_493 | List the name, date and result of each battle. | SELECT name ,  date ,  result FROM battle | 1. SELECT[tbl:​battle] <br>2. PROJECT[col:​battle:​name, #1] <br>3. PROJECT[col:​battle:​date, #1] <br>4. PROJECT[col:​battle:​result, #1] <br>5. UNION[#2, #3, #4] <br> | 1. SELECT[tbl:​battle] <br>2. PROJECT[col:​battle:​name, #1] <br>3. PROJECT[col:​battle:​date, #1] <br>4. PROJECT[col:​battle:​result, #1] <br>5. UNION[#2, #3, #4] <br> | + | medium | 
  | SPIDER_dev_494 | What is maximum and minimum death toll caused each time? | SELECT max(killed) ,  min(killed) FROM death | 1. SELECT[col:​death:​killed] <br>2. AGGREGATE[max, #1] <br>3. AGGREGATE[min, #1] <br>4. UNION[#2, #3] <br> | 1. SELECT[col:​death:​caused_by_ship_id] <br>2. PROJECT[tbl:​death, #1] <br>3. GROUP[min, #2, #1] <br>4. AGGREGATE[max, #3] <br>5. AGGREGATE[min, #3] <br>6. UNION[#4, #5] <br> | - | medium | 
  | SPIDER_dev_496 | What are the death and injury situations caused by the ship with tonnage 't'? | 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' | 1. SELECT[tbl:​ship] <br>2. PROJECT[col:​ship:​tonnage, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​t:​col:​ship:​tonnage] <br>4. PROJECT[col:​death:​killed, #3] <br>5. PROJECT[col:​death:​injured, #3] <br>6. UNION[#4, #5] <br> | 1. SELECT[tbl:​ship] <br>2. PROJECT[col:​ship:​tonnage, #1] <br>3. COMPARATIVE[#1, #2, comparative:​like:​t:​col:​ship:​tonnage] <br>4. PROJECT[col:​death:​killed, #3] <br>5. PROJECT[col:​death:​injured, #3] <br>6. UNION[#4, #5] <br> | + | medium | 
  | SPIDER_dev_497 | What are the name and results of the battles when the bulgarian commander is not 'Boril' | SELECT name ,  RESULT FROM battle WHERE bulgarian_commander != 'Boril' | 1. SELECT[tbl:​battle] <br>2. PROJECT[col:​battle:​bulgarian_commander, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​Boril:​col:​battle:​bulgarian_commander] <br>4. PROJECT[col:​battle:​name, #3] <br>5. PROJECT[col:​battle:​result, #3] <br>6. UNION[#4, #5] <br> | 1. SELECT[tbl:​battle] <br>2. COMPARATIVE[#1, #1, comparative:​!=:​Boril:​col:​battle:​bulgarian_commander] <br>3. PROJECT[col:​battle:​name, #2] <br>4. PROJECT[col:​battle:​result, #2] <br>5. UNION[#3, #4] <br> | + | medium | 
  | SPIDER_dev_498 | What are the different ids and names of the battles that lost any 'Brig' type shipes? | 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' | 1. SELECT[tbl:​battle] <br>2. FILTER[#1, comparative:​=:​Brig:​col:​ship:​ship_type] <br>3.*(distinct)* PROJECT[col:​battle:​id, #2] <br>4. PROJECT[col:​battle:​name, #2] <br>5. UNION[#3, #4] <br> | 1. SELECT[tbl:​battle] <br>2. PROJECT[tbl:​ship, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​Brig:​col:​ship:​ship_type] <br>4. COMPARATIVE[#1, #2, comparative:​=:​Brig:​col:​ship:​ship_type] <br>5. UNION[#3, #4] <br>6.*(distinct)* PROJECT[col:​ship:​id, #5] <br>7.*(distinct)* PROJECT[col:​battle:​name, #5] <br>8. UNION[#6, #7] <br> | - | medium | 
  | SPIDER_dev_499 | What are the ids and names of the battles that led to more than 10 people killed in total. | 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 | 1. SELECT[tbl:​battle] <br>2. PROJECT[col:​death:​killed, #1] <br>3. GROUP[col:​death:​killed, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​10:​col:​death:​killed] <br>5. PROJECT[col:​battle:​id, #4] <br>6. PROJECT[col:​battle:​name, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​battle] <br>2. PROJECT[col:​death:​id, #1] <br>3. GROUP[sum, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>:​10:​col:​death:​id] <br>5. PROJECT[col:​battle:​id, #4] <br>6. PROJECT[col:​battle:​name, #4] <br>7. UNION[#5, #6] <br> | - | hard | 
  | SPIDER_dev_500 | What is the ship id and name that caused most total injuries? | 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 | 1. SELECT[tbl:​ship] <br>2. SELECT[tbl:​death] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​max:​None] <br>5. PROJECT[col:​ship:​id, #4] <br>6. PROJECT[col:​ship:​name, #4] <br>7. UNION[#5, #6] <br> | 1. SELECT[tbl:​ship] <br>2. PROJECT[col:​death:​injured, #1] <br>3. GROUP[sum, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br>5. PROJECT[col:​ship:​id, #4] <br>6. PROJECT[col:​ship:​name, #4] <br>7. UNION[#5, #6] <br> | - | extra | 
  | SPIDER_dev_501 | What are the distinct battle names which are between bulgarian commander 'Kaloyan' and latin commander 'Baldwin I'? | SELECT name FROM battle WHERE bulgarian_commander  =  'Kaloyan' AND latin_commander  =  'Baldwin I' | 1. SELECT[tbl:​battle] <br>2. FILTER[#1, comparative:​=:​Kaloyan:​col:​battle:​bulgarian_commander] <br>3. FILTER[#2, comparative:​=:​Baldwin I:​col:​battle:​latin_commander] <br>4. PROJECT[col:​battle:​name, #3] <br> | 1. SELECT[tbl:​battle] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Kaloyan:​col:​battle:​bulgarian_commander] <br>3. COMPARATIVE[#2, #2, comparative:​=:​Baldwin I:​col:​battle:​latin_commander] <br>4.*(distinct)* PROJECT[col:​battle:​name, #3] <br> | + | medium | 
  | SPIDER_dev_502 | How many different results are there for the battles? | SELECT count(DISTINCT RESULT) FROM battle | 1. SELECT[tbl:​battle] <br>2.*(distinct)* PROJECT[col:​battle:​result, #1] <br>3. AGGREGATE[count, #2] <br> | 1. SELECT[tbl:​battle] <br>2. PROJECT[col:​battle:​result, #1] <br>3.*(distinct)* PROJECT[None, #2] <br>4. AGGREGATE[count, #3] <br> | + | easy | 
  | SPIDER_dev_504 | List the name and date the battle that has lost the ship named 'Lettice' and the ship named 'HMS Atalanta' | 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' | 1. SELECT[tbl:​battle] <br>2. PROJECT[tbl:​ship, #1] <br>3. PROJECT[col:​ship:​name, #2] <br>4. COMPARATIVE[#1, #3, comparative:​=:​Lettice:​col:​ship:​name] <br>5. COMPARATIVE[#1, #3, comparative:​=:​HMS Atalanta:​col:​ship:​name] <br>6. INTERSECTION[#1, #4, #5] <br>7. PROJECT[col:​battle:​name, #6] <br>8. PROJECT[col:​battle:​date, #6] <br>9. UNION[#7, #8] <br> | 1. SELECT[tbl:​ship] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Lettice:​col:​ship:​name] <br>3. COMPARATIVE[#1, #1, comparative:​=:​HMS Atalanta:​col:​ship:​name] <br>4. PROJECT[col:​battle:​name, #2] <br>5. PROJECT[col:​battle:​date, #2] <br>6. PROJECT[col:​battle:​date, #3] <br>7. UNION[#4, #5] <br> | + | extra | 
  | SPIDER_dev_506 | What are the notes of the death events which has substring 'East'? | SELECT note FROM death WHERE note LIKE '%East%' | 1. SELECT[tbl:​death] <br>2. FILTER[#1, comparative:​like:​%East%:​col:​death:​note] <br>3. PROJECT[col:​death:​note, #2] <br> | 1. SELECT[tbl:​death] <br>2. PROJECT[col:​death:​killed, #1] <br>3. COMPARATIVE[#1, #2, comparative:​like:​East:​col:​death:​note] <br>4. PROJECT[col:​death:​note, #3] <br> | + | medium | 
 ***
 Exec acc: **0.6154**
