MySQL练习题（附带答案和结果）

已于 2022-05-23 11:06:56 修改
阅读量2.7k 收藏 39
点赞数 6
分类专栏： sql 文章标签： mysql 数据库
于 2022-05-22 09:42:54 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45890113/article/details/124906771
版权

文章目录


所用到的表的SQL语句信息 

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;


CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
    );

CREATE TABLE EMP
       (EMPNO int(4)  not null ,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2)
    );

CREATE TABLE SALGRADE
      ( GRADE INT,
    LOSAL INT,
    HISAL INT );

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
30, 'SALES', 'CHICAGO');
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
40, 'OPERATIONS', 'BOSTON');
commit;

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10);
commit;

INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
1, 700, 1200);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
2, 1201, 1400);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
3, 1401, 2000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
4, 2001, 3000);
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
5, 3001, 9999);
commit;

在这里插入图片描述

问题

1、取得每个部门最高薪水的人员名称
2、哪些人的薪水再部门平均薪资以上
3、取得部门中（所有人的）平均薪水等级
4、不使用组函数max，取得最高薪资（两种方案）
5、取得平均薪资最高的部门的部门编号（两种方案）
6、取得平均薪资最高的部门的部门名称
7、求平均薪资的等级最低的部门的部门名称
8、 取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名
9、取得薪资最高的前5名员工
10、取薪资最高的第六到第十名员工
11、取得最后入职的五名员工
12、取得每个薪资等级有多少员工
13、
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1），找出没选过“黎明”老师的所有学生姓名。
2），列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。
3），即学过 1 号课程又学过 2 号课所有学生的姓名。

CREATE TABLE SC
(
SNO VARCHAR(200),
CNO VARCHAR(200),
SCGRADE VARCHAR(200)
);
CREATE TABLE S
(
SNO VARCHAR(200),
SNAME VARCHAR(200)
);
CREATE TABLE C
(
CNO VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明');
commit;
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生 1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生 2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生 3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生 4');
commit;
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');
commit；

14、请列出所有员工及领导姓名
15、列出受雇日期早于其直接上级的所有员工的编号、姓名和部门名称
16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
17、列出至少有五个员工的部门
18、列出薪资比"SMITH"多的所有员工信息
19、列出所有"CLERKK"的姓名、部门名称以及部门人数
20、列出最低薪资大于1500的各种工作以及从事该工作的全部员工的信息
21、列出在部门"SALES"工作的员工的姓名（不使用部门编号）
22、列出薪资高于公司平均薪资的所有员工姓名、所在部门、上级领导、工资等级
23、列出与"SCOTT"从事相同工作的所有员工及部门名称
24、列出薪资等于部门编号为30中员工的薪资的其他员工的姓名和薪资
25、列出薪资高于在部门编号为30工作的所有员工的薪资的员工姓名、薪资和部门名称
26、列出在每个部门工作的员工数量，平均工资和平均服务期限
27、列出所有员工的姓名、部门名称和工资
28、列出所有部门的详细信息和人数
29、列出不同工作的最低工资以及该工资的员工的信息
30、列出各个部门"MANAGER"的最低薪资
31、列出所有员工的年工资，按照年薪升序排列
32、列出员工领导的薪资超过3000的员工姓名和领导姓名
33、列出部门名称中带"S"字符的部门员工的工资合计和部门人数
34、给任职日期超过30年的员工加薪10%

问题加答案（附带查询结果）

1、取得每个部门最高薪水的人员名称、部门编号和薪资

// 先查询出每个部门的最高薪资
select deptno,max(sal) as maxsal from emp group by deptno;

// 将上述查询结果当作一张临时表与emp表进行连接查询
select
    e.ename,t.deptno,t.maxsal
from
    emp e
join 
    (select deptno,max(sal) as maxsal from emp group by deptno) t
on
    t.deptno=e.deptno and t.maxsal=e.sal;

在这里插入图片描述
2、哪些人的薪水在部门平均薪资以上

// 先查询出每个部门的平均薪资avgsal
select deptno,avg(sal) as avgsal from emp group by deptno; 
// 再查询sal>avgsal的员工信息
select 
    e.ename,sal,t.deptno,t.avgsal
from
    emp e
join 
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
on 
    e.deptno=t.deptno and sal>avgsal;

在这里插入图片描述
3、取得部门中（所有人的）平均薪水的等级

// 先取得每个部门的平均薪资avgsal
select deptno,avg(sal) as avgsal from emp group by deptno;
// 再去查询avgsal的薪资等级
select 
    t.deptno,t.avgsal,s.grade
from 
    salgrade s
join
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
on
    avgsal between s.losal and s.hisal;

在这里插入图片描述
4、不使用组函数max，取得最高薪资（两种方案）

// 第一种，按降序排列薪资，取第一条
select 
    sal as maxsal
from 
    emp
order by
   sal desc
limit
    1;

在这里插入图片描述

/// 第二种，自连接，查询出a表中sal小于b表中sal并去重
select distinct a.sal from emp a join emp b on a.sal < b.sal;

// 因为最大工资是不小于任何工资的，所以我们可以利用这一点来获取最大薪资
select 
    sal 
from 
    emp 
where 
    sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);

