[应用]SQL语句创建学生信息数据库
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
的示例
用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表结构
列名 说明 数据类型 约束
Cno 课程号 字符串,长度为10 主码
Cname 课程名 字符串,长度为20 非空
Ccredit 学分 整数 取值大于0
Semester 学期 整数 取值大于0
Period 学时 整数 取值大于0 Create table course
(
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)
)
SC表结构
列名 说明 数据类型 约束
Sno 学号 字符串,长度为7 主码,引用Student的外码
Cno 课程号 字符串,长度为10 主码,引用Course的外码
Grade 成绩 整数 取值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 course.cno=sc.Cno and sc.Grade 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选课人数,c.Sno,max(Grade) as最高分
From Course c left join Sc s on c.cno=s.CnoGroup by c.Cno
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 s1.sno=s2.sno and s2.cno=’c02’ 12(查询成绩在80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。
Select s1.sname,s2.cno,s2.grade
From student s1,sc s2
Where s1.sno=s2.sno and grade >80
Order by grade desc
13(查询哪些课程没有人选修、要求列出课程号和课程名。
Select c.cno,c.cname
From course c left join sc s on c.cno=s.cno Group by c.cno,c.cname
Having count(s.sno)=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 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
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 students.sno,sname,sdept,sc.cno,cname,ccredit From course,sc,students
Where course.cno=sc.cno
And sc.cno=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.cno=course.cno
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
nd E
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 students.sno=sc.sno and sc.cno=course.cno 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