Ex. Parcial

Create TableSpace Parcial
DATAFILE 'C:\WIN/usil.dbf'
SIZE 50M;

Create USER Admin2
Identified by 12345
default tablespace Parcial
temporary tablespace temp;

Grant connect, resource, create view to Admin2;

-- Tipo de datos: Char Varchar2 Number Date Clob(Grandes Tipo de Datos)

Create or REPLACE VIEW department_v as
Select DEPARTMENT_ID, DEPARTMENT_NAME
From Departments;

Select d.DEPARTMENT_ID, d.DEPARTMENT_NAME
From Departments d join EMPLOYEES e
on e.DEPARTMENT_ID = d.DEPARTMENT_ID;

Select d.DEPARTMENT_ID, d.DEPARTMENT_NAME, count(e.DEPARTMENT_ID) as cantidad_empleados
From DEPARTMENTS d join EMPLOYEES e
on d.DEPARTMENT_ID = e.DEPARTMENT_ID
GROUP by d.DEPARTMENT_ID, d.DEPARTMENT_NAME
ORDER by d.DEPARTMENT_NAME;

Create or Replace View Vista1 as
Select EMPLOYEE_ID, FIRST_NAME, LAST_NAME
From EMPLOYEES;

Create or Replace View Vista2 as
Select employee_id, first_name, Last_name
From EMPLOYEES
Where SALARY >= 5000;

Create or Replace Viww Vista3 as
Select employee_id, first_name, Last_name
From Employees
Where manager_id is null;

Create or Replace Viww Vista4 as
Select e.DEPARTMENT_ID, e.FIRST_NAME, e.LAST_NAME
From EMPLOYEES e join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
where e.DEPARTMENT_ID in (10, 30 , 50)
order by d.DEPARTMENT_ID;

Create or Replace Viww Vista5 as
Select EMPLOYEE_ID, first_name||' '||last_name, TO_CHAR(HIRE_DATE, 'DD') || ' de ' || To_CHAR(HIRE_DATE, 'MONTH') || ' del año ' || To_Char(hire_date, 'YYYY') as Fecha
From EMPLOYEES;


Create Table Tiendas
(
id_tienda number primary key,
nombre varchar2(50),
direccion varchar2(50)
)

Create Table Inventarios
(
id_articulos char(10) primary key,
nombre varchar2(50) unique,
comentario varchar2(50) not null,
precio decimal(10,2),
id_tienda number references Tiendas
);

insert into Tiendas values
(1,'Tienda "la Perla"','Calle Victoria 234,La Perla.callao');
insert into Tiendas values
(2,'Tienda "Cercado"', ' Jr. Puno 753, Cercado de Lima.Lima');
insert into Tiendas values
(3,'Tienda "San Miguel"', 'Av. La Marina 2641, San Miguel.Lima');
insert into Tiendas values
(4,'Tienda "Pueblo Libre"', ' Av. Bolivar 1632.Pueblo Libre.Lima');
insert into Tiendas values
(5,'Tienda "Constructores"', ' Av.Constructores 1023, La Molina.Lima');

insert into Inventarios values
(1,'cd-dvd','700 MB de Ram',40.00,5);
insert into Inventarios values
(2, 'usb', ' 64GB', 59.00, 4);
insert into Inventarios values
(3, 'laptop sony', '4GB ram', 1300.00, 3);
insert into Inventarios values
(4,'mouse optico','700dpi',84.00,2);
insert into Inventarios values
(5,'disco duro', ' 200TB', 800.00,1);
insert into Inventarios values
(6,'proyecto ','toshiba', 2500.00,5);


Create Index IDX_INVENTARIO
ON INVENTARIOS(id_tienda);

select i.ID_ARTICULOS, i.NOMBRE, i.PRECIO, i.ID_ARTICULOS, t.nombre, t.direccion
From INVENTARIOS i join TIENDAS t
on t.id_tienda = i.id_tienda;

Create Table Ventas_Diarias
(
ventas_id number primary key,
id_articulo char(10) REFERENCES Inventarios,
fecha date
);

Create sequence Ventas_id
start with 1
increment by 1
maxvalue 99999
minvalue 1
nocycle;

Insert Into VENTAS_DIARIAS
values (VENTAS_ID.NEXTVAL, 3, '05-ABR-2018');

Insert Into VENTAS_DIARIAS
values (VENTAS_ID.NEXTVAL, 4, '06-ABR-2018');

Insert Into VENTAS_DIARIAS
values (VENTAS_ID.NEXTVAL, 5, '07-ABR-2018');

Insert Into VENTAS_DIARIAS
values (VENTAS_ID.NEXTVAL, 1, '08-ABR-2018');

Alter Sequence Ventas_id
increment by 10;

Select EMPLOYEE_ID, SUBSTR(first_name,1,4) as short_name, last_name, salary, salary*0.05 as bonus
From employees;

Select DISTINCT JOB_ID
From EMPLOYEES;

Select EMPLOYEE_ID, FIRST_NAME, LAST_NAME
From EMPLOYEES
where substr(LAST_NAME, 1, 1)
  BETWEEN 'A' and 'L';

