create database if not exists jxdb05 character set=UTF8MB4;
show create database jxdb05;
create table if not exists Department05(
Dno char(2) primary key comment '学院编号',
Dname varchar(15) not null comment '学院名称'
);
show create table Department05;
create table if not exists Major05(
Mno char(4) primary key comment '专业编号' ,
Mname varchar(15) not null comment '专业名称',
Dno char(2) comment '学院编号'
);
show create table Major05;
create table if not exists Students05(
Sno char(10) primary key comment '学号' ,
Sname varchar(4) not null comment '姓名',
Sgender enum('男','女') default '男' comment '性别',
Sage int comment '年龄',
Dno char(2) comment '学院编号',
Mno char(4)comment '专业编号',
Sclass char(8) comment '班级编号'
);
show create table Students05;
create table if not exists Teachers05(
Tno char(4) primary key comment '教师编号',
Tname varchar(4) not null comment '教师姓名',
Tgender enum('男','女') comment '性别' ,
Tbirth date comment '出生日期' ,
Dno char(2) comment '学院编号'
);
show create table Teachers05;
create table if not exists Courses05(
Cno char(8) primary key comment '课程编号',
Cname varchar(20) not null comment '课程名称',
Chours tinyint default 32 comment '课时',
Ccredit tinyint default 2 comment '学分'
);
show create table Courses05;
create table if not exists Reports05(
Sno char(10) comment '学号',
Cno char(8) comment '课程号',
primary key (Sno,Cno),
Racademicyear year not null comment '选课学年',
Rterm char(1) not null comment '选课学期',
Grade tinyint comment '成绩'
);
show create table Reports05;
create table if not exists Tutors05(
Tno char(4) not null comment '教师编号',
Cno char(8) not null comment '课程号',
Tacademicyear year not null comment '授课学年',
Tterm char(1) not null comment '授课学期',
Sclass char(8) not null comment '班级编号'
);
show create table Tutors05;
alter table Department05
add column Dheader char(4);
describe Department05;
alter table Students05
change Sage Sbirth date;
alter table Students05
add column Snative varchar(20) after Sbirth;
describe Students05;
alter table Courses05
add column Pre_Cno char(8) after Cname;
describe Courses05;
alter table Teachers05
add column Tprof varchar(8) after Tgender;
alter table Teachers05
add column Tsal decimal(10,2);
alter table Teachers05
add Tcomm decimal(10,2);
describe Teachers05;
create index IDX_Stu_Mno on Students05(Mno DESC);
show index from Students05;
create unique index IDX_Cou_Cname on Courses05(Cname ASC);
show index from Courses05;
create index IDX_Cource_YearTerm on Reports05(Racademicyear DESC,Rterm ASC);
show index from Reports05;
alter table Teachers05
add index IDX_Teachers_TitleDOB(Tprof ASC,Tbirth DESC);
show index from Teachers05;
alter table Tutors05
add index IDX_Tutors_Tno(Tno ASC);
alter table Tutors05
add index IDX_Tutors_Sclass(Sclass DESC);
show index from Tutors05;
alter table Teachers05
drop index IDX_Teachers_TitleDOB;
show index from Teachers05;
alter table Department05
add constraint FK_Department05_Dheader
foreign key(Dheader) references Teachers05(Tno);
alter table Major05
add constraint FK_Major0_Dno
foreign key(Dno) references Department05(Dno);
alter table Students05
add constraint FK_Students05_Dno
foreign key (Dno) references Department05(Dno);
alter table Reports05
add constraint FK_Reports05_Sno
foreign key (Sno) references Students05(Sno);
SET SESSION sql_mode=NO_ZERO_IN_DATE;
INSERT INTO students05
VALUES (2016130101,'张三','男','1998-04-01','广东潮州',13,1301,20161301);
#no.3 请写出下列查询操作的SQL语句：“查询课程表Courses中，所有课程的详细记录”
SELECT *
FROM Courses05;
#no.4 请写出下列查询操作的SQL语句：“查询表中前6位学生的学号、姓名、出生日期以及入学年份，查询结果用中文显示列名”
SELECT Sno As '学号',Sname As '姓名',Sbirth As '出生日期', CONVERT(LEFT(Sno,4),UNSIGNED) As '入学年份'
FROM students05
LIMIT 6;
#no.5 请写出下列查询操作的SQL语句：“查询授课表Tutor中，教师编号为T009的教师都给哪些班级讲授过课程”
SELECT DISTINCT Sclass
FROM Tutors05
WHERE Tno='T009';
#no.6 请写出下列查询操作的SQL语句：“查询考试成绩有不及格的学生的学号”
SELECT DISTINCT Sno
FROM Reports05
WHERE Grade is null OR Grade<'60';
#no.7 请写出下列查询操作的SQL语句：“查询教师T003在2015学年第1学期的授课情况”
SELECT *
FROM Tutors05
WHERE Tno='T003' AND Tacademicyear='2015' AND Tterm='1';
#no.8 请写出下列查询操作的SQL语句：“查询前序课程编号是'112p0015'或'112p0055'的课程编号和课程名”
SELECT Cno,Cname
FROM Courses05
WHERE Pre_Cno='112p0015' OR Pre_Cno='112p0055';
#no.9 请写出下列查询操作的SQL语句：“查询工资(Tsal)在3000元以上的教师的姓名、职称和工资，查询结果按照工资升序排序”
SELECT Tname,Tprof,Tsal
FROM Teachers05
WHERE Tsal>3000
ORDER BY Tsal ASC;
#no.10 请写出下列查询操作的SQL语句：“查询院号为11或12的学生的学号、姓名、院号和专业号，结果按院号升序、专业号降序、姓名的汉语拼音升序排序”
SELECT Sno,Sname,Dno,Mno
FROM Students05
WHERE Dno='11' OR Dno='12'
# GROUP BY Dno,Mno,Sname
ORDER BY Dno ASC,Mno DESC,CONVERT(Sname USING gbk) COLLATE gbk_chinese_ci ASC;
#no.11 请写出下列查询操作的SQL语句：“查询课程名中包含了'计算机'或'原理'的课程的所有信息”
SELECT *
FROM Courses05
WHERE Cname LIKE '%计算机%' OR Cname LIKE '%原理%';
#no.12 请写出下列查询操作的SQL语句：“查询名字中第二个字为“一”的男学生的学号、姓名和出生年份，查询结果按学号降序排序”
SELECT Sno,Sname,YEAR(Sbirth)
FROM students05
WHERE Sname LIKE '_一%' AND Sgender='男'
ORDER BY Sno DESC;
#no.13 请写出下列查询操作的SQL语句：“查询生源地既不在“广东潮州”,也不在“山东”的学生的学号、姓名及生源地”
SELECT Sno,Sname,Snative
FROM students05
WHERE Snative NOT LIKE '山东%' AND Snative NOT LIKE '广东潮州';
#no.14 请写出下列查询操作的SQL语句：“查询学号为'2014112103'的学生在2015学年选修的各门课程的课程编号及成绩，查询结果按成绩降序排序”
SELECT Cno,Grade,Racademicyear
FROM Reports05
WHERE Sno='2014112103' AND Racademicyear='2015'
ORDER BY Grade DESC;
#no.15 请写出下列查询操作的SQL语句：“查询2014级的学生在读大二时，都有哪些教师给他们上过课，结果显示班级编号、教师编号、课程号、学年和学期，并用中文显示属性列名”
SELECT Sclass As '班级编号',Tno As '教师编号',Cno As '课程号',Tacademicyear As '学年',Tterm As '学期'
FROM Tutors05
WHERE Tacademicyear='2015';
#no.16 请写出下列查询操作的SQL语句：“查询各门课程(Cno)的选课情况，结果显示课程编号、选课人数、平均分、最高分和最低分，查询结果按课程编号降序排序，并用中文显示属性列名，平均分保留小数点后1位”
SELECT Cno As '课程编号',COUNT(Cno) As '选课人数',FORMAT(AVG(Grade),1) As '平均分',MAX(Grade) As '最高分',MIN(Grade) As '最低分'
FROM Reports05
GROUP BY Cno
ORDER BY Cno DESC;
#no.17 请写出下列查询操作的SQL语句：“查询至少有5个'11'学院的学生选修的课程的课程编号、选修的人数和平均成绩，选修人数和平均成绩只针对'11'学院的学生进行统计，并用中文显示属性列名，平均成绩保留小数点后1位”
SELECT Cno As '课程编号',COUNT(Cno) As '选修人数',FORMAT(AVG(Grade),1) As '平均成绩'
FROM reports05
WHERE SUBSTRING(Sno,5,2)='11'
GROUP BY Cno
HAVING COUNT(Cno)>=5;
#no.18 请写出下列查询操作的SQL语句：“查询20141121班中各位学生(Sno)每个学年所修课程的平均分，结果显示学生的学号、学 年、课程平均分，结果按学号、学年升序排序，并用中文显示属性列名，平均分保留小数点后1位”
SELECT Sno As '学号',Racademicyear As '学年',FORMAT(AVG(Grade),1) As '课程平均分'
FROM Reports05
WHERE LEFT(Sno,8)='20141121'
GROUP BY Sno, Racademicyear
ORDER BY Sno ASC,Racademicyear ASC;
#no.19 请写出下列查询操作的SQL语句：“查询各位老师(Tno)每个学年上的课程(Cno)门数，结果显示教师编号、学年、所上课程门数,按教师编号升序排序，并用中文显示属性列名，分页显示第10条开始的后5条结果”
SELECT Tno As '教师编号',Tacademicyear As '学年',COUNT(Cno) As '所上课程门数'
FROM tutors05
GROUP BY Tacademicyear,Tno
ORDER BY Tno ASC
LIMIT 9,5;
#no.20 请写出下列查询操作的SQL语句：“查询2017学年至少上了两门课程的教师信息，结果显示教师编号、学年、所上课程门数，按教师编号升序排序，并用中文显示属性列名”
SELECT Tno As '教师编号',Tacademicyear As '学年',COUNT(Cno) As '所上课程门数'
FROM tutors05
WHERE Tacademicyear='2017'
GROUP BY Tno
HAVING COUNT(Cno)>=2
ORDER BY Tno ASC;
#no.21 请写出下列查询操作的SQL语句：“查询至少有3位老师授课的课程，结果显示课程编号和授课人数，按授课人数降序排序，并用中文显示属性列名”
SELECT Cno As '课程编号',COUNT(Tno) As '授课人数'
FROM Tutors05
GROUP BY Cno
HAVING COUNT(Tno)>=3
ORDER BY COUNT(Tno) DESC;
#no.22 请写出下列查询操作的SQL语句：“查询所有选修了课程号为112p0024并且有成绩的学生的考试情况，结果要求显示学生的学号、课程号和成绩等级('优'、'良'、'中、'及格'和'不及格)，并按成绩降序排序(提示：CASE WHEN)”
SELECT Sno As '学号',Cno As '课程号',
CASE WHEN Grade>=90 THEN '优'
WHEN Grade>=80 THEN '良'
WHEN Grade>=70 THEN '中'
WHEN Grade>=60 THEN '及格'
ELSE '不及格'
END AS '成绩等级'
FROM Reports05
WHERE Cno='112p0024' AND Grade IS NOT NULL
ORDER BY Grade DESC;
#no.2 查询20161151班的学生在大学一年级选修的课程情况，查询结果要显示学号(Sno)、姓名(Sname)、专业名(Mname)、学年(Racademicyear)、学期(Rterm)、选课的课程号(Cno)、选课的课程名称(Cname)及成绩（Grade)，并按照学号、学年、学期升序排序
SELECT S.Sno,Sname,Mname,Racademicyear,Rterm,R.Cno,Cname,Grade
FROM Students05 AS S,Major05 AS M,Courses05 AS C,Reports05 AS R
WHERE S.Mno=M.Mno AND S.Sno=R.Sno AND C.Cno=R.Cno AND Sclass='20161151' AND Racademicyear='2016'
ORDER BY S.Sno ASC,Racademicyear ASC,Rterm ASC;
#no.3 查询教师张雪的授课信息，查询结果显示教师编号(Tno)、姓名(Tname）、授课学年(Tacademicyear)、授课学期(Tterm)、授课的课程编号(Cno)、课程名(Cname)以及授课班级(Sclass)，结果按授课学年、授课学期升序排序
SELECT T.Tno,Tname,Tacademicyear,Tterm,Tut.Cno,Cname,Sclass
FROM Teachers05 AS T,Tutors05 AS Tut,Courses05 AS C
WHERE T.Tno=Tut.Tno AND Tut.Cno=C.Cno AND Tname='张雪'
ORDER BY Tacademicyear ASC,Tterm ASC;
#no.4 查询计算机与信息工程学院的各个专业的学生数，查询结果显示专业号(Mno)、专业名称(Mname）、学生人数，按专业号升序排序，并用中文名显示属性列
SELECT M.Mno AS '专业号',Mname AS '专业名称',Count(Sno) AS '学生人数'
FROM Major05 AS M,Department05 AS D,Students05 AS S
WHERE M.Mno=S.Mno AND S.Dno=D.Dno AND Dname='计算机与信息工程学院'
GROUP BY M.Mno
ORDER BY M.Mno ASC;
#no.5a 用嵌套查询和'王一凡'在同一个学院，且总工资（基本工资Tsal+岗位津贴Tcomm）比'王一凡'高的教师的信息，结果显示教师编号(Tno)、姓名(Tname)和总工资，并用中文显示属性列名
SELECT Tsal + Tcomm AS Total,Tno,Tname
FROM Teachers05
WHERE (Tsal+Tcomm)>
(SELECT Tsal +Tcomm AS Total2
FROM Teachers05
WHERE Tname='王一凡') AND
Dno=(SELECT Dno
FROM Teachers05
WHERE Tname='王一凡')
ORDER BY Tno,Tname,Total;
#no.5b 用自身连接查询和'王一凡'在同一个学院，且总工资（基本工资Tsal+岗位津贴Tcomm）比'王一凡'高的教师的信息，结果显示教师编号(Tno)、姓名(Tname)和总工资，并用中文显示属性列名
SELECT T2.Tno AS '教师编号',T2.Tname AS '姓名',T2.Tsal+T2.Tcomm AS '总工资'
FROM Teachers05 AS T1,Teachers05 AS T2
WHERE T1.Dno=T2.Dno AND T2.Tname<>'王一凡' AND (T2.Tsal+T2.Tcomm)>(T1.Tsal+T1.Tcomm) AND T1.Tname='王一凡';
#no.6 用自身连接查询前序课程是数据结构的课程的信息，查询结果显示课程编号（Cno）、课程名称（Cname）和学分（Ccredit）
SELECT C1.Cno AS '课程编号',C1.Cname AS '课程名称',C1.Ccredit AS '学分'
FROM Courses05 AS C1,Courses05 AS C2
WHERE C1.Pre_Cno=C2.Cno AND C2.Cname='数据结构';
#no.7 用自身连接查询既给20161121班上过课，也给20161151班上过课的教师信息，查询结果显示教师编号(Tno)、教师姓名(Tname)
SELECT DISTINCT T.Tno AS '教师编号',T.Tname AS '教师姓名'
FROM Teachers05 AS T,Tutors05 AS Tut1,Tutors05 AS Tut2
WHERE T.Tno=Tut1.Tno AND Tut1.Sclass='20161121' AND T.Tno=Tut2.Tno AND Tut2.Sclass='20161151';
#no.8 查询课程表中的所有课程在每个学期的选课情况，查询结果以中文名显示课程编号(Cno)、课程名称(Cname)、选课学年(Racademicyear)、选课学期(Rterm)及该学年该学期的选课人数(SelNumber)，按课程编号、选课学年、选课学期升序排序
SELECT C.Cno AS '课程编号',Cname AS '课程名称',Racademicyear AS '选课学年',Rterm AS '选课学期',Count(Sno) AS '选课人数'
FROM Reports05 AS R,Courses05 AS C
WHERE R.Cno=C.Cno
GROUP BY R.Cno,Racademicyear,Rterm
ORDER BY R.Cno ASC,Racademicyear ASC,Rterm ASC;
#no.9 查询教师表中的每一位数学与统计学院的教师的授课课情况，查询结果显示教师编号(Tno)、教师姓名(Tname)、授课学年(Tacademicyear)、授课学期(Tterm)、课程编号(Cno)及课程名称(Cname)，按教师编号、开课学年、授课学期升序排序
SELECT T.Tno,Tname,Tacademicyear,Tterm,C.Cno,Cname
FROM Teachers05 AS T,Tutors05 AS Tut,Courses05 AS C,Department05 AS D
WHERE T.Tno=Tut.Tno AND Tut.Cno=C.Cno AND T.Dno=D.Dno AND D.Dname='数学与统计学院'
ORDER BY Tno ASC,Tacademicyear ASC,Tterm ASC;
#no.10 使用谓词IN嵌套查询，查询有课程成绩不及格的学生的信息，结果显示学号(Sno)、姓名(Sname)及所在班级(Sclass)
SELECT Sno,Sname,Sclass
FROM Students05
WHERE Sno IN
(SELECT Sno
FROM Reports05
WHERE Grade<60);
#no.11 使用谓词IN嵌套查询，查询在2014学年有授课的教师的信息，结果显示教师编号(Tno)、教师姓名(Tname)及职称(Tprof)
SELECT Tno,Tname,Tprof
FROM Teachers05
WHERE Tno IN
(SELECT Tno
FROM Tutors05
WHERE Tacademicyear='2014' AND Cno IS NOT NULL);
#no.12 使用嵌套查询，查询计算机与信息工程学院中总工资高于本学院的平均总工资的教师信息，结果以中文名称显示教师编号(Tno)、教师姓名(Tname)、职称（Tprof）及总工资
SELECT Tno AS '教师编号',Tname AS '教师姓名',Tprof AS '职称',Tsal+Tcomm AS '总工资'
FROM Teachers05
WHERE Tno IN
(SELECT Tno
FROM Teachers05 AS T,Department05 AS D
WHERE Dname='计算机与信息工程学院' AND T.Dno=D.Dno AND (Tsal+Tcomm)>
(SELECT AVG(Tsal+Tcomm)
FROM Teachers05 AS T,Department05 AS D
WHERE Dname='计算机与信息工程学院' AND T.Dno=D.Dno));
#no.13 使用嵌套查询，查询总工资高于所有学院的平均总工资的教师信息，结果显示教师编号(Tno)、教师姓名(Tname)、学院名称(Dname)、职称(Tprof)及总工资，并用中文显示属性列名
SELECT Tno AS '教师编号',Tname AS '教师姓名',Dname AS '学院名称',Tprof AS '职称',Tsal+Tcomm AS '总工资'
FROM Teachers05 AS T,Department05 AS D
WHERE T.Dno=D.Dno AND (Tsal+Tcomm)>
(SELECT AVG(Tsal+Tcomm)
FROM Teachers05
);
#no.14 使用嵌套查询，查询已获学分高于2014级所有计算机与信息工程学院的学生获得的学分的学生学号(Sno)、姓名(Sname)及已获得的总学分，并用中文显示属性列名
SELECT S.Sno AS '学号',Sname AS '姓名',SUM(Ccredit) AS '已获得的总学分'
FROM Students05 AS S,Reports05 AS R,Courses05 AS C
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND Grade>=60
GROUP BY S.Sno
HAVING SUM(Ccredit)>ALL
(SELECT SUM(Ccredit)
FROM Courses05 AS C,
Reports05 AS R
WHERE LEFT(Sno,4)='2014' AND Grade>=60 AND C.Cno=R.Cno
AND SUBSTRING(Sno, 5, 2) IN
(SELECT Dno
FROM Department05
WHERE Dname = '计算机与信息工程学院')
GROUP BY Sno
);
#no.15 使用EXISTS谓词查询计算机与信息工程学院中没有讲授过课程号为112p0054课程的教师编号(Tno)、姓名(Tname)和职称(Tprof)
SELECT Tno,Tname,Tprof
FROM Teachers05 AS T,Department05 AS D
WHERE NOT EXISTS(
SELECT *
FROM Tutors05 AS Tut
WHERE T.Tno=Tut.Tno AND Tut.Cno='112p0054'
)
AND D.Dname='计算机与信息工程学院' AND T.Dno=D.Dno;
#no.16 使用EXISTS谓语查询至少选修了学生2014112104选修的全部课程的学生的学号(Sno)、姓名(Sname)
SELECT Sno,Sname
FROM Students05 AS S
WHERE NOT EXISTS(
SELECT *
FROM Reports05 AS R1
WHERE R1.Sno='2014112104'
AND NOT EXISTS(
SELECT *
FROM Reports05 AS R2
WHERE R2.Sno=S.Sno AND R2.Cno=R1.Cno
)
);
#no.17 使用嵌套子关系查询，查询课程表中每一门课程的选课情况，查询结果要显示课程编号(Cno)、课程名称(Cname)、选课人数、平均分、最高分、最低分。若某门课程还没有被选，则要选课人数显示为‘尚无人选’,平均分等显示为NULL；若某门课程有人选，但平均分等结果为NULL，则显示为’尚无成绩’；非空的数据结果保留两位小数。结果按课程编号升序排序，并用中文显示属性列名
SELECT C.Cno AS '课程编号',C.Cname AS '课程名称',COALESCE(COUNT(R.Sno),'尚无人选') AS '选课人数',
CASE
WHEN COUNT(R.Sno)=0 THEN NULL
WHEN AVG(R.Grade) IS NULL THEN '尚无成绩'
ELSE FORMAT(AVG(R.Grade),2)
END AS '平均分',
FORMAT(MAX(R.Grade),2) AS '最高分',
FORMAT(MIN(R.Grade),2) AS '最低分'
FROM Courses05 AS C
LEFT JOIN Reports05 R on C.Cno=R.Cno
GROUP BY C.Cno,C.Cname
ORDER BY C.Cno ASC;
#no.18 使用嵌套子关系查询，查询在2015学年的第1学期，选修了刘伟老师上的计算机科学概论课程的学生的基本信息，查询结果显示所在班级(Sclass)、学号(Sno)、姓名(Sname）、选修的课程编号(Cno)、课程名(Cname)以及授课的教师(Tname)
SELECT Sclass,S.Sno,Sname,Cno,Cname,Tname
FROM Students05 AS S
LEFT JOIN(
SELECT DISTINCT Sno,C.Cno,Cname,Tname
FROM Reports05 AS R ,Courses05 AS C,Tutors05 AS Tut,Teachers05 AS T
WHERE T.Tno=Tut.Tno AND T.Tname='刘伟' AND Tut.Cno=C.Cno AND C.Cname='计算机科学概论' AND R.Cno=C.Cno AND R.Racademicyear=Tut.Tacademicyear AND R.Rterm=Tut.Tterm
)RN
ON S.Sno=RN.Sno
WHERE RN.Sno IS NOT NULL;
#no.19 使用嵌套子关系查询，查询选修了数据结构，且其成绩不低于本年级所有选修了该门课程的学生的平均成绩的学生信息，查询结果以中文名称显示学生所在年级、学号(Sno)、姓名(Sname)及其选修的该门课程的成绩(Grade)
SELECT LEFT(S.Sno,4) AS '年级',S.Sno AS '学号',S.Sname AS '姓名',R.Grade AS '成绩'
FROM Students05 AS S
JOIN Reports05 AS R ON S.Sno=R.Sno
JOIN Courses05 AS C ON R.Cno=C.Cno
LEFT JOIN (
SELECT LEFT(S2.Sno,4) AS Y2, AVG(R2.Grade) AS A
FROM Students05 AS S2
JOIN Reports05 AS R2 ON S2.Sno=R2.Sno
JOIN Courses05 AS C2 ON R2.Cno=C2.Cno
WHERE C2.Cname='数据结构'
GROUP BY LEFT(S2.Sno,4)
)RN
ON LEFT(S.Sno,4)=RN.Y2
WHERE C.Cname='数据结构' AND R.Grade>=RN.A;
#各表外键的设置
ALTER TABLE Department05
ADD CONSTRAINT FK_Dep_Dheader FOREIGN KEY (Dheader) REFERENCES Teachers05(Tno);
ALTER TABLE Major05
ADD CONSTRAINT FK_Maj_Dno FOREIGN KEY (Dno) REFERENCES Department05(Dno);
ALTER TABLE Students05
ADD CONSTRAINT fk_Mno FOREIGN KEY (Mno) REFERENCES Major05(Mno);
ALTER TABLE Students05
ADD CONSTRAINT FK_Stu_Dno FOREIGN KEY (Dno) REFERENCES Department05(Dno);
ALTER TABLE Teachers05
ADD CONSTRAINT FK_Tea_Dno FOREIGN KEY (Dno) REFERENCES Department05(Dno);
ALTER TABLE Reports05
ADD CONSTRAINT FK_Rep_Sno FOREIGN KEY (Sno) REFERENCES Students05(Sno);
ALTER TABLE Reports05
ADD CONSTRAINT FK_Rep_Cno FOREIGN KEY (Cno) REFERENCES Courses05(Cno);
ALTER TABLE Courses05
ADD CONSTRAINT FK_Cou_Precno FOREIGN KEY (Pre_Cno) REFERENCES Courses05(Cno);
ALTER TABLE Tutors05
ADD CONSTRAINT FK_Tut_Tno FOREIGN KEY (Tno) REFERENCES Teachers05(Tno);
ALTER TABLE Tutors05
ADD CONSTRAINT FK_Tut_Cno FOREIGN KEY (Cno) REFERENCES Courses05(Cno);
#no.2 在学院表中增加一个新的学院，外国语学院，学院编号为15，院长暂时不设置
INSERT INTO Department05(Dname,Dno)
VALUES('外国语学院','15');
#no.3 学号为2014112103的学生的数据结构课程的补考成绩为63分，请用补考成绩更新原有的考试成绩
UPDATE Reports05
SET Grade='63'
WHERE Sno='2014112103'
AND Cno=
(SElECT Cno
FROM Courses05
WHERE Cname='数据结构');
# SELECT R.Sno,C.Cno,C.Cname,R.Grade
# FROM Courses05 AS C,Reports05 AS R
# WHERE C.Cname='数据结构' AND R.Cno=C.Cno AND R.Sno='2014112103';
#no.4 为表Tutors增加新列Tutid，将该列设置为第一列，并将Tutid列设置为自动增长的整数列，同时设置该列为主键
ALTER TABLE Tutors05
ADD COLUMN Tutid INT AUTO_INCREMENT PRIMARY KEY FIRST;
#no.5 2018学年第1学期，为20171151班的所有学生开设了计算机网络课程，并由“许永军”教授进行授课
INSERT INTO Tutors05(Cno,Tno,Tacademicyear,Tterm,Sclass)
VALUES((SELECT Cno
FROM Courses05
WHERE Cname='计算机网络'),
(SELECT Tno
FROM Teachers05
WHERE Tname='许永军'),
'2018','1','20171151');
#no.6 李桂清教授光荣退休了，需要将他在2018学年第2学期给20161121班的学生所上的数字图像制作课程将改由李力授课
UPDATE Tutors05
SET Tno=(SELECT Tno
FROM Teachers05
WHERE Tname='李力')
WHERE Tacademicyear='2018' AND Tterm='2' AND Sclass='20161121'
AND Tno=(SELECT Tno
FROM Teachers05
WHERE Tname='李桂清');
# SELECT T.Tno,T.Tname,C.Cno,C.Cname,Tut.Tacademicyear,Tut.Tterm,Tut.Sclass
# FROM Teachers05 AS T,Courses05 AS C,Tutors05 AS Tut
# WHERE T.Tname='李力' AND T.Tno=Tut.Tno AND C.Cname='数字图像制作' AND C.Cno=Tut.Cno;
#no.7 李桂清教授光荣退休了，需要将他的信息从教师表中删除
DELETE FROM Teachers05
WHERE Tname='李桂清';
#no.9 学号为2017120202的学生因病退学了，请从学生表中将他的信息删除
DELETE FROM Students05
WHERE Sno='2017120202';
#no.10 ① 将选课表中对Sno的外键声明重新设置可为级联删除；
# ② 再从学生表中将学号2017120202的学生信息删除
ALTER TABLE Reports05
DROP FOREIGN KEY FK_Rep_Sno,
ADD CONSTRAINT FK_Rep_Sno2 FOREIGN KEY (Sno) REFERENCES Students05(Sno) ON DELETE CASCADE;
DELETE FROM Students05
WHERE Sno='2017120202';
#no.11 由于人才培养方案的变更，数学与统计学院的数据科学与大数据技术（非师范）专业的编号将变更为编号1203，请直接将专业表中的专业编号1202更改为1203的信息
UPDATE Major05
SET Mno='1203'
WHERE Mname='数据科学与大数据技术（非师范）';
#no.12 ① 将学生表中对Mno的外键声明重新设置可为级联更新；
# ② 再重新更新专业表中数据科学与大数据技术（非师范）专业的专业编号
ALTER TABLE Students05
DROP FOREIGN KEY fk_Mno,
ADD CONSTRAINT FK_Stu_Mno FOREIGN KEY (Mno) REFERENCES Major05(Mno) ON UPDATE CASCADE;
UPDATE Major05
SET Mno='1203'
WHERE Mname='数据科学与大数据技术（非师范）';
#no.13a 分别任命李力、张兰和张雪为本学院的院长
UPDATE Department05
SET Dheader=(SELECT Tno FROM Teachers05 WHERE Tname='李力')
WHERE Dno=(SELECT T.Dno FROM Teachers05 AS T WHERE T.Tname='李力');
UPDATE Department05
SET Dheader=(SELECT Tno FROM Teachers05 WHERE Tname='张兰')
WHERE Dno=(SELECT T.Dno FROM Teachers05 AS T WHERE T.Tname='张兰');
UPDATE Department05
SET Dheader=(SELECT Tno FROM Teachers05 WHERE Tname='张雪')
WHERE Dno=(SELECT T.Dno FROM Teachers05 AS T WHERE T.Tname='张雪');
#no.13b 分别任命李力、张兰和张雪为本学院的院长
UPDATE Department05
SET Dheader=CASE
WHEN Dno=(SELECT Dno FROM Teachers05 WHERE Tname='李力') THEN (SELECT Tno FROM Teachers05 WHERE Tname='李力')
WHEN Dno=(SELECT Dno FROM Teachers05 WHERE Tname='张兰') THEN (SELECT Tno FROM Teachers05 WHERE Tname='张兰')
WHEN Dno=(SELECT Dno FROM Teachers05 WHERE Tname='张雪') THEN (SELECT Tno FROM Teachers05 WHERE Tname='张雪')
END
WHERE Dno IN (SELECT Dno FROM Teachers05 WHERE Tname IN ('李力','张兰','张雪'));
#SELECT D.Dno,D.Dname,D.Dheader,T.Tname
#FROM Department05 AS D,Teachers05 AS T
#WHERE D.Dname<>'外国语学院' AND D.Dheader=T.Tno
#GROUP BY D.Dno;
#no.14 创建一个教师工作量统计表TeaQuantity，其中包含统计编号Qid、教师编号Tno、学年Tacademicyear和工作量Tquantity，
# 其中Tno和Tacademicyear的数据类型与Tutors表中的设置相同，Qid的数据类型定义为Binary(16)，默认输入UNIQUEINDETIFIER(UUID)的二进制数，
# 且设置为该表的主键，Tquantity的数据类型设置为整数，并且在属性列Tno和Tacademicyear上设置唯一性约束，即约束该表中每个教师编号，每个学年只统计一个工作量
CREATE TABLE IF NOT EXISTS TeaQuantity(
Qid VARCHAR(36) PRIMARY KEY DEFAULT (UUID()) COMMENT '统计编号',
Tno CHAR(4) COMMENT '教师编号',
Tacademicyear year COMMENT '学年',
Tquantity INT,
UNIQUE(Tno,Tacademicyear)
);
#no.15 对所有教师的授课工作量按学年进行统计，即统计出每个学年该位教师所上课程的总课时量，并将统计结果添加到TeaQuantity表中
INSERT INTO TeaQuantity(Tno,Tacademicyear,Tquantity)
(SELECT Tno,Tacademicyear,SUM(C.Chours)
FROM Tutors05 AS Tut,Courses05 AS C
WHERE Tut.Cno=C.Cno
GROUP BY Tno,Tacademicyear);
#SELECT *
#FROM TeaQuantity
#WHERE Tno='T004';
#no.16 ① 2019学年第1学期，为20171202班的所有学生开设了数据库系统原理，并由“王平”老师进行授课；
# ② 重新对“王平”老师的授课工作量按学年进行统计，再次将统计结果添加到TeaQuantity表中
INSERT INTO Tutors05(Tno,Cno,Tacademicyear,Tterm,Sclass)
VALUES((SELECT Tno FROM Teachers05 WHERE Tname='王平'),
(SELECT Cno FROM Courses05 WHERE Cname='数据库系统原理'),
'2019','1','20171202');
INSERT INTO Reports05(Sno,Cno,Racademicyear,Rterm)
VALUES((SELECT Sno FROM Students05 WHERE Sclass='20171202'),
(SELECT Cno FROM Courses05 WHERE Cname='数据库系统原理'),
'2019','1');
UPDATE TeaQuantity
SET Tquantity=(SELECT SUM(Chours)
FROM Courses05 AS C,Tutors05 AS Tut,Teachers05 AS T
WHERE T.Tname='王平' AND T.Tno=Tut.Tno AND Tut.Cno=C.Cno AND Tut.Tacademicyear='2019')
WHERE Tno=(SELECT Tno FROM Teachers05 WHERE Tname='王平') AND Tacademicyear='2019';
#SELECT T.Tno,Tname,C.Cno,Cname,Tacademicyear,Tterm,Sclass
#FROM Teachers05 AS T,Courses05 AS C,Tutors05 AS Tut
#WHERE T.Tname='王平' AND T.Tno=Tut.Tno AND C.Cname='数据库系统原理' AND C.Cno=Tut.Cno;
#SELECT S.Sno,Sname,Sclass,C.Cno,Cname,Racademicyear,Rterm
#FROM Students05 AS S,Courses05 AS C,Reports05 AS R
#WHERE S.Sclass='20171202' AND S.Sno=R.Sno AND C.Cname='数据库系统原理' AND C.Cno=R.Cno;
#SELECT T.Tname,T.Tno,Tea.Tacademicyear,Tquantity
#FROM TeaQuantity AS Tea,Teachers05 AS T
#WHERE T.Tname='王平' AND T.Tno=Tea.Tno;
#no.17 重新对“王平”老师的授课工作量按学年进行统计，并将统计结果更新到TeaQuantity表中
UPDATE TeaQuantity
SET Tquantity=(SELECT SUM(Chours)
FROM Courses05 AS C,Tutors05 AS Tut,Teachers05 AS T
WHERE T.Tname='王平' AND T.Tno=Tut.Tno AND Tut.Cno=C.Cno AND Tut.Tacademicyear='2019')
WHERE Tno=(SELECT Tno FROM Teachers05 WHERE Tname='王平') AND Tacademicyear='2019';
#no.18 ① 在学生表中增加CHECK约束：Sno的长度必须为10个字符长度；
# ② 分别向学生表中添加一条正确的学生信息和一条错误的学生信息，验证CHECK约束是否能正确执行
ALTER TABLE Students05
MODIFY Sno CHAR(10) CHECK(LENGTH(Sno) = 10);
INSERT INTO Students05(Sno,Sname)
VALUES('2021051295','易建联');
INSERT INTO Students05(Sno,Sname)
VALUES('202503005115','赵睿');
#no.19 在教师表中增加CHECK约束：
#若教师的职称是教授，则其工资Tsal要在3500元以上；
#若教师的职称是副教授，则其工资要在2600元到3500元之间；
#若教师的职称是讲师，则其工资要在1600元到2600元之间；
#若教师的职称是助教，则其工资要在800元到1600元之间
ALTER TABLE Teachers05
ADD CONSTRAINT Check_Tsal CHECK (
(Tprof='教授' AND Tsal>'3500') OR
(Tprof='副教授' AND Tsal>'2600' AND Tsal<'3500') OR
(Tprof='讲师' AND Tsal>'1600' AND Tsal<'2600') OR
(Tprof='助教' AND Tsal>'800' AND Tsal<'1600')
);
#no.20 张兰老师因评上教授，需将其职称(Tprof)由副教授改为教授，并将工资(Tsal)改为3300，岗位津贴(Tcomm)改为2400
UPDATE Teachers05
SET Tprof='教授',Tsal='3300',Tcomm='2400'
WHERE Tname='张兰';
#no.21 许红霞老师评上了副教授，需将其职称(Tprof)由讲师改为副教授，并将工资(Tsal)改为2700，岗位津贴(Tcomm)改为2000
UPDATE Teachers05
SET Tprof='副教授',Tsal='2700',Tcomm='2000'
WHERE Tname='许红霞';
SELECT *
FROM Teachers05
WHERE Tname='许红霞';
#no.2 请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuRepInfo_学号最后两位；
#利用学生学号查询该名学生各学期的选修课程情况，要求显示学生的学号，姓名，选修的学年，学期，选修的课程号，课程名及其成绩，显示结果按学年和学期升序排列。
DELIMITER //
CREATE PROCEDURE PROC_StuRepInfo_05(
IN SNO CHAR(10)
)
BEGIN
SELECT S.Sno AS '学号',S.Sname AS '姓名',R.Racademicyear AS '选修学年',R.Rterm AS '学期',R.Cno AS '课程号',
C.Cname AS '课程名',R.Grade AS '成绩'
FROM Students05 AS S,Reports05 AS R,Courses05 AS C
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND S.Sno=SNO
ORDER BY R.Racademicyear ASC,R.Rterm ASC;
END//
DELIMITER ;
#DROP PROCEDURE PROC_StuRepInfo_05;
#no.3 请写出下列调用存储过程PROC_StuRepInfo_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位查询学号为‘2015112101'的选课情况
CALL PROC_StuRepInfo_05('2015112101');
#no.4 请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuGrade_学号最后两位查询某位学生指定课程的成绩和可获得这门课程的学分
#（注：若该学生的课程成绩小于60分或是为空，则学分要显示为0分）
DELIMITER //
CREATE PROCEDURE PROC_StuGrade_05(
IN SN VARCHAR(4),IN CN VARCHAR(20)
)
BEGIN
SELECT S.Sname,C.Cname,R.Grade,
CASE WHEN R.Grade<'60' THEN '0'
WHEN R.Grade IS NULL THEN '0'
ELSE C.Ccredit
END AS Ccredit
FROM Students05 AS S,Reports05 AS R,Courses05 AS C
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND S.Sname=SN AND C.Cname=CN;
END//
DELIMITER ;
#DROP PROCEDURE PROC_StuGrade_05;
#no.5 请写出下列调用存储过程PROC_StuGrade_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
#（1）查询'张建国'选修的'数据库系统原理'课程的成绩和学分
#（2）查询'朱玉玲'选修的'计算机科学概论'课程的成绩和学分
CALL PROC_StuGrade_05('张建国','数据库系统原理');
CALL PROC_StuGrade_05('朱玉玲','计算机科学概论');
#no.6 请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_DepNativeInfo_学号最后两位
#统计指定入学年级指定学院名中，各省的生源人数，要求显示入学年级，学院名，生源地（只显示省份）及生源人数。
DELIMITER //
CREATE PROCEDURE PROC_DepNativeInfo_05(
IN SY CHAR(4),IN DN VARCHAR(10)
)
BEGIN
SELECT LEFT(S.Sno,4) AS '入学年级',D.Dname AS '学院名',LEFT(S.Snative,2) AS '生源地',COUNT(S.Sno) AS '生源人数'
FROM Students05 AS S,Department05 AS D
WHERE S.Dno=D.Dno AND LEFT(S.Sno,4)=SY AND D.Dname=DN
GROUP BY D.Dname,LEFT(S.Snative,2);
END//
DELIMITER ;
#DROP PROCEDURE PROC_DepNativeInfo_05;
#no.7 请写出下列调用存储过程PROC_DepNativeInfo_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号
#最后两位查询2014级计算机与信息工程学院的生源情况
CALL PROC_DepNativeInfo_05('2014','计算机与信息工程学院');
#no.8 请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuGradeNum_学号最后两位统计
#指定班级指定课程的考试情况，要求显示出不同分值段（10分为一个分值段）以及尚未考试的学生人数，如（100~90,2人；89~80,7人；…未考,2人）。
DELIMITER //
CREATE PROCEDURE PROC_StuGradeNum_05(
IN SCL CHAR(8),
IN CN VARCHAR(10)
)
BEGIN
SELECT T.'成绩范围',COUNT(T.'成绩范围') AS '人数'
FROM (
SELECT
CASE
WHEN R.Grade<='100' AND R.Grade>='90' THEN '100~90'
WHEN R.Grade<='89' AND R.Grade>'80' THEN '89~80'
WHEN R.Grade<='79' AND R.Grade>'70' THEN '79~70'
WHEN R.Grade<='69' AND R.Grade>'60' THEN '69~60'
WHEN R.Grade<='59' AND R.Grade>'50' THEN '59~50'
WHEN R.Grade<='49' AND R.Grade>'40' THEN '49~40'
WHEN R.Grade<='39' AND R.Grade>'30' THEN '39~30'
WHEN R.Grade<='29' AND R.Grade>'20' THEN '29~20'
WHEN R.Grade<='19' AND R.Grade>'10' THEN '19~10'
WHEN R.Grade<='9' AND R.Grade>='0' THEN '9~0'
WHEN R.Grade IS NULL THEN '未考'
END AS '成绩范围'
FROM Reports05 AS R,Students05 AS S,Courses05 AS C
WHERE S.Sno = R.Sno AND R.Cno = C.Cno AND C.Cname = CN AND S.Sclass = SCL
) AS T
GROUP BY
T.'成绩范围';
END//
DELIMITER ;
#DROP PROCEDURE PROC_StuGradeNum_05;
#no.9 请写出下列调用存储过程PROC_StuGradeNum_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
#查询20152101班计算机科学概论的考试情况
CALL PROC_StuGradeNum_05('20152101','计算机科学概论');
#no.10 请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuCreditInfo_学号最后两位
#利用学生学号查询该名学生在指定学年，指定学期所获得的学分数以及还未获得的学分数，若该门课程尚无成绩或
#成绩小于60分，则为尚未获得的学分，结果以变量形式输出（OUTPUT）。
DELIMITER //
CREATE PROCEDURE PROC_StuCreditInfo_05(
IN SNO CHAR(10),IN RAC CHAR(4),IN RT CHAR(1),OUT CRE INT,OUT NCRE INT
)
BEGIN
DECLARE Total INT;
SET CRE='0';
SET NCRE='0';
SELECT SUM(C.Ccredit) INTO CRE
FROM Students05 AS S,Reports05 AS R,Courses05 AS C
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND R.Grade>='60' AND SNO=S.Sno AND RAC=R.Racademicyear AND RT=R.Rterm;
SELECT SUM(C.Ccredit) INTO Total
FROM Students05 AS S,Reports05 AS R,Courses05 AS C
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND SNO=S.Sno AND RAC=R.Racademicyear AND RT=R.Rterm;
SET NCRE=Total-IFNULL(CRE,'0');
END//
DELIMITER ;
#DROP PROCEDURE PROC_StuCreditInfo_05;
#no.11 请写出下列调用存储过程PROC_StuCreditInfo_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
#（1）查询学号为2016115101的学生2017年第2学期的学分情况
#（2）查询学号为2014112104的学生2014年第1学期的学分情况
CALL PROC_StuCreditInfo_05('2016115101','2017','2',@CRE,@NCRE);
SELECT '2016115105' AS '学号','2017' AS '学年','2' AS '学年',IFNULL(@CRE,'0') AS '已获学分',IFNULL(@NCRE,'0') AS '未获学分';
CALL PROC_StuCreditInfo_05('2014112104','2014','1',@CRE,@NCRE);
SELECT '2014112104' AS '学号','2014' AS '学年','1' AS '学年',IFNULL(@CRE,'0') AS '已获学分',IFNULL(@NCRE,'0') AS '未获学分';
#no.12 请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_TeaWork_学号最后两位
#统计指定教师指定学年的教学工作量(教学工作量=所有教授的课程学时数的总和)，教学工作量要求作为输出参数（OUT）
DELIMITER //
CREATE PROCEDURE PROC_TeaWork_05(
IN TN VARCHAR(4),IN TAC CHAR(4),OUT TQ INT
)
BEGIN
SET TQ='0';
SELECT TQU.Tquantity INTO TQ
FROM Teachers05 AS T,TeaQuantity AS TQU
WHERE T.Tno=TQU.Tno AND TN=T.Tname AND TAC=TQU.Tacademicyear;
END//
DELIMITER ;
#DROP PROCEDURE PROC_TeaWork_05;
#no.13 请写出下列调用存储过程PROC_TeaWork_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
#（1）查询教师刘伟2018学年的教学工作量
#（2）查询教师王一凡2016学年的教学工作量
CALL PROC_TeaWork_05('刘伟',2018,@TQ);
SELECT '刘伟' AS '教师','2018' AS '学年',IFNULL(@TQ,'0') AS '教学工作量';
CALL PROC_TeaWork_05('王一凡',2016,@TQ);
SELECT '王一凡' AS '教师','2016' AS '学年',IFNULL(@TQ,'0') AS '教学工作量';
#no.14 请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_TeaTax_学号最后两位统计指定教师每月需要缴纳的税金是多少，并将统计结果以变量形式输出（OUT）
#如果是中级以下职称（含中级），则税金=（工资+岗位津贴-1500）*5%；
#如果是副高级职称，则税金=（工资+岗位津贴-2000）*5%；
#如果是正高级职称，则税金=（工资+岗位津贴-2500）*5%
#提示：IF… ELSE… 注意：返回的税金应是实数
DELIMITER //
CREATE PROCEDURE PROC_TeaTax_05(
IN TN VARCHAR(4),OUT TAX DOUBLE
)
BEGIN
DECLARE TPR VARCHAR(3);
DECLARE TS DOUBLE;
DECLARE TC DOUBLE;
SELECT Tprof,Tsal,Tcomm INTO TPR, TS, TC
FROM Teachers05
WHERE Tname=TN;
IF TPR='讲师' OR TPR='助教' THEN
SET TAX=(TS + TC - 1500) * 0.05;
ELSEIF TPR='副教授' THEN
SET TAX=(TS + TC - 2000) * 0.05;
ELSEIF TPR='教授' THEN
SET TAX=(TS + TC - 2500) * 0.05;
END IF;
END//
DELIMITER ;
#DROP PROCEDURE PROC_TeaTax_05;
#no.15 请写出下列调用存储过程PROC_TeaTax_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
#（1）查询教师张雪应缴纳的税金
#（2）查询教师田锋应缴纳的税金
CALL PROC_TeaTax_05('张雪',@TAX);
SELECT '张雪' AS '教师',FORMAT(@TAX,2) AS '税金';
CALL PROC_TeaTax_05('田峰',@TAX);
SELECT '田峰' AS '教师',FORMAT(@TAX,2) AS '税金';
#no.16 请写出下列创建存储过程的MySQL语句(选课情况综合查询)，存储过程名命名为PROC_StuRepSel_学号最后两位
#此存储过程可按照用户指定的查询字段，包括学号，学生姓名，课程编号，课程名称、学年或班级编号查询学生的选课情况，
#查询结果显示(班级编号，学号，学生姓名，课程编号，课程名称，选修学年，选修学期，成绩)
#提示：需要两个输入参数，一个参数用来指定要查询的字段，另一个参数用来指定要查询字段的具体值，存储过程中需要根据查询字段不同，分别写出对应的查询语句
DELIMITER //
CREATE PROCEDURE PROC_StuRepSel_05(
IN Query_Field VARCHAR(4), IN Query_Value VARCHAR(10)
)
BEGIN
IF Query_Field='学号' THEN
SELECT S.Sclass,S.Sno,S.Sname,C.Cno,C.Cname,R.Racademicyear,R.Rterm,R.Grade
FROM Students05 AS S,Courses05 As C,Reports05 AS R
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND Query_Value=S.Sno;
END IF;
IF Query_Field='学生姓名' THEN
SELECT S.Sclass,S.Sno,S.Sname,C.Cno,C.Cname,R.Racademicyear,R.Rterm,R.Grade
FROM Students05 AS S,Courses05 As C,Reports05 AS R
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND Query_Value=S.Sname;
END IF;
IF Query_Field='课程编号' THEN
SELECT S.Sclass,S.Sno,S.Sname,C.Cno,C.Cname,R.Racademicyear,R.Rterm,R.Grade
FROM Students05 AS S,Courses05 As C,Reports05 AS R
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND Query_Value=C.Cno;
END IF;
IF Query_Field='课程名称' THEN
SELECT S.Sclass,S.Sno,S.Sname,C.Cno,C.Cname,R.Racademicyear,R.Rterm,R.Grade
FROM Students05 AS S,Courses05 As C,Reports05 AS R
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND Query_Value=C.Cname;
END IF;
IF Query_Field='选修学年' THEN
SELECT S.Sclass,S.Sno,S.Sname,C.Cno,C.Cname,R.Racademicyear,R.Rterm,R.Grade
FROM Students05 AS S,Courses05 As C,Reports05 AS R
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND Query_Value=R.Racademicyear;
END IF;
IF Query_Field='选修学期' THEN
SELECT S.Sclass,S.Sno,S.Sname,C.Cno,C.Cname,R.Racademicyear,R.Rterm,R.Grade
FROM Students05 AS S,Courses05 As C,Reports05 AS R
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND Query_Value=R.Rterm;
END IF;
IF Query_Field='班级编号' THEN
SELECT S.Sclass,S.Sno,S.Sname,C.Cno,C.Cname,R.Racademicyear,R.Rterm,R.Grade
FROM Students05 AS S,Courses05 As C,Reports05 AS R
WHERE S.Sno=R.Sno AND R.Cno=C.Cno AND Query_Value=S.Sclass;
END IF;
END//
DELIMITER ;
#DROP PROCEDURE PROC_StuRepSel_05;
#no.17 请写出下列调用存储过程PROC_StuRepSel_学号最后两位的MySQL语句，并将执行结果截图，截图的右下角要求标上你的学号最后两位
#（1）查询学号为2014112106的学生的选课情况
#（2）查询20141151班的学生的选课情况
CALL PROC_StuRepSel_05('学号','2014112106');
CALL PROC_StuRepSel_05('班级编号','20141151');
#no.3 请写出下列的SQL程序代码：“在Students上创建一个UPDATE触发器Trig_UpdStuBirth_学号最后两位，
#要求：当更新学生的出生日期时，检查此学生的入学年龄是否是在14~40岁之间，若是则允许更新，若不是则提示错误信息‘学生的出生日期有误，请确认后重新输入！’。”
DELIMITER //
CREATE TRIGGER Trig_UpdStuBirth_05
BEFORE UPDATE
ON Students05
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(200);
IF (NEW.Sbirth!=OLD.Sbirth) THEN
IF LEFT(OLD.Sno,4)-YEAR(NEW.Sbirth)<'14' OR LEFT(OLD.Sno,4)-YEAR(NEW.Sbirth)>'40' THEN
SET msg='学生的出生日期有误，请确认后重新输入！';
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;
END IF ;
END IF;
END//
DELIMITER ;
#DROP TRIGGER Trig_UpdStuBirth_05;
#no.4 针对UPDATE触发器Trig_UpdStuBirth_学号最后两位，分别设计并写出正反例的SQL语句各一条，即一条语句执行后允许更改，
#一条语句执行后，系统提示出错。要求将执行结果截图，截图中写入学号后两位。
#正例
UPDATE Students05
SET Sbirth='2000-04-25'
WHERE Sno='2021051295';
#反例
UPDATE Students05
SET Sbirth='2005-12-22'
WHERE Sno='2017120201';
#no.5 请写出下列的SQL程序代码："在Students表上创建一个INSERT触发器Trig_InsStuSno_学号最后两位，要求：当插入一条学生记录时，
#首先判断学生的学号长度是否为10，然后再判断，输入的学号是否都是数字。若两个条件都成立，则允许执行INSERT语句，否则，要分别给出错误提示信息"
DELIMITER //
CREATE TRIGGER Trig_InsStuSno_05
BEFORE INSERT
ON Students05
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(200);
IF LENGTH(NEW.Sno)!=10 THEN
SET msg='学生的学号长度应为10，请重新输入！';
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;
ELSE IF NEW.Sno NOT REGEXP '^[0-9]+$' THEN
SET msg='学生的学号应都为数字，请重新输入！';
SIGNAL SQLSTATE 'HY001' SET MESSAGE_TEXT=msg;
END IF;
END IF;
END//
DELIMITER ;
#DROP TRIGGER Trig_InsStuSno_05;
#no.6 针对INSERT触发器Trig_InsStuSno_学号最后两位，分别设计并写出正例的SQL语句一条，反例的SQL语句两条（分别对应两个判断），
#即正例语句执行后允许插入，反例语句执行后，系统提示出错。要求将执行结果截图，截图中写入学号后两位。
#正例
INSERT INTO Students05(Sno,Sname)
VALUES('2019050113','赵睿');
#反例1
INSERT INTO Students05(Sname,Sno)
VALUE('郭艾伦','20180211258');
#反例2
INSERT INTO Students05(Sname,Sno)
VALUES('郭艾伦','A201801129');
#no.7 请写出下列的SQL程序代码：“在Teachers表上创建一个UPDATE触发器Trig_UpdTeaProf_学号最后两位，
#要求：首先确认更新的职称是否正确，即只能输入'助教'、'讲师'、'副教授'或'教授'，若输入错误，则系统提示错误信息；
#当职称从'助教'晋升为'讲师'时，岗位津贴(TComm)增加300元；当职称从'讲师'晋升为'副教授'时，岗位津贴(TComm)增加500元；
#当职称从'副教授'晋升为'教授'时，岗位津贴自动增加900元；不允许越级晋升，即不能从助教直接升级为副教授，不能从讲师直接升级为教授；也不能降级。”
DELIMITER //
CREATE TRIGGER Trig_UpdTeaProf_05
BEFORE UPDATE
ON Teachers05
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(20);
IF NEW.Tprof NOT IN('助教','讲师','副教授','教授') THEN
SET msg='教师的职称有误，请重新输入！';
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;
ELSE
IF NEW.Tprof='讲师' AND OLD.Tprof='助教' THEN
SET NEW.Tcomm=OLD.Tcomm+300;
ELSEIF NEW.Tprof='副教授' AND OLD.Tprof='讲师' THEN
SET NEW.Tcomm=OLD.Tcomm+500;
ELSEIF NEW.Tprof='教授' AND OLD.Tprof='副教授' THEN
SET NEW.Tcomm=OLD.Tcomm+900;
ELSE
SET msg='不允许越级晋升或降级';
SIGNAL SQLSTATE 'HY001' SET MESSAGE_TEXT=msg;
END IF;
END IF;
END//
DELIMITER ;
#DROP TRIGGER Trig_UpdTeaProf_05;
#no.8 针对UPDATE触发器Trig_UpdTeaTpro_学号最后两位，设计并写出反例的SQL语句两条，如修改的职称写成了'jiangshi'，
#原有的职称'助教'改成了'副教授'，语句执行后，系统提示出错信息。要求将执行结果截图，截图中写入学号后两位。
#反例1
UPDATE Teachers05
SET Tprof='jiangshi'
WHERE Tname='张兰';
#反例2
UPDATE Teachers05
SET Tprof='副教授'
WHERE Tname='田峰';
#no.9 针对UPDATE触发器Trig_UpdTeaTpro_学号最后两位，设计并写出正例的SQL语句一条，如'讲师'改为'副教授'，
#提示：执行语句后，系统还是会提示出错信息，但提示的信息似乎与触发器中的信息不同，将执行结果截图，并分析原因，截图中写入学号后两位。
#正例
UPDATE Teachers05
SET Tprof='副教授'
WHERE Tname='王平';
#no.10 请写出更改后的Teachers表上的UPDATE触发器Trig_UpdTeaTpro_学号最后两位的SQL程序，使得语句能正确执行，即给出正例SQL语句一条，
#当职称'讲师'改为'副教授'时，语句能正确执行。将执行结果截图，截图中写入学号后两位。
#提示：（1）在UPDATE触发器中，加入新的赋值语句，以确保满足系统提示出错的约束条件即可；（2）执行正反例程序时，最好给出语句执行前后的结果对照
DELIMITER //
CREATE TRIGGER Trig_UpdTeaProf_05
BEFORE UPDATE
ON Teachers05
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(20);
IF NEW.Tprof NOT IN('助教','讲师','副教授','教授') THEN
SET msg='教师的职称有误，请重新输入！';
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;
ELSE
IF NEW.Tprof='讲师' AND OLD.Tprof='助教' THEN
SET NEW.Tsal=1601;
SET NEW.Tcomm=OLD.Tcomm+300;
ELSEIF NEW.Tprof='副教授' AND OLD.Tprof='讲师' THEN
SET NEW.Tsal=2601;
SET NEW.Tcomm=OLD.Tcomm+500;
ELSEIF NEW.Tprof='教授' AND OLD.Tprof='副教授' THEN
SET NEW.Tsal=3601;
SET NEW.Tcomm=OLD.Tcomm+900;
ELSE
SET msg='不允许越级晋升或降级';
SIGNAL SQLSTATE 'HY001' SET MESSAGE_TEXT=msg;
END IF;
END IF;
END//
DELIMITER ;
#DROP TRIGGER Trig_UpdTeaProf_05;
#正例
UPDATE Teachers05
SET Tprof='副教授'
WHERE Tname='王平';
#no.11 请写出下列的SQL程序代码："在Tutors表上创建一个INSERT触发器Trig_InsTutCno_学号最后两位，
#要求：当插入一条教师的授课记录时，首先判断是否已经有教师给当前班级讲授过该门课程了（无论哪个学期），
#若有，则系统提示出错信息，不允许执行INSERT语句，
#若没有，则允许执行语句，且在选课表中自动插入该班所有学生选修该门课程的选课信息（要注意选修学年和选修学期也要对应）。"
DELIMITER //
CREATE TRIGGER Trig_InsTutCno_05
BEFORE INSERT
ON Tutors05
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(200);
IF EXISTS(SELECT * FROM Tutors05 WHERE Sclass=NEW.Sclass AND Cno=NEW.Cno) THEN
SET msg='已有教师给当前班级讲授过该门课程，请重新插入！';
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;
ELSE
INSERT INTO Reports05(Sno, Cno, Racademicyear, Rterm)
(SELECT Sno,NEW.Cno,NEW.Tacademicyear,NEW.Tterm
FROM Students05
WHERE Sclass=NEW.Sclass);
END IF;
END//
DELIMITER ;
#DROP TRIGGER Trig_InsTutCno_05;
#no.12 针对INSERT触发器Trig_InsTutCno_学号最后两位，设计并写出正例的SQL语句一条，即正例语句执行后允许插入授课信息，
#且自动在Reports表中插入学生的选课信息；设计并写出反例的SQL语句一条，即反例语句执行后，系统提示出错。要求将执行结果截图，截图中写入学号后两位。
#正例
INSERT INTO Tutors05(Tno, Cno, Tacademicyear, Tterm, Sclass)
VALUES('T003','112p0023','2015','1','20141121');
#反例
INSERT INTO Tutors05(Tno, Cno, Tacademicyear, Tterm, Sclass)
VALUES('T003','112p0055','2015','2','20141121');
#no.13 请写出下列的SQL程序代码：“在Courses表上创建一个DELETE触发器Trig_DelCou，
#要求：当要删除一门课程时，首先在选课表中查看是否已有学生选修了该门课程，若有且已有成绩，则系统提示出错信息，不允许删除；
#若还没有学生选修，或是有学生选修了但成绩为空，则先将选课表中该门课程的选修记录删除掉，并将授课表中该门课程的授课记录也删除掉，
#同时允许删除课程表中的课程记录。”
DELIMITER //
CREATE TRIGGER Trig_DelCou_05
BEFORE DELETE
ON Courses05
FOR EACH ROW
BEGIN
DECLARE msg varchar(200);
IF EXISTS(SELECT * FROM Reports05 WHERE Cno=OLD.Cno AND Grade IS NOT NULL) THEN
SET msg='已有学生选修了该门课程，不允许删除！';
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;
ELSE DELETE FROM Reports05 WHERE Cno=OLD.Cno;
DELETE FROM Tutors05 WHERE Cno=OLD.Cno;
END IF;
END//
DELIMITER ;
#DROP TRIGGER Trig_DelCou_05;
#no.14 针对DELETE触发器Trig_DelCou_学号最后两位，设计并写出正例的SQL语句一条，
#提示：执行语句前，可先在课程表中加入一门新的课程信息，然后在授课表中添加一条该门课程的授课信息后
#（因已创建了授课表上的Trig_InsTutor，因此系统自动在选课表中加入了该门课程的选课信息，将选课信息查出来并截图），
#再执行删除课程的语句；设计并写出反例的SQL语句一条，语句执行后，系统还是会提示出错信息。将执行结果截图，截图中写入学号后两位。
#正例
INSERT INTO Courses05(Cno,Cname,Pre_Cno,Chours,Ccredit)
VALUES('112p0068','软件工程','112p0046','80','4');
INSERT INTO Tutors05(Tno,Cno,Tacademicyear,Tterm,Sclass)
VALUES('T005','112p0068','2018','1','20161151');
DELETE FROM Courses05
WHERE Cno='112p0068';
#反例
DELETE FROM Courses05
WHERE Cno='112p0015';
#no.15 请写出下列的SQL程序代码：
#“（1）创建一个学分表StuCredits，包含两个属性列(学号Sno，总学分TotalCredits)，用来保存学生已获得的总学分数，
#其中学号定义为学分表的主键，同时也是外键，被参照表为Students。
#（2）在选课表Reports上创建一个UPDATE触发器Trig_UpdRepGrade，
#要求：当修改某位学生的选课成绩Grade时，统计该名学生获得的总学分，并将结果保存到StuCredits表中
#① 当StuCredits表没有该位学生的学分信息时，在学分表中插入该位学生已获得的总学分；
#② 当StuCredits表已有该位学生的学分信息时，则更新学分表中该位学生已获得的总学分。
CREATE TABLE StuCredits(
Sno VARCHAR(10) PRIMARY KEY,
TotalCredits INT,
FOREIGN KEY(Sno) REFERENCES Students05(Sno)
);
DELIMITER //
CREATE TRIGGER Trig_UpdRepGrade_05
AFTER UPDATE
ON Reports05
FOR EACH ROW
BEGIN
DECLARE Total INT;
SELECT SUM(Ccredit)
INTO Total
FROM Reports05 AS R,Courses05 AS C
WHERE R.Cno=C.Cno AND R.Sno=NEW.Sno AND R.Grade>='60';
IF NOT EXISTS (SELECT * FROM StuCredits WHERE Sno=NEW.Sno) THEN
INSERT INTO StuCredits (Sno,TotalCredits) VALUES (NEW.Sno,Total);
ELSE
UPDATE StuCredits
SET TotalCredits=Total
WHERE Sno=NEW.Sno;
END IF;
END//
DELIMITER ;
#DROP TRIGGER Trig_UpdRepGrade_05;
#no.16 针对UPDATE触发器Trig_UpdRepGrade_学号最后两位，设计并写出以下两条SQL语句，并将执行结果截图，截图中写入学号后两位。
#（1）在Reports表中找到有选课成绩为空的学生学号，写出将该位学生的选课成绩改为76的更新语句，并将学分表中的结果截图；
#（2）在Reports表中将（1）中的学生的另一门选课的成绩做一下修改，如将另一门成绩为空的选课成绩设置为60分以上的成绩，
#或是将某一门不及格的成绩改为60分以上的成绩，或是将一门60分以上的成绩改为60分一下，写出对应的更新语句，并将学分表中的结果截图。
UPDATE Reports05
SET Grade='76'
WHERE Sno='2014112103' AND Cno='112p0023';
UPDATE Reports05
SET Grade='59'
WHERE Sno='2014112103' AND Cno='112p0015';
#no.3 创建一个视图，名称设置为view_StuInfoDno11_学号最后两位，用来查询学院编号为11的学生的所有信息，
#并且要求基于该视图进行更新操作时，仍需保证该视图只有该学院的学生。
#提交内容：（1）创建视图的语句；（2）查询该视图中女学生的学号，姓名，并将结果截图，图片的右下角写上学号的最后两位；
CREATE VIEW view_StuInfoDno11_05
AS
SELECT *
FROM Students05
WHERE SUBSTRING(Sno,5,2)='11'
WITH CHECK OPTION;
SELECT Sno,Sname
FROM view_StuInfoDno11_05
WHERE Sgender='女';
#no.4 通过视图view_StuInfoDno11_学号最后两位，分别插入以下两个学生的信息。
#提交内容：
#（1）写出插入学生张三信息的语句；
#（2）写出在学生表中查询张三信息的语句，并将执行结果截图，图片的右下角写上学号的最后两位；
#（3）写出插入学生李四信息的语句；
#（4）将执行结果截图，图片的右下角写上学号的最后两位；
#（5）分析两个插入语句的执行结果
INSERT INTO view_StuInfoDno11_05
VALUES('2022115199','张三','男','2004-06-13','广东潮州','11','1151','20221151');
SELECT *
FROM Students05
WHERE Sname='张三';
INSERT INTO view_StuInfoDno11_05
VALUES('2022210101','李四','女','2004-05-10','广东揭阳','21','2101','20222101');
#SELECT *
#FROM Students05
#WHERE Sname='李四';
#no.5 创建一个视图，名称设置为view_RepInfo_学号最后两位，用来查询学生的选课信息，结果显示学号，姓名，课程号，课程名和成绩。
#提交内容：（1）创建视图的语句；（2）查询该视图中学号为2014112104的学生的选课情况，并将结果截图，图片的右下角写上学号的最后两位；
CREATE VIEW view_RepInfo_05
AS
SELECT R.Sno,S.Sname,R.Cno,C.Cname,Grade
FROM Reports05 AS R,Students05 AS S,Courses05 AS C
WHERE R.Sno=S.Sno AND R.Cno=C.Cno
WITH CHECK OPTION;
#DROP VIEW view_RepInfo_05;
SELECT *
FROM view_RepInfo_05
WHERE Sno='2014112104';
#no.6 通过视图view_RepInfo_学号最后两位，将学生2016112105选修的课程112p0024的成绩修改为76分。
#提交内容：（1）写出修改成绩的语句；（2）将执行结果截图，图片的右下角写上学号的最后两位；（5）分析执行结果
UPDATE view_RepInfo_05
SET Grade='76'
WHERE Sno='2016112105' AND Cno='112p0024';
#no.7 创建一个视图，名称设置为view_StuCreditInfo_学号最后两位，用来查询学生已获学分的情况，结果显示学号，姓名和已获学分。
#提交内容：（1）创建视图的语句；（注意，已获学分需要给出字段别名）
#（2）查询该视图中学号为2015210101的学生已获学分情况，并将结果截图，图片的右下角写上学号的最后两位；
CREATE VIEW view_StuCreditInfo_05(Sno,Sname,CRE)
AS
SELECT S.Sno,Sname,SUM(Ccredit)
FROM Reports05 AS R,Courses05 AS C,Students05 AS S
WHERE R.Sno=S.Sno AND R.Cno=C.Cno AND R.Grade>='60'
GROUP BY S.Sno;
#DROP VIEW view_StuCreditInfo_05;
SELECT *
FROM view_StuCreditInfo_05
WHERE Sno='2015210101';
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
