SQL SERVER查询练习习题

设教学数据库Education有三个关系：
学生关系s（student_id，student_name，birthday，sex，major）；
学习关系sc（student_id，course_id，score）；
课程关系course（course_id,course_name，term,period,credit）
查询问题：
单表查询
1：查所有年龄在20岁以下的学生姓名及年龄。

select student_name,year(getdate()) - year(birthday) as 年龄
from s
where year(getdate()) - year(birthday) < 30
2：查考试成绩有不及格的学生的学号
select distinct student_id
from sc
where score < 60
3：查所年龄在20至23岁之间的学生姓名、系别及年龄。

select student_name,major,year(getdate()) - year(birthday) as 年龄from s
where year(getdate()) - year(birthday) between 20 and 24
4：查舞蹈编导、英语、通信技术专业的学生姓名、性别。

select student_name, sex
from s
where major in ('舞蹈编导','英语','通信技术')
5：查不是舞蹈编导、英语、通信技术的学生姓名、性别
select student_name, sex
from s
where major not in ('舞蹈编导','英语','通信技术')
6：查所有姓“刘”的学生的姓名、学号和性别。

select student_name,student_id,sex
from s
where student_name like '刘%'
8：查所有不姓“张”且单名的学生的姓名。

select student_name
from s
where student_name not like '张_'
9：查C++课程的课程号。

select course_id
from course
where course_name='C++'
10：查缺考的学生的学号和课程号。

select student_id,course_id
from sc
where score is null
11：查开课学期为空值的课程编号和名称。

select course_id,course_name
from course
where term is null
12：查信息管理专业20岁以下的学生的学号和姓名。

select student_id,student_name
from s
where major='信息管理' and year(getdate()) - year(birthday)<20
13：查舞蹈编导专业性别为女的学生姓名、性别。

select student_name,sex
from s
where major='舞蹈编导' and sex='女'
14：查询选修了110010课程的学生的学号和成绩，其结果按分数的降序排列。

select student_id,score
from sc
where course_id='110010'
order by score desc
15：查询全体学生的情况，查询结果按所在专业升序排列，对同一系中的学生按年龄降序排列。

select *
from s
order by major asc,year(getdate()) - year(birthday) desc
16：查询学生总人数。

select count(*) as 人数
from s
17：查询选修了课程的学生人数。

select count(*) as 选修课程人数
from sc
18：计算选修了110010课程的学生平均成绩。

（改为C++）
select avg(score) as 平均成绩
from sc
where course_id=
(select course_id
from course
where course_name='C++'
)
19：查询学习110010课程的学生最高分数。

select max(score) as 最高分
from sc
where course_id='110010'
20：查询各个课程号与相应的选课人数。

（改为课程名称）
select course_name,count(*) as 人数
from sc,course
where sc.course_id=course.course_id
group by course_name
21：查询至少选修两门课程的学号。

select student_id
from sc
group by student_id
having count(*) > 2
22：查询选修课程超过3门的学生姓名及课程门数
select student_name,count(*) as 课程门数
from s,sc
where s.student_id=sc.student_id
group by student_name
having count(*) > 3
多表查询
23：查询每个学生及其选修课程的情况。

select *
from s,sc,course
where s.student_id=sc.student_id and sc.course_id=course.course_id 24：查询选修了110011课程且成绩在90分以上的学生信息。

select *
from s,sc
where s.student_id=sc.student_id and course_id='110010' and score>90 25：查询每个学生的姓名，选修的课程名及其成绩。

select student_name,course_name,score
from s,sc,course
where s.student_id=sc.student_id and
sc.course_id=course.course_id
子查询
27：查询选修了110011课程的学生姓名。

select student_name
from s
where student_id in
(select student_id
from sc
where course_id='110010'
)
28：查询与“梁婷婷”在同一个专业学习的学生学号、姓名和专业。

select student_id,student_name,major
from s
where major=
(select major
from s
where student_name='梁婷婷'
)
29：查询选修课程名为“C++”的学生学号和姓名。

select student_id,student_name
from s
where student_id in
(select student_id
from sc
where course_id=
(select course_id
from course
where course_name='C++'
)
)
30：查询至少选修课程号为110010和110011的姓名。

