SQL FUNDAMENTOS

EXAMEN CERTIFICACIN ORACLE

OCA (ORACLE CERTIFICATION ASOCIATE)


UNIDAD 1
TECNOLOGAS ORACLE SERVER Y EL PARADIGMA RELACIONAL


1. TECNOLOGAS DE SERVIDOR

1.1. ARQUITECTURA ORACLE SERVER

La instancia es un conjunto de procesos y estructuras de memoria, su existencia es temporal,
puede ser iniciada y detenida, los usuarios de la base de datos establecen sesiones
mediante instancias.
Para el manejo del lenguaje SQL se utiliza SQL Developer (Grafico) o SQL*Plus (Consola)

1.2. SERVIDOR DE APLICACIONES ORACLE

Diseado para el manejo de aplicaciones WEB para J2EE.

1.3. ADMINISTRADOR ORACLE ENTERPRISE

Herramienta diseada para la administracin de Oracle, maneja tres formularios:
A. Control de la base de datos
B. Control de servidor de aplicaciones
C. Control GRID

1.4. COMPUTACIN GRID

Combinar procesamiento y almacenamiento para obtener mayor eficacia y efectividad.

2. ENTENDIMIENTO DE ESTRUCTURAS RELACIONALES

2.1. FILAS Y TABLAS

Tablas = Entidad
Fila = Dato
Columna = Atributos



2.2. NORMALIZACIN DE DATOS

1 forma normal = Eliminar atomicidad
2 forma normal = Eliminar dependencias de columnas
3 forma normal = Separar elementos independientes
4 y 5 forma = afinamiento en las tablas

3. RESUMIR EL LENGUAJE SQL

3.1. ESTNDAR SQL

SQL = Lenguaje de consulta estructurado (ISO, ANSI 1979)
Oracle maneja Java, PL/SQL y SQL

3.2. COMANDOS SQL

Existen 16 comandos

DML (Lenguaje de manipulacin de datos)

SELECT
INSERT
UPDATE
DELETE
MERGE

DDL (Lenguaje de definicin de datos)

CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT

DCL (Lenguaje de control de datos)

GRANT
REVOKE

TCL (Lenguaje de control de transacciones)

COMMIT
ROLLBACK
SAVEPOINT

4. USO DE LAS HERRAMIENTAS CLIENTE

4.1. SQL*PLUS

Manejo de instrucciones Oracle a nivel texto


4.2. SQL*PLUS EN LINUX

>/u01/app/oracle/product/db_1/bin/sqlplus
>sqlplus system/admin@orcl

4.3. SQL*PLUS EN WINDOWS

D:\oracle\app\product\11.1.0\db_2\BIN\sqlplus.exe
>sqlplus system/admin@orcl

Forma de conexin sin login

>sqlplus /nolog

4.4. CREANDO Y PROBANDO UNA CONEXIN A UNA BASE DE DATOS

>sqlplus scott/tiger@orcl
>sqlplus scott/tiger@linsrv1.bplc.co.za:1521/orcl.bplc.com

4.5. INSTALANDO Y EJECUTANDO SQL DEVELOPER

Descargar desde ORACLE iberia y descomprimir, ejecutarlo y manejar en modo grafico

5. HACER UNA DEMOSTRACIN DE ESQUEMAS

5.1. ESQUEMA HR y OE

Oracle trae por defecto los esquemas HR y OE

5.2. DEMOSTRACIN DE CREACIN DE ESQUEMAS

>alter user hr account unlock identified by hr;
>alter user oe account unlock identified by oe;

Se puede ingresar como superusuario con

>sqlplus / as sysdba




MODELO LOGICO HR
UNIDAD 2
SENTENCIA SQL SELECT

1. CAPACIDADES DE LA SENTENCIA SQL SELECT

1.1. INTRODUCCIN A LA SENTENCIA SQL SELECT

Bsqueda de datos en las tablas se utiliza la sentencia SELECT


SELECT identifica las columnas what
FROM identifica la tabla which

1.2. COMANDO DESCRIBE EN TABLAS

DESC[RIBE] <SCHEMA>.tablename


2. EJECUTANDO UNA SENTENCIA SELECT BSICA

2.1. Sintaxis de una sentencia SELECT primaria

SELECT * | {[DISTINCT] column|expression [alias],}
FROM table;





2.2. REGLAS A SER SEGUIDAS

Maysculas o minsculas

SELECT * FROM LOCATIONS;
Select * from locations;
select * from locations;


Terminacin de sentencias

select country_name, country_id, location_id from countries;

select city, location_id,
state_province, country_id
from locations
/

Identacin, lectura y buenas prcticas

select city, location_id,state_province, country_id
from locations

2.3. EXPRESIONES Y OPERADORES SQL

Operadores aritmticos










Expresiones y alias en columnas

Expresin: (END_DATE-START_DATE)+1
Alias: select alias [as] a



Operaciones de concatenacin de caracteres y STRING




Literales y la tabla DUAL

SELECT 'literal'||'processing using the REGIONS table'
FROM regions;




Dos Quotes Individuales o la alternativa al operador Quote

Cuando en letras se quiere representar la comilla sencilla se hace doble as:

select 'Plural''s have one quote too many' from dual;








NULL























EJERCICIOS UNIDAD 2
Esta prctica podr realizarse tanto con SQL*PLUS como con SQL Developer. Si se utiliza
SQL*PLUS ejecutar desde una terminal de unix lo siguiente:
sqlplus hr/hr@prod
Si se utiliza SQL Developer ejecutarlo haciendo doble clic en el cono del escritorio, crear
una conexin para el usuario HR y conectarse, todo ello de acuerdo a los pasos que se
detallan a continuacin:




Esquema HR - Descripcin de las tablas
REGIONS contiene regiones como ser Amrica, Asia, etc.

Nombre Nulo? Tipo
----------------------------------------- --------------- ----------------------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)

COUNTRIES contiene pases, cada uno de ellos asociados a una regin.

Nombre Nulo? Tipo
----------------------------------------- --------------- ----------------------------
COUNTRY_ID NOT NULL CHAR(2)
COUNTRY_NAME VARCHAR2(40)
REGION_ID NUMBER

LOCATIONS contiene la direcciones de las oficinas de la compaa en cada pas

Nombre Nulo? Tipo
----------------------------------- --------------- ---------------------------
LOCATION_ID NOT NULL NUMBER(4)
STREET_ADDRESS VARCHAR2(40)
POSTAL_CODE VARCHAR2(12)
CITY NOT NULL VARCHAR2(30)
STATE_PROVINCE VARCHAR2(25)
COUNTRY_ID CHAR(2)

DEPARTMENTS contiene los departamentos de trabajo de las distintas locaciones. Cada
departamento puede o no tener un empleado manager( relacin con tabla EMPLOYEES)

Nombre Nulo? Tipo
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)






EMPLOYEES contiene el detalle de cada empleado trabajando en un departamento.
Algunos empleados pueden no tener asignados departamento

Nombre Nulo? Tipo
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
WORK_RECORD VARCHAR2(4000)

JOBS contiene los diferentes tipos de trabajo que puede tener un empleado

Nombre Nulo? Tipo
----------------------------------------- -------- ----------------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)

JOB_HISTORY contiene el historial de puestos de trabajo de los empleados

Nombre Nulo? Tipo
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
START_DATE NOT NULL DATE
END_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)






2.1 Prctica Comando DESCRIBE

a. En el editor escriba el comando DESCRIBE JOBS.
b. Ejecute el comando presionando F5 en caso de SQL DEVELOPER o <ENTER> en
SQL*PLUS
c. La descripcin de la tabla JOBS aparecer en pantalla
d. Realice los mismos pasos para describir las tablas JOB_HISTORY, LOCATIONS,
COUNTRIES y REGIONS
e. SQL Developer provee adems otra forma para visualizar las estructuras de las tablas.
Navegue por la jerarqua del panel izquierdo que nace a partir del nombre de conexin
hr. Haga click en TABLES y luego en DEPARTMENTS. SQL Developer describe la
tabla automticamente mostrndola en la pantalla de la derecha.

2.2 Prctica Comando SELECT
a. Mostrar todas las columnas de las tablas REGIONS y COUNTRIES:
>SELECT * FROM REGIONS;
>SELECT * FROM COUNTRIES;

b. Consultar nmero de empleado, nombre, apellido, fecha de ingreso y sueldo de todos los
empleados de la tabla EMPLOYEES:

>SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES;

c. Consultar los puestos de trabajo y su salario mximo en la tabla JOBS:

>SELECT JOB_TITLE, MAX_SALARY
FROM JOBS;

2.3. Prctica Comando SELECT con Operadores y expresiones
a. Mostrar de los empleados el nombre, apellido, salario actual y cul sera el salario si se le
aumentan 1000$ (usar alias en las columnas de clculo):

>SELECT FIRST_NAME, LAST_NAME, SALARY Salario actual, SALARY + 1000 Salario futuro
FROM EMPLOYEES;




b. Verificar como cumplen las reglas de precedencia y el efecto del uso de parntesis en las
siguientes sentencias:

>SELECT LAST_NAME, SALARY, 12*SALARY+100+10 AS CALCULO 1
FROM EMPLOYEES;

>SELECT LAST_NAME, SALARY, 12*(SALARY+100)+10 AS CALCULO 2
FROM EMPLOYEES;

c. Consultar el apellido del empleado, la fecha de ingreso y un clculo de la fecha de ingreso
sumados 5 das:

>SELECT LAST_NAME, HIRE_DATE, HIRE_DATE + 5
FROM EMPLOYEES;

d. Consulte la tabla JOBS y retorne una expresin con la siguiente forma:

