ECNU数据库作业——Lab2

Lab 2

查询题 1

List the name, born state and age of the heads of departments ordered by age.

SELECT `name`, born_state, age
FROM head
ORDER BY age;

查询题 2

What are the maximum and minimum budget of the departments?

SELECT MAX(Budget_in_Billions), MIN(Budget_in_Billions)
FROM department

查询题 3

What is the average number of employees of the departments whose rank is between 10 and 15?

SELECT ROUND(AVG(Num_Employees)) AS avg_employee
FROM department
WHERE Ranking >= 10 AND Ranking <= 15;

查询题 4

What are the distinct creation years of the departments managed by a secretary born in state ‘Alabama’?

SELECT DISTINCT department.Creation
FROM department
JOIN management ON management.department_ID = department.Department_ID
JOIN head ON head.head_ID = management.head_ID
WHERE head.born_state = 'Alabama';

查询题 5

What are the names of the states where at least 3 heads were born?

SELECT head.born_state 
FROM head
GROUP BY born_state
HAVING COUNT(*) >= 3;

查询题 6

Retrieve the names of each department along with their average budget, but only include departments whose average budget exceeds the overall average budget of all departments.

SELECT `Name`, Budget_in_Billions / Num_Employees AS avg_budget
FROM department
WHERE Budget_in_Billions / Num_Employees > (
	SELECT AVG(Budget_in_Billions / Num_Employees) 
	FROM department
);