 | Idx | Table      | Column | Primary Key | Foreign Key | 
 | ----------- | ----------- | ----------- | ----------- | ----------- | 
  | 0 |  | * |   |   | 
 | 1 | **AREA_CODE_STATE** | area_code | + |   | 
 | 2 |   | state |   |   | 
 | 3 | **CONTESTANTS** | contestant_number | + |   | 
 | 4 |   | contestant_name |   |   | 
 | 5 | **VOTES** | vote_id | + |   | 
 | 6 |   | phone_number |   |   | 
 | 7 |   | state |   | --> 2 | 
 | 8 |   | contestant_number |   | --> 3 | 
 | 9 |   | created |   |   | 
 
  | Index | Question  | SQL | gold QDMR | pred QDMR | Exec | SQL hardness |
  | ----------- | ----------- | ----------- |  ----------- | ----------- | ----------- | ----------- | 
 | SPIDER_dev_687 | How many states are there? | SELECT count(*) FROM area_code_state |  | 1. SELECT[col:​AREA_CODE_STATE:​state] <br>2. AGGREGATE[count, #1] <br> | - | easy | 
  | SPIDER_dev_688 | List the contestant numbers and names, ordered by contestant name descending. | SELECT contestant_number ,  contestant_name FROM contestants ORDER BY contestant_name DESC |  | 1. SELECT[tbl:​CONTESTANTS] <br>2. PROJECT[col:​CONTESTANTS:​contestant_number, #1] <br>3. PROJECT[col:​CONTESTANTS:​contestant_name, #1] <br>4. UNION[#2, #3] <br>5. SORT[#4, #3, sortdir:​descending] <br> | + | medium | 
  | SPIDER_dev_689 | List the vote ids, phone numbers and states of all votes. | SELECT vote_id ,  phone_number ,  state FROM votes |  | 1. SELECT[tbl:​VOTES] <br>2. PROJECT[col:​VOTES:​vote_id, #1] <br>3. PROJECT[col:​VOTES:​phone_number, #1] <br>4. PROJECT[col:​VOTES:​state, #1] <br>5. UNION[#2, #3, #4] <br> | + | medium | 
  | SPIDER_dev_690 | What are the maximum and minimum values of area codes? | SELECT max(area_code) ,  min(area_code) FROM area_code_state |  | 1. SELECT[tbl:​AREA_CODE_STATE] <br>2. PROJECT[col:​AREA_CODE_STATE:​area_code, #1] <br>3. AGGREGATE[max, #2] <br>4. AGGREGATE[min, #2] <br>5. UNION[#3, #4] <br> | + | medium | 
  | SPIDER_dev_691 | What is last date created of votes from the state 'CA'? | SELECT max(created) FROM votes WHERE state  =  'CA' |  | 1. SELECT[tbl:​VOTES] <br>2. PROJECT[col:​VOTES:​state, #1] <br>3. COMPARATIVE[#1, #2, comparative:​=:​CA:​col:​VOTES:​state] <br>4. PROJECT[col:​VOTES:​created, #3] <br> | - | easy | 
  | SPIDER_dev_692 | What are the names of the contestants whose names are not 'Jessie Alloway' | SELECT contestant_name FROM contestants WHERE contestant_name != 'Jessie Alloway' |  | 1. SELECT[tbl:​CONTESTANTS] <br>2. PROJECT[col:​CONTESTANTS:​contestant_name, #1] <br>3. COMPARATIVE[#1, #2, comparative:​!=:​Jessie Alloway:​col:​CONTESTANTS:​contestant_name] <br>4. PROJECT[col:​CONTESTANTS:​contestant_name, #3] <br> | + | easy | 
  | SPIDER_dev_693 | What are the distinct states and create time of all votes? | SELECT DISTINCT state ,  created FROM votes |  | 1. SELECT[tbl:​VOTES] <br>2.*(distinct)* PROJECT[col:​VOTES:​state, #1] <br>3. PROJECT[col:​VOTES:​created, #1] <br>4. UNION[#2, #3] <br> | + | medium | 
  | SPIDER_dev_694 | What are the contestant numbers and names of the contestants who had at least two votes? | SELECT T1.contestant_number , T1.contestant_name FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number GROUP BY T1.contestant_number HAVING count(*)  >=  2 |  | 1. SELECT[tbl:​CONTESTANTS] <br>2. PROJECT[tbl:​VOTES, #1] <br>3. GROUP[count, #2, #1] <br>4. COMPARATIVE[#1, #3, comparative:​>=:​2] <br>5. PROJECT[col:​CONTESTANTS:​contestant_number, #4] <br>6. PROJECT[col:​CONTESTANTS:​contestant_name, #4] <br>7. UNION[#5, #6] <br> | + | medium | 
  | SPIDER_dev_695 | Of all the contestants who got voted, what is the contestant number and name of the contestant who got least votes? | SELECT T1.contestant_number , T1.contestant_name FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number GROUP BY T1.contestant_number ORDER BY count(*) ASC LIMIT 1 |  | 1. SELECT[tbl:​CONTESTANTS] <br>2. SELECT[tbl:​VOTES] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​min:​None, #1, #3] <br>5. AGGREGATE[sum, #4] <br>6. PROJECT[col:​CONTESTANTS:​contestant_name, #4] <br>7. UNION[#5, #6] <br> | + | extra | 
  | SPIDER_dev_696 | What are the number of votes from state 'NY' or 'CA'? | SELECT count(*) FROM votes WHERE state  =  'NY' OR state  =  'CA' |  | 1. SELECT[tbl:​VOTES] <br>2. COMPARATIVE[#1, #1, comparative:​=:​NY:​col:​VOTES:​state] <br>3. COMPARATIVE[#1, #1, comparative:​=:​CA:​col:​VOTES:​state] <br>4. UNION[#2, #3] <br>5. AGGREGATE[count, #4] <br> | + | medium | 
  | SPIDER_dev_697 | How many contestants did not get voted? | SELECT count(*) FROM contestants WHERE contestant_number NOT IN ( SELECT contestant_number FROM votes ) |  | 1. SELECT[tbl:​CONTESTANTS] <br>2. COMPARATIVE[#1, #1, tbl:​VOTES] <br>3. DISCARD[#1, #2] <br>4. AGGREGATE[count, #3] <br> | + | extra | 
  | SPIDER_dev_698 | What is the area code in which the most voters voted? | SELECT T1.area_code FROM area_code_state AS T1 JOIN votes AS T2 ON T1.state  =  T2.state GROUP BY T1.area_code ORDER BY count(*) DESC LIMIT 1 |  | 1. SELECT[col:​AREA_CODE_STATE:​area_code] <br>2. PROJECT[tbl:​VOTES, #1] <br>3. GROUP[count, #2, #1] <br>4. SUPERLATIVE[comparative:​max:​None, #1, #3] <br> | + | extra | 
  | SPIDER_dev_699 | What are the create dates, states, and phone numbers of the votes that were for the contestant named 'Tabatha Gehling'? | SELECT T2.created ,  T2.state ,  T2.phone_number FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number WHERE T1.contestant_name  =  'Tabatha Gehling' |  | 1. SELECT[tbl:​CONTESTANTS] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Tabatha Gehling:​col:​CONTESTANTS:​contestant_name] <br>3. PROJECT[tbl:​VOTES, #2] <br>4. PROJECT[col:​VOTES:​created, #3] <br>5. PROJECT[col:​VOTES:​state, #3] <br>6. PROJECT[col:​VOTES:​phone_number, #3] <br>7. UNION[#4, #5, #6] <br> | + | medium | 
  | SPIDER_dev_700 | List the area codes in which voters voted both for the contestant 'Tabatha Gehling' and the contestant 'Kelly Clauss'. | SELECT T3.area_code FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number JOIN area_code_state AS T3 ON T2.state  =  T3.state WHERE T1.contestant_name  =  'Tabatha Gehling' INTERSECT SELECT T3.area_code FROM contestants AS T1 JOIN votes AS T2 ON T1.contestant_number  =  T2.contestant_number JOIN area_code_state AS T3 ON T2.state  =  T3.state WHERE T1.contestant_name  =  'Kelly Clauss' |  | 1. SELECT[tbl:​CONTESTANTS] <br>2. COMPARATIVE[#1, #1, comparative:​=:​Tabatha Gehling:​col:​CONTESTANTS:​contestant_name] <br>3. COMPARATIVE[#1, #1, comparative:​=:​Kelly Clauss:​col:​CONTESTANTS:​contestant_name] <br>4. PROJECT[col:​AREA_CODE_STATE:​area_code, #1] <br>5. INTERSECTION[#4, #2, #3] <br> | + | extra | 
  | SPIDER_dev_701 | Return the names of the contestants whose names contain the substring 'Al'. | select contestant_name from contestants where contestant_name like "%al%" |  | 1. SELECT[tbl:​CONTESTANTS] <br>2. PROJECT[col:​CONTESTANTS:​contestant_name, #1] <br>3. COMPARATIVE[#2, #2, comparative:​like:​AL:​col:​AREA_CODE_STATE:​state] <br> | - | medium | 
 ***
 Exec acc: **0.8000**
