前端学习之数据管理作业

一、emp数据库

1、插入数据

插入数据

1.向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。

2.向部门表新增一个部门，部门编号为60，部门名称为MARKET。

3.向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空。

4.使用CREATE TABLE emp_back as 

        SELECT * FROM EMP WHERE 1=0，创建emp_back表,拷贝下来即可。

5.把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中。

一、
1、
delete from dept where DEPTNO=50;
INSERT into dept
VALUES(50,'HR','SY');
2、
delete from dept where DEPTNO=60;
INSERT into dept
VALUES(60,'MARKET',null);
3、
INSERT into emp
VALUES(8888,'BOB','CLERK',7788,'1985-03-03',3000,NULL,null);
4、
CREATE TABLE emp_back SELECT *FROM emp WHERE 1=0;
5、
INSERT emp_back SELECT *FROM emp where hiredate>'1982-01-01'

2、更新数据

1.修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天

2.修改奖金为null的员工，奖金设置为0

3.修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500

1、
update emp set hiredate=hiredate+10 
WHERE hiredate>'1982-12-31'

2、
update emp 
set COMM=0 
WHERE ISNULL(COMM);

3、
UPDATE emp
set SAL = SAL+500
where DEPTNO in (
	SELECT DEPTNO
	from dept
	where LOC='NEW YORK' or LOC='CHICAGO'
);

3、删除

1.删除经理编号为7566的员工记录

2.删除工作在NEW YORK的员工记录

3.删除工资大于所在部门平均工资的员工记录

1、delete
from emp
where mgr=7566;
2、delete 
from emp 
where DEPTNO in (
	SELECT DEPTNO
	from dept
	where LOC='NEW YORK' 
);

二、综合 使用如下语句，建立以下表

1、

CREATE TABLE copy _emp   (

  empno int(4),

  ename varchar(20),

  hiredate date,

  deptno int(2),

  sal double(8,2))

(1)在表copy_emp中插入数据，要求sal字段插入空值，部门号50，参加工作时间为2000年1月1日，其他字段随意

(2)在表copy_emp中插入数据，要求把emp表中部门号为10号部门的员工信息插入

(3)修改copy_emp表中数据，要求10号部门所有员工涨20%的工资

(4)修改copy_emp表中sal为空的记录，工资修改为平均工资

(5)把工资为平均工资的员工，工资修改为空

1、
INSERT INTO copy_emp VALUES(7909,'jack','2000-01-01',50,NULL);
2、
INSERT INTO copy_emp(empno,ename,hiredate,deptno,sal)
	SELECT empno,ename,hiredate,DEPTNO,SAL 
	from emp
	where DEPTNO=10;
3、
UPDATE copy_emp
set sal=sal*1.2
where deptno=10;

4、
update copy_emp 
set sal = (SELECT a.avgsal from (select avg(sal) avgsal from copy_emp) as a)
where ISNULL(sal);
5、
update copy_emp 
set sal=NULL
WHERE sal = (SELECT a.avgsal from (select avg(sal) avgsal from copy_emp) as a);

2、

新建学生数据库，添加适量测试数据

---

年级表  grade

年级编号  gradeId 主键自增

年级名称  gname 

---

学生表  student

学号  stuNo 主键自增

密码  loginPwd 

姓名  stuName  

性别  sex

年级编号  gradeId

手机号码  phone

家庭住址  address

出生日期  bornDate

邮箱  email

身份证号码 identityCard

---

科目表  subject

科目编号 subNo 主键自增

科目名称  subName

课时数  classHour 

所属年级 gradeId

成绩表  result

学号  stuNo

科目编号 subNo

成绩 score 

考试时间 ExamDate

建表语句

添加测试数据语句

---

（1）添加三个班级信息为：1，JAVA1班，null

                         2，JAVA2班，null

                         3，JAVA3班，null

（2）添加学生信息如下：‘A001’,‘张三’,‘男’,‘01-5月-05’,100,1

（3）添加学生信息如下：'A002','MIKE','男','1905-05-06',10

（4）插入部分学生信息： 'A003','JOHN','女’

（5）将A001学生性别修改为'女‘

（6）将A001学生信息修改如下：性别为男，生日设置为1980-04-01

（7）将生日为空的学生班级修改为Java3班

（8）请使用一条SQL语句，使用子查询，更新班级表中每个班级的人数字段

1、
INSERT
INTO grade
VALUES(1,'JAVA1班',null),
			(2,'JAVA2班',null),
			(3,'JAVA3班',null);
2、
INSERT
INTO stu(stuNo,stuName,sex,bornDate,gradeID)
VALUES('A001','张三','男','2001-05-05',1);

3、
INSERT
INTO stu(stuNo,stuName,sex,bornDate,gradeID)
VALUES('A002','MIKE','男','1905-05-06',2);

4、
INSERT
INTO stu(stuNo,stuName,sex)
VALUES('A003','JOHN','女');

5、
UPDATE stu
set sex = '女' 
where stuNo='A001';

6、
UPDATE stu
set sex = '男' ,bornDate = '1980-04-01'
where stuNo='A001';

7、
UPDATE stu,grade
set stu.gradeID = (SELECT c.gradeid from (SELECT gradeID gradeid from grade where gname='JAVA3班') as c)
where ISNULL(bornDate);


