MYSQL作业

/*----------------------------------------第二章 SQL查询和函数  ----------------------------------*/

-- 1. 创建一个学生表包含：学号、姓名、性别、年龄、出生日期等 字段。

create  table  studentWork(
                              sno integer,
                              sname varchar(10),
                              gender varchar(2),
                              age integer ,
                              birthday varchar(20)
);
alter table  studentwork modify birthday date;

-- 2、修改学生表额外添加成绩字段。

alter  table  studentwork
    add column  achievement double;

-- 3、给学号加上主键约束。

alter  table studentwork
    add  constraint  primary key (sno);

-- 4、给性别加检查约束：提示 check(sex='男' or sex='女') 。

alter table studentwork
    add constraint ck_studentwork_gender check ( gender = '男' or gender = '女' );

-- 5、年龄加检查约束，年龄在18~25之间；提示：check(age>=18 and age<=25)。

alter table studentwork
    add constraint ck_studentwork_age check ( age >= 18 and age <= 25 );

-- 6、向表里插入记录来验证约束是否生效。

insert into studentwork
values (1, '1', '女', 20, '1970-10-22', 60.5);

insert into studentwork
values (1, '1', '1', 20, '2002-10-22', 60.5); # Check constraint 'ck_studentwork_gender' is violated.

insert into studentwork
values (1, '1', '女', 30, '2003-01-23', 60.5);#Check constraint 'ck_studentwork_age' is violated.

-- 7、查询出表中的所有记录。

select  * from  studentwork;

-- 8. 选择部门为30中的所有员工

select  * from  emp where deptno=30 ;

-- 9. 列出所有办事员（CLERK）的姓名，编号和部门编号

select  ename,empno,deptno from emp where job='clerk';

-- 10. 找出佣金高于薪金的员工。

select * from  emp where comm>sal;

-- 11. 找出佣金高于薪金60%的员工

select * from emp where comm > (0.6 * sal);

-- 12. 找出部门10中所有的（MANAGER）和部门20中所有办事员（CLERK）。

select *
from emp
where (deptno = 10 and job = 'manager')
   or (deptno = 20 and job = 'clerk');

-- 13. 找出收取佣金的员工的不同工作

select  job from  emp where  comm>0;

-- 14. 找出不收取佣金或收取的佣金不低于100的员工

select  * from emp where comm>=100 or comm is null;

-- 15. 找出部门10中所有的（MANAGER）和部门20中所有办事员（CLERK）,
-- 和既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料

select  * from emp where (deptno=10 and job='manager') or (deptno=20 and job='clerk') or ((job!='manager' and job!='clerk') and sal>=2000);

/*---------------------------------------函数部分------------------------------------------*/        
-- 16. 找出各月倒数第三天受雇的所有员工

select * from emp where last_day(hiredate)-2=hiredate;

-- 17. 找出早于25年前受雇佣的员工

select * from emp where year(now())-year(hiredate)>25;

-- 18. 以首字母大写其它字母小写的方式显示所有的员工的姓名。

select concat(upper(substr(ename,1,1)),lower(substr(ename,2))) from emp;

-- 以首字母小写其它字母大写的方式显示所有的员工的姓名。

select concat(lower(substr(ename,1,1)),upper(substr(ename,2))) from emp;

-- 19. 显示正好为5字符的员工的姓名

select ename from emp where char_length(ename)=5;

-- 20. 显示不带有‘R’的与员工的姓名

select ename
from emp
where ename not like '%R%';

-- 21. 显示所有员工姓名的前三个字符。

select substr(ename,1,3) from emp;

-- 22. 显示所有员工的姓名，用 A 替换 a 

select replace(ename,'a','A') from emp;

-- 23. 显示 满25年 服务年限的员工的姓名 和受雇日期

select ename,hiredate from emp where  year(now())-year(hiredate)>=25;

-- 24. 显示员工的详细资料，按姓名排序,姓名相同按工资降序排序。

select * from emp order by ename asc , sal desc ;

-- 25. 显示与员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面。

select ename,hiredate from emp order by hiredate asc ;

-- 26. 显示所有员工的姓名，工作和薪金，按工作的降序排序，若工作相同按薪金排序
/*

select  ename,job,sal from emp order by job desc ,sal asc ;

27. 显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在的月排序，
    若月份相同，则将最早的年份的员工排在最前面
*/

select  ename,concat(concat(year(hiredate),'-') ,month(hiredate))from emp order by month(hiredate) asc ,hiredate asc ;