El cdigo de trabajo para el trabajo <JOB_TITLE> es: <JOB_ID>.
>SELECT 'El codigo de trabajo para el trabajo ' || job_id || ' es:' || job_title
FROM JOBS;
e. Agregue un alias a la columna con el texto Descripcion del trabajo
>SELECT El codigo de trabajo para el trabajo || JOB_TITLE || es: || JOB_ID AS
Descripcion del trabajo FROM JOBS;
f. Mostrar los departamentos que tienen empleados trabajando (no mostrar filas repetidas)

>SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;

g. Mostrar la expresin Hello World (usando la tabla dual)

>SELECT Hello World FROM DUAL;
h. Ejecutar el resultado de la operacin aritmtica 1000 dividido 5:

>SELECT 1000/5 FROM DUAL;





UNIDAD 3
LIMITANDO Y ORDENANDO DATOS

1. LIMITAR LAS FILAS RECUPERADAS POR UNA CONSULTA















a. CLAUSULA WHERE





Condiciones basadas en nmeros

select last_name, salary from employees
where salary = 10000;

select last_name, salary from employees
where salary = department_id;

select last_name, salary from employees
where salary/10 = department_id*10;




Condiciones basadas en caracteres

select last_name
from employees
where job_id='SA_REP';

select employee_id, job_id
from employees
where last_name=first_name;

Condiciones basadas en fechas

select employee_id from job_history
where start_date = end_date;

select employee_id from job_history
where start_date = 13/01/01;














b. OPERADORES DE COMPARACIN

Igualdad y desigualdad



Clause 1: where salary <= 3000;
Clause 2: where salary >= 5000;
Clause 3: where salary <> department_id;
Clause 4: where salary != 4000+department_id;




Condicin BETWEEN



select last_name from employees
where salary between 3400 and 4000;

select first_name, hire_date from employees
where hire_date between '24-JUL-1994' and '07-JUN-1996';

select first_name, hire_date from employees
where '24-JUL-1994' between hire_date+30 and '07-JUN-1996';




Condicin IN


select last_name from employees
where salary in (1000,4000,6000);

select last_name from employees
where last_name in ('King','Garbharran','Ramklass');

select last_name from employees
where hire_date in ('01-JAN-1998','01-DEC-1999');

Condicin LIKE



select first_name from employees
where first_name like 'A%';

select * from jobs
where job_id like 'SA_%';


select job_id from jobs
where job_id like 'SA\_%' escape '\';

Condicin IS NULL



select last_name from employees
where commission_pct is null;

c. OPERADORES BOOLEANOS


Operador AND



select first_name, last_name, commission_pct, hire_date
from employees
where first_name like 'J%'
and commission_pct > 0.1;





Operador OR


select first_name, last_name, commission_pct, hire_date
from employees
where first_name like 'B%'
or commission_pct > 0.35;

Operador NOT


select first_name, last_name, commission_pct, hire_date
from employees
where first_name not like 'B%'
or not (commission_pct > 0.35);
d. Reglas de precedencia









2. ORDENAR LAS FILAS RECUPERADAS POR UNA CONSULTA

a. Clausula ORDER BY




Ordenamiento ascendente y descendente



select last_name, hire_date, salary
from employees
where job_id in ('SA_REP','MK_MAN')
order by last_name;

select last_name, salary, hire_date, hire_date-(salary/10) emp_value
from employees
where job_id in ('SA_REP','MK_MAN')
order by emp_value;



Ordenamiento posicional

select last_name, hire_date, salary
from employees
where job_id in ('SA_REP','MK_MAN')
order by 2;

Ordenamiento compuesto

select job_id, last_name, salary, hire_date
from employees
where job_id in ('SA_REP','MK_MAN')
order by job_id desc, last_name, 3 desc;
3. SUSTITUCIN AMSPERSAND



a. VARIABLES DE SUSTITUCIN

Sustitucin individual Amspersand






Sustitucin doble Amspersand
select first_name, last_name
from employees
where last_name like '%&SEARCH%'
and first_name like '%&SEARCH%';




Sustituyendo nombres de columna
select first_name, job_id, &&col
from employees
where job_id in ('MK_MAN','SA_MAN')
order by &col;


Sustituyendo expresiones y texto

select &SELECT_CLAUSE
from &FROM_CLAUSE
where &WHERE_CLAUSE
order by &ORDER_BY_CLAUSE;



b. DEFINIR Y VERIFICAR









Comandos DEFINE y UNDEFINE


SET DEFINE OFF
SET DEFINE ON







Comando VERIFY

SET VERIFY ON|OFF




































EJERCICIOS UNIDAD 3
1. Prctica Primer parte

Consultar employee_id, last_name, job_id, department_id de los empleados que
pertenezcan al departamento 90

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;

Consultar nombre y apellido de los empleados cuyo sueldo sea mayor o igual a 6000

SELECT last_name, first_name
FROM employees
WHERE salary >= 6000;

Consultar nombre y apellido de los empleados cuyo apellido sea Smith

SELECT last_name, first_name
FROM employees
WHERE last_name = Smith;

Seleccionar apellido y salario de los empleados que ganen entre 1000 y 10000

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 1000 AND 10000;

Recupere de la tabla de departamentos una lista con aquellos nombres de departamento
que finalicen con la cadena ing:

SELECT department_name
FROM departments
WHERE department_name LIKE %ing;







2. Prctica Segunda parte

Mostrar employee_id, last_name, job_id, salary de los empleados que tengan su salario
mayor o igual a 10000 que su job_id contenga la cadena MAN:

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%';

Consultar apellido, nombre y fecha de ingreso de los empleados que ganen 3000$ y
pertenezcan al departamento 50:

SELECT last_name, first_name
FROM employees
WHERE salary = 3000
AND department_id=50;

Consultar apellido y job_id de los empleados cuyo job_id no sea ni 'IT_PROG', ni
'ST_CLERK' ni 'SA_REP' :

SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

3. Prctica Tercera parte
Listar department_id y department_name de la tabla de departamentos ordenado por
department_id de manera ascendente:
SELECT department_id, department_name
FROM departments
ORDER BY department_id;

Mostrar apellido, nombre y fecha de ingreso de los empleados ordenados por fecha de
ingreso de manera descendente:

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC;

De la tabla de cargos (JOBS) obtenga para cada cargo: el nombre, el salario mnimo y
mximo. Obtenga tambin la diferencia entre el salario mximo y el mnimo. Liste
solamente aquellos cargos que tengan en su nombre la cadena President o Manager.
Ordene el resultado en forma descendente por la columna que contiene la variacin de
los salarios:

SELECT job_title, min_salary,max_salary, (max_salary min_salary) variance
FROM jobs
WHERE job_title LIKE %President%' OR
job_title LIKE %Manager%
ORDER BY variance DESC;

4. Prctica Cuarta parte
Consultar employee_id, last_name, salary, department_id de un empleado ingresando el
employee_id, armar la consulta para que permita ingresar dinmicamente el employee_id

SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;

Desarrolle un query que permita determinar el impuesto a deducir a un empleado. El
legajo y el porcentaje a deducir sern ingresados dinmicamente al ejecutar el query. La
sentencia deber mostrar las columnas EMPLOYEE_ID, FIRST_NAME, SALARY,
ANNUAL SALARY (SALARY * 12), porcentaje de impuesto y el importe del impuesto
TAX_RATE/100 *( SALARY *12)

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
SALARY*12 AS ANNUAL SALARY,
&&TAX_RATE,
&TAX_RATE/100*(SALARY*12) AS TAX
FROM EMPLOYEES
WHERE EMPLOYEE_ID = &EMPLOYEE_ID;









UNIDAD 4
FUNCIONES DE UNA SOLA FILA


1. TIPOS DE FUNCIONES DISPONIBLES EN SQL

A. DEFINIENDO UNA FUNCIN














Operando sobre datos de carcter









Manipulacin de caracteres


REPLACE('1#3#5#7#9#','#','->') 1->3->5->7->9->






Operando sobre datos numricos





















Operando sobre informacin de fecha







Date1 Date2 =Num1
Date1 Num1 =Date2
Date1 +Num1 =Date2















































EJERCICIOS UNIDAD 4

1. Prctica Primer parte
Consultar employee_id, apellido y nombre, ambos en mayscula, de los empleados
que pertenezcan al departamento 90:

SELECT employee_id, UPPER(last_name), UPPER(first_name)
FROM employees
WHERE department_id = 90;

Consultar el job_id, el job_id en minscula, job_title y el job_title en minscula de la
tabla de jobs:

SELECT job_id, LOWER(job_id) , job_title, LOWER(job_title)
FROM jobs;

Obtener una lista de los empleados(apellido y nombre) cuyo nombre contenga la
cadena "li" independientemente de que dicha cadena este en maysculas, minsculas
o sea una combinacin de ambas :

SELECT first_name, last_name
FROM employees
WHERE LOWER(first_name) LIKE '%li%';

2. Prctica Segunda parte
Consultar nombre, apellido, el primer carcter del nombre y los primeros 5 caracteres
del apellido de los empleados pertenecientes al departamento 90

SELECT FIRST_NAME, LAST_NAME, SUBSTR(FIRST_NAME,1,1),
SUBSTR(LAST_NAME,1,5)
FROM EMPLOYEES
WHERE DEPARTMENT_ID=90;

Seleccionar apellido, salario, mostrar el salario completando hasta 10 caracteres * a la
izquierda:

SELECT last_name,LPAD(salary,10,'*') "Salary"
FROM employees;


3. Prctica Tercera parte
Ejecutar la siguiente select e interpretar el funcionamiento de la funcin ROUND:

