数据更新-------实验七

在这里插入图片描述[在这里插入图片描述
四、实验内容

create database EDUC
create table student(sno char(8) primary key,sname char(8),ssex char(2) constraint ck_student_ssex check (ssex='男' or ssex='女'),sage int,sdept char(10))
create table course(cno char(2)primary key,cname char(30),credit int,cpno char(3))
create table sc(sno char(8),cno char(2),grade int constraint ck_sc_grade check (grade between 0 and 100),primary key (sno,cno),foreign key (sno) references student(sno),foreign key (cno) references course(cno),)
insert into student(sno,sname,ssex,sage,sdept)
values('95001','李勇','男',20,'CS')
insert into student(sno,sname,ssex,sage,sdept)
values('95002','刘晨','女',19,'IS')
insert into student(sno,sname,ssex,sage,sdept)
values('95003','王敏','女',18,'MA')
insert into student(sno,sname,ssex,sage,sdept)
values('95004','张立','男',19,'IS')
insert into student(sno,sname,ssex,sage,sdept)
values('95005','刘云云','女',18,'CS')

insert into course(cno,cname,credit,cpno)
values('1','数据库',4,'5')
insert into course(cno,cname,credit,cpno)
values('2','数学',6,NULL)
insert into course(cno,cname,credit,cpno)
values('3','信息系统',3,'1')
insert into course(cno,cname,credit,cpno)
values('4','操作系统',4,'6')
insert into course(cno,cname,credit,cpno)
values('5','数据结构',4,'7')
insert into course(cno,cname,credit,cpno)
values('6','数据处理',3,NULL)
insert into course(cno,cname,credit,cpno)
values('7','PASCAL语言',4,'6')

insert into sc(sno,cno,grade)
values('95001','1',0)
insert into sc(sno,cno,grade)
values('95001','2',69)
insert into sc(sno,cno,grade)
values('95001','3',88)
insert into sc(sno,cno,grade)
values('95002','2',10)
insert into sc(sno,cno,grade)
values('95002','3',80)
insert into sc(sno,cno,grade)
values('95003','2',15)
insert into sc(sno,cno,grade)
values('95004','1',58)
insert into sc(sno,cno,grade)
values('95004','2',85)
insert into sc(sno,cno,grade)
values('95004','3',NULL)
insert into sc(sno,cno,grade)
values('95030','1',NULL)
--1.插入学生记录（学号：95030，姓名：李莉，年龄：18）
--1
insert into student(sno,sname,sage)
values('95030','李莉',18)
--2
insert into student
values('95030','李莉',NULL,18,NULL)

select * from student
--2.插入选课记录（95030,1）
--1
insert into sc(sno,cno)
values('95030','1')
--2
insert into sc
values('95030','1',NULL)

select * from sc

--3.将CS学院的学生年龄改成17
update student
set sage=17
where sdept='CS'

select * from student where sdept='CS'
--4.将MA学院所有学生成绩改成0
update sc
set grade=0
where sno in(select sno from student where sdept='MA')

select student.sno,sname,sdept,grade from student,sc where student.sno=sc.sno and sdept='MA'
--5.把低于总平均成绩的女同学成绩提高5分
update sc
set grade=grade+5 
where grade<(select avg(grade) from sc) and (select ssex from student where student.sno=sc.sno)='女'
select sname,ssex,grade from sc,student where student.sno=sc.sno and ssex='女' and grade<(select avg(grade) from sc )
--6.修改2号课程的成绩，若成绩小于75分则提高5%
select sno,cno,grade from sc where cno='2'and grade<75

update sc
set grade=grade*1.05
where cno='2' and grade<75

select sno,cno,grade from sc where cno='2'and grade<75
--7.删除95030学生信息
select * from student where sno='95030'
delete from sc where sno='95030'
delete from student where sno='95030'
select * from student 
--8.删除SC表中无成绩的记录
select * from sc where grade is null

delete from sc where grade is null

select * from sc
--9.删除王敏的选课记录
select sname,cno,grade from student,sc where student.sno=sc.sno and sname='王敏'
delete from sc where sno in(select sno from student where sname='王敏')
select * from sc where sno in(select sno from student where sname='王敏')
--10.删除不及格的学生选课记录
select * from sc where grade<60
delete from sc where grade<60
--11.删除IS系所有学生选课记录
select student.sno,cno,sdept,grade from student,sc where student.sno=sc.sno and sdept='IS'
delete from sc where sno in (select sno from student where sdept='IS')
--12.删除所有未被选修的课程
select cno from course where cno not in (select distinct cno from sc)
delete from course where cno not in(select distinct cno from sc )
--13.查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个以及存在的基本表STU(SNO,SNAME,SSEX)中
create table stu(sno char(3),sname char(10),ssex char(3))

insert into stu(sno,sname,ssex)
select sno,sname,ssex from student where sno in(select sno from sc group by sno having min(grade)>=80)

select sno,sname,ssex from student where sno in(select sno from sc group by sno having min(grade)>=80)
select * from stu
--14.建立一个sdeptgrade表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade
create table sdeptgrade(sdept char(8),avggrade int)
insert into sdeptgrade(sdept,avggrade)
select sdept,avg(grade) from student,sc where student.sno=sc.sno group by sdept
select * from sdeptgrade