8、
update grade
set gnum=(
		select count(stu.gradeID)
		from stu 
		where stu.gradeID=grade.gradeID
);

三、简单查询

1.使用两种方式查询所有员工(EMP)信息
2.查询(EMP)员工编号、员工姓名、员工职位、员工月薪、工作部门编号。
3.员工转正后，月薪上调20%，请查询出所有员工转正后的月薪。
4.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的年薪所得（不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)
5.员工试用期6个月，转正后月薪上调20%，请查询出所有员工工作第一年的所有收入（需考虑奖金部分)，要求显示列标题为员工姓名，工资收入，奖金收入，总收入。
6.查询员工表中一共有哪几种岗位类型。
7. 分别查看员工表、部门表、薪资等级表的表结构。
8.查询职位为SALESMAN的员工编号、职位、入职日期。
9.查询1985年12月31日之前入职的员工姓名及入职日期。
10.查询部门编号不在10部门的员工姓名、部门编号。
11.查询入职日期在82年至85年的员工姓名，入职日期。
12.查询月薪在3000到5000的员工姓名，月薪。
13.查询部门编号为10或者20的员工姓名，部门编号。
14.查询经理编号为7902, 7566, 7788的员工姓名，经理编号。
15.查询员工姓名以W开头的员工姓名。
16.查询员工姓名倒数第2个字符为T的员工姓名。
17.查询奖金为空的员工姓名，奖金。
18.查询工资超过2000并且职位是MANAGER,或者职位是SALESMAN的员工姓名、职位、工资
19.查询工资超过2000并且职位是 MANAGER或SALESMAN的员工姓名、职位、工资。
20.查询部门在10或者20，并且工资在3000到5000之间的员工姓名、部门、工资。
21.查询入职日期在81年，并且职位不是SALES开头的员工姓名、入职日期、职位。
22.查询职位为SALESMAN或MANAGER，部门编号为10或者20，姓名包含A的员工姓名、职位、部门编号。
23.查询入职时间在1982-7-9之后，并且不从事SALESMAN工作的员工姓名、入职时间、职位。
24.查询员工姓名的第三个字母是a的员工姓名。
25.查询除了10、20号部门以外的员工姓名、部门编号。
26.查询部门号为30号员工的信息，先按工资降序排序，再按姓名升序排序。 
27.查询没有上级的员工(经理号为空)的员工姓名。
28.查询工资大于等于4500并且部门为10或者20的员工的姓名\工资、部门编号。

1、
SELECT*
from emp;

SELECT 	empno,ename,job,mgr,hiredate,SAL,COMM,DEPTNO
from emp;
2、
SELECT empno,ename,job,SAL,DEPTNO 
from emp;
3、
SELECT ename,SAL*1.2 SAL 
from emp;
4、
SELECT ename,SAL*1.2*6+SAL*6 SALyear 
from emp;
5、
SELECT ename,SAL*1.2*6+SAL*6 SALyear,COMM,SAL*1.2*6+SAL*6+COMM sumSALyear 
from emp;
6、
SELECT DISTINCT job
from emp;
7、
DESC emp;
DESC dept;
DESC salgrade;
8、
SELECT empno,job,hiredate
from emp
WHERE job = 'SALASMAN';
9、
SELECT ename,hiredate
from emp
WHERE hiredate>'1985-12-31';
10、
SELECT ename,DEPTNO
from emp
WHERE DEPTNO!=10;
11、
SELECT ename,hiredate
from emp
WHERE hiredate BETWEEN '1982-01-01' and '1985-12-31';
12、
SELECT ename,SAL 
from emp 
where SAL BETWEEN 3000 AND 5000;
13、
SELECT ename,DEPTNO
from emp
WHERE DEPTNO=10 or DEPTNO=20;
14、
SELECT ename,mgr
from emp
WHERE mgr in (7902, 7566, 7788);
15、
SELECT ename
from emp
WHERE ename LIKE 'W%';
16、
SELECT ename
from emp
WHERE ename LIKE '%T_';
17、
SELECT ename,COMM
from emp
WHERE ISNULL(COMM);
18、
SELECT ename,job,SAL 
from emp 
where SAL>2000 and job in ('MANAGER','SALESMAN');
19、
SELECT ename,job,SAL 
from emp 
where SAL>2000 and job in ('MANAGER','SALESMAN');
20、
SELECT ename,DEPTNO,SAL 
from emp 
where DEPTNO in (10,20);
21、
SELECT ename,hiredate,job 
from emp 
where job!='SALES%' and (hiredate BETWEEN'1981-01-01' and'1981-12-31');
22、
SELECT ename,job,DEPTNO 
from emp 
where job in ('MANAGER','SALESMAN')and DEPTNO in (10,20) and ename LIKE '%A%';

23、
SELECT ename,hiredate,job 
from emp 
where job!='SALESMAN' and hiredate>'1982-07-09';
24、
SELECT ename
from emp 
where ename LIKE '__a%';
25、
SELECT ename,DEPTNO
from emp
WHERE DEPTNO=!10 or DEPTNO!=20;
26、
SELECT*
from emp
WHERE DEPTNO=30
ORDER BY SAL DESC,ename ASC;
27、
SELECT ename
from emp 
where ISNULL(mgr);
28、
SELECT ename,SAL,DEPTNO
from emp 
where SAL>=4500 and DEPTNO in (10,20);