数据库第三章同步练习
数据库第三章所有例题参考答案
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)（完）。
数据库第三章习题参考
5.求至少用了供应商S1所供应的全部零件的工程号JNO。 即查找：不存在这样的零件y，供应商S1供应了y，而工程x为选用y。 Select distinct jno From spj z Where not exists (select * from spj x where sno=‘S1’ and not exists (select * from spj y where y.pno=x.pno and y.jno=z.jno));
习题三 第5题
1. 找出所有供应商的姓名及其所在城市。 Select sname, city from s; 2. 找出所有零件的名称、颜色、重量。 Select pname, color, weight from p; 3.找出使用供应商S1所供应零件的工程项目代码。 Select jno from spj where sno=‘S1’;
7. 找出没有使用天津产的零件的工程项目代码。 Select jno from j where not exists (Select * from spj where spj.jno=j.jno and sno in (Select sno from s where city=‘天津’) );
3.求供应工程J1零件为红色的供应商号码。 Select sno from spj, p Where spj.pno=p.pno and jno=‘J1’ and color=‘红’; 或： Select sno from spj Where jno =‘J1’ and pno in (Select pno from p Biblioteka where color=‘红’ );
6. 找出使用上海产的零件的工程项目名。 Select jname from j,spj,s where j.jno=spj.jno and spj.sno=s.sno and s.city=‘上海’; 或： Select jname from j where jno in (Select jno from spj, s where spj.sno=s.sno and s.city=‘上海’);
数据库系统原理第三章同步练习
性。
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)建立存书表和销售表。
【精选】数据库第三章课后习题
• 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 张勇
2020年计算机二级《MySQL》数据库章节练习题及答案
2020年计算机二级《MySQL》数据库章节练习题及答案第三章数据库和表1[单选题]在MySQL中，通常使用________语句来指定一个已有数据库作为当前工作数据库。
INGEDESE参考答案：D2[简答题]请使用MySQL命令行客户端在MySQL中创建一个名为db_test的数据库。
参考解析：在MySQL命令行客户端输入如下SQL语句即可实现：mysql>CREATE DATABASE db_test;Query OK，1 row affected(0.05 see)3[简答题]列名name修改为username参考解析：alter table user change column name username varchar(20);4[单选题]设置表的默认字符集关键字是( )A.DEFAULT CHARACTERB.DEFAULT SETC.DEFAULTD.DEFAULT CHARACTER SET参考答案：D5[填空题]在创建数据库时，能够使用( )子句确保如果数据库不存有就创建它，如果存有就直接使用它。
参考解析：IF NOT EXISTS6[简答题]创建数据库sxcj，引擎采用InnoDB,在sxcj中创建表xs包含字段如下字段名称数据类型说明snoint主键snameChar(8)名字ZhuanyemingChar(10)专业名sexChar(1)性别sbirdate生日photoblob照片commenttext注释参考解析：create database sxcj;use sxcjcreate table xs( sno int not null auto_increment primary key ,sname char(8) not null,zhuanyeming char(10) null,sex char(1) not null,sbir date not null,photo blob null,comment text null)engine=InooDB;7[填空题] 在CREATE TABLE语句中，通常使用________关键字来指定主键。
数据库第三章习题答案
第3章习题参考答案3.1select readername,workunit,identitycardfrom readerwhere substring(identitycard,7,4)=‘1991’///字符串截取substr(字段名，起始点,个数) 或者select readername,workunit,identitycardfrom readerwhere identitycard like ‘______1991%’六个_3.2select readerno,readername,sexfrom readerwhere workunit=’信息管理学院’3.3select readerno,readername, workunit,bookno,bookname,borrowdatafrom reader,borrow,bookwhere reader.readerno=borrow.reader and borrow.bookno=book.booknoand year(returndata) between 2005 and 2008 and ifreturn =03.4select a.classno,max(price) 最高价格,avg(price) 平均价格from book a,bookclass bwhere a.classno=b.classnogroup by a.classnoorder by 最高价格desc3.5 select * from book where bookname like ‘%数据库%’3.6 select bookno,publishingdata,shopdata,booknameFrom bookWhere year(shopdata) between 2005 and 20083.7 select readerno,readernameForm readerWhere readerno not in (select distinct readerno from borrow where bookno like ‘001%’)3.8 select readerno,bookno,borrowdataForm borrowWhere bookno=’001-000029’3.9 select readernameForm readerWhere readerno not in (select distinct readerno from borrow)3.10 select classname,count（distinct book.classno），sum（shopnum）From book,bookclassWhere book.classno=’001’ and book.classno=bookclass.classnoGroup by book.classno3.11 select classname,sum(shopnum)From book,bookclassWhere book.classno=bookclass.classnoGroup by book.classno3.12 select a.readerno,readername,borrowdata,booknameFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoAnd b.readerno in (select readerno from borrowwhere bookno in(select bookno from bookwhere bookname=’离散数学’)) And b.readerno in (select readerno from borrowwhere bookno in(select bookno from bookwhere bookname=’数据库’))3.13 select a.readerno,readername,borrowdata,booknameFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoAnd not exists (select * from book where bookno=’002’And not exi s ts (select * from borrowwhere book.bookno=borrow.bookno))3.14 select b.bookno,bookname.borrowdata,returndataFrom reader a,borrow b,book cWhere a.readerno=b.readerno and b.bookno=c.bookno and a.readername=’马永强’3.15 select a.readerno,readername,borrowdata,bookname，returndataFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoand b.workunit=’会计学院‘and c.ifreturn=03.16 select a.readerno,readername,borrowdata,bookname，returndataFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoand a.publishingname=’清华大学出版社‘3.17 select readerno,readername,workunitFrom readerWhere not exist(select * from borrow where reader.readerno=borrow.readerno)3.18 select a.readerno,readername,a.bookno,booknameFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoAnd b.readerno in (select readerno from borrowgroup by readernohaving count(*)>=3)order by a.readerno3.19 select a.readerno,readername,a.bookno,booknameFrom borrow a,reader b , book cWhere a.readerno=b.readerno and a.bookno=c.booknoAnd year(borrowdate) between 2007 and 20083.20 select readerno,readername,workunitFrom readerWhere not exist s(select * from readerwhere readername=’马永强’and not exist s(select * from borrow where reader.readerno=borrow.readerno))3.21 select a.readerno,readername,sum(price)From borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknoAnd b.readerno in (select readerno from borrowgroup by readernohaving sum(price)>150)group by a.readerno,readername3.22 select readerno,readername, substring(identitycard,7,4)From readerWhere readerno not in(select readerno from borrow,book,bookclass where book.bookno=borrow.bookno and bookclass.classno=book.classno and bookclass.classname=’经济管理’)3.23 select a.readerno,readername, substring(identitycard,7,4)From borrow a,reader b , book cWhere a.readerno=b.readerno and a.boono=c.booknogroup by a.readernohaving sum(price)=(select max(sumprice) from (select sum(price) sumpricefrom borrowgroup by readerno) d)3.24 update bookSet price=price+price*0.1 (set price=price*1.1)From book,bookclassWhere book.classno=bookclass.classno and classname=’经济管理’3.28 create view view1AsSelect book.* from book,bookclassWhere book.classno=bookclass.classno and publishingname=’清华大学出版社’and year(publishingdate) between 2008 and 2009 and classname=’计算机类’补充内容--【字符串函数】--字符串截取substr(字段名，起始点,个数)select Name,substr(Name,2,4),substr(Name,0,3),substr(Name,-2,3),substr(Name,-2,1) from t1;--字符串从前面取三个(0开始)select Name,substr(Name,0,3) from t1;--字符串从后面取三个select Name,substr(Name,-3,3),length(Name) 串长度 from t1;SELECT ASCII('A'),ASCII('B') from dual;select CHR(100),CHR(80) from dual;select CONCAT(CHR(65),CONCAT(CHR(67),CHR(98))) from dual;select CHR(65)||CHR(66)||CHR(76) from dual;--将每个单词的第一个字母大写其它字母小写返回。
数据库第三章习题参考答案范文大全
数据库第三章习题参考答案范文大全第一篇：数据库第三章习题参考答案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查询：⑴ 统计有学生选修的课程门数。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第三章一、选择题1、可以随表的打开而自动打开的索引是CA）单项索引文件B）复合索引文件C）结构化复合索引文件D）非结构化复合索引文件2、“主键”不允许取重复值，是指AA）实体完整性约束规则B）引用完整性约束规则C）用户自定义完整性约束规则D）数据完整性约束规则3、下列关于索引的叙述中，不正确的是DA）Visual FoxPro支持两种索引文件：单一索引文件和复合索引文件B）打开和关闭索引文件均使用SET INDEX TO命令C）索引的类型有主索引、候选索引、惟一索引和普通索引D）索引文件不随库文件的关闭而关闭4、自由表中字段名长度的最大值是BA）8B）10C）128D）2555、要同时打开多个数据表文件，选择不同的工作区可使用的命令是CA）USEB）OPENC）SELECTD）以上命令均可6、在Visual FoxPro中，以共享方式打开数据库文件的命令短语是BA）EXCLUSIVEB）SHAREDC）NOUPDATED）VALIDATE7、下列索引中，不具有“惟一性”的是DA）主索引B）候选索引C）惟一索引D）普通索引8、如要设定学生年龄有效性规则在18至20岁之间，当输入的数值不在此范围内，则给出错误信息，我们必须定义BA）实体完整性B）域完整性C）参照完整性D）以上各项都需要定义9、命令SELECT 0的功能是AA）选择编号最小的空闲工作区B）选择编号最大的空闲工作区C）随机选择一个工作区的区号D）无此工作区，命令错误10、假设工资表中按基本工资升序索引后，并执行过赋值语句N=800，则下列各条命令中，错误的是BA）SEEK N B）SEEK FOR 基本工资=NC）FIND 1000 D）LOCATE FOR 基本工资=N11、下列关于自由表的说法中，错误的是CA）在没有打开数据库的情况下所建立的数据表，就是自由表B）自由表不属于任何一个数据库C）自由表不能转换为数据库表D）数据库表可以转换为自由表12、在Visual FoxPro中，可以同时打开表文件的个数最多是BA）16C）系统会弹出“保存”对话框，请用户输入数据库名并保存D）出错信息18、ABC.DBF是一个具有两个备注型字段的数据表文件，若使用COPY TO TEMP命令进行复制操作，其结果是BA）得到一个新的数据表文件B）得到一个新的数据表文件和一个新的备注文件C）得到一个新的数据表文件和两个新的备注文件D）错误信息，不能复制带有备注型字段的数据表文件19、要将数据库表从数据库中移出成为自由表，可使用命令BA）DELETE TABLE <数据表名>B）REMOVE TABLE <数据表名>C）DROP TABLE <数据表名>D）RELEASE TABLE <数据表名>20、在Visual FoxPro中，数据库文件和数据表文件的扩展名分别是DA）.DBF和.DCTB）.DBC和.DCTC）.DBC和.DCXD）.DBC和.DBF21、建立一个表文件，表中包含字段：姓名（C，6）、出生日期（D）和婚否（L），则该表中每条记录所占的字节宽度为BA）15B）16C）17D）1822、在Visual FoxPro中，可以对字段设置默认值的表是 BA）自由表B）数据库表C）自由表或数据库表D）都不能设置23、利用SET RELATION命令可以建立两个表之间的关联，该关联是BA）永久性联系B）临时性联系C）任意的联系D）以上说法均不正确24、假设表中共有10条记录，执行下列命令后，屏幕所显示的记录号顺序DUSE ABC.dbfGOTO 6LIST NEXT 5A）1~5B）1~6C）5~10D）6~1025、惟一索引的“惟一性”是指CA）字段值的“惟一”B）表达式的“惟一”C）索引项的“惟一”D）列属性的“惟一”26、下面有关索引的描述正确的是CA) 建立索引以后，原来的数据库表文件中记录的物理顺序将被改变B) 索引与数据库表的数据存储在一个文件中C) 创建索引是创建一个指向数据库表文件记录的指针构成的文件D) 使用索引并不能加快对表的查询操作27、在Visual FoxPro中，调用表设计器建立数据库表STUDENT.DBF的命令是CA) MODIFY STRUCTURE STUDENTB) MODIFY COMMAND STUDENTC) CREATE STUDENTD) CREATE TABLE STUDENT28、在Visual FoxPro中，关于自由表叙述正确的是BA) 自由表和数据库表是完全相同的B) 自由表不能建立字段级规则和约束C) 自由表不能建立候选索引D) 自由表不可以加入到数据库中29、在Visual FoxPro中，建立数据库表时，将年龄字段值限制在12～40岁之间的这种约束属于BA) 实体完整性约束B) 域完整性约束C) 参照完整性约束D) 视图完整性约束30、在Visual FoxPro中，学生表STUDENT中包含有通用型字段，表中通用型字段中的数据均存储到另一个文件中，该文件名为DA) STUDENT.DOC B) STUDENT.MENC) STUDENT.DBT D) STUDENT.FTP31、在Visual FoxPro中，建立索引的作用之一是CA) 节省存储空间B) 便于管理C) 提高查询速度D) 提高查询和更新的速度32、在Visual FoxPro中，相当于主关键字的索引是AA) 主索引B) 普通索引C) 唯一索引D) 排序索引33、在Visual FoxPro 中，创建一个名为SDB.DBC的数据库文件，使用的命令是DA) CREATE B)CREATE SDBC) CREATE TABLE SDB D) CREATE DATABASE SDB34、在Visual FoxPro中，存储图象的字段类型应该是BA) 备注型B) 通用型C) 字符型D) 双精度型35、为了设置两个表之间的数据参照完整性，要求这两个表是AA) 同一个数据库中的两个表B) 两个自由表C) 一个自由表和一个数据库表D) 没有限制36、数据库表可以设置字段有效性规则，字段有效性规则属于域完整性范畴，其中的“规则”是一个AA) 逻辑表达式B) 字符表达式 C) 数值表达式 D) 日期表达式37、通过指定字段的数据类型和宽度来限制该字段的取值范围，这属于数据完整性中的CA) 参照完整性B) 实体完整性 C) 域完整性 D) 字段完整性38、用命令"INDEX on TAG index_name"建立索引，其索引类型是CA) 主索引B) 候选索引C) 普通索引D) 惟一索引39、执行命令"INDEX on 姓名 TAG index_name"建立索引后，下列叙述错误的是BA) 此命令建立的索引是当前有效索引B) 此命令所建立的索引将保留在.idx文件中C) 表中记录按索引表达式升序排序D) 此命令的索引表达式是“姓名”，索引名是"index_name"40、两表之间“临时性”联系称为关联，在两个表之间的关联已经建立的情况下，有关“关联”的正确叙述是CA) 建立关联的两个表一定在同一个数据库中B) 两表之间“临时性”联系是建立在两表之间“永久性”联系基础之上的C) 当父表记录指针移动时，子表记录指针按一定的规则跟随移动D) 当关闭父表时，子表自动被关闭41、打开表并设置当前有效索引（相关索引已建立）的正确的命令是BA) ORDER student IN 2 index 学号B) USE student IN 2 ORDER 学号C) index 学号 ORDER studentD) USE student IN 242、执行下列一组命令之后，选择“职工”表所在工作区的错误命令是BCLOSE ALLUSE 仓库 IN 0USE 职工 IN 0A) SEELECT 职工B) SELECT 0B) SELECT 2 C) SELECT B二、填空题1、如果在第一个工作区中打开一个数据表文件，然后在另一个工作区上再次打开该数据表文件，且不关闭前一个工作区上打开的，必须加短语 Again 。
2、在Visual FoxPro中，物理删除当前表中所有记录，可使用命令 zap 。
3、在Visual FoxPro中，数据表中备注型字段所保存的数据信息存储在以 fpt 为扩展名的文件中。
4、表间永久性联系不能控制不同工作区中记录指针的联动，要实现联动功能，需要建立表之间的关联。
5、在Visual FoxPro中，利用DELETE命令可以逻辑删除数据表的记录，必要时可以利用 recall 命令进行恢复。
6、在数据库设计器中设计表之间的联系时，要在父表中建立主索引，在子表中建立普通索引。
7、在Visual FoxPro中，索引分为主索引、候选索引、惟一索引和普通索引。
8、将当前表中所有的学生年龄加1，可使用命令：replace all 年龄 WITH 年龄+19、使用命令在结构复合索引添加一个对“姓名”字段的索引项，索引名为“xm”。
请将语句填写完整。
INDEX on 姓名 tag xm10、将工资表中总金额字段的默认值设置为0.00，这属于定义数据域完整性。
11、自由表与数据库表相比较，在自由表中不能设置主索引。
12、同一个表的多个索引可以创建在一个索引文件中，索引文件名与相关的表同名，索引文件的扩展名是 cdx ，这种索引称为结构复合索引。
13、在Visual FoxPro中数据库文件的扩展名是dbc ，数据库表文件的扩展名是dbf 。
14、在Visual FoxPro中，建立索引的作用之一是提高查询速度。
15、在Visual FoxPro中通过建立主索引或候选索引来实现实体完整性约束。
16、在Visual FoxPro中选择一个没有使用的、编号最小的工作区的命令是select 0（关键字必须拼写完整）。
附：参考答案一、选择题1. Again2.zap3.fpt4.记录指针关联5.逻辑recall6.主索引普通索引7.主索引候选索引8.replace all9.on, tag10.域11.主12.cdx 结构复合索引 13.dbc dbf 14.查询15.实体16.select 0。