数据库练习题.doc

一、关系运算1、设有两个关系R(A,B)和S(B,C),请写出与下面关系代数表达式等价的SQL语句。 B(R) -B(c=C56(S)2、已知关系R、S如下所示，以表的形式给出下列运算结果。 YZbcbbcd SXYabcdec R(1)X=Z(R S)(2) R S已知关系模式如下：学生：S(Sno,Sname,Ssex,Sage,Sdept) 对应学生的学号，姓名，性别，年龄，系；课程：C(Cno,Cname,Tname) 对应课程的课程号，课程名，授课教师名字；学生选课：SC（Sno,Cno,Grade）对应学生选修课程的成绩。3、检索至少选修两门课程的学生学号及姓名。4、查询王力同学不学课程的课程名。5、查询既选修了课程1也选修了课程2的学生学号。6、检索全部学生都选修的课程的课程号与课程名。7、查询刘宏老师所授课程的每门课程的学生平均成绩。8、为“三建”工程项目建立一个供应情况视图，包括SNO、PNO、QTY。9、试修改Student表结构，实现学生性别取值为男，女的约束。答案：1、 SELECT BFROM RWHERE B NOT IN(SELECT BFROM SWHERE C=C56）；2、 (bebb,cccc) ,(ccc)3、检索至少选修两门课程的学生学号及姓名。select sno,sname from student where sno in (select sno from sc group by sno having count(*)=2);4、查询王力同学不学课程的课程名。SELECT CNAME FROM CWHERE NOT EXISTS (SELECT*FROM SCWHERE SC.CNO=C.CNO AND SNO = (SELECT SNO FROM STUDENT WHERE SNAME= 王力);5、查询既选修了课程1也选修了课程2的学生学号。SELECT Sno FROM SC WHERE Cno=1 AND Sno IN (SELECT Sno FROM SC WHERE Cno=2);6、检索全部学生都选修的课程的课程号与课程名。select cno,cname from course where not exists (select * from student where not exists (select * from sc where sno=student.sno and cno=o);7、查询刘宏老师所授课程的每门课程的学生平均成绩。select cno,avg(grade) 平均成绩from scwhere cno in (select cno from course where Tname=刘宏)group by cno;8、为三建工程项目建立一个供应情况视图V-SPJ，包括SNO、PNO、QTY。 CREATE VIEW V-SPJ AS SELECT SNO ,PNO ,QTY FROM SPJ WHERE JNO = (SELECT JNO FROM J WHERE JNAME = 三建 ) 9、实现性别是男或女的Check约束.ALTER TABLE Student ADD CONSTRAINT Ssex_check CHECK (Ssex in (男,女);二、1、对于nvarchar数据类型，下列说法正确的是A. 最多可以存储长度为8000个汉字的数据 B. 最多可以存储长度为4000个汉字的数据C. 最多可以存储长度为2000个汉字的数据 D. 存储数据的大小没限制2、“图书”表中价格列是一个定点小数，小数点前3位，小数点后保留1位。下列类型中最合适的是A. numeric(3,1) B. numeric(4,1)C. numeric(5,1) D. float3、对存储邮政编码的字段（邮政编码均为6位数字），最适宜的数据类型是A.nchar(6) B.char(6)C.varchar(6) D.nvarchar(6)4、某字段的数据类型为nchar(8),存储字符串“database”占用的字节数为A.4 B.16C.2 D.85、有某存储成绩信息的字段，需存储的数据为“优”、“良”、“中”、“差”，则最适宜该字段的数据类型是A.char(1) B.nchar(1)C.varchar(1) D.nvarchar(1)6、设某字段的数据类型为nchar(10),存储字符串“演算法”占用的字节数是A.10 B.20C.6 D.37、查询电话字段开头字符串为”800”的记录，正确的SQL条件是：where 电话 likeA.800%_ B.800?C.800% D.800% 8、已知一个数据表的结构如下，表名为“Product”。列名 数据类型 长度 备注 描述ProductID char 12 主键 商品编号ProductName var char 50 商品名称ProductPrice float 商品价格ProductMemo Varchar 50 备注由于供应商对商品的批发价格进行调整，现需对现行商品价格进行调整，规则如下：原价格小于10元上调8%，原价格大于30元上调6%，其它上调7%，请编写一段SQL脚本完成上述功能。UPDATE Product SET ProductPrice = CASE WHEN ProductPrice 30 THEN ProductPrice*1.06ELSE ProductPrice*1.07END11、设有学生表：Student(Sno,Sname,Ssex,Sage,Sdept)， 课程表：Course(Cno,Cname,Credit,Semester)， 选课表：SC(Sno,Cno,Grade) 针对以上各表，用SQL语句完成以下操作。修改高等数学的考试成绩，修改规则如下：如果是计算机系学生，则加10分；如果是信息管理系学生则加5分；如果是数学系学生则分数不变。update sc set grade = grade + case sdept when 计算机系 then 10 when 信息管理系 then 5 when 数学系 then 0 end from sc join student s on s.sno = sc.sno join course c on o = o where cname = 高等数学12、为描述教师对课程的授课情况，设计了三张表：教师表、课程表和授课表，各表结构如下： 教师表：教师号：普通编码定长字符型，长度为8，主码。教师名：普通编码定长字符型，长度为10，不允许空。职称：普通编码定长字符型，长度为8，取值范围为：讲师，副教授，教授。所在部门：普通编码定长字符型，长度为20，默认值为“计算机学院”。课程表：课程号：普通编码定长字符型，长度为6，主码。课程名：普通编码定长字符型，长度为20，不允许空。学分：整型，允许空，取值范围为110。学期：整型，允许空。授课表：教师号：普通编码定长字符型，长度为8，不允许空。外键，引用“教师表”的“教师号”课程号：普通编码定长字符型，长度为6，不允许空。外键，引用“课程表”的“课程号”授课学期：普通编码定长字符型，长度为10，不允许空。 授课时数：整型。其中（教师号，课程号，授课学期）为主码。写出创建“教师表”、“课程表”和“授课表”的SQL语句，要求在创建表时定义表中的全部约束。Create table 教师表(教师号 char(8) primary key,教师名 char(10) not null,职称 char(8) check(职称 in (讲师,副教授,教授),所在部门 char(20) default 计算机学院)goCreate table 课程表(课程号 char(6) primary key,课程名 char(10) not null,学分 int check(学分 between 1 and 10),学期 int )goCreate table 授课表(教师号 char(8) not null,课程号 char(6) not null,授课学期 char(10) not null,授课时数 int,primary key(教师号,课程号,授课学期),foreign key(教师号) references 教师表(教师号),foreign key(课程号) references 课程表(课程号)三、1、已知图书表结构如下：条码码(char(5)，主键),书名(varchar(50),状态(char(1)，取值为1、2、3，分别表示：可借、不可借、已借。写出创建满足如下要求的视图（v_book）：查询书名中包含“数据库”的图书，显示：条形码、书名和状态，要求状态用对应的中文含义显示。create view vw_book asselect 条码号,书名,case when 状态=1 then 可借 when 状态=2 then 不可借 when 状态=3 then 已借 end from 图书 where 书名 like %数据库%;2、已知商品表、订单表、订单细节表、顾客表的结构如下所示：商品表：商品编号（char(5)，主键），商品名称（varchar(50)，非空），单价（numeric(10,2)，非空），库存数量（int，取值大于等于10）。顾客表：顾客编号（char(5)，主键），顾客姓名（varchar(50)），联系电话（char(6)，每位的取值均为0-9数字）。订单表：订单编号（char(10)，主键），下单日期（date，非空），顾客编号（char(5)，外键，引用顾客表的顾客编号）。订单细节：订单编号（char(10)，主键。外键，引用订单表的订单编号），商品编号（char(5)，主键。外键，引用商品表的商品编号），数量（int，默认值为1）。写出创建这四张表的SQL语句，要求考虑全部约束。create table 商品表( 商品编号 char(5) primary key, 商品名称 varchar(50) not null, 单价 numeric(10,2), 库存数量 int check (库存数量 = 10)；gocreate table 顾客表( 顾客编号 char(5) primary key, 顾客姓名 varchar(50), 联系电话 char(6) check(联系电话like 0-90-90-90-90-90-9)；通配符表示方括号中的某个字符gocreate table 订单表( 订单编号 char(10) primary key, 下单日期 date not null, 顾客编号 char(5) references 顾客(顾客编号)；gocreate table 订单细节表( 订单编号 char(10) references 订单(订单编号), 商品编号 char(5) references 商品(商品编号), 数量 int default 1, primary key(订单编号,商品编号)；go3、已知“图书借阅”表和“借阅历史”表的结构如下：图书借阅表：条码号char(10)主键,借阅日期Date,应还日期 Date.借阅历史表：序号int主键、自增，条码号char(10),借阅日期 Date,应还日期 Date,删除时间 Datetime默认值为系统当前时间. 请创建一个触发器，当从图书借阅表中删除数据时，将被删除的数据存放至借阅历史表。create trigger tri_book on 图书借阅 for delete as begin declare tiaoma char(10), bdate Date,rdate Date select tiaoma=条码号,bdate=借阅日期,rdate=应还日期 from deleted insert into 借阅历史(条码号，借阅日期，应还日期，删除时间) values(tiaoma, bdate ,rdate Date,GETDATE() end;4、为表C创建一个级联删除触发器TRIGGER_DC：通过课程名从C表中删除某课程信息，同时删除SC表中与此课程相关的选课记录 Create trigger TRIGGER_DC On C For DELETE As declare CNO_DEL CHAR(2) BEGIN Select CNO_DEL=CNO From DELETED DELETE FROM SC WHERE CNO=CNO_DEL END 5、设有学生表：Student(Sno,Sname,Ssex,Sage,Sdept)， 课程表：Course(Cno,Cname,Credit,Semester)， 选课表：SC(Sno,Cno,Grade)针对以上各表，写出不能将不及格成绩改为及格的后触发型触发器。create trigger tri4 on SC for upate as if exists(select * from inserted i join deleted d on i.sno = d.sno and o = o where i.grade