Select EMPLOYEE_ID, FIRST_NAME, LAST_NAME
From EMPLOYEES
where LAST_NAME like 'A%';

Select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID
From EMPLOYEES
WHERE DEPARTMENT_ID IN (100,110,120);

Select COUNTRY_NAME,
      CASE region_id
          when 1 THEN 'Europa'
          when 2 THEN 'Latinoamerica'
          ELSE 'OTROS'
      end as Mercado
From COUNTRIES;

--Laboratorio

Select COUNTRY_ID, COUNTRY_NAME
From COUNTRIES
WHERE SUBSTR(COUNTRY_ID, 1,1) NOT LIKE SUBSTR(COUNTRY_NAME,1,1);

SELECT DEPARTMENT_ID, DEPARTMENT_NAME
From DEPARTMENTS
WHERE DEPARTMENT_NAME LIKE 'C%g';

SELECT EMPLOYEE_ID, first_name||' '||last_name
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL OR DEPARTMENT_ID IS NULL;

SELECT JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY,(MAX_SALARY-MIN_SALARY) AS DIFERENCIA
FROM JOBS
ORDER BY JOB_TITLE, MIN_SALARY, MAX_SALARY;

SELECT COUNTRY_ID, STREET_ADDRESS, CITY
FROM LOCATIONS
WHERE COUNTRY_ID LIKE 'JP%' OR
COUNTRY_ID LIKE 'CH%'
OR COUNTRY_ID LIKE 'D%';

SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM Departments
WHERE MANAGER_ID IS NULL;

SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID AS SIN_GERENTE
FROM DEPARTMENTS
WHERE MANAGER_ID IS NULL;

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (20,40,60) AND SALARY BETWEEN 5000 AND 7000
ORDER BY SALARY DESC;

SELECT e.FIRST_NAME, e.LAST_NAME, d.department_name
FROM EMPLOYEES e join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
where d.DEPARTMENT_NAME like 'Sales';

Select e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, jo.JOB_TITLE, j.START_DATE, j.END_DATE, d.DEPARTMENT_NAME, d.DEPARTMENT_ID
From DEPARTMENTS d join EMPLOYEES e
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
join JOB_HISTORY j
on j.EMPLOYEE_ID = e.EMPLOYEE_ID
join jobs jo
on jo.JOB_ID = j.JOB_ID
where d.DEPARTMENT_ID in (80,90,110);

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, CITY, COUNTRY_NAME, REGION_NAME
FROM REGIONS R INNER JOIN COUNTRIES C
ON C.REGION_ID=R.REGION_ID
INNER JOIN LOCATIONS L
ON C.COUNTRY_ID= L.COUNTRY_ID
INNER JOIN DEPARTMENTS D
ON L.LOCATION_ID=D.LOCATION_ID
INNER JOIN EMPLOYEES E
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
WHERE R.REGION_NAME = 'Europe';

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,JOB_TITLE,SALARY,CITY, COUNTRY_NAME, REGION_NAME
FROM REGIONS R INNER JOIN COUNTRIES C
ON C.REGION_ID=R.REGION_ID
INNER JOIN LOCATIONS L
ON C.COUNTRY_ID= L.COUNTRY_ID
INNER JOIN DEPARTMENTS D
ON L.LOCATION_ID=D.LOCATION_ID
INNER JOIN EMPLOYEES E
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
INNER JOIN JOBS J
ON E.JOB_ID=J.JOB_ID
WHERE C.COUNTRY_NAME = 'United States of America'AND E.SALARY > 4000
OR C.COUNTRY_NAME = 'United Kingdom' AND E.SALARY<9000
OR JOB_TITLE = 'Stock Clerk';

--LabConsultas2
Select count(e.EMPLOYEE_ID) as cant_empleados, d.DEPARTMENT_NAME
From EMPLOYEES e join DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_NAME;

Select count(e.EMPLOYEE_ID) as contador, TO_CHAR(j.START_DATE, 'YYYY' ) as ANIO
From EMPLOYEES e join JOB_HISTORY j
on e.EMPLOYEE_ID = j.EMPLOYEE_ID
GROUP BY j.START_DATE
ORDER BY j.START_DATE ASC;

--Laboratorio

Select COUNTRY_ID, COUNTRY_NAME
From COUNTRIES
WHERE SUBSTR(COUNTRY_ID, 1,1) NOT LIKE SUBSTR(COUNTRY_NAME,1,1);

SELECT DEPARTMENT_ID, DEPARTMENT_NAME
From DEPARTMENTS
WHERE DEPARTMENT_NAME LIKE 'C%g';

SELECT EMPLOYEE_ID, first_name||' '||last_name
FROM EMPLOYEES
WHERE MANAGER_ID IS NULL OR DEPARTMENT_ID IS NULL;

SELECT JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY,(MAX_SALARY-MIN_SALARY) AS DIFERENCIA
FROM JOBS
ORDER BY JOB_TITLE, MIN_SALARY, MAX_SALARY;

