「 数据更新 」

CREATE DATABASE EDUC;
USE EDUC;
--创建STUDENT表
CREATE TABLE STUDENT(
  sno CHAR(8) PRIMARY KEY,
  sname CHAR(8) NOT NULL,
  ssex CHAR(2)  CHECK (ssex ='男'OR ssex ='女') DEFAULT ('男'),
  sage    INT,
  sdept    CHAR(10)
);
--创建COURSE表
CREATE TABLE COURSE(
    cno    CHAR(2) PRIMARY KEY,
    cname CHAR(30),
    credit INT,    
    cpno CHAR(3)
);

--创建SC表
CREATE TABLE SC(
    sno CHAR(8),
    cno    CHAR(2),
    grade INT,
    PRIMARY KEY (sno,cno),
    FOREIGN KEY(sno)REFERENCES STUDENT(sno),
    FOREIGN KEY(cno)REFERENCES COURSE(cno),
    CHECK (grade BETWEEN 0 AND 100)
);
INSERT INTO STUDENT VALUES ('95001','李勇','男',20,'CS'),
                           ('95002','刘晨','女',19,'IS '),
                           ('95003','王敏','女',18,'MA'),
                           ('95004','张立','男',19,'IS '),
                           ('95005','刘云云','女',18,'CS');
INSERT INTO COURSE VALUES ('1','数据库',4,'5'),
                          ('2','数学',6,NULL),
                          ('3','信息系统',3,'1'),
                          ('4','操作系统',4,'6'),
                          ('5','数据结构',4,'7'),
                          ('6','数据处理',3,NULL),
                          ('7','PASCAL语言',4,'6');                          
INSERT INTO SC VALUES ('95001','1',0),
                      ('95001','2',69),
                      ('95001','3',88),
                      ('95002','2',10),
                      ('95002','3',80),
                      ('95003','2',15),
                      ('95004','1',58),
                      ('95004','2',85),
                      ('95004','3',NULL);
                      ('95030','1',NULL)        
--1.插入学生记录（学号：95030，姓名：李莉，年龄：18）
--因sno为95030的学生记录在STUDENT中已存在，sno为主键，不能出现重复且STUDENT表中学号sno为SC表中的外键,，故现将之前插入的sno为95030的学生记录删除，插入题目要求的记录
DELETE FROM  SC WHERE sno = '95030';
DELETE FROM  STUDENT WHERE sno = '95030';
INSERT INTO STUDENT (sno,sname,sage) VALUES('95030','李莉',18);
--2.插入选课记录（95030,1）
INSERT INTO SC (sno,cno) VALUES ('95030','1');
--3.将CS学院的学生年龄改成17
UPDATE  STUDENT SET sage = 17 WHERE sdept = 'CS';
--4.将MA学院所有学生成绩改成0
UPDATE  SC SET grade = 0 WHERE sno IN
    (SELECT sno FROM STUDENT WHERE sdept = 'MA');
--5.把低于总平均成绩的女同学成绩提高5分
UPDATE SC SET grade = grade + 5
    WHERE sno IN (SELECT sno FROM STUDENT WHERE ssex='女')
    AND grade<(SELECT * FROM (SELECT Avg(grade) AS grade FROM SC) AS a);
--6.修改2号课程的成绩，若成绩小于75分则提高5%
UPDATE SC SET grade =grade*(1+0.05) WHERE grade < 75 AND cno = '2';
--7.删除95030学生信息
--sno是SC表中的外键，如需在student表中删除则需要先在sc表中删除
DELETE FROM SC WHERE sno = '95030';
DELETE FROM STUDENT WHERE sno = '95030';
--8.删除SC表中无成绩的记录
DELETE FROM SC WHERE grade IS NULL;
--9.删除王敏的选课记录
DELETE FROM SC WHERE sno IN(SELECT sno FROM STUDENT WHERE sname = '王敏');
--10.删除不及格的学生选课记录
DELETE FROM SC WHERE grade < 60;
--11.删除IS系所有学生选课记录
DELETE FROM SC WHERE sno IN(SELECT sno FROM STUDENT WHERE sdept = 'IS');
--12.删除所有未被选修的课程
DELETE FROM COURSE WHERE cno NOT IN(SELECT cno FROM SC);
--13.查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个以及存在的基本表STU(SNO,SNAME,SSEX)中
CREATE TABLE STU(    
    sno CHAR(8), 
    sname CHAR(8), 
    ssex CHAR(2) 
);
INSERT INTO STU(sno,sname,ssex)
    SELECT STUDENT.sno,sname,ssex FROM STUDENT,SC 
    WHERE STUDENT.sno NOT IN
        (SELECT sno FROM sc WHERE grade<80) AND STUDENT.sno=SC.sno;

--14.建立一个sdeptgrade表，包含（sdept,avggrade）字段，对每一个系，求学生的成绩，并把结果存入sdeptgrade
CREATE TABLE sdeptgrade(
    sdept CHAR(10),
    avggrade INT
);
INSERT INTO sdeptgrade(sdept,avggrade)
    SELECT STUDENT.sdept, AVG(SC.grade) FROM STUDENT,SC 
        WHERE STUDENT.sno = SC.sno GROUP BY STUDENT.sdept;