mysql数据库练习作业

1、先模拟emp表和dept表，做初始数据：

create table DEPT
(
deptno int not null,
dname VARCHAR(14),
loc VARCHAR(13)
);

alter table DEPT
add constraint PK_DEPT primary key (DEPTNO);

create table EMP
(
empno int,
ename VARCHAR(10),
job VARCHAR(9),
mgr int,
hiredate date,
sal double(7,2),
comm double(7,2),
deptno 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;

str_to_date(str,‘%Y-%m-%d’)
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, ‘dog’, ‘code’, 7369, str_to_date(‘05-06-2018’, ‘%d-%m-%Y’), 5000, 5000, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, ‘SMITH’, ‘CLERK’, 7902, str_to_date(‘17-12-1980’, ‘%d-%m-%Y’), 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, ‘ALLEN’, ‘SALESMAN’, 7698, str_to_date(‘20-02-1981’, ‘%d-%m-%Y’), 1600, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, ‘WARD’, ‘SALESMAN’, 7698, str_to_date(‘22-02-1981’, ‘%d-%m-%Y’), 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, ‘JONES’, ‘MANAGER’, 7839, str_to_date(‘02-04-1981’, ‘%d-%m-%Y’), 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, ‘MARTIN’, ‘SALESMAN’, 7698, str_to_date(‘28-09-1981’, ‘%d-%m-%Y’), 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, ‘BLAKE’, ‘MANAGER’, 7839, str_to_date(‘01-05-1981’, ‘%d-%m-%Y’), 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, ‘SCOTT’, ‘ANALYST’, 7566, str_to_date(‘19-04-1987’, ‘%d-%m-%Y’), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, ‘KING’, ‘PRESIDENT’, null, str_to_date(‘17-11-1981’, ‘%d-%m-%Y’), 5000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, ‘TURNER’, ‘SALESMAN’, 7698, str_to_date(‘08-09-1981’, ‘%d-%m-%Y’), 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, ‘ADAMS’, ‘CLERK’, 7788, str_to_date(‘23-05-1987’, ‘%d-%m-%Y’), 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, ‘JAMES’, ‘CLERK’, 7698, str_to_date(‘03-12-1981’, ‘%d-%m-%Y’), 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, ‘FORD’, ‘ANALYST’, 7566, str_to_date(‘03-12-1981’, ‘%d-%m-%Y’), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, ‘MILLER’, ‘CLERK’, 7782, str_to_date(‘23-01-1982’, ‘%d-%m-%Y’), 1300, null, 10);
commit;

