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

用SQL语句创建如下三个基本表：学生表(Student)、课程表（Course）、学生选课表（SC），结构如下所示

Student表结构

列名

Sno

Sname

Ssex

Sage

Sdept

Create table Student

(

)

Course表结构

列名

Cno

Cname

Ccredit

Semester

Period

Create table course

(

)

SC表结构

列名

Sno

说明

学号

数据类型

字符串，长度为7

约束

主码，引用Student的外码

Cno varchar(10) primary key,

Cname varchar(20) not null,

Ccredit int check(Sctedit>0),

Semester int check(Semester>0),

Period int check(Period>0)

说明

课程号

课程名

学分

学期

学时

数据类型

字符串，长度为10

字符串，长度为20

整数

整数

整数

约束

主码

非空

取值大于0

取值大于0

取值大于0

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(‘计算机系’)

说明

学号

姓名

性别

年龄

所在院系

数据类型

字符串，长度为7

字符串，长度为10

字符串，长度为2

整数

字符串，长度为20

约束

主码

非空

取‘男’或‘女’

取值15～45

默认为‘计算机系’

Cno

Grade

课程号

成绩

字符串，长度为10

整数

主码，引用Course的外码

取值0～100

Create table SC

(

Sno varchar(7) foreign key references student(Sno),

Cno varchar(10) foreign key references course(Cno),

Grade int check(Grade between 0 and 100),

Primary key (Sno,Cno)

)

1．查询学生选课表中的全部数据。

SELECT *

FROM SC

go

2．查询计算机系学生的姓名、年龄。

Select Sname,Sage

From Student

Where Sdept=’计算机系’

3．查询成绩在70～80分之间的学生的学号、课程号和成绩。

Select Sno,Cno,Grade

From Course,Sc

Where = and between 70 and 80

4．查询计算机系年龄在18～20之间且性别为“男”的学生的姓名和年龄。

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 Cno=’C01’ where Cno=’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选课人数，,max(Grade) as最高分

From Course c left join Sc s on =

Group by

9．统计每个学生的选课门数和考试平均成绩，并按学号的升序显示结果。

Select sno,avg(grade) as ’平均成绩’,count (cno) 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”的学生的姓名和所在系。

Select sname,sdept

From student s1,sc s2

Where = and =’c02’

12．查询成绩在80分以上的学生的姓名、课程号和成绩，并按

成绩的降序排列结果。

Select ,,

From student s1,sc s2

Where = and grade >80

Order by grade desc

13．查询哪些课程没有人选修、要求列出课程号和课程名。

Select ,

From course c left join sc s on =

Group by ,

Having count()=0

14．用子查询实现如下查询：

(1)查询选修了课程“C01”的学生的姓名和所在系。

Select sname,sdept ,sno

From student

Where sno in (

Select sno

From sc

Where cno=’c01’

)

(2)查询信息系成绩在80分以上的学生的学号、姓名。

Select sno,sname

From student

Where sdept=’外语系’and sno in(

)

Select sno

From sc

Where grade>80

(3)查询计算机系考试成绩最高的学生的姓名。

Select 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

Where cno=’c01’

17．将计算机系所有选修了课程“计算机文化基础”课程的学生的成绩加10分。

Select*from sc

Update sc

Set grade=grade+10

Where cno in

(select cno from course

Where cname=’计算机文化基础’)

18．创建查询学生的学号、姓名、所在系、课程号、课程名、课程学分的视图。

Select* from course

Select* from students

Select* from sc

Create view 学生基本信息

As

Select ,sname,sdept,,cname,ccredit

From course,sc,students

Where =

And =

19．创建查询每个学生的平均成绩的视图，要求列出学生学号

及平均成绩。

Create view s_avg

As

Select sno,avg(Grade)as 平均成绩 from sc

Group by sno

20．创建查询每个学生的选课学分的视图，要求列出学生学号及总学分。

Create view s_sc

As

Select ,sum(ccredit)as 总学分 from

Students,sc,course

Where =

And =

Group by

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（学号） 求出与该学号对应的学生姓名、课程名、成绩。

Create procedure pro_get_stu_information @m char(6) output

As

Select sname,cname,grade from students,sc,course

Where = and = and =@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