• 编程题（共10分）

有一个“学生选课成绩系统”数据库，数据库中包括三个表：

(1)“学生”表Student由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，可记为： Student(Sno,Sname,Ssex,Sage,Sdept) Sno 为关键字。

(2)“课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，可记为： Course(Cno,Cname,Cpno,Ccredit) Cno为关键字。

(3)“学生选课”表SC由学号（Sno）、课程号（Cno）、成绩（Grade）三个属性组成，可记为：SC(Sno,Cno,Grade) (SNO, CNO) 为关键字。

完成下列操作：

• 请把其中建立 “学生”表Student的语句写下来，表Student是由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。（2分）

CREATETABLE Student                   

(Sno CHAR(5) NOT NULL primary key,         

Sname CHAR(20),                       

Ssex CHAR(1),                         

Sage INT,                             

Sdept CHAR(15));

• 在student表中查询Sdept是‘计算机’的学生的所有信息，并按Sno降序排列。（2分）

select * from student where Sdept=’计算机’ order by Sno desc

③在以上三个表中查询Ccredit为5并且Grade大于60的学生的学号、姓名和性别。（2分）

select student.Sno,Sname,Ssex          

from student, course, sc               

where (student.Sno=sc.Sno) and (course.Cno=sc.Cno)    

        and (course.Ccredit=5) and (sc.Grade>60)  

④为Course表创建一个名称为my_trig的触发器，当用户成功删除该表中的一条或多条记录时，触发器自动删除SC表中与之有关的记录。（注：在创建触发器之前要判断是否有同名的触发器存在，若存在则删除之。）（4分）

 If exists(Select name From sysobjects

Where name=’my_trig’and type=’tr’)     

          Drop trigger my_trig                           

Go                  

Create trigger my_trig        

On Course              

For Delete              

As               

Delete From sc          

Where Sno in (Select Sno From Deleted)          

Go

• 编程题（共10分）

说明：为了管理学生的成绩，现建有学生管理数据库SCDB，以下各程序题以该数据库为操作对象。SCDB数据库包括student（学生表）、course（课程表）、sc（成绩表），表结构如下：

表1 student

字段名	数据类型	长度	是否允许为空	说明
Studentid	varchar	10	否	学号
Name	varchar	8	否	姓名
Sex	varchar	2	是	性别

表2 course

字段名	数据类型	长度	是否允许为空	说明
CourseID	Varchar	10	否	课程编号
CourseName	Varchar	40	否	课程名称
Teacher	Varchar	10	是	任课教师

表3 sc

字段名	数据类型	长度	是否允许为空	说明
Studentid	varchar	10	否	学号
CourseID	Varchar	10	否	课程编号
Grade	Float		是	成绩

根据上述SCDB数据库结构的描述，完成下列操作：

• 查询成绩大于85分的学生的姓名、课程号和成绩，并按照课程号的升序。（3分）

Use SCDB

Go

Select name‘姓名’,courseid ‘课程号’,grade’成绩’

From student,sc

Where student. studentid =sc. studentid and grade>85

Order by courseid ASC

• 按照课程统计每门课程的选课人数和该门课程的平均成绩，查询结果显示coursename课程名、选课人数、平均成绩几项内容，结果按照平均成绩由高到低排列。（4分）

Use SCDB

Go

Select coursename ’课程名’,count（*） ‘选课人数’,avg(grade) ‘平均成绩’

From course,sc

Where course.courseid= sc.courseid

Group by coursename

Order by avg(grade)

• 调用程序填空题第1小题创建的的avgStudent存储过程，要求查询学生“李萍”的平均分，并将平均分数存于@lipingavg变量中。（3分）

USE SCDB

GO

DECLARE  @lipingavg float

EXEC avgStudent ‘李萍’, @lipingavg output

GO

• 编程题（共10分）

说明：为了管理学生的成绩，现建有学生管理数据库SCDB，以下各程序题以该数据库为操作对象。SCDB数据库包括student（学生表）、course（课程表）、sc（成绩表），表结构如下：

表1 student

字段名	数据类型	长度	是否允许为空	说明
Studentid	varchar	10	否	学号
Name	varchar	8	否	姓名
Sex	varchar	2	是	性别

表2 course

字段名	数据类型	长度	是否允许为空	说明
CourseID	Varchar	10	否	课程编号
CourseName	Varchar	40	否	课程名称
Teacher	Varchar	10	是	任课教师

表3 sc

字段名	数据类型	长度	是否允许为空	说明
Studentid	varchar	10	否	学号
CourseID	Varchar	10	否	课程编号
Grade	Float		是	成绩

• 在student表中，查询姓名不是以“张”、“李”、“王”字开头的学生的所有信息，并按学号升序排列（3分）

Use SCDB

