mysql exercise

1. 建立基本表Student(S#,Sname,Sex,Bdate,Height,Dorm)，Course(C#,Cname,Period,Credit,Teacher)，SC(S#,C#,Grade)
2. 在定义的基本表上完成以下查询
   1). 查询选修课程“CS-02”的学生学号、成绩
   2). 查询选修课程“EE-01”的女学生姓名
   3). 查询不选修课程“CS-02”的学生姓名
   4). 查询身高高于“王涛”同学的男生学号、姓名及年龄
   5). 查询选修课程“CS-01”的学生中成绩最高的学生学号
   6). 查询学生姓名及其所选修课程的课程号、学分和成绩
   7). 查询平均成绩超过80分的学生姓名和平均成绩
   8). 查询选修三门以上课程（包括三门）的学生已获得的学分数，并按学号进行升序排列
3. 分别在student和course表中加入记录(‘01032005’,‘刘静’,‘男’,‘1983-12-10’,‘1.75’,‘西14舍312’)、(‘CS-03’,‘离散数学’,‘64’,‘4’,‘陈建明’)
4. 将student表中已修学分数大于110的记录删除
5. 将“张明”老师负责的“信号与系统”课程的课时数调整为56，同时增加一个学分
6. 在定义的基本表上建立如下视图：
   1). 所有居住在“西18“舍的男生视图，包括学号、姓名、出生日期、身高等属性。
   2). “张明“老师所开设课程情况的视图，包括课程编号、课程名称、平均成绩等属性。
   3). 所有选修了“人工智能“课程的学生视图，包括学号、姓名、成绩等属性。
7. code

create table Student(
    Sno char(8) not null,
    Sname VARCHAR(30) not null,
    Sex char(2) not null,
    Bdate char(10),
    Height DECIMAL(3,2),
    Dorm char(10)
);
CREATE INDEX snumindex ON Student(Sno);
CREATE TABLE Course(
    Cno CHAR(5) NOT NULL ,
    Cname VARCHAR(30) not NULL ,
    Period_ INT NOT NULL ,
    Credit DECIMAL(1,0),
    Teacher VARCHAR(20)
);
CREATE INDEX cnumindex ON Course(Cno);

CREATE TABLE SC(
    Sno char(8) not null,
    Cno CHAR(5) NOT NULL ,
    Grade DECIMAL(4,1),
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno) on delete CASCADE ,
    FOREIGN KEY(Cno) REFERENCES Course(Cno) on delete CASCADE ,
    CHECK ((grade is NULL ) OR (grade between 0 and 100))
);

INSERT INTO student(sno,sname,sex,Bdate,Height,Dorm) VALUES
('01032010','王涛','男','1982-4-5','1.72','西14舍221'),
('01032023','孙文','男','1983-6-10','1.80','西14舍221'),
('01032001','张晓梅','女','1982-11-17','1.58','西1舍312'),
('01032005','刘静','女','1982-1-10','1.63','西1舍312'),
('01032112','董卫锋','男','1982-2-20','1.71','西14舍221'),
('03031011','王倩','女','1983-12-20','1.66','西2舍104'),
('03031014','赵思扬','男','1981-6-6','1.85','西18舍421'),
('03031051','周剑','男','1981-5-8','1.68','西18舍422'),
('03031009','田婷婷','女','1982-8-11','1.60','西2舍104'),
('03031033','蔡明亮','男','1982-3-12','1.75','西18舍423');

INSERT INTO course(cno,Cname,Period_,Credit,Teacher)VALUES
('CS-01','数据结构','60','3','张军'),
('CS-02','计算机组成原理','80','4','张伟'),
('CS-04','人工智能','40','2','李华'),
('EE-01','信号与系统','40','2','张明'),
('EE-02','数字逻辑电路','100','5','赵正');

INSERT INTO sc(sno,Cno,Grade)VALUES
('01032010','CS-01','82.0'),
('01032010','CS-02','91.0'),
('01032010','CS-04','83.5'),
('01032001','CS-01','77.5'),
('01032001','CS-02','85.0'),
('01032001','CS-04','83.0'),
('01032005','CS-01','62.0'),
('01032005','CS-02','77.0'),
('01032005','CS-04','82.0'),
('01032023','CS-01','55.0'),
('01032023','CS-02','81.0'),
('01032023','CS-04','76.0'),
('01032112','CS-01','88.0'),
('01032112','CS-02','91.5'),
('01032112','CS-04','86.0'),
('03031033','EE-01','93.0'),
('03031033','EE-02','89.0'),
('03031009','EE-01','88.0'),
('03031009','EE-02','78.5'),
('03031011','EE-01','91.0'),
('03031011','EE-02','86.0'),
('03031051','EE-01','78.0'),
('03031051','EE-02','58.0'),
('03031014','EE-01','79.0'),
('03031014','EE-02','71.0');