/*--------------------------------------------- 分组查询 -------------------------------------*/
-- 28.按各部门的'办事员'分别统计薪资情况，且平均大于1000的

select deptno,avg(sal) from emp
where job='clerk'
group by deptno
having avg(sal)>1000;

-- 29. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，
-- 并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资排序。

select job,sum(sal) from emp where job!='salesman'
group by job
having sum(sal)>5000
order by sum(sal) asc ;

-- 30. 查询出各部门的部门编号以及各部门的总工资和平均工资。

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

-- 31. 按男生和女生统计JAVA和ORACLE成绩的总分和平均分？
--  1）  建表
CREATE table STUDENT2(
  STUNO       CHAR(4) not null primary KEY,
  STUNAME     VARCHAR2(20),
  GENDER      CHAR(2),
  JAVASCORE   INTEGER,
  ORACLESCORE INTEGER
);

create table student2(
    STUNO char(4) not null primary key ,
    STUNAME varchar(20),
    GENDER char(2),
    JAVASCORE integer,
    ORACLESCORE integer
);
desc student2;

--  2）  插入记录

insert into student2 values ('1','1','1',1,1);
insert into student2 values ('2','1','2',1,1);

--  3）按性别统计成绩：平均分，总成绩等

select GENDER,avg(JAVASCORE)  java平均分,sum(JAVASCORE) Java总成绩,avg(ORACLESCORE),sum(ORACLESCORE)  from student2
group by GENDER;

/*-----------------------------------------------第三章 高级查询  --------------------------------*/

-- 32. 查询部门在‘NEW YORK’工资低于4000，不是‘CLERK’的员工？

   

select emp.*, dept.DNAME, dept.LOC
from emp,
     dept
where emp.deptno = dept.DEPTNO
  and dept.LOC = 'new york'
  and emp.sal >= 4000;

-- 33. 查询部门在‘CHICAGO’，在1981年入职，工资在2000~4000的员工？

select emp.*,dept.DNAME,dept.LOC
from emp,
     dept
where emp.sal between 2000 and 4000
  and year(emp.hiredate) = 1981
and dept.LOC='chicago';

-- 34：查询员工及所在的部门信息（部门号，部门名，所在城市）

select emp.*, dept.DNAME, dept.LOC
from emp,
     dept
where emp.deptno = dept.DEPTNO;

-- 35：查询在10号部门号的员工及部门信息（部门号，部门名，所在城市）

select emp.*,d.DNAME,d.LOC
from emp
         inner join dept d on emp.deptno = d.DEPTNO
where emp.deptno = 10;

-- 36：查询工资低于3000,工作是clerk和salman,部门在"芝加哥”的员工基本信息和员工的部门信息。

select emp.*,d.DNAME,d.LOC
from emp
         inner join dept d on emp.deptno = d.DEPTNO
where emp.sal < 3000
  and emp.job in ('clerk', 'salman')
  and d.LOC = 'chicago';

/*
37: 问题：查看每个员工的工资等级情况
    1等级-->显示为：临时工
    2等级-->显示为：苦力工
    3等级-->？？
*/
select e.*,
       case g.grade
           when 1 then '苦力'
           when 2 then '农民工'
           when 3 then '搬砖'
           when 4 then '白领'
           else '金领'
           end  grade
from emp e, salgrade g
where e.sal between g.losal and g.hisal;

select e.*,
       case
           when g.grade=1 then '苦力'
           when g.grade=2 then '农民工'
           when g.grade=3 then '搬砖'
           when g.grade=4 then '白领'
           else '金领'
           end  grade
from emp e, salgrade g
where e.sal between g.losal and g.hisal;

-- 38.(自连接)查询有上级领导的员工信息以及他的上级领导的信息。显示为：谁（工人）为谁（上级）工作

select  concat(work.ename,' 为 ',mannager.ename,' 打工')from emp work,emp mannager
where work.mgr=mannager.empno;

/*----------------------------------------------- 子查询 -----------------------------------------

子查询注意的问题：
    1、要将子查询发在圆括号内。

    2、子查询可出现在WHERE子句、FROM子句、SELECT列表(此处只能是一个单行子查询)、HAVING子句中。

    3、子查询不能出现在主查询的GROUP BY语句中。

    4、子查询和主查询使用表可以不同，是要子查询返回的结果能够被主查询使用即可。

    5、一般不会在子查询中使用ORDER BY语句，但在TOP-N(只需前几条记录)分析中必须使用ORDER BY语句。

    6、单行子查询只能使用单行操作符，多行子查询只能使用多行操作符。

    7、采用合理的缩进和换行来提过SQL语句的可读性。

    8、子查询中的空值问题。
*/
-- 39. 查询部门名称为SALES和ACCOUNTING的员工信息