在这里插入图片描述
5、取得平均薪资最高的部门的部门编号（两种方案）

第一种：

// 取得部门平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno;
// 降序排列后使用分页查询取第一个
select 
    deptno,avg(sal) as avgsal 
from 
    emp 
group by 
    deptno 
order by 
    avgsal desc 
limit 1;

在这里插入图片描述

/*第二种：
使用max分组函数
取得部门平均薪资*/
select deptno,avg(sal) as avgsal from emp group by deptno;

// 使用max分组函数取得最大值
select max(t.avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) t;

// 最后查询出想要的结果
select
    deptno,avg(sal) as avgsal
from
    emp
group by
    deptno
having
    avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

在这里插入图片描述
6、取得平均薪资最高的部门的部门名称

// 取得部门平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno;

// 降序排列后使用分页查询取第一个即为最高薪资的部门，获取其部门编号和平均薪资
select
    deptno,avg(sal) as avgsal
from
    emp
group by
    deptno
order by
    avgsal desc
limit 1;
// 连接查询，与salgrade表进行内连接，查询出对应的部门名
select
    e.deptno,avg(e.sal) as avgsal,d.dname
from
    emp e
join 
    dept d
on
    e.deptno=d.deptno
group by
    e.deptno
order by
    avgsal desc
limit 1;

在这里插入图片描述
7、求平均薪资的等级最低的部门的部门名称

注意：平均薪资等级最低的部门可能不止一个

// 第一步：找出每个部门的平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno;

// 第二步：找出每个部门平均薪资的等级并创建视图view_avgsal_grade    
create 
    view view_avgsal_grade    
as
    select
        t.*,s.grade
    from
        (select deptno,avg(sal) as avgsal from emp group by deptno) t
    join
        salgrade s
    on
        t.avgsal between s.losal and s.hisal;
// 第三步：查出最低薪资等级并创建视图view_min_grade
create 
    view view_min_grade 
as
    select 
        min(grade)
    as 
        mingrade 
    from 
        view_avgsal_grade;
// 第四步：查询出平均薪资等级等于最低薪资等级的信息并创建视图view_min_grade_deptno
create
    view view_min_grade_deptno
as
    select
        vag.deptno,vag.grade
    from
        view_avgsal_grade vag
    join
        view_min_grade vmg
    on
        vag.grade=vmg.mingrade;

// 第五步：视图view_min_grade_deptno与表dept进行连接，查询出部门名
select
    vmgd.*,d.dname
from
    view_min_grade_deptno vmgd
join
    dept d
on 
    vmgd.deptno=d.deptno;

8、 取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名