​

               --1、选择30部门中的雇员　　
                   select * from emp where deptno=30
              
              --2、列出所有办事员的姓名、编号和部门　　
                select * from emp where job='CLERK'
               
               --3、找出佣金高于薪金的雇员　　
               select * from emp where sal<comm
             
             --4、找出佣金高于薪金60%的雇员
               select * from emp where comm>sal*0.6　　
               
               --5、找出部门10中所有经理和部门20中的所有办事员的详细资料　
               select * from emp where (deptno=10 and job='MANAGER')or(deptno=20 and job='CLERK')
               
               --6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料　　
        select * from emp where (deptno=10 and job='MANAGER')or(deptno=20 and job='CLERK')or(job<>'CLERK'and job<>'MANAGER' and sal>=2000)
               
                --7、找出收取佣金的雇员的不同工作　　
               select distinct job from emp where comm>0
               --8、找出不收取佣金或收取的佣金低于100的雇员
               select * from emp where comm is null or comm<100
               
               --9、找出各月最后一天受雇的所有雇员
               select * from emp where to_char(hiredate+1,'DD')='01' 

                --10 找出早于25年之前受雇的雇员
               select * from emp where add_months(hiredate,25*12)<sysdate
               
               --11、显示只有首字母大写的所有雇员的姓名　
               select * from emp where initcap(ename)=ename 　
               
               --12、显示正好为6个字符的雇员姓名　
               select * from emp where length(ename)=6
               
                --13、显示不带有'R'的雇员姓名　
               select ename from emp where ename not like '%R%' 　
               
                --14、显示所有雇员的姓名的前三个字符　
               select substr(ename,1,3) from emp　
               
               --15、显示所有雇员的姓名，用a替换所有'A'
               select replace(ename,'A','a') from emp　　
               
               --16、显示所有雇员的姓名以及满10年服务年限的日期　　
                select ename,add_months(hiredate,10*12) from emp
                
                --17、显示雇员的详细资料，按姓名排序
                select * from emp order by ename 　
                
                --18、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面　　
                select ename from emp order by hiredate
                
                --19、显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序　　
                select ename,sal,comm,job from emp order by job desc,sal asc
                
                --20、显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面　　
               select ename,to_char(hiredate,'YYYY') year,to_char(hiredate,'MM') month from emp  order by hiredate,month
               
                --21、显示在一个月为30天的情况下所有雇员的日薪金　　
                select ename,sal/30 日薪金 from emp
                
                --22、找出在（任何年份的）2月受聘的所有雇员　　
                select * from emp where to_char(hiredate,'MM')='02'
                
                --23、对于每个雇员，显示其加入公司的天数　　
                select sysdate-hiredate from emp
                
                --24、显示姓名字段的任何位置，包含 "A" 的所有雇员的姓名　　
                select ename from emp where ename like '%A%'
                
                -- 25、以年、月和日显示所有雇员的服务年限　
                select ename,to_char(hiredate,'YYYY-MM-DD')服务年限 from emp
                
             

25.查询各部门的最高薪水、最低薪水、平均薪水….
SELECT DEPTNO,”MAX”(SAL),”MIN”(SAL),”AVG”(SAL) FROM EMP GROUP BY DEPTNO;

26.查询‘SMITH’的领导姓名
SELECT ENAME FROM EMP WHERE EMPNO = (SELECT MGR FROM EMP WHERE ENAME = ‘SMITH’);

27.查询部门名称是‘SALES’的员工信息
SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = ‘SALES’);

28.查询公司中薪水最高的员工信息
SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP);

29.查询公司所有员工的个数
SELECT “COUNT”(ENAME) FROM EMP ;

30.查询公司中最高薪水是多少
SELECT “MAX”(SAL) FROM EMP ;

31.查询公司中平均奖金是多少
SELECT “AVG”(“NVL”(COMM, 0)) FROM EMP ;

32.查询公司中最晚入职的时间
SELECT “MAX”(HIREDATE) FROM EMP ;

33.查询公司中有奖金的人数
SELECT “COUNT”(ENAME) FROM EMP WHERE (COMM IS NOT NULL) ;

34.查询20部门的最高薪水是多少
SELECT “MAX”(SAL) FROM EMP WHERE DEPTNO = 20 ;

35.查询各部门的平均薪水及部门编号，部门名称。
SELECT “AVG”(SAL),DNAME,EMP.DEPTNO FROM EMP,DEPT where EMP.DEPTNO = DEPT.DEPTNO GROUP BY DNAME,EMP.DEPTNO;

36.查询各部门中最高薪水的员工编号，姓名…
select empno , ename,DEPTNO from emp WHERE SAL in (select “MAX”(SAL) from EMP GROUP BY DEPTNO);

37.查询所有员工姓名中包含‘A’的最高薪水
SELECT “MAX”(SAL) FROM EMP WHERE ENAME LIKE ‘%A%’ ;

38.查询各岗位的最高薪水，最低薪水。要求只统计薪水>1000的
SELECT “MAX”(SAL),”MIN”(SAL) FROM EMP WHERE SAL > 1000 GROUP BY “JOB”;

39.查询各部门的平均薪水及部门编号，要求只列出平均薪水>2000
SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000;

