数据库考试

这次考试很差所以要记录一下

#1.列出员工表中每个部门的员工数，和部门号
select count(1),deptno from emp GROUP BY deptno;
#2.列出员工表中每个部门的员工数（员工数必须大于 3），和部门名称 
select count(1) 员工数,deptno from emp GROUP BY deptno HAVING count(1)>3;
#3.找出工资比WARD多的员工 
select e.* from emp e JOIN emp e1 on e1.sal<e.sal WHERE e1.ename= 'WARD';
#4.列出所有员工的姓名和其上级的姓名 
select e.ename 员工 ,e1.ename 上级的姓名 from emp e JOIN emp e1 on e.mgr=e1.empno;
#5.以职位分组，找出平均工资最高的两种职位 
SELECT avg(sal) FROM emp GROUP BY job ORDER BY AVG(sal) DESC LIMIT 0,2;
#6.查找出不在部门 20，且比部门 20 中任何一个人工资都高的员工姓名、部门名称 
#比任何人的工资高就是比工资最高的还要高 
select e.ename,d.dname from emp e JOIN dept d on e.deptno = d.deptno WHERE e.sal>(SELECT max(sal) from emp e2 WHERE e2.deptno= 20 GROUP BY e2.deptno) and e.deptno!=20;
#7.得到平均工资大于 2000 的工作职种 
SELECT DISTINCT job FROM emp GROUP BY sal HAVING AVG(sal)>2000;
#8.分部门得到  工资大于 2000 的所有员工的平均工资，并且平均工资还要大于 2500 
SELECT deptno,avg(sal) FROM emp where sal>2000 GROUP BY deptno HAVING avg(sal)>2500;
#9.得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 
SELECT d.* FROM dept d JOIN (SELECT deptno, SUM(sal) total FROM emp GROUP BY deptno) t ON d.`deptno` = t.deptno ORDER BY total ASC LIMIT 1;
 
#10.分部门得到平均工资等级为 3 级（等级表）的部门编号 
SELECT DISTINCT t.a,t.deptno FROM emp e Join (SELECT deptno ,avg(e1.sal) a from emp e1 GROUP BY e1.deptno) t on t.deptno=e.deptno   JOIN  grede on a BETWEEN  lower and hige where grade=3;


#11.查找出部门 10 和部门 20 中，工资最高第 3 名到工资第 5 名的员工的员工名字，部门名字，部门位置 
SELECT e.ename,e.sal,d.dname,d.loc FROM emp e JOIN dept d on e.deptno = d.deptno WHERE e.deptno in (10,20) ORDER BY e.sal DESC LIMIT 2,3;
#12.查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入 
 SELECT e.empno,e.ename,e.sal+IFNULL(e.comm,0) from emp e JOIN emp e1 on e1.empno = e.mgr where e1.sal+IFNULL(e1.comm,0)<e.sal+IFNULL(e.comm,0);
#13.查找出工资等级不为 4 级的员工的员工名字，部门名字，部门位置 
SELECT e.ename,d.dname,d.loc from emp e JOIN dept d on d.deptno=e.deptno JOIN grede on e.sal+IFNULL(e.comm,0) BETWEEN lower and hige where grade!=4;
#14.查找出职位和'MARTIN'  或者'SMITH'一样的员工的平均工资 
select avg(sal) from emp GROUP BY job in (SELECT job from emp e WHERE e.ename in ('MARTIN','SMITH') );
#15.查找出不属于任何部门的员工 
select * from emp WHERE deptno is NULL;
#16.按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）;

select num , d.dname,d.loc FROM dept d join (SELECT deptno, COUNT(1)  num FROM emp GROUP BY deptno) t ON t.deptno = d.deptno ORDER BY num DESC LIMIT 1, 4;

#17.查询出 king 所在部门的部门号\部门名称\部门人数 
SELECT d.*,t.num  FROM dept d join (select deptno, count(1)num FROM emp e GROUP BY e.deptno) t on t.deptno = d.deptno JOIN emp e on e.deptno = d.deptno where e.ename='king';
#18.查询出 king 所在部门的工作年限最大的员工名字 
SELECT e.ename,e.hiredate from emp e  JOIN emp e1 on e1.deptno = e.deptno where e1.ename='king' and e.hiredate in (select min(hiredate) FROM emp GROUP BY deptno) ;
#19.查询出工资成本最高的部门的部门号和部门名称 
SELECT d.deptno,d.dname,d.loc FROM dept d   join  (SELECT sum(sal),deptno FROM emp GROUP BY deptno ORDER BY sum(sal) DESC LIMIT 1) t on t.deptno = d.deptno;
#20.查询工资不在1500-2850之间的所有雇员名及工资
SELECT e.ename,e.sal FROM emp e WHERE e.sal+IFNULL(e.comm,0) NOT BETWEEN 1500 and 2850;

1. 根据user数据表编写SQL查出所有name重复的记录且按照age降序(6)。

id	name	age
1001	zhangsan	21
1002	lisi	22
1003	zhangsan	20
1004	lisi	19
1005	wangwu	18
1006	zhaoliu	22

、SELECT e.ename,e.sal,d.dname,d.loc FROM emp e JOIN dept d on e.deptno = d.deptno WHERE e.deptno in (10,20) ORDER BY e.sal DESC LIMIT 2,3;

1. 编写一条SQL实现下列功能(7)

表内容：

 2022-05-09 胜

 2022-05-09 胜

 2022-05-09 负

 2022-05-09 负

 2022-05-10 胜

 2022-05-10 负

 2022-05-10 负

 

如果要生成下列结果, 该如何写sql语句?

 

     日期      胜 负

 2005-22-09  2  2

 2005-22-10  1  2

#2

SELECT date ,sum(CASE result
    WHEN '胜' THEN
        1
    ELSE
        0
END
) 胜,

sum(CASE result

    WHEN '负' THEN

        1

    ELSE

        0

END

) 负 FROM two GROUP BY date;

3. 表形式如下： (7)

Year      Salary

2018       1000

2019       2000

2020       3000

2021       4000

想得到如下形式的查询结果

Year      Salary

2018      1000

2019      3000

2020      6000

2021      10000

SELECT t1.YEAR,sum(t2.salary) FROM three t1 LEFT JOIN three t2 on t2.`year`<=t1.`year` GROUP BY t1.`year`;

1. 怎么把表中的数据(10分)

year	month	amount
2020	1	1.1
2020	2	1.2
2020	3	1.3
2020	4	1.1
2021	1	1.2
2021	2	1.3
2021	3	1.5
2021	4	1.2

查询成如下的结果

year	m1	m2	m3	m4
2020	1.1	1.2	1.3	1.1
2021	1.2	1.3	1.5	1.2

请编写一条SQL语句实现上图的效果

#4

SELECT year ,sum(CASE month
    WHEN 1 THEN
        amount
    ELSE
        0
END
) m1,

sum(CASE month

    WHEN 2 THEN

        amount

    ELSE

        0

END

) m2,

sum(CASE month

    WHEN 3 THEN

        amount

    ELSE

        0

END

) m3,

sum(CASE month

    WHEN 4 THEN

        amount

    ELSE

        0

END

) m4 FROM four GROUP BY year