SELECT ROUND(45.923,2),
ROUND(45.923,0),
ROUND(45.923,-1),
ROUND(345.923,-2),
ROUND(355.923,-2)
FROM DUAL;

Mostrar apellido, salario, y el resto de dividir el salario por 5000 de los empleados del
job_id ='SA_REP':

SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';

4. Prctica Cuarta parte
Obtenga la lista de empleados que trabajaron ms de 100 meses desde la fecha en
que fueron contratados. Liste el cdigo de empleado, apellido y fecha de contratacin.

SELECT EMPLOYEE_ID, LAST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE MONTHS_BETWEEN(sysdate, HIRE_DATE) > 100;


















UNIDAD 5
FUNCIONES DE CONVERSIN Y EXPRESIONES CONDICIONALES


1. TIPOS DE FUNCIONES DE CONVERSIN DISPONIBLES EN SQL

A. FUNCIONES DE CONVERSIN


Conversin de datos de tipo implcito



Conversin de datos de tipo explicito



2. FUNCIONES DE CONVERSIN TO_CHAR, TO_NUMBER Y TO_DATE

a. Usando funciones de conversin

ALTER SESSION set NLS_CURRENCY='GBP';
National Language Support (NLS) session parameters


Conversin de nmeros a caracteres con TO_CHAR

TO_CHAR(number1, [format], [nls_parameter]),

select to_char(00001)||' is a special number' from dual;

select to_char(00001,'0999999')||' is a special number' from dual;




Conversin de fechas a caracteres con TO_CHAR

TO_CHAR(date1, [format], [nls_parameter]),


select to_char(sysdate)||' is today''s date' from dual;

select to_char(sysdate,'Month' )||'is a special time' from dual;

select to_char(sysdate,'fmMonth')||'is a special time' from dual;

















Conversin de caracteres a fechas con TO_DATE

TO_DATE(string1, [format], [nls_parameter]),

select to_date('25/12/2010') from dual;

select to_date('25/12') from dual;

select to_date('25-DEC', 'DD-MON') from dual;

select to_date('25-DEC-2010 18:03:45', 'DD-MON-YYYY HH24:MI:SS') from dual;

select to_date('25-DEC-10', 'fxDD-MON-YYYY' ) from dual;

Conversin de caracteres a nmeros con TO_NUMBER

TO_NUMBER(string1, [format], [nls_parameter]),

select to_number('$1,000.55') from dual;

select to_number('$1,000.55','$999,999.99') from dual;







3. EXPRESIONES CONDICIONALES EN SENTENCIA SELECT

A. FUNCIONES ANIDADAS













B. FUNCIONES GENERALES



FUNCIN NVL

NVL(original, ifnull)

select nvl(1234) from dual;

select nvl(null,1234) from dual;

select nvl(substr('abc',4),' No substring exists') from dual;






FUNCIN NVL2

NVL2(original, ifnotnull, ifnull)

select nvl2(1234,1,'a string') from dual;
select nvl2(null,1234,5678) from dual;
select nvl2(substr('abc',2),' Not bc','No substring') from dual;


FUNCIN NULLIF

NULLIF(ifunequal, comparison_term)

select nullif(1234,1234) from dual;
select nullif(1234,123+1) from dual;
select nullif('24-JUL-2009','24-JUL-09') from dual;


FUNCIN COALESCE

COALESCE(expr1, expr2,,exprn)

COALESCE(expr1,expr2) = NVL(expr1,expr2)

COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3))

select coalesce(null, null, null, 'a string') from dual;
select coalesce(null, null, null) from dual;
select coalesce(substr('abc',4),'Not bc','No substring') from dual;





C. FUNCIONES CONDICIONALES



FUNCIN DECODE

DECODE(expr1,comp1, iftrue1,[comp2,iftrue2...[ compN,iftrueN]], [iffalse])



select decode(1234,123,'123 is a match') from dual;

select decode(1234,123,'123 is a match','No match') from dual;

select decode('search','comp1','true1', 'comp2','true2','search','true3',
substr('2search',2,6),'true4' , 'false') from dual;











EXPRESIN CASE




CASE search_expr
WHEN comparison_expr1 THEN iftrue1
[WHEN comparison_expr2 THEN iftrue2

WHEN comparison_exprN THEN iftrueN
ELSE iffalse]
END

select
case substr(1234,1,3)
when '134' then '1234 is a match'
when '1235' then '1235 is a match'
when concat('1','23') then concat('1','23')||' is a match'
else 'no match'
end
from dual;

CASE
WHEN condition1 THEN iftrue1
[WHEN condition2 THEN iftrue2

WHEN conditionN THEN iftrueN
ELSE iffalse]
END


select last_name, hire_date,
trunc(months_between(sysdate,hire_date)/12) years,
trunc(months_between(sysdate,hire_date)/60) "Years divided by 5",
case
when trunc(months_between(sysdate,hire_date)/60) < 1 then 'Intern'
when trunc(months_between(sysdate,hire_date)/60) < 2 then 'Junior'
when trunc(months_between(sysdate,hire_date)/60) < 3 then 'Intermediate'
when trunc(months_between(sysdate,hire_date)/60) < 4 then 'Senior'
else 'Furniture'
end Loyalty
from employees
where department_id in (60,10);













EJERCICIOS UNIDAD 5
1. Prctica Primer parte
Consultar apellido, nombre y salario con el formato '$99,999.99' ordenados por
apellido:

SELECT last_name, first_name, TO_CHAR(salary,'$99,999.99')
FROM employees
ORDER by last_name;

Consultar los job_id , salario mximo y el salario mximo con el formato '9999.99' de
todos los Jobs ordenados por job_id. Evaluar si la mscara elegida es correcta.

SELECT job_id, max_salary, TO_CHAR(max_salary,'9999.99')
FROM jobs
ORDER BY job_id;

Obtener una lista de los empleados(apellido y nombre) cuyo da de ingreso haya sido
sbado. Mostrar adems la fecha de ingreso con el alias START_DATE y con un
formato que se muestre como el siguiente texto:

Saturday, the 17th of February, One Thousand Nine Hundred Ninety-Six.
SELECT first_name,
last_name,
TO_CHAR(hire_date, 'fmDay, "the "ddth "of " Month, Yyyysp.') START_DATE
FROM employees
WHERE TO_CHAR(hire_date,'fmDay') = 'Saturday';

Obtener una lista de los empleados(apellido y fecha de ingreso) , la fecha de ingreso
debe mostrarse con un formato como el siguiente texto:

Seventeenth of June 2007
SELECT last_name,
TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY') HIREDATE
FROM employees;




Mostrar employee_id , job_id y end_date de la tabla job_history ordenado por
end_date. La columna end_date debe mostrarse con el alias "Quitting Date" y con un
formato como el siguiente texto:

SATURDAY the 27th of October 2001
SELECT EMPLOYEE_ID,
job_id,
TO_CHAR(end_date,'fmDAY "the "ddth "of" Month YYYY') "Quitting Date"
FROM job_history
ORDER BY end_date;

2. Prctica Segunda parte
Consultar nombre, apellido y fecha de ingreso de todos los empleados cuya fecha de
ingreso sea mayor al 01/01/2008:

SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date > TO_DATE('01/01/2008', 'DD/MM/YYYY' );

Seleccionar apellido y mostrar concatenadas las 8 primeras letras del apellido con la
cadena _US, a esta concatenacin mostrarla en maysculas:

SELECT last_name,
UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
FROM employees;

Mostrar apellido, salario, commission(mostrar cero si es nula) y el salario anual
sumadas las comisiones:

SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

Mostrar apellido, salario, comisin y la leyenda Gana comisin o No gana comisin
con el alias EMPLOYEE_TYPE segn sea o no nula, para los empleados cuyo
apellido empiece con G:

SELECT last_name, salary, commission_pct,
NVL2(commission_pct,'Gana comisin','No gana comisin') EMPLOYEE_TYPE
FROM employees
WHERE last_name LIKE 'G%';
Evaluar la siguiente funcin COALESCE:

SELECT COALESCE(state_province,postal_code,city), postal_code, state_province, city
FROM locations
WHERE country_id in ('UK','IT','JP');

3. Prctica Tercera parte
Buscar dentro de la tabla LOCATIONS las filas que tengan el country_id=US. Mostrar
la siguiente leyenda con alias LOCATION_INFO segn sea el valor de la columna
STATE_PROVINCE(usar decode). Ordenar por LOCATION_INFO.

Washington mostrar Headquarters
Texas mostrar Oil Wells
California mostrar CITY column value
New Jersey mostrar The STREET_ADDRESS column value

SELECT DECODE(state_province, 'Washington', 'Headquarters',
'Texas', 'Oil Wells',
'California', city,
'New Jersey', street_address) LOCATION_INFO
FROM locations
WHERE country_id='US'
ORDER BY location_info;

Mostrar apellido, salario y la siguiente leyenda con el alias qualified_salary para cada
empleado segn sea el caso usando CASE:

Salario menor a 5000 mostrar Low
Salario menor a 10000 mostrar Medium
Salario menor a 20000 mostrar Good

SELECT last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
WHEN salary<10000 THEN 'Medium'
WHEN salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;

UNIDAD 6
INFORMES AGREGADOS DE DATOS MEDIANTE FUNCIONES DE GRUPO

1. FUNCIONES DE GRUPO

a. Definicin de las funciones de grupo







b. Tipos y sintaxis de las funciones de grupo






COUNT({*|[DISTINCT|ALL] expr});

1. COUNT(*)
2. COUNT(DISTINCT expr)
3. COUNT(ALL expr)
4. COUNT(expr)

