TSQL的综合题目

1、有学生－课程关系数据库，各关系表描述如下：

        student

列名	说明	数据类型	约束
Sno	学号	字符（8）	主键
Sname	姓名	字符（12）	非空，唯一
Ssex	性别	字符（2）	取‘男’或‘女’, 默认值 ‘女’
Sage	年龄	整型	取值16～40,默认值21
Sdept	院系	字符（20）	默认 ‘电子系’

列名	说明	数据类型	约束
Cno	课程号	字符（8）	主键
Cname	课程名	字符（8）	非空
Teacher	教师名	字符（10）	唯一

  Course

 

 

 

 

 

SC

列名	说明	数据类型	约束
Sno	学号	字符（8）	主键，参照Student表，级联更新和删除
Cno	课程号	字符（8）	主键，参照Course表，级联更新,拒绝删除
Grade	成绩	短整型	取值0～100

 

(1)用关系代数表达式表示下列问题

1)检索选修了教师‘王敏’所授课程的学生姓名和学号 ；    （2分）

（难度系数B）正确答案：

∏姓名, 学号（Student    （SC     (s授课教师=‘王敏’ (Course) ) ） ）

或  ∏姓名, 学号（ s授课教师=‘王敏’（ Student     SC  Course））

2)检索选修了课程号为C3或者C5的学生学号和成绩 ；      （3分）

（难度系数B）正确答案：

∏学号,成绩（ s课程号=‘C3’（ SC）∪∏学号（ s课程号=‘C5’（ SC）

(2)用SQL语句实现下列问题

1)写出创建S表的T－SQL语句 ；                           （5分）

（难度系数A）正确答案：

CREATE TABLE S

(  Sno CHAR(8)   PRIMARY KEY,

   Sname  CHAR(12) NOT NULL UNIQUE

,

   Ssex  CHAR(2)  CHECK(Ssex in('男','女')) DEFAULT '女',

   Sage  INT   CHECK(STage>=16 AND STage<=40) DEFAULT 21,

   Sdept CHAR(20)  DEFAULT ‘电子系’

);

2) 检索姓"李"且倒数第2个字为"华"或"涛"字的学生的姓名和学号；

（4分）

（难度系数A）正确答案：

SELECT Sname，Sno

FROM Student

WHERE Sname LIKE '李% [华涛] _ _'；

3)向学生选修课程表中插入元组“学生S6选修课程号C2”。     （2分）

（难度系数B）正确答案：

INSERT                                              

INTO  SC (Sno, Cno)

VALUES( ‘S6’,’ C2’ )

4) 删除选课成绩在60分以下的学生记录；                  （4分）

（难度系数A）正确答案：

DELETE

     FROM Student

WHERE  SNO IN

            (SELECT  SNO

             FROM     SC

             WHERE GRADE<60)；

或        

DELETE

          FROM Student

WHERE Student.SNO = SC.SNO AND SC.GRADE<60  ;

5) 把“大学英语”课程成绩提高5％；                     （3分）

（难度系数B）正确答案：

UPDATE  SC

SET   Grade=Grade*1.05

WHERE Cno IN

            (SELECT  Cno

             FROM   Course

WHERE  Cname=‘大学英语’)

6)求出男同学的每一年龄组（超过4人）有多少人？要求查询结果按人数降序排列，人数相同的按年龄升序排列；                                （5分）

（难度系数A）正确答案：

SELECT Sage, COUNT(Sno)

FROM Student

WHERE Ssex=‘男’

 

GROUP BY  Sage  HAVING  COUNT(Sno)>4

ORDER BY  2 DESC, Sage ASC                       //其中ASC也可以省略不写

7)定义视图S_age，其中包括学生学号以及每个学生选修课程的门数(要求成绩非空)和平均成绩；                                   （5分）

（难度系数A）正确答案：

CREATE VIEW  S_age  (Sno, Cno_num, Avg_grade)

AS  SELECT Sno,COUNT(Cno),AVG(grade)

     FROM SC  

     WHERE grade IS NOT NULL

GROUP BY Sno10、alter table student

add

jiguan char(20),

 

8)在S表中删除院系字段列。                              （2分）

（难度系数C）正确答案：

alter table student

drop column Sdept