数据库原理第三章作业

合集下载

数据库第三章所有例题参考答案

11级信管，保密，图档上机考试题目与参考答案3.3Simple Select Statements1.EXAMPLE 3.3.1find the aid values and names of agents that are based in New York. select aid, aname from agents where city=’New York’;2.EXAMPLE3.3.3Retrieve all pid values of parts for which orders are placed.select distinct pid from orders;3.EXAMPLE 3.3.4retrieve all customer-agent name pairs, (cname, aname), where the customer places an order through the agent.select distinct ame,agents.anamefrom customers,orders,agentswhere customers.cid=orders.cid and orders.aid=agents.aid;4.EXAMPLE 3.3.6all pairs of customers based in the same city.select c1.cid, c2.cidfrom customers c1, customers c2where c1.city = c2.city and c1.cid < c2.cid;5.EXAMPLE 3.3.7find pid values of products that have been ordered by at least twocustomers.select distinct x1.pidfrom orders x1, orders x2where x1.pid = x2.pid and x1.cid < x2.cid;6.EXAMPLE 3.3.8Get cid values of customers who order a product for which an order is also placed by agent a06.select distinct y.cidfrom orders x, orders ywhere y.pid = x,pid and x.aid = ‘a06’;3.4Subqueries7.EXAMPLE 3.4.1Get cid values of customers who place orders with agents in Duluth or Dallas.select distinct cid from orderswhere aid in (select aid from agentswhere city= ‘Duluth’ or city = ‘Dallas’)8.EXAMPLE 3.4.2to retrieve all information concerning agents based in Duluth or Dallas (very close to the Subquery in the previous example).select * from agentswhere city in (‘Duluth’, ‘Dallas’ );or select *from agentswhere city = ‘Duluth’ or city = ‘Dallas’;9.EXAMPLE 3.4.3to determine the names and discounts of all customers who place orders through agents in Duluth or Dallas.select distinct cname, discnt from customerswhere cid in (select cid from orders where aid in(select aid from agents where city in (‘Duluth’, ‘Dallas’ ))); 10.EXAMPLE 3.4.4to find the names of customers who order product p05.select distinct cname from customers, orderswhere customers.cid = orders.cid and orders.pid = ‘p05’or select disti nct cname from customers where ‘p05’ in(select pid from orders where cid = customers.cid);11.EXAMPLE 3.4.5Get the names of customers who order product p07 from agent a03. select distinct cname from customerswhere cid in (select cid from orders where pid = ‘p07’ and aid = ‘a03’) 12.EXAMPLE 3.4.6to retrieve ordno values for all orders placed by customers in Duluth through agents in New York.select ordno from orders x where exists(select * from customers c, agents awhere c.cid = x.cid and a.aid = x.aid and c.city = ‘Duluth’ anda.city=‘New York’);13.EXAMPLE 3.4.7find aid values of agents with a minimum percent commission.select aid from agents where percent = (select min(percent) from agents);14.EXAMPLE 3.4.8find all customers who have the same discount as that of any of the customers in Dallas or Boston.select cid, cname from customerswhere discnt = some (select discnt from customerswhere city = ‘Dallas’ or city = ‘Boston’);15.EXAMPLE 3.4.9Get cid values of customers with discnt smaller than those of any customers who live in Duluth.select cid from customerswhere discnt <all (select discnt from customerswhere city = ‘Duluth’);16.EXAMPLE 3.4.10Retrieve all customer names where the customer places an order through agent a05.select distinct ame from customers cwhere exists (select * from orders xwhere c.cid = x.cid and x.aid = ‘a05’);or select distinct ame from customers c, orders xwhere c.cid = x.cid and x.ai d = ‘a05’ ;17.EXAMPLE 3.4.11Get cid values of customers who order both products p01 and p07. select distinct cid from orders xwhere pid = ‘p01’ and exsits (select * from orderswhere cid = x.cid and pid = ‘p07’);orselect distinct x.cid from orders x, orders ywhere x.pid = ‘p01’ and x.cid = y.cid and y.pid = ‘p07’;18.EXAMPLE 3.4.12Retrieve all customer names where the customer does not place an order through agent a05.select distinct ame from customers cwhere not exists (select * from orders xwhere c.cid = x.cid and x.aid = ‘a05’);19.EXAMPLE 3.4.13retrieving all customer names where the customer does not place an order through agent a05, but using the two equivalent NOT IN and <>ALLpredicates in place of NOT EXISTS.select distinct ame from customers cwhere c.cid not in (select cid from orders where aid = ‘a05’);or select ame from customers cwhere c.cid <>all (select cid from orders where aid = ‘a05’);20.EXAMPLE 3.4.14Find cid values of customers who do not place any order through agent a03.select distinct cid from orders xwhere not exists (select * from orderswhere cid = x.cid and aid = ‘a03’);orselect cid from customers cwhere not exists (select * from orderswhere cid = c.cid and aid = ‘a03’);21.EXAMPLE 3.4.15Retrieve the city names containing customers who order product p01. select distinct city from customers where cid in(select cid from orders where pid = ‘p01’);or select distinct city from customers where cid =some(select cid from orders where pid = ‘p01’);or select distinct city from customers c where exsits(select * from orders where cid = c.cid and pid = ‘p01’);or select distinct city from customers c, orders xwhere x.cid = c.cid and x.pid = ‘p01’;or select distinct city from customers c where ‘p01’ in(select pid from orders where cid = c.cid);3.5UNION Operators and FOR ALL Conditions 22.EXAMPLE 3.5.1to create a list of cities where either a customer or an agent, or both, is based.select city from customersunion select city from agents;23.EXAMPLE 3.5.2Get the cid values of customers who place orders with all agents based in New York.select c.cid from customers cwhere not exsits(select * from agents awhere a.city = ‘New York’ and not exsits(select * from orders xwhere x.cid = c.cid and x.aid = a.aid));24.EXAMPLE 3.5.3Get the aid values of agents in New York or Duluth who place orders forall products costing more than a dollar.select aid from agents awhere (a.city = ‘New York’ or a.city = ‘Duluth’)and not exsits(select p.pid from products pwhere p.price > 1.00 and not exsits(select * from orders xwhere x.pid = p.pid and x.aid = a.aid));25.EXAMPLE 3.5.4Find aid values of agents who place orders for product p01 as well as for all products costing more than a dollar.select a.aid from agents a where a.aid in(select aid from orders where pid = ‘p01’)and not exsits (select p.pid from products pwhere p.price > 1.00 and not exsits (select * from orders xwhere x.pid = p.pid and x.aid = a.aid));or select distinct y.aid from orders ywhere y.pid = ‘p01’ and not exsits(select p.pid from products pwhere p.price > 1.00 and not exsits(select * from orders xwhere x.pid = p.pid and x.aid = y.aid));26.EXAMPLE 3.5.6Find pid values of products supplied to all customers in Duluth.select pid from products pwhere not exsits(select c.cid from customers cwhere c.city = ‘Duluth’and not exists(select * from orders xwhere x.pid = p.pid and x.cid = c.cid));3.7 Set Functions in SQL27.EXAMPLE 3.7.1determine the total dollar amount of all orders.select sum(dollars) as totaldollars from orders28.EXAMPLE 3.7.2To determine the total quantity of product p03 that has been ordered. select sum(qty) as TOTAL from orders where pid=’p03’29.EXAMPLE 3.7.4Get the number of cities where customers are based.select count(distinct city) from customers30.EXAMPLE 3.7.5List the cid values of alt customers who have a discount less than the maximum discount.select cid from customerswhere discnt < (select max(discnt) from customers)31.EXAMPLE 3.7.6Find products ordered by at least two customers.select p.pid from products pwhere 2 <=(select count(distinct cid) from orders where pid=p.pid)图档的学生的上机考查的考题到此为止___________________________________________________________ ___________________________________________________________ 信管，保密的学生上机考查还包括下面的题目32.EXAMPLE 3.7.7Add a row with specified values for columns cid, cname, and city (c007, Windix, Dallas, null)to the customers table.insert into customers(cid, cname, city)values (‘c007’, ‘Windix’, ‘Dallas’)33.EXAMPLE 3.7.9After inserting the row (c007, Windix, Dallas, null) to the customers table in Example 3.7.7, assume that we wish to find the average discount of all customers.select avg(discnt) from customers3.8 Groups of Rows in SQL34.EXAMPLE 3.8.1to calculate the total product quantity ordered of each individual product by each individual agent.select pid, aid, sum(qty) as TOTAL from ordersgroup by pid, aid35.EXAMPLE 3.8.2Print out the agent name and agent identification number, and the product name and product identification number, together with the total quantity each agent supplies of that product to customers c002 and c003.select aname, a.aid, pname, p.pid, sum(qty)from orders x, products p, agents awhere x.pid = p.pid and x.aid = a.aid and x.cid in (‘c002’, ‘c003’)group by a.aid, a.aname, p.pid, p.pname36.EXAMPLE 3.8.3Print out all product and agent IDs and the total quantity ordered of the product by the agent, when this quantity exceeds 1000.select pid, aid, sum(qty) as TOTAL from ordersgroup by pid, aidhaving sum(qty) > 100037.EXAMPLE 3.8.4Provide pid values of all products purchased by at least two customers. select distinct pid from ordersgroup by pidhaving count(distinct cid) >= 23.9 A Complete Description of SQL Select38.EXAMPLE 3.9.1List all customers, agents, and the dollar sales for pairs of customers and agents, and order the result from largest to smallest sales totals. Retain only those pairs for which the dollar amount is at least equal to 900.00. select ame, c.cid, a.aname, a.aid, sum(dollars) as casalesfrom customers c, orders o, agents awhere c.cid = o.cid, and a.aid = o.aidgroup by ame, c.cid, a.aname, a.aidhaving sum(o.dollars) >= 900.00order by casales desc39.EXAMPLE 3.9.2listed the cid values of all customers with a discount less than the maximum discount.select cid from customerswhere discnt < (select max(discnt) from customers)40.EXAMPLE 3.9.3Retrieve the maximum discount of all customers.select max(discnt) from customers;select distinct discnt from customers cwhere discnt >= all (select discnt from customers dwhere d.cid<>c.cid)41.EXAMPLE 3.9.4Retrieve all data about customers whose cname begins with the letter “A”.select * from customers where cname like ‘A%’42.EXAMPLE 3.9.5Retrieve cid values of customers whose cname does not have a third letter equal to “%”.select cid from customers where cname not like ‘__[%]’43.EXAMPLE 3.9.6Retrieve cid values of customers whose cname begins “Tip_” and has an arbitrary number of characters following.select cid from customers where cname like ‘TIP\[_]%’44.EXAMPLE 3.9.7Retrieve cid values of customers whose cname starts with the sequence “ab\”.select cid from customers where cname like ‘ab\%’3.10 Insert, Update, and Delete Statements 45.EXAMPLE 3.10.1Add a row with specified values to the orders table, setting the qty and dollars columns null.insert into orders (ordno, month, cid, aid, pid)values (1107, ‘aug’, ‘c006’, ‘a04’, ‘p01’)46.EXAMPLE 3.10.2Create a new table called swcusts of Southwestern customers, and insert into it all customers from Dallas and Austin.create table swcusts (cid char(4) not null,cname varchar(13),city varchar(20),discnt real);insert into swcustsselect * from customerswhere city in (‘Dallas’, ‘Austin’)47.EXAMPLE 3.10.3Give all agents in New York a 10% raise in the percent commission they earn on an order.update agents set percent = 1.1 * percent where city = ‘New York’48.EXAMPLE 3.10.4Give all customers who have total orders of more than $1000 a 10% increase in the discnt.update agents set percent = 1.1 * discntwhere cid in(select cid from orders group by cid having sum(dollars) > 1000) 49.EXAMPLE 3.10.6Delete all agents in New York.delete from agents where city = ‘New York’50.EXAMPLE 3.10.7Delete all agents who have total orders of less than $600.Delete from agents where aid in(select aid from ordersGroup by aidHaving sum(dollars)<600)51.EXAMPLE 3.11.2Retrieve the names of customers who order products costing $0.50. delete from agents where aid in(select aid from orders group by aid having sum(dollars)<600)（完）。