Query 1: select count(*) from employees
Query 2: select count(commission_pct) from employees
Query 3: select count(distinct commission_pct) from employees
Query 4: select count(hire_date), count(manager_id) from employees

AVG([DISTINCT|ALL] expr);



1. AVG(DISTINCT expr)
2. AVG(ALL expr)
3. AVG(expr)

Query 1: select avg(2) from employees
Query 2: select avg(salary) from employees
Query 3: select avg(distinct salary) from employees
Query 4: select avg(commission_pct) from employees

SUM([DISTINCT|ALL] expr) ;

1. SUM(DISTINCT expr)
2. SUM(ALL expr)
3. SUM(expr)

Query 1: select sum(2) from employees
Query 2: select sum(salary) from employees
Query 3: select sum(distinct salary) from employees
Query 4: select sum(commission_pct) from employees


MAX([DISTINCT|ALL] expr); MIN([DISTINCT|ALL] expr)

1. MAX(DISTINCT expr); MIN(DISTINCT expr)
2. MAX(ALL expr); MIN(ALL expr)
3. MAX(expr); MIN(expr);

Query 1: select min(commission_pct), max(commission_pct) from employees
Query 2: select min(start_date),max(end_date) from job_history
Query 3: select min(job_id),max(job_id) from employees

VARIANCE([DISTINCT|ALL] expr);

1. VARIANCE(DISTINCT expr)
2. VARIANCE(ALL expr)
3. VARIANCE(expr)


STDDEV([DISTINCT|ALL] expr);

1. STDDEV(DISTINCT expr)
2. STDDEV(ALL expr)
3. STDDEV(expr)













2. USANDO GRUPOS DE DATOS CON LA CLAUSULA GROUP BY

G1(group_item) = result
G1(G2(group_item ) = result
G1(G2(G3(group_item))) is NOT allowed.


a. GROUP BY

SELECT column|expression|group_function(column|expression [alias]),}
FROM table
[WHERE condition(s)]
[GROUP BY {col(s)|expr}]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];


select max(salary), count(*)
from employees
group by department_id
order by department_id;
















b. Agrupacin para mltiples columnas





select department_id, sum(commission_pct)
from employees
where commission_pct is not null
group by department_id;

select department_id, job_id, sum(commission_pct)
from employees
where commission_pct is not null
group by department_id, job_id;









3. INCLUIR O EXCLUIR FILAS AGRUPADAS CON LA CLAUSULA HAVING




a. Restringir los resultados del grupo

select department_id
from job_history
where department_id in (50,60,80,110);

select department_id, count(*)
from job_history
where department_id in (50,60,80,110)
group by department_id;

b. La clausula HAVING


SELECT column|expression|group_function(column|expression [alias]),}
FROM table
[WHERE condition(s)]
[GROUP BY {col(s)|expr}]
[HAVING group_condition(s)]
[ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];





select department_id, count(*)
from job_history
where department_id in (50,60,80,110)
group by department_id
having count(*)>1;












Ejercicios UNIDAD 6
1. Prctica Primer parte
Contar la cantidad de empleados que se encuentran en el departamento 50:

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

Contar la cantidad de empleados del departamento 50 cuya comisin es no nula :

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 80;

Contar la cantidad de departamentos distintos que existen dentro de tabla empleados:

SELECT COUNT(DISTINCT department_id)
FROM employees;

Sumar los salarios de todos los empleados que ingresaron despus del 01/01/2008:

SELECT SUM(salary)
FROM employees
WHERE hire_date > TO_DATE(01/01/2008,DD/MM/YYYY);

Sumar los distintas comisiones de los empleados del departamento 80:

SELECT SUM(DISTINCT commission_pct)
FROM employees
WHERE department_id=80

Obtener el clculo del salario promedio de todos los empleados de la compaa:

SELECT AVG(salary)
FROM employees;

Obtener la mnima y mxima fecha de ingreso, el mnimo y mximo salario de los
empleados con job_id ='SA_REP' :

SELECT MIN(hire_date),MIN(salary), MAX(hire_date),MAX(salary)
FROM employees
WHERE job_id='SA_REP';
2. Prctica Segunda parte
Obtener el mximo salario y cantidad de empleados por departamento de todos los
empleados. Ordenar la salida por department_id:
SELECT department_id, MAX(salary), COUNT(*)
FROM employees
GROUP BY department_id
ORDER BY department_id;

Mostrar la cantidad de empleados ingresados por ao en la compaa, ordenarlos de
manera descendente por cantidad de ingresados:

SELECT TO_CHAR(HIRE_DATE,' YYYY') , COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,' YYYY')
ORDER BY COUNT(*) DESC;

Obtener el salario promedio por departamento de todos los empleados:

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

Obtener la suma de los salaries por puesto de trabajo dentro de cada departamento.
Ordenar la salida por department_id:

SELECT department_id , job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id;

3. Prctica Tercera parte
Mostrar aquellos departamentos cuyo salario mximo sea mayor de 10000$.

e r SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;




Mostrar los das de la semana donde hubo ms de 15 ingresos de empleados:

SELECT TO_CHAR(HIRE_DATE,'DAY'), COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'DAY')
HAVING COUNT(*) > 15;

Mostrar aquellos puestos de trabajo que no contentan la cadena REP en su job_id y
que la suma de los salarios en cada uno de los mismos sea mayor a 13000$.
Mostrarlos ordenados por suma de salarios:

SELECT job_id, SUM(salary)
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);


























UNIDAD 7
MOSTRAR DATOS DE VARIAS TABLAS





































1. SENTENCIA SELECT PARA ACCEDER A DATOS EQUIJOINS Y NONEQUIJOINS

Ejemplo de la misma consulta escrita en SQL ANSI 1999 y la sintxis propia de Oracle

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e
JOIN departments d
ON (e.department_id = d.department_id);

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;

a. Tipos de JOINS

EQUIJOINS: Join sobre dos o ms tablas, por igualdad de campos.








NATURAL JOINS





SELECT table1.column, table2.column
FROM table1
NATURAL JOIN table2;









SELECT table1.column, table2.column
FROM table1
JOIN table2 USING (join_column1, join_column2);




SELECT table1.column, table2.column
FROM table1
JOIN table2 ON (table1.column_name = table2.column_name);







select r.region_name, c.country_name, l.city, d.department_name
from departments d
join locations l on (l.location_id=d.location_id)
join countries c on (c.country_id=l.country_id)
join regions r on (r.region_id=c.region_id);

select r.region_name, c.country_name, l.city, d.department_name
from departments d
join locations l using (location_id)
join countries c using (country_id)
join regions r using (region_id);

Query 1: select d.department_name from departments d
join locations l on (l.LOCATION_ID=d.LOCATION_ID)
where d.department_name like 'P%'

Query 2: select d.department_name from departments d
join locations l on
(l.LOCATION_ID=d.LOCATION_ID and d.department_name like 'P%')





NONEQUIJOINS
Por desigualdad, sin correspondencia directa entre campos de tablas. La relacin se puede
establecer mediante criterios de rango (<, >, BETWEEN, ...)

SELECT table1.column, table2.column
FROM table1
[JOIN table2 ON (table1.column_name < table2.column_name)]|
[JOIN table2 ON (table1.column_name > table2.column_name)]|
[JOIN table2 ON (table1.column_name <= table2.column_name)]|
[JOIN table2 ON (table1.column_name >= table2.column_name)]|
[JOIN table2 ON (table1.column BETWEEN table2.col1 AND table2.col2)]|



2. UNIENDO UNA TABLA AS MISMA CON SELF-JOIN

a. Uniendo una tabla dentro de s misma usando la clausula JOIN ON

Query 1: select id, name, father_id from family;

Query 2: select name from family where id=&father_id;

Query 3: select f1.name Dad, f2.name Child from family f1 join family f2 on (f1.id=f2.father_id)






3. VISTA DE DATOS QUE NO CUMPLE CON UNA CONDICIN DE UNIN MEDIANTE
OUTER JOINS

a. LEFT OUTER JOINS



SELECT table1.column, table2.column
FROM table1
LEFT OUTER JOIN table2
ON (table1.column = table2.column);

Query 1: select e.employee_id, e.department_id EMP_DEPT_ID,
d.department_id DEPT_DEPT_ID, d.department_name
from departments d
left outer join employees e
on (d.DEPARTMENT_ID=e.DEPARTMENT_ID)
where d.department_name like 'P%'







b. RIGHT OUTER JOINS




SELECT table1.column, table2.column
FROM table1
RIGHT OUTER JOIN table2
ON (table1.column = table2.column);

select e.last_name, d.department_name from departments d
right outer join employees e
on (e.department_id=d.department_id)
where e.last_name like 'G%';














c. FULL OUTER JOINS



SELECT table1.column, table2.column
FROM table1
FULL OUTER JOIN table2
ON (table1.column = table2.column);





4. GENERAR UN PRODUCTO CARTESIANO DE 2 O MS TABLAS

a. Creando productos cartesianos con CROSS JOIN



SELECT table1.column, table2.column
FROM table1
CROSS JOIN table2;

Query 1: select * from jobs cross join job_history;
Query 2: select * from jobs j cross join job_history jh
where j.job_id='AD_PRES';













Ejercicios UNIDAD 7

1. Prctica Primer parte
Mostrar department_id, department_name, street_address y city de todos los
departamentos que se encuentren en la ciudad de Toronto. Usar las tablas
DEPARTMENTS Y LOCATIONS, usar NATURAL JOIN para obtener los resultados.

SELECT department_id, department_name, STREET_ADDRESS, city
FROM departments
NATURAL JOIN locations
WHERE city='Toronto'

