Database: employee_hire_evaluation

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: How many employees are there?
SQL: SELECT count(*) FROM employee

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

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

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

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

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

Question: 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: 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: Find the number of shops in each location.
SQL: SELECT count(*) ,  LOCATION FROM shop GROUP BY LOCATION

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

Question: 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: 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: find the minimum and maximum number of products of all stores.
SQL: SELECT min(Number_products) ,  max(Number_products) FROM shop

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

Question: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: What is total bonus given in all evaluations?
SQL: SELECT sum(bonus) FROM evaluation

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

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

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

Question: 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: 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: How many different store locations are there?
SQL: SELECT count(DISTINCT LOCATION) FROM shop

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