-- 1.创建带参数的存储过程,根据课程名统计选修该门课程的学生学号、姓名和成绩,并按照成绩降序,学号升序
delimiter //
create procedure pro01(a varchar(60))
select student.Sno as 学号,student.Sname as 姓名,SC.Score as 成绩
from student inner join SC on student.Sno=SC.Sno
group by 学号
order by 成绩 desc,学号 asc;
end//
call pro01('数据库基础')
-- 2.创建带参数的存储过程,如果该门课程的平均分大于85,则显示'课程名+课程成绩较好',否则显示'课程名+课程成绩一般' 如参数为'数据库基础' 则显示'数据库基础课程成绩较好'或'数据库基础课程成绩一般'
delimiter //
CREATE PROCEDURE pro02(a varchar(60))
BEGIN
SELECT AVG(SC.Score) INTO a
FROM Couse,SC
Where Couse.Cno=SC.Cno and Couse.Cname = a;
IF a > 85 THEN
SELECT CONCAT(course_name, '课程成绩较好') AS result;
ELSE
SELECT CONCAT(course_name, '课程成绩一般') AS result;
END IF;
END //
call pro02('灰原哀后援会');
-- 3. 创建带参数的存储过程,根据学生名字查看其所有选修课程的总学分,信息显示为 学号 姓名 总学分
delimiter //
create procedure pro03(a varchar(18))
begin
select student.Sno as 学号,student.Sname as 姓名,sum(Couse.Credit) as 总学分
from student inner join SC on student.Sno=SC.Sno inner join Couse on SC.Cno=Couse.Cno
where SC.Score>=60 and student.Sname=a
group by 学号;
end //
call pro03('李荣浩');
-- 4.输出所有的水仙花数
delimiter //
create procedure A_sxh()
begin
declare m int default 100;
declare a int;
declare b int;
declare c int;
while m<1000 do
set a=floor(m/100);
set b=floor(m/10)%10;
set c=m%10;
if(a*a*a+b*b*b+c*c*c=m) then
select m;
end if;
set m=m+1;
end while;
end//
call A_sxh();
基础练习sql自习
最新推荐文章于 2024-08-09 21:25:57 发布