Go

Select *

From student

Where name like ‘[^张，李，王]%’

Order by studentid asc

②创建一个“学生成绩”视图，视图包含学生的学号studentid、姓名name、课程编号courseid、课程名coursename和学生该门课程的成绩grade。（4分）

Use SCDB

Go

Create view学生成绩

As

Select studentid 学号,name 姓名, courseid 课程编号, coursename 课程名, grade 成绩

From course,sc,student

Where course.courseid= sc.courseid and student.studentid =sc.studentid

③调用程序填空题第1小题创建的的CountStudent存储过程，要求查询学号“2015001”的总分，并将平均分数存于@sumgrade变量中。（3分）

USE SCDB

GO

DECLARE  @sumgrade int

EXEC CountStudent ‘2015001’, @sumgrade output

GO

• 编程题（共10分）

1. 创建数据库，名称为test，路径是d:\data\，初始大小是3M，最大大小是100M，自动增长幅度为5M。（2分）

Create database test

On primary

(name=test,

Filename=’d:\data\test.mdf’,

Size=3,

Maxsize=100,

Filegrowth=5)

2. 利用create语句创建下列各表，注意完整性约束：（字段名都用中文表示）（4分）

表1学生信息表

列名	数据类型	长度	是否允许为空	说明
学号	char	11	否	主键
姓名	nchar	8	否	唯一
性别	nchar	2	是	只能取男和女
家庭住址	nvarchar	20	是	默认为“杭州”

表2 课程信息表

列名	数据类型	长度	是否允许为空	说明
课程号	char	6	否	主键
课程名	nvarchar	20	否	唯一
学分	Char	2	是	默认为0，取值0-10

表3选课成绩表

列名	数据类型	长度	是否允许为空	说明
学号	char	6	否	主键，外键
课程号	char	6	否	主键，外键
成绩	int		是	取值范围为0-100

Create table 学生信息表(

学号 char(11) primary key，

姓名 nchar(8) not null unique(姓名)，

性别 nchar(2))null check(性别=‘男’ or 性别=‘女’)，

家庭住址 nvarchar(20) null default(N‘杭州’))

Create table 课程信息表(

课程号 char(6) primary key,

课程名 nvarchar(20) null unique(课程名)，

学分 char(2) null default(‘0’)

check(学分>=0 and 学分<=10))

Create table 选课成绩表(

学号 char(6) not null，

课程号 char(6) not null,

成绩 int null check(成绩>=0 and 成绩<=100))

3. 用SQL语句实现下列操作（4分）

1. 1. 在学生信息表中输入一条记录：学号（201301）、姓名（张三）、性别（男）、家庭住址（上海市申花路110号）；

Insert into 学生信息表

Values(‘200131’，‘张三’，‘男’，‘上海市申花路110号’)

1. 1. 查询学号为200131号的学生所选课程的平均成绩；

Select avg(成绩)

From 成绩表

Where 学号=’200131’

1. 1. 查询姓名为李四的学生所选课程的课程名；

Select 课程名

From 学生信息表 as a，课程信息表 as b，成绩表 as c

Where 姓名=’李四’ and a.学号=c.学号

and b.课程号=c.课程号

1. 1. 查询数据结构课程的平均成绩；

Select avg(成绩)

From 成绩表，课程信息表

Where 课程名=’数据结构’ and 成绩表.课程号=课程信息表.课程号

• 编程题（共10分）

1. 创建数据库，名称为News，路径是d:\data\，初始大小是5M，最大大小是200M，自动增长幅度为3M。（2分）

Create database News

On primary

(name=news,

Filename=’d:\data\news.mdf’,

Size=5,

Maxsize=200,

Filegrowth=3)

2. 利用Create语句创建下列各表，注意完整性约束（字段名都用中文表示）（4分）   

表1新闻表

列名	数据类型	长度	是否允许为空	说明
新闻编号	int	4	否	主键，自动增长
发布人	nchar	6	否	默认为“管理员”
标题	nvarchar	50	否
内容	nvarchar	300	否
发布时间	datetime		是	默认为系统时间
点击数量	int	4	是	默认为0

表2 评论表

列名	数据类型	长度	是否允许为空	说明
评论编号	int	4	否	主键，自动增长
新闻编号	int	4	否	外键，引用新闻表中的新闻编号
评论内容	nvarchar	300	否
评论时间	datetime		是	默认为系统时间
状态	nchar	3	否	默认“未审核”
评论人	nchar	6	否

create table 新闻表(

    新闻编号 int not null indentity(1,1) primary key,

    发布人 nchar(6) not null default(‘管理员’),

    标题 nvarchar (50) not null,

    内容 nvarchar(300) not null,

    发布时间datetime null default (getdate()),

    点击数量 int default(0))