Crear una consulta para mostrar nombre de pais y regin de todos los paises. Usar las
tablas COUNTRIES y REGIONS, usar NATURAL JOIN para obtener los resultados.

SELECT country_name, region_id, region_name
FROM countries NATURAL JOIN regions;

Crear una consulta para mostrar employee_id, last_name, first_name,
department_name de todos los empleados. Usar JOIN USING para obtener los
resultados:

SELECT employee_id, last_name, first_name, department_name
FROM employees
JOIN departments USING (department_id);

Buscar apellido, fecha de ingreso, puesto de trabajo actual, el primer puesto en la
compaa junto con la fecha de comienzo y fin en dicho puesto. Usar tablas
JOB_HISTORY y EMPLOYEES, usar JOIN ON.

SELECT e.last_name, e.hire_date, e.job_i d CURRENT_JOB,
j.job_id PREVIOUS_JOB, j.start_date, j.end_date,
FROM job_history j
JOIN employees e
ON (j.start_date = e.hire_date AND j.employee_id=e.employee_id);




Buscar employee_id, last_name, department_id, department_name, manager_id de
todos los empleados cuyo manager sea el 149. Usar tablas EMPLOYEES,
DEPARTMENTS y usar JOIN ON para obtener los resultados:

SELECT e.employee_id, e.last_name, e.department_id,
d.department_name, e.manager_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 149;

Consultar todos los departamentos y mostrar nombre de departamento, ciudad, pas y
regin donde se encuentra ubicado. Ordenado por departamento. Usar tablas
DEPARTMENTS, LOCATIONS, COUNTRIES, REGIONS, usar JOIN ON:

SELECT d.department_name, l.city, c.country_name, r.region_name
FROM departments d
JOIN locations l ON (l.location_id=d.location_id)
JOIN countries c ON (c.country_id=l.country_id)
JOIN regions r ON (r.region_id=c.region_id)
ORDER BY d.department_name;

2. Prctica Segunda parte
Mostrar nombre y apellido de cada empleado y el nombre y apellido de su manager.
Usar los alias de tablas worker y manager.

SELECT worker.first_name , worker.last_name ,
manager.first_name , manager.last_name
FROM employees worker
JOIN employees manager
ON (worker.manager_id = manager.employee_id);

Obtener el producto cartesiano de las regiones 3 y 4 con todos los paises, ordenar por
regin y pas

SELECT r.region_name, c.country_name
FROM regions r
CROSS JOIN countries c
WHERE r.region_id IN (3,4)
ORDER BY region_name, country_name;

3. Prctica Tercera parte
Mostrar apellido, department_id, department_name para todos los empleados, mostrar
aquellos empleados que no tengan departamento asignado tambin.

e r SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

Mostrar deparment_id, department_name de todos los departamentos y los
empleados que pertenecen a cada uno de ellos. Incluir tambin aquellos
departamentos que no tengan empleados asignados:

SELECT e.department_id, d.department_name, e.last_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

Mostrar apellido, department_id, department_name para todos los empleados, mostrar
aquellos empleados que no tengan departamento asignado as como tambin todos
los departamentos que no tengan empleados asignados.

SELECT e.last_name, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);





















UNIDAD 8
USO DE SUBCONSULTAS


1. DESCRIBIR LOS TIPOS DE PROBLEMAS QUE PUEDEN RESOLVER LAS
SUBCONSULTAS










a. El uso de un conjunto de resultados de subconsultas para propsitos de
comparacin



select avg(salary) from employees;
select last_name from employees where salary < result_of_previous_query;

select last_name from employees
where salary < (select avg(salary)from employees);

select department_name from departments where department_id in
(select distinct(department_id) from employees);

select department_name from departments inner join employees
on employees.department_id = departments.department_id
group by department_name;

b. Transformacin Estrella

select from sales s, products p, buyers b, channels c
where s.prod_code=p.prod_code
and s.buy_code=b.buy_code
and s.chan_code=c.chan_code
and p.product=Books
and b.country=Germany
and c.channel=Internet;

c. Generando una tabla con subconsultas SELECT

select avg(salary),country_id from
(select salary,department_id,location_id,country_id from
employees natural join departments natural join locations)
group by country_id;
d. Generar valores de proyeccin

select
(select max(salary) from employees) *
(select max(commission_pct) from employees) / 100
from dual;

e. Generando filas para ser pasadas a sentencias DML

insert into dates select sysdate from dual;

2. LISTAR LOS TIPOS DE SUBCONSULTAS

Single-row subqueries
Multiple-row subqueries
Correlated subqueries










Subconsultas correlacionadas

select p.last_name, p.department_id
from employees p
where p.salary < (select avg(s.salary) from employees s
where s.department_id=p.department_id);



3. ESCRIBIR CONSULTAS DE MLTIPLES Y NICAS FILAS

select last_name from employees
where manager_id in
(select employee_id from employees where department_id in
(select department_id from departments where location_id in
(select location_id from locations where country_id=UK)));

select job_title from jobs natural join employees group by job_title
having avg(salary) =
(select max(avg(salary)) from employees group by job_id);

select last_name from employees where salary > all
(select salary from employees where department_id=80);
select last_name from employees where salary >
(select max(salary) from employees where department_id=80);


















Ejercicios UNIDAD 8

1. Prctica Primer parte
Escribir un query que use subqueries en la lista de columnas del select. El query debe
reportar la fecha de hoy, el nmero total de departamentos y el nmero total de
empleados:

SELECT sysdate Today,
(SELECT COUNT(*) FROM departments) Dept_count,
(SELECT COUNT(*) FROM employees) Emp_count
FROM dual;

Escribir una query que use subqueries en la lista de columnas del select. Identificar el
mximo salario y la mxima comisin de todos los empleados.

SELECT (SELECT MAX(salary) FROM employees) "Max Salary" ,
(SELECT MAX(commission_pct) FROM employees) "Max Commission"
FROM dual;

Buscar apellido, job y salario de todos los empleados cuyo salario sea igual al salario
mnimo. Usar subquery en el WHERE.

SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);

Buscar el apellido y job de los empleados que tengan el mismo job que el empleado
141. Usar subquery en el WHERE:

SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141);




Buscar apellido, job y salario de todos los empleados que tengan el mismo job que el
empleado de apellido Taylor y salario mayor a Taylor. Usar subqueries en el WHERE,
evaluar el porqu del error de esta query.

SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Taylor')
AND salary >
(SELECT salary
FROM employees
WHERE last_name = 'Taylor');

Consultar apellido, nombre y salario de todos los empleados cuyo salario es mayor
que el del empleado con apellido Abel, usar subquery en WHERE.

SELECT last_name, first_name, salary
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');

Buscar los apellidos de todos los empleados que tengan un salario mayor al salario
promedio general, usar subquery en el WHERE

SELECT last_name
FROM employees
WHERE salary < (SELECT AVG(salary) FROM employees);

Buscar el salario mnimo de aquellos departamentos que tengan el salario mnimo
mayor al del departamento 50. Usar suquery en HAVING.

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);

2. Prctica Segunda parte

Escribir un query que muestre el apellido y nombre de todos los empleados que sean
managers. Usar operador IN.
SELECT last_name
FROM employees
WHERE (employee_id IN (SELECT manager_id FROM employees));

Mostrar el employee_id, apellido, job y salario de todos los empleados que no sean
IT_PROG y a su vez que sus salarios sean menores a los salario del puesto
IT_PROG. Utilizar operador ANY .

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

Mostrar el employee_id, apellido, job y salario de todos los empleados que no sean
ST_CLERK y a su vez que sus salarios sean mayores a los salario del puesto
ST_CLERK. Utilizar operador ALL .

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ALL
(SELECT salary
FROM employees
WHERE job_id = 'ST_CLERK')
AND job_id <> 'ST_CLERK;

Obtener un subquery en el FROM obteniendo los campos salary, department_id,
location_id y country_id haciendo natural join entre employees , departments,
locations. A partir de ese subquery obtener salario mximo por pais.

SELECT MAX(salary),country_id
FROM (SELECT salary,department_id, location_id,country_id
FROM employees
NATURAL JOIN departments
NATURAL JOIN locations)
GROUP BY country_id;
Mostrar department_id, apellido y salario de todos los empleados cuyo salario sea
mayor al salario promedio del departamento al cual pertenecen. Usar correlated
subquery.

SELECT department_id, last_name, salary
FROM employees x
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE x.department_id = department_id)
ORDER BY department_id;





































UNIDAD 9
USANDO OPERADORES DE CONJUNTO


1. DESCRIBIR LOS OPERADORES DE CONJUNTO

UNION Returns the combined rows from two queries, sorting them and removing
duplicates.

UNION ALL Returns the combined rows from two queries without sorting or removing
duplicates.

INTERSECT Returns only the rows that occur in both queries result sets, sorting them and
removing duplicates.

MINUS Returns only the rows in the first result set that do not appear in the second result
set, sorting them and removing duplicates.

2. UTILIZAR UN OPERADOR DE CONJUNTO PARA COMBINAR VARIAS CONSULTAS
EN UNA SOLA CONSULTA


a. Operador UNION ALL






b. Operador UNION



c. Operador INTERSECT Y MINUS









3. CONTROLAR EL ORDEN DE LAS FILAS DEVUELTAS

select department_id dept,to_number(null) mgr,sum(salary)
from employees
group by department_id
union all
select to_number(null),manager_id,sum(salary) from employees
group by manager_id
union all
select to_number(null),to_number(null),sum(salary) from
employees;

