实验三：高级SQL语言.docVIP

实验三：高级SQL语言 实验目的 熟悉Oracle10g关于层次、情景、翻译、分析、线性回归等各种高级查询技术。 实验内容 练习层次查询； 练习情景查询； 练习翻译查询； 练习分析查询； 练习线性回归查询。 实验步骤 层次查询 create table bicycle( part_id number(5) constraint pk_bicycle_part_id primary key, parent_id number(5) constraint fk_bicycle_pid references bicycle(part_id), part_name varchar2(30) not null, mp_cost number(9, 2), describe varchar2(30)); insert into bicycle values(1, null, 自行车, 379.28, 装配); insert into bicycle values(2, 1, 导向系统, 101.11, 制造); insert into bicycle values(3, 1, 驱动系统, 159.56, 制造); insert into bicycle values(4, 1, 其他部分, 118.61, 制造和采购); insert into bicycle values(5, 2, 车把, 37.28, 制造); insert into bicycle values(6, 2, 前叉, 24.35, 制造); insert into bicycle values(7, 2, 前轴, 19.67, 制造); insert into bicycle values(8, 2, 前轮, 19.81, 制造); insert into bicycle values(9, 7, 前轴棍, 8.16, 制造); insert into bicycle values(10, 7, 前轴身, 4.82, 制造); insert into bicycle values(11, 7, 前轴碗, 6.69, 制造); insert into bicycle values(12, 10, 前轴管, 1.61, 制造); insert into bicycle values(13, 10, 前花盘, 3.21, 制造); insert into bicycle values(14, 3, 脚蹬, 18.99, 制造); insert into bicycle values(15, 3, 中轴, 25.27, 制造); insert into bicycle values(16, 3, 链条, 21.65, 制造); insert into bicycle values(17, 3, 飞轮, 29.12, 制造); insert into bicycle values(18, 3, 后轴, 31.72, 制造); insert into bicycle values(19, 3, 后轮, 32.81, 制造); insert into bicycle values(20, 17, 外套, 9.35, 制造); insert into bicycle values(21, 17, 平档, 5.82, 制造); insert into bicycle values(22, 17, 芯子, 5.11, 制造); insert into bicycle values(23, 17, 千斤, 6.56, 制造); insert into bicycle values(24, 17, 钢珠, 2.28, 采购); insert into bicycle values(25, 4, 车架, 81.78, 制造); insert into bicycle values(26, 4, 车闸, 15.26, 制造); insert into bicycle values(27, 4, 链罩, 7.10, 采购); insert into bicycle values(28, 4, 车铃, 4.33, 采购); insert into bicycle values(29, 4, 车锁, 5.02, 采购); insert into bicycle values(30, 4, 支架, 5.12, 制造); select part_id, parent_id, part_name, mp_cost from bicycle start with part_id = 1 connect by prior part_id = parent_id; select level, part_id,