sesion05 - manipulacion de datos (oracle)

/* Sesión05 – Manipulación de DatosEstudiante: José Luis Toro AlcarrazCurso: Base de Datos Avanzado IICorreo:[email protected]*/

1) Introducción a SQL2) Instrucciones DML y operadores3) Funciones pre-definidas4) Sub-consultas5) Consultas multi-tabla6) Practicando lo aprendido

1) Introducción a SQL

Oracle 11G SQL utiliza el SQL (Structured Query Language). Basado en estándares ANSI (American National Standards Institute) e ISO (International Standards Organization).

Categorías:

DCL - Data Control LanguageUtilizadas en el control de acceso a datos en la base de datos. Ejemplo: GRANT, REVOKE.

TCL - Transaction Control LanguageUtilizadas para confirmar o restaurar transacciones que son un conjunto de sentencias SQL que se completan o fallan como unidad. Ejemplo: COMMIT (Confirma las transacciones), ROLLBACK (Desase las transacciones).

DDL - Data Definition LanguageUtilizadas para crear, alterar o borrar objetos de la base de datos. Ejemplo: CREATE, ALTER, DROP, RENAME.

DML - Data Manipulation LanguageUtilizadas para consultar o actualizar los datos contenidos en tablas de una base de datos.

2) Instrucciones DML y operadores

Bueno en esta sesión nos basaremos en revisar las particularidades de las sentencias DML en Oracle. Explotando al máximo las funciones predefinidas de Oracle.

Sentencias:

SELECT INSERTObtiene filas de tablas de base de datos. Agrega filas a una tabla de la base de datos.SELECT [DISTINCT|ALL] lista_selecciónFROM tabla [,tabla ...][WHERE condición(es)][GROUP BY expresión [,expresión]...]

INSERT [INTO] tabla[lista de campos de tabla]VALUES (lista de valores de datos)ó

[HAVING condición][ORDER BY {columna, expresión} {ASC|DESC}]

INSERT INTO tablaSentencia SELECT

UPDATE DELETEModifica filas de una tabla que cumplan cláusula WHERE.

Borra filas identificadas por la cláusula WHERE

UPDATE tablaSET columna = expresión [, columna = expresión...][WHERE condición(es)]

DELETE FROM tabla[WHERE condición(es)]

3) Funciones pre-definidas

Funciones de carácter Funciones numérica LOWER, UPPER INITCAP CONCAT, SUBSTR LENGTH, INSTR LPAD, RPAD, TRIM CHR ASCII REPLACE

ROUND, TRUNC CEIL, FLOOR MOD POWER SQRT ABS SIGN Funciones de grupo : AVG, COUNT,

SUM, MAX, MIN

Funciones de fecha Funciones de conversión SYSDATE ADD_MONTHS, MONTHS_BETWEEN NEXT_DAY, LAST_DAY ROUND, TRUNC

TO_DATE TO_CHAR TO_NUMBER NVL DECODE

4) Sub-consultas

Es una sentencia SELECT que se encuentra dentro de una cláusula de otra sentencia SELECT.

Tipos:

Sub-consultas de una filaSub-consultas de múltiples filasSub-consultas de múltiples columna

5) Consultas multi-tablas

JOIN: Utilizada para consultar datos de más de una tabla.

Tipos de Join:Equijoin Non-equijoin Outer-join Selfjoin

6) Ejemplos

a) Demostración de las sentencias

Primero conectemos como el usuario scott/tiger. Connect Scott/tiger.

SQL> connect scott/tigerConectado.

La sentencia SELECT permite obtener filas de las tablas de base de datos.Ejemplo1: Muestra todas las tablas que pertenecen al usuario SCOTT.

SQL> SELECT TABLE_NAME FROM USER_TABLES;

TABLE_NAME------------------------------DEPTBONUSSALGRADEEMPCLIENTEVENDEDORCOMIDASFACTURADETALLE_FACTURACINESALA

11 filas seleccionadas.

Ejemplo2: Muestre todos los CONSTRAINTS a lo que puede acceder el usuario SCOTT.

SQL> SELECT CONSTRAINT_NAME, TABLE_NAME, SUBSTR(COLUMN_NAME,1,255) FROM ALL_CONS_COLUMNS WHERE TABLE_NAME='EMP';

CONSTRAINT_NAME TABLE_NAME SUBSTR(COLUMN_NAME,1,255)------------------------------ ------------------------------ --------------------------------------------------------SYS_C0011162 EMP EMPNO

Ejemplo3: Muestre todos los campos de la tabla empleado con la condición de que el cargo sea manager y ordénelo por el nombre del empleado.

SQL> SELECT * FROM EMP WHERE JOB = 'MANAGER' ORDER BY ENAME;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7698 BLAKE MANAGER 7839 01/05/81 2850 30 7698 BLAKE MANAGER 7839 01/05/81 2850 30

7782 CLARK MANAGER 7839 09/06/81 2450 10 7782 CLARK MANAGER 7839 09/06/81 2450 10