create table 评论表(

评论编号 int not null indentity(1,1) primary key,

新闻编号 int not null foreign key references 新闻表(新闻编号),

评论内容 nvarchar(300) not null,

评论时间 datetime null default (getdate()),

状态 nchar(3) not null default (‘未审核’),

评论人nchar (6) not null)

3. 用SQL语句实现下列操作（4分）

1. 1. 在新闻表中输入一条记录：发布人（张三）、标题（放假通知）、内容（决定于5月1日至5月3日共放假三天）；

Insert into 新闻表(发布人,标题,内容) Values(‘张三’，‘放假通知’,

   ’决定于5月1日至5月3日共放假三天’)

1. 1. 查询所有由张三发布的新闻记录；

Select *

From新闻表

Where 发布人=’张三’

1. 1. 按评论时间降序显示所有针对1号新闻发表的评论（评论编号、评论内容、评论人、评论时间）；

Select 评论编号、评论内容、评论人、评论时间

From 评论表

Where 新闻编号=1

Order by 评论时间 desc

显示评论数量在10条以上的新闻记录（新闻标题、内容、发布人、发布时间）；

Select新闻标题、内容、发布人、发布时间

From 新闻表

Where新闻编号 in

(Select 新闻编号

From评论表

Group by新闻编号

Having count(新闻编号)>=10)

• 编程题（共10分）

某高校学生在SQL Server上成功对数据库进行了创建和相关操作。以下为他的实验记录。

1. 正确创建数据库

请用SQL命令创建该数据库：             create  database youku                 。（1分）

1. 正确设计并创建电影表（movies）

设计如下表所示：

请写出创建该表的SQL命令：         create table  movies （id int primary key，name varchar（50），type varchar （50），price float，descs varchar(250)）            。（2分）

1. 请在该表中插入以下1条记录：

 

请写出插入该记录的SQL命令： insert into movies  values  (1,’芈月传’,’电视剧’,1.1,’秦国与楚国’)                            。（2.5分）

4.修改movies表，添加男主角（firstman）和女主角（firstwomen）两列，具体如下图所示：

 

请用SQL命令修改该数据库表： alter table movies  add column firstman  varchar(50),firstwomen varchar(50)                             。（2分）

5.修改movies表，如下图所示，要求在插入电影数据时让type这一列默认的内容是剧情片。

请用SQL命令修改该数据库表，约束名称自己定义：

alter table movies  add constraint default-type default('剧情片') for type                                 。（2.5分）

 

• 编程题（共10分）

已知学生表S和学生选课表Sc。其关系模式如下：

S(Sno，Sn，Sd，Prov)

Sc(Sno，Cno，Cn，G)

其中，Sno为学号，Sn为姓名，Sd为系名，Prov为省区，Cn为课程名，G为分数。

试用Sql语言实现下列操作：

(1).查询“信息系”的学生来自哪些省区。（1分）

Select Distinct Prov From S   Where Sd=“信息系”

(2).按分数降序排序，输出“英语系”学生选修了“计算机”课程的学生的姓名和分数。（2分）

Select Sn，G  From S，Sc Where Sd=“英语系”And Cn=“计算机”And S.Sno＝Sc.Sno Order By G Desc；

(3).建立一个视图V-Ssc(Sn，Sn，Cn，Cn，G)，并按Cno升序排序；（4分）

Create  View V-Ssc(Sno，Sn，Cno，Cn，G)As Select S.Sno， S.Sn，Cno，Sc.Cn，Sc.G From S，Sc Where S．Sno=Sc．Sno Order By Cno

(4).从视图V-Ssc上查询平均成绩在90分以上的Sn、Cn和G。（3分）

Select Sn，Cn，G From V-Ssc Group By Sno Having Avg(G)＞90

• 编程题（共10分）

有一个“学生选课成绩系统”数据库，数据库中包括三个表：

(1)“学生”表Student由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，可记为：

(2)“课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，可记为： Course(Cno,Cname,Cpno,Ccredit) Cno为关键字。

(3)“学生选课”表SC由学号（Sno）、课程号（Cno）、成绩（Grade）三个属性组成，可记为：SC(Sno,Cno,Grade) (SNO, CNO) 为关键字。

完成下列操作：

• 请把其中建立 “课程”表COURSE的语句写下来，表COURSE是由课程号CNO、课程名Cname、先修课号Cpno、学分Ccredit四个属性组成，其中课程号是主键，课程名不能为空。（2分）

1. CREATE TABLE Course                   

(Cno CHAR(5) PRIMARY KEY,              

Cname CHAR(20) NOT NULL,               

Cpno CHAR(4),                         

Ccredit SMALLINT );    

• 查询选修2号课程且成绩在90分以上的所有学生。（2分）