// 查询出在mgr字段出现过的员工代码并去重,则员工编号不在(not in)其范围中的员工为普通员工(使用not in时，需要排除小括号中的null)
select distinct mgr from emp is not null;
// 找出普通员工的最高薪资,并创建视图view_max_sal
create 
    view view_max_sal 
as
    select 
        max(sal) as maxsal
    from 
        emp 
    where 
        empno not in (select distinct mgr from emp where mgr is not null);    
// 查出薪资大于普通员工的最高薪资的人的信息即为所求
select
    e.ename,e.sal
from
    emp e
join
    view_max_sal v
on
    e.sal>v.maxsal;

在这里插入图片描述
9、取得薪资最高的前5名员工

select
    ename,sal
from
    emp
order by
    sal desc
limit 5;

在这里插入图片描述
10、取薪资最高的第六到第十名员工

select
    ename,sal
from
    emp
order by
    sal desc
limit 5,5;

在这里插入图片描述
11、取得最后入职的五名员工

// 按入职时间分页降序排序，取前五条
select 
    ename,hiredate
from
    emp
order by
    hiredate desc
limit 0,5;

在这里插入图片描述
12、取得每个薪资等级有多少员工

// 找出每个员工的薪资等级
select
    e.ename,s.grade
from
    emp e
join
    salgrade s
on
 e.sal between s.losal and s.hisal;

// 按工资等级分组
select
    s.grade,count(s.grade)
from    
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal
group by
    grade;

在这里插入图片描述
13、（面试题）
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

CREATE TABLE SC
(
SNO VARCHAR(200),
CNO VARCHAR(200),
SCGRADE VARCHAR(200)
);
CREATE TABLE S
(
SNO VARCHAR(200),
SNAME VARCHAR(200)
);
CREATE TABLE C
(
CNO VARCHAR(200),
CNAME VARCHAR(200),
CTEACHER VARCHAR(200)
);
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵');
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明');
commit;
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生 1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生 2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生 3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生 4');
commit;
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');
commit；

在这里插入图片描述
问题：
1），找出没选过“黎明”老师的所有学生姓名。

// 先查询出每个学生所选的课程
select  sc.sno,c.cno,c.cname,c.cteacher from sc join c on sc.cno=c.cno;

// 找出选过"黎明"老师的课的学生的编号
select s.sno from s join (select  sc.sno,c.cno,c.cname,c.cteacher from sc join c on sc.cno=c.cno) t on s.sno=t.sno where t.cteacher='黎明';

在这里插入图片描述

// 得到选择了"黎明"老师课的学生的编号之后，使用not in查询出没有选择的学生的姓名
select 
    s.sname 
from 
    s 
where 
    sno not in (select s.sno from s join (select  sc.sno,c.cno,c.cname,c.cteacher from sc join c on sc.cno=c.cno) t on s.sno=t.sno where t.cteacher='黎明');

在这里插入图片描述
2，列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。

// 查询出各学生的不及格的门数
select s.sno,count(sc.scgrade) as countfail from s join sc on s.sno=sc.sno where sc.scgrade<60 group by s.sno;

// 找出不及格门数大于两门的学生的姓名，编号,并创建视图view_sno_sname 
create view view_sno_sname as
select s.sno,s.sname from s join (select s.sno,count(sc.scgrade) as countfail from s join sc on s.sno=sc.sno where sc.scgrade<60 group by s.sno) t on s.sno=t.sno where t.countfail >= 2;

// 找到对应条件的学生编号后连接sc表进行查询
select 
    vs.sname,avg(sc.scgrade) as avgsagrade
from
    sc
join
    view_sno_sname vs
on
    vs.sno=sc.sno
group by
    vs.sname;

在这里插入图片描述
3，既学过 1 号课程又学过 2 号课所有学生的姓名。

// 查询出各个学生所选的课程
select sno,cno from sc;

// 找出学了1或2的课程的学生
select s.sno,t.cno from s join (select sno,cno from sc) t on s.sno=t.sno where t.cno=1 or t.cno=2;