SELECT COUNTRY_ID, STREET_ADDRESS, CITY
FROM LOCATIONS
WHERE COUNTRY_ID LIKE 'JP%' OR
COUNTRY_ID LIKE 'CH%'
OR COUNTRY_ID LIKE 'D%';

SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM Departments
WHERE MANAGER_ID IS NULL;

SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID AS SIN_GERENTE
FROM DEPARTMENTS
WHERE MANAGER_ID IS NULL;

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (20,40,60) AND SALARY BETWEEN 5000 AND 7000
ORDER BY SALARY DESC;

SELECT e.FIRST_NAME, e.LAST_NAME, d.department_name
FROM EMPLOYEES e join DEPARTMENTS d
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
where d.DEPARTMENT_NAME like 'Sales';

Select e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, jo.JOB_TITLE, j.START_DATE, j.END_DATE, d.DEPARTMENT_NAME, d.DEPARTMENT_ID
From DEPARTMENTS d join EMPLOYEES e
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
join JOB_HISTORY j
on j.EMPLOYEE_ID = e.EMPLOYEE_ID
join jobs jo
on jo.JOB_ID = j.JOB_ID
where d.DEPARTMENT_ID in (80,90,110);

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, CITY, COUNTRY_NAME, REGION_NAME
FROM REGIONS R INNER JOIN COUNTRIES C
ON C.REGION_ID=R.REGION_ID
INNER JOIN LOCATIONS L
ON C.COUNTRY_ID= L.COUNTRY_ID
INNER JOIN DEPARTMENTS D
ON L.LOCATION_ID=D.LOCATION_ID
INNER JOIN EMPLOYEES E
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
WHERE R.REGION_NAME = 'Europe';

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,JOB_TITLE,SALARY,CITY, COUNTRY_NAME, REGION_NAME
FROM REGIONS R INNER JOIN COUNTRIES C
ON C.REGION_ID=R.REGION_ID
INNER JOIN LOCATIONS L
ON C.COUNTRY_ID= L.COUNTRY_ID
INNER JOIN DEPARTMENTS D
ON L.LOCATION_ID=D.LOCATION_ID
INNER JOIN EMPLOYEES E
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
INNER JOIN JOBS J
ON E.JOB_ID=J.JOB_ID
WHERE C.COUNTRY_NAME = 'United States of America'AND E.SALARY > 4000
OR C.COUNTRY_NAME = 'United Kingdom' AND E.SALARY<9000
OR JOB_TITLE = 'Stock Clerk';

--LabConsultas2
Select count(e.EMPLOYEE_ID) as cant_empleados, d.DEPARTMENT_NAME
From EMPLOYEES e join DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
GROUP BY d.DEPARTMENT_NAME;

Select count(e.EMPLOYEE_ID) as contador, TO_CHAR(j.START_DATE, 'YYYY' ) as ANIO
From EMPLOYEES e join JOB_HISTORY j
on e.EMPLOYEE_ID = j.EMPLOYEE_ID
GROUP BY j.START_DATE
ORDER BY j.START_DATE ASC;

/*
Subquery en cláusula WHERE
Este subquery debe devolver sólo un valor
*/
SELECT employee_id, first_name, last_name,
department_id, salary
FROM employees
WHERE salary >= (SELECT salary FROM employees
WHERE first_name = 'Alexander'
AND last_name = 'Hunold');

/*
Subquery en cláusula WHERE
Este subquery debe devolver sólo un valor
Condiciones en subqueries se pueden combinar
con otras
*/
SELECT employee_id, first_name, last_name,
department_id, salary
FROM employees
WHERE salary = (SELECT max(salary) FROM employees
WHERE department_id = 30)
AND department_id = 80;

/*
Subquery en cláusula WHERE
Este subquery puede devolver más de un valor
*/
SELECT d.department_id, d.department_name
FROM departments d
WHERE d.department_id IN (SELECT e.department_id
FROM employees e)
ORDER BY d. department_id;

/*
Subquery en cláusula WHERE
Este subquery puede devolver más de un valor
ALL significa TODOS
ANY significa POR LO MENOS UNO
*/
SELECT employee_id, first_name, last_name,
department_id, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees
WHERE first_name='David');

/*
Subquery en cláusula FROM
Este subquery se utiliza como si fuera una tabla
o vista
*/
SELECT e.last_name, e.salary, e.department_id,
m.max_salary
FROM employees e JOIN
(SELECT department_id, max(salary) max_salary
FROM employees
GROUP BY department_id) m
ON e.department_id = m.department_id
WHERE e.salary < m.max_salary;

/*
Subquery en cláusula SELECT
Este subquery está correlacionado, utiliza columnas
que pertenecen a la sentencia externa
*/
SELECT employee_id, first_name, last_name,
department_id, salary,
(SELECT max(salary)
FROM employees
WHERE department_id=e.department_id) dept_max
FROM employees e;

/*
Semijoin
Subquery correlacionado
*/
SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS
(SELECT 1
FROM employees e
WHERE e.department_id = d.department_id)
ORDER BY d. department_id;

/*
Antijoin
Subquery correlacionado
*/
SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS
(SELECT 1
FROM employees e
WHERE e.department_id = d.department_id)
ORDER BY d. department_id;