Question

SQL函数题目

创建下面三个表
1.学生(id,姓名)
2.科目(id,科目名称)
3.成绩(id,学生id,科目id，分数)
请把下面表格的资料插入到以上建立好的表中
姓名 科目名称 分数
张三 c语言 100
张三 sql语言 80
张三 英语 50
张三 计算机基础 90
李四 c语言 98
李四 sql语言 85
李四 英语 70
李四 计算机基础 98
李四 计算机网络 78
王五 c语言 59
王五 sql语言 64
王五 英语 98
王五 计算机基础 68
王五 计算机网络 31

请得到有一下字段的查询结果：
姓名,平均成绩,最高分，最低分
科目，最高成绩，最低成绩
编写函数，可以传入姓名，得到某个人的总成绩。
编写函数，可以传入姓名，得到某个人最高成绩的科目名称
编写函数，可以传入科目名称，得到当前科目最高成绩的人的名称和分数
编写函数，传入科目名称，得到当前科目的平均成绩和小于平均成绩的人的名称。
为每个函数编写一个调用。即每个函数都写一个使用的实例。
作业全部使用sql脚本完成，脚本名称为:姓名函数作业.sql
作业的每一题都需要有注释语句，如--创建表xx –-插入记录 --作业第一题答案，作业判断按照注释来，没有看到对应的注释的作业，视为没有做。
表的建立和表的记录的插入也全部都写到脚本中
作业请准时上传到ftp里面。
注释的样式
--创建xx表格
--添加xx表的记录
--作业第一题的第一小题
--作业第xx题

Answer 1

---创建数据库
create database schoolBD
go

--创建学生表
use schoolBD
go
create table [student]
(
s_id int primary key,
s_name nvarchar(20)
)
GO
---创建科目表
create table subject
(
subject_id int primary key,
subject_name char(20)
)
GO

---创建成绩表
create table score
(
score_id int primary key,
s_id int
constraint FK_subject_s_id Foreign key (s_id)
references student(s_id), ---引用学生表中的学生ID
subject_id int
constraint FK_subject_subject_id Foreign key (subject_id)
references subject(subject_id),---引用科目表中的科目ID
grade int
)
GO

---插入信息到学生表
insert student values (00001,'张三')
insert student values (00002,'李四')
insert student values (00003,'王五')

----插入到科目表
insert subject values (001,'c语言')
insert subject values (002,'sql语言')
insert subject values (003,'英语')
insert subject values (004,'计算机基础')
insert subject values (005,'计算机网络')

---把张三的个门成绩插入成绩表
insert score values (01,00001,001,100)
insert score values (02,00001,002,80)
insert score values (03,00001,003,50)
insert score values (04,00001,004,90)

---把李四的个门成绩插入成绩表
insert score values (05,00002,001,98)
insert score values (06,00002,002,85)
insert score values (07,00002,003,70)
insert score values (08,00002,004,98)
insert score values (09,00002,005,78)
---把王五的个门成绩插入成绩表
insert score values (10,00003,001,59)
insert score values (11,00003,002,64)
insert score values (12,00003,003,98)
insert score values (13,00003,004,68)
insert score values (14,00003,005,31)

---姓名和平均分
select s_name as '姓名',avg(grade) as '平均成绩'
from student as s1,subject as s2,score as s3
where s1.s_id=s3.s_id and s2.subject_id=s3.subject_id
group by s_name
----最高成绩的科目名称

select s2.subject_name as '最高成绩的科目的名称'
from student as s1,subject as s2,score as s3
where grade=(select max(grade) from score as s4 where s1.s_id=s4.s_id)
and s1.s_id=s3.s_id and s2.subject_id=s3.subject_id
----最低成绩的科目名称
select s2.subject_name as '最高成绩的科目的名称'
from student as s1,subject as s2,score as s3
where grade=(select max(grade) from score as s4 where s1.s_id=s4.s_id)
and s1.s_id=s3.s_id and s2.subject_id=s3.subject_id

union
---最高和最低成绩
select max(grade) as '最高成绩',min(grade) as '最低成绩'
from student as s4,score as s5
where s4.s_id=s5.s_id group by s4.s_id

---编写函数，可以传入姓名，得到某个人的总成绩。
create function func_SumGrade(@name nvarchar(20))
returns int
as
begin
declare @sumgrade int --返回的总成绩
select @sumgrade=sum(grade)
from student as s1,subject as s2,score as s3
where s1.s_id=s3.s_id and s2.subject_id=s3.subject_id and s1.s_name=@name

if (@sumgrade is null)
set @sumgrade=0
return @sumgrade
end
---example

----编写函数，可以传入姓名，得到某个人最高成绩的科目名称
create function func_SumGrade_subject(@name nvarchar(20))
returns char(20) --返回姓名 此处不可以返回 如果 最高成绩的科目 多于一门的情况
as
begin
declare @subjectname char(20)
select @subjectname=s2.subject_name
from student as s1,subject as s2,score as s3
where grade=(select max(grade) from score as s4 where s1.s_id=s4.s_id) and s1.s_name=@name
and s1.s_id=s3.s_id and s2.subject_id=s3.subject_id
if (@subjectname is null)
set @subjectname=''
return @subjectname
end
---example

---编写函数，可以传入科目名称，得到当前科目最高成绩的人的名称和分数

create function func_tatalName_Grade(@subjectname char(20))
returns table
as
return(
select s1.s_name as '姓名',grade as '分数'
from student as s1,subject as s2,score as s3
where grade=(
select max(grade)
from student as s1,subject as s2,score as s3
where s1.s_id=s3.s_id and s2.subject_id=s3.subject_id and s2.subject_name='英语'
) and
s1.s_id=s3.s_id and s2.subject_id=s3.subject_id and s2.subject_name='英语'
)
---example
select * from func_tatalName_Grade('英语')

---编写函数，传入科目名称，得到当前科目的平均成绩和小于平均成绩的人的名称。
create function func_LowerAvgName(@subjectname char(20))
returns table
as
return(
select s_name,grade
from student as s1,subject as s2,score as s3
where grade<(select avg(grade) as pj ----子查询得到此科目的平均成绩
from student as s1,subject as s2,score as s3
where s1.s_id=s3.s_id and s2.subject_id=s3.subject_id and s2.subject_name=@subjectname)
and s1.s_id=s3.s_id and s2.subject_id=s3.subject_id and s2.subject_name=@subjectname
)
----example
select * from func_LowerAvgName('英语')

Answer 2

那是50分，不是50元，谁能帮你做啊

Answer 3

怎么大的工作量，自己慢慢想！