producto 2 - consultas base de datos oracle

PRODUCTO N° 2 – HUERTAS PÉREZ, CARLOS HUGO

1. Desbloquear esquema HR.

2. Conectarse a HR y consultar catálogo

3. Describir estructura de una tabla

4. Creación de un esquema de usuario de bd

Creación de Tablas

Restricciones

1. AGREGAR UNA FILA DE DATOS A UNA TABLA

INSERT INTO HR.DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME,MANAGER_ID,

LOCATION_ID) VALUES(280,'PUBLIC RELATIONS1',100,1700); SELECT * FROM DEPARTMENTS;

2. INSERTAR VALORES NULOS

INSERT INTO HR.DEPARTMENTS VALUES(290,'PUBLIC RELATIONS2',(NULL),(NULL)); SELECT * FROM DEPARTMENTS;

- Método Implícito

INSERT INTO DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME) VAL-

UES(300,'RR.HH'); SELECT * FROM DEPARTMENTS;

3. INSERTAR VALORES ESPECIALES

INSERT INTO EMPLOYEES INSERT INTO HR.EMPLOYEES

VALUES(207,'DEN','RAPLEALY','DRAPHEAL','515.127.4461',TO_DATE(HIRE_DATE,'01-FEB-

2000'),'MK_MAN',11000,(NULL),100,30);

SELECT * FROM EMPLOYEES;

INSERT INTO EMPLOYEES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUM-

BER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)

VALUES(113,'LUIS','POPP','LPOPP','512.124.4565',SYSDATE,'AC_AC-

COUNT',6900,(NULL),205,100); SELECT * FROM EMPLOYEES;

4. INSERTAR VALORES MEDIANTE VARIABLES DE SUSTITUCIÓN

INSERT INTO DEPARTMENTS(DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID)

VALUES (&DEPARTMENT_ID,'&DEPARTMENT_NAME',&LOCATION_ID);

5. MODIFICAR LAS FILAS EN UNA TABLA

UPDATE EMPLOYEES SET DEPARTMENT_ID=70 WHERE EMPLOYEE_ID=113; SELECT * FROM EMPLOYEES;

6. ELIMINAR FILAS DE UNA TABLA

DELETE FROM DEPARTMENTS WHERE DEPARTMENT_NAME='PUBLIC RELATIONS1'; SELECT * FROM DEPARTMENTS;

7. VALOR POR DEFECTO

7.1 Default en una Inserción

INSERT INTO DEPARTMENTS(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID)

VALUES(301,'ENGINEERING',DEFAULT); SELECT * FROM DEPARTMENTS;

7.2 Default en una Actualización

UPDATE DEPARTMENTS SET MANAGER_ID=DEFAULT WHERE DEPARTMENT_ID=10; SELECT * FROM DEPARTMENTS;

SELECCIONA TODAS LAS FILAS Y COLUMNAS DE UNA TABLA USANDO

SELECT * FROM JOBS;

SELECCIONAR COLUMNAS ESPECÍFICAS LISTANDO LAS COLUMNAS

EN EL ORDEN DESEADO

SELECT JOB_TITLE,MIN_SALARY FROM JOBS;

USO DE ALIAS PARA RENOMBRAR ENCABEZADOS DE COLUMNAS

MEDIANTE AS

SELECT JOB_TITLE AS TITULOS,MIN_SALARY AS "SALARIO MINIMO" FROM JOBS;

USO DE VALORES ÚNICOS PARA ELIMINAR FILAS DUPLICADAS

MEDIANTE DISTINCT

SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;

OPERADORES DE CONCATENACIÓN USANDO ||

SELECT LAST_NAME || JOB_ID AS EMPLOYEES FROM EMPLOYEES;

EXPRESIÓN LITERAL CONCATENANDO STRING DE CARACTERES

SELECT LAST_NAME ||' is a '|| JOB_ID AS "EMPLOYEES DETAILS" FROM EMPLOYEES;

USO DE EXPRESIONES ARITMÉTICAS DE COLUMNAS DE TIPO NUMBER

O DATE

SELECT LAST_NAME,SALARY, SALARY+300 FROM EMPLOYEES;

RESTRINGIR LAS FILAS SELECCIONADAS USANDO WHERE PARA

MOSTRAR SOLO LOS QUE CUMPLE CON LA CONDICIÓN

SELECT FIRST_NAME||LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE

DEPARTMENT_ID='90';

USO DE LA CONDICIÓN BETWEEN PARA MOSTRAR LAS FILAS

BASADAS EN UN RANGO DE VALORES.

SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE SALARY BETWEEN 2500 AND

3500;

USO DE LA CONDICIÓN IN PARA EVALUAR LOS VALORES EN UNA

LISTA

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,MANAGER_ID FROM EMPLOYEES WHERE

MANAGER_ID IN (100,101,201);

USO DE LA CONDICIÓN LIKE PARA BUSCAR CADENAS LITERALES

CONTENIDAS EN LOS DATOS

SELECT FIRST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE 'S%';

USO DE LA CONDICIÓN IS NULL PARA EVALUAR CAMPOS VACÍOS

SELECT FIRST_NAME, MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NULL;

DEFINIR MÚLTIPLES CONDICIONES USANDO AND

SELECT EMPLOYEE_ID,FIRST_NAME, JOB_ID,SALARY FROM EMPLOYEES WHERE

SALARY >=10000 AND JOB_ID LIKE '%MAN%';

DEFINIR MÚLTIPLES CONDICIONES USANDO OR

SELECT EMPLOYEE_ID,FIRST_NAME, JOB_ID,SALARY FROM EMPLOYEES WHERE

SALARY >=10000 OR JOB_ID LIKE '%MAN%';

USO DEL OPERADOR NOT PARA INVERTIR OTROS OPERADORES SQL

SELECT FIRST_NAME, JOB_ID FROM EMPLOYEES WHERE JOB_ID NOT IN

('IT_PROG','ST_CLERK','SA_REP');

USO DEL OPERADOR ANY

SELECT FIRST_NAME ||' '|| LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE

DEPARTMENT_ID <= ANY (10,15,20,25);

USO DEL OPERADOR ALL

SELECT FIRST_NAME ||' '|| LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE

DEPARTMENT_ID >= ALL (80,90,100);

ORDENAR FILAS

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID=90

ORDER BY FIRST_NAME;

ESPECIFICANDO EL TIPO DE ORDEN EN MÚLTIPLES COLUMNAS

SELECT FIRST_NAME, HIRE_DATE, SALARY,MANAGER_ID MID FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (110,100) ORDER BY MID ASC, SALARY DESC, HIRE_DATE;

FUNCIONES DE MANIPULACION DE CARACTERES

SELECT EMPLOYEE_ID, CONCAT(FIRST_NAME,LAST_NAME) JOB_ID,LENGTH(LAST_NAME), INSTR(LAST_NAME,'a') "CONTAINS 'a' 7" FROM EMPLOYEES WHERE SUBSTR(JOB_ID,4)='REP';

FUNCIÓN DE CONVERSIÓN DE CARACTERES

SELECT EMPLOYEE_ID, LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE LOWER(LAST_NAME)='higgins';

FUNCIONES NUMÉRICAS

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

SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)FROM DUAL;

FORMATO DE FECHAS

SELECT LAST_NAME,HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE <'01-FEB-02';

SYSDATE es una función de fecha que no toma ningún input y retorna la fecha

actual del sistema

SELECT LAST_NAME,(SYSDATE-HIRE_DATE)/7 AS WEEKS FROM EMPLOYEES WHERE

DEPARTMENT_ID=90;

OPERACIONES CON FECHAS

SELECT SYSDATE AS HOY, EXTRACT(YEAR FROM SYSDATE) AS AÑO FROM DUAL;

SELECT SYSDATE AS HOY, EXTRACT(MONTH FROM SYSDATE) AS MES FROM DUAL;

SELECT SYSDATE AS HOY, EXTRACT(DAY FROM SYSDATE) AS DIA FROM DUAL;

CAMBIOS DE TIPOS DE DATOS

SELECT LAST_NAME, TO_CHAR(HIRE_DATE,'FMDD MONTH YYYY') AS HIREDATE FROM EMPLOYEES;

SELECT TO_CHAR(SALARY,'&99,999.00') SALARY FROM EMPLOYEES WHERE

LAST_NAME='ERNST';

FUNCIÓN NVL

SELECT LAST_NAME,SALARY,NVL(COMMISSION_PCT,0), (SALARY*12) +

(SALARY*12*NVL(COMMISSION_PCT,0)) AN_SAL FROM EMPLOYEES;

FUNCION NVL2

SELECT LAST_NAME,SALARY,

COMMISSION_PCT,NVL2(COMMISSION_PCT,'SAL+COMM','SAL') INCOME FROM

EMPLOYEES WHERE DEPARTMENT_ID IN(50,60);

FUNCION NULLIF

SELECT FIRST_NAME, LENGTH(FIRST_NAME) "EXPR1",

LAST_NAME,LENGTH(LAST_NAME) "EXPR2",

NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME)) RESULT FROM EMPLOYEES;

