MySQL基础20题(续前面的20题)

爱新觉罗买买提

写在前面

今天继续前面的20题之后的练习，数据都是一样的，可以直接使用，来看看你的sql功底降了没。
基础20题

1. #1.查询每个员⼯的姓名、邮箱、职位名称以及所在部⻔名称。
2.         SELECT
3.         CONCAT(last_name,first_name) as 姓名,
4.         email,
5.         job_title,
6.         department_name
7. FROM
8.         employees e
9.         JOIN departments d ON e.department_id=d.department_id
10.         JOIN jobs j ON e.job_id = j.job_id ;
12. #2.查询⽉薪最⾼的员⼯的姓名、职位名称以及⽉薪。
13.         SELECT
14.         CONCAT(last_name,first_name) as 姓名,
15.         job_title,
16.         salary
17. FROM
18.         employees e
19. JOIN jobs j on e.job_id = j.job_id
20. WHERE        salary = (SELECT MAX(salary) FROM employees);
22. #3.查询每个部⻔的平均⽉薪。
23. #注：如果某一部门没有员工不会显示
24.         SELECT
25.         department_name,
26.         AVG(salary)
27. FROM
28.         employees e
29. JOIN departments d ON e.department_id = d.department_id
30. GROUP BY
31.         department_name;   
33. #4.查询部⻔中员⼯⽉薪⾼于5000的所有员⼯的姓名、⽉薪以及所在部⻔名称。
34.         SELECT
35.         CONCAT(last_name,first_name) as 姓名,
36.         salary,
37. department_name
38. FROM
39.         employees e
40. JOIN departments d ON e.department_id = d.department_id
41. WHERE
42.         salary > 5000;
44. #5.查询奖⾦率最⾼的员⼯的姓名、职位名称以及奖⾦率。
45.         SELECT
46.         CONCAT(last_name,first_name) as 姓名,
47.         job_title,
48.         commission_pct
49. FROM
50.         employees e
51. JOIN jobs j ON e.job_id = j.job_id
52. WHERE
53.         commission_pct = (SELECT MAX(commission_pct) FROM employees);
55. #6.查询每个职位的最⾼⽉薪和最低⽉薪。
56.         SELECT
57.         job_title,
58.         MAX(salary),
59.         MIN(salary)
60. FROM
61.         employees e
62. JOIN jobs j ON e.job_id = j.job_id
63. GROUP BY
64.         job_title;
65.        
66. #7.查询⼊职时间早于2000年的所有员⼯的姓名、⼊职⽇期以及职位名称。
67.         SELECT
68.         CONCAT(last_name,first_name) as 姓名,
69.         hiredate,
70.         job_title
71. FROM
72.         employees e
73. JOIN jobs j on e.job_id = j.job_id
74. WHERE
75.         YEAR(hiredate) < 2000;
77. #8.查询每个部⻔的员⼯⼈数。
78.         SELECT
79.         COUNT(*) as 人数,
80.         department_name
81. FROM
82.   employees e
83. JOIN departments d ON e.department_id = d.department_id
84. GROUP BY
85.         department_name;
87. #9.查询在每个部⻔中⽉薪最⾼的员⼯的姓名、⽉薪以及部⻔名称。
88. #注：在使用in时可以使用（）将需要的列括起来查询
89.         SELECT
90.         CONCAT(e.last_name,e.first_name) as 姓名,
91.         e.salary,
92.         d.department_name
93. FROM
94.         employees e JOIN departments d ON e.department_id = d.department_id
95. WHERE
96.         (e.salary,d.department_id) in (
97. SELECT MAX(salary),department_id FROM employees GROUP BY department_id);               
99. #10.查询⽉薪超过部⻔平均⽉薪的员⼯的姓名、⽉薪以及所在部⻔名称。
100.         SELECT
101.         CONCAT( e1.last_name, e1.first_name ) AS 姓名,
102.         e1.salary,
103.         d.department_name
104. FROM
105.         employees e1
106.         JOIN departments d ON e1.department_id = d.department_id
107. WHERE
108. e1.salary > ( SELECT AVG( e2.salary ) FROM employees e2 WHERE e1.department_id = e2.department_id);
110. #11.查询每个职位的员⼯⼈数。
111.         SELECT
112.         COUNT(*) as 人数,
113.         job_title
114. FROM
115.         employees e
116. JOIN jobs j ON e.job_id = j.job_id
117. GROUP BY
118.         job_title;
120. #12.查询每个部⻔的最⾼⽉薪和最低⽉薪。
121.         SELECT
122.         department_name,
123.         MAX(salary),
124.         MIN(salary)
125. FROM
126.         employees e
127. JOIN departments d ON e.department_id = d.department_id
128. GROUP BY
129.         department_name;
131. #13.查询每个员⼯的姓名、邮箱、职位名称以及他们的上级领导的姓名。
132.         SELECT
133.         CONCAT(e1.last_name,e1.first_name) as 姓名,
134.         e1.email,
135.         j.job_title,
136.         CONCAT(e2.last_name,e2.first_name) as 上级领导
137. FROM
138.         employees e1
139. JOIN employees e2 ON e1.manager_id = e2.employee_id
140. JOIN jobs j ON e1.job_id = j.job_id;
142. #14.查询每个部⻔的员⼯平均奖⾦率。
143.         SELECT
144.         department_name,
145.         AVG(commission_pct) as 平均奖金率
146. FROM
147.         employees e
148. JOIN departments d ON e.department_id = d.department_id
149. GROUP BY
150.         department_name;
152. #15.查询每个城市的员⼯⼈数。
153.         SELECT
154.         COUNT(*) as 人数,
155.         city
156. FROM
157.         employees e
158. JOIN departments d ON e.department_id = d.department_id
159. JOIN locations l ON d.location_id = l.location_id
160. GROUP BY
161.         city;
163. #16.查询每个部⻔的职位种类数。
164. #注：使用distinct可以去重算种类数
165.         SELECT
166.         COUNT(DISTINCT job_id) as 种类数,
167.         department_name
168. FROM
169.         employees e
170. JOIN departments d ON e.department_id = d.department_id
171. GROUP BY
172.         department_name;  
174. #17.查询⼯资⾼于其职位平均⼯资的员⼯姓名、职位名称以及⽉薪。
175.         SELECT
176.         CONCAT(e1.last_name, e1.first_name ) AS 姓名,
177.         j.job_title,
178.         e1.salary
179. FROM
180. employees e1
181. JOIN jobs j ON e1.job_id = j.job_id
182. WHERE
183.         e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.job_id = e2.job_id);
185. #18.查询每个国家的员⼯⼈数。
186.         SELECT
187.         COUNT(DISTINCT employee_id) as 人数,
188.         country_id
189. FROM
190.         employees e
191. JOIN departments d ON e.department_id = d.department_id
192. JOIN locations l ON d.location_id = l.location_id
193. GROUP BY
194.         country_id;
196. #19.查询没有领导的员⼯的姓名以及职位名称。
197.         SELECT
198.         CONCAT( last_name, first_name ) AS 姓名,
199.         job_title
200. FROM
201.         employees e
202. JOIN jobs j ON e.job_id = j.job_id
203. WHERE
204.         manager_id is null ;
206. #20.查询job_id为"IT_PROG"的员⼯的姓名、职位名称以及⽉薪。
207.         SELECT
208.         CONCAT( last_name, first_name ) AS 姓名,
209.         job_title,
210.         salary
211. FROM
212.         employees e
213. JOIN jobs j ON e.job_id = j.job_id
214. WHERE
215.         e.job_id = 'IT_PROG';

复制代码

好了，今天的分享结束了，答案仅供参考不代表最终答案，如果有更换的方法，欢迎在底下留言评论！

来源:https://www.cnblogs.com/cjybigdatablog/p/18334913
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！