clase 24-10-2018

SELECT * FROM hr.employees
WHERE department_id=90;

SELECT POWER (2,10)
FROM DUAL;

-- Ahora listando solo los distintos

SELECT DISTINCT job_id Cargo, salary Sueldo
FROM hr.employees
ORDER BY job_id DESC;

-- Listar id de empleado, la renta y la renta aumentada en 20%

SELECT employee_id, salary, salary*1.2 "salario nuevo"
FROM hr.employees;

-- Listar todos los empleados contratados despues del 1 de marzo del 2000

SELECT employee_id, first_name, hire_date
FROM hr.employees
WHERE hire_date > '01-03-2000';

-- Cambiar fecha en servidor

ALTER SESSION SET nls_date_format='dd-mm-yyyy'

-- Listar todos los empleados contratados en el año 2000

SELECT employee_id, first_name, hire_date
FROM hr.employees
WHERE hire_date >= '01-01-2000' AND hire_date <= '31-12-2000';

-- Listar todos los empleados contratados en el año distinto al 2000

SELECT employee_id, first_name, hire_date
FROM hr.employees
WHERE NOT (hire_date >= '01-01-2000' AND hire_date <= '31-12-2000');

-- listar todos los empleados del departamento 90

SELECT employee_id, first_name, hire_date, department_id
FROM hr.employees
WHERE department_id=90;

-- Listar todos los empleados contratados en el 2001 (between)

SELECT employee_id, first_name, hire_date
FROM hr.employees
WHERE hire_date BETWEEN '01-01-2001' AND '31-12-2001';

-- listar los empleados de los departamentos 90,110,150,300 (or) (con el and se excluye)

SELECT employee_id, first_name, hire_date, department_id
FROM hr.employees
WHERE department_id=90
OR department_id=110
OR department_id=150
OR department_id=300;

-- in

SELECT employee_id, first_name, hire_date, department_id
FROM hr.employees
WHERE department_id IN ( 90,110,150,300 );

-- not (que no sean del departamento 90,110,150,300)

SELECT employee_id, first_name, hire_date, department_id
FROM hr.employees
WHERE department_id NOT IN ( 90,110,150,300 );

-- listar empleados cuyo nombre comienza con la letra A (LIKE)

SELECT employee_id, first_name, hire_date, department_id
FROM hr.employees
WHERE first_name LIKE 'A%'

-- listar empleados cuyo nombre termina con la letra R (LIKE)

SELECT employee_id, first_name, hire_date, department_id
FROM hr.employees
WHERE first_name LIKE '%r'

-- listar empleados cuyo nombre tengan 4 letras

SELECT employee_id, first_name, hire_date, department_id
FROM hr.employees
WHERE first_name LIKE '____'

--listar empleados con su salario y comision solo aquellos que tenfan comision.

SELECT employee_id, first_name, hire_date, department_id, salary, commission_pct
FROM hr.employees
WHERE commission_pct > 0;

--listar empleados que no tengan comision

SELECT employee_id, first_name, hire_date, department_id, salary, commission_pct
FROM hr.employees
WHERE commission_pct IS NULL;

-- listar los empleados seleccionados
SELECT employee_id, first_name, last_name
FROM hr.employees
WHERE department_id = 90;

-- creando tabla nueva

CREATE TABLE Empleado90 (id, nombre, apellido)
AS
SELECT employee_id, first_name, last_name
FROM hr.employees
WHERE department_id =90;

SELECT * FROM Empleado90;

--creando una tabla con los empleados del departamento 80

CREATE TABLE Empleado80
AS
SELECT employee_id, first_name, last_name
FROM hr.employees
WHERE department_id =80;

SELECT *FROM Empleado80;

--contar las pesonas del departamento 80

SELECT COUNT(*)
FROM hr.employees
WHERE department_id=80;

--contar los empleados de cada departamento y ordenarlos de forma ascendente

SELECT department_id, COUNT(*)
FROM hr.employees
GROUP BY department_id
ORDER BY department_id ASC;

--encontrar el jefe del departamento 90

SELECT employee_id, first_name, last_name, manager_id
FROM hr.employees
WHERE department_id = 90;

--Contar los empleados por cada departamento (nombre)

SELECT d.department_name, COUNT(*)
FROM hr.employees e
join hr.departments d ON e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY d.department_name;