FUNCION COALESCE

SELECT LAST_NAME, COALESCE(MANAGER_ID,COMMISSION_PCT, -1) COMM FROM

EMPLOYEES ORDER BY COMMISSION_PCT;

FUNCION DECODE

SELECT LAST_NAME,JOB_ID,SALARY, DECODE(JOB_ID,'IT_PROG',1.10*SALARY,

'ST_CLERK',1.15*SALARY, 'ST_CLERK',1.20*SALARY,SALARY) REVISED_SALARY FROM

EMPLOYEES;

FUNCION DE GRUPO

SELECT AVG(SALARY), MAX(SALARY), MIN(SALARY), SUM(SALARY) FROM EMPLOYEES WHERE JOB_ID LIKE '%REP%';

COUNT(*) CUENTA LA CANTIDAD DE FILAS DE UNA TABLA

SELECT COUNT(*) FROM EMPLOYEES WHERE DEPARTMENT_ID=50;

COUNT (EXPR) CUENTA LA CANTIDAD DE FILAS CON VALORES NO

NULOS QUE CUMPLAN CON LA EXPRESIÓN

SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES WHERE DEPARTMENT_ID=80;

COUNT (DISTINCT EXPR) CUENTA EL NÚMERO DE DISTINTOS

VALORES NO NULOS DE LA EXPRESIÓN

