SQL语句创建学生信息数据库表的示例-学生信息数据库表.doc

. .
用SQL语句创立如下三个根本表：学生表(Student)、课程表〔Course〕、学生选课表〔SC〕，构造如下所示
Student表构造
列名
说明
数据类型
约束
Sno
学号
字符串，长度为7
主码
Sname

字符串，长度为10
非空
Ssex
性别
字符串，长度为2
取‘男’或‘女’
Sage
年龄
整数
取值15～45
Sdept
所在院系
字符串，长度为20
默认为‘计算机系’
Create table Student
(
Sno varchar(7) primary key,
Sname varchar(10) not null,
Ssex char (2) check(Ssex=‘男’or Ssex=’女’),
Sage int check(Sage between 15 and 45),
Sdept varchar(20)default(‘计算机系’)
)
Course表构造
列名
说明
数据类型
约束
o
课程号
字符串，长度为10
主码
ame
课程名
字符串，长度为20
非空
Ccredit
学分
整数
取值大于0
Semester
学期
整数
取值大于0
Period
学时
整数
取值大于0
Create table course
(
o varchar(10) primary key,
ame varchar(20) not null,
Ccredit int check(Sctedit>0),
Semester int check(Semester>0),
Period int check(Period>0)
)
SC表构造
列名
说明
数据类型
约束
Sno
学号
字符串，长度为7
主码，引用Student的外码
o
课程号
字符串，长度为10
主码，引用Course的外码
Grade
成绩
整数
取值0～100
Create table SC
(
Sno varchar(7) foreign key references student(Sno),
o varchar(10) foreign key references course(o),
Grade int check(Grade between 0 and 100),
Primary key (Sno,o)
)

1．查询学生选课表中的全部数据。
SELECT *
FROM SC
go
2．查询计算机系学生的XX、年龄。
Select Sname,Sage
From Student
Where Sdept=’计算机系’
3．查询成绩在70～80分之间的学生的学号、课程号和成绩。
Select Sno,o,Grade
From Course,Sc
Where course.o=sc.o and sc.Grade between 70 and 80
4．查询计算机系年龄在18～20之间且性别为“男〞的学生的XX和年龄。
Select Sname,Sage
From Student
Where Sage between 18 and 20 and Ssex=’男’and Sdept=’计算机系’
go
5．查询课程号为“C01”的课程的最高分数。
Select top 1 Grade select max(Grade) as 最高分
From Sc from Sc
Where o=’C01’ where o=’C01’
Order by Grade desc order by Grade desc
6．查询计算机系学生的最大年龄和最小年龄。
Select max(Sage) as 年龄最大，min(Sage) as 年龄最小
From Student
Where Sdept=’计算机系’
7．统计每个系的学生人数。
Select count(Sdept) as学生人数，Sdept
From Student
Group by Sdept
8．统计每门课程的选课人数和考试最高分。
Select count(Sno) as选课人数，c.Sno,max(Grade) as最高分
From Course c left join Sc s on c.o=s.o
Group by c.o
9．统计每个学生的选课门数和考试平均成绩，并按学号的升序显示结果。
Select sno,avg(grade) as ’平均成绩’,count (o) as ’选课门数’
From sc
Group by sno
Order by sno
10．查询总成绩超过200分的学生，要求列出学号、总成绩。
Select sno,sum(grade)
From sc
Group by sno
Having sum(grade)>200
11．查询选修了课程“C02”的学生的XX和所在系。
Select sname,sdept
From student s1,sc s2
Where s1.sno=s2.sno and s2.o=’c02’
12．查询成绩在80分以上的学生的XX、课程号和成绩，并按成绩的降序排列结果。
Select s1.sname,s2.o,s2.grade
From student s1,sc s2
Where s1.sno=s2.sno and grade >80
Order by grade desc
13．查询哪些课程没有人选修、要求列出课程号和课程名。
Select c.o,c.ame
From course c left join sc s on c.o=s.o
Group by c.o,c.ame
Having count(s.sno)=0
14．用子查询实现如下查询：
(1)查询选修了课程“C01”的学生的XX和所在系。
Select sname,sdept ,sno
From student
Where sno in (
Select sno
From sc
Whereo=’c01’
)
(2)查询信息系成绩在80分以上的学生的学号、。
Select sno,sname
From student
Where sdept=’外语系’and sno in(
Select sno
From sc
Where grade>80
)
(3)查询计算机系考试成绩最高的学生的XX。
Select s1.sname from students
Where sdept=’计算机系’ and sno in
(select sno from sc
Where grade in
(select max(Grade)from sc)
)
15．删除选课成绩小于50分的学生的选课记录。
Delete from sc
Where grade<70
Select* from sc—验证
16．将所有选修了课程“C01”的学生的成绩加10分：
Update sc
Set grade=grade+10
Whereo=’c01’
17．将计算机系所有选修了课程“计算机文化根底〞课程的学生的成绩加10分。
Select*from sc
Update sc
Set grade=grade+10
Whereo in
(selecto from course
Whereame=’计算机文化根底’)
18．创立查询学生的学号、、所在系、课程号、课程名、课程学分的视图。
Select* from course
Select* from students
Select* from sc
Create view 学生根本信息
As
Select students.sno,sname,sdept,sc.o,ame,ccredit
From course,sc,students
Where course.o=sc.o
And sc.o=students.sno
19．创立查询每个学生的平均成绩的视图，要求列出学生学号及平均成绩。
Create view s_avg
As
Select sno,avg(Grade)as 平均成绩 from sc
Group by sno
20．创立查询每个学生的选课学分的视图，要求列出学生学号及总学分。
Create view s_sc
As
Select students.sno,sum(ccredit)as 总学分 from
Students,sc,course
Where students.sno=sc.sno
And sc.o=course.o
Group by students.sno
21．用SQL语句创立一个名为f_1的函数，该函数能够求出3到100之间的所有素数之和。
Create function f_1()
Returns int
As
Begin
Declare a int,b int,i int,sum int
Set i=3
Set sum=0
While i<101
Begin
Set b=0
While a<=i/2
Begin
If i%a=0
Begin
Set b=1
Break
End
Set a=a+1
End
If b=0 --b为0说明之前没有比i小的数字可以把i整除
Begin
Set sum=sum+i
End
Set i=i+1
End
Return sum
End
Go
Select dbo.f_1()
22．用SQL语句创立一个名为f_2的函数，该函数能够求出任意两个数的最大值。
Create function f_2(x1 int,x2 int)returns int
As
Begin
Declare max int
If x1>x2
Return max
End
Select dbo.f_2(2,6)
23．用SQL语句创立一个名为pro_get_stu_information的存储过程，该存储过程能够根据用户指定的 Sno〔学号〕 求出与该学号对应的学生XX、课程名、成绩。
Create procedure pro_get_stu_information m char(6) output
As
Select sname,ame,grade from students,sc,course
Where students.sno=sc.sno and sc.o=course.o and sc.sno=m
Exec pro_get_stu_information’0603002’

24．为“学生〞表创立一个依赖于“学号〞的唯一的、非聚集的索引
Create unique nonclustered index stu_int on students(sno)
25．通过游标逐行读取“学生〞表的记录
Declare stu_cur cursor for
Select * from students for read only
Open stu_cur
Fetch stu_cur
Close stu_cur
Deallocate stu_cur
. .word..