数据库原理第三章第四章课后习题答案建库建表

数据库原理第三章第四章课后习题答案建库建表张敬怡12032316习题3建表和数据录入CREAT TABLE StudentSno Char(7) NOT NULL UNIQE,Sname VarChar(20) NOT NULL,Ssex Char(2) NOT NULL,Sage Smallint NULL,Clno Char(5) NOT NULL;INSERT INTO StudentV ALUES(‘2000101’, ‘李勇’, ‘男’, ‘20’, ‘00311’), (‘2000102’, ‘刘诗晨’, ‘女’, ‘19’, ‘00311’), (‘2000103’, ‘王一鸣’, ‘男’, ‘20’, ‘00312’), (‘2000104’, ‘张婷婷’, ‘女’, ‘21’, ‘00312’), (‘2001101’, ‘李勇敏’, ‘女’, ‘19’, ‘01311’), (‘2001102’, ‘贾向东’, ‘男’,‘22’, ‘01311’), (‘2001103’, ‘陈宝玉’, ‘男’, ‘20’, ‘01311’), (‘2001104’, ‘张逸凡’, ‘男’, ‘21’, ‘01311’); CREAT TABLE CourseCno Char(1) NOT NULL UNIQE,Cname VarChar(20) NOT NULL,Credit Smallint NULL;INSERT INTO CourseV ALUES(‘1’, ‘数据库’, ‘4’, ),(‘2’, ‘离散数学’, ‘3’, ),(‘3’, ‘管理信息系统’, ‘2’, ),(‘4’, ‘操作结构’, ‘4’, ),(‘6’, ‘数据处理’, ‘2’, ),(‘7’, ‘C语言’, ‘4’, );CREAT TABLE ClassClno Char(5) NOT NULL UNIQE,Speciality VarChar(20) NOT NULL,Inyear Char(4) NOT NULL,Number Integer NULL,Monitor Char(7) NULL;INSERT INTO ClassV ALUES(‘00311’, ‘计算机软件’, ‘2000’, ‘120’, ‘2000101’)，(‘00312’, ‘计算机应用’, ‘2000’, ‘140’, ‘2000103’)，CREAT TABLE GradeSno Char(7) NOT NULL,Cno Char(1) NOT NULL,Gmark Numberic(4,1) NULL;INSERT INTO GradeV ALUES(‘2000101’, ‘1’, ‘92’, ),(‘2000101’, ‘3’, ‘88’, ),(‘2000101’, ‘5’, ‘86’, ),(‘2000102’, ‘1’, ‘78’, ),(‘2000102’, ‘6’, ‘55’, ),(‘2000103’, ‘3’, ‘65’, ),(‘2000103’, ‘6’, ‘78’, ),(‘2000103’, ‘5’, ‘66’, ),(‘2000104’, ‘1’, ‘54’, ),(‘2000104’, ‘6’, ‘83’, ),(‘2001101’, ‘2’, ‘70’, ),(‘2001102’, ‘2’, ‘80’, ),(‘2001102’, ‘4’, ‘90’, ),(‘2000103’, ‘1’, ‘83’, ),(‘2000103’, ‘2’, ‘76’, ),(‘2000103’, ‘4’, ‘56’, ),(‘2000103’, ‘7’, ‘88’, );习题311题1.ALTER TABLE StudentADD Nation Varchar(20) NULL;2.ALTER TABLE StudentDROP COLUMN Nation;3.INSET INTO CourseV ALUES(‘2001110’, ‘3’, ‘80’);4.UPDATA Course SETCredit=70 WHERE Cno=‘2001110’;5.DELATE FROM CourseWHERE Cno=‘2001110’;6.CREATE INDEX IX_ClassON Student(Clno ASC);7.DROP INDEX Student IX_Class;1.SELECT DISTINCT CnoFROM Grade;2.SELECT*FROM StudentWHERE Ssex=女and Clno=’01311’;3.SELECT Sname,Ssex,(2014-Sage)as birthday FROM StudentWHERE Clno=’01311’OR Clno=’01312’;4.SELECT*WHERE Sname LIKE ‘李%’;5.SELECT NUMBERFROM ClassWHERE Clno=(SELECT ClnoFROM StudentWHERE Sname=李勇);6.SELECT A VG(Gmark)AS平均成绩MAX(Gmark) AS 最高分MIN(Gmark)AS 最低分FROM GradeWHERE Cno=（SELECT CnoFROM CourseWHERE Cname=操作系统）;7.SELECT Count(DISTINCT Sno)FROM Grade;8.SELECT Count(DISTINCT Sno)FROM GradeWHERE Sno=(SELECT SnoFROM GradeWHERE Cno=(SELECT CnoFROM CourseWHERE Cname=操作系统));9.SELET SnameFROM StudentWHERE Sno=(SELECT SnoWHERE Sno NOT IN (SELECT DISTINCT SnoFROM Grade))AND Clno=(SELECT Clno1.SELECT*FROM StudentWHERE Sname<>李勇AND Clno=(SELECT ClnoWHERE Sname=李勇);2.SELECT*FROM StudentWHERE Sname<>李勇AND Sno IN=(SELECT DISTINCT Sno FROM GradeWHERE Cno=ANY（SELECT Cno FROM GradeWHERE Sno=(SELECT SnoFROM StudentWHERE Sname=李勇)));3.SELECT*FROM StudentWHERE Sage BETWEEN(SELECT Sage FROM StudentWHERE Sname=李勇)AND 25;4.SELECT Sno,SnameFROM StudentWHERE Sno=(SELECT SnoFROM GradeWHERE Cno=(SELECT CnoFROM CourseWHERE Cname=操作系统));5.SELECT SnameFROM StudentWHERE Sno<>(SELECT SnoFROM GradeWHERE Cno=1);6.SELECT SnameWHERE NOT EXISTS(SELECT*FROM CourseWHERE NOT EXISTS (SELECT*FROM GradeWHERE Student Sno=Grade.SnoAND/doc/b21760812.html,o=/doc/ b21760812.html,o));14题1.SELECT Sno,GmarkFROM GradeWHERE Cno=3ORDER BY Gmark DESC;降序2.SELECT*FROM StudentORDER BY Clno,Sage DESC;3.SELECT Cno,COUNT(Sno)AS 选课人数FROM GradeGROUP BY Cno;4.SELECT SnoFROM GradeGROUP BY SnoHA VING COUNT(Sno)>3; 找出选修了三门以上课程的学生学号15题1.UPDATA GradeSET Gmark=0WHERE Sno IN(SELECT SnoFROM StudentWHERE Clno=‘01311’);2. DELETE*FROM GradeWHERE Sno IN(SELECT SnoFROM StudentWHERE Clno=(SELECT ClnoFROM ClassWHERE Speciality=计算机软件AND Inyear=2001));3.DELETE*FROMGradeWHERE Sno IN(SELECT SnoFROM StudentWHERE Sname='李勇')UPDATE ClassSET Number=Number-1WHERE Clno=(SELECT ClnoFROM StudentWHERE Sname='李勇')UPDATE ClassSET Monitor=NULLWHEN Monitor=(SELECT SnoFROM StudentWHERE Sname='李勇')DELETE FROM StudentWHERE Sname='李勇' ;4.ALTER TABLE ClassADD Cavg_age Smallint NULLUPDATE ClassUPDATE Clno='00311' THEN (SELECT A VG(Sage) FROM Student WHERE Clno='00311')，UPDATEClno='00312' THEN(SELECT A VG(Sage) FROM Student WHERE Clno='00312')，WHEN Clno='01311' THEN (SELECT A VG(Sage) FROM Student WHERE Clno='01311');16题1.CREAT VIEW Stu_01311_1AS SELECT*FROM StudentWHERE Sno=(SELECT SnoFROM GradeWHERE Cno=1);2.CREAT VIEW Stu_01311_2AS SELECT FROM StudentWHERE Sno=(SELECT SnoFROM GradeWHERE Cno=1AND Gmark<60);3.CREAT VIEW Stu_yearAS SELECT Sno,Sname,(2014-Sage)AS Birth_yearFROM Student;4.SELECT SnameFROM VIEW Stu_yearWHERE Birth_year>1990;5.SELECT Sno,Sname,Birth_yearFROM VIEW Stu_yearWHERE Sno=(SELECT SnoFROM VIEW Stu_01311_2);习题4建表和完整性约束CREAT TABLE Student( Sno Char(7) NOT NULL UNIQE PRIMARY KEY,Sname VarChar(20) NOT NULL,Ssex Char(2) NOT NULL CHECK(Sex IN (‘男’，‘女’))Sage Smallint NULL CHECK(Age>=14 AND Age<=65),Clno Char(5) NOT NULL REFERENCES Class(Clno)ON UPDATE CASCADE );CREAT TABLE Course( Cno Char(1) NOT NULL UNIQE PRIMARY KEY,Cname VarChar(20) NOT NULL,Credit Smallint NULL CHECK(Redit IN(‘1’,‘2’,‘3’,‘4’,‘5’,‘6’)) );CREAT TABLE Class( Clno Char(5) NOT NULL UNIQE PRIMARY KEY,Speciality VarChar(20) NOT NULL,Inyear Char(4) NOT NULL,Number Integer NULL CHECK(Number>=1AND Number<=100),Monitor Char(7) NULL REFERENCES Student(Sno) );CREAT TABLE Grade( Sno Char(7) NOT NULL,Cno Char(1) NOT NULL,Gmark Numberic(4,1) NULL CHECK(Mark>=0 AND Mark<=100)PRIMARY KEY(Sno,Cno)FOREIGN KEY Sno REFERENCES Student(Sno)ON DELETE CASCADEON UPDATE CASCADEFOREIGN KEY Cno REFERENCES Course(Cno)ON DELETE CASCADEON UPDATE CASCADE );PS：王老师我笔记本里装不了SQL的软件所以只是把代码写出来了没运行过......。