SELECT COUNT(DISTINCT DEPARTMENT_ID) FROM EMPLOYEES;

MANEJO DE VALORES NULOS EN LAS FUNCIONES DE GRUPO

Las funciones de grupo excluyen a los valores nulos

SELECT AVG(COMMISSION_PCT) FROM EMPLOYEES;

LA FUNCIÓN NVL FUERZA A LAS FUNCIONES DE GRUPO A INCLUIR

LOS VALORES NULOS

SELECT AVG(NVL(COMMISSION_PCT,0)) FROM EMPLOYEES;

AGRUPACIÓN DE DATOS

SELECT DEPARTMENT_ID,AVG(SALARY) FROM EMPLOYEES GROUP BY

DEPARTMENT_ID;

RESTRINGIR LOS RESULTADOS DE LOS GRUPO

SELECT DEPARTMENT_ID, MAX(SALARY) FROM EMPLOYEES GROUP BY

DEPARTMENT_ID HAVING MAX(SALARY)>10000;

OPERADOR DE UNIÓN

SELECT EMPLOYEE_ID,JOB_ID FROM EMPLOYEES UNION SELECT EMPLOYEE_ID,JOB_ID

FROM JOB_HISTORY;

OPERADOR UNION ALL

SELECT EMPLOYEE_ID,JOB_ID,DEPARTMENT_ID FROM EMPLOYEES UNION ALL SELECT

EMPLOYEE_ID,JOB_ID,DEPARTMENT_ID FROM JOB_HISTORY ORDER BY EMPLOYEE_ID;

OPERADOR INTERSECT

SELECT EMPLOYEE_ID,JOB_ID FROM EMPLOYEES INTERSECT SELECT EMPLOYEE_ID,

JOB_ID FROM JOB_HISTORY;

OPERADOR MINUS

SELECT EMPLOYEE_ID,JOB_ID FROM EMPLOYEES MINUS SELECT EMPLOYEE_ID,JOB_ID

FROM JOB_HISTORY;

TOP-N

SELECT ROWNUM AS RANK,LAST_NAME,SALARY FROM (SELECT LAST_NAME, SALARY

FROM EMPLOYEES ORDER BY SALARY DESC) WHERE ROWNUM<=3;

ROLLUP SELECT DEPARTMENT_ID,JOB_ID,SUM(SALARY) FROM EMPLOYEES WHERE DEPART-

MENT_ID<60 GROUP BY ROLLUP (DEPARTMENT_ID,JOB_ID);

OBTENCIÓN DE DATOS DESDE MÚLTIPLES TABLAS

USO DE ALIAS

MÉTODOS DE UNIÓN ENTRE TABLAS

- UNIÓN DE MÁS DE DOS TABLAS

NATURAL JOINS SELECT DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID,CITY FROM DEPART-

MENTS NATURAL JOIN LOCATIONS;

NO-EQUIJOINS SELECT E.LAST_NAME,E.SALARY, J.GRADE_LEVEL FROM EMPLOYEES E JOIN

JOB_GRADES J ON SALARY BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL;

TABLA JOB_GRADES NO EXISTE, POR ESO GENERA ERROR LA CON-

SULTA

OUTER JOIN (+) SELECT E.LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME FROM EMPLOYEES E,

DEPARTMENTS D WHERE E.DEPARTMENT ID(+) =D.DEPARTMENT ID;

SELF JOIN O AUTO JOIN

CROSS JOINS

SINTAXIS DEL USO DE SUBCONSULTAS

SUBCONSULTAS DE FILA SIMPLE

USO DEL OPERADOR ANY

USO DEL OPERADOR ALL

USO DEL OPERADOR IN CON VALORES NULOS

FUNCIONES DE GRUPO EN UNA SUBCONSULTA

SUB CONSULTAS ANINADAS A N NIVELES

- CREACIÓN DE TABLAS USANDO SUBCONSULTAS

- INSERCIÓN DE DATOS USANDO SUBCONSULTAS

- ACTUALIZACIÓN DE DATOS USANDO SUBCONSULTAS

- ELIMINACIÓN DE DATOS USANDO SUBCONSULTAS

SUB CONSULTAS CORRELACIONADAS

EL OPERADOR EXIST