Ejemplo4: Muestre todos los campos de la tabla empleado con la condición de que el código del departamento sea 30 y ordénelo de forma descendiente por el nombre del empleado.

SQL> SELECT * FROM EMP WHERE DEPTNO = 30 ORDER BY ENAME DESC;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7521 WARD SALESMAN 7698 22/02/81 1250 500 30 7521 WARD SALESMAN 7698 22/02/81 1250 500 30 7844 TURNER SALESMAN 7698 08/09/81 1500 0 30 7844 TURNER SALESMAN 7698 08/09/81 1500 0 30 7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30 7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30 7698 BLAKE MANAGER 7839 01/05/81 2850 30 7698 BLAKE MANAGER 7839 01/05/81 2850 30 7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30 7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30


Ejemplo5: Muestre el código del departamento y la cantidad de empleados por departamento.

SQL> SELECT DEPTNO, COUNT (EMPNO) AS CANTIDAD FROM EMP GROUP BY DEPTNO;

DEPTNO CANTIDAD---------- ---------- 30 10 10 4

Ejemplo6: Muestre el código y el nombre del departamento, y además la cantidad de empleados por departamento.

SQL> SELECT E.DEPTNO,DNAME,COUNT(E.EMPNO) EMPLEADOS FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO GROUP BY E.DEPTNO,DNAME;

DEPTNO DNAME EMPLEADOS---------- -------------- ---------- 10 ACCOUNTING 4 30 SALES 10

Ejemplo7: Muestre el código y el nombre del departamento, y además la cantidad de empleados por departamento mayores o iguales a 4.

SQL> SELECT E.DEPTNO,DNAME,COUNT(E.EMPNO) EMPLEADOS FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO GROUP BY E.DEPTNO,DNAME HAVING COUNT (E.EMPNO)>=4;

DEPTNO DNAME EMPLEADOS---------- -------------- ---------- 30 SALES 10

La sentencia INSERT permite insertar varios registros en una tabla.Ejemplo1: Insertar datos especificando únicamente los valores de los campo.

SQL> INSERT INTO DEPT VALUES (50, 'MI AREA 1', 'LIMA' );

1 fila creada.

Ejemplo2: Insertar datos especificando todos los valores a ingresar.

SQL> INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES (60, 'MI AREA 2', 'LIMA' );

1 fila creada.

Ejemplo3: Insertar registros con una consulta select.

SQL> CREATE TABLE EJEMPLO ( EMPNO NUMBER(4), ENAME VARCHAR2(10) );

Tabla creada.

SQL> INSERT INTO EJEMPLO(EMPNO,ENAME) SELECT EMPNO,ENAME FROM EMP WHERE COMM != 0;

6 filas creadas.

La sentencia UPDATE permite la actualización de uno o varios registros de una única tabla. Es decir actualiza un campo de todos los registros como también actualiza varios campos de todos los registros

Ejemplo1: Actualiza el saldo de los empleado en un 12% cuyo cargo sea manager.

SQL> UPDATE EMPSET SAL= SAL * 1.2WHERE JOB='MANAGER';COMMIT;

Tabla modificada.

Ejemplo2: Actualiza el saldo de los empleado en 5000 y la comisión en 2000

SQL> UPDATE EMPSET SAL= 5000, COMM=1000;COMMIT;

Tabla modificada.

La sentencia DELETE permite eliminar uno o más registros de una tabla, como también permite eliminar un registro especifico. Ejemplo1: Eliminar todos los registro de la tabla ejemplo.

SQL> DELETE FROM EJEMPLO;

6 filas suprimidas.

Ejemplo2: Eliminar los departamentos cuyo código sean 50 y 60.

SQL> DELETE FROM DEPT WHERE DEPTNO >= 50 AND DEPTNO<=60;

2 filas suprimidas.

b) Demostración de las funciones de carácter.

Las funciones UPPER, LOWER, INITCAP reciben como parámetro una cadena cad. UPPER(cad).LOWER(cad).INITCAP(cad). Ejemplo: Muestre el nombre del empleado en Mayúscula, Minúscula y Letra capital.

SQL> SELECT UPPER(ENAME) MAYUSCULA, LOWER(ENAME) MINUSCULA, INITCAP(ENAME) LETRACAPITAL FROM EMP;

MAYUSCULA MINUSCULA LETRACAPIT---------------- -------------- ----------------ALLEN allen AllenWARD ward WardMARTIN martin MartinBLAKE blake Blake…


La función CONCAT recibe 2 parámetros. Devuelve cad1 concatenada con cad2. Esta función es equivalente al operador ||. CONCAT(cad1,cad2).Ejemplo: Concatena el nombre y el grado de los empleados.

SQL> SELECT CONCAT(ENAME,JOB), ENAME || '-' || JOB FROM EMP;

CONCAT(ENAME,JOB) ENAME||'-'||JOB------------------- --------------------ALLENSALESMAN ALLEN-SALESMANWARDSALESMAN WARD-SALESMANMARTINSALESMAN MARTIN-SALESMANBLAKEMANAGER BLAKE-MANAGER…14 filas seleccionadas.

