2016 04 01 Banco de dados

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

create table tb_log(
 cd_log number(6),
 nm_log varchar2(50),
 ds_log varchar2(100),
 constraint tb_log_cd_log_pk primary key (cd_log)
);
alter table tb_log
add(vl_valor number(10,2));
insert into tb_log (cd_log, nm_log, ds_log, vl_valor)
values (1,'inclusão', 'teste de inclusao', 10.25);
insert into tb_log (cd_log, nm_log, ds_log, vl_valor)
values (2, null, 'teste de inclusao', 10);
insert into tb_log (cd_log, nm_log, ds_log, vl_valor)
values (3,'alteração', null, 100.01);
insert into tb_log (cd_log, nm_log, ds_log, vl_valor)
values (4,'exclusão', 'teste de exclusão', null);
insert into tb_log (cd_log, nm_log, ds_log, vl_valor)
values (5,'inclusão', 'mais um teste', 10.25);
select * from tb_log;
select nvl(vl_valor, 0) from tb_log;
select cd_log, nvl(nm_log, '-'), nvl(vl_valor, 0) from tb_log;
select cd_log, upper(nm_log), ds_log from tb_log;
select cd_log, lower(nm_log), ds_log from tb_log;
insert into tb_log (cd_log, nm_log, ds_log, vl_valor)
values (7, upper('inclusão'), lower('mais uma alteracão'), 0);
select * from tb_log where lower(nm_log) = lower('alteração');
select initcap(nvl(nm_log,'-')), nm_log, nm_log as "i" from tb_log;
select substr(nm_log,1,4), substr(nm_log,5,2), nm_log from tb_log;
select nm_log, instr(nm_log, 'Ã'), instr(upper(nm_log), 'Ã') from tb_log;
select nm_log, instr(nm_log, 'era'), instr(lower(nm_log), 'era') from tb_log;
insert into tb_log (cd_log, nm_log, ds_log, vl_valor)
values (8, 'TESTE', 'ALEXANDRE ALEX ALEXANDRINO', 1);
SELECT nm_log, length(nm_log), ds_log, length(ds_log) from tb_log;
SELECT nm_log, translate(nm_log, 'A', 'x') from tb_log;
SELECT nm_log, translate(nm_log, 'AE', 'xy') from tb_log;
SELECT nm_log, replace(nm_log, 'AL', 'xy') from tb_log;
update tb_log set vl_valor = 10.57 where cd_log = 1;
update tb_log set vl_valor = 99.13 where cd_log = 2;
select vl_valor, round(vl_valor,1) from tb_log;
select vl_valor, trunc(vl_valor,1) from tb_log;
select round(1.9865,3) from dual;
select sum(vl_valor) from tb_log;
select avg(nvl(vl_valor,0)) from tb_log;
select count(vl_valor) from tb_log;
select max(vl_valor) from tb_log;
select min(vl_valor) from tb_log;
select sysdate from dual;
select to_char(sysdate,'DD-MM-YYYY') from dual;
select * from hr.employees where to_char(hire_date,'YYYY') = 2006;
select * from hr.employees where to_char(hire_date,'YYYY') between 2003 and 2006;
select to_char(hire_date,'MONTH') from hr.employees where to_char(hire_date,'YYYY') between 2003 and 2006;
select to_char(hire_date,'YYYY-MM-DD') from hr.employees where hire_date between '01/08/03' and '30/09/06';