#使用SQL语句，创建一个名为jxdb+学号后2位的教学管理数据库（如：学号后两位为01，则数据库名为jxdb01）。
#要求：（1）首先判断是否已存在该数据库，若不存在才创建，否则退出；（2）指定所使用的字符集为UFT8MB4。
#（1）按要求写出创建数据库的SQL语句；（2）使用SHOW CREATE DATABASE jxdb;查看数据库的创建信息，并将信息截图上传，要求尽可能将所有信息显示出来
CREATE DATABASE IF NOT EXISTS jxdb CHARACTER SET UTF8MB4;
USE jxdb;
# 使用SQL语句，在你所创建的教学管理数据库中，按照要求创建学院基本表，表名定义为Department+学号后2位，表中包含的字段及其对应的数据类型如下图所示。
#
# 要求：（1）完成字段名及其数据类型的定义；（2）声明主键及非空属性列；（3）给出字段名的中文注释
# 答案提交：（1）按要求写出创建基本表的SQL语句；（2）使用'SHOW CREATE TABLE 基本表名;'命令查看基本表的创建信息
CREATE TABLE Department
(
Dno CHAR(2) COMMENT '学院编号',
Dname VARCHAR(15) NOT NULL COMMENT '学院名称' ,
PRIMARY KEY (Dno)
);
SHOW CREATE TABLE Department;
#
# 使用SQL语句，在你所创建的教学管理数据库中，按照要求创建专业基本表，表名定义为Major+学号后2位，表中包含的字段及其对应的数据类型如下图所示。
#
# 要求：（1）完成字段名及其数据类型的定义；（2）声明主键及非空属性列；（3）给出字段名的中文注释
#
# 答案提交：（1）按要求写出创建基本表的SQL语句；（2）使用'SHOW CREATE TABLE 基本表名;'命令查看基本表的创建信息，
CREATE TABLE Major
(
Mno CHAR(4) comment '专业编号',
Mname VARCHAR (15) NOT NULL comment '专业名称',
Dno CHAR(2) comment '学院编号',
PRIMARY KEY (Mno)
);
Show CREATE TABLE Major;
#使用SQL语句，在你所创建的教学管理数据库中，按照要求创建学生基本表，表名定义为Students+学号后2位，表中包含的字段及其对应的数据类型如下图所示。
# 要求：（1）完成字段名及其数据类型的定义；（2）声明主键及非空属性列；（3）给出字段名的中文注释
# 答案提交：（1）按要求写出创建基本表的SQL语句；（2）使用'SHOW CREATE TABLE 基本表名;'命令查看基本表的创建信息，
CREATE TABLE Students(
Sno CHAR(10) comment '学号',
Sname varchar(4) not null comment '姓名',
Sgender ENUM('男','女')not null default '男' comment '性别',
Sage int comment '年龄',
Dno CHAR(2) comment'学院编号',
Mno CHAR(4) comment '专业编号',
Sclass CHAR(8) comment '班级编号',
primary key(Sno)
);
SHOW CREATE TABLE Students;
# 使用SQL语句，在你所创建的教学管理数据库中，按照要求创建教师基本表，表名定义为Teachers+学号后2位，表中包含的字段及其对应的数据类型如下图所示。
#
# 要求：（1）完成字段名及其数据类型的定义；（2）声明主键及非空属性列；（3）给出字段名的中文注释
#
# 答案提交：（1）按要求写出创建基本表的SQL语句；（2）使用'SHOW CREATE TABLE 基本表名;'命令查看基本表的创建信息
CREATE TABLE Teachers(
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 teachers;
# 使用SQL语句，在你所创建的教学管理数据库中，按照要求创建课程基本表，表名定义为Courses+学号后2位，表中包含的字段及其对应的数据类型如下图所示。
#
# 要求：（1）完成字段名及其数据类型的定义；（2）声明主键及非空属性列；（3）给出字段名的中文注释
#
# 答案提交：（1）按要求写出创建基本表的SQL语句；（2）使用'SHOW CREATE TABLE 基本表名;'命令查看基本表的创建信息
CREATE TABLE Courses(
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 Courses;
# 使用SQL语句，在你所创建的教学管理数据库中，按照要求创建选课基本表，表名定义为Reports+学号后2位，表中包含的字段及其对应的数据类型如下图所示。
#
# 要求：（1）完成字段名及其数据类型的定义；（2）声明主键及非空属性列；（3）给出字段名的中文注释
#
# 答案提交：（1）按要求写出创建基本表的SQL语句；（2）使用'SHOW CREATE TABLE 基本表名;'命令查看基本表的创建信息，
CREATE TABLE Reports(
Sno CHAR(10) comment '学号',
Cno CHAR(8) comment '课程号',
Racademicyear year not null comment '选课学年',
Rterm char(1) not null comment '选课学期',
Grade TINYINT comment '成绩',
primary key (Sno,Cno)
);
SHOW CREATE TABLE Reports;
#使用SQL语句，在你所创建的教学管理数据库中，按照要求创建授课基本表，表名定义为Tutors+学号后2位，表中包含的字段及其对应的数据类型如下图所示。
#要求：（1）完成字段名及其数据类型的定义；（2）声明主键及非空属性列；（3）给出字段名的中文注释
#答案提交：（1）按要求写出创建基本表的SQL语句；（2）使用'SHOW CREATE TABLE 基本表名;'命令查看基本表的创建信息
CREATE TABLE Tutors(
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 Reports;
#使用SQL语句，按照下面的要求修改学院基本表
#要求：（1）增加‘学院院长’字段，字段名为Dheader，数据类型为CHAR(4)
#答案提交：（1）按要求写出修改基本表的SQL语句；（2）使用'DESCRIBE 基本表名;'命令查看基本表的表结构并截图，
ALTER TABLE Department ADD Dheader CHAR(4) comment '学院院长';
DESCRIBE Department;
#使用SQL语句，按照下面的要求修改学生基本表
#要求：（1）将“年龄”字段Sage改为“出生日期”，同时字段名改为Sbirth，数据类型为DATE；
#（2）增加一个“生源地”字段，字段名为Snative，数据类型为VARCHAR(20)，将该字段放在出生日期Sbirth的后面
#答案提交：（1）按要求写出修改基本表的SQL语句；（2）使用'DESCRIBE 基本表名;'命令查看基本表的表结构并截图，并在右下角标注本人的学号后2位。
ALTER TABLE students
CHANGE Sage Sbirth DATE comment'出生日期';
ALTER TABLE students ADD Snative VARCHAR(20) after Sbirth;
DESCRIBE students;
#使用SQL语句，按照下面的要求修改课程基本表
#要求：（1）增加“先序课程编号”字段，字段名为Pre_Cno，数据类型为CHAR(8)，将该字段放在课程名Cname的后面
#答案提交：（1）按要求写出修改基本表的SQL语句；（2）使用'DESCRIBE 基本表名;'命令查看基本表的表结构并截图，并在右下角标注本人的学号后2位。
ALTER TABLE Courses ADD Pre_Cno CHAR(8) after Cname;
DESCRIBE Courses;
#使用SQL语句，按照下面的要求修改教师基本表
#要求：（1）增加“职称”字段，字段名为Tprof，数据类型为VARCHAR(8)，将该字段放在教师性别Tgender的后面;
#（2）增加“工资”字段，字段名为Tsal，数据类型为DECIMAL(10,2)；
#（3）增加“绩效”字段，字段名为Tcomm，数据类型为DECIMAL(10,2);
#答案提交：（1）按要求写出修改基本表的SQL语句；（2）使用'DESCRIBE 基本表名;'命令查看基本表的表结构并截图，并在右下角标注本人的学号后2位。
ALTER TABLE Teachers ADD Tprof VARCHAR(8) comment '职称'after Tgender;
ALTER TABLE Teachers ADD Tsal DECIMAL(10,2) comment '工资';
ALTER TABLE Teachers ADD Tcomm DECIMAL(10,2) comment'绩效';
DESCRIBE Teachers;
#使用SQL语句，创建学生基本表的索引。
#要求：（1）使用CREATE INDEX语句，在学生表的Mno列上按降序创建普通索引，索引名为IDX_Stu_Mno
#答案提交：（1）按要求写出创建索引的SQL语句；（2）使用'SHOW INDEX FROM 基本表名;'命令查看基本表的索引信息
DROP INDEX IDX_Stu_Mno ON students;
CREATE INDEX IDX_Stu_Mno ON Students(Mno DESC );
SHOW INDEX FROM students;
# 使用SQL语句，创建课程基本表的索引。
# 要求：（1）使用CREATE INDEX语句，在课程表的Cname列上按升序创建唯一索引，索引名为IDX_Cou_Cname
# 答案提交：（1）按要求写出创建索引的SQL语句；（2）使用'SHOW INDEX FROM 基本表名;'命令查看基本表的索引信息并
CREATE UNIQUE INDEX IDX_Cou_Cname ON Courses(Cname ASC ) ;
SHOW INDEX FROM Courses;
#使用SQL语句，创建选课基本表的索引。
#要求：（1）使用CREATE INDEX语句，在选课表的学年列上按降序，同时在学期列上按升序创建混合索引；
#答案提交：（1）按要求写出创建索引的SQL语句；（2）使用'SHOW INDEX FROM 基本表名;'命令查看基本表的索引信息
CREATE INDEX IDX_Rep_Ryear ON reports(Racademicyear DESC ,Rterm ASC );
SHOW INDEX FROM Reports;
#使用SQL语句，创建教师基本表的索引。
#要求：（1）使用ALTER TABLE语句，在教师表的职称列上按升序，同时在出生日期列上按降序创建混合索引
#答案提交：（1）按要求写出创建索引的SQL语句；（2）使用'SHOW INDEX FROM 基本表名;'命令查看基本表的索引信息
ALTER TABLE teachers ADD index IDX_Tea_Tprof(Tprof,Tbirth DESC );
SHOW INDEX FROM Teachers;
#使用SQL语句，创建授课基本表的索引。
#要求：（1）使用ALTER TABLE语句，在授课表的Tno列上按升序创建普通索引；
#（2）使用ALTER TABLE语句，在授课表的Sclass列上按降序创建普通索引
#答案提交：（1）按要求写出创建索引的SQL语句；（2）使用'SHOW INDEX FROM 基本表名;'命令查看基本表的索引信息
ALTER TABLE Tutors ADD INDEX IDX_Tut_Tno(Tno ASC );
ALTER TABLE Tutors ADD INDEX IDX_Tut_Scl(Sclass DESC );
SHOW INDEX FROM Tutors;
#使用SQL语句，删除教师基本表的指定索引。
#要求：（1）使用DROP INDEX命令，将教师表中在职称列和出生日期列上创建的索引删除
#答案提交：（1）按要求写出删除索引的SQL语句；（2）使用'SHOW INDEX FROM 基本表名;'命令查看基本表的索引信息
DROP INDEX IDX_Tea_Tprof on teachers;
#使用SQL语句，用ALTER TABLE语句对各基本表的外键进行声明。
#要求：（1）学院表Department，外键Dheader，参照Teachers表的Tno；
#（2）专业表Major，外键Dno，参照Department表的Dno；
# 3）学生表Students，外键Mno，参照Major表的Mno；
# （4）学生表Students，外键Dno，参照Department表的Dno；
# （5）教师表Teachers，外键Dno，参照Department表的Dno；
# （6）选课表Reports，外键Sno，参照Students表的Sno；
# （7）选课表Reports，外键Cno，参照Courses表的Cno；
# （8）课程表Courses，外键Pre_Cno，参照Courses表的Cno；
# （9）授课表Tutors，外键Tno，参照Teachers表的Tno；
# （10）授课表Tutors，外键Cno，参照Courses表的Cno
# 答案提交：（1）按要求选择其中三个外键写出对应的SQL语句即可，其余外键可自行添加
ALTER TABLE Department ADD CONSTRAINT FK_Dep_Dheader FOREIGN KEY (Dheader) REFERENCES Teachers (Tno);#运行不了
ALTER TABLE Major
ADD CONSTRAINT FK_Maj_Dno FOREIGN KEY (Dno) REFERENCES Department(Dno);
Alter table reports
add constraint FK_Sno FOREIGN KEY (Sno) REFERENCES students(Sno);
INSERT INTO Students
VALUES (2016130101,'张三','男',1998-04-01,'广东潮州',13,1301,20161301);#错误
INSERT INTO Students
VALUES (2016130101, '张三', '男', '1998-04-01', '广东潮州', 13, 1301, 20161301);
INSERT INTO Students
VALUES (2017120203,'张三','男','1999-03-12','山东荷泽','12',1202,20171202);
INSERT INTO courses
VALUES ('112p0037','DB_程序设计','112p0015',48,3);
#请写出下列查询操作的SQL语句：“查询课程表Courses中，所有课程的详细记录”
#将查询结果截图，截图的右下角要求标上你的学号最后两位
SELECT *
FROM Courses;
#请写出下列查询操作的SQL语句：“查询表中前6位学生的学号、姓名、出生日期以及入学年份，查询结果用中文显示列名”
SELECT Sno '学号',Sname'姓名', Students.Sbirth '出生日期',SUBSTRING(Sclass,1,4) '入学年份'
From students
LIMIT 6;
#请写出下列查询操作的SQL语句：“查询授课表Tutor中，教师编号为T009的教师都给哪些班级讲授过课程”
SELECT Tno,Sclass
FROM Tutors
where Tno='T009';
#请写出下列查询操作的SQL语句：“查询考试成绩有不及格的学生的学号”(记得函数去重)
SELECT DISTINCT Sno
FROM Reports
WHERE Grade<60;
#请写出下列查询操作的SQL语句：“查询教师T003在2015学年第1学期的授课情况”
SELECT *
FROM Tutors
where Tno='T003' and Tacademicyear=2015 and Tterm=1;
#请写出下列查询操作的SQL语句：“查询前序课程编号是'112p0015'或'112p0055'的课程编号和课程名”. 或者WHERE Pre_Cno IN ('112p0015', '112p0055');
SELECT Cno,Cname
FROM courses
where Courses.Pre_Cno='112p0015' or Courses.Pre_Cno='112p0055';
#请写出下列查询操作的SQL语句：“查询工资(Tsal)在3000元以上的教师的姓名、职称和工资，查询结果按照工资升序排序”(用order by)
SELECT Tno,Tprof,Tsal
FROM teachers
where
Teachers.Tsal>3000
ORDER BY Tsal ASC ;
#请写出下列查询操作的SQL语句：“查询院号为11或12的学生的学号、姓名、院号和专业号，结果按院号升序、专业号降序、姓名的汉语拼音升序排序”
SELECT Sno,Sname,Dno,Mno
FROM Students
where Dno in (11,12)
ORDER BY Dno ASC ,Mno DESC ,Sname ASC ;
#请写出下列查询操作的SQL语句：“查询课程名中包含了'计算机'或'原理'的课程的所有信息”
SELECT *
FROM Courses
#where Cname like '%计算机%' or '%原理%'; 错误
WHERE Cname LIKE '%计算机%' OR Cname LIKE '%原理%';
#请写出下列查询操作的SQL语句：“查询名字中第二个字为“一”的男学生的学号、姓名和出生年份，查询结果按学号降序排序”
SELECT Sno,Sname,YEAR(Sbirth) AS "BornYear"
FROM students
#where Sname like '%一%' and Sgender='男'，错误
WHERE Sname LIKE '_一%' AND Sgender='男'
ORDER BY Sno DESC ;
#请写出下列查询操作的SQL语句：“查询生源地既不在“广东潮州”,也不在“山东”的学生的学号、姓名及生源地”
SELECT Sno,Sname,Snative
FROM students
where
Students.Snative not in ('广东潮州','山东%');
#请写出下列查询操作的SQL语句：“查询学号为'2014112103'的学生在2015学年选修的各门课程的课程编号及成绩，查询结果按成绩降序排序”
SELECT Cno,Grade
FROM Reports
WHERE Sno=2014112103 and Racademicyear=2015
ORDER BY Grade DESC ;
#请写出下列查询操作的SQL语句：“查询2014级的学生在读大二时，都有哪些教师给他们上过课，结果显示班级编号、教师编号、课程号、学年和学期，并用中文显示属性列名”
SELECT Sclass'班级编号' ,Tno'教师编号', Cno'课程号',Tacademicyear'学年',Tterm'学期'
FROM Tutors
where LEFT (Sclass,4)=2014 and Tacademicyear=2015;
#请写出下列查询操作的SQL语句：“查询各门课程(Cno)的选课情况，结果显示课程编号、选课人数、平均分、最高分和最低分，查询结果按课程编号降序排序，
# 并用中文显示属性列名，平均分保留小数点后1位” #FORMAT(AVG(Grade),1)
SELECT Cno'课程编号',count(*) '选课人数',FORMAT(AVG(Grade),1)'平均分',MAX(Grade)'最高分',MIN(Grade)'最低分'
FROM Reports
GROUP BY Cno DESC ;
#请写出下列查询操作的SQL语句：“查询至少有5个'11'学院的学生选修的课程的课程编号、选修的人数和平均成绩，
# 选修人数和平均成绩只针对'11'学院的学生进行统计，并用中文显示属性列名，平均成绩保留小数点后1位”
# SELECT Cno'课程编号',count(*)'选修人数',FORMAT(avg(Grade),1)'平均成绩'
# FROM reports
# GROUP BY Cno
# # HAVING Cno='%11%';错误
# SELECT Cno'课程编号',count(*)'选修人数',FORMAT(avg(Grade),1)'平均成绩'
# FROM reports
# where Cno='%11%'
# GROUP BY Cno
# HAVING COUNT(*)>=5;错误
SELECT Cno'课程编号',count(*)'选修人数',FORMAT(avg(Grade),1)'平均成绩'
FROM reports
WHERE SUBSTRING(Sno,5,2)='11'
GROUP BY Cno
HAVING COUNT(*)>=5;
#请写出下列查询操作的SQL语句：“查询20141121班中各位学生(Sno)每个学年所修课程的平均分
# 结果显示学生的学号、学 年、课程平均分，结果按学号、学年升序排序，并用中文显示属性列名，平均分保留小数点后1位”
SELECT Sno'学号',Racademicyear'学年',FORMAT(avg(Grade),1)'课程平均分'
From reports
where SUBSTRING(Sno ,1,8)='20141121'
GROUP BY Racademicyear, Sno
ORDER BY Sno ASC ,Racademicyear ASC ;
#请写出下列查询操作的SQL语句：“查询各位老师(Tno)每个学年上的课程(Cno)门数，
# 结果显示教师编号、学年、所上课程门数,按教师编号升序排序，并用中文显示属性列名，分页显示第10条开始的后5条结果”
#不会分页查询
SELECT Tno '教师编号',Tacademicyear '学年',COUNT(DISTINCT Cno)'课程门'
FROM Tutors
GROUP BY Tno, Tacademicyear
ORDER BY Tno ASC
LIMIT 9,5;
#请写出下列查询操作的SQL语句：“查询2017学年至少上了两门课程的教师信息，
# 结果显示教师编号、学年、所上课程门数，按教师编号升序排序，并用中文显示属性列名”
SELECT Tno'教师编号',Tacademicyear'学年',count(DISTINCT Cno) '所上课程门数'
FROM Tutors
where Tacademicyear=2017
GROUP BY Tno, Tacademicyear
HAVING count(DISTINCT Cno)>=2
ORDER BY Tno ASC ;
#请写出下列查询操作的SQL语句：“查询至少有3位老师授课的课程，结果显示课程编号和授课人数，按授课人数降序排序，并用中文显示属性列名”
#记得去重
SELECT Cno'课程编号',COUNT(DISTINCT Tno)'授课人数'
From Tutors
GROUP BY Cno
HAVING count(Tno)>=3
ORDER BY COUNT(Tno) DESC ;
#请写出下列查询操作的SQL语句：“查询所有选修了课程号为112p0024并且有成绩的学生的考试情况，
# 结果要求显示学生的学号、课程号和成绩等级('优'、'良'、'中、'及格'和'不及格)，并按成绩降序排序(提示：CASE WHEN)”
# SELECT Sno,Cno
# FROM reports
# where Cno='112p0024'
# GROUP BY Grade,Sno
# HAVING
# CASE WHEN Grade>90 THEN '优'
#
#
# ORDER BY Grade DESC 错误
SELECT Sno,Cno,
CASE
WHEN Grade>90 THEN '优秀'
WHEN Grade>80 THEN '良'
WHEN Grade>70 THEN '中'
WHEN Grade>=60 THEN '及格'
ELSE '不及格'
END AS '成绩等级'
FROM reports
where Cno='112p0024' and Grade is not null
GROUP BY Grade,Sno
ORDER BY Grade DESC;
#请写出下列查询操作的SQL语句："查询20161151班的学生在大学一年级选修的课程情况，查询结果要显示学号(Sno)、姓名(Sname)
# 专业名(Mname)、学年(Racademicyear)、学期(Rterm)、选课的课程号(Cno)、选课的课程名称(Cname)及成绩（Grade)，并按照学号、学年、学期升序排序"
#多表连接
# SELECT Students.Sno,Sname,(DISTINCT Mname),Racademicyear,Rterm,Courses.Cno,Cname,Grade
# FROM reports,students,major,courses
# where Sclass='20161151' and Racademicyear=2016
# ORDER BY Sno ASC ,Racademicyear ASC,Rterm ASC ;错误
# SELECT Students.Sno,Sname, Mname,Racademicyear,Rterm,Courses.Cno,Cname,Grade
# FROM reports,students,major,courses
# where Sclass='20161151' and Racademicyear=2016 and Reports.Sno=Students.Sno and Students.Mno = Major.Mno
# ORDER BY Sno ASC ,Racademicyear ASC,Rterm ASC ;错误，
SELECT S.Sno, Sname, Mname, Racademicyear, Rterm, C.Cno, Cname, Grade
FROM Students S, Major M, Courses C, Reports R
WHERE S.Sno=R.Sno AND S.Mno=M.Mno AND C.Cno=R.Cno
AND Sclass='20161151' AND Racademicyear=2016
ORDER BY S.Sno,Racademicyear, Rterm;
#请写出下列查询操作的SQL语句："查询教师张雪的授课信息，查询结果显示教师编号(Tno)、姓名(Tname）、授课学年(Tacademicyear)、
# 授课学期(Tterm)、授课的课程编号(Cno)、课程名(Cname)以及授课班级(Sclass)，结果按授课学年、授课学期升序排序"
SELECT Teachers. Tno,Tname,Tacademicyear,Tterm,Courses. Cno,Cname,Sclass
FROM Teachers,Tutors,Courses
where Teachers.Tno= Tutors.Tno
and Tutors.Cno=Courses.Cno and Tname='张雪'
ORDER BY Tacademicyear ASC ,Tterm ASC ;
SELECT T.Tno, Tname,Tacademicyear,Tterm,C.Cno,Cname,Sclass
FROM Teachers T, Tutors Tut, Courses C
WHERE T.Tno=Tut.Tno AND Tut.Cno=C.Cno
AND Tname='张雪'
ORDER BY Tacademicyear,Tterm;
SELECT T.Tno, Tname,Tacademicyear,Tterm,C.Cno,Cname,Sclass
FROM Teachers T, Tutors Tut, Courses C
WHERE T.Tno=Tut.Tno AND Tut.Cno=C.Cno
AND Tname='张雪'
ORDER BY Tacademicyear,Tterm;
#请写出下列查询操作的SQL语句："查询计算机与信息工程学院的各个专业的学生数，查询结果显示专业号(Mno)、专业名称(Mname）、
# 学生人数，按专业号升序排序，并用中文名显示属性列"
# SELECT Major. Mno'专业号',Mname'专业名称',COUNT(DISTINCT Sno)'学生人数'
# From Major,Students,Department
# where Students.Mno=Major.Mno and Dname='计算机与信息工程学院'
# GROUP BY Sno,Major.Mno
# ORDER BY Major.Mno ASC ;# 错误
SELECT M.Mno 专业号, Mname 专业名称, COUNT(*) 学生人数
FROM Students S, Department D, Major M
WHERE S.Dno=D.Dno AND S.Mno=M.Mno
AND Dname='计算机与信息工程学院'
GROUP BY M.Mno, Mname
ORDER BY M.Mno;
#请写出下列查询操作的SQL语句："用自身连接查询和'王一凡'在同一个学院，且总工资（基本工资Tsal+岗位津贴Tcomm）
# 比'王一凡'高的教师的信息，结果显示教师编号(Tno)、姓名(Tname)和总工资，并用中文显示属性列名"
SELECT t1.Tno'教师编号',t1.Tname'姓名',(t1.Tsal+t1.Tcomm)'总工资'
From teachers t1,teachers t2
where t1.Dno=t2.Dno and t2.Tname='王一凡' and (t1.Tsal+t1.Tcomm)>(t2.Tsal+t2.Tcomm);
#请写出下列查询操作的SQL语句："用自身连接查询前序课程是数据结构的课程的信息，查询结果显示课程编号（Cno）、课程名称（Cname）和学分（Ccredit）"
SELECT c2.Cno'课程编号',c2.Cname'课程名称',c2.Ccredit'学分'
From Courses c1,courses c2
where c1.Cname = '数据结构' and c1.Cno=c2.Pre_Cno ;
#请写出下列查询操作的SQL语句："用自身连接查询既给20161121班上过课，也给20161151班上过课的教师信息，查询结果显示教师编号(Tno)、教师姓名(Tname)"
SELECT DISTINCT t1.Tno'教师编号',t2.Tname'教师姓名'
From tutors t1,teachers t2,tutors t3
where t1.Sclass = '20161121' and t3.Sclass='20161151' and t3.Tno=t1.Tno and t3.Tno = t2.Tno;
#请写出下列查询操作的SQL语句："查询课程表中的所有课程在每个学期的选课情况，查询结果以中文名显示课程编号(Cno)、课程名称(Cname)、
# 选课学年(Racademicyear)、选课学期(Rterm)及该学年该学期的选课人数(SelNumber)，按课程编号、选课学年、选课学期升序排序"
SELECT Courses. Cno'课程编号',Cname'课程名称',Racademicyear'选课学年',Rterm'选课学期',count(*)'选课人数'
From courses,reports
where Courses.Cno=Reports.Cno
GROUP BY Courses. Cno, Cname, Racademicyear, Rterm
ORDER BY Courses.Cno ASC ,Racademicyear ASC ,Rterm ASC ;
#请写出下列查询操作的SQL语句："查询教师表中的每一位数学与统计学院的教师的授课课情况，查询结果显示教师编号(Tno)、
# 教师姓名(Tname)、授课学年(Tacademicyear)、授课学期(Tterm)、课程编号(Cno)及课程名称(Cname)，按教师编号、开课学年、授课学期升序排序"
SELECT Tutors.Tno,Tname,Tacademicyear,Tterm,Courses.Cno,Cname
FROM tutors,teachers,courses,department
where Teachers.Dno=Department.Dno and Department.Dname='数学与统计学院' and Tutors.Tno=Teachers.Tno and Tutors.Cno=Courses.Cno
ORDER BY Tno,Tacademicyear,Tterm;
SELECT Tutors.Tno,Tname,Tacademicyear,Tterm,C.Cno,Cname
FROM tutors RIGHT JOIN Courses C on Tutors.Cno = C.Cno
RIGHT JOIN Teachers T on Tutors.Tno = T.Tno
RIGHT JOIN Department D on T.Dno = D.Dno
where D.Dname='数学与统计学院'
ORDER BY Tno,Tacademicyear,Tterm;
SELECT T.Tno, Tname,Tacademicyear, Tterm, Tut.Cno, Cname
FROM Department D
INNER JOIN Teachers T ON T.Dno=D.Dno
LEFT JOIN Tutors Tut ON T.Tno=Tut.Tno
LEFT JOIN Courses C ON Tut.Cno=C.Cno
WHERE Dname='数学与统计学院';
#请写出下列查询操作的SQL语句："使用谓词IN嵌套查询，查询有课程成绩不及格的学生的信息，结果显示学号(Sno)、姓名(Sname)及所在班级(Sclass)"
SELECT DISTINCT Students.Sno,Sname,Sclass
FROM Students
inner join reports r on Students.Sno = r.Sno
where Grade in
(SELECT Grade
FROM Reports
where Reports.Grade <60);
#请写出下列查询操作的SQL语句："使用谓词IN嵌套查询，查询在2014学年有授课的教师的信息，结果显示教师编号(Tno)、教师姓名(Tname)及职称(Tprof)"
SELECT Tno,Tname,Tprof
FROM teachers
where Tno IN (
SELECT Tno
FROM tutors
where Tacademicyear=2014
);
#请写出下列查询操作的SQL语句："使用嵌套查询，查询计算机与信息工程学院中总工资高于本学院的平均总工资的教师信息
# # 结果以中文名称显示教师编号(Tno)、教师姓名(Tname)、职称（Tprof）及总工资"
# SELECT Tno, Tname,Tprof
# FROM teachers
# where Teachers.Tno in
# (SELECT Tno
# FROM teachers
# GROUP BY Tno
# HAVING Teachers.Tprof + Teachers.Tcomm > AVG(Teachers.Tprof + Teachers.Tcomm))
# and Dno in
# (SELECT Dno
# FROM Department
# where Dname='计算机与信息工程学院');
SELECT Tno AS "教师编号", Tname AS "教师姓名", Tprof AS "职称", Tsal+Tcomm AS "总工资"
FROM Teachers T, Department D
WHERE T.Dno=D.Dno AND Dname='计算机与信息工程学院'
AND (Tsal+Tcomm)>
( SELECT AVG(Tsal+Tcomm)
FROM Teachers
WHERE Dno=
( SELECT Dno
FROM Department
WHERE Dname='计算机与信息工程学院'
));
#请写出下列查询操作的SQL语句："使用嵌套查询，查询总工资高于所有学院的平均总工资的教师信息，结果显示教师编号(Tno)、
# # 教师姓名(Tname)、学院名称(Dname)、职称(Tprof)及总工资，并用中文显示属性列名"
# SELECT Tno'教师编号',Tname'教师姓名',Dname'学院名称',Tprof'职称',Teachers.Tsal+Teachers.Tcomm'总工资'
# FROM teachers,Department
# where Teachers.Tprof+Teachers.Tcomm>(
# SELECT AVG(Teachers.Tcomm+Teachers.Tprof)
# FROM teachers
# )AND Teachers.Dno=Department.Dno; c
SELECT Tno AS "教师编号", Tname AS "教师姓名", Dname AS "学院名称", Tprof AS "职称", Tsal+Tcomm AS "总工资"
FROM Teachers T, Department D
WHERE T.Dno=D.Dno
AND (Tsal+Tcomm)> ALL
( SELECT AVG(Tsal+Tcomm)
FROM Teachers
GROUP BY Dno);
#子查询 AVG(Tsal+Tcomm) 返回每个学院的教师工资平均值，而外部查询则找出了那些工资高于所有学院平均工资的教师。
# 这样使用 ALL 可以对比每个学院平均工资，确保选出的教师工资高于所有学院的平均水平。
#请写出下列查询操作的SQL语句："使用嵌套查询，查询已获学分高于2014级所有计算机与信息工程学院的学生获得的学分的学生学号(Sno)、
# 姓名(Sname)及已获得的总学分，并用中文显示属性列名"（提示：① 已获学分是指已有成绩且成绩大于等于60分的课程的学分；
# ② 先统计出计算机与信息工程学院的每位学生获得的学分；③ 统计的每位学生获得的学分，再筛选出满足条件的学生）
SELECT Sno'学号',Sname'姓名',count(Courses.Ccredit)'总学分'
from Students,courses
where count(Courses.Ccredit)>ALL(
SELECT count(Ccredit)
FROM department,Courses,students
where Dname='计算机与信息工程学院' and LEFT(Sclass,4)=2014
and Students.Dno=Department.Dno
GROUP BY Sno
)
GROUP BY Sno, Sname; #错误 #难
SELECT Students.Sno'学号',Sname'姓名',SUM(Courses.Ccredit)'总学分'
from Students,courses,reports
where Students.Sno=Reports.Sno and Reports.Cno=Courses.Cno and
Reports.Grade>=60
GROUP BY Students.Sno,Students. Sname
HAVING SUM(
Courses.Ccredit
)>ALL(
SELECT SUM(Ccredit)
FROM courses,reports,students
where Students.Sno=Reports.Sno and Reports.Cno=Courses.Cno and LEFT(Reports.Sno,4)='2014'
and Dno=(
select Dno
from department
where
Dname='计算机与信息工程学院'
)
GROUP BY Reports.Sno
) ;
#请写出下列查询操作的SQL语句："使用EXISTS谓词查询计算机与信息工程学院中没有讲授过课程号为112p0054课程的教师编号(Tno)、姓名(Tname)和职称(Tprof)"
SELECT Teachers.Tno'教师编号',Tname'姓名',Tprof'职称'
From teachers inner join tutors t on Teachers.Tno = t.Tno
inner join Courses C on t.Cno = C.Cno
inner join department d on Teachers.Dno = d.Dno
where d.Dname='计算计与信息工程学院' and not exists(
SELECT *
from tutors
where Cno='112p0054'
);
#错误，WHERE 子句中 not exists 子查询没有相关的条件限制，应该关联到外部查询中的教师表。内部子查询应该与外部查询相关联，
# 检查是否在计算机与信息工程学院中的教师未授课号为 '112p0054' 的课程。以下两种
SELECT Teachers.Tno AS '教师编号', Teachers.Tname AS '姓名', Teachers.Tprof AS '职称'
FROM Teachers
INNER JOIN Department D ON Teachers.Dno = D.Dno
WHERE D.Dname = '计算机与信息工程学院'
AND NOT EXISTS (
SELECT *
FROM Tutors T
WHERE Cno = '112p0054' AND T.Tno = Teachers.Tno
);
SELECT Tno, Tname, Tprof
FROM Teachers T
WHERE Dno=
( SELECT Dno FROM Department
WHERE Dname='计算机与信息工程学院')
AND NOT EXISTS
(SELECT * FROM Tutors Tut
WHERE Tut.Tno=T.Tno AND Tut.Cno='112p0054');
# #请写出下列查询操作的SQL语句："使用EXISTS谓语查询至少选修了学生2014112104选修的全部课程的学生的学号(Sno)、姓名(Sname)"
# SELECT Sno,Sname
# FROM Students
# where exists (
# SELECT Cno
# FROM reports
# where Sno=2014112104
# )
SELECT S.Sno, Sname
FROM Students S
WHERE S.Sno<>'2014112104'
AND NOT EXISTS
(SELECT * FROM Reports Rep
WHERE Sno='2014112104'
AND NOT EXISTS
(SELECT * FROM Reports
WHERE S.Sno=Sno AND Rep.Cno=Cno)
);
#请写出下列查询操作的SQL语句："使用嵌套子关系查询，查询课程表中每一门课程的选课情况，查询结果要显示课程编号(Cno)、课程名称(Cname)、
# 选课人数、平均分、最高分、最低分。若某门课程还没有被选，则要选课人数显示为‘尚无人选’,平均分等显示为NULL；若某门课程有人选，但平均分等结果为NULL，
# 则显示为’尚无成绩’；非空的数据结果保留两位小数。结果按课程编号升序排序，并用中文显示属性列名"
#提示：① 本查询会使用到COALESCE()函数、FORMAT()函数；② 本查询需要使用CASE WHEN语句；③先统计出每门课程的选修情况，再与课程表建立左连接
# SELECT Courses.Cno,Cname,
# (CASE WHEN count(Sno)='0' THEN '尚无人选' ELSE count(Sno) end) AS '选课人数',
# FORMAT(AVG(Grade),1),MAX(Grade),MIN(Grade)
# FROM courses
# inner join Reports R on Courses.Cno = R.Cno
# GROUP BY Courses.Cno, Cname;
# 难度 5颗星星
SELECT C.Cno '课程号', Cname '课程名',
(CASE WHEN SelNum IS NULL THEN '尚未选'
ELSE CAST(SelNum AS CHAR) END) '选课人数',
(COALESCE(CAST(FORMAT(AvgGrd,2) AS CHAR),'尚无成绩')) '平均分',
(COALESCE(CAST(FORMAT(MaxGrd,2) AS CHAR),'尚无成绩')) '最高分',
(COALESCE(CAST(FORMAT(MinGrd,2) AS CHAR),'尚无成绩')) '最低分'
FROM Courses C LEFT JOIN
(SELECT Cno, COUNT(*) 'SelNum', AVG(Grade) 'AvgGrd',
MAX(Grade) 'MaxGrd', MIN(Grade) 'MinGrd'
FROM Reports
GROUP BY Cno) R
ON C.Cno=R.Cno
ORDER BY C.Cno;
#请写出下列查询操作的SQL语句："使用嵌套子关系查询，查询在2015学年的第1学期，选修了刘伟老师上的计算机科学概论课程的学生的基本信息，
# 查询结果显示所在班级(Sclass)、学号(Sno)、姓名(Sname）、选修的课程编号(Cno)、课程名(Cname)以及授课的教师(Tname)"
# SELECT DISTINCT Students.Sclass'班级', Students.Sno'学号',Sname'姓名',C.Cno'选修得到课程编号',Cname'课程名',Tname'教师名'
# From Students
# inner join reports r on Students.Sno = r.Sno
# inner join Courses C on r.Cno = C.Cno
# inner join tutors t on C.Cno = t.Cno
# inner join teachers t2 on Students.Dno = t2.Dno
# where Students.Sclass in (
# SELECT t2.Sclass
# From tutors t2
#
# where t2.Tno=t.Tno and Tacademicyear=2014 and Tterm=1 and Tname='刘伟' and C.Cno in
# (
# SELECT Cno
# FROM Courses
# where Cname='计算机科学概论'
# )
# ); 错误
SELECT Tut.Sclass, Sno, Sname, T.Tno, Tname
FROM Tutors Tut, Teachers T, Courses C,
( SELECT S.Sno, Sname, Sclass
FROM Reports R, Courses C, Students S
WHERE R.Cno=C.Cno AND R.Sno=S.Sno
AND Racademicyear=2015 AND Rterm=1
AND Cname='计算机科学概论'
) R
WHERE Tut.Tno=T.Tno AND Tut.Cno=C.Cno AND R.Sclass=Tut.Sclass
AND Tacademicyear=2015 AND Tterm=1
AND Cname='计算机科学概论' AND Tname='刘伟';
#请写出下列查询操作的SQL语句："使用嵌套子关系查询，查询选修了数据结构，且其成绩不低于本年级所有选修了该门课程的学生的平均成绩的学生信息，
# 查询结果以中文名称显示学生所在年级、学号(Sno)、姓名(Sname)及其选修的该门课程的成绩(Grade)"
SELECT LEFT(Sclass,4)'所在年级',Students.Sno,Sname,R.Grade
FROM Students,(
SELECT Grade,Sno,Cno
FROM reports
where Grade>ALL(
SELECT AVG(Grade)
FROM reports
where Cno in(
Select Cno
FROM courses
where Cname='数据结构'
)
)
) R
where Students.Sno=R.Sno and Cno in(
Select Cno
FROM courses
where Cname='数据结构'
);
#请写出下列操作的SQL语句："在学院表中增加一个新的学院，外国语学院，学院编号为15，院长暂时不设置。"
INSERT INTO Department(Dname,Dno)
VALUES ('外国语学院',15);
#请写出下列操作的SQL语句："学号为2014112103的学生的数据结构课程的补考成绩为63分，请用补考成绩更新原有的考试成绩。"
UPDATE reports
SET Grade=63
where Sno=2014112103 and Cno in(
SELECT Cno
from courses
where Cname='数据结构'
);
#请写出下列操作的SQL语句："为表Tutors增加新列Tutid，将该列设置为第一列，并将Tutid列设置为自动增长的整数列，同时设置该列为主键”(注意)
ALTER TABLE Tutors
ADD Tutid INT PRIMARY KEY AUTO_INCREMENT FIRST ;
#请写出下列操作的SQL语句："2018学年第1学期，为20171151班的所有学生开设了计算机网络课程，并由“许永军”教授进行授课"。
#提示：（1）以上操作需要对多个数据表进行更新操作，所用到的数据必须是根据要求中提供的数据完成；（2）某一个属性列的取值可以由子查询获得
INSERT INTO tutors(tno, cno, tacademicyear, tterm, sclass)
VALUES (
(select tno from teachers where tname='许永军'),
(SELECT cno from Courses where Cname='计算机网络'),
2018,1,20171151
);
INSERT INTO reports(Sno, Cno, Racademicyear, Rterm)
values (
(select sno from students where Sclass='20171151'),
(SELECT cno from Courses where Cname='计算机网络'),
2018,1
);
#请写出下列操作的SQL语句："李桂清教授光荣退休了，需要将他在2018学年第2学期给20161121班的学生所上的数字图像制作课程将改由李力授课"。
# 提示：（1）所用到的数据必须是根据要求中提供的数据完成；（2）某一个属性列的取值可以由子查询获得
update tutors
set Tno = (select tno
from teachers
where tname='李力')
where cno in (
select Cno
from courses
where Cname='数字图像制作'
) and Tacademicyear=2018 and Tterm=2 and Sclass=20161121 and tno in (select tno from teachers where tname='李桂清');
#请写出下列操作的SQL语句："学号为2017120202的学生因病退学了，请从学生表中将他的信息删除"。① 能删除成功吗？② 若删除失败，请说明原因；若删除成功，请用SELECT语句在学生表中查询该位学生的相关信息
#请写出下列操作的SQL语句："① 将选课表中对Sno的外键声明重新设置可为级联删除；② 再从学生表中将学号2017120202的学生信息删除"。
ALTER TABLE reports
DROP FOREIGN KEY FK_Sno;
ALTER TABLE reports
ADD CONSTRAINT FK_Sno FOREIGN KEY(Sno)
REFERENCES students(Sno)
ON DELETE CASCADE;
DELETE FROM Students
WHERE Sno='2017120202';
#请写出下列操作的SQL语句："由于人才培养方案的变更，数学与统计学院的数据科学与大数据技术（非师范）专业的编号将变更为编号1203，
# 请直接将专业表中的专业编号1202更改为1203的信息"。
#① 能更新成功吗？② 若更新失败，请说明原因；若更新成功，请用SELECT语句在专业表中查询数据科学与大数据技术（非师范）专业的相关信息
Update major
set Mno=1202
where Mno='1203' and Mname='数据科学与大数据技术（非师范）';#更新失败，违法了完整性约束
select *
from major
where Mno=1202;
ALTER TABLE students
DROP FOREIGN KEY FK_Mno;
ALTER TABLE students
ADD CONSTRAINT FK_Mno FOREIGN KEY(Mno)
REFERENCES major(Mno);
#请写出下列操作的SQL语句："分别任命李力、张兰和张雪为本学院的院长"。要求：使用一条更新语句完成次操作。
#根据要求完成操作，并将操作结果截图（可用SELECT语句查询更新后的结果），截图的右下角要求标上你的学号最后两位
#提示：（1）所用到的数据必须是根据要求中提供的数据完成；（2）可以先用子关系查询获得三位教师的教师编号和所在学院编号，
# 然后再用关联子关系的更新语句修改Department中的数据。
UPDATE Department
set Dheader = (
select Tno
from teachers
where Tname='李力'
)
where Dno=(
select Dno
from teachers
where Tname='李力'
); #弊端，只能针对一个人的
UPDATE Department D,
( SELECT Dno, Tno
FROM Teachers
WHERE Tname IN ('李力', '张兰', '张雪')
) DH
SET Dheader=DH.Tno
WHERE D.Dno=DH.Dno;
#请写出下列操作的SQL语句："创建一个教师工作量统计表TeaQuantity，其中包含统计编号Qid、教师编号Tno、学年Tacademicyear和工作量Tquantity，
# 其中Tno和Tacademicyear的数据类型与Tutors表中的设置相同，Qid的数据类型定义为Binary(16)，默认输入UNIQUEINDETIFIER(UUID)的二进制数，
# 且设置为该表的主键，Tquantity的数据类型设置为整数，并且在属性列Tno和Tacademicyear上设置唯一性约束，即约束该表中每个教师编号，
# 每个学年只统计一个工作量"
Create TABLE TeaQuantity
(
Qid Binary(16) ,
Tno CHAR(4) not null comment '教师编号',
Tacademicyear year not null comment '授课学年',
Tquantity int comment '工作量',
primary key (Qid),
CONSTRAINT UK_Tno UNIQUE KEY(Tno,Tacademicyear)
);
DELIMITER //
CREATE TRIGGER generate_uuid
BEFORE INSERT ON teaquantity FOR EACH ROW
BEGIN
SET NEW.Qid = UNHEX(REPLACE(UUID(), '-', ''));
END //
DELIMITER ;
#请写出下列操作的SQL语句："对所有教师的授课工作量按学年进行统计，即统计出每个学年该位教师所上课程的总课时量，并将统计结果添加到TeaQuantity表中"。
#请根据要求完成操作，并将操作结果截图（将TeaQuantity表中编号为'T004'的教师的工作量的查询结果截图），截图的右下角要求标上你的学号最后两位
INSERT INTO TeaQuantity(Tno, Tacademicyear, Tquantity)
(SELECT Tno,Tacademicyear,SUM(Chours)
FROM Tutors Tut,Courses C
WHERE Tut.Cno=C.Cno
GROUP BY Tno,Tacademicyear);
select * from teaquantity where tno='T004';
#请写出下列操作的SQL语句："① 2019学年第1学期，为20171202班的所有学生开设了数据库系统原理，并由“王平”老师进行授课；
# 重新对“王平”老师的授课工作量按学年进行统计，再次将统计结果添加到TeaQuantity表中"。
#① 第一步操作与前面的操作类似，需对多个表完成操作；② “将统计结果添加到TeaQuantity表中”，指的是执行数据插入语句
insert into tutors(Tno, Cno, Tacademicyear, Tterm, Sclass)
(SELECT Tno,Cno,2019,1,20171202
from tutors
where tno in(
select tno
from teachers
where tname='王平'
) and cno in(
select cno
from courses
where Cname='数据库系统原理'
));
#① 添加授课记录
INSERT INTO Tutors(Tno,Cno,Tacademicyear,Tterm,Sclass)
VALUES
((SELECT Tno FROM Teachers WHERE Tname='王平'),
(SELECT Cno FROM Courses WHERE Cname='数据库系统原理'),
2019,1,'20171202');
-- ② 再插入选课信息
INSERT INTO Reports(Sno,Cno,Racademicyear,Rterm)
(SELECT Sno,Cno,2019,1
FROM Students, Courses
WHERE Sclass='20171202' AND Cname='数据库系统原理');
-- ③ 插入授课工作量统计表
INSERT INTO TeaQuantity(Tno,Tacademicyear,Tquantity)
(SELECT Tut.Tno,Tacademicyear,SUM(Chours)
FROM Tutors Tut,Courses C, Teachers T
WHERE Tut.Cno=C.Cno AND T.Tno=Tut.Tno AND Tname='王平'
GROUP BY Tno,Tacademicyear);
-- 请写出下列操作的SQL语句："重新对“王平”老师的授课工作量按学年进行统计，并将统计结果更新到TeaQuantity表中"。
-- 请根据要求完成操作，并将操作结果截图（用SELECT语句在TeaQuantity表中查询王平的工作量），
UPDATE TeaQuantity,
(SELECT Tut.Tno,Tacademicyear,SUM(Chours) SumofQuantity
FROM Tutors Tut,Courses C, Teachers T
WHERE Tut.Cno=C.Cno AND T.Tno=Tut.Tno AND Tname='王平'
GROUP BY Tno,Tacademicyear) TQ
SET TeaQuantity.Tquantity= TQ.SumofQuantity
WHERE TeaQuantity.Tno=TQ.Tno AND TeaQuantity.Tacademicyear=TQ.Tacademicyear;
#请写出下列操作的SQL语句："① 在学生表中增加CHECK约束：Sno的长度必须为10个字符长度；
# ② 分别向学生表中添加一条正确的学生信息和一条错误的学生信息，验证CHECK约束是否能正确执行。"
ALTER TABLE Students
ADD CONSTRAINT CHK_LenSno CHECK (CHAR_LENGTH(Sno)=10);
INSERT INTO Students(Sno,Sname)
VALUES ('202311','李四');
#请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuRepInfo_学号最后两位.利用学生学号查询该名学生各学期的选修课程情况，
# 要求显示学生的学号，姓名，选修的学年，学期，选修的课程号，课程名及其成绩，显示结果按学年和学期升序排列。
delimiter //
create procedure PROC_StuRepInfo_
(in sid char(10))
begin
select s.sno, sname,Racademicyear,Rterm, c.Cno,Cname,Grade
from students s
INNER JOIN reports r ON s.Sno = r.Sno
INNER JOIN Courses c ON r.Cno = c.Cno
where s.sno=sid
ORDER BY sno ,cno;
end//
CALL PROC_StuRepInfo_('2015112101');
#请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuGrade_学号最后两位
#查询某位学生指定课程的成绩和可获得这门课程的学分
#（注：若该学生的课程成绩小于60分或是为空，则学分要显示为0分）
delimiter //
Create procedure PROC_StuGrade(in ssname char(10),ccname varchar(20))
begin
select sname,Cname,Grade,
(
CASE when Grade<60 THEN '0'
when Grade is null THEN '0'
ELSE Ccredit
END
) AS "Credit"
from courses
inner join reports r on Courses.Cno = r.Cno
INNER JOIN students s on r.Sno = s.Sno
where sname=ssname and Cname=ccname;
end //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE PROC_StuGrade(
SN VARCHAR(4),CN VARCHAR(20))
BEGIN
SELECT S.Sno,Sname,Cname,Grade,
(CASE WHEN Grade<60 OR Grade IS NULL THEN 0
ELSE Ccredit
END) AS "Credit"
FROM Students S INNER JOIN Reports R ON S.Sno=R.Sno
INNER JOIN Courses C ON R.Cno=C.Cno
WHERE S.Sname=SN AND C.Cname=CN;
END //
DELIMITER ;
#查询'张建国'选修的'数据库系统原理'课程的成绩和学分
#（2）查询'朱玉玲'选修的'计算机科学概论'课程的成绩和学分
CALL PROC_StuGrade('张建国','计算机科学概论');
#请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_DepNativeInfo.统计指定入学年级指定学院名中，
# 各省的生源人数，要求显示入学年级，学院名，生源地（只显示省份）及生源人数
DELIMITER //
CREATE PROCEDURE PROC_DepNativeInfo(
StuGrd CHAR(4), DN VARCHAR(15))
BEGIN
SELECT LEFT(Sno,4) AS "入学年级", Dname AS "学院名", LEFT(Snative,2) AS "省份", COUNT(Sno) AS "生源数"
FROM Students S, Department D
WHERE S.Dno=D.Dno AND LEFT(Sno,4)=StuGrd
AND Dname=DN
GROUP BY LEFT(Snative,2),LEFT(Sno,4),Dname;
END //
DELIMITER ;
#请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_StuGradeNum_学号最后两位
#统计指定班级指定课程的考试情况，要求显示出不同分值段（10分为一个分值段）以及尚未考试的学生人数，如（100~90,2人；89~80,7人；…未考,2人）。
CALL PROC_DepNativeInfo('2014','计算机与信息工程学院');
DELIMITER //
CREATE PROCEDURE PROC_StuGradeNum(
CLS CHAR(8), CN VARCHAR(20))
BEGIN
SELECT (CASE
WHEN Grade BETWEEN 90 AND 100 THEN '90~100'
WHEN Grade BETWEEN 80 AND 89 THEN '80~89'
WHEN Grade BETWEEN 70 AND 79 THEN '70~79'
WHEN Grade BETWEEN 60 AND 69 THEN '60~69'
WHEN Grade BETWEEN 0 AND 59 THEN '0~59'
ELSE '未考'
END) AS "成绩分段", COUNT(Sno) AS "分段人数"
FROM Reports R,Courses C
WHERE R.Cno=C.Cno AND LEFT(Sno,8)=CLS AND Cname=CN
GROUP BY (CASE
WHEN Grade BETWEEN 90 AND 100 THEN '90~100'
WHEN Grade BETWEEN 80 AND 89 THEN '80~89'
WHEN Grade BETWEEN 70 AND 79 THEN '70~79'
WHEN Grade BETWEEN 60 AND 69 THEN '60~69'
WHEN Grade BETWEEN 0 AND 59 THEN '0~59'
ELSE '未考'
END);
END //
DELIMITER ;
#存储过程名命名为PROC_StuCreditInfo_学号最后两位
#利用学生学号查询该名学生在指定学年，指定学期所获得的学分数以及还未获得的学分数，若该门课程尚无成绩或成绩小于60分，
# 则为尚未获得的学分，结果以变量形式输出（OUTPUT）
DELIMITER //
CREATE PROCEDURE PROC_StuCreditInfo(SN CHAR(10), ADyear INT, TM INT, OUT GetCredits INT, OUT NotGetCredits INT)
BEGIN
SELECT SUM(Ccredit)
FROM Reports R, Courses C
WHERE R.Cno=C.Cno AND Sno=SN AND Racademicyear=ADyear
AND Rterm=TM AND Grade>=60
INTO GetCredits;
SELECT SUM(Ccredit)
FROM Reports R, Courses C
WHERE R.Cno=C.Cno AND Sno=SN AND Racademicyear=ADyear
AND Rterm=TM AND (Grade<60 OR Grade IS NULL)
INTO NotGetCredits;
END //
DELIMITER ;
#请写出下列创建存储过程的MySQL语句，存储过程名命名为PROC_TeaWork_学号最后两位
#统计指定教师指定学年的教学工作量(教学工作量=所有教授的课程学时数的总和)，教学工作量要求作为输出参数（OUT）
DELIMITER //
CREATE PROCEDURE PROC_TeaWork(TN VARCHAR(4), ADyear INT, OUT CouHours INT)
BEGIN
SELECT SUM(Chours)
FROM Teachers T,Tutors TC,Courses C
WHERE T.Tno=TC.Tno AND TC.Cno=C.Cno
AND Tname=TN AND Tacademicyear=ADyear;
END //
DELIMITER ;
CALL PROC_TeaWork('刘伟','2018',@work);
#“在Students上创建一个UPDATE触发器Trig_UpdStuBirth_学号最后两位，要求：当更新学生的出生日期时，检查此学生的入学年龄是否是在14~40岁之间，
# 若是则允许更新，若不是则提示错误信息‘学生的出生日期有误，请确认后重新输入！’。”
DELIMITER //
CREATE TRIGGER Trig_UpdStuBirth
BEFORE UPDATE ON Students
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(200);
IF (NEW.Sbirth!=OLD.Sbirth) THEN
IF ( (CONVERT(LEFT(NEW.Sno,4),UNSIGNED)-YEAR(NEW.Sbirth)) NOT BETWEEN 14 AND 40)
THEN
SET msg='学生的出生日期有误,请确认后重新输入!';
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;
END IF;
END IF;
END //
DELIMITER ;
数据库学习题目从头到位代码
最新推荐文章于 2024-06-13 09:00:30 发布
1847
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
