期末作业

湛江师范学院2013年-2014学年度第二学期大型数据库系

统设计期末作业

班级:11计本(2)班学号:姓名:

某体育公司的数据库包括三个表,

产品表Product(pCode, pType, pName, cost, price),属性意义分别是产品代码,产品类型,产品名称,成本,价格。

订单表Order(orderNo, custNo, orderDate, delivDate) ,属性意义分别为订单号,顾客编号,订购日期,送货日期。

订单明细表OrderDetail(orderNo, pCode, qty, discount) ,属性意义分别为订单编号,产品代码,订购数量,折扣。

各个表的中的数据如下:

期末作业

期末作业

期末作业

请根据上面给出的数据库,在SQL*Plus下完成下面各题(各题给出相关的SQL语句,并给出在SQL*Plus下的运行结果截图):

1、给出创建Product、Order和OrderDetail这个三个表的SQL语句,并定义主

键和外键以及相关的约束(有哪些约束自己考虑)。

创建Product表:

CREATE TABLE Product(

pCode NUMBER(3) PRIMARY KEY,

pType V ARCHAR2(10) NOT NULL,

pName V ARCHAR2(10) NOT NULL,

cost NUMBER(3,1) NOT NULL,

Price NUMBER(3,1) NOT NULL);

期末作业

创建Order表:

CREATE TABLE "Order"(

orderNo NUMBER(2) PRIMARY KEY,

custNo CHAR(5) NOT NULL,

OrderDate DATE NOT NULL,

DelivDate DATE NOT NULL

);

期末作业

创建OrderDetail表:

CREATE TABLE OrderDetail(

orderNo NUMBER(2),

pCode NUMBER(3),

PRIMARY KEY(orderNo,pCode),

qty NUMBER(4) NOT NULL,

discount NUMBER(3,2) NOT NULL,

CONSTRAINT OrderDetail_Order FOREIGN KEY (orderNo) REFERENCES "Order" (orderNo),

CONSTRAINT OrderDetail_Product FOREIGN KEY (pCode) REFERENCES Product (pCode)

)

期末作业

2、把上面三个表中的数据插入数据库中。(这一步不需要SQL语句和截图)。

3、给出以下查询:

(1)查找至少比一种球类产品的价格高的非球类产品的编号、类型、名称和价格:

SELECT pCode,pType,pName,price FROM pRoduct

WHERE pType not like '%球类'and price > SOME(

Select price FROM Product WHERE pType like '%球类')

期末作业

(2)查询至少在两个不同的订单中订购的产品的产品编号。

期末作业

(3)查找24号订单所包含的每个产品的编号和销售总额SELECT Product.pCode,price*qty*(1-discount) as gross_sales FROM Product,OrderDetail

WHERE Product.pCode = OrderDetail.pCode AND orderNo = 24

期末作业

(4)查询曾经购买过101号产品的客户的编号。

SELECT COSTNO FROM "Order",orderDetail

WHERE "Order".orderNo = orderDetail.orderNo AND pCode = 101

期末作业

(5)查询购买了产品101但没有购买102的订单详情。SELECT * FROM OrderDetail

WHERE orderNo = (

SELECT orderNo FROM OrderDetail

WHERE pCode = 101

MINUS

SELECT orderNo FROM OrderDetail

WHERE pCode = 102)

期末作业

4、创建一个过程,统计某种类型的产品中,价格超出成本30%以上的产品的个数,并将其降价10%。(需要测试)

创建过程:

CREATE OR REPLACE PROCEDURE pcode_price

(product_ptype In varchar2,pmatch OUT number) AS

BEGIN

SELECT count(*) into pmatch FROM Product

WHERE price IN(

SELECT price FROM Product

WHERE price > cost*(1+0.3) AND pType = product_ptype);

UPDATE Product SET price = price*(1-0.1)

WHERE price IN(

SELECT price FROM Product

WHERE price > cost*(1+0.3) AND pType = product_ptype);

END pcode_price;

期末作业

统计产品个数:(以游泳类为例)

期末作业

期末作业

降价后更新:

期末作业

5、当修改产品的价格时,修改后的价格不能低于其成本,否则将修改的价格改为成本。用什么能够实现此约束条件?请给出解决方法。(给出解决方法后需要测试)

创建触发器:

CREATE OR REPLACE TRIGGER product_price

BEFORE UPDATE OF price

ON Product

FOR EACH ROW

BEGIN IF :new.price < :old.cost THEN

:new.price := :new.cost;

END IF;

END product_price;

更新前价格(以产品代码301,,302为例):

期末作业

更新价格:

期末作业

更新后价格:

期末作业

相关推荐
相关主题
热门推荐