35道SQL题目

时间：2024-4-27    作者：老大夫    分类： Mysql

需要导入的sql脚本包：
powernode.zip

1. 取得每个部门最高薪水的人员名称

第一步：取得每个部门最高薪水

select deptno,max(sal) as maxsal
  from emp
  group by deptno;

+--------+---------+
| deptno | maxsal |
+--------+---------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+---------+
第二步：将上面的查询结果当做临时表t，t和emp e进行连接，条件：t.deptno=e.deptno and t.maxsal=e.sal

select t.deptno,t.maxsal,e.ename
     from (select deptno,max(sal) as maxsal from emp group by deptno) as t join emp e
     on t.deptno=e.deptno and  t.maxsal=e.sal;

+--------+---------+-------+
| deptno | maxsal | ename |
+--------+---------+-------+
| 30 | 2850.00 | BLAKE |
| 20 | 3000.00 | SCOTT |
| 10 | 5000.00 | KING |
| 20 | 3000.00 | FORD |
+--------+---------+-------+

2.哪些人的薪水在部门的平均薪水之上

第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal
    from emp
    group by deptno;

+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
第二步：将上面的查询结果当做临时表t，t和emp e连接，条件：t.deptno=e.deptno and e.sal > t.avgsal

select e.ename,e.sal,e.deptno,t.avgsal
    from (select deptno,avg(sal) as avgsal from emp group by deptno)  t join emp  e
    on t.deptno=e.deptno and e.sal > t.avgsal;

+-------+---------+--------+-------------+
| ename | sal | deptno | avgsal |
+-------+---------+--------+-------------+
| ALLEN | 1600.00 | 30 | 1566.666667 |
| JONES | 2975.00 | 20 | 2175.000000 |
| BLAKE | 2850.00 | 30 | 1566.666667 |
| SCOTT | 3000.00 | 20 | 2175.000000 |
| KING | 5000.00 | 10 | 2916.666667 |
| FORD | 3000.00 | 20 | 2175.000000 |
+-------+---------+--------+-------------+

3.取得每个部门平均薪水的等级
第一步：取得每个部门平均薪水

select deptno, avg(sal) as avgsal
     from emp
     group by deptno;

+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
第二步：以上查询结果当做临时表t，和salgrade s表连接，条件：t.avgsal between s.losal and s.hisal;

select t.deptno,t.avgsal,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;

+--------+-------------+-------+
| deptno | avgsal | grade |
+--------+-------------+-------+
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
+--------+-------------+-------+

  1. 取得部门中（所有人的）平均的薪水等级
    每个部门中薪水等级的平均值。

第一步：找出每个人的薪水等级

select e.ename,e.sal,e.deptno,s.grade
     from emp e join salgrade s
     on e.sal between s.losal and hisal;

+--------+---------+--------+-------+
| ename | sal | deptno | grade |
+--------+---------+--------+-------+
| CLARK | 2450.00 | 10 | 4 |
| KING | 5000.00 | 10 | 5 |
| MILLER | 1300.00 | 10 | 2 |
| SMITH | 800.00 | 20 | 1 |
| JONES | 2975.00 | 20 | 4 |
| SCOTT | 3000.00 | 20 | 4 |
| ADAMS | 1100.00 | 20 | 1 |
| FORD | 3000.00 | 20 | 4 |
| ALLEN | 1600.00 | 30 | 3 |
| WARD | 1250.00 | 30 | 2 |
| MARTIN | 1250.00 | 30 | 2 |
| BLAKE | 2850.00 | 30 | 4 |
| TURNER | 1500.00 | 30 | 3 |
| JAMES | 950.00 | 30 | 1 |
+--------+---------+--------+-------+
第二步：基于以上结果继续按照deptno进行分组，对grade求平均值

select t.deptno,avg(t.grade)
     from (select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) t
     group by deptno;

