SQL高级查询03

SQL查询语句的下载脚本链接！！！

【免费】SQL练习资源-具体练习操作可以查看我发布的文章资源-CSDN文库编辑https://download.csdn.net/download/Z0412_J0103/89908378https://download.csdn.net/download/Z0412_J0103/89908378

1 查询employees表中前10行记录

SELECT employee_id, first_name, salary

FROM employees

LIMIT 10;

展示结果：

2 查询employees表中第1行记录开始后的10行记录

SELECT employee_id, first_name, salary

FROM employees

LIMIT 0,10;

展示结果：

3 查询employees表中部门id为50的薪资排名前三的员工记录

SELECT first_name, salary, department_id

FROM employees

WHERE department_id = 50

ORDER BY salary DESC

LIMIT 3;

展示结果：

4 查询employees表中第11行记录开始后的10行记录

SELECT employee_id, first_name, salary

FROM employees

LIMIT 10,10;

展示结果：

5 查询员工信息，并附带显示部门名

SELECT e.employee_id, e.first_name, e.department_id, d.department_name

FROM employees e,

departments d

WHERE e.department_id = d.department_id

ORDER BY employee_id;

展示结果：

6 查询部门以及部门所在的城市(部门表的location_id和locations表的location_id关联)

SELECT d.department_id, d.department_name, l.city

FROM departments d,

locations l

WHERE d.location_id = l.location_id;

展示结果：

7 查询部门以及部门经理(部门表的manager_id和员工表的employee_id关联)

SELECT d.department_name, e.first_name

FROM departments d,

employees e

WHERE d.manager_id = e.employee_id;

展示结果：

8 查询员工和员工的主管名(employee表的manager_id和employee表的employee_id关联)

SELECT e1.employee_id 员工ID

, e1.first_name 员工姓名

, e2.employee_id 上级ID

, e2.first_name 上级姓名

FROM employees e1,

employees e2

WHERE e1.manager_id = e2.employee_id;

展示结果：

9 查询107个员工并显示部门名(将符合条件的记录保留外,还会将左边的表的不符合条件的记录保留)

SELECT e.employee_id, e.first_name, e.department_id, d.department_name

FROM employees e

LEFT JOIN departments d

ON (e.department_id = d.department_id)

ORDER BY employee_id;

SELECT e.employee_id, e.first_name, e.department_id, d.department_nameFROM departments d

RIGHT JOIN employees e

ON (e.department_id = d.department_id)

ORDER BY employee_id;

展示结果：

10 查询27个部门，有经理显示经理，没有经理显示NULL

SELECT d.department_name, e.first_name

FROM departments d

LEFT JOIN employees e

ON d.manager_id = e.employee_id;

展示结果：

11 查询107个员工，显示主管名

SELECT e1.employee_id 员工ID

, e1.first_name 员工姓名

, e2.employee_id 上级ID

, e2.first_name 上级姓名

FROM employees e1

LEFT JOIN employees e2

ON e1.manager_id = e2.employee_id;

展示结果：

12 107个员工，显示部门名

SELECT e.employee_id 员工ID,

e.first_name 员工名,

e.department_id 部门ID,

d.department_name 部门名

FROM employees e

LEFT JOIN departments d

ON (e.department_id = d.department_id)

ORDER BY employee_id;

展示结果：

13 107个员工，显示城市

SELECT e.employee_id 员工ID,

e.first_name 员工名,

e.department_id 部门ID,

d.department_name 部门名,

d.location_id 区域ID,

l.city 城市

FROM employees e

LEFT JOIN departments d

ON (e.department_id = d.department_id)

LEFT JOIN locations l

ON (d.location_id = l.location_id)

ORDER BY employee_id;