CREATE TABLE HR_EMPLOYEES
(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR(20 BYTE),
LAST_NAME VARCHAR(20 BYTE),
EMAIL VARCHAR(20 BYTE),
PHONE_NUMBER VARCHAR(20 BYTE),
HIRE_DATE DATE,
SALARY NUMBER(6,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(4),
DEPARTAMENT_ID NUMBER(4)
);

ALTER TABLE HR_EMPLOYEES ADD CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY

CREATE TABLE HR_DEPARTAMENTS
{
DEPARMENT_ID NUMBER(4),
DEPARMENT_NAME VARCHAR2(30),
MANAGER_ID NUMBER(4),
LOCATION_ID NUMBER(4),
}

ALTER TABLE HR_DEPARTAMENTS ADD CONSTRAINT DEPT_ID_PK PRIMARY KEY;

#Change COLUMNS name

ALTER TABLE HR_EMPLOYEES RENAME TO HR_EMPLEADOS;
ALTER TABLE HR_EMPLEADOS RENAME COLUMN EMPLOYEE_ID TO EMPLEADO_ID;
ALTER TABLE HR_EMPLEADOS RENAME COLUMN FIRST_NAME TO NOMBRE;
ALTER TABLE HR_EMPLEADOS RENAME COLUMN LAST_NAME TO APELLIDO;
ALTER TABLE HR_EMPLEADOS RENAME COLUMN EMAIL TO CORREO_ELECTRONICO;
ALTER TABLE HR_EMPLEADOS RENAME COLUMN PHONE_NUMBER TO TELEFONO;
ALTER TABLE HR_EMPLEADOS RENAME COLUMN HIRE_DATE TO FECHA_INGRESO;
ALTER TABLE HR_EMPLEADOS RENAME COLUMN SALARY TO SALARIO;
ALTER TABLE HR_EMPLEADOS RENAME COLUMN COMMISSION_PCT TO COMISION;
ALTER TABLE HR_EMPLEADOS RENAME COLUMN MANAGER_ID TO JEFE_ID;
ALTER TABLE HR_EMPLEADOS RENAME COLUMN DEPARTAMENT_ID TO DEPARTAMENTO_ID;


ALTER TABLE HR_DEPARTAMENTS RENAME TO HR_DEPARTAMENTOS;
ALTER TABLE HR_DEPARTAMENTOS RENAME COLUMN DEPARMENT_ID TO DEPARMENTO_ID;
ALTER TABLE HR_DEPARTAMENTOS RENAME COLUMN DEPARMENT_NAME TO NOMBRE_DEPARTAMENTO;
ALTER TABLE HR_DEPARTAMENTOS RENAME COLUMN MANAGER_ID TO JEFE_ID;
ALTER TABLE HR_DEPARTAMENTOS RENAME COLUMN LOCATION_ID TO UBICACION_ID;

ALTER TABLE HR_EMPLEADOS MODIFY CORREO_ELECTRONICO VARCHAR(30 BYTE);
ALTER TABLE HR_EMPLEADOS MODIFY SALARIO NUMBER(12,2);
ALTER TABLE HR_EMPLEADOS MODIFY FECHA_INGRESO NUMBER(12,2);


/* Taller 02 09 2015*/
SELECT * FROM ibo.ventas WHERE ID_VENDEDOR = 201
SELECT COUNT(*) FROM ibo.productos
SELECT COUNT(*) FROM ibo.ciudades
SELECT COUNT(*) FROM ibo.paises
SELECT COUNT(*) FROM ibo.vendedores

SELECT * FROM ibo.ventas
SELECT * FROM ibo.productos
SELECT * FROM ibo.paises
SELECT * FROM ibo.ciudades
SELECT * FROM ibo.vendedores


SELECT * FROM ibo.ventas ORDER BY ID_PRODUCTO DESC

INSERT INTO ibo.productos VALUES (201, 'Audi');
INSERT INTO ibo.productos VALUES (202, 'BMW');
INSERT INTO ibo.productos VALUES (203, 'Ferrari');
INSERT INTO ibo.productos VALUES (204, 'Roll Royce');
INSERT INTO ibo.productos VALUES (205, 'Land Rover');
INSERT INTO ibo.productos VALUES (206, 'Bentley');
INSERT INTO ibo.productos VALUES (207, 'Mazda');
INSERT INTO ibo.productos VALUES (208, 'Ford');
INSERT INTO ibo.productos VALUES (209, 'Wolkswagen');
INSERT INTO ibo.productos VALUES (209, 'Subaru');

SELECT * FROM ibo.paises

INSERT INTO ibo.paises VALUES (201, 'Rusia');

SELECT * FROM ibo.ciudades

INSERT INTO ibo.ciudades VALUES (201, 'Moscu', 201);
INSERT INTO ibo.ciudades VALUES (202, 'San Petersburgo', 201);
INSERT INTO ibo.ciudades VALUES (203, 'Novosibirsk', 201);
INSERT INTO ibo.ciudades VALUES (204, 'Ekaterinburgo', 201);
INSERT INTO ibo.ciudades VALUES (205, 'Nizhni Nóvgorod', 201);

SELECT * FROM ibo.vendedores

INSERT INTO ibo.vendedores VALUES (201, 'Sergio Barbosa');

SELECT * FROM ibo.ventas

INSERT INTO ibo.ventas VALUES (201, 201, 201, 6000000000);

SELECT * FROM ibo.productos

UPDATE ibo.productos SET USUARIO = 'sbarbosa120' WHERE PRODUCTO_ID IN (201, 202, 203, 204, 205, 206, 207, 208, 209);

SELECT * FROM ibo.ventas

INSERT INTO ibo.ventas SELECT
TRUNC(DBMS_RANDOM.VALUE(low => 201, high => 209)) AS ID_PRODUCTO,
201 AS ID_VENDEDOR,
TRUNC(DBMS_RANDOM.VALUE(low => 201, high => 209)) AS ID_CIUDAD,
ROUND(DBMS_RANDOM.VALUE(low => 1000000000, high => 9999999999), 2) AS VALOR_VENTAS
FROM ibo.ventas
CONNECT BY level <= 1000;