// 按学号分组，找出课程1和2都学的学生
select 
    s.sname
from 
    s 
join 
    (select sno,cno from sc) t       
on 
    s.sno=t.sno 
where 
    t.cno=1 or t.cno=2                
group by 
    s.sname
having 
    count(t.cno)=2;

在这里插入图片描述
14、请列出所有员工及领导姓名

// 左外连接
select
    a.ename '员工' , ifnull(b.ename,'没有领导') '领导'
from
    emp a
left join
    emp b
on
    a.mgr=b.empno;

在这里插入图片描述

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

// 内连接，找出有领导的员工
select
    a.ename
from
    emp a
join
    emp b
on
    a.mgr=b.empno;

// 条件查询，找出受雇日期早于其领导的员工
select
    a.ename,a.hiredate,b.ename,b.hiredate,a.empno,d.dname
from
    emp a
join
    emp b
on
    a.mgr=b.empno
join
    dept d
on 
    a.deptno=d.deptno
where
    b.hiredate>a.hiredate;

在这里插入图片描述
16、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

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

在这里插入图片描述

17、列出至少有五个员工的部门

// 查询出员工数大于等于5 的部门的编号及人数
select
    deptno,count(*)
from
    emp
group by
    deptno
having
    count(*)>=5;

// 连接查询
select
    e.deptno,count(*),d.dname
from
    emp e
join
    dept d
on
    e.deptno=d.deptno
group by
    deptno
having
    count(*)>=5;

在这里插入图片描述
18、列出薪资比"SMITH"多的所有员工信息

// 先查出SMITH的工资
select sal from emp where ename='SMITH';
// 查出工资高于SMITH
select * from emp where sal>(select sal from emp where ename='SMITH');

在这里插入图片描述
19、列出所有JOB为"CLERK"的姓名、部门名称以及部门人数

// 查询job为"CLERK"的员工的姓名及部门编号并创建视图view_clerk
create view view_clerk as select ename,deptno from emp where job='clerk';

// 连接表，查出部门名并创建视图view_ename_deptno
create view 
    view_ename_deptno 
as
    select
        v.ename,v.deptno,d.dname
    from
        view_clerk v
    join
        dept d
    on
        v.deptno=d.deptno;
// 查找出每个部门的人数并创建视图view_deptcount
create view 
    view_deptcount 
as
    select 
        deptno,count(*) as deptcount 
    from 
        emp 
    group by 
        deptno;

// view_ename_deptno和view_deptcount连接查询
select
    ved.*,vd.deptcount
from 
    view_ename_deptno ved
join
    view_deptcount vd
on
    ved.deptno=vd.deptno

在这里插入图片描述
20、列出最低薪资大于1500的各种工作以及从事该工作的全部员工的信息

// 找出最低工资大于1500的工作
select job from emp group by job having min(sal)>1500;

// 查找出从事上述工作的人
select 
    e.*
from 
    emp e
join
    (select job from emp group by job having min(sal)>1500) t
on
    e.job=t.job;

在这里插入图片描述
21、列出在部门"SALES"工作的员工的姓名，假设不知道部门编号

select
    ename
from 
    emp
where
    deptno=(select deptno from dept where dname='SALES');

在这里插入图片描述
22、列出薪资高于公司平均薪资的所有员工姓名、所在部门、上级领导、工资等级

// 查出公司平均工资
select avg(sal) as avgsal from emp;

// 查出工资高于平均工资的员工的ename，deptno，mgr，empno,sal并创建视图view_t
create view view_t as
    select
        e.ename,e.deptno,e.mgr,e.empno,e.sal
    from
        emp e
    join
        (select avg(sal) as avgsal from emp) t
    on
        e.sal>t.avgsal;
// 连接emp表查上级领导，连接dept查部门名,连接salgrade表查工资等级
select
    t.ename,d.dname,e.ename,t.sal,s.grade
