数据库练习

圈外人员

已于 2024-08-14 11:13:05 修改

阅读量70

点赞数

文章标签：数据库

于 2023-01-30 15:32:18 首次发布

本文链接：https://blog.csdn.net/cxxc980322/article/details/128802142

版权

原文：SQL经典训练【1】_爪哇的博客-CSDN博客

CREATE TABLE emp(
    empno    INT(4) NOT NULL COMMENT '员工编号',
    ename    VARCHAR(10) COMMENT '员工姓名',
    job      VARCHAR(9) COMMENT '职位',
    mgr      INT(4) COMMENT '经理',
    hiredate DATE DEFAULT NULL COMMENT '雇佣日期',
    sal      DOUBLE(7, 2) COMMENT '薪水',
    comm     DOUBLE(7, 2) COMMENT '津贴',
    deptno   INT(2) COMMENT '部门编号',
    PRIMARY KEY (empno)
) COMMENT '员工表';
CREATE TABLE dept(
    deptno INT(2) NOT NULL COMMENT '部门编号',
    dname  VARCHAR(14) COMMENT '部门名字',
    loc    VARCHAR(13) COMMENT '位置',
    PRIMARY KEY (deptno)
) COMMENT '部门表';
CREATE TABLE salgrade(
    grade INT COMMENT '等级',
    losal INT COMMENT '最高薪水',
    hisal INT COMMENT '最低薪水'
) COMMENT '薪水级别表';

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7369, 'smith', 'clerk', 7902, '1980-12-17', 800, NULL, 20),
       (7499, 'allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30),
       (7521, 'ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30),
       (7566, 'jones', 'manager', 7839, '1981-04-02', 2975, NULL, 20),
       (7654, 'martin', 'salesman', 7698, '1981-09-28', 1250, 1400, 30),
       (7698, 'blake', 'manager', 7839, '1981-05-01', 2850, NULL, 30),
       (7782, 'clark', 'manager', 7839, '1981-06-09', 2450, NULL, 10),
       (7788, 'scott', 'analyst', 7566, '1987-04-19', 3000, NULL, 20),
       (7839, 'king', 'president', NULL, '1981-11-17', 5000, NULL, 10),
       (7844, 'turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30),
       (7876, 'adams', 'clerk', 7788, '1987-05-23', 1100, NULL, 20),
       (7900, 'james', 'clerk', 7698, '1981-12-03', 950, NULL, 30),
       (7902, 'ford', 'analyst', 7566, '1981-12-03', 3000, NULL, 20),
       (7934, 'miller', 'clerk', 7782, '1982-01-23', 1300, NULL, 10);
INSERT INTO dept (deptno, dname, loc)
VALUES (10, 'accounting', 'new york'),
       (20, 'research', 'dallas'),
       (30, 'sales', 'chicago'),
       (40, 'operations', 'boston');
INSERT INTO salgrade (grade, losal, hisal)
VALUES (1, 700, 1200),
       (2, 1201, 1400),
       (3, 1401, 2000),
       (4, 2001, 3000),
       (5, 3001, 9999);

1 取得薪水最高的前五名员工
select * from emp order by sal desc limit 5;
2 取得薪水最高的第六到第十名员工
select * from emp order by sal desc limit 5(从多少条数据开始),5(统计多少条数据);
3 取得最后(也就是最大的日期)入职的5名员工
select *from emp  ORDER BY hiredate desc limit 5;
4 取得每个薪水等级有多少员工
select salgrade.grade,count(grade),emp.*,salgrade.* 
from emp join salgrade on sal BETWEEN losal and hisal 
group by grade ORDER BY grade  ;
5 取得每个部门最高薪水的人员名称

6 哪些人的薪水在部门平均薪水之上
select ename,deptno,sal from emp JOIN (select avg(sal) a,deptno d 
from emp  GROUP BY deptno) t on emp.deptno=t.d and emp.sal>t.a;
7 取得部门中（所有人的）平均薪水等级
select grade,deptno,t.a,losal,hisal from salgrade
join (select avg(sal)a ,deptno from emp GROUP BY deptno)t 
on a BETWEEN losal and hisal;
8 不准用组函数（MAX），取得最高薪水（给出两种解决方案）
select sal from emp ORDER BY sal desc limit 1;
9 取得平均薪水最高的部门的部门编号（至少两个方案）
select avg(sal) 平均工资,deptno from emp  
GROUP BY deptno 
ORDER BY avg(sal)
DESC limit 1;
10 取得平均薪水最高的部门的部门名称
select avg(sal) 平均工资,dname,dept.deptno from emp 
join dept on emp.deptno=dept.deptno 
GROUP BY deptno 
ORDER BY avg(sal) DESC limit 1;
11 求平均薪水的等级最低(高)的部门的部门名称（有问题）
select dname from dept 
join (select grade,deptno,t.a,losal,hisal from salgrade
join (select avg(sal)a ,deptno from emp GROUP BY deptno)t 
on a BETWEEN losal and hisal;)m on dept.deptno=m.deptno;
12 取得比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名

13 列出所有员工及领导的名字
select e1.ename,emp.ename from emp
join emp e1 on emp.empno=e1.mgr;
14 列出受雇日期早于其直接上级的所有员工编号、姓名、部门名称
select e1.ename,e1.deptno,dname from emp
join emp e1 on emp.empno=e1.mgr 
join dept on e1.deptno=dept.deptno
where e1.hiredate<emp.hiredate;
15 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select dname,emp.* from emp
right JOIN dept on dept.deptno=emp.deptno;
16 列出至少有5个员工的所有部门
select emp.deptno,count(*),dname 
from emp 
join dept on emp.deptno=dept.deptno
group by deptno HAVING count(*)>=5;
18 列出薪水比“smith”多的所有员工信息
select *from emp where sal >(select sal from emp where ename ='smith');
19 列出所有 “clerk”（办事员）的姓名及其部门名称，部门人数（缺失）
,count(dept.deptno)
select emp.ename,dept.dname from emp 
join dept on emp.deptno=dept.deptno
where job='clerk';
20 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数（缺失）
select DISTINCT(job) from emp where sal >1500
21 列出在部门“sales”（销售部）工作的员工的姓名
select *from emp where deptno=(select deptno from dept where dname='sales');
22 列出薪金高于公司平均薪金的所有员工（少了一条数据），所在部门、上级领导、雇员的工资等级
select e1.ename,emp.ename,dept.dname,grade,e1.sal from emp 
join emp e1 on emp.empno=e1.mgr
join dept on emp.deptno=dept.deptno
join salgrade on emp.sal BETWEEN losal and hisal
where e1.sal >(select avg(emp.sal)from emp);
23 列出与“scott”从事相同工作的所有员工及部门名称
select *from emp where job=(select job from emp where ename='scott');
select dname from dept where deptno=20;
24 列出薪金等于部门30中员工的薪金的其它员工的姓名和薪金
select ename,emp.sal from emp 
join (select sal from emp where deptno=30)t on emp.sal=t.sal;
25 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称

26 列出在每个部门工作的员工数量、平均工资和平均服务期限
select deptno,count(deptno),avg(sal) from emp GROUP BY deptno;
27 列出所有员工的姓名、部门名称和工资
select ename,dname,sal from emp,dept where emp.deptno=dept.deptno;
28 列出所有部门的详细信息和人数
select count(emp.deptno),dept.* from emp
right join dept on dept.deptno=emp.deptno
GROUP BY deptno;
29 列出各种工作的最低工资及从事此工作的雇员姓名
select ename,emp.job from emp
join (select job,min(sal) a from emp
GROUP BY job )t on emp.job=t.job and emp.sal=t.a;
30 列出各个部门manager的最低薪金

31 列出所有员工的年工资，按年薪从低到高排序
select sal*12,emp.*from emp ORDER BY sal*12 DESC;
32 求出员工领导的薪水超过3000的员工名称和领导名称

33 求部门名称中带“S”字符的部门员工的工资合计、部门人数

34 给任职日期超过30年的员工加薪10%