Database: employee_hire_evaluation
Schema: CREATE TABLE "employee" ( "Employee_ID" int, "Name" text, "Age" int, "City" text, PRIMARY KEY ("Employee_ID") ); CREATE TABLE "shop" ( "Shop_ID" int, "Name" text, "Location" text, "District" text, "Number_products" int, "Manager_name" text, PRIMARY KEY ("Shop_ID") ); CREATE TABLE "hiring" ( "Shop_ID" int, "Employee_ID" int, "Start_from" text, "Is_full_time" bool, PRIMARY KEY ("Employee_ID"), FOREIGN KEY (`Shop_ID`) REFERENCES `shop`(`Shop_ID`), FOREIGN KEY (`Employee_ID`) REFERENCES `employee`(`Employee_ID`) ); CREATE TABLE "evaluation" ( "Employee_ID" text, "Year_awarded" text, "Bonus" real, PRIMARY KEY ("Employee_ID","Year_awarded"), FOREIGN KEY (`Employee_ID`) REFERENCES `employee`(`Employee_ID`) );

Question (easy): How many employees are there?
SQL: SELECT count(*) FROM employee

Question (easy): Count the number of employees
SQL: SELECT count(*) FROM employee

Question (easy): Sort employee names by their age in ascending order.
SQL: SELECT name FROM employee ORDER BY age

Question (easy): List the names of employees and sort in ascending order of age.
SQL: SELECT name FROM employee ORDER BY age

Question (medium): What is the number of employees from each city?
SQL: SELECT count(*) ,  city FROM employee GROUP BY city

Question (medium): Count the number of employees for each city.
SQL: SELECT count(*) ,  city FROM employee GROUP BY city

Question (medium): Which cities do more than one employee under age 30 come from?
SQL: SELECT city FROM employee WHERE age  <  30 GROUP BY city HAVING count(*)  >  1

Question (medium): Find the cities that have more than one employee under age 30.
SQL: SELECT city FROM employee WHERE age  <  30 GROUP BY city HAVING count(*)  >  1

Question (medium): Find the number of shops in each location.
SQL: SELECT count(*) ,  LOCATION FROM shop GROUP BY LOCATION

Question (medium): How many shops are there in each location?
SQL: SELECT count(*) ,  LOCATION FROM shop GROUP BY LOCATION

Question (medium): Find the manager name and district of the shop whose number of products is the largest.
SQL: SELECT manager_name ,  district FROM shop ORDER BY number_products DESC LIMIT 1

Question (medium): What are the manager name and district of the shop that sells the largest number of products?
SQL: SELECT manager_name ,  district FROM shop ORDER BY number_products DESC LIMIT 1

Question (medium): find the minimum and maximum number of products of all stores.
SQL: SELECT min(Number_products) ,  max(Number_products) FROM shop

Question (medium): What are the minimum and maximum number of products across all the shops?
SQL: SELECT min(Number_products) ,  max(Number_products) FROM shop

Question (medium): Return the name, location and district of all shops in descending order of number of products.
SQL: SELECT name ,  LOCATION ,  district FROM shop ORDER BY number_products DESC

Question (medium): Sort all the shops by number products in descending order, and return the name, location and district of each shop.
SQL: SELECT name ,  LOCATION ,  district FROM shop ORDER BY number_products DESC

Question (hard): Find the names of stores whose number products is more than the average number of products.
SQL: SELECT name FROM shop WHERE number_products  >  (SELECT avg(number_products) FROM shop)

Question (hard): Which shops' number products is above the average? Give me the shop names.
SQL: SELECT name FROM shop WHERE number_products  >  (SELECT avg(number_products) FROM shop)

Question (extra): find the name of employee who was awarded the most times in the evaluation.
SQL: 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

Question (extra): Which employee received the most awards in evaluations? Give me the employee name.
SQL: 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

Question (hard): Find the name of the employee who got the highest one time bonus.
SQL: 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

Question (hard): Which employee received the biggest bonus? Give me the employee name.
SQL: 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

Question (hard): Find the names of employees who never won any award in the evaluation.
SQL: SELECT name FROM employee WHERE Employee_ID NOT IN (SELECT Employee_ID FROM evaluation)

Question (hard): What are the names of the employees who never received any evaluation?
SQL: SELECT name FROM employee WHERE Employee_ID NOT IN (SELECT Employee_ID FROM evaluation)

Question (extra): What is the name of the shop that is hiring the largest number of employees?
SQL: 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

Question (extra): Which shop has the most employees? Give me the shop name.
SQL: 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

Question (hard): Find the name of the shops that do not hire any employee.
SQL: SELECT name FROM shop WHERE shop_id NOT IN (SELECT shop_id FROM hiring)

Question (hard): Which shops run with no employees? Find the shop names
SQL: SELECT name FROM shop WHERE shop_id NOT IN (SELECT shop_id FROM hiring)

Question (medium): Find the number of employees hired in each shop; show the shop name as well.
SQL: SELECT count(*) ,  t2.name FROM hiring AS t1 JOIN shop AS t2 ON t1.shop_id  =  t2.shop_id GROUP BY t2.name

Question (medium): For each shop, return the number of employees working there and the name of the shop.
SQL: SELECT count(*) ,  t2.name FROM hiring AS t1 JOIN shop AS t2 ON t1.shop_id  =  t2.shop_id GROUP BY t2.name

Question (easy): What is total bonus given in all evaluations?
SQL: SELECT sum(bonus) FROM evaluation

Question (easy): Find the total amount of bonus given in all the evaluations.
SQL: SELECT sum(bonus) FROM evaluation

Question (easy): Give me all the information about hiring.
SQL: SELECT * FROM hiring

Question (easy): What is all the information about hiring?
SQL: SELECT * FROM hiring

Question (hard): Which district has both stores with less than 3000 products and stores with more than 10000 products?
SQL: SELECT district FROM shop WHERE Number_products  <  3000 INTERSECT SELECT district FROM shop WHERE Number_products  >  10000

Question (hard): Find the districts in which there are both shops selling less than 3000 products and shops selling more than 10000 products.
SQL: SELECT district FROM shop WHERE Number_products  <  3000 INTERSECT SELECT district FROM shop WHERE Number_products  >  10000

Question (easy): How many different store locations are there?
SQL: SELECT count(DISTINCT LOCATION) FROM shop

Question (easy): Count the number of distinct store locations.
SQL: SELECT count(DISTINCT LOCATION) FROM shop