MySQL基础练习20题,看看你的sql基础man不man
数据获取
表的数据信息(sql文件)放在这个链接里了,提取码:52xz,需要的自行提取。数据来自网上的练习,已经给小伙伴们总结好了。
https://pan.baidu.com/s/11YMWaXtZb9K60cpOuYTwag将数据导入到mysql中
大家可以直接在navicat运行该脚本,就可以直接导入数据了,如果遇到问题很可能是编码问题,改一下数据库的编码为gb2312,如图:
基础20题
点击新建查询就可以写我们的mysql基础20题了。
#(1)查询和Zlotkey相同部门的员工姓名和工资 SELECT last_name, salary FROM employees WHERE department_id = ( SELECT department_id from employees WHERE last_name = "Zlotkey" ); #(2)查询工资比公司平均工资高的员工的员工号,姓名和工资。 SELECT employee_id, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); #(3)选择工资大于所有JOB_ID = SA_MAN 的员工的工资的员工的last_name, job_id, salary SELECT last_name, job_id, salary FROM employees WHERE salary > ( SELECT max( salary ) FROM employees WHERE job_id = "SA_MAN" ); #(4)查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 SELECT employee_id, last_name FROM employees WHERE department_id IN ( SELECT DISTINCT ( department_id ) FROM employees WHERE last_name LIKE "%u%") #(5)查询在部门的location_id为1700的部门工作的员工的员工号 SELECT employee_id FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location_id = 1700 ); #(6)查询管理者是King的员工姓名和工资 SELECT last_name, salary FROM employees WHERE manager_id IN ( SELECT employee_id FROM employees WHERE last_name = "K_ing" ); #(7)查询工资最低的员工信息: last_name, salary SELECT last_name, salary FROM employees WHERE salary = ( SELECT min( salary ) FROM employees ); #(8)查询平均工资最低的部门信息 SELECT b.department_id, d.department_name, d.manager_id, d.location_id FROM departments d JOIN ( SELECT department_id, AVG( salary ) AS avg_salary FROM employees GROUP BY department_id HAVING avg_salary IN ( SELECT MIN( a.avg_salary ) FROM ( SELECT department_id, AVG( salary ) AS avg_salary FROM employees GROUP BY department_id ) AS a ) ) AS b ON d.department_id = b.department_id; #(9)查询平均工资最低的部门信息和该部门的平均工资(相关子查询) SELECT b.department_id, d.department_name, d.manager_id, d.location_id, b.avg_salary FROM departments d JOIN ( SELECT department_id, AVG( salary ) AS avg_salary FROM employees GROUP BY department_id HAVING avg_salary IN ( SELECT MIN( a.avg_salary ) FROM ( SELECT department_id, AVG( salary ) AS avg_salary FROM employees GROUP BY department_id ) AS a ) ) AS b ON d.department_id = b.department_id; #(10)查询平均工资最高的 job 信息 SELECT b.job_id, j.job_title, j.min_salary, j.max_salary FROM jobs j JOIN ( SELECT job_id , AVG(salary) AS avg_salary FROM employees GROUP BY job_id HAVING avg_salary in ( SELECT MAX(a.avg_salary) FROM ( SELECT job_id, AVG( salary ) AS avg_salary FROM employees GROUP BY job_id ) as a ) )as b ON j.job_id = b.job_id; #(11)查询平均工资高于公司平均工资的部门有哪些? SELECT department_id, AVG(salary) as avg_salary FROM employees WHERE department_id is not null GROUP BY department_id HAVING avg_salary > ( SELECT AVG(salary) FROM employees ); #(12)查询出公司中所有 manager 的详细信息 SELECT * FROM employees WHERE employee_id in ( SELECT DISTINCT manager_id FROM employees); #(13)各个部门中 最高工资中最低的那个部门的 最低工资是多少? SELECT MAX(salary) as max_salary FROM employees WHERE department_id is not null GROUP BY department_id ORDER BY max_salary LIMIT 0,1; #(14)查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary SELECT last_name, department_id, email, salary FROM employees WHERE employee_id in ( SELECT DISTINCT manager_id FROM employees WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG( salary ) IN ( SELECT MAX( a.avg_salary ) FROM ( SELECT department_id, AVG( salary ) AS avg_salary FROM employees GROUP BY department_id ) AS a ) ) ); #(15)查询部门的部门号,其中不包括job_id是"ST_CLERK"的部门号 SELECT department_id FROM employees WHERE job_id NOT IN ( SELECT job_id FROM employees WHERE job_id = "ST_CLERK"); #(16)选择所有没有管理者的员工的last_name SELECT last_name FROM employees WHERE manager_id is null; #(17)查询员工号、姓名、雇用时间、工资,其中员工的管理者为 ‘De Haan’ SELECT employee_id, last_name, hiredate, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE last_name = "De Haan"); #(18)查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(相关子查询) SELECT employee_id,last_name,salary,department_id FROM employees e1 WHERE salary > ( # 查询某员工所在部门的平均 SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.`department_id`); #(19)查询每个部门下的部门人数大于 5 的部门名称(相关子查询) SELECT department_name FROM departments WHERE department_id in ( SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 5); #(20)查询每个国家下的部门个数大于 2 的国家编号(相关子查询) SELECT country_id FROM locations WHERE location_id in ( SELECT location_id FROM departments GROUP BY location_id HAVING COUNT(*) > 2); 好了,今天的分享已经结束了,以后我还会多多更新这些对于大数据技术较为基础的相关分享,希望大家能共同进步,一起加油!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库