修改成性别查询
例题:
#请写出下列创建存储过程的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分)
请写出下列调用存储过程PROC_StuGrade_学号最后两位的MySQL语句,
(1)查询'张建国'选修的'数据库系统原理'课程的成绩和学分
(2)查询'朱玉玲'选修的'计算机科学概论'课程的成绩和学分
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 ;
CALL Proc_StuGrade('张建国','数据库系统原理');
CALL Proc_StuGrade('朱玉玲','计算机科学概论');
#请写出下列创建存储过程的MySQL语句,存储过程名命名为PROC_DepNativeInfo.统计指定入学年级指定学院名中, # 各省的生源人数,要求显示入学年级,学院名,生源地(只显示省份)及生源人数 请写出下列调用存储过程PROC_DepNativeInfo最后两位的MySQL语句, 查询2014级计算机与信息工程学院的生源情况
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 ;
CALL PROC_DepNativeInfo('2014','计算机与信息工程学院');
存储过程名命名为PROC_StuGradeNum_学号最后两位
统计指定班级指定课程的考试情况,要求显示出不同分值段(10分为一个分值段)以及尚未考试的学生人数,如(100~90,2人;89~80,7人;…未考,2人)
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 ;
带输出存储过程
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 ;
#查询验证
SET @GetCredit=0,@NotGetCredit=0;
CALL PROC_StuCreditInfo('2016115101',2017,2,@GetCredit,@NotGetCredit);
SELECT '2016115101' AS "学号",2017 AS "学年", 2 AS "学期",
IFNULL(@GetCredit,0) AS "已获学分", IFNULL(@NotGetCredit,0) AS "未获学分";