+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 20 | 2.8000 |
| 30 | 2.5000 |
| 10 | 3.6667 |
+--------+--------------+

  1. 不准用分组函数（Max），取得最高薪水（给出两种解决方案）
    第一种方案：降序,limit取第一个。
select sal
    from emp
    order by sal desc
    limit 0,1;

+---------+
| sal |
+---------+
| 5000.00 |
+---------+

第二种方案：自连接

select sal
    from emp
    where sal not in(select distinct  a.sal  from emp a join emp b  on a.sal < b.sal);

+---------+
| sal |
+---------+
| 1300.00 |
| 950.00 |
| 1100.00 |
| 1500.00 |
| 1250.00 |
| 800.00 |
| 2450.00 |
| 2850.00 |
| 1600.00 |
| 2975.00 |
| 3000.00 |
+---------+
+---------+
| sal |
+---------+
| 5000.00 |
+---------+

6.取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
第一种方案：limit
第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal
     from emp
     group by deptno;

+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
第二步：降序，limit 1

select t.deptno,t.avgsal
    from (select deptno,avg(sal) as avgsal from emp group by deptno) t
    order by avgsal desc
    limit 0,1;

+-------------+
| avgsal |
+-------------+
| 2916.666667 |
+-------------+
第三步：第一步 + 第二步联合起来。

select t.deptno,t.avgsal
    from (select deptno,avg(sal) as avgsal from emp group by deptno) t
    order by avgsal desc
    limit 0,1;

+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+

第二种方案：max

select deptno,avg(sal) as avgsal
     from emp
     group by deptno;

+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+

select max(avgsal)
      from (select deptno,avg(sal) as avgsal from emp group by deptno) t;

+-------------+
| max(avgsal) |
+-------------+
| 2916.666667 |
+-------------+

 select deptno , avg(sal) as avgsal
     from emp
     group by deptno
     having avg(sal)=(select max(avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) t);

+--------+-------------+
| deptno | avgsal |
+--------+-------------+
| 10 | 2916.666667 |
+--------+-------------+

7.取得平均薪水最高的部门的部门名称

select d.dname,avg(sal)
     from dept d join emp e
     on d.deptno = e.deptno
     group by e.deptno
     order by avg(sal) desc
     limit 0,1;

+------------+-------------+
| dname | avgsal |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

  1. 求平均薪水的等级最低的部门的部门名称
    第一步：找出每个部门的平均薪水（按照部门名字进行分组，求平均值）
select d.dname,avg(sal) as avgsal
    from dept d join emp e
    on d.deptno =e.deptno
    group by e.deptno;

+------------+-------------+
| dname | avgsal |
+------------+-------------+
| RESEARCH | 2175.000000 |
| SALES | 1566.666667 |
| ACCOUNTING | 2916.666667 |
+------------+-------------+
第二步：将上面的查询结果当做临时表t，和salgrade s连接，条件：t.avgsal between s.losal and s.hisal;

 select t.dname ,t.avgsal,s.grade
     from salgrade s join (select d.dname,avg(sal) as avgsal from dept d join emp e on d.deptno =e.deptno group by e.deptno) t
     on t.avgsal between s.losal and s.hisal;

+------------+-------------+-------+
| dname | avgsal | grade |
+------------+-------------+-------+
| RESEARCH | 2175.000000 | 4 |
| SALES | 1566.666667 | 3 |
| ACCOUNTING | 2916.666667 | 4 |
+------------+-------------+-------+

+-------+-------------+-------+
| dname | avgsal | grade |
+-------+-------------+-------+
| SALES | 1566.666667 | 3 |
+-------+-------------+-------+

第三步：找出最低的等级值
+-------+
| grade |
+-------+
| 3 |
+-------+

select t.dname ,t.avgsal,s.grade
     from salgrade s join (select d.dname,avg(sal) as avgsal from dept d join emp e on d.deptno =e.deptno group by e.deptno) t
     on t.avgsal between s.losal and s.hisal
     order by grade asc
     limit 0,1;