SQL> SELECT (CONCAT (CONCAT (ENAME,'-'), JOB)), ENAME || '-' || JOB FROM EMP; Devuelve lo mismo.

La función SUBSTR permite extraer una porción de una cadena, para los cuáles recibe 3 parámetros. SUBSTR (cad, posicionInicial, nroCaracteres).Nota: la primera posición de una cadena en Oracle es 1.Ejemplo: Muestra los 3 primero caracteres de la columna nombre de la tabla empleados

SQL> SELECT SUBSTR(ENAME,1,3) FROM EMP;

SUB---ALLWARMARBLA…14 filas seleccionadas.

La función LENGTH recibe como parámetro una cadena cad devolviendo la longitud de dicha cadena. LENGTH(cad)Ejemplo: Devolver la longitud del campo nombre de la tabla empleados.

SQL> SELECT ENAME,LENGTH(ENAME) FROM EMP;

ENAME LENGTH(ENAME)---------- -------------ALLEN 5WARD 4MARTIN 6BLAKE 5…


La función INSTR recibe 2 parámetros y devuelve la posición de la segunda cadena dentro de la primera. INSTR(cad1,cad2).Ejemplo: Muestre la posición de ‘A’ dentro del nombre de los empleados.

SQL> SELECT ENAME, INSTR(ENAME,'A') FROM EMP;

ENAME INSTR(ENAME,'A')---------- ----------------ALLEN 1WARD 2MARTIN 2BLAKE 3…14 filas seleccionadas.

La función LPAD y RPAD reciben 3 parámetros. Concatena por la izquierda LPAD devuelve cad1 con longitud n, y ajustada a la derecha, rellenando por la izquierda con cad2. (cad1,n,cad2’). Concatena por la derecha RPAD Devuelve cad1 con longitud n, y ajustada a la izquierda, rellenando por la derecha con cad2. (cad1,n,cad2’).

SQL> SELECT LPAD(ENAME,20,'*'), RPAD(ENAME,20,'*') FROM EMP;

LPAD(ENAME,20,'*') RPAD(ENAME,20,'*')-------------------- -------------------***************ALLEN ALLEN*******************************WARD WARD******************************MARTIN MARTIN*****************************BLAKE BLAKE***************…14 filas seleccionadas.

La función CHR devuelve el carácter cuyo valor codificado es n. CHR(N).

SQL> SELECT CHR(65) FROM DUAL;

C-A

La función ASCII devuelve el valor ascii de cad.

SQL> SELECT ASCII('A') FROM DUAL;

ASCII('A')---------- 65

La función REPLACE devuelve la cadena en la que cada ocurrencia de la cadena antigua ha sido sustituida por la cadena nueva.

SQL> SELECT REPLACE('DIGO','I','IE') FROM DUAL;

REPLA-----DIEGO

c) Demostración de las funciones numéricas.

La función ROUND calcula el redondeo de m a n decimales. Si n<0 el redondeo se efectúa a porla izquierda del punto decimal. ROUND(m,n).

SQL> SELECT ROUND(234.345,1) FROM DUAL;

ROUND(234.345,1)---------------- 234,3

SQL> SELECT ROUND(234.345,0) FROM DUAL;

ROUND(234.345,0)---------------- 234

La función TRUNC calcula m truncado a n decimales (n puede ser negativo). TRUNC(m.n).

SQL> SELECT TRUNC(123.456,1) FROM DUAL;

TRUNC(123.456,1)---------------- 123,4

La función CEIL calcula el valor entero inmediatamente superior o igual a n (devuelve el entero mayor mas próximo). CEIL(n).

SQL> SELECT CEIL(13.7) FROM DUAL;

CEIL(13.7)---------- 14

La función FLOOR calcula el valor entero inmediatamente inferior o igual a n (el entero menor más próximo). FLOOR(n).

SQL> SELECT FLOOR(13.7) FROM DUAL;

FLOOR(13.7)----------- 13

La función MOD calcula el resto resultante de dividir m entre n. MOD(m,n).

SQL> SELECT MOD(15,2) FROM DUAL;

MOD(15,2)---------- 1

La función ABS calcula el valor absoluto de n. ABS(n).

SQL> SELECT ABS(-15) FROM DUAL;

ABS(-15)---------- 15

La función POWER Calcula la potencia n-esima de m. POWER(m,n).

SQL> SELECT POWER(3,2) FROM DUAL;

POWER(3,2)---------- 9

La función SQRT calcula la raíz cuadrada de n. SQRT(n).

SQL> SELECT SQRT(4) FROM DUAL;

SQRT(4)---------- 2

La función SIGN calcula el signo de n, devolviendo -1 si n<0, 0 si n=0 y 1 si n>0.

SQL> SELECT SIGN(-12) FROM DUAL;

SIGN(-12)---------- -1

sesion05 - manipulacion de datos (oracle)

Education