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 FILE

D、CREATE

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. master B. model C. tempdb D.msdb

12、下列说法正确的是（ B ）。

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

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

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

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

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

（A）网状型 （B）层次型 （C）关系型 （D）以上都不是

A ）。

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 某门课程高低均分 @课程名='专业英语'