+-------+-------------+-------+
| dname | avgsal | grade |
+-------+-------------+-------+
| SALES | 1566.666667 | 3 |
+-------+-------------+-------+

9.取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
第一步：找出普通员工的最高薪水

 select max(sal)
    -> from emp
    -> where empno not in (select distinct mgr from emp where mgr is not null);

+----------+
| max(sal) |
+----------+
| 1600.00 |
+----------+

第二步：找出大于1600的员工

select ename,sal
     from emp
     where sal > 1600;

+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+

  1. 取得薪水最高的前五名员工
 select ename,sal
     from emp
     order by sal desc
     limit 0,5;

+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

11.取得薪水最高的第六到第十名员工

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

+--------+---------+
| ename | sal |
+--------+---------+
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
+--------+---------+

  1. 取得最后入职的5名员工
 select ename,hiredate
     from emp
     order by hiredate desc
     limit 0,5;

+--------+------------+
| ename | hiredate |
+--------+------------+
| ADAMS | 1987-05-23 |
| SCOTT | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
+--------+------------+

13.取得每个薪水等级有多少员工

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

+----------+-------+
| count(*) | grade |
+----------+-------+
| 3 | 1 |
| 2 | 3 |
| 3 | 2 |
| 5 | 4 |
| 1 | 5 |
+----------+-------+

  1. 列出所有员工及领导的姓名
select a.ename ,b.ename
    from emp a right join emp b
    on b.empno = a.mgr;

+--------+-------+
| 员工 | 领导 |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+

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

select distinct e.empno as '员工编号', e.ename as '员工姓名',e.hiredate ,d.dname as '部门名称'
     from emp e join dept d
     on e.deptno = d.deptno
     join emp f
     on e.mgr = f.empno
     where e.hiredate < f.hiredate;

+----------+----------+------------+----------+------------+------------+
| 员工编号 | 员工姓名 | hiredate | 领导姓名 | hiredate | 部门名称 |
+----------+----------+------------+----------+------------+------------+
| 7369 | SMITH | 1980-12-17 | FORD | 1981-12-03 | RESEARCH |
| 7499 | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES |
| 7521 | WARD | 1981-02-22 | BLAKE | 1981-05-01 | SALES |
| 7566 | JONES | 1981-04-02 | KING | 1981-11-17 | RESEARCH |
| 7698 | BLAKE | 1981-05-01 | KING | 1981-11-17 | SALES |
| 7782 | CLARK | 1981-06-09 | KING | 1981-11-17 | ACCOUNTING |
+----------+----------+------------+----------+------------+------------+

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

+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+

  1. 列出至少有5个员工的所有部门
    +--------+----------+
    | deptno | count(*) |
    +--------+----------+
    | 20 | 5 |
    | 30 | 6 |
    +--------+----------+

18.列出薪金比"SMITH"多的所有员工信息
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

19.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数

+--------+-------+
| deptno | total |
+--------+-------+
| 20 | 5 |
| 30 | 6 |
| 10 | 3 |
+--------+-------+ 临时表t

+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ADAMS | RESEARCH |
| JAMES | SALES |
| MILLER | ACCOUNTING |
+--------+------------+

+--------+------------+-------+
| ename | dname | total |
+--------+------------+-------+
| SMITH | RESEARCH | 5 |
| ADAMS | RESEARCH | 5 |
| JAMES | SALES | 6 |
| MILLER | ACCOUNTING | 3 |
+--------+------------+-------+

  1. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
    +-----------+----------+
    | job | count(*) |
    +-----------+----------+
    | MANAGER | 3 |
    | ANALYST | 2 |
    | PRESIDENT | 1 |
    +-----------+----------+

21.列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+

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