select department_id dept,to_number(null) mgr,sum(salary)
from employees
group by department_id
union
select to_number(null),manager_id,sum(salary) from employees
group by manager_id
union all
select to_number(null),to_number(null),sum(salary) from
employees;

select 20,count(1) from employees where department_id=20
union all
select 30,count(1) from employees where department_id=30
union all
select 40,count(1) from employees where department_id=40;

select manager_id from employees where department_id=20
intersect
select manager_id from employees where department_id=30
minus
select manager_id from employees where department_id=40;

select department_id dept,to_number(null) mgr,sum(salary)
from employees
group by department_id
union
select to_number(null),manager_id,sum(salary) from employees
group by manager_id
union all
select to_number(null),to_number(null),sum(salary) from
employees;




Ejercicios UNIDAD 9
1. Prctica Primer parte
Ejecute el siguiente query para comprobar el funcionamiento del operador UNION.
Que se observa? Cuantas veces se repite cada regin?

SELECT region_name
FROM regions
UNION
SELECT region_name
FROM regions;

Ahora ejecute el mismo query con UNION ALL para comprobar el funcionamiento de
este operador

SELECT region_name
FROM regions
UNION ALL
SELECT region_name
FROM regions;

Listar los empleados y puestos de trabajo actuales junto con los empleados y puesto
del historial. Usar operador UNION para no mostrar filas repetidas, evitando mostrar
aquellos empleados que actualmente tengan un puesto que hayan ocupado en el
pasado.

SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

Mostrar los employee_id de los empleados que actualmente son SA_MAN junto con
los que en el pasado fueron SA_REP(utilizo UNION por si empleados estuvieron en
ambos puestos de trabajo):

SELECT employee_id
FROM employees
WHERE job_id in ('SA_MAN')
UNION
SELECT employee_id
FROM job_history
WHERE job_id in ('SA_REP');
Mostrar department_id, department_name, city de los departamentos que estn en
Toronto y los que estn en Seattle. Usar operador de conjuntos(conveniente usar
UNION ALL ya que no hay departamentos que estn en ambas ciudades a la vez, se
evita el ordenamiento)

SELECT department_id, department_name, city
FROM departments
NATURAL JOIN locations
WHERE city='Toronto'
UNION ALL
SELECT department_id, department_name, city
FROM departments
NATURAL JOIN locations
WHERE city='Seattle';

Mostrar en un mismo query department_id, deparment_name del departamento 80
junto con employee_id, last_name de los empledos que pertenezcan al departamento
80. Usar operadores de conjunto.

SELECT department_id, department_name
FROM departments
WHERE department_id =80
UNION ALL
SELECT employee_id, last_name
FROM employees
WHERE department_id =80;

2. Prctica Segunda parte
Mostrar employee_id y job_id de aquellos empleados que tienen actualmente un
puesto de trabajo que ya han tenido en el pasado.
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;





Crear un reporte que liste los employee_id y job_id de los empleados cuyo trabajo
actual coincide con el puesto de trabajo que tuvieron cuando entraron a la compaa.

SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT e.employee_id, j.job_id
FROM job_history j
JOIN employees e
ON (j.start_date = e.hire_date AND j.employee_id=e.employee_id);

Listar los departamentos que no contengan el puesto de trabajo(job_id) ST_CLERK.
Usar operadores de conjunto para realizar este reporte.

SELECT department_id
FROM departments
MINUS
SELECT department_id
FROM employees
WHERE job_id='ST_CLERK';

Mostrar el nombre de aquellos paises donde no haya departamentos de la compaa.

SELECT country_name
FROM countries
MINUS
SELECT country_name
FROM departments
NATURAL JOIN locations
NATURAL JOIN countries;

Mostrar los employee_id de aquellos empleados que no han cambiado nunca su
puesto de trabajo:

SELECT employee_id
FROM employees
MINUS
SELECT employee_id
FROM job_history;



Mostrar los employee_id de aquellos empleados que han trabajado en el pasado tanto
en SA_REP como en SA_MAN

SELECT employee_id
FROM job_history
WHERE job_id='SA_REP'
INTERSECT
SELECT employee_id
FROM job_history
WHERE job_id='SA_MAN';

3. Prctica Tercer parte
Mostrar employee_id , job_id y salarios actuales de los empleados junto con los
puestos que tuvieron en el pasado. Como job_history no guarda salarios pasados usar
columna dummy en cero(literal 0) para poder unir ambas tablas. Usar UNION, el
reporte saldr ordenado

SELECT employee_id, job_id,salary
FROM employees
UNION
SELECT employee_id, job_id,0
FROM job_history;

Ejecutar el siguiente query y evaluar como participan las columnas dummy.

SELECT location_id, department_name "Department", TO_CHAR(NULL) "Warehouse
location"
FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department", state_province
FROM locations;











UNIDAD 10
MANIPULACIN DE DATOS (DML)


SELECT
INSERT
UPDATE
DELETE
MERGE

1. INSERTAR FILAS EN UNA TABLA




insert into hr.regions values (10,'Great Britain');
insert into hr.regions (region_name, region_id) values ('Australasia',11);
insert into hr.regions (region_id) values (12);
insert into hr.regions values (13,null);


















insert into employees (employee_id, last_name, hire_date)
values (1000,'WATSON','03-Nov-07');

insert into employees (employee_id, last_name, hire_date)
values (1000,upper('Watson'),to_date('03-Nov-07' ,'dd-mon-yy' ));

insert into employees (employee_id,last_name,hire_date)
values (1000 + 1,user,sysdate - 7);

INSERT INTO table [ (column [, column] ) ] subquery;

insert into regions_copy select * from regions;

truncate table department_salaries;
insert into department_salaries (department,staff,salaries)
select coalesce(department_name,'Unassigned'),
count(employee_id),
sum(coalesce(salary,0))
from employees e full outer join departments d
on e.department_id = d.department_id
group by department_name
order by department_name;
insert all
when 1=1 then
into emp_no_name (department_id,job_id,salary,commission_pct,hire_date)
values (department_id,job_id,salary,commission_pct,hire_date)
when department_id <> 80 then
into emp_non_sales (employee_id,department_id,salary,hire_date)
values (employee_id,department_id,salary,hire_date)
when department_id = 80 then
into emp_sales (employee_id,salary,commission_pct,hire_date)
values (employee_id,salary,commission_pct,hire_date)
select employee_id,department_id,job_id,salary,commission_pct,hire_date
from employees where hire_date > sysdate - 30;





















2. ACTUALIZAR FILAS EN UNA TABLA

UPDATE table SET column=value [,column=value] [WHERE condition];
























3. BORRAR FILAS EN UNA TABLA

a. Removiendo filas con DELETE








delete from employees where employee_id=206;
delete from employees where last_name like 'S%';
delete from employees where department_id=&Which_department;
delete from employees where department_id is null;

delete from employees where department_id in
(select department_id from departments where location_id in
(select location_id from locations where country_id in
(select country_id from countries where region_id in
(select region_id from regions where region_name='Europe'))))

b. Removiendo filas con TRUNCATE

TRUNCATE TABLE table;

c. MERGE











MERGE INTO employees e using new_employees n
on (e.employee_id = n.employee_id)
when matched then
update set e.salary=n.salary
when not matched then
insert (employee_id,last_name,salary)
values (n.employee_id,n.last_name,n.salary);







4. CONTROL DE TRANSACCIONES







a. Declaraciones de control de transacciones



COMMIT

COMMIT;

ROLLBACK











ROLLBACK [TO SAVEPOINT savepoint] ;





SAVEPOINT

SAVEPOINT savepoint;


AUTOCOMMIT

SET AUTOCOMMIT ON
SET AUTOCOMMIT OFF






































Ejercicios UNIDAD 10
1. Prctica Primer parte
Haga un query de la tabla REGIONES para verificar que valores estn usados en la
columna REGION_ID

SELECT * FROM regions;
Inserte una fila en la tabla REGIONS, proporcionando los valores 101, Great Britain.

INSERT INTO regions VALUES (101,'Great Britain');
Inserte una fila en la tabla REGIONS, proporcionando los valores como variables de
sustitucin

INSERT INTO regions VALUES (&Region_number,'&Region_name' );
Al solicitar el valor, ingrese 102 para el numrico, Australia para el nombre. Usar comillas
en el ingreso del string.
Inserte una fila en la tabla REGIONS, calculando la REGION_ID como un valor ms
alto que el valor ms alto actual. Usar subquery en clusula values.

INSERT INTO regions VALUES ((SELECT MAX(region_id)+1 FROM regions), 'Oceania');
Confirmar la insercin de las filas.

SELECT * FROM regions;
Hacer commit.
COMMIT;
2. Prctica Segunda parte
Modifique la columna region_name de fila de regions con region_id=101, actualizar
con Scandinavia:

UPDATE regions
SET region_name='Scandinavia'
WHERE region_id=101;

Devolver el mensaje 1 row updated
Modifique la columna region_name de la tabla regions de aquellas filas con region_id
mayor a 100. Setear region_name igual a Iberia

UPDATE regions SET region_name='Iberia' WHERE region_id > 100;
Devolver el mensaje 3 rows updated
Ejecutar el siguiente update con subqueries y analizar que realiza.

UPDATE regions
SET region_id=(region_id+(SELECT MAX(region_id) FROM regions))
WHERE region_id IN (SELECT region_id
FROM regions
WHERE region_id > 100);

Devolver el mensaje 3 rows updated
Confirmar el estado de las filas.

SELECT * FROM regions;
Hacer commit.

COMMIT;
3. Prctica Tercer parte
Borrar de regions la fila con region_id = 204:

DELETE FROM regions WHERE region_id=204;
Devolver el mensaje 1 row deleted
Intentar borrar todas las filas de la tabla regions , que ocurre?

DELETE FROM regions;
Dar error, debido a una violacin de constraints.
Borrar filas ejecutando el siguiente delete con subqueries:


DELETE FROM regions
WHERE region_id IN (SELECT region_id FROM regions
WHERE region_name='Iberia');

Devolver el mensaje 2 rows deleted
Confirmar que la tabla REGIONS contiene las 4 filas originales.

SELECT * FROM regions;
Hacer commit.

COMMIT;
4. Prctica Cuarta parte
Conectar dos sesiones concurrentemente con usuario HR. La siguiente tabla lista los pasos a
seguir en cada sesin:
Paso En su primera sesin En su segunda sesin
1 select * from regions; select * from regions;
2 insert into regions insert into regions
values(100,'UK'); values(101,'GB');
3 select * from regions; select * from regions;

Ambas sesiones ven datos diferentes: los datos originales ms sus
propios cambios
4 commit;
5 select * from regions; select * from regions;

La transaccin de la sesin 1 public los datos para todos, la otra
transaccin
sigue siendo visible para la sesion 2 nicamente
6 rollback; rollback;
7 select * from regions; select * from regions;

En la primer session no hay nada para rollbackear, la segunda session si
har rollback ya que no se haba ejecutado commit hasta el momento
8 delete from regions where delete from regions where
region_id=100; region_id=101;
9 select * from regions; select * from regions;
Cada fila borrada sigue estando visible en la session contraria
10 commit; commit;
11 select * from regions; select * from regions;

Transacciones finalizadas, ambas sesiones ahora tienen una lectura
consistente de la tabla


Conectar 2 sesiones concurrentes, ejecutar en cada una de ellas el siguiente select for
update:

SELECT * FROM regions
WHERE region_id=2
FOR UPDATE;




















UNIDAD 11
USO DE SENTENCIAS DDL PARA CREAR Y GESTIONAR TABLAS


1. CLASIFICAR OBJETOS DE LA BASE DE DATOS PRINCIPAL

a. Tipos de Objetos

SQL> select object_type,count(object_type) from dba_objects


b. Usuarios y esquemas


A user is a person who can connect to the database.

The user will have a username and a password. A schema is a container for the objects
owned by a user.

When a user is created, their schema is created too.

A schema is the objects owned by a user; initially, it will be empty

c. Nombrar objetos de esquema


d. Espacio de nombres de objetos

Tables
Views
Sequences
Private synonyms











2. ENUMERAR LOS TIPOS DE DATOS QUE ESTN DISPONIBLES PARA LAS
COLUMNAS



a. VARCHAR2
Variable-length character data, from 1 byte to 4KB. The data is stored in the database
character set.

b. NVARCHAR2
Like VARCHAR2, but the data is stored in the alternative national language character set, one
of the permitted Unicode character sets.

c. CHAR
Fixed-length character data, from 1 byte to 2KB, in the database character set. If the data is
not the length of the column, then it will be padded with spaces.

d. RAW
Variable-length binary data, from 1 byte to 4KB. Unlike the CHAR and VARCHAR2 data
types, RAW data is not converted by Oracle Net from the databases character set to the user
processs character set on SELECT or the other way on INSERT.

e. NUMBER
Numeric data, for which you can specify precision and scale.
The precision can range from to 1 to 38, the scale can range from 84 to 127.

f. FLOAT
This is an ANSI data type, floating-point number with precision of 126 binary (or 38 decimal).
Oracle also provides BINARY_FLOAT and BINARY_DOUBLE as alternatives.

g. INTEGER
Equivalent to NUMBER, with scale zero.

h. DATE
This is either length zero, if the column is empty, or 7 bytes. All DATE data includes century,
year, month, day, hour, minute, and second.
The valid range is from January 1, 4712 BC to December 31, 9999 AD.

i. TIMESTAMP
This is length zero if the column is empty, or up to 11 bytes, depending on the precision
specified. Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6
places by default.

j. TIMESTAMP WITH TIMEZONE
Like TIMESTAMP, but the data is stored with a record kept of the time zone to which it refers.
The length may be up to 13 bytes, depending on precision. This data type lets Oracle
determine the difference between two times by normalizing them to UTC, even if the times
are for different time zones.

k. TIMESTAMP WITH LOCAL TIMEZONE
Like TIMESTAMP, but the data is normalized to the database time zone on saving. When
retrieved, it is normalized to the time zone of the user process selecting it.

l. INTERVAL YEAR TO MONTH
Used for recording a period in years and months between two DATEs or TIMESTAMPs.

m. INTERVAL DAY TO SECOND
Used for recording a period in days and seconds between two DATEs or TIMESTAMPs.

n. CLOB
Character data stored in the database character set, size effectively unlimited: 4GB multiplied
by the database block size.

o. NCLOB
Like CLOB, but the data is stored in the alternative national language character set, one of
the permitted Unicode character sets.
p. BLOB
Like CLOB, but binary data that will not undergo character set conversion by Oracle Net.

q. BFILE
A locator pointing to a file stored on the operating system of the database server. The size of
the files is limited to 4GB.

r. LONG
Character data in the database character set, up to 2GB. All the functionality of LONG (and
more) is provided by CLOB; LONGs should not be used in a modern database, and if your
database has any columns of this type they should be converted to CLOB. There can only be
one LONG column in a table.

s. LONG RAW
Like LONG, but binary data that will not be converted by Oracle Net. Any LONG RAW
columns should be converted to BLOBs.

t. ROWID
A value coded in base 64 that is the pointer to the location of a row in a table. Encrypted.
Within it is the exact physical address. ROWID is an Oracle proprietary data type, not visible
unless specifically selected.









3. CREANDO UNA TABLA SIMPLE



















a. Creacin de tablas con columnas especificadas

CREATE TABLE [schema.]table [ORGANIZATION HEAP]
(column datatype [DEFAULT expression]
[,column datatype [DEFAULT expression]);



CREATE TABLE SCOTT.EMP
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
HIREDATE DATE DEFAULT TRUNC(SYSDATE),
SAL NUMBER(7,2),
COMM NUMBER(7,2) DEFAULT 0.03);







b. Creacin de tablas con subconsultas



CREATE TABLE [schema.]table AS subquery;

create table employees_copy as select * from employees;

create table emp_dept as
select last_name ename,department_name dname,round(sysdate hire_date) service
from employees natural join departments order by dname,ename;


c. Alterar las tablas despus de creadas















Adding columns:


Modifying columns:



Dropping columns:













Marking columns as unused:



Renaming columns:


alter table emp rename column hiredate to recruited;



Marking the table as read-only:

alter table emp read only;

ALTER TABLE tablename DROP UNUSED COLUMNS;

d. Quitar y Truncar tablas

DROP TABLE [schema.]tablename ;







4. EXPLICAR CONSTRAINTS EN EL TIEMPO DE CREACIN DE LA TABLA
































































































1 create table dept(
2 deptno number(2,0) constraint dept_deptno_pk primary key
3 constraint dept_deptno_ck check (deptno between 10 and 90),
4 dname varchar2(20) constraint dept_dname_nn not null);
5 create table emp(
6 empno number(4,0) constraint emp_empno_pk primary key,
7 ename varchar2(20) constraint emp_ename_nn not null,
8 mgr number (4,0) constraint emp_mgr_fk references emp (empno),
9 dob date,
10 hiredate date,
11 deptno number(2,0) constraint emp_deptno_fk references dept(deptno)
12 on delete set null,
13 email varchar2(30) constraint emp_email_uk unique,
14 constraint emp_hiredate_ck check (hiredate >= dob + 365*16),
15 constraint emp_email_ck
16 check ((instr(email,'@') > 0) and (instr(email,'.') > 0)));























Ejercicios UNIDAD 11

1. Prctica Primer parte
Crear la tabla DEPT con las siguientes caractersticas:
id NUMBER(7),
name VARCHAR2(25)

CREATE TABLE dept
(id NUMBER(7),
name VARCHAR2(25));

DESCRIBE dept

Llenar la tabla DEPT a partir de la tabla DEPARTMENTS con las columnas
necesarias

INSERT INTO dept
SELECT department_id, department_name
FROM departments;
COMMIT;
Crear la tabla EMP con las siguientes caractersticas:

id NUMBER(7)
last_name VARCHAR2(25)
first_name VARCHAR2(25)
dept_id NUMBER(7)

CREATE TABLE emp
(id NUMBER(7),
last_name VARCHAR2(25),
first_name VARCHAR2(25),
dept_id NUMBER(7));

DESCRIBE emp


Llenar tabla EMP a partir de la tabla EMPLOYEES:

INSERT INTO EMP
SELECT employee_id, last_name, first_name, department_id
FROM employees;

COMMIT;

Confirmar en la tabla USER_TABLES del diccionario de datos existen las 2 tablas
creadas recientemente: DEPT y EMP

SELECT table_name
FROM user_tables
WHERE table_name IN ('DEPT', 'EMP');

2. Prctica Segunda parte
Modificar la tabla EMP para que permita apellidos de hasta 50 caracteres, confirmar el
cambio verificando la estructura de la tabla

ALTER TABLE emp
MODIFY (last_name VARCHAR2(50));

DESCRIBE emp

Crear la tabla emp2 basada en la estructura de EMPLOYEES, incluir las columnas
EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY y DEPARTMENT_ID. Los
nombres de las columnas deben ser ID, FIRST_NAME, LAST_NAME, SALARY , y
DEPT_ID, respectivamente.

CREATE TABLE emp2 AS
SELECT employee_id id, first_name, last_name, salary, department_id dept_id
FROM employees;