from
    view_t t
left join
    emp e
on
    t.mgr=e.empno
join
    dept d
on
    d.deptno=t.deptno
join
    salgrade s
on
    t.sal between s.losal and s.hisal;

在这里插入图片描述
23、列出与"SCOTT"从事相同工作的所有员工及部门名称

// 查询出SCOTT的工作
select job from emp where ename='scott';

// 查询
select
    e.ename,e.deptno,d.dname
from
    emp e
join
    (select job from emp where ename='scott') t
on
    e.job=t.job
join
    dept d
on
    d.deptno=e.deptno
where
    e.ename != 'scott';

在这里插入图片描述
24、列出薪资等于部门编号为30中员工的薪资的其他员工的姓名和薪资

select
    ename,sal
from
    emp
where
    sal in(select distinct sal from emp where deptno=30)
and
    deptno != 30;

在这里插入图片描述
25、列出薪资高于在部门编号为30工作的所有员工的薪资的员工姓名、薪资和部门名称

查询出部门编号为30的员工的最高薪资
select max(sal) maxsal from emp where deptno=30;

// 连接查询
select 
    e.ename,e.sal,d.dname
from
    emp e
join
    (select max(sal) maxsal from emp where deptno=30) t
on
    e.sal>t.maxsal
join
    dept d
on
    e.deptno=d.deptno;

在这里插入图片描述
26、列出在每个部门工作的员工数量，平均工资和平均服务期限

// 查询每个部门的员工数量
select d.deptno,count(e.ename) from emp e  right join dept d on e.deptno=d.deptno group by deptno;

// 查询每个部门的平均薪资
select d.deptno,ifnull(avg(sal),0) as avgsal from emp e right join dept d on e.deptno=d.deptno group by deptno;

// 查询每个部门的平均服务期限（timeStampDiff函数，计算两个时间的时间差：第一个参数是返回的类型，第二个参数是开始时间，第三个参数是结束时间）
select d.deptno,ifnull(avg(timeStampDiff(year,hiredate,now())),0) as avgtime from emp e right join dept d on e.deptno=d.deptno group by deptno;

在这里插入图片描述
27、列出所有员工的姓名、部门名称和工资

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

在这里插入图片描述
28、列出所有部门的详细信息和人数

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

在这里插入图片描述
29、列出不同工作的最低工资以及该工资的员工的信息

// 先查出每个岗位最低工资
select min(sal) as minsal,ename from emp group by job;

// 连接查询，找出job和sal都对应相等的员工得到其详细信息
select 
    e.* 
from 
    emp e 
join 
    (select min(sal) as minsal,job from emp group by job) t 
on 
    e.sal=t.minsal and e.job=t.job;

在这里插入图片描述
30、列出各个部门"MANAGER"的最低薪资

select
    deptno,min(sal) as minsal
from
    emp
where
    job='manager'
group by
    deptno;

在这里插入图片描述
31、列出所有员工的年工资，按照年薪升序排列

select
    ename,(sal + ifnull(comm,0)) * 12 as yearsal
from 
    emp
order by
    yearsal asc;

在这里插入图片描述
32、列出员工领导的薪资超过3000的员工姓名和领导姓名

select
    a.ename '员工姓名',b.ename '领导姓名'
from 
    emp a
join     
    emp b
on
    a.mgr=b.empno
where
    b.sal>3000;

在这里插入图片描述
33、列出部门名称中带"S"字符的部门员工的工资合计和部门人数

// 查询出部门名称带S的部门
select deptno,dname from dept where dname like '%S%';

// 连接查询
select
   t.deptno,t.dname,ifnull(sum(e.sal),0) as sumsal,count(e.ename) countnum
from 
    emp e
right join
    (select deptno,dname from dept where dname like '%S%') t
on
    e.deptno=t.deptno
group by
    t.deptno;

在这里插入图片描述
34、给任职日期超过40年的员工加薪10%

