select section_name from sections where section_id =60;
results
section_name
--------------
IT
(1row)
3. 查询工资最高的五名员工,返回员工编号及员工姓名
SQL code
SELECT staff_id, first_name ||' '|| last_name as full_name
FROM staffs
ORDERBY salary DESCLIMIT5;
results
staff_id | full_name
----------+-------------------100| Steven King
101| Neena Kochhar
102| Lex De Haan
201| Michael Hartstein
205| Shelley Higgins
(5rows)
4. 查询编号为201员工的部门经理编号及其姓名
SQL code
SELECT s.manager_id, m.first_name ||' '|| m.last_name as manager_name
FROM staffs s
INNERJOIN staffs m ON s.manager_id = m.staff_id
WHERE s.staff_id =201;
results
manager_id | manager_name
------------+--------------100| Steven King
(1row)
5. 查询工资差距最大的职位,返回职位编号,和职位名称
法1
SQL code
SELECT employment_id, employment_title
FROM employments
ORDERBY(max_salary - min_salary)DESCLIMIT1;
results
employment_id | employment_title
---------------+------------------
AD_PRES | President
(1row)
法2
SQL code
SELECT employment_id, employment_title
FROM employments
WHERE max_salary-min_salary in(SELECTMAX(max_salary-min_salary)FROM employments);
results
employment_id | employment_title
---------------+------------------
AD_PRES | President
(1row)
6. 查询各部门工资最高的员工姓名,及其对应的部门名称
SQL code
SELECT d.section_name, s.first_name ||' '|| s.last_name AS full_name
FROM(SELECT section_id,MAX(salary)AS max_salary
FROM staffs
GROUPBY section_id
) m
INNERJOIN staffs s ON m.section_id = s.section_id AND m.max_salary = s.salary
INNERJOIN sections d ON s.section_id = d.section_id;
results
section_name | full_name
------------------+-------------------
Administration | Jennifer Whalen
Marketing | Michael Hartstein
Human Resources | Susan Mavris
Shipping | Douglas Grant
Shipping | Donald OConnell
IT | Alexander Hunold
Public Relations | Hermann Baer
Executive | Steven King
Finance | Nancy Greenberg
Accounting | Shelley Higgins
(10rows)
SELECT staff_id
FROM employment_history
WHERE employment_id IN('AC_ACCOUNT','AC_MGR')GROUPBY staff_id
HAVINGCOUNT(DISTINCT employment_id)=2;
results
staff_id
----------101(1row)
8. 查询雇佣历史表中员工的雇佣时长,返回并显示如下信息:员工编号,职位编号,部门编号,雇佣时长
SQL code
SELECT staff_id, employment_id, section_id,(end_date-start_date)AS employment_time
FROM employment_history;
results
staff_id | employment_id | section_id | employment_time
----------+---------------+------------+-----------------102| IT_PROG |60|2018 days
101| AC_ACCOUNT |110|1497 days
101| AC_MGR |110|1234 days
201| MK_REP |20|1401 days
114| ST_CLERK |50|647 days
122| ST_CLERK |50|364 days
200| AD_ASST |90|2100 days
176| SA_REP |80|647 days
176| SA_MAN |80|364 days
200| AC_ACCOUNT |90|1644 days
(10rows)
9. 查询在城市(city)South San Francisco工作的员工编号和员工姓名,按工资降序排列
SQL code
SELECT staff_id, first_name ||' '|| last_name AS full_name, salary
FROM staffs
WHERE section_id IN(SELECT section_id
FROM sections
WHERE place_id IN(SELECT place_id FROM places WHERE city ='South San Francisco'))ORDERBY salary DESC;
results
staff_id | full_name | salary
----------+-----------------+---------199| Douglas Grant|2600.00198| Donald OConnell |2600.00
10. 查询员工平均工资在5000以上的部门,返回部门编号及部门名称
SQL code
SELECT s.section_id, s.section_name
FROM(SELECT section_id,AVG(salary)AS avg_salary
FROM staffs
GROUPBY section_id
HAVINGAVG(salary)>5000) m
INNERJOIN sections s ON m.section_id = s.section_id;
results
section_id | section_name
------------+------------------20| Marketing
40| Human Resources
60| IT
70|Public Relations
90| Executive
100| Finance
110| Accounting
(7rows)
11. 查询last_name 以 字母F开头的员工,返回员工编号和姓名
SQL code
SELECT staff_id, first_name ||' '|| last_name AS full_name
FROM staffs
WHERE last_name LIKE'F%';
results
staff_id | full_name
----------+---------------202| Pat Fay
109| Daniel Faviet
(2rows)
12. 查询雇佣历史表中在两个及以上不同职位工作过的员工,返回员工编号
SQL code
SELECT staff_id
FROM employment_history
GROUPBY staff_id
HAVINGCOUNT(DISTINCT employment_id)>=2;
results
staff_id
----------101176200(3rows)
13. 查询各个国家办事处的数量
SQL code
SELECT s.state_name, m.count
FROM(SELECT state_id,COUNT(place_id)AS count
FROM places
GROUPBY state_id) m
INNERJOIN states s ON m.state_id = s.state_id
ORDERBY m.count DESC;
results
state_name | count
--------------------------+-------
United States of America |4
United Kingdom |3
Italy |2
Japan |2
Canada |2
Switzerland |2
Australia |1
Netherlands |1
Brazil |1
Germany |1
Mexico |1
Singapore |1
India |1
China |1(14rows)