数据库系统原理第三章同步练习

性。
8. 消除了非主属性对候选键局部依赖的关系模式， 9. 两个函数依赖集F和G等价的充分必要条件是
10. 消除了每一属性对候选键传递依赖的关系模
式称为 BCNF 模式
11. 一个关系模式属于 4NF ，它必定属于BCNF。
A. 互不相关的
B. 不可分解的
C. 长度可变的
D. 互相关联的
6. 假设关系模式R(A,B)属于3NF，下列说法（ B ）
是正确的
A. 它一定消除了插入和删除异常
B. 仍存在一定的插入和删除异常
C. 一定属于BCNF
D. A和C
7. 设有关系W(工号, 姓名, 工种, 定额), 将其规范
化到第三范式正确的答案是（ C ）
1NF变成了3NF
A. 局部函数依赖和传递函数依赖
B. 完全函数依赖和传递函数依赖
C. 完全函数依赖
D. 局部函数依赖
13. 下述说法正确的是（ D ）
A. 属于BCNF的关系模式不存在存储异常
B. 函数依赖可由属性值决定，不由语义决定
C. 超键就是候选键
D. 键是唯一能决定一个元组的属性或属性组
一、单项选择题
1. 当B属性函数依赖于A属性时，属性A与B的联
系是（B ）
A. 一对多
C. 多对多
B. 多对一
C. 以上都不是
2. 关系模式R中的属性全部是主属性，则R的最高
范式必定是（B ）
A. 2NF
B. 3NF
C. BCNF
D. 4NF
3. 在关系模式R(A,B,C,D)中，有函数依赖集F={
Z=U-X-Y，则 X →→Z
5. 若关系模式R已属于第一范式，且其中的每一

数据库第三章习题参考答案

三、设计题1.(1)SELECT BAuth FROM Book, PublishWHERE Book.PNo= Publish.PNo AND BName=’操作系统’ AND PName=’高等教育出版社’(2)查找为作者“张欣”出版全部“小说”类图书的出版社的电话。

SELECT PTel FROM Book, PublishWHERE Book.PNo= Publish.PNo AND BType =’小说’ AND BAuth=’张欣’(3)查询“电子工业出版社”出版的“计算机”类图书的价格，同时输出出版社名称及图书类别。

SELECT BPrice, PName, BType FROM Book, PublishWHERE Book.PNo= Publish.PNo AND PName =’电子工业出版社’ AND BType =’计算机’(4)查找比“人民邮电出版社”出版的“高等数学”价格低的同名书的有关信息。

SELECT * FROM BookWHERE BName =’高等数学’AND BPrice<ANY(SELECT BPrice FROM Book,PublishWHERE Book.PNo= Publish.PNo AND PName =’人民邮电出版社’ AND BName =’高等数学’）AND PName <>’人民邮电出版社’(5)查找书名中有“计算机”一词的图书的书名及作者。

SELECT BName, BAuth FROM BookWHERE BName LIKE’%计算机%’(6)在“图书”表中增加“出版时间”（BDate）项，其数据类型为日期型。

ALTER TABLE BookADD BDate datetime(7)在“图书”表中以“作者”建立一个索引。

CREATE INDEX Name ON Book(BAuth) desc2.(1)建立存书表和销售表。

【精选】数据库第三章课后习题

order by grade
• 14、 • (1)GRANT SELECT ON 职工,部门TO 王明 • (2) GRANT INSERT,DELETE ON 职工,部门TO
李勇
• (3) GRANT SELECT ON 职工WHEN USER() = NAME TO ALL
• (4) GRANT SELECT,UPDATE(工资) ON 职工 TO 刘星
• 7、视图的优点 • 视图能够简化用户的操作 • 视图使用户能以多种角度看待同一数据 • 视图对重构数据库提供了一定程度的逻辑
独立性； • 视图能够对机密数据提供安全保护。
• 8、所有的视图是否都可以更新？
• 不是。视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。因为有些视图的更新不能惟一有意义地转换成对相应基本表的更新，所以，并不是所有的视图都是可更新的。
SPJ TO 李天明；
• 13、 • （1）INSERT INTO SC(Sno,Cno,Grade)
VALUES("2000012", "1128", NULL); • （2）SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;
• （3）SELECT cname,grade FROM course,Sc WHERE o=o AND cname="英语"
• (1) SELECT DIST PNO,QTY FROM SPQ
• (2) SELECT DIST * FROM SPQ WHERE SNO="S1‘
• 12、 • （1）GRANT INSERT
ON TABLE S TO 张勇

数据库原理第三章练习

第三章SQL语言一、选择题：1、SQL语言是的语言，易学习。

A．过程化 B．非过程化C．格式化 D．导航式2、SQL语言是语言。

A．层次数据库 B．网络数据库C．关系数据库 D．非数据库3、SQL语言具有的功能。

2、关系规范化，数据操纵，数据控制B．数据定义，数据操纵，数据控制C．数据定义，关系规范化，数据控制D．数据定义，关系规范化，数据操纵4、SQL语言具有两种使用方式，分别称为交互式SQL和。

A．提示式SQL B．多用户SQLC．嵌入式SQL D．解释式SQL5、SQL语言中，实现数据检索的语句是。

A．SELECT B．INSERTC．UPDATE D．DELETE6、下列SQL语句中，修改表结构的是。

A．ALTER B．CREATEC．UPDATE D．DELETE7、SQL中，与“NOT IN”等价的操作符是。

A．=SOME B．<>SOMEC．=ALL D．<>ALL8、假设有三个基本表：学生表S、课程表C、学生选课表SC，它们的结构如下：S（S#，SN，SEX，AGE，DEPT）C（C#，CN）SC（S#，C#，GRADE）检索所有比“王华”年龄大的学生姓名、年龄和性别。

正确的SQL语句是。

A．SELECT SN，AGE，SEXFROM SWHERE AGE>(SELECT AGE FROM SWHERE SN=”王华”)B．SELECT SN，AGE，SEXFROM SWHERE SN=”王华”C．SELECT SN，AGE，SEXFROM SWHERE AGE>(SELECT AGEWHERE SN=”王华”)D．SELECT SN，AGE，SEXFROM SWHERE AGE>王华.AGE9、检索选修课程”C2”的学生中成绩最高的学生的学号。

正确的SELECT语句是。

A．SELECT S#FROM SCWHERE C#=”C2” AND GRADE>=(SELECT GRADE FROM SCWHERE C#= “C2”)B．SELECT S#FROM SCWHERE C#=”C2” AND GRADE IN(SELECT GRADE FROM SCWHERE C#= “C2”)C．SELECT S#FROM SCWHERE C#=”C2” AND GRADE NOT IN(SELECT GRADE FROM SCWHERE C#= “C2”)D．SELECT S#FROM SCWHERE C#=”C2” AND GRADE>=ALL(SELECT GRADE FROM SCWHERE C#= “C2”)10、检索学生姓名及其所选修课程的课程号和成绩。

数据库第三章习题参考答案范文大全

数据库第三章习题参考答案范文大全第一篇：数据库第三章习题参考答案3-2 对于教务管理数据库的三个基本表S(SNO,SNAME, SEX, AGE,SDEPT) SC(SNO,CNO,GRADE)C(CNO,CNAME,CDEPT,TNAME) 试用SQL的查询语句表达下列查询：⑴ 检索LIU老师所授课程的课程号和课程名。

⑵ 检索年龄大于23岁的男学生的学号和姓名。

⑶ 检索学号为200915146的学生所学课程的课程名和任课教师名。

⑷ 检索至少选修LIU老师所授课程中一门课程的女学生姓名。

⑸ 检索WANG同学不学的课程的课程号。

⑹ 检索至少选修两门课程的学生学号。

⑺ 检索全部学生都选修的课程的课程号与课程名。

⑻ 检索选修课程包含LIU老师所授课程的学生学号。

解：⑴ SELECT C#,CNAME FROM C WHERE TEACHER=’LIU’; ⑵ SELECT S#,SNAME FROM S WHERE AGE>23 AND SEX=’M’; ⑶ SELECT CNAME，TEACHER FROM SC,C WHERE SC.C#=C.C# AND S#=’200915146’ ⑷ SELECT SNAME (连接查询方式) FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C# AND TEACHER=’LIU’;或：SELECT SNAME (嵌套查询方式) FROM S WHERE SEX=’F’AND S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE TEACHER=’LIU’)) 或：SELECT SNAME (存在量词方式)SEX=’F’ AND FROM S WHERE SEX=’F’ AND EXISTS(SELECT* FROM SC WHERE SC.S#=S.S# AND EXISTS(SELECT * FROM C WHERE C.C#=SC.C# AND TEACHER=’LIU’)) ⑸ SELECT C# FROM C WHERE NOT EXISTS(SELECT * FROM S,SC WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=’WANG)); ⑹ SELECT DISTINCT X.S# FROM SC AS X，SC AS Y WHERE X.S#=Y.S# AND X.C#!=Y.C#; ⑺ SELECT C#.CNAME FROM C WHERE NOT EXISTS (SELECT * FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE S#=S.S# AND C#=C.C#)); ⑻ SELECT DISTINCT S# FROM SC AS X WHERE NOT EXISTIS (SELECT * FROM C WHERE TEACHER=’LIU’ AND NOT EXISTS (SELECT * FROM SC AS Y WHERE Y.S#=X.S# AND Y.C#=C.C#)); 3-3 试用SQL查询语句表达下列对3.2题中教务管理数据库的三个基本表S、SC、C查询：⑴ 统计有学生选修的课程门数。

数据库原理与应用77页第三章习题3

完成第三章课后第2、3、4题，写相关SQL程序，提交数据库备份文件及SQL程序代码，（如不提交附件）可直接粘贴代码提交1、设有一图书馆数据库，包括三个表：图书表、读者表、借阅表。

三个表的结构如表3-3、表3-4和表3-5所示。

完成以下习题。

表3-3 图书表结构列名说明数据类型约束图书图书唯一的图书号定长字符串，长度主键号为20书名图书的书名变长字符串，长度为50空值作者图书的编著者名变长字符串，长度为30空值出版社图书的出版社变长字符串，长度为30空值单价出版社确定的图书的单价浮点型，FLOAT空值表3- 4 读者表结构列名说明数据类型约束说明读者号读者唯一编号定长字符串，长度为10主键姓名读者姓名定长字符串，长度为8非空值性别读者性别定长字符串，长度为2非空值办公电话读者办公电话定长字符串，长度为8空值部门读者所在部门变长字符串，长度为30空值表3- 5 借阅表结构列名说明数据类型约束说明读者号读者的唯一编号定长字符串，长度为10外码，引用读者表的主键图书号图书的唯一编号定长字符串，长度为20外码，引用图书表的主键借图书借定长字符非空值出日期出的日期串，长度为8，为’yymmdd’归还日期图书归还的日期定长字符串，长度为8，为’yymmdd’空值主键为：(读者号, 图书号)（1）用SQL语句创建图书馆数据库。

C读者表te database 图书馆数据库；（2）用SQL语句创建上述三个表。

use图书馆数据库Goc读者表te table图书表(图书号char (20)primary key,书名varchar (50),作者varchar (30),出版社varchar (30),单价float);use图书馆数据库goc读者表te table读者表(读者号char (10)primary key,姓名char (8)not null,性别char (2)not null,办公电话char (8),部门char(30));use图书馆数据库gocreate table借阅表(读者号char (10)not null,图书号char (20)not null,借出日期char (8)not null,归还日期char (8),foreign key (读者号)references 读者表(读者号),foreign key (图书号)references 图书表(图书号));（3）基于图书馆数据库的三个表，用SQL语言完成以下各项操作：1）给图书表增加一列“ISBN”，数据类型为CHAR(10)alter table图书表add ISBN CHAR(10);2）为刚添加的ISBN列增加缺省值约束，约束名为ISBNDEF，缺省值为’7111085949’；ALTER TABLE图书表ADD CONSTRAINT ISBNDEF DEFAULT'7111085949'FOR ISBN; 3）为读者表的’办公电话’列，添加一个CHECK约束，要求前五位’88320’，约束名为CHECKDEF。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

数据库原理第三章作业
学号:20112730
姓名：陈佳宇
一. 简答题
1.简述表间数据完整性的实现方式？
⑴外键约束，即在从表上定义外键约束。

⑵利用触发器，即主表的触发器维护主表到从表方向的数据完整性，从表的触发器维护从表到主表方向的参照完整性。

2.简述主键约束的要求？
主键约束针对主键而言，并且值唯一，不能为空值。

3.简述唯一限制的要求？
唯一约束针对候选键而言，并且值唯一，允许有且只有一个空值。

4.SQL-92标准支持的完整性限制是否一定会在SQL SERVER中实现，举例说明？不一定。

比如SOL SERVER不支持“断言”，虽然SOL-92标准是支持的。

5.SQL SERVER中规则的目的？
指明表中某一列的取值范围。

6.SQL SERVER中在定义某些限制时，分列级与表级，其分类的原则是什么？列级方式是：在要定义约束的列本身定义完后，紧接其后定义其约束。

表级方式：表中所有的列都定义往后，再定义所要的约束。

7.简述外键约束限制定义的条件？
定义外键的列必须是另一个表中的主键或候选键。

8.一张表上可定义的触发器个数是多少个？
答：12个
9.关系代数的基本操作符？
selection(选择) ：σ
projection(投影)：π
union（并）
intersection（交）
difference（差）
cross-product（积）
10.关系代数中对结果有重复元组时，如何处理？
只保留其中一个，另外的被去掉。

11.简述联结的分类？
条件连接连接
自然连接
外连接：左外连接，右外连接，全外连接
12.简述关系运算的种类？
主要有两种，一：元组关系运算，二：域关系运算
二. 单项选择题
1. （ A ）不是关系代数的基本操作。

ASelection BProjection CJoin DIntersection 2. （ C ）用唯一限制来约束。

A主键B外键C候选键D简单键
3. （ B ）与“列”不同义。

A字段B元组C域D属性
三. 判断题（正确打√，错误打×）
1. （√）关系代数中的改名操作既可用于改名也可用于存放临时关系模式结果。

2. （×）对主表，插入操作可能会违背参照完整性限制，但删除和更新不会。

3. （×）等连接是自然连接的特例.
4. （√）关系代数是与关系模型有关的查询语言。

四. 设有三个关系
S（Sid(学号)，Sname(姓名)，Age(年龄)，Sex(性别)）
SC（Sid(学号)，Cid(课程号)，Score(成绩)）
C（Cid(课程号)，Cname(课程名)，Teacher(教师)）
试用关系代数表达式表示下列查询语句，并且写出前四个的SQL查询语句：
①检索LIU老师所授课程的课程号和课程名。

表达式：πCid,Cname(σTeacher=’LIU’（C）)
对应SQL：select Cid as 课程号，Cname as 课程名from C
Where Teacher=’LIU’.
②检索年龄大于23岁的男学生的学号和姓名。

表达式：πSid,Sname(σAge>23（S）)
对应SQL：select Sid as 学号，Sname as 姓名from S
Where Age>23.
③检索学号为S3学生所学课程的课程名与任课教师名。

表达式：πCid,Teacher(σSid=S3（S C） C )。

对应SQL：select Cid 课程名,Teacher 任课教师名from SC,C
Where SC.Sid=S3 and SC.Cid=C.Cid
④检索至少选修LIU老师所授课程中一门课的女学生姓名。

表达式：πSname （σTeacher=LIU（S C）σSex=’女’（S）C）。

⑤检索W ANG同学不学的课程的课程号。

表达式：πCid（SC-（σSname=’WANG’（S）SC））
⑥检索至少选修两门课的学生学号。

表达式：p(temp1,πCid,Sid,Sname（S SC）)
P（temp2（1→sid1，2→Sname，3→Cid1，4→Sid2，5→Sname2，6→Cid2），temp1×temp1）
π,Sname1（σSid1=Sid2^Cid1≠Cid2（temp2））
⑦检索全部学生都选修的课程的课程号与课程名。

表达式：πCid,Cname（πCid,Sid（SC）/πCid（C）S）
⑧检索选修课程包含LIU老师所授全部课程的学生学号。