Repaso BD

Practica 1-----------------------------------------------------------------------------------------------------------
sqlplus  / as sysdba
alter user "UserName" identified by "newpassword";
sqlplus hr
select country_id, country_name from countries;

Connection name: hr_conn
username: hr
Password: hr
service name: orcl

Practica 2----------------------------------------------------------------------------------------------------------
SELECT employees.employee_id AS Clave, employees.last_name ||',' || employees.First_name AS Empleado, Job_title AS puesto,
Boss.last_name || ',' || Boss.First_name AS Jefe
FROM employees, jobs, (select * from Employees) boss
where employees.job_id = jobs.job_id
and employees.manager_id = boss.employee_id;

SELECT Distinct
employees.last_name ||',' || employees.First_name AS Empleado,
departments.department_name as Departamento,
jobs.job_title as TituloDeTrabajo,
employees.hire_date
FROM employees, jobs,departments
where employees.job_id = jobs.job_id
and employees.department_id = departments.department_id
and salary > 1000

Practica 3----------------------------------------------------------------------------------------------------------
Insert into regions (region_id, region_name)
values (5, 'Occidente');

select * from regions order by region_id;
rollback;

delete from regions
where region_name = 'Occidente';

Create table telefonos(apellido_paterno varcahr (20),nombre varchar(20));
insert into telefonos(nombre, apellido_paterno)
values('Mora', Alejandro');
insert into telefonos(nombre, apellido_paterno)
values('Palafox', Fernando');
insert into telefonos(nombre, apellido_paterno)
values('Gutierrez', 'Pablo');

create index TELS on telefonos (apellido_paterno, nombre);

Practica 4----------------------------------------------------------------------------------------------------------
create or replace procedure hola_mundo (MUNDO in varchar)
as
begin
dbms_output.put_line('Hola ' || MUNDO);
end hola_mundo;

set serveroutput on;
execute hola_mundo('mundo');

CREATE OR REPLACE PROCEDURE ALTA_EMPLEADO (
  aFIRST_NAME IN employees.first_name%type
, aLAST_NAME IN employees.last_name%type
, aPHONE_NUMBER IN employees.phone_number%type
, aJOB_ID IN employees.job_id%type
, aDEPARTMENT_ID IN employees.department_id%type)
AS BEGIN
insert into employees(
employees.first_name,
employees.last_name,
employees.phone_number,
employees.job_id,
employees.department_id,
employees.employee_id,
employees.email,
employees.SALARY,
employees.MANAGER_ID,
employees.hire_date)
values
(ALTA_EMPLEADO.aFIRST_NAME,
ALTA_EMPLEADO.aLAST_NAME,
ALTA_EMPLEADO.aphone_number,
ALTA_EMPLEADO.ajob_id,
ALTA_EMPLEADO.adepartment_id,

(select count(employee_id) +100 from employees),
(SUBSTR (ALTA_EMPLEADO.afirst_name, 0, 1) || ALTA_EMPLEADO.alast_name ||'@iteso.mx'),
(select JOBS.MIN_SALARY FROM JOBS WHERE JOBS.JOB_ID = ALTA_EMPLEADO.ajob_id),
(SELECT MANAGER_ID FROM DEPARTMENTS WHERE DEPARTMENTS.MANAGER_ID = ALTA_EMPLEADO.adepartment_id), sysdate);
insert into job_history( employee_id, start_date,
end_date,
job_id)
values(
(select max(employees.employee_id) from employees), sysdate,
(select add_months(sysdate,3) from dual), alta_empleado.ajob_id
);
COMMIT; exception
when others then
rollback;
raise;
END ALTA_EMPLEADO;

execute alta_empleado('alejandro','mora','3317192659','IT_PROG',60);
select * from employees where first_name = 'alejandro';

Practica 5----------------------------------------------------------------------------------------------------------
create table MI_LOG (fecha date, actividad varchar2(50));
create table MI_TABLA (clave number, articulo varchar2(50), precio number, primary key (clave));

create sequence incrementos
start with 1
increment by 1
nocache
nocycle;

create or replace trigger tr_incrementos
before
insert on mi_tabla
for each row
begin
:new.clave := incrementos.nextval;
end;

insert into mi_tabla (articulo,precio) values ('Lapiz', 10);
select * from mi_tabla;

create or replace trigger cambio_precio
before
update on mi_tabla
for each row
Enable
begin
if (:old.precio != :new.precio) then
insert into mi_log values (sysdate, ('El ' || :old.articulo ||' cambia precio de ' || :old.precio || ' a ' || :new.precio ));
end if;
end;

update mi_tabla set precio = 11 where articulo = 'Lapiz';

select * from log_users;

create or replace trigger login
after logon
on HR.schema
begin
  insert into log_users (usuario,tipo,fecha)
  values (user,'normal',sysdate);
 		if to_char (sysdate,'HH24:MI') not between '08:00' and '18:00'
 		  or to_char (sysdate, 'DY') in ('SAT', 'SUN')
 		  then update log_users set tipo = ('Fuera de horario');
 	   end if;
end;

select * from log_users;

Practica 6----------------------------------------------------------------------------------------------------------
SELECT XMLELEMENT ("Empleados",
XMLAGG(
XMLForest(first_name || ' ' || last_name as "Nombre", jobs.job_id AS "Puesto" )))
FROM EMPLOYEES, JOBS
where employees.job_id = jobs.job_id AND jobs.job_id = 'FI_ACCOUNT';

SELECT XMLELEMENT ("Empleados",
XMLAGG(
XMLELEMENT("Empleado",
xmlattributes(first_name || ' '  ||last_name as "Nombre", jobs.job_id as "Puesto")))) AS "Emps"
FROM EMPLOYEES, JOBS
where employees.job_id = jobs.job_id AND jobs.job_id = 'FI_ACCOUNT';