# 查询选修课程“CS-02”的学生学号、成绩
SELECT sno,grade FROM sc WHERE Cno='CS-02';

# 查询选修课程“EE-01”的女学生姓名
SELECT sname FROM student,sc WHERE student.Sex='女'and sc.Cno='EE-01'and student.Sno=sc.Sno;

# 查询不选修课程“CS-02”的学生姓名
SELECT sname FROM student WHERE student.Sno not IN (SELECT Sno FROM sc WHERE cno='CS-02');

# 查询身高高于“王涛”同学的男生学号、姓名及年龄
SELECT sno,sname,2021-bdate age FROM student WHERE Height>ALL(SELECT Height FROM student WHERE Sname='王涛')AND sex='男';

# 查询选修课程“CS-01”的学生中成绩最高的学生学号
SELECT sno FROM sc WHERE cno='CS-01' AND Grade>=ALL (SELECT Grade FROM sc WHERE cno='CS-01');

# 查询学生姓名及其所选修课程的课程号、学分和成绩
SELECT Sname,course.cno,credit,grade FROM course,sc,student WHERE student.Sno=sc.Sno AND sc.Cno=course.Cno;

# 查询平均成绩超过80分的学生姓名和平均成绩
select s.sname,tmp.avg_sc
from student s
         join (select sno, avg(grade) avg_sc from sc group by sno having avg_sc > 80) tmp on s.sno = tmp.sno;

# 查询选修三门以上课程（包括三门）的学生已获得的学分数，并按学号进行升序排列
SELECT tmp.sno,sum(if(tmp.grade>=60,tmp.credit,0)) FROM (SELECT sno,sc.cno,credit,grade FROM sc,course WHERE sc.Cno=course.Cno) tmp GROUP BY tmp.sno HAVING COUNT(*)>=3  ORDER BY Sno;

# 分别在student和course表中加入记录('01032005','刘静','男','1983-12-10','1.75','西14舍312')、('CS-03','离散数学','64','4','陈建明')
INSERT INTO student(sno,sname,sex,Bdate,Height,Dorm) VALUES
('01032005','刘静','男','1983-12-10','1.75','西14舍312');
INSERT INTO course(cno,Cname,Period_,Credit,Teacher)VALUES
('CS-03','离散数学','64','4','陈建明');

# 将student表中已修学分数大于110的记录删除
DELETE FROM student WHERE sno IN (SELECT tmp.sno FROM (SELECT sno,sc.cno,credit,grade FROM sc,course WHERE sc.Cno=course.Cno) tmp GROUP BY tmp.sno HAVING SUM(if(tmp.grade>=60,tmp.credit,0))>=110);

# 将“张明”老师负责的“信号与系统”课程的课时数调整为56，同时增加一个学分
UPDATE course set credit=credit+1,Period_=56 WHERE Teacher='张明'AND Cname='信号与系统';

# 所有居住在“西18“舍的男生视图，包括学号、姓名、出生日期、身高等属性。
CREATE VIEW male_west_18 as SELECT sno,sname,bdate,height FROM student WHERE sex='男'and dorm LIKE '西18%';

# “张明“老师所开设课程情况的视图，包括课程编号、课程名称、平均成绩等属性。
CREATE VIEW teacherZM as SELECT temp.ccno,temp.cname,avg(temp.grade) FROM (select course.cno ccno,cname,grade FROM course,sc WHERE course.Teacher='张明' and course.Cno=sc.cno) temp GROUP BY temp.ccno,temp.cname;

# 所有选修了“人工智能“课程的学生视图，包括学号、姓名、成绩等属性。
CREATE VIEW ai as SELECT temp.ssno,temp.sname,temp.grade FROM (select student.sno ssno,sname,grade FROM student,sc WHERE student.Sno=sc.Sno and sc.Cno=(SELECT cno FROM course WHERE cname='人工智能'))temp;