+-------+------------+-------+-------+
| 员工 | dname | 领导 | grade |
+-------+------------+-------+-------+
| FORD | RESEARCH | JONES | 4 |
| SCOTT | RESEARCH | JONES | 4 |
| CLARK | ACCOUNTING | KING | 4 |
| BLAKE | SALES | KING | 4 |
| JONES | RESEARCH | KING | 4 |
| KING | ACCOUNTING | NULL | 5 |
+-------+------------+-------+-------+

  1. 列出与"SCOTT"从事相同工作的所有员工及部门名称

+-------+----------+
| ename | dname |
+-------+----------+
| FORD | RESEARCH |
+-------+----------+

  1. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

+---------+
| sal |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
| 950.00 |
+---------+

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

+----------+
| max(sal) |
+----------+
| 2850.00 |
+----------+

+-------+---------+------------+
| ename | sal | dname |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| FORD | 3000.00 | RESEARCH |
+-------+---------+------------+

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

+----------------+-------------+-------------+--------+
| count(e.ename) | avgsal | avgdate | deptno |
+----------------+-------------+-------------+--------+
| 3 | 2916.666667 | 42.37623333 | 10 |
| 5 | 2175.000000 | 40.40328000 | 20 |
| 6 | 1566.666667 | 42.67490000 | 30 |
| 0 | 0.000000 | 0.00000000 | 40 |
+----------------+-------------+-------------+--------+

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

+--------+---------+------------+
| ename | sal | dname |
+--------+---------+------------+
| SMITH | 800.00 | RESEARCH |
| ALLEN | 1600.00 | SALES |
| WARD | 1250.00 | SALES |
| JONES | 2975.00 | RESEARCH |
| MARTIN | 1250.00 | SALES |
| BLAKE | 2850.00 | SALES |
| CLARK | 2450.00 | ACCOUNTING |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES |
| ADAMS | 1100.00 | RESEARCH |
| JAMES | 950.00 | SALES |
| FORD | 3000.00 | RESEARCH |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+

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

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

+-----------+---------+
| job | minsal |
+-----------+---------+
| CLERK | 800.00 |
| SALESMAN | 1250.00 |
| MANAGER | 2450.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+---------+ 临时表t

+--------+-----------+---------+
| ename | job | minsal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| FORD | ANALYST | 3000.00 |
+--------+-----------+---------+

  1. 列出各个部门的工作岗位是MANAGER的最低薪金
    +--------+----------+
    | deptno | min(sal) |
    +--------+----------+
    | 20 | 2975.00 |
    | 30 | 2850.00 |
    | 10 | 2450.00 |
    +--------+----------+

  2. 列出所有员工的年工资,按年薪从低到高排序
    +--------+----------+
    | ename | yearsal |
    +--------+----------+
    | SMITH | 9600.00 |
    | JAMES | 11400.00 |
    | ADAMS | 13200.00 |
    | MILLER | 15600.00 |
    | TURNER | 18000.00 |
    | WARD | 21000.00 |
    | ALLEN | 22800.00 |
    | CLARK | 29400.00 |
    | MARTIN | 31800.00 |
    | BLAKE | 34200.00 |
    | JONES | 35700.00 |
    | SCOTT | 36000.00 |
    | FORD | 36000.00 |
    | KING | 60000.00 |
    +--------+----------+

  3. 求出员工领导的薪水超过3000的员工名称与领导名称

+-------+------+
| 员工 | 领导 |
+-------+------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+------+

  1. 求出部门名称中,带'S'字符的部门员工的工资合计、部门人数

+----------------+----------+
| count(e.ename) | sumsal |
+----------------+----------+
| 5 | 10875.00 |
| 6 | 9400.00 |
| 0 | 0.00 |
+----------------+----------+

  1. 给任职日期超过41年的员工加薪10%

35.某公司面试题
有3个表S（学生表），C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

