1. 数据插入

依次将准备好的数据用Insert Into语句将数据插入相应的基本表：

正确数据的插入，这些数据应该不会违反完整性约束
注意基本表的先后插入顺序，体会参照完整性。特别要注意课程表的数据插入与其他基本表的不同。
对每一个基本表，插入错误的数据，这些数据应该是事先准备好，且违反了基本表的某一个约束条件的。

1. 插入系表

-- 插入系表
insert into department values(1,'计算机与信息系','周波',18856245890,'安徽省宣城市');
insert into department values(2,'法律系','王立',18856247532,'安徽省合肥市');
insert into department values(3,'英语系','周扬',18856246429,'安徽省芜湖市');
insert into department values(4,'机器系','宋志诚',18856241353,'安徽省宣城市');

2. 插入专业表

-- 插入专业表
insert into major values(21,'物联网工程',1);
insert into major values(22,'计算机科学',1);
insert into major values(23,'人工智能',1);
insert into major values(31,'国家政法',2);
insert into major values(32,'法律事务',2);
insert into major values(41,'世界英语',3);
insert into major values(51,'机器设计制造',4);
insert into major values(52,'自动化',4);

3. 插入学生表：日期类型注意加单引号

-- 插入学生表
insert into student values(2018217876,'钟嗣儒','男','2000-02-04',21);
insert into student values(2018217879,'何飞','男','2000-02-04',21);
insert into student values(2018217912,'王珊','女','2000-06-23',41);
insert into student values(2018217923,'李子木','女','2001-06-23',41);
insert into student values(2018217863,'宋承羽','男','2000-01-14',22);
insert into student values(2018217845,'张波','男','2001-10-14',22);
insert into student values(2018217989,'王骁','男','2000-06-29',23);
insert into student values(2018217765,'张云轩','男','2000-09-29',23);
insert into student values(2018217821,'袁姗','女','2000-11-04',32);
insert into student values(2018217823,'章怡','女','2000-9-04',32);
insert into student values(2018217978,'王琼','女','2000-5-23',31);
insert into student values(2018217965,'宋宇','男','2000-5-11',31);
insert into student values(2018217654,'高原','男','1999-09-14',51);
insert into student values(2018217647,'王志鹏','男','1999-11-04',51);
insert into student values(2018217552,'庞博','男','1998-06-27',52);
insert into student values(2018217593,'宋诚','男','1998-10-27',52);

4. 插入课程表：注意顺序问题，先修课的课程要先创建！

-- 插入课程表
insert into course values(94,'计算机基础',5,null);
insert into course values(100,'JAVA技术',4,94);
insert into course values(90,'高等数学',6,null);
insert into course values(95,'概率论与数理统计',5,90);
insert into course values(101,'机器学习',3,95);
insert into course values(80,'英语',3,null);
insert into course values(81,'国际英语交流',5,80);
insert into course values(97,'汇编语言',3,94);
insert into course values(96,'微机原理与接口技术',4,97);
insert into course values(112,'政法基础',3,null);
insert into course values(111,'法律素养',3,112);
insert into course values(73,'自动化入门',6,null);
insert into course values(71,'机器制造',6,73);

5. 插入学生选课表

-- 插入学生选课表
insert into select_course values(2018217876,100,090.5)
insert into select_course values(2018217876,96,079.0)
insert into select_course values(2018217876,97,081.0)
insert into select_course values(2018217879,94,091.5);
insert into select_course values(2018217879,101,095.0);
insert into select_course values(2018217912,80,099.0);
insert into select_course values(2018217912,81,089.5);
insert into select_course values(2018217923,80,096.5);
insert into select_course values(2018217923,81,094.5);
insert into select_course values(2018217863,96,091.5);
insert into select_course values(2018217863,73,088.0);
insert into select_course values(2018217845,100,091.0);
insert into select_course values(2018217845,96,088.0);
insert into select_course values(2018217989,101,076.0);
insert into select_course values(2018217989,95,081.0);
insert into select_course values(2018217765,71,097.0);
insert into select_course values(2018217765,73,096.0);
insert into select_course values(2018217821,111,081.0);
insert into select_course values(2018217821,112,087.0);
insert into select_course values(2018217823,111,091.0);
insert into select_course values(2018217823,112,098.0);
insert into select_course values(2018217978,111,071.5);
insert into select_course values(2018217978,112,087.5);
insert into select_course values(2018217965,111,067.5);
insert into select_course values(2018217965,112,091.0);
insert into select_course values(2018217654,71,086.5);
insert into select_course values(2018217647,71,084.5);
insert into select_course values(2018217552,73,067.5);
insert into select_course values(2018217593,73,077.5);

错误数据的插入：

insert into department values(5,null,'王青',188562459080,'安徽省宣城市');

2. 数据修改

用Update 语句完成下列任务：

修改某位学生的专业号，要求包括：修改语句成功执行和修改操作被拒绝两种情形。
修改某门课学生的成绩，学生、课程及成绩由学生自行确定。
将选课表中50~59之间的成绩整体增加10分。
将“Java语句”课程的先修课改为“c209”。（可根据表中数据调整条件数据）

1. 修改某位学生的专业号，要求包括：修改语句成功执行和修改操作被拒绝两种情形。

-- 更改2018217876学生的专业号为22
update student set tc_mj = 22 where st_id = '2018217876';

-- 更改2018217876学生的专业号为55(不存在的专业号)
update student set tc_mj = 55 where st_id = '2018217876';

2. 修改某门课学生的成绩，学生、课程及成绩由学生自行确定。

-- 更改2018217876号学生的96号课成绩为91分
update select_course set sc_grade = 091.0 where sc_id = 2018217876 and sc_num = 96

3. 将选课表中70~79之间的成绩整体增加10分

-- 将选课表中70~79之间的成绩整体增加10分
update select_course set sc_grade = sc_grade + 10 where sc_grade >= 70 and sc_grade <= 79

4. 将“JAVA技术”课程的先修课改为“90”

-- 将“JAVA技术”课程的先修课改为“90”
update course set cs_prerequisite = 90 where cs_name = 'JAVA技术'

3. 数据删除

用Delete from语句完成下列任务：

先在学生表中插入一个专业号为空值的元组。
删除学生表中所有未设定专业号的学生。

-- 先在学生表中插入一个专业号为空值的元组
insert into student values(2018217971,'zsr','男','2000-09-07',null);-- 删除学生表中所有未设定专业号的学生
delete from student where tc_mj is null