select *
from emp e ,
     (select DEPTNO, DNAME from dept where DNAME in ('sales', 'accounting')) as t
where e.deptno = t.DEPTNO;

-- 40. 查询不是经理的员工的信息(使用in 或 not in来做)

select *
from emp
where job not in ('manager');

-- 41. 查询工资比10号部门员工中任意一个低的员工信息

select *
from emp
where sal < any(select sal from emp where deptno = 10);

-- 42. 查询工资比10号部门都要低的员工信息

select *
from emp
where sal < all (select sal from emp where deptno = 10);

-- 43. (稍难)查询出部门名称，部门员工数，部门平均工资，部门最低工资雇员的姓名，及工资等级

select distinct DNAME                                        '部门名称',
                abc                                          '部门员工数',
                aaa                                          '部门平均工资',
                (select  ename from emp where sal=bbb)  '部门最低工资雇员的姓名',
                (select case GRADE
                            when 1 then '一级'
                            when 2 then '二级'
                            when 3 then '三级'
                            when 4 then '四级'
                            else '五级'
                            end
                 from SALGRADE g
                 where bbb between g.LOSAL and g.HISAL  ) as '工资等级'
from emp e
         inner join dept d on e.deptno = d.DEPTNO
         inner join (select count(*) abc, avg(sal) aaa,min(sal) bbb,deptno from emp group by deptno) t on e.deptno = t.deptno;

-- 44. 找出与入职时间最早的员工在同一个部门的员工信息以及所在部门名称

select *,(select DNAME from dept where emp.deptno=dept.DEPTNO) as '部门名称'
from emp
where deptno = (select deptno from emp where hiredate = (select min(hiredate) from emp)) ;

-- 45. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数

select  job,count(*) from emp where sal>1500 group by job;

-- 46. 求出在'SALES'部门工作的员工姓名，假设不知道销售部的部门编号

select ename
from emp
where deptno in (select deptno from dept where DNAME = 'sales');

-- 47. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，和工资等级

select deptno,
       mgr,
       (select case GRADE
                   when 1 then '一级'
                   when 2 then '二级'
                   when 3 then '三级'
                   when 4 then '四级'
                   else '五级'
                   end
        from SALGRADE g
        where emp.sal between g.LOSAL and g.HISAL) as '工资等级'
from emp
where sal > (select avg(sal) from emp);

-- 48. 列出于“SCOTT”从事相同工作的所有员工及部门名称

select *, (select DNAME from dept where emp.deptno = dept.DEPTNO) as '部门名称'
from emp
where job in (select job from emp where ename = 'scott')
  and ename != 'scott';

-- 49. 查询和SMITH部门相同, 岗位相同的人

select *
from emp
where (job, deptno) in (select job, deptno from emp where ename = 'ward');

-- 50. 和ALLEN同部门，工资高于MARTIN的雇员有哪些

select *
from emp
where (deptno in (select deptno from emp where ename = 'allen'))
  and (sal > (select sal from emp where ename = 'MARTIN'));

-- 51. 比blake工资高的雇员有哪些?

select *
from emp
where sal > (select sal from emp where ename = 'blake');

-- 52. 高于30部门最高工资的雇员有哪些?

select *
from emp
where sal > all (select sal from emp where deptno = 30);

-- 53. 查询scott.emp表中所有的经理的信息(此操作子查询会返回多行记录)

select *
from emp
where job = 'manager';

-- 54. 工资高于本部门平均工资的人（拿上游工资的人）有哪些？

select *
from emp e
         inner join (select avg(sal) bbb, deptno from emp group by deptno) t on e.deptno = t.deptno
where e.sal > bbb;

-- 55. 工作和部门与SMITH相同，工资高于JAMES的雇员有哪些?

select *
from emp
where ((job, deptno) in (select job, deptno from emp where ename = 'ward'))
  and (sal > (select sal from emp where ename = 'james'));

-- 56.列出每个部门工作的员工数量，平均工资和平均服务年限

select deptno,count(*) '员工数量', avg(sal) '平均工资', avg(year(now())-year(hiredate)) '平均服务年限'
from emp
group by deptno;

-- 57. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

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

-- 58.列出薪金大于部门30中员工的薪金的所有员工的姓名和薪金

select ename, sal
from emp
where sal>all(select sal from emp where deptno = 30);