40.查询各部门的平均薪水及部门编号，要求只有员工姓名中包含
‘A’才参与统计，只列出平均薪水>1500的，按照平均薪水降序排列
SELECT AVG(SAL),DEPTNO FROM EMP where ENAME LIKE ‘%A%’ GROUP BY DEPTNO HAVING AVG(SAL) > 1500 ORDER BY AVG(SAL) DESC;

41.查询各部门最高薪水的员工信息（有坑）（薪水和部门编号都需要匹配）
select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO);

42.查询最高薪水的员工信息
SELECT * FROM EMP WHERE SAL = (SELECT “MAX”(SAL) FROM EMP);

43.查询薪水大于该部门平均薪水的员工信息
select * from emp e1 where sal > (
select avg(sal) from emp e2 where e1.deptno = deptno
);

44.查询雇员表中，姓名为SMITH的雇员，截止到今天共工作了多少周，则可以使用如下的SQL语句
SELECT ROUND((SYSDATE-HIREDATE)/7) AS “Weeks” FROM EMP WHERE ENAME = ‘SMITH’;

45.查询各部门最高薪水的员工信息
select * from EMP where (deptno,SAL) in (select deptno,MAX(sal) from emp GROUP BY DEPTNO);

46.查询‘SMITH’的领导姓名
select ENAME from EMP where EMPNO = (select MGR from EMP where ENAME = ‘SMITH’) ;

47.查询部门名称是‘SALES’的员工信息
select * from EMP where DEPTNO = (SELECT DEPTNO FROM DEPT WHERE dname = ‘SALES’) ;

48.查询公司中薪水最高的员工信息
select * from EMP where SAL = (SELECT MAX(SAL) FROM EMP) ;

49.查询薪水等级为4的员工信息
select * from emp where sal between
(select LOSAL from salgrade where grade = 4) AND
(select HISAL from salgrade where grade = 4) ;

50.查询领导者是‘BLAKE’的员工信息
select * from emp where MGR = (select EMPNO from emp where ENAME = ‘BLAKE’);

51.查询最高领导者的薪水等级
select grade FROM SALGRADE where (SELECT SAL from emp where mgr is NULL) BETWEEN LOSAL AND HISAL;

52.查询薪水最低的员工信息
select * from emp where SAL = (select MIN(sal) from emp);

53.查询和SMITH工作相同的员工信息
select * from emp where JOB = (select JOB from emp where ENAME = ‘SMITH’) ;

54.查询不是领导的员工信息
select * from emp where EMPNO not in (SELECT “NVL”(MGR, 0) FROM EMP) ;

select * from emp e1 where not EXISTS
(select * from emp e2 where e2.mgr = e1.empno);

55.查询平均工资比10部门低的部门编号
select deptno from emp group by deptno having avg(sal) <
(select avg(sal) from emp where deptno = 10);

56.查询在纽约工作的所有员工
select * from EMP where DEPTNO = (select DEPTNO from DEPT where LOC = ‘NEW YORK’);

57.查询‘SALES’部门平均薪水的等级
select grade from SALGRADE where
(select AVG(SAL) from emp where DEPTNO =
(select DEPTNO FROM DEPT WHERE dname = ‘SALES’) ) BETWEEN LOSAL AND HISAL;

58.查询10号部门的员工在整个公司中所占的比例：
select (select COUNT(DEPTNO) from EMP where DEPTNO = 10) / (select COUNT(*) from EMP) FROM dual;

59.查询员工姓名及领导者姓名
select a.ename AS 员工姓名 ,b.ename AS 领导姓名
from emp a LEFT JOIN emp b on a.mgr = b.empno;



60.查询各岗位工资小于该岗位平均工资的员工信息；
select * from emp e1 where sal < (select avg(sal) from emp e2 where e1.job= job group by job);

61.查询所有领导的信息：要求使用exists关键字
select * from emp e1 where EXISTS
(select * from emp e2 where e2.mgr = e1.empno);

62.查询所有员工的姓名，薪水，部门名称
select ename,sal,dname from emp,dept where EMP.DEPTNO = DEPT.DEPTNO;