2.        

SELECT Student.Sno, Sname             

FROM    Student, SC                   

WHERE Student.Sno = SC.Sno            

AND   SC.Cno= ‘2’                    

AND SC.Grade > 90；       

③查询没有选修1号课程的学生姓名。（2分）

3、

    SELECT Sname                          

FROM Student                          

WHERE NOT EXISTS                      

(SELECT * FROM SC                     

WHERE Sc.Sno = Student.Sno AND Cno='1')；

④创建一个存储过程，产生某门课程的学生选课情况列表，其中包括课程号、课程名、选课人数。要求输入1号课程，得到上述信息，带参数运行该存储过程。（4分）               

4.

CREATE PROCEDURE CourseList                        

@cno CHAR(4)                                        

AS   BEGIN                                         

    DECLARE @number int                            

    SELECT sc.cno,min(Cname),Count(sc.cno) AS 人数 

    FROM SC,Course                                 

    WHERE sc.cno=course.cno and sc.cno=@cno        

    GROUP BY sc.Cno                                

END                                                

EXEC Courselist 1                                  

• 编程题（共10分）

有一个“学生成绩管理系统”数据库，数据库中包括三个表：

(1) stud_info（stud_id、stud_name、gender、nation、birthday、class_id、enter_date）

(2) course_info（course_id、course_name、book_id、week_perior、total_perior、credit）

(3) student_grade（course_id，stud_id，grade，stud_credit, term, form）

对上述数据表依次说明如下：

学生表，每列的含义为学号、姓名、性别、民族、出生日期、班级、入学日期。

课程表，每列的含义为课程编号、课程名称、教材编号、周数、总学时、学分。

成绩表，每列的含义为课程编号、学号、成绩、获得的学分、考试所在的学期、考试形式。

按照要求完成下列操作：

• 利用SQL语句创建课程表，字段要求如下图所示，请同时指定数据表的关键字（2分）。

 

CREATE TABLE [course_info](                      

   [course_id] [char](10) NOT NULL PRIMARY KEY,  

   [course_name] [nvarchar](40) NOT NULL,        

   [book_name] [nvarchar](50) NOT NULL,          

   [week_perior] [tinyint] NULL,                 

   [total_perior] [tinyint] NULL,                

   [credit] [numeric](3, 2) NOT NULL,            

 )

• 从学生表中查询出不是在2010至2015年之间入学的学生信息。（2分）

Select *                                            

From stud_info                                      

Where year(enter_date) not between 2010 and 2015    

• 从成绩表中统计各个学生的“考试”课程的平均成绩，要求只显示平均分在60分以下的学生的学号、平均分。（2分）

Select stud_id, avg(grade)       

From student_grade

Where form='考试'                

Group by stud_id                 

Having avg(grade)<60             

• 创建存储过程proc_2，使得可以按照学号查询出某个学生的考试课程（form ='考试'）的平均分，当平均分高于85分时，提示“成绩优秀”，否则输出“继续加油”。（4分）

       Create proc proc_2                      

@stud_id char(10)                      

 As

 Begin

  declare @grade int                     

  select @grade=avg(grade)                

from student_grade                     

  where stud_id=@stud_id and form='考试' 

  If @grade>=85  Print '成绩优秀'         

  Else Print '继续加油'                  

End

• 编程题（共10分）

根据上面程序填空题给出的学生选课成绩管理数据库（StuSelectCourse）表结构，完成下列操作：

• 请把其中建立 “学生”表Student的语句写下来，表Student是由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。（2分）

CREATETABLE Student                   

(Sno CHAR(5) NOT NULL UNIQUE,         

Sname CHAR(20),                       

Ssex CHAR(1),                         

Sage INT,                             

Sdept CHAR(15));                      

• 在student表中查询Sdept是‘计算机’的学生的所有信息，并按Sno降序排列。（2分）

select * from student where Sdept=’计算机’ order by Sno desc

③在以上三个表中查询Ccredit为5并且Grade大于60的学生的学号、姓名和性别。（2分）

select student.Sno,Sname,Ssex          

from student, course, sc               

where (student.Sno=sc.Sno) and (course.Cno=sc.Cno)    

        and (course.Ccredit=5) and (sc.Grade>60)    

• 为Course表创建一个名称为my_trig的触发器，当用户成功删除该表中的一条或多条记录时，触发器自动删除SC表中与之有关的记录。（注：在创建触发器之前要判断是否有同名的触发器存在，若存在则删除之。）（4分）

If exists(Select name From sysobjects

Where name=’my_trig’and type=’tr’)     

          Drop trigger my_trig                           

Go                   

Create trigger my_trig        

On Course              

For Delete               

As                 

Delete From sc     

Where Sno in (Select Sno From Deleted)       

Go