数据库第三章作业及SQL上机实验标准答案
大三sql课后习题答案
第二章3.上机练习题02 程序代码如下：CREATE DATABASE STUDENT1 ON PRIMARY(NAME= STUDENT1_data, FILENAME='E:\DATA\',SIZE=3,MAXSIZE=unlimited, FILEGROWTH=15%)LOG ON(NAME= STUDENT1_log, FILENAME='E:\DATA\',SIZE=2,MAXSIZE=30,FILEGROWTH=2)03 程序代码如下：create database students on primary(name=students1,filename='E:\DATA\',size=5,maxsize=75,filegrowth=10%),(name= students12, filename='E:\DATA\',size=10,maxsize=75,filegrowth=1)log on(name=studentslog1, filename='E:\DATA\',size=5,maxsize=30,filegrowth=1),(name=studentslog2, filename='E:\DATA\',size=5,maxsize=30,filegrowth=1)第三章：3 上机练习题01 程序代码如下：-- 创建表book的Transact-SQL语句：USE test01GOCREATE TABLE book(book_id nchar(6)NOT NULL,book_name nchar(30)NULL,price numeric(10, 2)NULL,CONSTRAINT PK_book PRIMARY KEY CLUSTERED(book_id ASC))ON PRIMARY-- 创建表uthor的Transact-SQL语句：CREATE TABLE(anthor_name nchar(4)NOT NULL,book_id nchar(6)NOT NULL,address nchar(30)NOT NULL)ON [PRIMARY]-- 设置book中的book_id为主键，author表中的book_id为外键ALTER TABLE WITH CHECKADD CONSTRAINT FK_ book_author FOREIGN KEY(book_id) REFERENCES(book_id)02 程序代码如下：--利用Transact-SQL语句创建表booksales的代码。
大三 sql 课后习题答案
第二章3.上机练习题02 程序代码如下：CREATE DATABASE STUDENT1ON PRIMARY(NAME= STUDENT1_data,FILENAME='E:\DATA\STUDENT1.mdf', SIZE=3,MAXSIZE=unlimited,FILEGROWTH=15%)LOG ON(NAME= STUDENT1_log,FILENAME='E:\DATA\STUDENT1.ldf', SIZE=2,MAXSIZE=30,FILEGROWTH=2)03 程序代码如下：create database studentson primary(name=students1,filename='E:\DATA\students1.mdf', size=5,maxsize=75,filegrowth=10%),(name= students12,filename='E:\DATA\students2.ndf', size=10,maxsize=75,filegrowth=1)log on(name=studentslog1,filename='E:\DATA\studentslog1.ldf', size=5,maxsize=30,filegrowth=1),(name=studentslog2,filename='E:\DATA\studentslog2.ldf', size=5,maxsize=30,filegrowth=1)第三章：3 上机练习题01 程序代码如下：-- 创建表book的Transact-SQL语句：USE test01GOCREATE TABLE book(book_id nchar(6)NOT NULL,book_name nchar(30)NULL,price numeric(10, 2)NULL,CONSTRAINT PK_book PRIMARY KEY CLUSTERED(book_id ASC))ON PRIMARY-- 创建表uthor的Transact-SQL语句：CREATE TABLE dbo.author(anthor_name nchar(4)NOT NULL,book_id nchar(6)NOT NULL,address nchar(30)NOT NULL)ON [PRIMARY]-- 设置book中的book_id为主键，author表中的book_id为外键ALTER TABLE dbo.author WITH CHECKADD CONSTRAINT FK_ book_author FOREIGN KEY(book_id) REFERENCES dbo.book (book_id)02 程序代码如下：--利用Transact-SQL语句创建表booksales的代码。
数据库第三章所有例题参考答案
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)（完）。
数据库上机实验题目和答案
试用SQL的查询语句表达下列查询：1.检索王丽同学所学课程的课程号和课程名。
select Cno ,Cname from c where Cno in(select cno from sc where sno in (select sno from s where sname='王丽' ))2.检索年龄大于23岁的男学生的学号和姓名。
select sno,sname from swhere sex='男' and age>233.检索‘c01’课程中一门课程的女学生姓名select sname from swhere sex='女' and sno in(select sno from sc where cno='c01')4.检索s01同学不学的课程的课程号。
select cno from cwhere cno not in (select cno from sc where sno ='s01')5.检索至少选修两门课程的学生学号。
select sc.sno from s,scwhere s.sno=sc.snogroup by sc.snohaving count(o)>=26.每个学生选修的课程门数。
解法一：select so.sno sno,ount,s.snamefrom(select sc.sno sno,count(sc.sno) ccountfrom sc,swhere s.sno=sc.snogroup by sc.sno ) so,swhere s.sno=so.sno解法二：select sc.sno sno,s.sname,count(sc.sno) ccountfrom sc,swhere s.sno=sc.snogroup by sc.sno,sname7.求选修C4课程的学生的平均分。
数据库 第三章习题参考答案
三、设计题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 张勇
数据库系统及应用(SQL)第三次作业题及答案.doc
第3次作业一、填空题（本大题共20分，共10小题，每小题2分）1.SQL Server使用的数据库编程语言是__________。
2. _____ 是数据服务器方法支持的最自然必数据模型。
3.DBMS i访问程序找到有关的物理数据块（或页面）地址，向 ____________ 发出读块（页）操作命令。
4.乘积空间中的有限集合称为_________ ,无限集合称为_________5.一个基木的ODBC结构由_________ 、 ________ 、________ 和______ 四个部分组成。
6.SQL Sever 2000在安装过程中自动创建了6个数据库：master, model, msdb, tempdb, pubs 和Northwind,其屮______ , ________ , ______ , ________ 为系统数据库。
7.若要求分解具有无损连接性，那么分解一定可以达到 ____________ o& ________ 是用户与分布式数据库系统的接口。
根据构成各个局部数据库的DBMS及其数据模型，可以将分布式数据库系统分为两类：________________ 和9.Transact-SQL的数据类型分为_________ 和__________ 两大类，其中______ 是指系统捉供的数据类型，__________ 由基本数据类型导出。
10.抱共享同样屈性和方法的所冇对彖称为一个_____________ ,每个类冇一个______ ,所有的子类共有一个___________ o二、简答题（本大题共40分，共4小题，每小题10分）1.什么是宿主型DML和自主型DML。
2.什么是“数据建模” ？3.简述函数依赖的数学模型。
4.简述SQL语言的基本功能。
三、分析题（本大题共20分，共2小题，每小题10分）1.查询所有出版社的名称，如果它所在的州有书店，则一起显示书店的名称。
数据库上机实验题目和答案
数据库上机实验题目和答案试用SQL的查询语句表达下列查询：1.检索王丽同学所学课程的课程号和课程名。
select Cno ,Cname from c where Cno in(select cno from sc where sno in (select sno from s where sname='王丽' ))2.检索年龄大于23岁的男学生的学号和姓名。
select sno,sname from swhere sex='男' and age>233.检索‘c01’课程中一门课程的女学生姓名select sname from swhere sex='女' and sno in(select sno from sc where cno='c01')4.检索s01同学不学的课程的课程号。
select cno from cwhere cno not in (select cno from sc where sno ='s01')5.检索至少选修两门课程的学生学号。
select sc.sno from s,scwhere s.sno=sc.snogroup by sc.snohaving count(/doc/1411529677.html,o)>=26.每个学生选修的课程门数。
解法一：select so.sno sno,/doc/1411529677.html,ount,s.sname from(select sc.sno sno,count(sc.sno) ccountfrom sc,swhere s.sno=sc.snogroup by sc.sno ) so,swhere s.sno=so.sno解法二：select sc.sno sno,s.sname,count(sc.sno) ccountfrom sc,swhere s.sno=sc.snogroup by sc.sno,sname7.求选修C4课程的学生的平均分。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
（11）请将(S2, J6, P4, 200)插入供应情况。 INSERT INTO SPJ VALUES(S2, P4, J6, 200);
11.请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件 代码（PNO）、供应数量（QTY）。针对该视图完成下列查询： （1）找出三建工程项目使用的各种零件代码及其数量。 （2）找出供应商 S1 的供应情况。 建视图：
( SELECT * FROM SPJ WHERE SPJ.JNO=J.JNO AND SNO IN ( SELECT SNO FROM S WHERE CITY=’天津’ ) AND PNO IN ( SELECT PNO FROM P WHERE COLOR=’红’ )
); 或者
SELECT JNO FROM J WHERE NOT EXISTS (SELECT * FROM SPJ, S, P WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY=’天津’ AND P.COLOR=’红’ ); （5）求至少使用了供应商 S1 所供应的全部零件的工程号。 SELECT DISTINCT JNO
4.针对上题中建立的 4 个表试用 SQL 语言完成第二章习题 5 中的查询。 （1）求供应工程 J1 零件的供应商号码 SNO；
SELECT SNO FROM SPJ WHERE JNO=’J1’; （2）求供应工程 J1 零件 P1 的供应商号码 SNO； SELECT SNO FROM SPJ WHERE JNO=’J1’ AND PNO=’P1’; （3）求供应工程 J1 零件为红色的供应商号码 SNO；
CREATE VIEW V_SPJ AS SELECT SNO, PNO, QTY WHERE JNO=
( SELECT JNO FROM J WHERE JNAME=’三建’
); （1）SELECT PNO, QTY
FROM V_SPJ; （2）SELECT PNO, QTY
FROM V_SPJ WHERE SNO=’S1’;
FROM SPJ SPJZ WHERE NOT EXISTS (SELECT *
FROM SPJ SPJX WHERE SNO=’S1’ AND NOT EXISTS ( SELECT * FROM SPJ SPJY WHERE SPJY.PNO=SPJX.PNO AND SPJY.JNO=SPJZ.JNO ) ); 5.针对习题 3 中的 4 个表试用 SQL 语言完成以下各项操作： （1）找出所有供应商的姓名和所在城市。 SELECT SNAME, CITY FROM S; （2）找出所有零件的名称、颜色、重量。 SELECT PNAME, COLOR, WEIGHT FROM P; （3）找出使用供应商 S1 所供应零件的工程号码。 SELECT JNO FROM SPJ WHERE SNO=’S1’; （4）找出工程项目 J2 使用的各种零件的名称及数量。 SELECT , SPJ.QTY FROM P, SPJ WHERE P.PNO=SPJ.PNO AND SPJ.JNO=’J2’; （5）找出上海厂商供应的所有零件号码。 SELECT DISTINCT PNO FROM SPJ WHERE SNO IN
(
SELECT JNO FROM SPJ, S WHERE SPJ.SNO=S.SNO AND S.CITY=’上海’ ); （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=’天津’ )
( SELECT SNO FROM S WHERE CITY=’上海’
); （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
第三章作业及 SQL 上机实验标准答案
3.用 SQL 语句建立第二章习题 5 中的 4 个表。 S表 CREATE TABLE S ( SNO CHAR(2) PRIMARY KEY, SNAME CHAR(20), STATUS INT, CITY CHAR(4) ); P表 CREATE TABLE P ( PNO CHAR(2) PRIMARY KEY, PNAME CHAR(20), COLOR CHAR(2), WEIGHT INT ); J表 CREATE TABLE J ( JNO CHAR(2) PRIMARY KEY, JNAME CHAR(20), CITY CHAR(4) ); SPJ CREATE TABLE SPJ ( SNO CHAR(2), PNO CHAR(2), JNO CHAR(2), QTY INT, PRIMARY KEY (SNO,PNO,JNO), FOREIGN KEY (SNO) REFERENCES S(SNO), FOREIGN KEY (PNO) REFERENCES P(PNO), FOREIGN KEY (JNO) REFERENCES J(JNO) );
); 或者
SELECT JNO FROM J WHERE NOT EXISTS
( SELECT * FROM SPJ, S WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND
S.CITY=’天津’ );
（8）把全部红色零件的颜色改为蓝色。 UPDATE P SET COLOR=’蓝’ WHERE COLOR=’红’;
SELECT SNO FROM SPJ WHERE JNONO FROM P WHERE COLOR=’红’
); 或者
SELECT SNO FROM SPJ, P WHERE JNO=’J1’ AND SPJ.PNO=P.PNO AND COLOR=’红’; （4）求没有使用天津供应商生产的红色零件的工程号 JNO； SELECT JNO FROM J WHERE NOT EXISTS
（9）把 S5 供给 J4 的零件 P6 改为 S3 供应，请做必要修改。 UPDATE SPJ SET SNO=’S3’ WHERE SNO=’S5’ AND JNO=’J4’ AND PNO=’P6’;
（10）从供应关系中删除 S2 的记录，并从供应情况关系中删除相应的记录。 （请注意删除顺序）