第二天

第三章：
学习内容：DML，DQL
练习1
1.向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。
INSERT INTO dept (DEPTNO,DNAME,Daddress) VALUES (50,”HR”,”SY”);
2.向部门表新增一个部门，部门编号为60，部门名称为MARKET。
INSERT INTO dept (DEPTNO,DNAME) VALUES (60,”MARKET”);

练习2
1.向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空
INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL) VALUES (8888,”BOB”,”CLERK”,7788,”1985-03-03”,3000);

练习3
1.使用CREATE TABLE emp_back as
SELECT FROM EMP WHERE 1=0，创建emp_back表,拷贝下来即可。
CREATE TABLE emp_back as
SELECT FROM EMP WHERE 1=0;

2.把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中。
INSERT INTO emp_back SELECT * FROM emp WHERE HIREDATE>”1982-1-1” ;

练习4
1.修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天
update emp SET HIREDATE=DATE_ADD(HIREDATE,INTERVAL 10 DAY) WHERE DEPTNO=20 ;
2.修改奖金为null的员工，奖金设置为0
UPDATE emp SET COMM =0 WHERE COMM IS NULL;
3.修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500
UPDATE emp SET SAL=SAL+500 WHERE DEPTNO IN (SELECT DEPTNO FROM dept WHERE LOC=”NEW YORK” OR LOC=”CHICAGO”);

练习5
1.删除经理编号为7566的员工记录
DELETE FROM emp WHERE MGR=7566;
2.删除工作在NEW YORK的员工记录
DELETE FROM emp WHERE address=”NEW YORK”;
3.删除工资大于所在部门平均工资的员工记录 —每个部门的平均薪资
解1：DELETE FROM emp WHERE emp.EMPNO IN
( SELECT from(SELECT e.EMPNO from
emp e INNER JOIN (SELECT DEPTNO ,AVG(SAL) AS “SAL2” from emp where DEPTNO IS NOT NULL GROUP BY DEPTNO) a
ON e.DEPTNO=a.DEPTNO WHERE e.SAL>a.SAL2) c );
解2：DELETE FROM emp WHERE empno IN (SELECT FROM (SELECT empno FROM emp B JOIN (SELECT AVG(SAL) as avg,deptno FROM emp GROUP BY deptno) AS A ON A.deptno = B.deptno WHERE sal > A.avg) AS TEMP); —待验证？？？
解3：
DELETE FROM emp WHERE emp.EMPNO IN (SELECT * FROM (SELECT emp.EMPNO FROM emp,(SELECT AVG(emp.SAL) b,emp.DEPTNO FROM emp GROUP BY emp.DEPTNO) a WHERE a.DEPTNO =emp.DEPTNO AND emp.SAL > a.b) c ) —待验证？？？

练习5
1.test表为空表，分析如下语句操作后，最后test表的状态。
BEGININSERT INTO test(id,name) values(1, ‘a’)；
INSERT INTO test(id,name) values(2, ‘b’)；
SAVEPOINT s1; — 设置定点
INSERT INTO test(id,name) values(3, ‘c’)；
INSERT INTO test(id,name) values(4, ‘d’)；
DELETE FROM test WHERE id in (1,3); —设置定点
ROLLBACK TO s1; —回滚到s1断点 ， 则s1后的执行语句到当前回滚操作的更新语句撤销并恢复
DELETE FROM test WHERE id in (2,4);
COMMIT;
ROLLBACK;

第三章课后作业：
略

第四章：

课后作业：
1.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
SELECT ENAME,HIREDATE,JOB FROM emp WHERE JOB NOT IN (‘SALESMAN’) AND HIREDATE>’1982-07-09’;
2.查询员工姓名的第三个字母是a的员工姓名。
SELECT ENAME FROM emp WHERE ENAME LIKE ‘__a%’;
3.查询除了10、20号部门以外的员工姓名、部门编号。
SELECT ENAME,EMPNO FROM emp WHERE DEPTNO NOT IN(10,20);
4.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。
SELECT FROM emp WHERE DEPTNO=30 ORDER BY SAL,ENAME
5.查询没有上级的员工(经理号为空)的员工姓名。
SELECT FROM emp WHERE MGR IS NULL;
6.查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。
SELECT ENAME,SAL,DEPTNO FROM emp WHERE SAL>=4500 AND (DEPTNO=10 OR DEPTNO=20);

练习1
1.使用两种方式查询所有员工(EMP)信息
SELECT *from emp;
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL FROM emp;

2.查询(EMP)员工编号、员工姓名、员工职位、员工月薪、工作部门编号。
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM emp;

练习2
1.员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。
SELECT ENAME,SAL0.02 from emp ;
2.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)
SELECT ENAME as 员工姓名,SAL6+SAL0.026 AS “年薪” FROM eMP;

