1. 实验目的：
熟悉oracle10g关于层次、情景、翻译、分析、线性回归等各种高级查询技术。
2. 实验内容。
1） 练习层次查询。
2） 练习情景查询；
3） 练习翻译查询；
4） 练习分析查询；
5） 练习线性回归查询。
3. 实验步骤。
4. 实验总结。
5. 实验数据和sql语句。
层次查询。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, parent_id, part_name, mp_cost
from bicycle
start with part_id = 1
connect by prior part_id = parent_id
order by level;
column partname format a35
column mp_cost format 99999.99
select level,
lpad(' 3*level-1) |part_name as partname,mp_cost
from bicycle
start with part_id = 1
connect by prior part_id = parent_id;
select level,
lpad(' 3*level-1) |part_name as partname,mp_cost
from bicycle
start with part_id = 7
connect by prior part_id = parent_id;
select level,
lpad(' 3*level-1) |part_name as partname,mp_cost
from bicycle
start with part_id = select part_id
from bicycle
where part_name like '%飞轮%')
connect by prior part_id = parent_id;
select level,
lpad(' 3*level-1) |part_name as partname,mp_cost
from bicycle
start with part_id = select part_id
from bicycle
where part_name like '%花盘%')
connect by prior parent_id = part_id;
select level,
lpad(' 3*level-1) |part_name as partname,mp_cost
from bicycle
where part_name <>驱动系统'
start with part_id = 1
connect by prior part_id = parent_id;
select level,
lpad(' 3*level-1) |part_name as partname,mp_cost
from bicycle
start with part_id = 1
connect by prior part_id = parent_id
and part_name <>驱动系统';
select level,
lpad(' 3*level-1) |part_name as partname,mp_cost
from bicycle
where mp_cost >=80
start with part_id = 1
connect by prior part_id = parent_id;
情景查询。select ename,sal,decode(deptno, '10', 会计部', 其他部门')
from emp;
select ename,sal,decode(deptno, '10', 会计部',
20', 研发部',
30', 销售部',
其他部门')
from emp;
select ename,sal,case deptno
when 10 then '会计部'
when 20 then '研发部'
else '其他部门'
endfrom emp;
select ename, case
when hiredate < 1-7月-81' then '老员工'
when hiredate >=1-7月-81' and hiredate <=1-7月-82' then '普通员工'
oracle实验
大型数据库技术 实验报告。一 实验目的 理解重做日志文件的基本概念。掌握重做日志文件组及其成员的添加 移动 删除等操作。掌握归档重做日志的设置。理解表空间的基本概念和分类。掌握永久表空间的创建和管理操作。掌握还原表空间的管理。二 实验要求 完成重做日志文件组及其成员文件的管理操作。完成归档重做日志的...
Oracle实验
2015 2016 2学期。oracle数据库应用技术。实验报告。班级13级软件工程2班。学号2220131011 姓名蒋生栋。指导教师史金余。成绩。2016年 5 月 24 日。目录。2.1 触发器设计 2 2.2 存储过程 自定义函数设计 2 2.3 程序包设计 3 3.1 创建表空间resta...
Oracle实验
实验报告。一 实验目的。1 掌握表空间的存储参数设置方法。2 熟练掌握创建 删除和扩展表空间的方法。二 实验使用环境 本次实验所使用的平台和相关软件 oracle 10g sql plus 三 实验内容。1 创建本地管理方式的用户表空间。2 创建本地管理方式的临时表空间。3 改变表空间的大小 可用性...