Producto 2 - Consultas Base de Datos Oracle
-
Upload
carlos-huertas-perez -
Category
Documents
-
view
23 -
download
0
description
Transcript of 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