select student_name
from s
where student_id in
( select s1.student_id
from sc s1,sc s2
where s1.course_id='110010' and s2.course_id='110011'
and s1.student_id=s2.student_id
)
31：查询选修“C++”的最高分的学生姓名，性别，系别
select student_name,sex,major
from s,sc
where s.student_id = sc.student_id
and course_id in
( select sc.course_id
from sc,course
where sc.course_id = course.course_id and course_name = 'C++' )
and score =
( select max(score)
from sc,course
where sc.course_id = course.course_id
and course_name = 'C++'
)
32：查询所有未选修C++课程的学生姓名。

select student_name
from s
where not exists
(select *
from sc
where course_id=
( select course_id
from course
where course_name='C++') and sc.student_id=s.student_id )
1. （ A ）是位于用户与操作系统之间的一层数据管理软件，它属于系统
软件，它为用户或应用程序提供访问数据库的方法。

数据库在建立、使用和维护时由其统一管理、统一控制。

A．DBMS B．DB （database）
C．DBS D．DBA
2、查询表中的前3条记录使用的关键字是（ D ）
A、up 3
B、down 3
C、pre 3
D、top 3
3. SQL Server安装程序创建4个系统数据库，下列哪个不是（ C ）系统
数据库。

A. Master
B. Model
C. pub
D. msdb
4. 下列哪个不是sql 数据库文件的后缀（扩展名）。

（ C ）
A．.mdf B． .ldf C．.tif D．.ndf
5. SQL的视图是从（ C ）中导出的。

A. 基本表
B. 视图
C. 基本表或视图
D. 数据库
6. 在SQL语言中，建立存储过程的命令是( A )
A、CREATE PROCEDURE
B、CREATE RULE
C、CREATE DURE
D、CREATE FILE
7. SQL语言中，删除表中数据的命令是( A )。

A. DELETE
B. DROP
C. CLEAR
D. REMOVE
8. 在MS SQL Server中，用来显示数据库信息的系统存储过程是( D )。

A. sp_dbhelp
B. sp_db
C. sp_help
D. sp_helpdb
9. Microsoft 公司的SQL Server 2005 数据库管理系统一般只能运行在
（ A ）。

A. Windows 平台
B. UNIX平台
C. LINX平台
D. NetWare 平台
10. SQL Server 2005 的物理存储主要包括3类文件（A ）。

A. 主数据文件、次数据文件、事务日志文件
B. 主数据文件、次数据文件、文本文件
C. 表文件、索引文件、存储文件
D. 表文件、索引文件、图表文件
11. SQL Server 2000 系统中的所有系统级信息存储于哪个数据库（ A ）。

A. master
B. model
C. tempdb
D.msdb
12、下列说法正确的是（ B ）。

A 、视图是观察数据的一种方法，只能基于基本表建立。

B 、视图是虚表，观察到的数据是实际基本表中的数据。

C 、索引查找法一定比表扫描法查询速度快。

D 、索引的创建只和数据的存储有关系。

13、SQL Server 2005是一个（ C ）的数据库系统。

（A）网状型（B）层次型（C）关系型（D）以上都不是
14、在SQL Server 2005中，当数据表被修改时，系统自动执行的数据库对象
是（ B ）。

（A）存储过程（B）触发器（C）视图（D）其他数据库对象15、要查询book表中所有书名中包含“计算机”的书籍情况，可用（ B ）
语句。