// 查询任期超过40年的员工的信息
select ename,empno,hiredate,sal from emp where timeStampDiff(year,hiredate,now())>40;

// 加薪10%
update
    emp e
set
    e.sal=e.sal*1.1
where
    timeStampDiff(year,hiredate,now())>40;

在这里插入图片描述

栗山未来~
关注
  • 6
    点赞
  • 踩
  • 39
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL练习题(附带数据)
12-02
MySQL练习题(附带数据)， 包含：DDL：数据定义语言，定义库，表结构等，包括create,drop,alter等 DML：数据操作语言，增删改查数据，包括insert,delete,update,select等 DCL：数据控制语言，权限，事务等管理。 DQL:...
数据库练习
weixin_56680764的博客
08-13 1308
数据库练习
MySQL：练习题
璃白的博客
04-27 7375
题目 答案 1、取得每个部门最高薪水的人员名称 // 思路：先查询部门最高工资，再通过最高工资匹配对应人员 SELECT ename,sal,t.deptno FROM emp e JOIN (SELECT deptno,MAX(sal) AS maxsal FROM emp GROUP BY deptno) t ON e.deptno=t.deptno AND e.sal=t.maxsal 2、哪些人的薪水在部门的平均薪水之上 // 思路：先查出部门平均薪水，再查薪水大于平均薪水的员工 SELE
sql题目（2）
最新发布
qq_58293114的博客
02-01 538
- 23,列出job=‘CLERK’员工的平均薪资 按照部门分组 并且部门编号 in(20,30) 并且部门员工数量>=2人 按照平均薪资 降序排列。-- 08,列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。-- 12,列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。-- 05,列出部门名称和这些部门的员工信息，包括那些没有员工的部门。-- 11,列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。-- 03,列出所有员工的姓名及其直接上级的姓名。
MySQL34道作业（有难度，坚持）
LuerFly的博客！
08-20 228
4、不准用组函数（Max），取得最高薪水（给出两种解决方案）5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）2、哪些人的薪水在部门的平均薪水之上。1、取得每个部门最高薪水的人员名称。
MySQL34道练习题
heliuerya的博客
01-19 3387
MySQL34道练习题
【MySQL】MySQL习题（1-8）
你今天真好看呀
07-31 1016
文章目录问题1：取得每个部门最高薪水的人员名称问题2： 哪些人的薪水在部门的平均薪水之上问题3：取得平均薪水的等级问题4：求每个部门中平均薪水的等级 问题1：取得每个部门最高薪水的人员名称 第一步：取得每个部门的最高薪水（按照部门分组求最大值） select deptno,max(sal) from emp group by deptno; 因为求出的结果是多行多列，可以把子查询作为一张虚拟表...
MySQL笔试题33道（附带答案、表结构与数据
10-25
机测并精心整理筛选的33道MySQL笔试题（附带答案、表结构与数据），安装数据库后导入test.sql文件即可练习
mysql习题库
05-24
这是练习题，附带三张数据库表sql，导进去直接用即可。
Python零基础速成班-第18讲-Python for db MySQL数据库，Web Sever打造网页和API接口服务
06-29
本教程由专业老师设计，由浅入深，带你扎实学习Python知识，附带的课后练习题精心设计，作为学习后知识的巩固和提升。 本教程设置3级标题，对知识点进行分类，内容上由基础+进阶的方式呈现，练习题也设置了必做和...
基于Python和mysql开发的智慧校园答题考试系统(源码+数据库+程序配置说明书+程序使用说明书)
09-11
本项目是一套基于Python和mysql开发的智慧校园答题考试系统，主要针对计算机相关专业的正在做毕设的学生与需要项目实战练习的Python学习者。 包含：项目源码、项目文档、数据库脚本等，该项目附带全部源码可作为毕设...
数据库---34练习题
X..RH
08-13 938
1、取得每个部门最高薪水的人员名称 select e.ename ,t.* from emp e join (select deptno,max(sal) maxsal from emp group by deptno) t on t.deptno=e.deptno and t.maxsal=e.sal; 2、哪些人的薪水在部门的平均薪水之上 select e.ename, e.sal from emp e join (select deptno,avg(sal) avgsal from emp gro
MySQL基础篇 | 经典三十四道练习题
m0_61933976的博客
09-18 5599
关于MySQL的三十四道练习题，大佬们都会做吗
MySQL作业题
小白学习中
03-17 788
日期也可以降序，升序。
MySQL练习题及解题思路
公子二的博客
04-09 772
1、取得每个部门最高薪水的人员名称 第一步：求出每个部门的最高薪水 SELECT e.deptno,MAX(e.sal) FROM emp e GROUP BY e.deptno; 第二部：将以上查询结果当成一个临时表t(deptno,maxsal) SELECT e.deptno,e.ename,e.sal FROM (SELECT e.deptno,MAX(e.sal) AS maxsal F...
第三章 MySQL数据库
weixin_44530052的博客
10-23 3182
MySQL的练习和一些SQL语句
基础sql代码讲解含运行截图（详细版）
adru的博客
05-29 822
LIMIT子句用于控制查询结果的总行数。GROUP BY子句用于将查询结果按照指定的列进行分组。一般建议采用 left join 或者 right join 前者会将左表的数据全部打印 后者会将右表的数据完全打印。ORDER BY子句用于按照指定的列对查询结果进行排序。WHERE子句用于指定一个条件，从表中筛选符合条件的数据。还可以不写字段名字进行插入，但是此种方式必须和数据库字段一一对应，容易出错，所以开发中一般禁用。表示降序 ，默认是按照升序排列的。表示要返回的第一行的偏移量，
mysql笔试题18道
bubbleJessica的博客
03-11 1679
mysql笔试题
mysql练习题一
热门推荐
小芳
02-22 1万+
mysql练习题
mysql数据库查询的习题和答案
05-11
以下是一些MySQL数据库查询练习题及其答案： 1. 查询所有学生的姓名和年龄。 ``` SELECT name, age FROM students; ``` 2. 查询所有科目的名称和学分。 ``` SELECT subject_name, credits FROM subjects; ``` 3. 查询所有学生的姓名和所在班级的名称。 ``` SELECT students.name, classes.class_name FROM students JOIN classes ON students.class_id = classes.class_id; ``` 4. 查询所有选修了“数据库”课程的学生的姓名和所在班级的名称。 ``` SELECT students.name, classes.class_name FROM students JOIN classes ON students.class_id = classes.class_id JOIN enrollments ON students.student_id = enrollments.student_id JOIN courses ON enrollments.course_id = courses.course_id WHERE courses.course_name = "数据库"; ``` 5. 查询所有学生的姓名和选修的所有课程的名称。 ``` SELECT students.name, GROUP_CONCAT(courses.course_name SEPARATOR ',') AS courses_taken FROM students JOIN enrollments ON students.student_id = enrollments.student_id JOIN courses ON enrollments.course_id = courses.course_id GROUP BY students.student_id; ``` 6. 查询所有学生的姓名和选修课程的总学分数。 ``` SELECT students.name, SUM(subjects.credits) AS total_credits FROM students JOIN enrollments ON students.student_id = enrollments.student_id JOIN courses ON enrollments.course_id = courses.course_id JOIN subjects ON courses.subject_id = subjects.subject_id GROUP BY students.student_id; ``` 7. 查询所有学生的姓名和平均成绩（假设每个学生的每个课程只有一次考试）。 ``` SELECT students.name, AVG(grades.grade) AS average_grade FROM students JOIN enrollments ON students.student_id = enrollments.student_id JOIN courses ON enrollments.course_id = courses.course_id JOIN grades ON enrollments.enrollment_id = grades.enrollment_id GROUP BY students.student_id; ``` 以上是一些较为基础的MySQL数据库查询练习题及其答案，希望对你有所帮助！

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值