createtable Student(
Sno char(8)notnull,
Sname VARCHAR(30)notnull,
Sex char(2)notnull,
Bdate char(10),
Height DECIMAL(3,2),
Dorm char(10));CREATEINDEX snumindex ON Student(Sno);CREATETABLE Course(
Cno CHAR(5)NOTNULL,
Cname VARCHAR(30)notNULL,
Period_ INTNOTNULL,
Credit DECIMAL(1,0),
Teacher VARCHAR(20));CREATEINDEX cnumindex ON Course(Cno);CREATETABLE SC(
Sno char(8)notnull,
Cno CHAR(5)NOTNULL,
Grade DECIMAL(4,1),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCES Student(Sno)ondeleteCASCADE,FOREIGNKEY(Cno)REFERENCES Course(Cno)ondeleteCASCADE,CHECK((grade isNULL)OR(grade between0and100)));INSERTINTO 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');INSERTINTO 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','赵正');INSERTINTO 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 notIN(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 groupby 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 GROUPBY tmp.sno HAVINGCOUNT(*)>=3ORDERBY Sno;# 分别在student和course表中加入记录('01032005','刘静','男','1983-12-10','1.75','西14舍312')、('CS-03','离散数学','64','4','陈建明')INSERTINTO student(sno,sname,sex,Bdate,Height,Dorm)VALUES('01032005','刘静','男','1983-12-10','1.75','西14舍312');INSERTINTO course(cno,Cname,Period_,Credit,Teacher)VALUES('CS-03','离散数学','64','4','陈建明');# 将student表中已修学分数大于110的记录删除DELETEFROM student WHERE sno IN(SELECT tmp.sno FROM(SELECT sno,sc.cno,credit,grade FROM sc,course WHERE sc.Cno=course.Cno) tmp GROUPBY tmp.sno HAVINGSUM(if(tmp.grade>=60,tmp.credit,0))>=110);# 将“张明”老师负责的“信号与系统”课程的课时数调整为56,同时增加一个学分UPDATE course set credit=credit+1,Period_=56WHERE Teacher='张明'AND Cname='信号与系统';# 所有居住在“西18“舍的男生视图,包括学号、姓名、出生日期、身高等属性。CREATEVIEW male_west_18 asSELECT sno,sname,bdate,height FROM student WHERE sex='男'and dorm LIKE'西18%';# “张明“老师所开设课程情况的视图,包括课程编号、课程名称、平均成绩等属性。CREATEVIEW teacherZM asSELECTtemp.ccno,temp.cname,avg(temp.grade)FROM(select course.cno ccno,cname,grade FROM course,sc WHERE course.Teacher='张明'and course.Cno=sc.cno)tempGROUPBYtemp.ccno,temp.cname;# 所有选修了“人工智能“课程的学生视图,包括学号、姓名、成绩等属性。CREATEVIEW ai asSELECTtemp.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;