（A） SELECT * FROM book WHERE book_name LIKE ‘计算机*’
（B） SELECT * FROM book WHERE book_name LIKE ‘计算机%’
（C） SELECT * FROM book WHERE book_name = ‘计算机*’
（D） SELECT * FROM book WHERE book_name = ‘计算机%’
16、关于主键描述正确的是：（ C ）
（A）包含一列（B）包含两列（C）包含一列或者多列（D）以上都不正确
17、在SQL SERVER中局部变量前面的字符为：（ D ）
（A）* （B）# （C）@@ （D） @
18、在WHILE循环语句中，如果循环体语句条数多于一条，必须使用：(A) （A） BEGIN……END
（B） CASE……END
（C） IF…………THEN
（D） GOTO
19、SELECT查询中，要把结果中的行按照某一列的值进行排序，所用到的子句
是：(A)
（A）ORDER BY （B）WHERE （C）GROUP BY （D）HAVING 20、对视图的描述错误的是：（ C ）
（A）是一张虚拟的表
（B）在存储视图时存储的是视图的定义
（C）在存储视图时存储的是视图中的数据
（D）可以像查询表一样来查询视图
五、设计题(共45分)
现有关系数据库如下：
数据库名：学生成绩数据库
学生信息表(学号 char(6)，姓名，性别，民族，身份证号)
课程信息表(课号 char(6)，名称)
成绩信息表(ID,学号，课号，分数)
Select 学号,姓名 from 学生信息表 where 学号 in (Select distinct 学号 from 成绩信息表 where 分数<60)
用SQL语言实现下列功能的sql语句代码。

1. 创建数据库[学生成绩数据库]代码(2分)。

create database学生成绩数据库
on primary
(filename='d:\stu.mdf',
name=studata,
size=3mb,
maxsize=unlimited,
filegrowth=3%
)
log on
(filename='d:\stu.ldf',
name=stulog,
size=1mb,
maxsize=3mb
)
2. 创建数据表[课程信息表]代码；（2分）
课程信息表(课号 char(6)，名称)
要求使用：主键(课号)、非空(名称)
create table课程信息表
(课号char(6)primary key,
名称nvarchar(30)not null)
3. 创建数据表[学生信息表]代码；（4分）
学生信息表(学号 char(6)，姓名，性别，民族，身份证号)
要求使用：主键(学号)、默认(民族为汉)、非空(民族，姓名)、唯一(身份证号)、检查(性别是男或是女)
create table学生信息表
(学号char(6)primary key,
姓名nvarchar(30)not null,
性别char(2)check(性别='男'or性别='女'),
民族nvarchar(10)not null default'汉',
身份证号char(18)unique
)
4. 创建数据表[成绩信息表]；（5分）
成绩信息表(ID,学号，课号，分数)
要求使用：外键(学号，课号)、检查(分数必须是0-100之间)
create table成绩信息表
( id int,
学号char(6)foreign key references学生信息表(学号),
课号char(6)foreign key references课程信息表(课号),
分数int check(分数>=0 and分数<=100)
)
5. 将下列课程信息添加到课程信息表的代码（8分）
课号名称
100101 西班牙语
100102 大学英语
insert into课程信息表
values('100101','西班牙语')
insert into课程信息表
values('100102','大学英语')
修改课号为100102的课程名称：专业英语
update课程信息表
set名称='专业英语'
where课号='100102'
删除课号为100101的课程信息
delete from课程信息表
where课号='100101'
6. 创建视图[成绩信息表视图]的代码；（5分）
成绩信息表视图(学号，姓名，课号，课程名称，分数)
Create view成绩信息表视图
as
select学号,姓名,课号,课程名称,分数
from学生信息表,课程信息表,成绩信息表
where学生信息表.学号=成绩信息表.学号and课程信息表.课号=成绩信息表.课号
7. 从学生信息表中查询姓刘的女同学的情况：姓名、性别、民族。

（2分select姓名,性别,民族
from学生信息表
where姓名like'刘%'and性别='女'
8. 查询有一门或一门以上课程成绩小于60分的所有学生的信息，包括学号、姓名。

（4分）
select学号,姓名
from学生信息表
where学号in
(select distinct学号
from成绩信息表
where分数<60)
9. 创建带参数的存储过程[某门课程高低均分]、执行该过程的代码（7分）
存储过程功能：查询某门课程的最高分、最低分、平均分；
执行该过程，查询所有修’专业英语’这门学生的最高分、最低分、平均分；
create procedure某门课程高低均分
@课程名nvarchar(30)
as
select max(分数)as最高分,min(分数)as最低分,avg(分数)as平均分from课程信息表,成绩信息表
where课程信息表.课号=成绩信息表.课号and名称=@课程名
执行过程：
exec某门课程高低均分 @课程名='专业英语'。