SQL上机考试(答案)

数据库原理及应用上机考试

提示：

1、在F盘建立一个以学号+姓名为文件名的文件夹，将此Word文档存入该文件夹。

2、附加“THXY_SM”数据库，完成下面操作。

3、将每题目的操作要求完成。

4、提交该Word文档。

操作要求如下：

1、创建“专业表”。

请写出SQL语句：

Create table专业表

(

序号INT identity(1,1)primary key,

专业代码char(8),

专业名称varchar(20),

系号char(10)references系部表(系号)

)

2、设置“成绩表”的主键。

请写出SQL语句：

alter table成绩表

add constraint pk1 primary key(学号,课程号)

3、在“THXY_SM”数据库中，打开“数据库关系图”中的“学生管理关系图”，请完善表之间关系的创建。请截关系图并粘贴在此下面：

SQL 语句为：

（1）、Alter table学生表

Add Constraint fk1 foreign key(班号)references班级表(班号)

（2）、Alter table成绩表

Add Constraint fk2 foreign key(学号)references学生表(学号)

（3）、Alter table成绩表

Add Constraint fk3 foreign key(课程号)references课程表(课程号)

4、修改“学生表”，为学生表的“性别”列创建取值约束，约束条件为性别列的取值为“男”或“女”，约束名称为“性别取值约束”。

请写出SQL语句：

Alter table学生表

add constraint性别取值约束check( 性别in('男','女') )

5、修改“成绩表”，将“成绩”列的数据类型修改为numeric,精度为6，小数位保留1位。

请写出SQL语句：

Alter table成绩表

alter column成绩numeric(6,1)

6、为学生表的“姓名、班号”列建立一个非聚集组合索引

“name_class”，按姓名的降序、班号升序排列。

请写出SQL语句：

create nonclustered index name_class

on学生表(姓名desc,班号asc)

7、数据查询：(将每题SQL语句写在该题后面)

（1）查询所有姓何的学生信息。

select*

from学生表

where姓名like'何%'

（2）查询“星期五”出生的所有学生的学号，姓名和出生日期；

select学号,姓名,出生日期

from学生表

where datepart(weekday ,出生日期)=6

（3）查询成绩小于70分的学生的学号，姓名、课程名称和成绩。

select学生表.学号,姓名,课程名称,成绩

from成绩表join课程表on成绩表.课程号=课程表.课程号

join学生表on成绩表.学号=学生表.学号

where成绩<70

（4）按班级名称分组，统计每个班的人数，显示班级名称与人数。

select班级名称,人数=count(*)

from学生表join班级表on学生表.班号=班级表.班号

group by班级名称

（5）查询平均分在90分以上的学生的学号和平均成绩

select学号,平均成绩=avg(成绩)

from成绩表

group by学号

having avg(成绩)>=90

（6）查询和“李腾”同一个班的其他学生的学号，姓名，性别，出生日期。

（注：李腾不能显示在查询结果中,使用子查询完成）

select学号,姓名,性别,出生日期

from学生表

where姓名<>'李腾'and 班号=

( select班号

from学生表

where 姓名='李腾'

)

8、视图(将每题SQL语句写在该题后面)

(1)、创建视图：学生成绩。该视图包括学号，姓名，课程名称，成绩；

create view学生成绩

as

select学生表.学号,姓名,课程名称,成绩

from学生表join成绩表on学生表.学号=成绩表.成绩

join课程表on课程表.课程号=成绩表.课程号

(2)、以“学生成绩”视图为数据源，查找课程名为“数据库原理及应用(SQL)”的学生的学号和成绩。

select学号,成绩

from学生成绩

where课程名称='数据库原理及应用(SQL)'

9、在课程表中，插入一条新记录。课程号为“K0023”、课程名称为“电子商务”、课程类型为“必修”、学分为3.

请写出SQL语句：

Insert课程表(课程号,课程名称,课程类型,学分)

values('k0033','电子商务','必修', 3)

或：

Insert课程表

values('k0033','电子商务','必修', 3，null)

10、将“教师表”中职称为“教授”的基本工资设置为5000。

请写出SQL语句：

Update教师表

set基本工资= 5000

where职称= '教授'

11、请删除“教师表”中备注显示为“离职”的老师信息。

请写出SQL语句：