sesion06a - manipulacion de datos (oracle)
DESCRIPTION
Este minitutorial tiene como objetivo captar todos los conceptos dictados en cada sesión en el curso de Base de Datos Avanzado II, así como brindar apoyo a los alumnos de la carrera técnica de Computación e Informática, que por algún motivo no hayan asistido a clases. UNIDAD 3. Lenguaje de Manipulación de Datos Logro de la Unidad de Aprendizaje Al término de la unidad, el alumno manipula la información mediante el uso de la herramienta SQL*PLUS, sentencias SQL y funciones predefinidas en la base de datos Oracle. Temario 3.1 Tema 5: LENGUAJE DE MANIPULACIÓN DE DATOS 3.1.1 Lenguaje SQL 3.1.2 Instrucciones DML y operadores 3.1.3 Consultas multitabla 3.1.3 Funciones PredefinidasTRANSCRIPT
/* Sesión05 – Manipulación de DatosEstudiante: José Luis Toro AlcarrazCurso: Base de Datos Avanzado IICorreo:[email protected]*/
6) Practicando lo aprendido
d) Demostración de las Funciones de fechas
La función SYSDATE devuelve la fecha y hora actuales.
SQL> CONNECT scott/tigerConectado.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE--------19/03/12
Ejemplo1: Suma 5 días a la fecha actual 19/03/2012.
SQL> SELECT SYSDATE + 5 FROM DUAL;
SYSDATE+--------24/03/12
Ejemplo2: Suma 500 horas a la fecha actual 19/03/2012. Donde n = 500 = número de horas.
SQL> SELECT SYSDATE + 500/24 FROM DUAL;
SYSDATE+--------09/04/12
La función ADD_MONTHS recibe 2 parámetros y devuelve la fecha d incrementada en n meses. ADD_MONTHS(d,n).
SQL> SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;
ADD_MONT--------19/05/12
Ejemplo: Obtener la fecha actual de hoy, pero del siguiente año.
SQL> SELECT ADD_MONTHS(SYSDATE,12) FROM DUAL;
ADD_MONT--------19/03/13
La función MONTHS_BETWEEN devuelve el número de meses entre 2 fechas
SQL> SELECT MONTHS_BETWEEN(SYSDATE + 100,SYSDATE) FROM DUAL;
MONTHS_BETWEEN(SYSDATE+100,SYSDATE)----------------------------------- 3,25806452
La función NEXT_DAY recibe 2 parámetros devolviendo la fecha del primer día de la semana cad después de la fecha d. NEXT_DAY(cad,d).
Ejemplo: Hoy estamos lunes 19/03/2012 y quiero que me devuelva el lunes 26/03/2012.
SQL> SELECT NEXT_DAY(SYSDATE,1) FROM DUAL;
NEXT_DAY--------26/03/12
La función LAST_DAY recibe un parámetro y devuelve la fecha del último día del mes de d. LAST_DAY(d).Ejemplo1: Obtener el ultimo día del mes de febrero.
SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
LAST_DAY--------31/03/12
Ejemplo2: Obtener el lunes del siguiente mes, sabiendo que hoy es lunes 19/03/2012
SQL> SELECT NEXT_DAY(NEXT_DAY(SYSDATE,1),1) FROM DUAL;
NEXT_DAY--------02/04/12
SQL> SELECT NEXT_DAY(LAST_DAY(SYSDATE),1) FROM DUAL;
NEXT_DAY
--------02/04/12
La función ROUND y TRUNC también en funciones de fechas. Reciben 2 parámetros. ROUND (fecha,dimensionFecha).
SQL> SELECT ROUND(SYSDATE,'YY'), ROUND(SYSDATE,'MM'), ROUND(SYSDATE,'DD') FROM DUAL;
ROUND(SY ROUND(SY ROUND(SY-------- -------- --------01/01/12 01/04/12 20/03/12
Ejemplo1: Devuelve el primer lunes del año 2012.
SQL> SELECT NEXT_DAY(ROUND(SYSDATE,'YY'),1) FROM DUAL;
NEXT_DAY--------02/01/12
SQL> SELECT NEXT_DAY(TRUNC(SYSDATE,'YY')-1,1) FROM DUAL;
NEXT_DAY--------02/01/12
Ejemplo2: Fecha del primer sábado del siguiente año.
SQL> SELECT NEXT_DAY(ADD_MONTHS(ROUND(SYSDATE,'YY'),12),6) FROM DUAL;
NEXT_DAY--------05/01/13
SQL> SELECT NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'YY'),12)-1,6) FROM DUAL;
NEXT_DAY--------05/01/13
e) Funciones de conversión
Cadena Formato:
YYYY = AñoMM = MesDD = DíaDAY = Día
MONTH = MesHH24 = HoraMI = MinutosSS = Segundos
La función TO_DATE convierte la cadena cad de tipo varchar2 a fecha, opcionalmente de acuerdo con el formato fmto. TO_DATE(cad,fmto).
SQL> SELECT TO_DATE('19/03/2012','DD/MM/YYYY') FROM DUAL;
TO_DATE(--------19/03/12
La función TO_CHAR Convierte la fecha d a una cadena de caracteres, opcionalmente de acuerdo con el formato fmto. TO_CHAR(d,fmto).
SQL> SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'DD-------------------19-03-2012 12:24:52
SQL> SELECT TO_CHAR(SYSDATE,'DAY MONTH YYYY HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'DAYMONTHYYYYHH24:----------------------------------LUNES MARZO 2012 12:33:17
La función TO_NUMBER convierte la cadena cad a un número, opcionalmente de acuerdo con el formato fmto. TO_NUMBER(cad,fmto).
SQL> SELECT TO_NUMBER('458.85','99999.99') FROM DUAL;
TO_NUMBER('458.85','99999.99')------------------------------ 458,85
La función NVL devuelve la expresión exp si val es nulo y val en caso no sea nulo. NVL(val,exp).
SQL> SELECT EMPNO,ENAME,NVL(COMM,0) FROM SCOTT.EMP;
EMPNO ENAME NVL(COMM,0)---------- ---------- ----------- 7499 ALLEN |300 7521 WARD 500 7654 MARTIN 1400 7698 BLAKE 0 7782 CLARK 0
7839 KING 0 7844 TURNER 0 7499 ALLEN 300 7521 WARD 500 7654 MARTIN 1400 7698 BLAKE 0 7782 CLARK 0 7839 KING 0 7844 TURNER 0
14 filas seleccionadas.
La función DECODE Convierte el valor de var, de acuerdo con la codificación. DECODE(var, val1,cod1, val2, cod2, ...,defecto).
SQL> SELECT DECODE(ENAME,'PRESIDENTE', 'P','DIRECTOR', 'D', 'X')FROM EMP;
El resultado sería P,D,X,…
d) Demostración de las Sub-consulas
-- Subconsulta de una fila, >,<,>=,<=,<>,=
SELECT ENAME, JOB, DEPTNO, HIREDATEFROM EMPWHERE SAL > (SELECT SAL FROM EMP WHERE EMPNO = 7566);
-- Subconsulta de múltiples filas, IN, ANY, ALL
SELECT ENAME,JOB,HIREDATE,DEPTNOFROM EMPWHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);
SELECT * FROM EMPWHERE EMPNO IN (SELECT EMPNO FROM ORD);
-- Subconsulta de múltiples columnas
SELECT * FROM EMP WHERE (EMPNO, DEPTNO) IN (SELECT EMPNO, DEPTNO FROM EMP WHERE JOB='MANAGER');
d) Demostración de las cconsultas multi-tablas
SQL> SELECT DISTINCT E.*
FROM EMP E JOIN ORD OON E.EMPNO=O.EMPNO;
SQL> SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
EMPNO ENAME DEPTNO DNAME---------- ---------- ---------- -------------- 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7839 KING 10 ACCOUNTING 7844 TURNER 30 SALES 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 10 ACCOUNTING 7839 KING 10 ACCOUNTING 7844 TURNER 30 SALES
14 filas seleccionadas.
SQL> SELECT D.DEPTNO, D.DNAME, COUNT(E.EMPNO) FROM DEPT D LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO GROUP BY D.DEPTNO, D.DNAME;
DEPTNO DNAME COUNT(E.EMPNO)---------- -------------- -------------- 10 ACCOUNTING 4 40 OPERATIONS 0 20 RESEARCH 0 30 SALES 10 70 RRHH 0 50 SISTEMA 0
6 filas seleccionadas.
SQL> SELECT E.EMPNO,E.ENAME,E.SAL FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
EMPNO ENAME SAL---------- ---------- ----------
7654 MARTIN 1250 7521 WARD 1250 7654 MARTIN 1250 7521 WARD 1250 7844 TURNER 1500 7844 TURNER 1500 7499 ALLEN 1600 7499 ALLEN 1600 7782 CLARK 2450 7782 CLARK 2450 7698 BLAKE 2850 7698 BLAKE 2850 7839 KING 5000 7839 KING 5000
14 filas seleccionadas.