练习3
1.员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。
SELECT ENAME,SAL0.02 from emp ;
2.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)
SELECT ENAME as 员工姓名,SAL6+SAL0.026 AS “年薪” FROM emp;
1.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分)，要求显示列标题为员工姓名，工资收入，奖金收入，总收入。
SELECT ENAME AS 员工姓名, SAL6+SAL0.026 AS 工资收入,COMM AS 奖金收入,SAL6+SAL0.026+ISNULL(COMM,0) AS 总收入 from emp;

练习4
1.查询员工表中一共有哪几种岗位类型
SELECT DISTINCT DEPTNO FROM emp ;

练习5
1. 分别查看员工表、部门表、薪资等级表的表结构
DESC emp;
DESC dept;
DESC salgrade;

练习6
1.查询职位为SALESMAN的员工编号、职位、入职日期。
SELECT EMPNO,JOB,HIREDATE FROM emp where JOB=”SALESMAN”;
2.查询1985年12月31日之前入职的员工姓名及入职日期。
SELECT ENAME,HIREDATE FROM emp WHERE HIREDATE<’1985-12-31’;
3.查询部门编号不在10部门的员工姓名、部门编号。
SELECT ENAME,DEPTNO FROM emp WHERE DEPTNO NOT IN (10);

练习7
1.查询入职日期在82年至85年的员工姓名，入职日期。
SELECT ENAME,HIREDATE FROM emp WHERE HIREDATE BETWEEN ‘1982-0-0’ AND ‘1985-0-0’;
2.查询月薪在3000到5000的员工姓名，月薪。
SELECT ENAME,SAL FROM emp WHERE SAL BETWEEN 3000 AND 5000;
3.查询部门编号为10或者20的员工姓名，部门编号。
SELECT ENAME,DEPTNO FROM emp WHERE DEPTNO IN (10,20);
4.查询经理编号为7902, 7566, 7788的员工姓名，经理编号。
SELECT ENAME,MGR FROM emp WHERE MGR IN (7902,7566,7788);

练习8
1.查询员工姓名以W开头的员工姓名。
SELECT ENAME FROM emp WHERE ENAME LIKE “W%”;
2.查询员工姓名倒数第2个字符为T的员工姓名。
SELECT ENAME FROM emp WHERE ENAME LIKE “%T_”;
3.查询奖金为空的员工姓名，奖金。
SELECT ENAME FROM emp WHERE COMM IS NULL;

练习9
1.查询工资超过2000并且职位是MANAGER,或者职位是SALESMAN的员工姓名、职位、工资
SELECT ENAME,JOB,SAL FROM emp WHERE ( SAL>2000 AND JOB=”MANAGER” ) OR JOB=”SALESMAN”;
2.查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资。
SELECT ENAME,JOB,SAL FROM emp WHERE sal>2000 AND (JOB=”MANAGER” OR JOB=”SALESMAN”);
3.查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。
SELECT ENAME,JOB,SAL FROM emp WHERE sal BETWEEN 3000 AND 5000 AND DEPTNO IN (10,20);
4.查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
SELECT ENAME,HIREDATE,JOB FROM emp WHERE HIREDATE LIKE “1981%” AND JOB NOT LIKE “SALES%”;
5.查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。
SELECT ENAME,JOB,DEPTNO FROM emp WHERE (JOB=”SALESMAN” OR JOB=”MANAGER”) AND DEPTNO IN (10,20) AND ENAME LIKE “%A%”;

练习10
1.查询部门在20或30的员工姓名，部门编号，并按照工资升序排序。
SELECT ENAME,DEPTNO FROM emp WHERE DEPTNO IN (20,30) ORDER BY SAL;
2.查询工资在2000-3000之间，部门不在10号的员工姓名，部门编号，工资，并按照部门升序，工资降序排序。
SELECT ENAME,DEPTNO,SAL FROM emp WHERE SAL BETWEEN 2000 AND 3000 AND DEPTNO NOT IN (10) ORDER BY DEPTNO ASC,SAL DESC;
3.查询入职日期在82年至83年之间，职位以SALES或者MAN开头的员工姓名，入职日期，职位，并按照入职日期降序排序。
SELECT ENAME,HIREDATE,JOB FROM emp WHERE HIREDATE BETWEEN ‘1982-0-0’ AND ‘1983-0-0’ AND ENAME LIKE “MAN%” OR ENAME LIKE “SALES%”
ORDER BY HIREDATE DESC;

练习11
1.查询入职日期最早的前5名员工姓名，入职日期。
SELECT ENAME,HIREDATE FROM emp ORDER BY HIREDATE LIMIT 0,5;
2.查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期。
SELECT ENAME,HIREDATE FROM emp WHERE ADDRESS=”CHICAGO” ORDER BY HIREDATE LIMIT 0,2;
3.按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称。
SELECT ENAME AS 员工姓名,HIREDATE AS “入职日期”,DEPTNO AS “部门名称” from emp LIMIT 0,5;
SELECT ENAME AS 员工姓名,HIREDATE AS “入职日期”,DEPTNO AS “部门名称” from emp LIMIT 6,5;
SELECT ENAME AS 员工姓名,HIREDATE AS “入职日期”,DEPTNO AS “部门名称” from emp LIMIT 12,5;