Skip to content

Latest commit

 

History

History
58 lines (57 loc) · 19.4 KB

employee_hire_evaluation_results.md

File metadata and controls

58 lines (57 loc) · 19.4 KB
Idx Table Column Primary Key Foreign Key
0 *
1 employee Employee_ID +
2 Name
3 Age
4 City
5 shop Shop_ID +
6 Name
7 Location
8 District
9 Number_products
10 Manager_name
11 hiring Shop_ID --> 5
12 Employee_ID + --> 1
13 Start_from
14 Is_full_time
15 evaluation Employee_ID + --> 1
16 Year_awarded
17 Bonus
Index Question SQL gold QDMR pred QDMR Exec SQL hardness
SPIDER_dev_260 Count the number of employees SELECT count(*) FROM employee 1. SELECT[tbl:​employee]
2. AGGREGATE[count, #1]
1. SELECT[tbl:​employee]
2. AGGREGATE[count, #1]
+ easy
SPIDER_dev_261 Sort employee names by their age in ascending order. SELECT name FROM employee ORDER BY age 1. SELECT[tbl:​employee]
2. PROJECT[col:​employee:​Name, #1]
3. PROJECT[col:​employee:​Age, #1]
4. SORT[#2, #3, sortdir:​ascending]
1. SELECT[tbl:​employee]
2. PROJECT[col:​employee:​Name, #1]
3. PROJECT[col:​employee:​Age, #1]
4. SORT[#2, #3, sortdir:​ascending]
+ easy
SPIDER_dev_262 List the names of employees and sort in ascending order of age. SELECT name FROM employee ORDER BY age 1. SELECT[tbl:​employee]
2. PROJECT[col:​employee:​Name, #1]
3. PROJECT[col:​employee:​Age, #1]
4. SORT[#2, #3, sortdir:​ascending]
1. SELECT[tbl:​employee]
2. PROJECT[col:​employee:​Name, #1]
3. PROJECT[col:​employee:​Age, #1]
4. SORT[#2, #3, sortdir:​ascending]
+ easy
SPIDER_dev_263 What is the number of employees from each city? SELECT city , count(*) FROM employee GROUP BY city 1. SELECT[col:​employee:​City]
2. PROJECT[tbl:​employee, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
1. SELECT[col:​employee:​City]
2. PROJECT[tbl:​employee, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
+ medium
SPIDER_dev_264 Count the number of employees for each city. SELECT city , count(*) FROM employee GROUP BY city 1. SELECT[col:​employee:​City]
2. PROJECT[tbl:​employee, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
1. SELECT[col:​employee:​City]
2. PROJECT[tbl:​employee, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
+ medium
SPIDER_dev_265 Which cities do more than one employee under age 30 come from? SELECT city FROM employee WHERE age < 30 GROUP BY city HAVING count(*) > 1 1. SELECT[col:​employee:​City]
2. PROJECT[tbl:​employee, #1]
3. PROJECT[col:​employee:​Age, #2]
4. COMPARATIVE[#2, #3, comparative:​<:​30:​col:​employee:​Age]
5. GROUP[count, #4, #1]
6. COMPARATIVE[#1, #5, comparative:​>:​1]
1. SELECT[col:​employee:​City]
2. PROJECT[tbl:​employee, #1]
3. COMPARATIVE[#2, #2, comparative:​<:​30:​col:​employee:​Age]
4. GROUP[count, #3, #1]
5. SUPERLATIVE[comparative:​max:​None, #1, #4]
+ medium
SPIDER_dev_266 Find the cities that have more than one employee under age 30. SELECT city FROM employee WHERE age < 30 GROUP BY city HAVING count(*) > 1 1. SELECT[col:​employee:​City]
2. PROJECT[tbl:​employee, #1]
3. PROJECT[col:​employee:​Age, #2]
4. COMPARATIVE[#2, #3, comparative:​<:​30:​col:​employee:​Age]
5. GROUP[count, #4, #1]
6. COMPARATIVE[#1, #5, comparative:​>:​1]
1. SELECT[col:​employee:​City]
2. PROJECT[tbl:​employee, #1]
3. COMPARATIVE[#2, #2, comparative:​<:​30:​col:​employee:​Age]
4. GROUP[count, #3, #1]
5. COMPARATIVE[#1, #4, comparative:​>:​1]
+ medium
SPIDER_dev_267 Find the number of shops in each location. SELECT LOCATION , count(*) FROM shop GROUP BY LOCATION 1. SELECT[col:​shop:​Location]
2. PROJECT[tbl:​shop, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
1. SELECT[col:​shop:​Location]
2. PROJECT[tbl:​shop, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
+ medium
SPIDER_dev_268 How many shops are there in each location? SELECT LOCATION , count(*) FROM shop GROUP BY LOCATION 1. SELECT[col:​shop:​Location]
2. PROJECT[tbl:​shop, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
1. SELECT[col:​shop:​Location]
2. PROJECT[tbl:​shop, #1]
3. GROUP[count, #2, #1]
4. UNION[#1, #3]
+ medium
SPIDER_dev_269 Find the manager name and district of the shop whose number of products is the largest. SELECT manager_name , district FROM shop ORDER BY number_products DESC LIMIT 1 1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. GROUP[col:​shop:​Number_products, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​shop:​Manager_name, #4]
6. PROJECT[col:​shop:​District, #4]
7. UNION[#5, #6]
1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. AGGREGATE[sum, #2]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​shop:​Manager_name, #4]
6. PROJECT[col:​shop:​District, #4]
7. UNION[#5, #6]
+ medium
SPIDER_dev_270 What are the manager name and district of the shop that sells the largest number of products? SELECT manager_name , district FROM shop ORDER BY number_products DESC LIMIT 1 1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. GROUP[col:​shop:​Number_products, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​shop:​Manager_name, #4]
6. PROJECT[col:​shop:​Manager_name, #5]
7. PROJECT[col:​shop:​District, #4]
8. UNION[#6, #7]
1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. GROUP[sum, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​shop:​Manager_name, #4]
6. PROJECT[col:​shop:​District, #4]
7. UNION[#5, #6]
+ medium
SPIDER_dev_271 find the minimum and maximum number of products of all stores. SELECT min(Number_products) , max(Number_products) FROM shop 1. SELECT[col:​shop:​Number_products]
2. FILTER[#1, tbl:​shop]
3. AGGREGATE[min, #2]
4. AGGREGATE[max, #2]
5. UNION[#3, #4]
1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. GROUP[sum, #2, #1]
4. AGGREGATE[min, #3]
5. AGGREGATE[max, #3]
6. UNION[#4, #5]
+ medium
SPIDER_dev_272 What are the minimum and maximum number of products across all the shops? SELECT min(Number_products) , max(Number_products) FROM shop 1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. GROUP[col:​shop:​Number_products, #2, #1]
4. AGGREGATE[min, #3]
5. AGGREGATE[max, #3]
6. UNION[#4, #5]
1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. AGGREGATE[sum, #2]
4. AGGREGATE[min, #3]
5. AGGREGATE[max, #3]
6. UNION[#4, #5]
+ medium
SPIDER_dev_273 Return the name, location and district of all shops in descending order of number of products. SELECT name , LOCATION , district FROM shop ORDER BY number_products DESC 1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Name, #1]
3. PROJECT[col:​shop:​Location, #1]
4. PROJECT[col:​shop:​District, #1]
5. PROJECT[col:​shop:​Number_products, #1]
6. GROUP[count, #5, #1]
7. UNION[#2, #3, #4]
8. SORT[#7, #6, sortdir:​descending]
1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Name, #1]
3. PROJECT[col:​shop:​Location, #1]
4. PROJECT[col:​shop:​District, #1]
5. PROJECT[col:​shop:​Number_products, #1]
6. GROUP[count, #5, #1]
7. UNION[#2, #3, #4]
8. SORT[#7, #6, sortdir:​descending]
+ medium
SPIDER_dev_274 Sort all the shops by number products in descending order, and return the name, location and district of each shop. SELECT name , LOCATION , district FROM shop ORDER BY number_products DESC 1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Name, #1]
3. PROJECT[col:​shop:​Location, #1]
4. PROJECT[col:​shop:​District, #1]
5. PROJECT[col:​shop:​Number_products, #1]
6. GROUP[count, #5, #1]
7. UNION[#2, #3, #4]
8. SORT[#7, #6, sortdir:​descending]
1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Name, #1]
3. GROUP[count, #2, #1]
4. PROJECT[col:​shop:​Name, #1]
5. PROJECT[col:​shop:​District, #1]
6. UNION[#3, #4, #5]
7. SORT[#6, #5, sortdir:​descending]
- medium
SPIDER_dev_275 Find the names of stores whose number products is more than the average number of products. SELECT name FROM shop WHERE number_products > (SELECT avg(number_products) FROM shop) 1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. GROUP[col:​shop:​Number_products, #2, #1]
4. AGGREGATE[avg, #3]
5. COMPARATIVE[#1, #3, comparative:​>:​#4]
6. PROJECT[col:​shop:​Name, #5]
1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. AGGREGATE[sum, #2]
4. AGGREGATE[avg, #3]
5. COMPARATIVE[#1, #1, col:​shop:​Number_products]
6. PROJECT[col:​shop:​Name, #5]
- hard
SPIDER_dev_276 Which shops' number products is above the average? Give me the shop names. SELECT name FROM shop WHERE number_products > (SELECT avg(number_products) FROM shop) 1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. GROUP[col:​shop:​Number_products, #2, #1]
4. AGGREGATE[avg, #3]
5. COMPARATIVE[#1, #3, comparative:​>:​#4]
6. PROJECT[col:​shop:​Name, #5]
1. SELECT[tbl:​shop]
2. PROJECT[col:​shop:​Number_products, #1]
3. GROUP[sum, #2, #1]
4. AGGREGATE[avg, #3]
5. COMPARATIVE[#1, #1, col:​shop:​Number_products]
6. PROJECT[col:​shop:​Name, #5]
- hard
SPIDER_dev_277 find the name of employee who was awarded the most times in the evaluation. SELECT t1.name FROM employee AS t1 JOIN evaluation AS t2 ON t1.Employee_ID = t2.Employee_ID GROUP BY t2.Employee_ID ORDER BY count(*) DESC LIMIT 1 1. SELECT[tbl:​employee]
2. PROJECT[tbl:​evaluation, #1]
3. GROUP[count, #2, #1]
4. COMPARATIVE[#1, #3, comparative:​max:​None]
5. PROJECT[col:​employee:​Name, #4]
1. SELECT[tbl:​employee]
2. PROJECT[tbl:​evaluation, #1]
3. GROUP[count, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​employee:​Name, #4]
+ extra
SPIDER_dev_278 Which employee received the most awards in evaluations? Give me the employee name. SELECT t1.name FROM employee AS t1 JOIN evaluation AS t2 ON t1.Employee_ID = t2.Employee_ID GROUP BY t2.Employee_ID ORDER BY count(*) DESC LIMIT 1 1. SELECT[tbl:​employee]
2. PROJECT[tbl:​evaluation, #1]
3. PROJECT[awards in #REF, #2]
4. GROUP[count, #3, #1]
5. SUPERLATIVE[comparative:​max:​None, #1, #4]
6. PROJECT[col:​employee:​Name, #5]
1. SELECT[tbl:​employee]
2. PROJECT[tbl:​evaluation, #1]
3. GROUP[count, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​employee:​Name, #4]
+ extra
SPIDER_dev_279 Find the name of the employee who got the highest one time bonus. SELECT t1.name FROM employee AS t1 JOIN evaluation AS t2 ON t1.Employee_ID = t2.Employee_ID ORDER BY t2.bonus DESC LIMIT 1 1. SELECT[tbl:​employee]
2. PROJECT[col:​evaluation:​Bonus, #1]
3. SUPERLATIVE[comparative:​max:​None, #1, #2]
4. PROJECT[col:​employee:​Name, #3]
1. SELECT[tbl:​employee]
2. PROJECT[col:​evaluation:​Bonus, #1]
3. SUPERLATIVE[comparative:​max:​None, #1, #2]
4. PROJECT[col:​employee:​Name, #3]
+ hard
SPIDER_dev_280 Which employee received the biggest bonus? Give me the employee name. SELECT t1.name FROM employee AS t1 JOIN evaluation AS t2 ON t1.Employee_ID = t2.Employee_ID ORDER BY t2.bonus DESC LIMIT 1 1. SELECT[tbl:​employee]
2. PROJECT[col:​evaluation:​Bonus, #1]
3. COMPARATIVE[#1, #2, comparative:​max:​None]
4. PROJECT[col:​employee:​Name, #3]
1. SELECT[tbl:​employee]
2. PROJECT[col:​evaluation:​Bonus, #1]
3. SUPERLATIVE[comparative:​max:​None, #1, #2]
4. PROJECT[col:​employee:​Name, #3]
+ hard
SPIDER_dev_281 Find the names of employees who never won any award in the evaluation. SELECT name FROM employee WHERE Employee_ID NOT IN (SELECT Employee_ID FROM evaluation) 1. SELECT[tbl:​employee]
2. FILTER[#1, tbl:​evaluation]
3. DISCARD[#1, #2]
4. PROJECT[col:​employee:​Name, #3]
1. SELECT[tbl:​employee]
2. COMPARATIVE[#1, #1, tbl:​evaluation]
3. DISCARD[#1, #2]
4. PROJECT[col:​employee:​Name, #3]
+ hard
SPIDER_dev_283 What is the name of the shop that is hiring the largest number of employees? SELECT t2.name FROM hiring AS t1 JOIN shop AS t2 ON t1.shop_id = t2.shop_id GROUP BY t1.shop_id ORDER BY count(*) DESC LIMIT 1 1. SELECT[tbl:​shop]
2. PROJECT[tbl:​hiring, #1]
3. GROUP[count, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​shop:​Name, #4]
1. SELECT[tbl:​shop]
2. PROJECT[tbl:​hiring, #1]
3. GROUP[count, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​shop:​Name, #4]
+ extra
SPIDER_dev_284 Which shop has the most employees? Give me the shop name. SELECT t2.name FROM hiring AS t1 JOIN shop AS t2 ON t1.shop_id = t2.shop_id GROUP BY t1.shop_id ORDER BY count(*) DESC LIMIT 1 1. SELECT[col:​hiring:​Shop_ID]
2. PROJECT[employees, #1]
3. GROUP[count, #2, #1]
4. COMPARATIVE[#1, #3, comparative:​max:​None]
5. PROJECT[col:​shop:​Name, #4]
1. SELECT[tbl:​shop]
2. PROJECT[tbl:​employee, #1]
3. GROUP[count, #2, #1]
4. SUPERLATIVE[comparative:​max:​None, #1, #3]
5. PROJECT[col:​shop:​Name, #4]
+ extra
SPIDER_dev_285 Find the name of the shops that do not hire any employee. SELECT name FROM shop WHERE shop_id NOT IN (SELECT shop_id FROM hiring) 1. SELECT[tbl:​shop]
2. FILTER[#1, tbl:​hiring]
3. DISCARD[#1, #2]
4. PROJECT[col:​shop:​Name, #3]
1. SELECT[tbl:​shop]
2. COMPARATIVE[#1, #1, tbl:​hiring]
3. DISCARD[#1, #2]
4. PROJECT[col:​shop:​Name, #3]
+ hard
SPIDER_dev_287 Find the number of employees hired in each shop; show the shop name as well. SELECT count(*) , t2.name FROM hiring AS t1 JOIN shop AS t2 ON t1.shop_id = t2.shop_id GROUP BY t2.name 1. SELECT[col:​shop:​Name]
2. PROJECT[tbl:​hiring, #1]
3. GROUP[count, #2, #1]
4. PROJECT[col:​shop:​Name, #1]
5. UNION[#3, #4]
1. SELECT[col:​shop:​Shop_ID]
2. PROJECT[tbl:​hiring, #1]
3. GROUP[count, #2, #1]
4. PROJECT[col:​shop:​Name, #1]
5. UNION[#4, #3]
+ medium
SPIDER_dev_288 For each shop, return the number of employees working there and the name of the shop. SELECT count(*) , t2.name FROM hiring AS t1 JOIN shop AS t2 ON t1.shop_id = t2.shop_id GROUP BY t2.name 1. SELECT[col:​shop:​Name]
2. PROJECT[tbl:​hiring, #1]
3. GROUP[count, #2, #1]
4. PROJECT[col:​shop:​Name, #1]
5. UNION[#3, #4]
1. SELECT[col:​shop:​Name]
2. PROJECT[tbl:​hiring, #1]
3. GROUP[count, #2, #1]
4. PROJECT[col:​shop:​Name, #1]
5. UNION[#3, #4]
+ medium
SPIDER_dev_289 What is total bonus given in all evaluations? SELECT sum(bonus) FROM evaluation 1. SELECT[tbl:​evaluation]
2. PROJECT[col:​evaluation:​Bonus, #1]
3. AGGREGATE[sum, #2]
1. SELECT[tbl:​evaluation]
2. PROJECT[col:​evaluation:​Bonus, #1]
3. AGGREGATE[sum, #2]
+ easy
SPIDER_dev_290 Find the total amount of bonus given in all the evaluations. SELECT sum(bonus) FROM evaluation 1. SELECT[tbl:​evaluation]
2. PROJECT[col:​evaluation:​Bonus, #1]
3. AGGREGATE[sum, #2]
1. SELECT[tbl:​evaluation]
2. PROJECT[col:​evaluation:​Bonus, #1]
3. AGGREGATE[sum, #2]
+ easy
SPIDER_dev_293 Which district has both stores with less than 3000 products and stores with more than 10000 products? SELECT district FROM shop WHERE Number_products < 3000 INTERSECT SELECT district FROM shop WHERE Number_products > 10000 1. SELECT[col:​shop:​District]
2. PROJECT[tbl:​shop, #1]
3. PROJECT[col:​shop:​Number_products, #2]
4. GROUP[col:​shop:​Number_products, #3, #2]
5. COMPARATIVE[#1, #4, comparative:​<:​3000:​col:​shop:​Number_products]
6. COMPARATIVE[#1, #4, comparative:​>:​10000:​col:​shop:​Number_products]
7. INTERSECTION[#1, #5, #6]
1. SELECT[col:​shop:​District]
2. PROJECT[tbl:​shop, #1]
3. PROJECT[col:​shop:​Number_products, #2]
4. COMPARATIVE[#1, #3, comparative:​<:​3000:​col:​evaluation:​Bonus]
5. COMPARATIVE[#1, #3, comparative:​>:​10000:​col:​shop:​Number_products]
6. INTERSECTION[#1, #4, #5]
+ hard
SPIDER_dev_294 Find the districts in which there are both shops selling less than 3000 products and shops selling more than 10000 products. SELECT district FROM shop WHERE Number_products < 3000 INTERSECT SELECT district FROM shop WHERE Number_products > 10000 1. SELECT[col:​shop:​District]
2. PROJECT[tbl:​shop, #1]
3. PROJECT[col:​shop:​Number_products, #2]
4. GROUP[col:​shop:​Number_products, #3, #2]
5. COMPARATIVE[#1, #4, comparative:​<:​3000:​col:​shop:​Number_products]
6. COMPARATIVE[#1, #4, comparative:​>:​10000:​col:​shop:​Number_products]
7. INTERSECTION[#1, #5, #6]
1. SELECT[col:​shop:​District]
2. PROJECT[tbl:​shop, #1]
3. PROJECT[col:​shop:​Number_products, #2]
4. COMPARATIVE[#1, #3, comparative:​<:​3000:​col:​evaluation:​Bonus]
5. COMPARATIVE[#1, #3, comparative:​>:​10000:​col:​shop:​Number_products]
6. INTERSECTION[#1, #4, #5]
+ hard
SPIDER_dev_295 How many different store locations are there? SELECT count(DISTINCT LOCATION) FROM shop 1. SELECT[tbl:​shop]
2.(distinct) PROJECT[col:​shop:​Location, #1]
3. AGGREGATE[count, #2]
1. SELECT[col:​shop:​Location]
2.(distinct) PROJECT[None, #1]
3. AGGREGATE[count, #2]
+ easy
SPIDER_dev_296 Count the number of distinct store locations. SELECT count(DISTINCT LOCATION) FROM shop 1. SELECT[tbl:​shop]
2.(distinct) PROJECT[col:​shop:​Location, #1]
3. AGGREGATE[count, #2]
1. SELECT[col:​shop:​Location]
2.(distinct) PROJECT[None, #1]
3. AGGREGATE[count, #2]
+ easy

Exec acc: 0.9091