Agregar la columna job_id del tipo VARCHAR2 longitud 9 en la tabla EMP2, confirmar
cambio con el comando DESCRIBE, notar que la columna queda ltima en la
definicin de la tabla

ALTER TABLE emp2
ADD (job_id VARCHAR2(9));

DESCRIBE emp2
Borrar la columna first_name de la tabla EMP2, confirmar el cambio verificando la
estructura de la tabla

ALTER TABLE emp2
DROP COLUMN FIRST_NAME;
DESCRIBE emp2
Borrar la tabla EMP2

DROP TABLE emp2;

3. Prctica Tercera parte
Crear primary key llamada my_emp_id_pk en la tabla EMP sobre la columa ID.

ALTER TABLE emp ADD CONSTRAINT my_emp_id_pk PRIMARY KEY (id);
Crear primary key llamada my_dept_id_pk en la tabla DEPT sobre la columa ID.

ALTER TABLE dept ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);
Crear una FK en la tabla EMP que asegure que los empleados no pueden asignarse a
departamentos no existentes, la FK debe llamarse my_emp_dept_id_fk.

ALTER TABLE emp
ADD CONSTRAINT my_emp_dept_id_fk FOREIGN KEY (dept_id)
REFERENCES dept(id);



UNIDAD 12
CREACIN DE OTROS OBJETOS DE ESQUEMA


1. CREANDO VISTAS COMPLEJAS Y SIMPLES

a. Porque usar una vista

Security.
Simplifying user SQL.
Preventing error.
Making data comprehensible. Table and column names are often long and pretty
meaningless. The view and its columns can be much more obvious.
Performance.

Vistas para reforzar la seguridad

create view hr.emp_fin as select
hire_date,job_id,salary,commission_pct,department_id from hr.employees;

select * from emp_fin where department_id=50;

select department_name, sum(salary) from departments natural join emp_fin
group by department_name;

Vistas para simplificar usuarios SQL

create view dept_sal as
select d.department_name, sum(e.salary) from
departments d left outer join employees e on d.department_
id=e.department_id
group by department_name order by department_name;

select * from dept_sal;

Vistas para prevenir errores

create table emp(empno number constraint emp_empno_pk primary key,
ename varchar2(10),deptno number,active varchar2(1) default 'Y');

create view current_staff as select * from emp where active='Y';


Vistas para mejorar el rendimiento

create view dept_emp as
select /*+USE_HASH (employees departments)*/ department_name, last_name
from departments natural join employees;

b. Vistas simples y complejas



c. Crear Vistas, Alterar Vistas, y Quitar Vistas

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW
[schema.]viewname [(alias [,alias])]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraintname]]
[WITH READ ONLY [CONSTRAINT constraintname]] ;

OR REPLACE If the view already exists, it will be dropped before being created.
FORCE or NOFORCE The FORCE keyword will create the view even if the detail table(s)
in the subquery does not exist. NOFORCE is the default and will cause an error if the detail
table does not exist.
WITH CHECK OPTION This is to do with DML. If the subquery includes a WHERE clause,
then this option will prevent insertion of rows that wouldnt be seen in the view or updates that
would cause a row to disappear from the view. By default, this option is not enabled, which
can give disconcerting results.
WITH READ ONLY Prevents any DML through the view.
CONSTRAINT constraintname This can be used to name the WITH CHECK OPTION and
WITH READ ONLY restrictions so that error messages when the restrictions cause
statements to fail, will be more comprehensible.

DROP VIEW [schema.]viewname ;

2. RECUPERAR DATOS DESDE VISTAS

create view dept_emp as
select department_name, last_name from
departments join employees using (department_id);

select * from dept_emp where department_name='Marketing';

select * from
(select department_name, last_name
from departments join employees using (department_id))
where department_name='Marketing';


3. CREAR SINNIMOS PRIVADOS Y PBLICOS

select * from hr.employees@prod;

create public synonym emp for hr.employees@prod;

select * from emp;

CREATE [PUBLIC] SYNONYM synonym FOR object ;

DROP [PUBLIC] SYNONYM synonym ;
ALTER SYNONYM synonym COMPILE;

4. CREAR MANTENER Y USAR SECUENCIAS

a. Creacin de secuencias

CREATE SEQUENCE [schema.]sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE number | NOCACHE]
[ORDER | NOORDER];

CREATE sequence seq1;



b. Usando secuencias

create sequence order_seq start with 10;
create sequence line_seq start with 10;

insert into orders (order_number,order_date,customer_number)
values (order_seq.nextval,sysdate,'1000');
insert into order_lines (order_number,line_number,item_number,quantity)
values (order_seq.currval,line_seq.nextval,'A111',1);
insert into order_lines (order_number,line_number,item_number,quantity)
values (order_seq.currval,line_seq.nextval,'B111',1);
commit;

ALTER SEQUENCE sequencename
[INCREMENT BY number]
[START WITH number]
[MAXVALUE number | NOMAXVALUE]
[MINVALUE number | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE number | NOCACHE]
[ORDER | NOORDER] ;

alter sequence order_seq cache 1000;

drop sequence order_seq;


5. CREAR Y MANTENER INDICES

a. Creando y usando ndices

CREATE [UNIQUE | BITMAP] INDEX [ schema.]indexname
ON [schema.]tablename (column [, column] ) ;

create table dept(deptno number,dname varchar2(10));
create table emp(empno number, surname varchar2(10), forename varchar2(10), dob
date, deptno number);
create unique index dept_i1 on dept(deptno);
create unique index emp_i1 on emp(empno);
create index emp_i2 on emp(surname,forename);
create bitmap index emp_i3 on emp(deptno);
alter table dept add constraint dept_pk primary key (deptno);
alter table emp add constraint emp_pk primary key (empno);
alter table emp add constraint emp_fk foreign key (deptno) references
dept(deptno);


b. Modificando y quitando ndices

drop index emp_i2;
create index emp_i2 on emp(surname,forename,dob);
























Ejercicios UNIDAD 12

1. Prctica Primer parte
Crear la vista EMPLOYEES_VU basada en las columnas employee_id, last_name, y
department_id de la tabla employees. La vista debe tener los nombres de columna
nro_empleado, apellido y nro_departamento respectivamente(usar alias)

CREATE OR REPLACE VIEW employees_vu AS
SELECT employee_id nro_empleado,
last_name apellido,
department_id nro_departamento
FROM employees;

Mostrar la vista EMPLOYEES_VU con todas sus columnas, luego volver a mostrar la
vista pero solo las columnas de nro de empleado y apellido:

SELECT * FROM employees_vu;
SELECT nro_empleado, apellido FROM employees_vu;
Consultar las vistas que tiene el esquema, usar la vista del diccionario de datos
USER_VIEWS para realizar este chequeo. Notar que el esquema ya tiene una vista
creada con anterioridad:

SELECT view_name, text FROM user_views;
Crear una vista llamada DEPT50 que contenga las columnas employee_id, last_name,
y department_id de todos los empleados del departamento 50. Los nombres de
columna deben ser EMPNO, EMPLOYEE, DEPTNO respectivamente. No permitir que
un empleado sea reasignado a otro departamento cuando se realizan DMLs sobre la
vista.

CREATE VIEW dept50 AS
SELECT employee_id empno, last_name employee,
department_id deptno
FROM employees
WHERE department_id = 50
WITH CHECK OPTION CONSTRAINT emp_dept_50;



Ver la estructura de la vista creada en el punto anterior y su contenido

DESCRIBE dept50
SELECT * FROM dept50;
Intentar actualizar a travs de la vista dept50 el empleado Matos para asignarlo al
departamento 80, que ocurre?

UPDATE dept50
SET deptno = 80
WHERE employee = 'Matos';

3. Prctica Segunda parte

Crear un sinnimo llamado vista_empleados para la vista employees_vu .
CREATE SYNONYM vista_empleados FOR employees_vu;
Crear un sinnimo llamado vista_depto50 para la vista dept50.

CREATE SYNONYM vista_depto50 FOR dept50;
Ver la estructura de ambas vistas usando los sinnimos.

DESCRIBE vista_empleados
DESCRIBE vista_depto50
Consultar ambas vistas pero usando los sinnimos:

SELECT * FROM vista_empleados;
SELECT * FROM vista_depto50;
3. Prctica Tercer parte
Crear una secuencia que se utilizar como primary key de la tabla DEPT. La
secuencia se debe llamar DEPT_ID_SEQ, debe arrancar en 200 y un mximo de
1000, el incremento debe ser de a 10



CREATE SEQUENCE dept_id_seq
START WITH 300
INCREMENT BY 10
MAXVALUE 1000;

Consultar las secuencias que se encuentran creadas en el esquema, usar la vista del
diccionario de datos USER_SEQUENCES.

SELECT sequence_name, max_value, increment_by, last_number
FROM user_sequences;

Insertar filas en la tabla DEPT utilizando la secuencia para generar la primary
key(columna dept_id), los departamentos a crear son Education y Administration.
Hacer previamente un DESCRIBE de la tabla para ver su estructura.

DESC DEPT
INSERT INTO dept
VALUES (dept_id_seq.nextval, 'Education');

INSERT INTO dept
VALUES (dept_id_seq.nextval, 'Administration');

COMMIT;
4. Prctica Tercer parte
Crear un ndice no nico sobre la columna DEPT_ID de la tabla EMP

CREATE INDEX emp_dept_id_idx ON emp (dept_id);
Consultar los ndices creados en la tabla employees, para ello consultar la vista del
diccionario de datos user_indexes

SELECT index_name FROM user_indexes WHERE table_name='EMPLOYEES';