MySQL基础20题(续前面的20题)
写在前面
今天继续前面的20题之后的练习,数据都是一样的,可以直接使用,来看看你的sql功底降了没。
基础20题
#1.查询每个员⼯的姓名、邮箱、职位名称以及所在部⻔名称。 SELECT CONCAT(last_name,first_name) as 姓名, email, job_title, department_name FROM employees e JOIN departments d ON e.department_id=d.department_id JOIN jobs j ON e.job_id = j.job_id ; #2.查询⽉薪最⾼的员⼯的姓名、职位名称以及⽉薪。 SELECT CONCAT(last_name,first_name) as 姓名, job_title, salary FROM employees e JOIN jobs j on e.job_id = j.job_id WHERE salary = (SELECT MAX(salary) FROM employees); #3.查询每个部⻔的平均⽉薪。 #注:如果某一部门没有员工不会显示 SELECT department_name, AVG(salary) FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY department_name; #4.查询部⻔中员⼯⽉薪⾼于5000的所有员⼯的姓名、⽉薪以及所在部⻔名称。 SELECT CONCAT(last_name,first_name) as 姓名, salary, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE salary > 5000; #5.查询奖⾦率最⾼的员⼯的姓名、职位名称以及奖⾦率。 SELECT CONCAT(last_name,first_name) as 姓名, job_title, commission_pct FROM employees e JOIN jobs j ON e.job_id = j.job_id WHERE commission_pct = (SELECT MAX(commission_pct) FROM employees); #6.查询每个职位的最⾼⽉薪和最低⽉薪。 SELECT job_title, MAX(salary), MIN(salary) FROM employees e JOIN jobs j ON e.job_id = j.job_id GROUP BY job_title; #7.查询⼊职时间早于2000年的所有员⼯的姓名、⼊职⽇期以及职位名称。 SELECT CONCAT(last_name,first_name) as 姓名, hiredate, job_title FROM employees e JOIN jobs j on e.job_id = j.job_id WHERE YEAR(hiredate) < 2000; #8.查询每个部⻔的员⼯⼈数。 SELECT COUNT(*) as 人数, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY department_name; #9.查询在每个部⻔中⽉薪最⾼的员⼯的姓名、⽉薪以及部⻔名称。 #注:在使用in时可以使用()将需要的列括起来查询 SELECT CONCAT(e.last_name,e.first_name) as 姓名, e.salary, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE (e.salary,d.department_id) in ( SELECT MAX(salary),department_id FROM employees GROUP BY department_id); #10.查询⽉薪超过部⻔平均⽉薪的员⼯的姓名、⽉薪以及所在部⻔名称。 SELECT CONCAT( e1.last_name, e1.first_name ) AS 姓名, e1.salary, d.department_name FROM employees e1 JOIN departments d ON e1.department_id = d.department_id WHERE e1.salary > ( SELECT AVG( e2.salary ) FROM employees e2 WHERE e1.department_id = e2.department_id); #11.查询每个职位的员⼯⼈数。 SELECT COUNT(*) as 人数, job_title FROM employees e JOIN jobs j ON e.job_id = j.job_id GROUP BY job_title; #12.查询每个部⻔的最⾼⽉薪和最低⽉薪。 SELECT department_name, MAX(salary), MIN(salary) FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY department_name; #13.查询每个员⼯的姓名、邮箱、职位名称以及他们的上级领导的姓名。 SELECT CONCAT(e1.last_name,e1.first_name) as 姓名, e1.email, j.job_title, CONCAT(e2.last_name,e2.first_name) as 上级领导 FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.employee_id JOIN jobs j ON e1.job_id = j.job_id; #14.查询每个部⻔的员⼯平均奖⾦率。 SELECT department_name, AVG(commission_pct) as 平均奖金率 FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY department_name; #15.查询每个城市的员⼯⼈数。 SELECT COUNT(*) as 人数, city FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id GROUP BY city; #16.查询每个部⻔的职位种类数。 #注:使用distinct可以去重算种类数 SELECT COUNT(DISTINCT job_id) as 种类数, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY department_name; #17.查询⼯资⾼于其职位平均⼯资的员⼯姓名、职位名称以及⽉薪。 SELECT CONCAT(e1.last_name, e1.first_name ) AS 姓名, j.job_title, e1.salary FROM employees e1 JOIN jobs j ON e1.job_id = j.job_id WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.job_id = e2.job_id); #18.查询每个国家的员⼯⼈数。 SELECT COUNT(DISTINCT employee_id) as 人数, country_id FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id GROUP BY country_id; #19.查询没有领导的员⼯的姓名以及职位名称。 SELECT CONCAT( last_name, first_name ) AS 姓名, job_title FROM employees e JOIN jobs j ON e.job_id = j.job_id WHERE manager_id is null ; #20.查询job_id为"IT_PROG"的员⼯的姓名、职位名称以及⽉薪。 SELECT CONCAT( last_name, first_name ) AS 姓名, job_title, salary FROM employees e JOIN jobs j ON e.job_id = j.job_id WHERE e.job_id = 'IT_PROG';
好了,今天的分享结束了,答案仅供参考不代表最终答案,如果有更好的方法,欢迎在底下留言评论!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库