2023.10.26-SQL测试题

employee表：

department表：

job表：

location表：

题目及答案：

-- (1).查询工资大于一万的员工的姓名(first_name与last_name用“.”进行连接)和工资

--  select CONCAT(first_name,'.',last_name) as 姓名 ,salary
--  from employee
--  where salary>10000
-- 
-- commission _pct 译为“佣金百分比”
-- (2).查询佣金不为null的员工信息
--  SELECT *
--  FROM employee
--  WHERE commission_pct IS NOT NULL

-- (3).查询first_name中包含字母e的员工信息
-- SELECT *
-- FROM employee
-- WHERE first_name LIKE '%e%'

-- (4).查询first_name以K开头并且last_name中含有e的员工信息
-- SELECT *
-- FROM employee
-- WHERE first_name LIKE 'K%' and last_name like '%e%'

-- MySQL提供了LENGTH函数,可以查询字符串类型字段的长度。例如,查询表中字段名为“username”的长度
-- MySQL LENGTH(str)与CHAR_LENGTH(str)用法区别
-- ①  LENGTH(str)：返回字符串 str 的长度，以字节为单位。一个多字节字符计为多个字节。这意味着对于包含五个 2 字节字符的字符串，LENGTH()返回 10，这需要根据编码方式来判断每个字符所占的字节
-- ②  CHAR_LENGTH(str)：返回字符串 str 的长度，以代码点为单位。多字节字符计为单个代码点。对于包含两个 3 字节字符的字符串，LENGTH()返回 6，而 CHAR_LENGTH()返回 2。
-- (5).查询员工姓名长度大于等于10的员工姓名(first_name与last_name用“.”进行连接)及其长度
-- SELECT CONCAT(first_name,'.',last_name) '姓名',LENGTH(CONCAT(first_name,'.',last_name)) '姓名长度'
-- FROM employee
-- WHERE LENGTH(CONCAT(first_name,'.',last_name))>=10
-- 
-- 在MysqL中，我们可以使用ROUND函数来实现对数据的保留小数位数。该函数的语法如下：ROUND(X,D);其中，X表示要进行四舍五入的数值，D表示要保留的小数位数。
-- (6).查询平局工资在一万以上的部门的人数、总工资以及平均工资，并按照平均工资降序显示(平均工资保留小数点后两位)
-- SELECT COUNT(*) '部门人数',SUM(salary) '总工资',ROUND(AVG(salary),2) '平均工资'
-- FROM employee
-- GROUP BY department_id
-- HAVING AVG(salary)>10000
-- ORDER BY AVG(salary) DESC

-- (7).查询部门编号为90的部门名称以及员工的姓名和工资
-- SELECT department.department_name,CONCAT(first_name,'.',last_name) as 姓名,employee.salary
-- FROM employee,department 
-- WHERE employee.department_id=department.department_id and department.department_id=90
-- 
-- (8).显示所有员工的员工信息及其部门信息
-- SELECT *
-- FROM employee LEFT JOIN department ON employee.department_id=department.department_id

-- (9).显示员工及其领导的姓名和工资
-- SELECT CONCAT(e1.first_name,'.',e1.last_name) as '员工姓名',e1.salary '员工工资',CONCAT(e2.first_name,'.',e2.last_name) as '领导姓名',e2.salary as '领导工资'
-- FROM employee e1,employee e2
-- WHERE e1.manager_id=e2.employee_id

-- (10).显示职位是Programmer的员工信息
-- SELECT employee_id,CONCAT(first_name,'.',last_name)as '姓名',email,phone_number,employee.job_id,salary,commission_pct,manager_id,department_id
-- FROM employee,job
-- WHERE employee.job_id=job.job_id and job.job_title='Programmer'

-- (11).查询部门人数大于5人的部门名称
--  SELECT department_name
--  FROM employee,department
--  WHERE employee.department_id=department.department_id
--  GROUP BY department_name
--  HAVING COUNT(*)>5
-- 
-- 可能会出现部门名称一样的情况：直接通过部门名称分组会导致所属两个不同id的部门会分到一组，而distinct会导致筛选一个只保留一个，故需要利用子查询通过唯一索引id进行分组再显示部门名称
--  SELECT department_name
--  FROM department
--  WHERE department_id in (
--  	SELECT department_id
--  	FROM employee
--  	GROUP BY department_id
--  	HAVING COUNT(*)>5
--  )

-- 一直出现的误区：在子查询中WHERE条件一直使用的是emp.employee_id=employee.employee_id。实际上，这样筛选出来的只有一条记录
-- (12).查询高于部门平均工资的员工姓名及其工资
--  SELECT CONCAT(first_name,'.',last_name) as 姓名,salary
--  FROM employee emp
--  WHERE salary>(
--  	SELECT AVG(salary)
--  	FROM employee
--  	WHERE emp.department_id=employee.department_id
--  	GROUP BY employee.department_id
--  )
 

-- (13).显示各部们名称及其领导的姓名、职位名称
-- SELECT department_name '部门名称', CONCAT(first_name,'.',last_name) as 领导姓名, job_title '职位名称'
-- FROM department LEFT JOIN employee ON department.manager_id=employee.employee_id LEFT JOIN job ON employee.job_id=job.job_id
-- 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92