描述:如果 订货数量=0, PRating = NULL;
如果 0<订货数量<=100,PRating = 'RARE';
如果 100<订货数量<=1000,PRating = 'NORMAL';
否则 PRating = 'FREQUENT';要求:Set the correct values of an attribute PRating以下是个模拟数据库,请大家看看怎么能完成以上的要求,万分感谢。
SET ECHO ONCREATE SEQUENCE PartNumber START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE OrderNumber START WITH 1 INCREMENT BY 1;
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* Lookup tables */CREATE TABLE LRatings( /* List of part ratings */
Rating VARCHAR2(10) NOT NULL, /* Part rating */
CONSTRAINT LRatings_pkey PRIMARY KEY( Rating )
);
INSERT INTO Lratings VALUES( 'RARE' );
INSERT INTO Lratings VALUES( 'NORMAL' );
INSERT INTO Lratings VALUES( 'FREQUENT' );/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* Database tables */CREATE TABLE Part( /* Part description */
PNumber NUMBER(10) NOT NULL, /* Number */
PName VARCHAR(30) NOT NULL, /* Name */
PManufacturer VARCHAR(255) NOT NULL, /* Manufacturer */
PPrice NUMBER(7,2) NOT NULL, /* Price */
PRating VARCHAR(10) NULL, /* Rating */
CONSTRAINT Part_pkey PRIMARY KEY( PNumber ),
CONSTRAINT Part_fkey1 FOREIGN KEY( PRating )
REFERENCES LRatings( Rating )
);
INSERT INTO Part VALUES(PartNumber.NEXTVAL,
'bolt',
'Golden Bolts Pty Ltd',
25.20,
NULL);
INSERT INTO Part VALUES(PartNumber.NEXTVAL,
'nut',
'Silver Nuts Pty Ltd',
25.20,
NULL);
INSERT INTO Part VALUES(PartNumber.NEXTVAL,
'bolt',
'Lazy Bolts Pty Ltd',
25.20,
NULL);/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
CREATE TABLE Orders( /* Order description */
ONumber NUMBER(10) NOT NULL, /* Number */
ODate DATE NOT NULL, /* Date when issued */
OCustomer VARCHAR(255) NOT NULL, /* Customer involved */
CONSTRAINT Orders_pkey PRIMARY KEY( ONumber )
);/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */CREATE TABLE OrderLine( /* Ordered parts */
LOrder NUMBER(10) NOT NULL, /* Order number */
LLine NUMBER(3) NOT NULL, /* Line number */
LPart NUMBER(10) NOT NULL, /* Part number */
LQuantity NUMBER(6) NOT NULL, /* Quantity */
CONSTRAINT OrderLine_pkey PRIMARY KEY( LOrder, LLine ),
CONSTRAINT OrderLine_fkey1 FOREIGN KEY( LPart )
REFERENCES Part( PNumber ),
CONSTRAINT OrderLine_fkey2 FOREIGN KEY( LOrder )
REFERENCES Orders( ONumber )
);
INSERT INTO Orders VALUES ( OrderNumber.NEXTVAL,
'25-DEC-07',
'James' );
CREATE SEQUENCE LineNumber START WITH 1 INCREMENT BY 1;
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
3,
25);
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
1,
1);
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
2,
11);
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
3,
40);
DROP SEQUENCE LineNumber;
INSERT INTO Orders VALUES ( OrderNumber.NEXTVAL,
'25-DEC-07',
'Bob' );
CREATE SEQUENCE LineNumber START WITH 1 INCREMENT BY 1;
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
1,
10);
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
3,
60);
如果 0<订货数量<=100,PRating = 'RARE';
如果 100<订货数量<=1000,PRating = 'NORMAL';
否则 PRating = 'FREQUENT';要求:Set the correct values of an attribute PRating以下是个模拟数据库,请大家看看怎么能完成以上的要求,万分感谢。
SET ECHO ONCREATE SEQUENCE PartNumber START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE OrderNumber START WITH 1 INCREMENT BY 1;
/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* Lookup tables */CREATE TABLE LRatings( /* List of part ratings */
Rating VARCHAR2(10) NOT NULL, /* Part rating */
CONSTRAINT LRatings_pkey PRIMARY KEY( Rating )
);
INSERT INTO Lratings VALUES( 'RARE' );
INSERT INTO Lratings VALUES( 'NORMAL' );
INSERT INTO Lratings VALUES( 'FREQUENT' );/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* Database tables */CREATE TABLE Part( /* Part description */
PNumber NUMBER(10) NOT NULL, /* Number */
PName VARCHAR(30) NOT NULL, /* Name */
PManufacturer VARCHAR(255) NOT NULL, /* Manufacturer */
PPrice NUMBER(7,2) NOT NULL, /* Price */
PRating VARCHAR(10) NULL, /* Rating */
CONSTRAINT Part_pkey PRIMARY KEY( PNumber ),
CONSTRAINT Part_fkey1 FOREIGN KEY( PRating )
REFERENCES LRatings( Rating )
);
INSERT INTO Part VALUES(PartNumber.NEXTVAL,
'bolt',
'Golden Bolts Pty Ltd',
25.20,
NULL);
INSERT INTO Part VALUES(PartNumber.NEXTVAL,
'nut',
'Silver Nuts Pty Ltd',
25.20,
NULL);
INSERT INTO Part VALUES(PartNumber.NEXTVAL,
'bolt',
'Lazy Bolts Pty Ltd',
25.20,
NULL);/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
CREATE TABLE Orders( /* Order description */
ONumber NUMBER(10) NOT NULL, /* Number */
ODate DATE NOT NULL, /* Date when issued */
OCustomer VARCHAR(255) NOT NULL, /* Customer involved */
CONSTRAINT Orders_pkey PRIMARY KEY( ONumber )
);/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */CREATE TABLE OrderLine( /* Ordered parts */
LOrder NUMBER(10) NOT NULL, /* Order number */
LLine NUMBER(3) NOT NULL, /* Line number */
LPart NUMBER(10) NOT NULL, /* Part number */
LQuantity NUMBER(6) NOT NULL, /* Quantity */
CONSTRAINT OrderLine_pkey PRIMARY KEY( LOrder, LLine ),
CONSTRAINT OrderLine_fkey1 FOREIGN KEY( LPart )
REFERENCES Part( PNumber ),
CONSTRAINT OrderLine_fkey2 FOREIGN KEY( LOrder )
REFERENCES Orders( ONumber )
);
INSERT INTO Orders VALUES ( OrderNumber.NEXTVAL,
'25-DEC-07',
'James' );
CREATE SEQUENCE LineNumber START WITH 1 INCREMENT BY 1;
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
3,
25);
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
1,
1);
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
2,
11);
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
3,
40);
DROP SEQUENCE LineNumber;
INSERT INTO Orders VALUES ( OrderNumber.NEXTVAL,
'25-DEC-07',
'Bob' );
CREATE SEQUENCE LineNumber START WITH 1 INCREMENT BY 1;
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
1,
10);
INSERT INTO OrderLine VALUES( OrderNumber.CURRVAL,
LineNumber.NEXTVAL,
3,
60);
解决方案 »
- [急切求助]Select调用Function,这个Function还可以写数据库么?
- 推荐一款支持Oracle的sql编辑软件
- 关于数据库连接
- 有关审计的问题,
- 请高手帮忙看一下这样的sql怎么写,谢谢了
- 初学提问:小问题,怎么为system用户授予sysdba的权限
- ORA-01861 literal does not match format string 急!!
- 急!请高手oracle数据库恢复
- 请问存储过程中如何执行字符串?等。。。
- 关于存储过程的问题
- 数据库声明的一个number类型的字段,插入的时候什么都不传过去值是否报错
- sql请教 where trim("Stage")='Formal' 可以 ----- where Upper("Stage")=Upper('Formal') 可以--- "Stage"='Formal' 不可以 为什么
这个很简单啊
用CASE WHEN啊
你贴这么多建表的数据干什么?看的眼睛花了
假设你的表为a,订货数量 为qtyselect qty,
case
when qty>0 and qty<=100 then 'RARE'
when qty>100 and qty<=1000 then 'NORMAL'
when qty>1000 then 'FREQUENT'
else null
end PRating
from a