mysql> select from s;
+------+-------+
| SNO | SNAME |
+------+-------+
| 1 | 学生1 |
| 2 | 学生2 |
| 3 | 学生3 |
| 4 | 学生4 |
+------+-------+
mysql> select from c;
+------+-------+----------+
| CNO | CNAME | CTEACHER |
+------+-------+----------+
| 1 | 语文 | 张 |
| 2 | 政治 | 王 |
| 3 | 英语 | 李 |
| 4 | 数学 | 赵 |
| 5 | 物理 | 黎明 |
+------+-------+----------+
mysql> select * from sc;
+------+------+---------+
| SNO | CNO | SCGRADE |
+------+------+---------+
| 1 | 1 | 40 |
| 1 | 2 | 30 |
| 1 | 3 | 20 |
| 1 | 4 | 80 |
| 1 | 5 | 60 |
| 2 | 1 | 60 |
| 2 | 2 | 60 |
| 2 | 3 | 60 |
| 2 | 4 | 60 |
| 2 | 5 | 40 |
| 3 | 1 | 60 |
| 3 | 3 | 80 |
+------+------+---------+

  1. 找出没选过“黎明”老师的所有学生姓名。

第一步：先找出选择“黎明”老师的学生的学号
select sno from sc where cno=(select cno from c where cteacher='黎明');

第二步：not in
select sname from s where sno not in(select sno from sc where cno=(select cno from c where cteacher='黎明'));
+-------+
| sname |
+-------+
| 学生3 |
| 学生4 |
+-------+

  1. 列出2门以上（含2门）不及格学生姓名及平均成绩。

第一步：找出每个学生的平均成绩
select sno,avg(scgrade) as avgscore from sc group by sno;
+------+----------+
| sno | avgscore |
+------+----------+
| 1 | 46 |
| 2 | 56 |
| 3 | 70 |
+------+----------+

select s.sno,s.sname from sc join s on sc.sno=s.sno where sc.scgrade < 60 group by s.sno,s.sname having count(*) >= 2;
+------+-------+
| sno | sname |
+------+-------+
| 1 | 学生1 |
+------+-------+

select a.avgscore, b. from (select sno,avg(scgrade) as avgscore from sc group by sno) a join (select s.sno,s.sname from sc join s on sc.sno=s.sno where sc.scgrade < 60 group by s.sno,s.sname having count() >= 2) b on a.sno=b.sno;
+----------+------+-------+
| avgscore | sno | sname |
+----------+------+-------+
| 46 | 1 | 学生1 |
+----------+------+-------+

  1. 既学过1号课程又学过2号课所有学生的姓名。

select sno from sc where cno=1;
+------+
| sno |
+------+
| 1 |
| 2 |
| 3 |
+------+

select sno from sc where cno=2;
+------+
| sno |
+------+
| 1 |
| 2 |
+------+

select sno from sc where cno=1 and sno in(select sno from sc where cno=2);
+------+
| sno |
+------+
| 1 |
| 2 |
+------+

select s.sname from sc join s on sc.sno=s.sno where sc.cno=1 and sc.sno in(select sno from sc where cno=2);
+-------+
| sname |
+-------+
| 学生1 |
| 学生2 |
+-------+

版权所有：伸手党盘
文章标题：35道SQL题目
文章链接：https://ssdpan.cn/?post=412
本站文章来源于网络搜集和原创，仅供学习使用，未经授权请勿用于任何商业用途，如有侵权及时联系删除

推荐阅读：

扫描二维码，在手机上阅读


搜索

电视test

    hls.js player example

天气

辽宁交通广播

        菜鸟学堂

沈阳经济广播

        菜鸟学堂

个人资料

    blogger

    老大夫

    兽人永不为奴，除非包吃包住。

主页链接

分类

最新评论

链接

倒计时

    本日剩余 59.6%
    本周剩余 37.1%
    本月剩余 22.0%
    本年剩余 52.2%

日历

存档

标签

最新文章

热门文章