use teaching
--1.在STUDENTS表中插入如下两个学生的相应记录信息----
insert into
Students(studentid,sname,idcard,sex)
VALUES('201141531118','吴昕','432325199310203511','女');
insert into
Students(studentid,sname,idcard,sex)
VALUES('201141531202','江哲','430510199408026426','男');
--select *from students where sname='江哲';
--insert into 插入多条记录，原理就是将另一张表的查询结果插入到目标表
insert into Students(studentid,sname,idcard,sex)
select '201141531118','吴昕','432325199310203511','女'
union
select '201141531202','江哲','430510199408026426','男'
--2.查找全部课程总成绩totalmark在60分以下的
--学生学号、姓名、课程名称以及成绩信息，
--并将查找到的数据保存到关系表TS中；
/*这种方式不用自己建表*/
select S.studentid,S.sname,C.cname,SC.total_mark into TS
from students S,scts SC,courses C
where S.studentid=SC.studentid and SC.courseid=C.courseid and SC.total_mark<60
--3.将学号为'200742347114'的学生姓名改为'刘国华'，
--民族(nation)改为'景颇族'；
update students
set sname = '刘国华',
nation='景颇族'
where studentid='200742347114';
---select *from students where studentid='200742347114';
--4.将选修了'20812B0'号课程但成绩不及格的学生的对应成绩信息,
-- 全部改为空值（NULL）；
update SCTS
set total_mark = NULL,
Exam_Grade = NULL,
Regular_Grade=NULL,
exam_date=NULL
where SCTS.courseid='20812B0' and SCTS.total_mark<60
----测试：select *from scts where SCTS.courseid='20812B0'
--5.学生'郑宛然'在'20483B2'号课程的考试中作弊，将该生的该科成绩
--（total_mark ，Exam_Grade)置为0
update SCTS --这里不能取别名
set total_mark=0,
Exam_Grade=0
where SCTS.courseid='20483B2' and SCTS.studentid=(
select studentid
from students S
where S.sname='郑宛然'
)
---测试select *from scts where SCTS.courseid='20483B2'
/*6.将选修了'20815B0'号课程，且成绩(（total_mark)低于
该课程平均分的学生对应成绩提高20%；*/
update SCTS
set total_mark=1.2*total_mark
where SCTS.courseid='20815B0' and total_mark<(
select AVG(total_mark)
from SCTS
where SCTS.courseid='20815B0')
/*测试select total_mark
from SCTS
where SCTS.courseid='20815B0'*/
--7.在基本表SCTS中，将总成绩total_mark为空值（NULL）的选课记录全部删除；
delete from SCTS
where total_mark is null
/*测试select *
from SCTS
where total_mark is null
*/
--8.将所有姓'赵'的学生的选课记录删除；
delete
from scts
where scts.studentid in(
Select studentid
from students S
where S.sname like '赵%'
)
/*9.将'动物科学技术学院'所有学生的选课记录全部删除； */
delete
from SCTS
where SCTS.studentid in(
select S.studentid
from students S
where S.college=(
select collegeid from colleges C
where C.cname='动物科学技术学院'
)
)
/*10.学号为'200740512223'的学生因为考试舞弊被勒令退学，
能否在学生表中直接删除其记录信息？
如果一定要删除该生记录的话，应该如何操作？
写出对应的SQL语句。 */
--先删除scts中的数据
delete from scts where studentid='200740512223'
delete from students where studentid='200740512223'
/*
delete RESTRICT
from students
where studentid='200740512223' */
