Practica 1-----------------------------------------------------------------------------------------------------------
CREATE USER palafots IDENTIFIED BY palafots account UNLOCK;
ALTER USER palafots account unclock;
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';