MYSQL复杂查询练习题以及答案大全(难度适中)

目录
  • 建库建表插入数据
  • 测试题
  • 答案不唯一，仅供参考
  • 总结

能坚持全部做完的都是高手

直入主题

建库建表插入数据

代码直接按顺序复制就可以

-- 建库
CREATE DATABASE `emp`;
-- 打开库
USE emp;
-- 建dept表
CREATE TABLE `dept`( `deptno` INT(2) NOT NULL, `dname` VARCHAR(14), `loc` VARCHAR(13), CONSTRAINT pk_dept PRIMARY KEY(deptno) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 键emp表
CREATE TABLE `emp` ( `empno` int(4) NOT NULL PRIMARY KEY, `ename` VARCHAR(10), `job` VARCHAR(9), `mgr` int(4), `hiredate` DATE, `sal` float(7,2), `comm` float(7,2), `deptno` int(2), CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 建salgrade表
CREATE TABLE `salgrade` ( `grade` int, `losal` int, `hisal` int ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

dept表：

emp表：

salgrade表：

测试题

1.列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。

2. 列出公司各个工资等级雇员的数量、平均工资。

3. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

4. 列出在每个部门工作的员工数量、平均工资和平均服务期限。

5. 列出所有员工的姓名、部门名称和工资。

6. 列出所有部门的详细信息和部门人数。

7. 列出各种工作的最低工资及从事此工作的雇员姓名。

8. 列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。

9. 列出所有员工的年工资，所在部门名称，按年薪从低到高排序。

10. 查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000

11. 求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数。

12. 给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%， 30部门增长30%，依次类推。

13. 列出至少有一个员工的所有部门的信息：

14. 列出薪金比SMITH对的所有员工：

15. 列出所有员工的姓名以及其直接上级的姓名：

16. 列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称

17. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

18. 列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数

19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数

20. 列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号

21. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级

22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工 资。

23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。

24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。

25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。

26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。

27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。

28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工 资。

29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道 销售部的部门编号。

30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。

31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。

32. 查询dept表的结构

33. 检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段

34. 检索emp表中有提成的员工姓名、月收入及提成。

答案不唯一，仅供参考

有点乱，直接粘过来的格式不一样大家将就一下，勉强还是能看清的

– 2. 列出公司各个工资等级雇员的数量、平均工资。

show tables; select * from salgrade; select s.grade,count(),avg(e.sal) from emp e left join salgrade s on e.sal between s.losal and s.hisal group by s.grade ;

– 3. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

select ename,sal,d.dname,d.deptno from emp e left join dept d on e.deptno = d.deptno where e.sal > (select max(sal) from emp where deptno = 30);

– 4. 列出在每个部门工作的员工数量、平均工资和平均服务期限。

select count(),avg(sal),avg(year(now())-year(hiredate)) from emp group by deptno;

– 5. 列出所有员工的姓名、部门名称和工资。

SALES research accounting select e.ename,d.dname,e.sal from emp e left join dept d on d.deptno = e.deptno;

– 6. 列出所有部门的详细信息和部门人数。

OPERATIONS select d.,count(e.ename) from dept d left join emp e on e.deptno = d.deptno group by d.deptno;

– 7. 列出各种工作的最低工资及从事此工作的雇员姓名。

select a.ename,t. from emp a left join (select e.job,min(e.sal) from emp e group by e.job) t on a.job = t.job;

– 8. 列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。

– binary 实现区分大小写 – select ename from emp where job = binary ‘MANAGER'; – select binary ‘a' = ‘a'; – select binary ‘a'; – select binary ‘A'; select * from emp where job = binary ‘MANAGER'; select a.mm,c.ename,c.job,b.dname,b.cc from (select d.deptno,min(sal) mm from emp e left join dept d on e.deptno = d.deptno where job = ‘MANAGER' group by deptno) a left join (select d.deptno,d.dname,count() cc from emp e left join dept d on e.deptno = d.deptno group by d.deptno) b on a.deptno = b.deptno left join emp c on c.sal = a.mm and b.deptno = c.deptno ;

– 9. 列出所有员工的年工资，所在部门名称，按年薪从低到高排序。

select empno,ename,sal12,d.dname from emp left join dept d on d.deptno = emp.deptno order by sal12 asc;

– 10. 查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000

select a.empno,a.ename,b.ename,b.sal from emp a left join emp b on a.mgr = b.empno where b.sal>3000 and a.empno = 7566; select a.empno,a.ename,b.ename,b.sal from emp a left join emp b on a.mgr = b.empno where b.sal>3000; select a.empno,a.ename,b.ename,b.sal from emp a left join emp b on a.mgr = b.empno;

– 11. 求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数。

select d.dname,count(),sum(e.sal) from emp e left join dept d on e.deptno = d.deptno where d.dname like ‘%s%' group by d.deptno; select * from emp;

– 12. 给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长 20%，30部门增长30%，依次类推。

select empno,ename,sal,sal+sal*(deptno/100) from emp where year(curdate()) - year(hiredate)>30 or year(hiredate)=2022; update emp set sal = sal+sal*(deptno/100) where year(curdate()) - year(hiredate)>30 or year(hiredate)=2022; select * from emp;

– 13. 列出至少有一个员工的所有部门的信息

select distinct d.* from dept d join emp e on d.deptno = e.deptno;

– 14. 列出薪金比SMITH低的所有员工

select * from emp where sal < (select sal from emp where ename = ‘SMITH')

– 15. 列出所有员工的姓名以及其直接上级的姓名：

select a.empno,a.ename,b.ename from emp a left join emp b on a.mgr = b.empno;

– 16. 列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称

select a.empno,a.ename,b.ename,d.dname from emp a left join emp b on a.mgr = b.empno and a.hiredate<b.hiredate left join dept d on d.deptno = a.deptno;

– 17. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select * from dept d left join emp e on d.deptno = e.deptno;

– 18. 列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数

select a.ename,a.job,b.dname,b.cc from emp a join (select d.deptno,d.dname,count() cc from dept d left join emp e on d.deptno = e.deptno group by d.deptno) b on b.deptno = a.deptno and a.job = ‘CLERK';

– 19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数

select job,max(sal),min(sal),avg(sal),count() from emp where sal>1500 group by job;

– 20. 列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号

select ename from emp where deptno in (select deptno from dept where dname=‘sales'); select e.ename from emp e join dept d on e.deptno = d.deptno and d.dname=‘sales';

– 21. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级

select a.ename,a.en,d.dname,a.sal,s.grade from (select a.deptno,a.ename,b.ename en,a.sal from emp a join emp b on a.mgr = b.empno and a.sal>(select avg(sal) from emp)) a left join dept d on a.deptno=d.deptno left join salgrade s on a.sal between s.losal and s.hisal; select a.ename,b.ename from emp a join emp b on a.mgr = b.empno and a.sal> (select avg(sal) from emp);

– 22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高 工资。

select e.deptno,d.dname,avg(e.sal),max(e.sal),min(sal),count() from dept d join emp e on e.deptno = d.deptno group by e.deptno;

– 23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。

select a.empno,a.ename,d.dname,b.ename from (select * from emp where sal >(select min(sal) from emp where ename in (‘smith',‘allen'))) a left join emp b on a.mgr = b.empno left join dept d on d.deptno = a.deptno;

– 24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排 列。

select a.empno,a.ename,a.sal12,b.empno,b.ename,b.sal12 from emp a left join emp b on a.mgr = b.empno order by b.sal12;

– 25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。

select a.empno,a.ename,b.ename,d.dname from emp a left join emp b on a.mgr = b.empno and a.hiredate<b.hiredate left join dept d on d.deptno = a.deptno;

– 26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。

select d.deptno,d.dname,count(e.ename),avg(sal) from dept d left join emp e on d.deptno = e.deptno group by d.deptno;

– 27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。

select a.ename,a.job,b.dname,b.cc from emp a join (select d.deptno,d.dname,count() cc from dept d left join emp e on d.deptno = e.deptno group by d.deptno) b on b.deptno = a.deptno and a.job = ‘CLERK'; select e.deptno,count(e.deptno) from (select a.deptno,a.ename,d.dname,s.grade from (select deptno,ename,sal from emp where job=‘CLERK') a left join dept d on a.deptno=d.deptno left join salgrade s on a.sal between s.losal and s.hisal) aa left join emp e on aa.deptno = e.deptno group by e.deptno; select t1.,t2.deptcount from (select d.deptno,e.ename,e.job,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal where e.job = ‘CLERK') t1 join (select deptno, count() as deptcount from emp group by deptno) t2 on t1.deptno = t2.deptno;

– 28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、 平均工资。

select job,max(sal),min(sal),avg(sal),count() from emp where sal>1500 group by job;

– 29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定 不知道销售部的部门编号。

select e.ename,e.sal,e.hiredate,d.dname from emp e join dept d on d.deptno = e.deptno and d.dname=‘sales';

– 30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。

select a.ename,a.en,d.dname,a.sal,s.grade from (select a.deptno,a.ename,b.ename en,a.sal from emp a join emp b on a.mgr = b.empno and a.sal>(select avg(sal) from emp)) a left join dept d on a.deptno=d.deptno left join salgrade s on a.sal between s.losal and s.hisal;

– 31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。

create view v1 as select b.ename,d.dname,a.cc from (select deptno,count(*) cc from emp group by deptno) a join (select ename,deptno from emp where job = (select job from emp where ename = ‘scott')) b on b.deptno=a.deptno
left join dept d on d.deptno = b.deptno; select * from v1;

– 32. 查询dept表的结构

desc emp; describe emp; show create table emp; show columns from emp;

– 33. 检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段 is a 是oracle数据库

select concat(empno,ename,job) from emp; select concat_ws(‘-',empno,ename,job) from emp; select distinct job from emp; select group_concat(distinct job) from emp; select group_concat(distinct ename) from emp; select group_concat(distinct job order by job asc separator ‘=') from emp;

– 34. 检索emp表中有提成的员工姓名、月收入及提成。

select ename,sal,comm from emp where comm is not null; select ename,sal,comm from emp where comm is not null and comm>0;

总结

到此这篇关于MYSQL复杂查询练习题以及答案的文章就介绍到这了,更多相关MYSQL复杂查询练习题内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • Mysql 日期格式化及复杂日期区间查询

    目录 前言 查询使用场景案例 时间区间查询 查询日期今天时间比较数据 常用的周期时间查询 mysql日期时间函数 1 得当前日期+时间(date + time)函数:now() 2 获得当前日期+时间(date + time)函数:sysdate() 3 获得当前时间戳函数:current_timestamp, current_timestamp() 4 获取当前日期(date)函数: curdate() mysql日期时间转换函数 1 日期时间转换字符串格式 2 字符串转换为日期时间 3 (日

  • MySQL常用SQL语句总结包含复杂SQL查询

    1.复杂SQL查询 1.1.单表查询 (1)选择指定的列 [例]查询全体学生的学号和姓名 select Sno as 学号,Sname as 姓名 from student; select Sno,Sname from student; (2)查询全部列 [例]查询全体学生的详细信息 select * from student; (3)对查询后的指定列进行命名 [例]查询全部学生的"姓名"及其"出生年"两列 select Sname as 姓名,(2014-Sage

  • Mysql一些复杂的sql语句(查询与删除重复的行)

    1.查找重复的行 SELECT * FROM blog_user_relation a WHERE (a.account_instance_id,a.follow_account_instance_id) IN (SELECT account_instance_id,follow_account_instance_id FROM blog_user_relation GROUP BY account_instance_id, follow_account_instance_id HAVING C

  • MySQL之复杂查询的实现

    MySQL之复杂查询的实现

    目录 1.排序 2.分组 3.联合查询 4.多表连接 1.排序 ORDER BY 子句来设定哪个字段哪种方式来进行排序,再返回搜索结果.desc:降序 select * from blog order by balance desc; asc:升序,默认,可不写 select * from blog order by balance asc; 多字段排序 update blog set age = 25 where age < 25; 先根据年龄升序,再根据余额降序 select * from

  • MYSQL复杂查询练习题以及答案大全(难度适中)

    MYSQL复杂查询练习题以及答案大全(难度适中)

    目录 建库建表插入数据 测试题 答案不唯一,仅供参考 总结 能坚持全部做完的都是高手 直入主题 建库建表插入数据 代码直接按顺序复制就可以 -- 建库 CREATE DATABASE `emp`; -- 打开库 USE emp; -- 建dept表 CREATE TABLE `dept`( `deptno` INT(2) NOT NULL, `dname` VARCHAR(14), `loc` VARCHAR(13), CONSTRAINT pk_dept PRIMARY KEY(deptno)

  • 最全50个Mysql数据库查询练习题

    最全50个Mysql数据库查询练习题

    此数据库查询语句是网络上50个数据库查询练习题目,网上有些版本是oracle语句写的,大多数公司还是用免费的mysql数据库,以下都是mysql版本,全部都有验证过. 表名和字段 –1.学生表 Student(s#, sname, sage,ssex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c#,cname,t#) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t#,tname) –教师编号,教师姓名 –4.成绩表 Sc(s#,c#,sc

  • MySQL中查询、删除重复记录的方法大全

    前言 本文主要给大家介绍了关于MySQL中查询.删除重复记录的方法,分享出来供大家参考学习,下面来看看详细的介绍: 查找所有重复标题的记录: select title,count(*) as count from user_table group by title having count>1; SELECT * FROM t_info a WHERE ((SELECT COUNT(*) FROM t_info WHERE Title = a.Title) > 1) ORDER BY Titl

  • MySQL模糊查询用法大全(正则、通配符、内置函数)

    MySQL模糊查询用法大全(正则、通配符、内置函数)

    SELECT * from table where username like '%陈哈哈%' and hobby like '%牛逼' 这是一条我们在MySQL中常用到的模糊查询方法,通过通配符%来进行匹配,其实,这只是冰山一角,在MySQL中,支持模糊匹配的方法有很多,且各有各的优点.好了,今天让我带大家一起掀起MySQL的小裙子,看一看模糊查询下面还藏着多少鲜为人知的好东西. 一.MySQL通配符模糊查询(%,_) 1-1. 通配符的分类 "%" 百分号通配符: 表示任何字符出现

  • MySQL子查询用法实例分析

    本文实例讲述了MySQL子查询用法.分享给大家供大家参考,具体如下: 假设表my_tbl包含三个字段a,b,c:现在需要查询表中列a的每个不同值下的列b为最小值的记录量. 比如表记录为: a  b  c 1  3  'cd' 2  3  'nhd' 1  5  'bg' 2  6  'cds' 1  7  'kiy' 3  7  'vsd' 3  8  'ndf' 希望得到结果为: a  b  c 1  3  'cd' 2  3  'nhd' 3  7  'vsd' (1) 其中一个做法:先查出

  • MySQL嵌套查询实例详解

    本文实例分析了MySQL嵌套查询.分享给大家供大家参考,具体如下: MySQl从4.11版后已经完全支持嵌套查询了,那么下面举些简单的嵌套查询的例子吧(源程序来自MySQL User Manual): 1. SELECT语句的子查询 语法: 复制代码 代码如下: SELECT ... FROM (subquery) AS name ... 先创建一个表: CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT); INSERT INTO t1 VALUES (

  • MySQL切分查询用法分析

    本文实例讲述了MySQL切分查询用法.分享给大家供大家参考,具体如下: 对于大查询有时需要'分而治之',将大查询切分为小查询: 每个查询功能完全一样,但只完成原来的一小部分,每次查询只返回一小部分结果集. 删除旧的数据就是一个很好地例子.定期清理旧数据时,如果一条sql涉及了大量的数据时,可能会一次性锁住多个表或行,耗费了大量的系统资源,却阻塞了其他很多小的但重要的查询.将一个大得DELETE语句切分为较小的查询时,可以尽量减少影响msql的性能,同时减少mysql复制造成的延迟. 例如,每个月

  • MySQL 随机查询数据与随机更新数据实现代码

    MySQL随机查询数据 以前在群里讨论过这个问题,比较的有意思.MySQLl的语法真好玩.他们原来都想用PHP的实现随机,但取出多条好像要进行两次以上查询.翻了手册,找到了下面这个语句,可以完成任务了. 复制代码 代码如下: SELECT * FROM table_name ORDER BY rand() LIMIT 5; MySQL的rand()函数在手册里是这么说的: RAND() RAND(N) 返回在范围0到1.0内的随机浮点值.如果一个整数参数N被指定,它被用作种子值. 关于MySQL

  • Mysql自连接查询实例详解

    Mysql自连接查询实例详解

    本文实例讲述了Mysql自连接查询.分享给大家供大家参考,具体如下: 自连接查询 假想以下场景:某一电商网站想要对站内产品做层级分类,一个类别下面有若干子类,子类下面也会有别的子类.例如数码产品这个类别下面有笔记本,台式机,智能手机等:笔记本,台式机,智能手机又可以按照品牌分类:品牌又可以按照价格分类,等等.也许这些分类会达到一个很深的层次,呈现一种树状的结构.那么这些数据要怎么在数据库中表示呢?我们可以在数据库中创建两个字段来存储id和类别名称,使用第三个字段存储类别的子类或者父类的id,最后

  • mysql实现查询数据并根据条件更新到另一张表的方法示例

    本文实例讲述了mysql实现查询数据并根据条件更新到另一张表的方法.分享给大家供大家参考,具体如下: 原本的数据库有3张表 travel_way :旅游线路表,存放线路的具体信息 traveltag :线路标签表,存放线路目的地等信息 tagrelation:标签对应表,存放线路和目的地的对应关系 因为业务逻辑的改变,现在要把它们合并为一张表,把traveltag中的目的地信息插入到travel_way中. 首先获取到所有线路对应的目的地,以线路ID分组,合并目的地到一行,以逗号分隔. 复制代码

随机推荐