manual sql plus
TRANSCRIPT
Introducción a Oracle – SQL-Plus
Introducción a OracleSQL-Plus
Introducción a Oracle – SQL-Plus
INDICE DE CONTENIDO
ACCESO Y VISUALIZACIÓN DE DATOS .......................................................................................4EL MODELO RELACIONAL ..............................................................................................................4VISIÓN GENERAL DE SQL................................................................................................................6SQL CONTRA SQL*PLUS ..................................................................................................................6Comandos SQL......................................................................................................................................6Use los comandos de SQL*Plus para:....................................................................................................6VISIÓN GENERAL DEL INICIO DE UNA SESIÓN SQL.................................................................7ACCESO A LA AYUDA......................................................................................................................8VISTAZO DE LA SENTENCIA SELECT...........................................................................................8SELECCIÓN DE COLUMNAS ............................................................................................................9SELECCIÓN DE FILAS .....................................................................................................................12VER EL DICCIONARIO DE DATOS................................................................................................20Nombre de tabla ...................................................................................................................................20Descripción...........................................................................................................................................20EDICIÓN DEL BUFFER DE SQL-PLUS ..........................................................................................21LABORATORIO 1-1...........................................................................................................................24EJECUCIÓN DE OPERACIONES SOBRE TIPOS DE DATOS Y FUNCIONES...........................25VISTAZO A LOS VALORES Y FUNCIONES NUMÉRICOS.........................................................25REFERENCIA DE LOS VALORES Y FUNCIONES NUMÉRICOS...............................................25VISTAZO A LOS VALORES Y FUNCIONES DE FECHA.............................................................30REFERENCIA DE LOS VALORES Y FUNCIONES DE FECHA...................................................30REFERENCIA DE LOS VALORES Y FUNCIONES DE CARACTERES ......................................34VISIÓN GENERAL DE ALGUNAS FUNCIONES ADICIONALES...............................................37REFERENCIA DE ALGUNAS FUNCIONES ADICIONALES .......................................................38LABORATORIO 2-1...........................................................................................................................38VISTAZO A LAS FUNCIONES DE GRUPO....................................................................................40LABORATORIO 2-2...........................................................................................................................45OBTENIENDO DATOS DE MULTIPLES TABLAS........................................................................46CARACTERÍSTICAS DEL JOIN.......................................................................................................46TIPOS DE JOIN...................................................................................................................................47EQUIJOIN............................................................................................................................................47OUTER JOIN.......................................................................................................................................48SEF-JOINS ..........................................................................................................................................49NON-EQUIJOINS ...............................................................................................................................50OPERADORES DE CONJUNTO .......................................................................................................51EL OPERADOR UNION ....................................................................................................................51EL OPERADOR INTERSECCIÓN (INTERSECT) ...........................................................................52EL OPERADOR MENOS (MINUS)...................................................................................................52LABORATORIO 3-1...........................................................................................................................53VISIÓN GENERAL DE LA CREACIÓN DE TABLAS....................................................................56CREACIÓN DE TABLAS ..................................................................................................................57RESTRICCIONES...............................................................................................................................57MODIFICACIÓN DE TABLAS .........................................................................................................59Borrar la tabla original.........................................................................................................................61
Introducción a Oracle – SQL-Plus
CREACIÓN DE VISTAS ....................................................................................................................63VISTAZO AL ACCESO DE TABLAS Y VISTAS............................................................................66UN VISTAZO A LA CONCESIÓN DE ACCESO A TABLAS Y VISTAS .....................................67VISIÓN GENERAL DE LOS SINÓNIMOS......................................................................................68CREACIÓN DE SINÓNIMOS............................................................................................................69LABORATORIO 4-1...........................................................................................................................70VISIÓN GENERAL SOBRE LOS ÍNDICES .....................................................................................71CREACIÓN DE ÍNDICES ..................................................................................................................71CREACIÓN DE GENERADORES DE SECUENCIA.......................................................................74LABORATORIO 5-1...........................................................................................................................75INSERCIÓN DE FILAS ......................................................................................................................76ACTUALIZACIÓN DE FILAS...........................................................................................................77ELIMINACIÓN DE FILAS.................................................................................................................79UN VISTAZO AL PROCESAMIENTO DE TRANSACCIONES ....................................................80PROCESAMIENTO DE TRANSACCIONES....................................................................................81LABORATORIO 6-1...........................................................................................................................84
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 4
ACCESO Y VISUALIZACIÓN DE DATOS
EL MODELO RELACIONAL
En un sistema de bases de datos relacional, la información se organiza en forma de tablas.
Notas rápidas:
q Las categorías de información se listan en la parte de arriba de cada tabla
q Los casos individuales se listan al lado izquierdo
q En esta forma, usted puede visualizar, entender y usar esta información inmediatamente
Cada columna contiene un tipo de información. Cada fila está compuesta de columnas que contienen
un único valor.
Ejemplo:
La columna SAL en la fila del empleado SMITH tiene el valor de 800
Tabla EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO7369 SMITH RECEPCIONISTA 7902 17/12/1995 800 207499 ALLEN VENDEDOR 7698 20/02/1996 1600 300 307521 WARD VENDEDOR 7698 22/02/1996 1250 500 307566 JONES GERENTE 7839 02/04/1996 2975 207654 MARTIN VENDEDOR 7698 28/07/1996 1250 1400 307698 BLAKE GERENTE 7839 31/05/1996 2850 307782 CLARK GERENTE 7839 09/06/1996 2450 107788 SCOTT ANALISTA 7566 09/12/1997 3000 207839 KING PRESIDENTE 17/11/1996 5000 107844 TURNER VENDEDOR 7698 08/11/1996 1500 0 307876 ADAMS RECEPCIONISTA 7788 12/01/1998 1100 207900 JAMES RECEPCIONISTA 7698 03/12/1996 950 307902 FORD ANALISTA 7566 03/12/1996 3000 207934 MILLER RECEPCIONISTA 7782 23/01/1998 1300 10
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 5
FILAS COLUMNAS
La información en una tabla puede relacionarse con la información que se encuentra en otra.
Tabla EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO7369 SMITH RECEPCIONISTA 7902 17/12/1995 800 207499 ALLEN VENDEDOR 7698 20/02/1996 1600 300 307521 WARD VENDEDOR 7698 22/02/1996 1250 500 307566 JONES GERENTE 7839 02/04/1996 2975 207654 MARTIN VENDEDOR 7698 28/07/1996 1250 1400 307698 BLAKE GERENTE 7839 31/05/1996 2850 307782 CLARK GERENTE 7839 09/06/1996 2450 107788 SCOTT ANALISTA 7566 09/12/1997 3000 207839 KING PRESIDENTE 17/11/1996 5000 107844 TURNER VENDEDOR 7698 08/11/1996 1500 0 307876 ADAMS RECEPCIONISTA 7788 12/01/1998 1100 207900 JAMES RECEPCIONISTA 7698 03/12/1996 950 307902 FORD ANALISTA 7566 03/12/1996 3000 207934 MILLER RECEPCIONISTA 7782 23/01/1998 1300 10
Tabla EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO7369 SMITH RECEPCIONISTA 7902 17/12/1995 800 207499 ALLEN VENDEDOR 7698 20/02/1996 1600 300 307521 WARD VENDEDOR 7698 22/02/1996 1250 500 307566 JONES GERENTE 7839 02/04/1996 2975 207654 MARTIN VENDEDOR 7698 28/07/1996 1250 1400 307698 BLAKE GERENTE 7839 31/05/1996 2850 307782 CLARK GERENTE 7839 09/06/1996 2450 107788 SCOTT ANALISTA 7566 09/12/1997 3000 207839 KING PRESIDENTE 17/11/1996 5000 107844 TURNER VENDEDOR 7698 08/11/1996 1500 0 307876 ADAMS RECEPCIONISTA 7788 12/01/1998 1100 207900 JAMES RECEPCIONISTA 7698 03/12/1996 950 307902 FORD ANALISTA 7566 03/12/1996 3000 207934 MILLER RECEPCIONISTA 7782 23/01/1998 1300 10
Tabla DEPT
DEPTNO DNAME LOC10 FINANZAS NEW YORK20 INVESTIGACIÓN DALLAS30 VENTAS CHICAGO40 OPERACIONES BOSTON
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 6
VISIÓN GENERAL DE SQL
SQL-Plus es una herramienta diseñada para usar con ORACLE, la cual extiende las capacidades del
estándar ANSI-SQL
SQL CONTRA SQL*PLUS
SQL Es un lenguaje que consiste en comandos para guardar, recuperar, mantener yregular el acceso a una base de datos
SQL*Plus Es una aplicación que reconoce y ejecuta comandos SQL y comandosespecializados de SQL*Plus que pueden personalizar reportes, proveer facilidadesde ayuda y edición, así como mantener variables del sistema.
Con SQL se puede manipular y recuperar datos, así como crear y modificar la estructura de los datos.
Funciones de los comandos SQL
q Consultas
q Manipulación de datos
q Definición de datos
q Control de los datos
Comandos SQL
ALTER DROP REVOKEAUDIT GRANT ROLLBACKCOMMENT INSERT SELECTCOMMIT LOCK UPDATECREATE NO AUDIT VALIDATEDELETE RENAME
Escriba comandos SQL*Plus para manipular comandos SQL
Use los comandos de SQL*Plus para:
q Entrar, editar, guardar, recuperar y correr comandos SQL
q Formatear, guardar, imprimir y realizar cálculos sobre los resultados de una consulta en forma
de reportes
q Listar la definición de las columnas de cualquier tabla
q Acceder y copiar datos entre bases de datos SQL
q Enviar y aceptar mensajes de un usuario final
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 7
Comandos de SQL-Plus
@ (ejecutar) COPY QUIT / (ejecutar último)DESCRIBE REMARK ACCEPT DISCONNECTRUN APPEND EDIT SAVEBREAK EXIT SET BTITLEGET SHOW CHANGE HELPSPOOL CLEAR HOST SQLPLUSCOLUMN INPUT START COMPUTELIST TIMING CONNECT NEWPAGETTITLE UNDEFINE
VISIÓN GENERAL DEL INICIO DE UNA SESIÓN SQL
El primer paso para abrir una sesión SQL es acceder a SQL-Plus
a) Interfaz Grafica
b) Desde la línea de Comando
SQL> connect [username[/password[@database]]]
username = nombre de usuariopassword = clave de acceso@database = alias de la base de datos
c) Cierre de sesión:
SQL> exit <ENTER>
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 8
ACCESO A LA AYUDA
Acceso al sistema de ayuda en línea, siempre y cuando el administrador de la base de datos la ha
cargado y habilitado.
Ejemplos:
Listado de todos los comandos SQL-Plus
VISTAZO DE LA SENTENCIA SELECT
SQL> HELP TOPICS <ENTER>
Tabla EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO7369 SMITH RECEPCIONISTA 7902 17/12/1995 800 207499 ALLEN VENDEDOR 7698 20/02/1996 1600 300 307521 WARD VENDEDOR 7698 22/02/1996 1250 500 307566 JONES GERENTE 7839 02/04/1996 2975 207654 MARTIN VENDEDOR 7698 28/07/1996 1250 1400 307698 BLAKE GERENTE 7839 31/05/1996 2850 307782 CLARK GERENTE 7839 09/06/1996 2450 107788 SCOTT ANALISTA 7566 09/12/1997 3000 207839 KING PRESIDENTE 17/11/1996 5000 107844 TURNER VENDEDOR 7698 08/11/1996 1500 0 307876 ADAMS RECEPCIONISTA 7788 12/01/1998 1100 207900 JAMES RECEPCIONISTA 7698 03/12/1996 950 307902 FORD ANALISTA 7566 03/12/1996 3000 207934 MILLER RECEPCIONISTA 7782 23/01/1998 1300 10
Tabla DEPT
DEPTNO DNAME LOC10 FINANZAS NEW YORK20 INVESTIGACIÓN DALLAS30 VENTAS CHICAGO40 OPERACIONES BOSTON
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 9
Seleccionar información de una base de datos, es decir, hacer consultas, es una de las operaciones más
comunes que se realizan sobre una base de datos.
Items de un RDBMS que pueden incluirse en una sentencia SELECT
q Columnas
q Expresiones
q Constantes
Requerimientos mínimos para ejecutar una sentencia SELECT
q El comando SELECT (indica que se va a consultar)
q La palabra FROM (indica donde se va a realizar la consulta)
SELECCIÓN DE COLUMNAS
Existen dos maneras distintas de visualizar todas las columnas de una tabla:
q Digitando todos los nombres de columna separados por comas y la tabla donde esas columnas
están definidas, o
q Digitar un asterisco y el nombre de la tabla donde están definidas las columnas
Sintaxis:
Ejemplo:
Visualizar todas las columnas de información acerca de cada departamento. (LAB1)
SELECT nombre_columna [, nombre_columna ...]
FROM nombre_tabla
SELECT *
FROM nombre_tabla
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC--------------- --------------- ------------------ 10 FINANZAS NEW YORK
20 INVESTIGACION DALLAS 30 VENTAS CHICAGO 40 OPERACIONES BOSTON
4 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 10
DNAME------------------------------FINANZASINVESTIGACIÓNVENTASOPERACIONES
4 rows selected.
Se puede utilizar el comando DESCRIBE para visualizar los nombres de columnas de una tabla.
Ejemplo:
Visualizar la estructura de la tabla DEPT: LAB2
Para visualizar una columna específica, entre el nombre de la columna en la cláusula SELECT y la
tabla en la que está definida en la cláusula FROM.
Ejemplo de columna única:
Visualizar los nombres de todos los departamentos: LAB3
Ejemplo de múltiples columnas:
Visualizar el nombre y número de todos los departamentos. LAB4
SQL> DESCRIBE dept;
Name NULL? Type--------------- --------------- ------------------DEPTNO NOT NULL NUMBER(2)DNAME CHAR(14)LOC CHAR(13)
SQL> SELECT dname FROM dept;
SQL> SELECT dname, deptno FROM dept;
DNAME DEPTNO------------------------------ ---------------------FINANZAS 10INVESTIGACIÓN 20VENTAS 30OPERACIONES 40
4 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 11
Con SQL se pueden quitar las filas duplicadas antes que los datos sean devueltos por la sentencia
SELECT con la cláusula DISTINCT.
Sintaxis - SELECT usando la cláusula DISTINCT
Ejemplo:
Visualizar cada cargo de la tabla EMP de forma única. LAB5
Con SQL se pueden definir alias para los nombres de columnas en una sentencia SELECT.
Ejemplo:
Visualizar cada cargo de la tabla EMP de forma única y con el nombre de JOB LIST. LAB6
SELECT DISTINCT nombre_columna [, nombre_columna ...]
FROM nombre_tabla
SQL> SELECT DISTINCT job FROM emp;
JOB------------------------------ANALISTARECEPCIONISTAGERENTEPRESIDENTEVENDEDOR
5 rows selected.
SQL> SELECT DISTINCT job “JOB LIST” FROM EMP;
JOB LIST ------------------------------ANALISTARECEPCIONISTAGERENTEPRESIDENTEVENDEDOR
5 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 12
SINTAXIS DE LA SENTENCIA SELECT
SELECT ...FROM ...WHERE ...
Nota rápida:
q Solo se puede hacer referencia a los alias de columna en la cláusula SELECT de una consulta.
SELECCIÓN DE FILAS
Para seleccionar filas específicas de una tabla, use la cláusula WHERE para especificar una condición.
Componentes de la condición de búsqueda de la cláusula WHERE
q Nombre de columna o expresión o constante.
q Operador de comparación
q Expresión o nombre de columna o constante
Notas rápidas:
q Las constantes de cadena y de fecha deben encerrarse entre comillas simples.
q Las constantes de carácter son sensibles a mayúsculas y minúsculas y las constantes de fecha
deben estar en el formato DD-MM-YY
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 13
Operadores de comparación
= Igual a
!= <> ^= Diferente de
> Mayor que
>= Mayor o igual que
< Menor que<= Menor o igual que
BETWEEN... AND Entre dos valores
IN (lista) Igual que algún miembro dela lista
IS NULL Es un valor nulo
LIKE Concuerda con un patrón decaracteres
Selección de filas basados en criterios de igualdad o desigualdad usando los operadores
=, <>, >=, <=, >, <.
Ejemplo:
Seleccionar el número, nombre y salario de los empleados en el departamento 30. LAB7
SQL> SELECT empno, ename, sal FROM emp WHERE deptno = 30;
EMPNO ENAME SAL------------ ------------- -------------
7499 ALLEN 16007521 WARD 12507654 MARTIN 12507698 BLAKE 28507844 TURNER 15007900 JAMES 950
6 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 14
Mostrar el nombre, cargo y salario de todos los empleados, excepto los gerentes. LAB8
Selección basada en un rango de valores usando el operador BETWEEN ... AND.
Ejemplo:
Seleccionar el número, cargo y número de departamento de todos los empleados contratados entre
Enero 1 de 1982 y Enero 1 de 1983. LAB9
Selección de filas basándose en la coincidencia con valores dentro de una lista, usando el operador IN.
SQL> SELECT ename, job, sal FROM emp WHERE job <> ‘MANAGER’;
ENAME JOB SAL------------ ----------------- -------------SMITH CLERK 800ALLEN SALESMAN 1600WARD SALESMAN 1250MARTIN SALESMAN 1250SCOTT ANALYST 3000KING PRESIDENT 5000TURNER SALESMAN 1500ADAMS CLERK 1100JAMES CLERK 950FORD ANALYST 3000MILLER CLERK 1300
11 rows selected.
SQL> SELECT ename, job, deptno, hiredate FROM emp
WHERE hiredate BETWEEN '01/01/1982' AND '01/01/1983';
ENAME JOB DEPTNO HIREDATE---------- - -------- - --------- ----------MILLER CLERK 10 23/01/1982
1 row selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 15
Ejemplo:
Seleccionar el nombre, cargo y número de departamento de todos los empleados que son recepcionistas
y analistas: LAB10
Selección de filas basándose en la coincidencia con un patrón de caracteres usando comodines con el
operador LIKE:
Comodines:
q %: Coincidencia con cualquier cantidad de caracteres.
q _ : Coincidencia con un caracter.
Ejemplos:
Seleccionar el nombre, cargo, número de departamento y fecha de contratación para los empleados
cuyo apellido empiece con la letra mayúscula ‘M’. LAB11
SQL> SELECT ename, job, deptno FROM emp WHERE job IN (‘CLERK’, ‘ANALYST’);
ENAME JOB DEPTNO------------ ----------------- -------------SMITH CLERK 20SCOTT ANALYST 20ADAMS CLERK 20JAMES CLERK 30FORD ANALYST 20MILLER CLERK 10
6 rows selected.
SQL> SELECT ename, job, deptno, hiredate FROM emp
WHERE ename LIKE ‘M%’;
ENAME JOB DEPTNO HIREDATE------------ ----------------- ------------- ---------------MARTIN SALESMAN 30 28-SEP-81MILLER CLERK 10 23-JAN-82
2 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 16
Seleccionar el nombre, cargo, número de departamento y fecha de contratación para los empleados
cuyo apellido empiece con la letra mayúscula ‘J’ seguido de dos caracteres y que termine con ‘ES’.
LAB12
Use el operador NOT para hacer una condición negativa junto con los operadores:
q NOT BETWEEN ... AND ...
q NOT IN (lista)
q IS NOT NULL
q NOT LIKE
Conecte operadores lógicos con expresiones lógicas complejas.
Expresiones lógicas
q AND
q OR
Cuando una condición contiene más de una expresión, el RDBMS evalúa cada expresión. Luego los
resultados de cada condición se unen teniendo en cuenta la precedencia de los operadores de conexión.
Igual precedencia: =, <>, <, <=, >, >=, IN, LIKE, IS NULL, BETWEEN … AND …
Luego los operadores lógicos se evalúan en el siguiente orden:
q NOT
q AND
q OR
SQL> SELECT ename, job, deptno, hiredate FROM emp
WHERE ename LIKE ‘J__ES’;
ENAME JOB DEPTNO HIREDATE------------ ----------------- ------------- ---------------JONES MANAGER 20 02-APR-81JAMES CLERK 30 03-DEC-81
2 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 17
Ejemplo:
Seleccionar el nombre, cargo y salario de los gerentes que ganen más de $1500, al igual que el de todos
los vendedores: LAB13
Seleccionar el nombre, cargo y salario de los gerentes y vendedores que ganen $1500 o más: LAB14
SQL> SELECT ename, job, sal FROM emp
WHERE sal >= 1500 AND job = ‘MANAGER’ OR job = ‘SALESMAN’;
ENAME JOB SAL------------ ----------------- -------------ALLEN SALESMAN 1600WARD SALESMAN 1250JONES MANAGER 2975MARTIN SALESMAN 1250BLAKE MANAGER 2850CLARK MANAGER 2450TURNER SALESMAN 1500
7 rows selected.
SQL> SELECT ename, job, sal FROM emp
WHERE sal >= 1500 AND (job = ‘MANAGER’ OR job = ‘SALESMAN’);
ENAME JOB SAL------------ ----------------- -------------ALLEN SALESMAN 1600JONES MANAGER 2975BLAKE MANAGER 2850CLARK MANAGER 2450TURNER SALESMAN 1500
5 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 18
SINTAXIS DE LA SENTENCIA SELECT
SELECT ...FROM ...WHERE ...ORDER BY …
SALIDAS ORDENADAS DE UNA CONSULTA
ORDER BY puede ordenar:
q En orden ascendente.
q En modo descendente.
q Por múltiples columnas
q Por la posición de la columna.
Ejemplos
Ordenamiento ascendente: Mostrar el salario, cargo y nombre de los empleados del departamento 10
en orden ascendente, por salario: LAB15
SQL> SELECT sal, job, ename FROM emp
WHERE deptno = 10
ORDER BY sal;
SAL JOB ENAME------------ ----------------- -------------
1300 CLERK MILLER2450 MANAGER CLARK5000 PRESIDENT KING
3 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 19
Ordenamiento descendente: Mostrar el salario, cargo y nombre de los empleados del departamento 10
en orden descendente por salario: LAB16
Ordenamiento por múltiples columnas: Mostrar el nombre, cargo y salario de los empleados en el
departamento 30. Ordene los resultados por cargo. Si hay más de un empleado con el mismo cargo,
ordénelos por salario en orden descendente y finalmente por apellido. LAB17
SQL> SELECT sal, job, ename FROM emp
WHERE deptno = 10
ORDER BY sal DESC;
SAL JOB ENAME------------ ----------------- -------------
5000 PRESIDENT KING2450 MANAGER CLARK1300 CLERK MILLER
3 rows selected.
SQL> SELECT ename, job, sal FROM emp
WHERE deptno = 30
ORDER BY job, sal DESC, ename;
SAL JOB ENAME------------ ----------------- -------------JAMES CLERK 950BLAKE MANAGER 2850ALLEN SALESMAN 1600TURNER SALESMAN 1500MARTIN SALESMAN 1250WARD SALESMAN 1250
6 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 20
Ordenamiento por posición de columna: Muestre el nombre, salario y cargo de los empleados del
departamento 10 en orden ascendente por salario: LAB18
VER EL DICCIONARIO DE DATOS
El diccionario de datos es un conjunto de tablas y vistas que contiene información descriptiva acerca de
las tablas, privilegios de acceso de los usuarios, y demás características de la base de datos.
Tablas de usuario que se consultan frecuentemente en el diccionario de datos
Nombre de tabla Descripción
user_catalog Tablas, vistas, sinónimos, secuencias y otros objetos de propiedad del
usuario
user_constraints Información acerca de las restricciones en las tablas del usuario
user_indexes Descripción de los índices del usuario
user_sequences Descripción de las secuencias del usuario
user_synonyms Descripción de los sinónimos del usuario
user_tables Descripción de las tablas del usuario
user_users Información acerca del usuario actual
user_views Información acerca de las vistas del usuario
all_tables Descripción de todas las tablas accesibles por el usuario
SQL> SELECT ename, sal, job FROM emp
WHERE deptno = 10
ORDER BY 2;
ENAME SAL JOB------------ ------------ -----------------MILLER 1300 CLERKCLARK 2450 MANAGERKING 5000 PRESIDENT
3 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 21
Ejemplos
USER_TABLES: Mostrar una columna del diccionario de datos user_tables: LAB19
EDICIÓN DEL BUFFER DE SQL-PLUS
La edición del buffer de SQL-Plus se realiza con comandos de SQL-Plus:
Comando Descripción
A[PPEND] Adiciona texto específico a la línea actual en el buffer
C[HANGE] Cambia la primera ocurrencia de un texto específico en la línea actual del buffer
por una cadena especificada. Ejemplo: C / ENP / EMP
CL[EAR] BUFFER
o
CLE[AR] BUFFER
Borra el contenido del buffer
DEL Borra el contenido de la línea actual del buffer
I[NPUT] Adiciona una o más líneas de texto después de la línea actual del buffer.
Presione <ENTER> en una línea en blanco para salir del modo INPUT
L[IST] Lista todas las líneas del buffer
L[IST] n Lista la línea número n del buffer
R[UN] Muestra y ejecuta el contenido del buffer
/ Ejecuta el contenido del buffer
n Fija el puntero del buffer en la línea n
n <cadena de texto> Cambia el contenido de la línea n por la cadena de texto especificada
SQL> SELECT table_name FROM user_tables;
TABLE_NAME--------------------DEPTEMPSALGRADE
3 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 22
Nota rápida
q Un asterisco (*) aparecerá al lado del número de línea actual del buffer
Ejemplos:
Reescribir la línea 2 cuando ocurre un error: LAB20
Fijar el puntero del buffer en la línea 1. Cambiar SAL por DEPTNO:
SQL> SELECT ename, job, sal
FRO emp
WHERE deptno = 30;
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> 2 FROM emp
SQL> /
ENAME JOB SAL------------- ---------------- -------------ALLEN SALESMAN 1600WARD SALESMAN 1250MARTIN SALESMAN 1250BLAKE MANAGER 2850TURNER SALESMAN 1500JAMES CLERK 950
6 rows selected.
SQL> 11* SELECT ename, job, salSQL> C / sal / deptno1* SELECT ename, job, deptnoSQL> /
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 23
Agregar la columna HIREDATE en la línea actual usando el comando APPEND:
Borrar la línea 3:
SQL> 11* SELECT ename, job, deptnoSQL> A , hiredate1* SELECT ename, job, deptno, hiredateSQL> /
ENAME JOB DEPTNO------------- ---------------- -------------ALLEN SALESMAN 30WARD SALESMAN 30MARTIN SALESMAN 30BLAKE MANAGER 30TURNER SALESMAN 30JAMES CLERK 30
6 rows selected.
ENAME JOB DEPTNO HIREDATE------------- ---------------- ------------- ---------------ALLEN SALESMAN 30 20-FEB-81WARD SALESMAN 30 22-FEB-81MARTIN SALESMAN 30 28-SEP-81BLAKE MANAGER 30 31-MAY-81TURNER SALESMAN 30 08-SEP-81JAMES CLERK 30 03-DEC-81
6 rows selected.
SQL> 33* WHERE deptno = 30SQL> delSQL> /
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 24
LABORATORIO 1-1
Use la tabla EMP para completar los siguientes ejercicios:
1. Muestre cada nombre de empleado y su fecha de contratación.
2. Muestre la información del ejercicio 1, con la fecha de contratación apareciendo de primero.
3. Muestre los empleados que ganan más de $2.000. Rotule la columna como Employee.
4. Muestre los empleados que no son recepcionistas ni vendedores. Ordene la lista por el nombre
de empleado.
5. Muestre los empleados cuyo nombre contiene la letra ‘S’.
6. Muestre los nombres de empleados y su salario de aquellos que tengan un salario menor que su
comisión. Rotule el nombre del empleado como Name y la columna salario como Salary.
7. Muestre los empleados contratados en 1981.
8. Muestre los nombres y cargos de los empleados en los departamentos 10 y 20.
ENAME JOB DEPTNO HIREDATE------------- ---------------- ------------- ---------------SMITH CLERK 20 17-DEC-80ALLEN SALESMAN 30 20-FEB-81WARD SALESMAN 30 22-FEB-81JONES MANAGER 20 02-APR-81MARTIN SALESMAN 30 28-SEP-81BLAKE MANAGER 30 31-MAY-81CLARK MANAGER 10 09-JUN-81SCOTT ANALYST 20 09-DEC-82KING PRESIDENT 10 17-NOV-81TURNER SALESMAN 30 08-SEP-81ADAMS CLERK 20 12-JAN-83JAMES CLERK 30 03-DEC-81FORD ANALYST 20 03-DEC-81MILLER CLERK 10 23-JAN-82
14 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 25
EJECUCIÓN DE OPERACIONES SOBRE TIPOS DE DATOS Y FUNCIONES
VISTAZO A LOS VALORES Y FUNCIONES NUMÉRICOS
Realice cálculos varios sobre números usando los siguientes operadores aritméticos:
+ Suma
- Resta
* Multiplicación
/ División
( ) Paréntesis – anular precedencia
Los operadores aritméticos pueden ser usados en la mayoría de sentencias SQL.
REFERENCIA DE LOS VALORES Y FUNCIONES NUMÉRICOS
Muestre el resultado de los cálculos como si fueran columnas con expresiones aritméticas en sentencias
SELECT.
Ejemplo: expresiones en sentencias SELECT.
Muestre el nombre, salario, comisión y la compensación total para todos los vendedores cuya comisión
es mayor que el 25% de su salario. LAB21
SQL> SELECT ename, sal, comm, sal + comm
2 FROM emp
3 WHERE job = ‘SALESMAN’ AND comm > 0.25 * sal
4 ORDER BY 4;
ENAME SAL COMM SAL+COMM-------------- -------------- --------------- ---------------WARD 1250 500 1750MARTIN 1250 1400 2650
2 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 26
Una fila sin valor en una columna, se dice que contiene un valor NULO.
Cuando una expresión o función individual hace referencia a una columna que contiene un valor nulo,
el resultado es también nulo.
Ejemplo:
Muestre el nombre, cargo, salario, comisión y compensación total de los empleados del departamento
30. LAB22
SQL> DESCRIBE emp;
Name Null? Type--------------------- ---------------- ----------------------EMPNO NOT NULL NUMBER (4)ENAME CHAR (10)JOB CHAR (9)MGR NUMBER (4)HIREDATE DATESAL NUMBER (7, 2)COMM NUMBER (7, 2)DEPTNO NOT NULL NUMBER (2)
SQL> SELECT ename, job, sal, comm, sal + comm
2 FROM emp
3 WHERE deptno = 30;
ENAME JOB SAL COMM SAL+COMM-------------- -------------------- -------------- -------------- --------------------ALLEN SALESMAN 1600 300 1900WARD SALESMAN 1250 500 1750JONES SALESMAN 2975MARTIN SALESMAN 1250 1400 2650BLAKE MANAGER 2850SCOTT SALESMAN 3150TURNER SALESMAN 1500 0 1500JAMES CLERK 950
8 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 27
Convierta un valor nulo en uno no-nulo con el propósito de evaluar una expresión con la función NVL
Cuando NVL evalúa la primera expresión, si esta es no-nula, retorna dicha expresión; en caso
contrario, retorna la segunda.
Sintaxis:
Ejemplo:
Muestre el nombre de empleado, cargo, salario, comisión y compensación total de los empleados del
departamento 30. LAB23
El RDMBS evalúa cada expresión aritmética. Los resultados de las expresiones se combinan luego en
el orden determinado por la precedencia de los operadores.
NVL (expr1, expr2)
SQL> SELECT ename, job, sal, comm, NVL(sal, 0) + NVL(comm, 0)
2 FROM emp
3 WHERE deptno = 30;
ENAME JOB SAL COMM NVL(SAL, 0)+NVL(COMM, 0)-------------- -------------------- -------------- -------------- -------------------------------------------ALLEN SALESMAN 1600 300 1900WARD SALESMAN 1250 500 1750JONES SALESMAN 2975 2975MARTIN SALESMAN 1250 1400 2650BLAKE MANAGER 2850 2850SCOTT SALESMAN 3150 3150TURNER SALESMAN 1500 0 1500JAMES CLERK 950 950
8 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 28
Orden de evaluación
* Multiplicación
/ División
Luego
+ Suma
- Resta
Para anular la precedencia de los operadores, coloque parte de la expresión entre paréntesis. ORACLE
evalúa estas expresiones primero.
Ejemplo:
Muestre el nombre de empleado, salario, comisión y compensación total para todos los vendedores
ordenado por compensación total. LAB24
SQL> SELECT ename, sal, comm, 12 * (sal + comm) total
2 FROM emp
3 WHERE job = ‘SALESMAN’
4 ORDER BY 12 * (sal + comm);
ENAME SAL COMM TOTAL-------------- -------------- -------------- ------------TURNER 1500 0 18000WARD 1250 500 21000ALLEN 1600 300 22800MARTIN 1250 1400 31800JONES 2975SCOTT 3150
6 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 29
Muestre el nombre de empleado, salario, comisión y salario anual, más un mes de comisión para todos
los vendedores ordenado por compensación total. LAB25
Las funciones numéricas aceptan una entrada numérica y retornan valores numéricos
Ejemplo: función ROUND
Liste el nombre, salario mensual, salario diario (basado en un mes laboral de 22 días) y el salario diario
redondeado al próximo entero para los empleados ALLEN y JONES. LAB26
SQL> SELECT ename, sal, comm, 12 * sal + comm total
2 FROM emp
3 WHERE job = ‘SALESMAN’
4 ORDER BY 4;
ENAME SAL COMM TOTAL-------------- -------------- -------------- ------------WARD 1250 500 15500MARTIN 1250 1400 16400TURNER 1500 0 18000JONES 2975 300 19500SCOTT 3150ALLEN 1600
6 rows selected.
SQL> SELECT ename, sal, sal / 22, ROUND (sal / 22, 0)
2 FROM emp
3 WHERE ename IN (‘ALLEN’, ‘JONES’);
ENAME SAL SAL / 22 ROUND(SAL / 22, 0)-------------- -------------- --------------- ----------------------------ALLEN 1600 72.7272727 73JONES 2975 135.227273 135
2 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 30
VISTAZO A LOS VALORES Y FUNCIONES DE FECHA
REFERENCIA DE LOS VALORES Y FUNCIONES DE FECHA
Use operaciones aritméticas simples en sentencias SQL para efectuar cálculos sobre fechas en las bases
de datos.
Fecha + número Suma un número de días a una fecha, produciendo otra fecha.
Fecha – número Resta un número de días a una fecha, produciendo otra fecha.
Fecha – Fecha Resta una fecha de otra, produciendo el número de días entre las dos.
Ejemplo:
Mostrar el nombre, fecha de contratación y fecha de vencimiento del período de prueba (90 días) de los
empleados en el departamento 10: LAB27
FUNCIÓN EJEMPLO RESULTADO
MOD MOD (7, 5) El residuo de dividir 7 entre 5
SQRT SQRT (25) La raíz cuadrada de 25
ROUND ROUND (SAL, 2)El salario, redondeado a dos
dígitos después del puntodecimal.
TRUNC TRUNC (SAL, 2)El salario, truncado a dos dígitos
después del punto decimal.
POWER POWER (SAL, 3)El salario, elevado a la tercera
potencia.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 31
MONTHS_BETWEEN MONTHS_BETWEEN(SYSDATE, HIREDATE)
Devuelve la cantidad de mesestranscurridos entre HIREDATE y la
fecha actual.
GREATEST(FECHA1,FECHA2...)
GREATEST(HIREDATE,SYSDATE)
Devuelve la fecha más tardía de unalista de fechas
FUNCIÓN EJEMPLO RESULTADO
ADD_MONTHS
ADD_MONTHS (HIREDATE, 6)
ADD_MONTHS (HIREDATE, -6)
Devuelve la fecha 6 meses después deHIREDATE
Devuelve la fecha 6 meses antes deHIREDATE
LAST_DAY LAST_DAY (HIREDATE) Devuelve el último día del mes quecontiene HIREDATE
NEXT_DAYNEXT_DAY (HIREDATE,
‘FRIDAY’)Devuelve la fecha del siguienteviernes después de HIREDATE
SYSDATE SYSDATE Devuelve la fecha y hora actual
SQL> SELECT ename, hiredate, hiredate + 90 “$REVIEW$”
2 FROM emp
3 WHERE deptno = 10;
ENAME HIREDATE $REVIEW$-------------- --------------- ---------------CLARK 09/06/1981 07/09/1981KING 17/11/1981 15/02/1982MILLER 23/01/1982 23/04/1982
3 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 32
LEAST(FECHA1,FECHA2...)
LEAST(HIREDATE,SYSDATE)
Devuelve la fecha más temprana deuna lista de fechas
Ejemplo: ADD_MONTHS
Mostrar el nombre, fecha de contratación y 6 meses después de la fecha de contratación de los
empleados del departamento 10. LAB28
Ejemplo: SYSDATE
Mostrar el nombre y las semanas de trabajo de los empleados del departamento 20. LAB29
SQL> SELECT ename, hiredate, ADD_MONTHS (hiredate, 6) “$REVIEW$”
2 FROM emp
3 WHERE deptno = 10;
ENAME HIREDATE $REVIEW$-------------- --------------- ---------------CLARK 09/06/1981 09/12/1981KING 17/11/1981 17/05/1982MILLER 23/01/1982 23/07/1982
3 rows selected.
SQL> SELECT ename, ROUND((SYSDATE - hiredate) / 7,0) “WEEKS”
2 FROM emp
3 WHERE deptno = 10;
ENAME WEEKS-------------- ---------------CLARK 1098KING 1075MILLER 1066
3 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 33
FORMATEO DE FECHAS
ELEMENTO DESCRIPCIÓN
DD Día del mes (Ej. 1-31)
DY Día de la semana (Ej. FRI)
DAY Nombre del día, en mayúscula, con blancos adicionales hasta completar 9caracteres (Ej. MONDAY)
DDSPTH Nombre del día, en mayúscula, deletreado (Ej. TWELFTH)
MM Mes (Ej. 01 – 12)
MON Nombre del mes, en mayúsculas, abreviado a 3 letras (Ej. JAN)
MONTH Nombre del mes, en mayúsculas, con blancos adicionales hasta completar 9caracteres (Ej. JANUARY)
YY Año con dos dígitos (Ej. 91)
YYYY Año con cuatro dígitos (Ej. 1991)
HH:MI:SS Horas : Minutos : Segundos (Ej. 09:00:00)
fm Modo de relleno: lo que siga a este prefijo reemplaza los blancos adicionales,generando un resultado de tamaño variable
Formatee la visualización de valores de fecha con la función de conversión TO_CHAR
Ejemplo:
Muestre las fechas de contratación de todos los empleados del departamento 20 con el formato DD of
Month YYYY: LAB30
SQL> SELECT TO_CHAR (hiredate, ‘fmDD “ of “ MONTH YYYY’) “Date of Hire”
2 FROM emp
3 WHERE deptno = 20;
Date of Hire------------------------------17 of DECEMBER 198012 of JANUARY 19833 of DECEMBER 1981
3 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 34
REFERENCIA DE LOS VALORES Y FUNCIONES DE CARACTERES
Combine columnas con caracteres y constantes de carácter para producir una salida más significativa
que una simple columna estándar devuelta por SQL-Plus.
Ejemplo: concatenación
Muestre la combinación de departamento más su localización bajo un solo título DEPARTAMENTOS:
LAB31
SQL> SELECT dname || ‘ – ‘ || loc “DEPARTAMENTOS”
2 FROM dept;
DEPARTAMENTOS----------------------------------------ACCOUNTING – NEW YORKRESEARCH – DALLASSALES – CHICAGOOPERATIONS – BOSTONFINANCE – LOS ANGELESRESEARCH – BOSTONPERSONNEL – TAHITIEDUCATION - MAUI
8 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 35
FUNCIÓN EJEMPLO RESULTADO
INITCAP INITCAP (ENAME)Devuelve el nombre con su
primera letra en mayúscula, y lasdemás en minúscula (Ej. Smith)
UPPER UPPER (ENAME)Devuelve todos los caracteres de
la cadena en mayúscula.(Ej.SMITH)
LOWER LOWER (ENAME)Devuelve todos los caracteres de
la cadena en minúscula (Ej.smith)
SUBSTR SUBSTR (JOB, 1, 5)Empezando en la posición 1,
devuelve 5 caracteres (Ej.MANAG)
LENGTH LENGTH (ENAME)Devuelve el número de
caracteres de una cadena (Ej. 5)
CONCATCONCAT(ENAME,JOB)
Concatena dos cadenas
LPAD LPAD(sal,5,'*')
muestra un número decaracteres a la izquierda del
valor de la columna*****5000
TRIM TRIM('S' FROM 'SSMITH')Borra un carácter de una cadena
Ej. MITH
INSTR INSTR('String', 'r')Devuelve la posición de un
carácter dentro de una cadenaEj: 3
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 36
Ejemplo: INITCAP – UPPER
Muestre el nombre y el cargo de todos los empleados llamados Ward en mayúscula inicial bajo el título
NAME: LAB32
Ejemplo: SUBSTR
Mostrar las primeras 5 letras del nombre de departamento y su localización completa. LAB33
Formatee la visualización de valores numéricos con la función de conversión TO_CHAR
SQL> SELECT INITCAP (ename) NAME, job
3 FROM emp
4 WHERE UPPER (ename) = ‘WARD’;
NAME JOB---------- ----------------Ward SALESMAN
1 row selected.
SQL> SELECT SUBSTR (dname, 1, 5) dept, loc
2 FROM dept;
DEPT LOC------------ ---------------------ACCOU NEW YORKRESEA DALLASSALES CHICAGOOPERA BOSTONFINAN LOS ANGELESRESEA BOSTONPERSO TAHITIEDUCA MAUI
8 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 37
Ejemplo:
Muestre la comisión de cada empleado del departamento 30 usando signos de peso, comas en las
posiciones adecuadas y dos dígitos decimales. LAB34
VISIÓN GENERAL DE ALGUNAS FUNCIONES ADICIONALES
FUNCIÓN EJEMPLO RESULTADO
GREATEST GREATEST (SAL, COMM)Devuelve el mayor valor entre
SAL y COMM
LEAST LEAST (SAL, COMM)Devuelve el menor valor entre
SAL y COMM
SQL> SELECT ename EMPLOYEE, TO_CHAR (comm, ‘$9,990.99’) COMMISION
2 FROM EMP;
EMPLOYEE COMMISION----------------- ---------------------ALLEN $300.00WARD $500.00JONESMARTIN $1,400.00BLAKESCOTTTURNER $0.00JAMES
8 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 38
REFERENCIA DE ALGUNAS FUNCIONES ADICIONALES
Ejemplo: GREATEST
Muestre el nombre, el salario mensual y la comisión de todos los vendedores, incluyendo una columna
que muestre el mayor valor ya sea de salario o comisión. LAB35
LABORATORIO 2-1
Utilice para estos ejercicios la estructura de la tabla EMP del usuario SCOTT. Etiquete con un nombre
las columnas de cálculos
1. Para cada empleado, calcule el número de meses entre hoy y la fecha en que el empleado fué
contratado. Ordene su resultado por el número de meses que ha estado empleado. Redondee el
número de meses al número entero más próximo.
2. Muestre el nombre de empleado, la compensación total y la fecha de contratación de todos los
empleados cuyo salario sea mayor que dos veces su comisión. Formatee la fecha de
contratación para que luzca como: 3rd December, 1982.
3. Muestre el nombre de empleado en minúsculas y el salario redondeado en cientos para todos los
empleados.
SQL> SELECT ename, sal, NVL (comm, 0), GREATEST (sal, comm)
2 FROM emp
3 WHERE job = ‘SALESMAN’;
ENAME SAL NVL (comm, 0) GREATEST (sal, comm)----------------- ----------------- -------------------- -------------------------------ALLEN 1600 300 1600WARD 1250 500 1250JONES 2975 0 2975MARTIN 1250 1400 1400SCOTT 3150 0 3150TURNER 1500 0 1500
6 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 39
4. Muestre el numero de empleado, el nombre, el salario, el 15% del salario y el salario
incrementado en un 15% de todos los empleados
5. Seleccionar numero, nombre, cargo y fecha de ingreso y tiempo en la empresa en años de todos
los empleados de la tabla EMP
6. Seleccionar todos los empleados cuyo tiempo de servicio este entre 5 a 11 años.
7. Seleccionar todos los empleados cuyo nombre contengan la cadena ‘LER’ , ‘NES’ o ‘FOR’
8. Seleccionar la columna, salario, comisión y calcular la columna total devengado de todos los
empleados
9. Seleccionar la columna salario y calcular la columna retención en la fuente cuyo porcentaje es
el 4% del salario, y calcular el total a pagar para cada empleado. Seleccionar todos los
empleados cuyo salario incrementado en un 10% sea mayor que 5.000.
10. Muestre el nombre y la fecha de ingreso del empleado en el formato "Lunes, 3 de Junio 2001"
11. Para cada empleado muestre el nombre y calcule el numero de meses entre hoy y la fecha en
que ingreso en el empleado, etiquete la columna con la frase "MESES TRABAJADOS".
Ordene el resultado por el número de meses.
12. Escribe la consulta que produce lo siguiente para cada empleado:
ENAME GANA SAL PERO QUIERE Sal*3, ALLEN GANA 1600 PERO QUIERE 4800
formatee la columna resultante con la frase salarios Soñados.
13. Escriba una consulta que muestre el nombre del empleado con la primera letra en mayúscula y
las demás en minúscula, y la longitud del nombre del empleado.
14. Muestre el nombre, la fecha de ingreso y el día de la semana en la cual ingresaron los
empleados a la empresa, etiquete la columna con la palabra DIA.
15. Crear una consulta que muestre el nombre del empleado y el valor de la comisión. Si la
comisión del empleado es nula coloque en los registros visualizados la frase 'NO COMISION'
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 40
VISTAZO A LAS FUNCIONES DE GRUPO
Para devolver resultados basados en grupos de filas en lugar de un resultado por fila, utilice las
funciones de grupo.
FUNCIONES EJEMPLO RESULTADO
AVG AVG (sal)Devuelve el promedio aritmético
de SAL.
COUNT (comm) Devuelve el número de valores
no nulos de la columna COMM.COUNT
COUNT (*)Devuelve el número de filas no
nulas en un grupo.
MAX MAX (sal0029Devuelve el valor máximo de
SAL
MIN MIN (sal)Devuelve el valor mínimo de
SAL
SUM SUM (comm)Devuelve la suma de los valores
de COMM
Ejemplo: AVG, MAX, SUM
Muestre el promedio, el valor máximo y la suma de los salarios anuales de todos los vendedores.
LAB36
SQL> SELECT AVG (sal), MAX (sal), SUM (sal)
2 FROM emp
3 WHERE job = ‘SALESMAN’;
AVG (SAL) MAX (SAL) SUM (SAL)----------------- ----------------- --------------------
1954.16667 3150 11725
1 row selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 41
SINTAXIS DE LA SENTENCIA SELECT
SELECT ...FROM ...WHERE ...GROUP BY …HAVING …ORDER BY …
Ejemplo: COUNT
Muestre el número de filas en la tabla EMP, y el número de empleados con comisión no nula. LAB37
REVISANDO SALIDAS DE GRUPO.
SQL> SELECT COUNT (*) EMPLOYEES, COUNT (comm) COMMISIONABLE
2 FROM emp;
EMPLOYEES COMMISIONABLE------------------- --------------------------
14 4
1 row selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 42
Muestre una tabla en subgrupos de filas con la cláusula GROUP BY.
Ejemplo: GROUP BY – una columna
Muestre cada departamento y su número de empleados, agrupados por departamento. LAB38
Si se selecciona una columna normal y una función de grupo al mismo tiempo, se recibirá un mensaje
de error.
Ejemplo:
Mostrar el empleado que se contrató de primero. LAB39
SQL> SELECT deptno, COUNT (*)
3 FROM emp
4 GROUP BY deptno;
DEPTNO COUNT (*)------------------- --------------------------
10 320 330 8
3 rows selected.
SQL> SELECT ename, MIN (hiredate)
2 FROM emp;
ORA-00937: Not a single-group group function.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 43
Ejemplo: GROUP BY – más de una columna
Muestre la cantidad de empleados para cada categoría de cargos en cada departamento. LAB40
Coloque una condición a la función de grupo mediante la cláusula HAVING.
Ejemplo:
Muestre el salario anual promedio para todos los tipos de cargo con más de dos empleados. LAB41
SQL> SELECT deptno, job, COUNT (*)
2 FROM emp
3 GROUP BY deptno, job;
DEPTNO JOB COUNT (*)------------- ------------------- ---------------
10 CLERK 110 MANAGER 110 PRESIDENT 120 ANALYST 120 CLERK 230 CLERK 130 MANAGER 130 SALESMAN 6
8 rows selected.
SQL> SELECT job, 12 * AVG (sal)
2 FROM emp
3 GROUP BY job
4 HAVING COUNT (*) > 2;
JOB 12 * AVG (SAL)------------------- ----------------------CLERK 12450SALESMAN 23450
2 rows selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 44
La cláusula HAVING puede ser usada en la misma consulta con una cláusula WHERE.
Ejemplo:
Muestre los departamentos y la nómina de aquellos departamentos cuya nómina exceda de $8.000,
excluyendo todo el personal Aseador. Ordene la lista por el monto de la nómina. LAB42
SQL> SELECT deptno, SUM (sal)
2 FROM emp
3 WHERE job != ‘CLERK’
4 GROUP BY deptno
5 HAVING SUM (sal) > 8000
6 ORDER BY SUM (sal);
DEPTNO SUM (SAL)------------------- ----------------------
30 14575
1 row selected.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 45
LABORATORIO 2-2
Retorno de filas basadas en grupos de filas
Use las tablas EMP para completar los siguientes ejercicios:
1. Cuente todos los registros que no tienen el campo Job nulo.2. Seleccionar el salario mínimo y máximo de la tabla Emp3. Seleccionar la fecha de ingreso mínima y máxima de la tabla empleados.4. Seleccionar el numero de empleados por cada Departamento5. Seleccionar de cada departamento la suma de los salarios, y el No. De Empleados6. Contar el numero de empleados del mismo cargo por departamento7. Contar el numero de 'Analistas y Gerentes de Cada Departamento8. Seleccione el código del empleado, nombre y cargo de la tabla EMP donde el salario se encuentre
entre 1000 y 2000 y sean del Departamento No. 10, ordenado por nombre del empleado.9. Seleccione de la tabla EMP todos los empleados diferentes de 'PRESIDENT' que ganen mas de
1.500 cuyo y ordenando alfabéticamente por nombre del empleado y descendente por sueldo10. Muestre solo los nombres de los empleados de cargos 'MANAGER' que ganan mas de 2.600
ordenar alfabéticamente (tabla EMP).11. Seleccione información acerca de los 'MANAGER' y los 'PRESIDENT' de la tabla EMP. Ejecuta
esta consulta ordenando por fecha de ingreso (el mas antiguo) y el No. Departamento.12. Computar el promedio de los salarios, el valor máximo y mínimo agrupados por departamentos
donde el cargo sea vendedor.13. Sumar las comisiones de los empleados por departamento y seleccionar el departamento con la
sumatoria mayor que cero ordenando descendentemente.
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 46
OBTENIENDO DATOS DE MULTIPLES TABLAS.
Es la unión de dos tablas a través de los campos que la relacionan, llave primaria llave foránearespectivamente
JOIN: use un JOIN en una consulta de datos de más de una tabla
CARACTERÍSTICAS DEL JOIN
• Las tablas a ser unidad (JOINED) se especifican en la cláusula FROM• En la cláusula WHERE se especifica como unir las tablas.• Las columnas que tienen nombres iguales en las tablas especificadas en la cláusula FROM
deben ser identificadas utilizando NOMBRE_TABLA.NOMBRE_COLUMNA• Si las columnas no tienen homónimos en las tablas, no es necesario especificar el nombre de la
tabla en las cláusulas WHERE y SELECT.• Se pueden unir JOIN cuantas tablas se requieran• El criterio de coincidencia entre las tablas es denominado el predicado del JOIN o criterio del
EMPNO DEPTNO LOC----- ------- -------- 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO
EMP DEPT
EMPNO ENAME DEPTNO------ ----- ... ------ 7839 KING ... 10 7698 BLAKE... 30 7934 MILLER ... 10
DEPTNO DNAME LOC------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SELECT table1.column, table2.columnFROMtable1, table2WHERE table1.column1 = table2.column2;
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 47
JOIN• Las columnas especificadas en la condición JOIN deben estar anexadas.• Cuando n tablas son unidas, es necesario tener al menos n-1 condiciones de JOIN entre parejas
de tablas para evitar el producto cartesiano (una unión de cuatro tablas requiere especificarcriterio de JOIN para tres parejas de tablas)
TIPOS DE JOIN
EQUIJOIN
Equijoin Non-equijoin Outer join Self join
EMP DEPT
EMPNO ENAME DEPTNO------ ------- ------- 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20...14 rows selected.
DEPTNO DNAME LOC------- ---------- -------- 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCH DALLAS 20 RESEARCH DALLAS...
Foreign key Primary key
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 48
Ejemplo: Seleccionar número del empleado, nombre del departamento y localización. LAB 43
OUTER JOIN
Operador OUTERJOIN es el signo más entre paréntesis (+), este obliga a que un registro que contengavalores nulos en uno de los criterios del JOIN coincida con cada valor de la segunda tabla sobre la cualordinariamente no estaría esa coincidencia.
Sintaxis
Ejemplo: Lista la localización de todos los departamentos así como los empleados que trabajan en estosdepartamentos, incluya los departamentos en los cuales no trabaja empleado alguno. LAB44
OUTER JOINS Y LA CLAUSULA WHERE
Ejemplo: Liste los departamentos que actualmente no tienen empleados asignados. LAB45
SELECT table1.column, table2.columnFROMtable1, table2WHERE table1.column1(+) = table2.column2;
SQL> SELECT loc, ename2 FROM emp, dept3 WHERE emp.deptno(+) = dept.deptno;
SQL> SELECT empno, dept.deptno, loc2 FROM emp, dept3 WHERE dept.deptno= emp.deptno(+) and empno is null;
SQL> select ename, emp.deptno, loc2 From emp, dept3 Where emp.deptno = dept.deptno;
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 49
Ejemplo: Liste el nombre y la localización del departamento de todos los CLERK, incluya una listade todos los departamentos que actualmente no tengan ningún CLERK asignado. LAB46
Características del OUTER JOIN
• Si un registro de una de las tablas de un JOIN no satisface la condición del JOIN, normalmente eseregistro no aparece en el resultado. El OUTER JOIN, permite que un registro con estascaracterísticas aparezcan en el resultado.
• el OUTER JOIN a través SQL proporciona registros temporales para seleccionar los datos, losdatos de la tablas.
• Se coloca un (+) después de la combinación de tabla/columna (en la cláusula WHERE) sobre laque se necesita los registros falsos para realizar el OUTER JOIN (WHEREEMP.DEPTNO(+)=DEPT.DEPTNO).
• Si se requiere múltiples columnas para dar la condición JOIN, todas o ninguna de ellas puedellevar el (+).
• Solo una de las tablas en un JOIN, pueden escogerse para realizar el OUTER JOIN, es decir no sepuede colocar (+) en ambas tablas.
• Una tabla puede realizar un OUTER JOIN con tan solo otra tabla.• Los registros extras seleccionados en un OUTER JOIN pueden realizarse buscando aquellos que
tengan valores nulos (NULL) en las columnas del JOIN.
SEF-JOINS
Un SEF-JOINS es utilizado para buscar coincidencia entre registros sobre columnas diferentes de lamisma tabla.
Sintaxis
SQL> SELECT ename, dept.deptno, loc2 FROM emp, dept3 WHERE dept.deptno= emp.deptno(+) and (job='CLERK' OR empno is null);
SELECT alias1.column, alias2.columnFROMtable1 alias1, table2 alias 2WHERE alias1.column1 = alias2.column2;
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 50
Ejemplo: Liste el número, nombre y cargo de cada empleado, así como el numero nombre y cargo del jefecorrespondiente. LAB47
Características del SELF
• Una palabra puede ser unida JOINED a si mismo como si fueran dos tablas separadas.• El self-join es útil para unir un registro de una tala a otra de la misma tabla.• Como cualquier otro tipo de JOIN, este se realiza con columnas que contenga el mismo tipo de
información.• A la tabla se le deben dar alias para indicar que columnas son de la tabla.• En el ejemplo previo, la tabla EMP fue unida a si misma utilizando las columnas EMPNO y MGR
cumpliendo todas las reglas anteriores.
NON-EQUIJOINS
La mayoría de los JOINS se basan en la igualdad de valores en las tablas referenciadas. Por esto sedeterminan los EQUIJOINS. Los NON-EQUIJOINS por consiguiente se basan en los siguientesoperadores.
!= < <= > >= between
Ejemplo: Ordene el grado del salario (salgrade), el nombre, salario y cargo de cada empleado. Ordene elresultado por grado de salario y trabajo. LAB48
SELECT w.empno, w.ename, w.job,w.mgr,m.empno,m.ename,m.jobFROMemp w, emp m WHERE w.mgr = m.empno;
SQL> SELECT GRADE, ENAME, SAL, JOB2 FROM EMP, SALGRADE3 WHERE SAL BETWEEN LOSAL AND HISAL4 ORDER BY GRADE, SAL;
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 51
OPERADORES DE CONJUNTO
Los operadores de conjunto combinan dos o más tablas para obtener resultado.
UNION Los registros del primer query mas registros del segundoquery se eliminan duplicados
INTERSECCIÓN Los registros que las dos tablas tienen en comúnMENOS Registros únicos de la primera tabla
EL OPERADOR UNION
El operador UNION retorna todos los registros distintos seleccionados través de múltiples queries.
Sintaxis
Ejemplo: Quien gana más de $2000 en todos los departamentos.
SELECT columnaFROMtablaWHERE condiciones ciertasUNIONSELECT columnaFROMtablaWHERE condiciones ciertas
SQL> SELECT ENAME,SAL2 FROM ACCOUNTING3 WHERE SAL >20004 UNION5 SELECT ENAME,SAL6 FROM RESEARCH7 WHERE SAL >20008 UNION9 SELECT ENAME,SAL10 FROM SALES11 WHERE SAL >2000
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 52
EL OPERADOR INTERSECCIÓN (INTERSECT)
El operador INTERSECT retorna valores comunes de múltiples tablas
Ejemplo
EL OPERADOR MENOS (MINUS)
El operador MINUS. Retorna todos los registros seleccionados por el primer query que no fueron tambiénseleccionados por el segundo query
Ejemplo: Liste la información de los empleados que tienen cargos que están en el departamento deACCOUNTING pero que no estén en el departamento de ventas (SALES).
SELECT columnaFROMtablaWHERE condiciones ciertasINTERSECTSELECT columnaFROMtablaWHERE condiciones ciertas
SELECT columnaFROM tablaWHERE condiciones ciertasMINUSSELECT columnaFROMtablaWHERE condiciones ciertas
SQL> SELECT JOB2 FROM ACCOUNTING3 UNION4 SELECT JOB5 FROM RESEARCH6 UNION7 SELECT JOB8 FROM SALES;
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 53
Nota: Los operadores de conjunto son útiles para conformar subqueries que referencia a múltiples tablas.
LABORATORIO 3-1
1) Escriba una consulta que muestre el nombre del empleado y el numero y nombre del departamentopara todos los empleados.
ENAME DEPTNO DNAMEKING 10 ACCOUNTINGBLAKE 30 SALESCLARK 10 ACCOUNTINGJONES 20 RESEARCHMARTIN 30 SALES
2) Crear un listado de todos los cargos cuya localización departamento es Chigaco
JOB LOCCLERK CHICAGOMANARGER CHICAGOSALESMAN CHICAGO
3. Escribir una consulta que muestre el nombre del empleado, el nombre del departamento y lalocalización de todos los empleados que ganan una comisión
ENAME DNAME LOCALLEN SALES CHICAGOWARD SALES CHICAGOMARTIN SALES CHICAGO
4. Mostar el nombre del empleado y el nombre del departamento a los que pertenecen esos empleados.Salvar la sentencia en un archivo llamado p4q4.sql
ENAME DNAMEALLEN SALESWARD SALESMARTIN SALES
SQL> SELECT * FROM ACCOUNTING2 WHERE JOB (SELECT JOB FROM ACCOUNTING3 MINUS SELECT JOB FROM SALES)
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 54
5. Escribir una consulta que muestre el nombre, el cargo, el numero del departamento y el nombre detodos los empleados que trabajan en DALLAS.
ENAME JOB DEPTNO DNAMESMITH CLERK 20 RESEARCHADAMS CLERK 20 RESEARCHFORD ANALYST 20 RESEARCH
6. Mostrar el nombre y el numero del empleado al igual que el nombre y el numero de su respectivojefe. Salvar la sentencia SQL en el archivo p4q6.sql
ENAME EMPNO EMGR MGRSCOTT 7788 JONES 7566FORD 7902 JONES 7566ALLEN 7499 BLAKE 7698JAMES 7900 BLAKE 7698
7. Modificar el punto anterior incluyendo los empleados que no tienen jefe
ENAME EMPNO EMGR MGRSCOTT 7788 JONES 7566FORD 7902 JONES 7566ALLEN 7499 BLAKE 7698JAMES 7900 BLAKE 7698KING 7839
8. Crear un query que muestre el nombre del empleado, el numero del departamento y todos losempleados que trabajan en el mismo departamento como un empleado determinadoDEPTNO EMPLOYEE COLLEAGUE10 CLARK KING10 CLARK MILLER10 KING CLARK10 MILLER CLARK10 MILLER KING.....56 ROWS SELECT
9. Mirar la estructura de la tabla SALGRADE. Cree una consulta que muestre el nombre, cargo,nombre del departamento, salario y grado de todos los empleados.
ENAME JOB DNAME SAL GRADEMILLER CLERK ACCOUNTING 1300 2CLARK MANAGER ACCOUNTING 2450 4KING PRESIDENT ACCOUNTING 5000 5
Introducción a Oracle – SQL-Plus
Acceso y Visualización de Datos 55
SMITH CLERK RESEARCH 800 1
10. Cree una consulta que muestre el nombre del empleado y la fecha de ingreso de los empleados queingresaron después que BLAKE
ENAME HIREDATEKING 17-NOV-81CLARK 09-JUN-81MARTIN 28-SEP-81TURNER 08-SEP-81
11. Mostrar los nombres de los empleados, la fecha de ingreso, el nombre de su jefe y la fecha deingreso de su jefe. Etiquete los nombres de las columnas respectivamente.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 56
VISIÓN GENERAL DE LA CREACIÓN DE TABLAS
El nombre de una tabla debe seguir las reglas estándar para los nombres de cualquier objeto de una
base de datos ORACLE.
Requerimientos de la sentencia CREATE TABLE
q El nombre de la tabla.
q El nombre de la columna.
q El tipo de datos de la columna.
Reglas para los nombres
q Debe tener entre 1 y 30 caracteres y el primer carácter debe ser alfabético.
q Debe contener solo los caracteres:
o A-Z, a-z, 0-9, _ (guión bajo)
o $ y #. Son legales, pero su uso se está descontinuando.
q No debe ser ninguna palabra reservada de ORACLE.
q No Debe tener el mismo nombre que otro objeto dentro del mismo nombre de usuario de
ORACLE.
Nota rápida:
q Los nombres de tablas son los mismos si se escriben en mayúsculas o minúsculas; ejemplo:
EMP = Emp. Los nombres de columnas deben adherirse a las tres primeras reglas antes
descritas.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 57
CREACIÓN DE TABLAS
Tipo de datos DescripciónCHAR(tamaño) Almacena datos de tipo carácter de longitud fija. Con un tamaño máximo
de 2000 bytesVARCHAR2(Tamaño) Almacena datos de tipo carácter de longitud variable, con un tamaño
máximo de 4.000VARCHAR(Tamaño) Actualmente es igual al CharLONG Almacena datos de tipo carácter de longitud variable hasta 2 gigabytesNUMBER(L,D) Almacena datos numéricos siendo L la longitud y D el numero de dígitos
decimalesINTEGER Numérico sin decimal. Por defecto 38.DATE Almacena información relacionada con el tiempo incluyendo fechas,
horas, minutos y segundos. (Fechas hasta el 31 de diciembre de 4712)RAW Dato en binario puro (imágenes y sonido) con un ancho máximo de 2000
bytes.LONG RAW Dato en binario puro (imágenes y sonido) mas grande que el RAWCLOB Almacena objetos de caracteres grandes de un único byteBLOB Almacena objetos binarios de gran tamaño, siendo el tamaño máximo 4GB
(gigabytes).BFILE Almacena punteros de archivo a LOB administrados por sistemas de
archivos externos a la base de datos
La Versión 8 de ORACLE ofrece un conjunto mejorado de tipos de datos para manejar objetos de gran
tamaño, así como un mecanismo más potente para almacenar datos números. Los objetos de gran
tamaño se agrupan y referencian como LOBs (Large Objects).
RESTRICCIONES
Restricciones UsoPRIMARY KEY Identifica de manera única una fila de la tabla.
CHECK Especifica una condición que debe ser verdadera.
FOREIGN KEY (nombre_columna)
REFERENCES (nombre_columna)
Establece e impone una relación entre esta columna y una
PRIMARY KEY en la tabla referenciada.
[NOT] NULL Especifica que dicha columna no puede contener valores nulos.
UNIQUE Especifica una columna o columnas que deben tener valores
únicos a lo largo de todas las filas.
Las restricciones de integridad permiten al diseñador de la aplicación restringir el rango de valores
aceptables que se pueden almacenar en una tabla.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 58
Sintaxis abreviada
Ejemplo: LAB49
Podemos crear tablas usando la cláusula AS en la sentencia CREATE TABLE para usar los atributos
de columnas y los datos de una tabla ya existente.
Ejemplo: LAB50
CREATE TABLE [user.] nombre_tabla ( { nombre_columna tipo | restricción }
[ , { nombre_columna tipo | restricción } ] ... )
SQL> CREATE TABLE emp2 2 (empno NUMBER(4), 3 CONSTRAINT empno_constr PRIMARY KEY(empno), 4 ename CHAR(10) CHECK (ename = upper(ename)), 5 job CHAR(9), 6 mgr NUMBER(4) REFERENCES emp(empno), 7 hiredate DATE, 8 sal NUMBER(7,2), 9 comm NUMBER(7,2), 10 deptno NUMBER(2) NOT NULL REFERENCES dept(deptno));
Table created.
CREATE TABLE [user.] nombre_tabla [ ( nombre_columna1, nombre_columna2, …) ]
AS consulta
SQL> CREATE TABLE hdates2 AS SELECT empno, ename, hiredate3 FROM emp4 WHERE empno = 10;
Table created.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 59
Ejemplo: LAB51
||||||||||||||
vcb
MODIFICACIÓN DE TABLAS
Use la sentencia ALTER TABLE para modificar la estructura de una tabla. Se pueden hacer dos
operaciones:
q Adicionar columnas o restricciones
q Modificar la definición de las columnas (restricciones, NOT NULL y demás)
Sintaxis – Adicionar
Ejemplo: LAB52
Sintaxis – Modificar
SQL> CREATE TABLE empty_like_emp5 AS SELECT *6 FROM emp7 WHERE 1 = 2;
Table created.
ALTER TABLE nombre_tablaADD ( { nombre_columna tipo | restricción } [, { nombre_columna tipo | restricción } ] ... )
SQL> ALTER TABLE hdates 2 ADD (manager NUMBER(4) );
Table altered.
ALTER TABLE nombre_tablaMODIFY ( { nombre_columna tipo | restricción } [, { nombre_columna tipo | restricción } ] ... )
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 60
Ejemplo: Incrementar el tamaño de la columna ename de 10 a 18. LAB53
Sintaxis para Borrar la Columna de una Tabla
Use la cláusula Drop Column cuando desea borrar una columna de una tabla
Ejemplo: Borrar la columna ename de la tabla HDATES. LAB54
alter table empty_like_emp add constraint FK_MAU foreign key (deptno) references
dept(deptno);
Sintaxis – Eliminación de una tabla: DROP TABLE
Ejemplo: Eliminar la tabla EMPTY_LIKE_EMP. LAB55
Siga los siguientes pasos para eliminar una columna de una tabla:
SQL> ALTER TABLE hdates 2 MODIFY (ename CHAR(18) );
Table altered.
DROP TABLE nombre_tabla
SQL> DROP TABLE empty_like_emp;
Table dropped.
ALTER TABLE nombre_tablaDROP COLUMN ( nombre_columna, nombre_columna ... )
SQL> ALTER TABLE HDATES 2 DROP COLUMN ename ;Table altered.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 61
Crear un duplicado de la tabla original, sin la columna o columnas que queremos eliminar:
Borrar la tabla original
Renombrar la nueva tabla con el nombre de la tabla original (este paso es opcional)
SQL> CREATE TABLE tabla_temp2 AS SELECT columna1, columna2, columna4, columna53 FROM tabla_original;
Table created.
SQL> DROP TABLE tabla_original;
Table dropped.
SQL> RENAME tabla_temp TO tabla_original;
Table renamed.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 62
VISIÓN GENERAL DE LAS VISTAS
Se puede representar lógicamente una o más tablas como una vista. Una vista no contiene datos. Todos
los datos se derivan de las tablas subyacentes.
Ventajas de las vistas
q Seguridad
q Conveniencia
q Perspectiva
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- --------- ----- --------- ----- ----- -------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EEMMPPTTaabblleeEMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------- --------- ---- --------- ------ ----- -------
7839 KING PRESIDENT 17-NOV-81 5000 10
7782 CLARK MANAGER 7839 09-JUN-81 1500 300 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
EMPNO ENAME JOB------ -------- ----------- 7839 KING PRESIDENT 7782 CLARK MANAGER 7934 MILLER CLERK
EEMMPPVVUU1100VViieeww
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 63
CREACIÓN DE VISTAS
La creación de vistas es similar a la creación de tablas usando la cláusula AS.
Sintaxis
La consulta contiene un sentencia Select sin la cláusula order by
La cláusula WHIT CHECK OPTION se utiliza para al ingresar registros en una tabla se valide lacláusula where.
La cláusula WITH READ ONLY se utiliza cuando se requiere crear una vista de solo lectura, es decirno es permitido insertar, borrar o actualizar datos sobre ella.
Ejemplo:
Crear una vista que contenga el número, el nombre y el cargo de todos los empleados del departamento
10: LAB56
CREATE [OR REPLACE] VIEW nombre_vista [ (alias1, alias2, ... ) ]
AS consulta
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY];
SQL> CREATE VIEW empvu10
2 AS SELECT empno, ename, job
3 FROM emp
4 WHERE deptno = 10;
View created.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 64
Sintaxis – Eliminación de vistas: DROP VIEW
Ejemplo:
Ejemplo: alias de columnas en las vistas. LAB57
SQL> SELECT * FROM empvu10;
EMPNO ENAME JOB------------- ------------- ----------------
7782 CLARK MANAGER7839 KING PRESIDENT7934 MILLER CLERK
3 rows selected.
DROP VIEW nombre_vista
SQL> DROP VIEW empvu10;
View dropped.
SQL> CREATE VIEW empvu11 (id_number, employee, title)2 AS SELECT empno, ename, job3 FROM emp4 WHERE deptno = 10;
View created.
SQL> SELECT * FROM empvu11;
ID_NUMBER EMPLOYEE TITLE----------------- ---------------- ----------------
7782 CLARK MANAGER7839 KING PRESIDENT7934 MILLER CLERK
3 rows selected.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 65
Ejemplo: alias alternativos en las vistas. LAB58
Asegure que las inserciones y actualizaciones realizadas sobre una vista, no afectarán los datos que la
vista no sea capaz de acceder, mediante la utilización de la cláusula WITH CHECK OPTION.
Sintaxis
Ejemplo:
Ejemplo: Cree una vista solo con los datos de los empleados del departamento No. 20. LAB59
Ejemplo: Actualizando el departamento a 30 para el código 7369. LAB60
CREATE VIEW nombre_vista [ (alias 1, alias2, … ) ]AS consulta[ WITH CHECK OPTION [ CONSTRAINT restricción ] ]
SQL> CREATE VIEW empvu202 AS SELECT * FROM emp3 WHERE deptno = 204 WITH CHECK OPTION;
View created.
SQL> CREATE VIEW salvu102 AS SELECT empno, ename, sal * 12 annual_salary3 FROM emp4 WHERE deptno = 10;
View created.
SQL> SELECT * FROM salvu10;
EMP_NO ENAME ANNUAL_SALARY-------------- ------------- --------------------------
7782 CLARK 294007839 KING 600007934 MILLER 15600
3 rows selected.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 66
VISTAZO AL ACCESO DE TABLAS Y VISTAS
El privilegio de DBA (DataBase Administrator – Administrador de la base de datos) permite al
desarrollador saltarse muchos privilegios estándar que normalmente se requieren para usar objetos de la
base de datos.
Privilegios del sistema
DBA – todos los privilegios
q SELECT
q CREATE
q DROP
q GRANT / REVOKE
q Ejecutar imports y exports sobre la base de datos completa
q Modificar la estructura de la base de datos
q RESOURCE
q CONNECT
SQL> UPDATE empvu202 SET deptno = 303 WHERE empno = 7369;
UPDATE empvu20*
ERROR at line 1:ORA-01402: view WITH CHECK OPTION where-clause violation.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 67
UN VISTAZO A LA CONCESIÓN DE ACCESO A TABLAS Y VISTAS
Los propietarios de las tablas controlan el acceso a los objetos de su propiedad.
Privilegios de acceso a tablas
q Crear la tabla por sí mismo
q Obtener los privilegios del propietario
Como DBA o propietario de tablas, use el comando GRANT y sus opciones para otorgar distintos
niveles de acceso a los objetos de la base de datos.
Sintaxis
Ejemplos:
Otorgar todos los privilegios sobre la tabla EMP a todos los usuarios:
Otorgar privilegios de consulta (SELECT) al usuario Jones sobre la tabla DEPT:
GRANT privilegio1, privilegio2, ... [ o ALL ]ON nombre_tabla / nombre_vistaTO usuario1, usuario2, ... [ o PUBLIC ][ WITH GRANT OPTION ]
SQL> GRANT ALL2 ON emp3 TO PUBLIC;
Grant succeeded.
SQL> GRANT SELECT4 ON dept5 TO jones;
Grant succeeded.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 68
Otorgar privilegios de consulta (SELECT) al usuario Jones sobre la tabla DEPT, con la habilidad de
otorgar los mismos privilegios a otros:
Cambiar el password de acceso del usuario scott a ‘leopard’:
VISIÓN GENERAL DE LOS SINÓNIMOS
Los sinónimos se pueden crear por razones de seguridad y conveniencia.
Niveles de los sinónimos
q Público
q Privado
Ventajas de los sinónimos
q Referencia a tablas y vistas sin especificar el propietario o la base de datos.
q Flexibilidad al personalizar una convención de nombres más significativa.
SQL> GRANT SELECT2 ON dept3 TO jones4 WITH GRANT OPTION;
Grant succeeded.
SQL> GRANT CONNECT2 TO scott3 IDENTIFIED BY leopard;
Grant succeeded.
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 69
CREACIÓN DE SINÓNIMOS
Sintaxis – Sinónimo privado
Ejemplo:
Crear el sinónimo ‘dates’ para la tabla HDATES del usuario Scott. LAB61
CREATE SYNONYM nombre_sinonimoFOR nombre_tabla / nombre_vista
EMPNO ENAME HIREDATE------------- ------------- ---------------
7369 SMITH 17-DEC-807499 ALLEN 20-FEB-817521 WARD 22-FEB-817566 JONES 02-APR-817654 MARTIN 28-SEP-817698 BLAKE 31-MAY-817782 CLARK 09-JUN-817788 SCOTT 09-DEC-827839 KING 17-NOV-817844 TURNER 08-SEP-817876 ADAMS 12-JAN-837900 JAMES 03-DEC-817902 FORD 03-DEC-817934 MILLER 23-JAN-82
14 rows selected.
SQL> CREATE SYNONYM dates2 FOR scott.hdates;
Synonym created.
SQL> SELECT * FROM dates;
Introducción a Oracle – SQL-Plus
Creación de Tablas y Vistas 70
LABORATORIO 4-1
1. Crear un duplicado de la tabla EMP. Llámela EMPTEST.
2. Adicione una nueva columna llamada SEX a la tabla EMPTEST de tipo carácter y de tamaño
uno (1).
3. Los usuarios cambiaron de parecer. En lugar de almacenar en SEX los valores F o M, quieren
almacenar los valores FEMALE o MALE. Incremente el tamaño de la columna SEX.
4. Cree una vista llamada EMP_NO_MONEY, con todas las columnas de la tabla EMP, excepto
las columnas SAL y COMM.
5. Seleccione todas las columnas de la tabla del diccionario de datos USER_VIEWS.
6. Usando su vista EMP_NO_MONEY, haga una consulta que muestre todos los nombres y
fechas de contratación de los empleados.
7. Intente crear una consulta que muestre el nombre y el salario de los empleados de la vista
EMP_NO_MONEY.
¿Cuál fue el resultado obtenido? __________________________________________________
8. Cree una vista llamada EMP_DEPTNO_TEN, que incluya solo el nombre, el número de
departamento y el número de empleado de la tabla EMP. Los empleados que se pueden ver a
través de esta vista son los pertenecientes al departamento número 10.
Use los siguientes nombres de columna para su vista: NAME, DNO, ENO.
9. Cree una consulta usando su vista EMP_DEPTNO_TEN.
10. Elimine su vista EMP_DEPTNO_TEN.
Introducción a Oracle – SQL-Plus
Creación de Índices y Generadores de Secuencias 71
VISIÓN GENERAL SOBRE LOS ÍNDICES
Los índices son estructuras opcionales, asociadas con tablas, que se usan para agilizar la ejecución de
consultas y/o garantizar unicidad.
El RDBMS de ORACLE accede a los datos en las tablas de dos maneras:
q TABLE SCAN completo (acceso secuencial)
q Por INDICES (acceso indexado o selectivo)
Hay una regla general para los índices: Cree un índice cuando se prevea la extracción frecuente de
menos del 10 – 15% de las filas en una tabla grande y se hace referencia frecuente a las columnas en la
cláusula WHERE.
Características de las columnas para indexado
q Las columnas se usan frecuentemente en cláusulas WHERE
q Cada valor es único (el mejor caso)
q Amplio rango de valores
q Muchos valores nulos, o relativamente pocos valores
Una tabla puede tener cualquier cantidad de índices. Los índices aceleran la ejecución de consultas,
pero disminuyen las prestaciones en cuanto a velocidad de mantenimiento de las tablas.
Nota rápida:
q ORACLE actualiza los índices automáticamente. No tienen impacto en la sintaxis de SQL.
CREACIÓN DE ÍNDICES
Sintaxis – Índice simpleCREATE INDEX nombre_indice
ON TABLE nombre_tabla (nombre_columna [ , nombre_columna ] ... )
Introducción a Oracle – SQL-Plus
Creación de Índices y Generadores de Secuencias 72
Ejemplo:
Crear un índice simple sobre la columna ENAME en la tabla EMP: LAB62
En esta consulta se usa el índice: LAB63
En esta consulta NO se usa el índice: LAB64
En la tabla USER_INDEXES del diccionario de datos se encuentran todos los índices pertenecientes al
usuario actual: LAB65
Sintaxis – Eliminación de índices
SQL> CREATE INDEX i_emp_ename
2 ON emp (ename);
Index created.
SQL> SELECT *
2 FROM emp
3 WHERE ename = ‘SMITH’;
SQL> SELECT * FROM emp;
SQL> SELECT index_name
2 FROM user_indexes
3 WHERE table_name = ‘EMP’;
INDEX_NAME--------------------I_EMP_ENAME
1 rows selected.
DROP INDEX nombre_indice
Introducción a Oracle – SQL-Plus
Creación de Índices y Generadores de Secuencias 73
Ejemplo: LAB66
Cree índices únicos sobre una tabla para asegurar que nunca dos filas tendrán valores iguales en la
columna o columnas indexadas.
Sintaxis
Ejemplo:
Crear un índice en la columna EMPNO de la tabla EMP para incrementar el desempeño, en consultas,
así como garantizar valores únicos para los números de empleados: LAB67
Los índices concatenados crean una entrada en el índice con datos provenientes de cada una de las
columnas indicadas en la sentencia de creación del índice. Se pueden incluir hasta un máximo de 16
columnas por índice.
Ejemplo:
Crear un índice en la tabla EMP para las columnas EMPNO y ENAME: LAB68
SQL> DROP INDEX i_emp_ename;
Index dropped.
CREATE UNIQUE INDEX nombre_indice
ON nombre_tabla ( nombre_columna [ , nombre_columna ] ... )
SQL> CREATE UNIQUE INDEX i_emp_empno
2 ON emp ( empno );
Index created.
SQL> CREATE UNIQUE INDEX i_empno_ename
2 ON emp ( empno, ename );
Index created.
Introducción a Oracle – SQL-Plus
Creación de Índices y Generadores de Secuencias 74
CREACIÓN DE GENERADORES DE SECUENCIA
Cree secuenciadores con el fin de generar enteros únicos y consecutivos para múltiples usos. Los
secuenciadores se pueden utilizar para generar llaves primarias de forma automática.
Sintaxis abreviada
Ejemplos:
Cree un secuenciador simple para la columna EMPNO empezando desde el valor 8000: LAB69
Cree un secuenciador simple para la columna DEPTNO empezando desde el valor 100: LAB70
CREATE SEQUENCE nombre_secuencia[ INCREMENT BY {1 | n } ][ START WITH n ][ { MAXVALUE n | NOMAXVALUE } ][ { MINVALUE n | NOMINVALUE ]
SQL> CREATE SEQUENCE s_emp_empno START WITH 8000;
Sequence created.
SQL> CREATE SEQUENCE s_dept_deptno START WITH 100;
Sequence created.
Introducción a Oracle – SQL-Plus
Creación de Índices y Generadores de Secuencias 75
Ejemplo – Mostrar una secuencia
Mostrar el siguiente valor disponible de la secuencia S_EMP_EMPNO: LAB71
Eliminar un secuenciador de la base de datos con DROP SEQUENCE
Sintaxis
Ejemplo: LAB72
LABORATORIO 5-11. Crear un índice sobre la tabla EMP llamado I_EMP_EMPNO que no permita números de
empleado duplicados.
Usted probará este índice en el laboratorio de la próxima sección.
2. Cree un secuenciador llamado S_DEPT_DEPTNO que empiece en 60 y se incremente en 10.
Este secuenciador lo usará en el laboratorio de la próxima sección.
3. Muestre su secuencia seleccionando todas las columnas de la tabla USER_SEQUENCES del
diccionario de datos.
4. Use la tabla DUAL para mostrar el próximo valor de secuencia disponible para el secuenciador
S_DEPT_DEPTNO.
5. Repita el ejercicio 4.
SQL> SELECT s_emp_empno.nextval2 FROM dual;
NEXTVAL--------------
8000
1 rows selected.
DROP SEQUENCE nombre_secuencia;
SQL> DROP SEQUENCE s_emp_empno;
Sequence dropped.
Introducción a Oracle – SQL-Plus
Formato de Reportes 76
ACTUALIZACION DE DATOS EN TABLAS
INSERCIÓN DE FILAS
Inserte filas en una tabla con la instrucción INSERT.
Sintaxis
Ejemplo:
Mostrar la estructura de la tabla DEPT:
Inserte el departamento de Finanzas con código 50 con sede en Los Angeles en la tabla DEPT: LAB73
Inserte valores en todas las columnas de una tabla: LAB74
INSERT INTO nombre_tabla [ (columna1, columna2, …) ]
VALUES (value1, value2, ...)
SQL> DESCRIBE dept;
Name NULL? Type--------------- --------------- ------------------DEPTNO NOT NULL NUMBER(2)DNAME CHAR(14)LOC CHAR(13)
SQL> INSERT INTO dept
2 VALUES (50, ‘FINANCE’, ‘LOS ANGELES’);
1 row created.
SQL> INSERT INTO emp
2 VALUES (1234, ‘EMMETT’, ‘SALESMAN’, 7698, SYSDATE, 2000, NULL, 30);
1 row created.
Introducción a Oracle – SQL-Plus
Formato de reportes 77
Inserte valores en solo algunas columnas de una tabla: LAB75
Inserte valores en las columnas utilizando parámetros: LAB76
ACTUALIZACIÓN DE FILAS
Cambie los datos en una tabla específica con la sentencia UPDATE.
Sintaxis
Ejemplo:
Mostrar la estructura de la tabla EMP
SQL> INSERT INTO emp ( empno, ename, hiredate, sal, deptno )
2 VALUES ( S_EMP_EMPNO.NEXTVAL, ‘LERNER’, ’01/01/1992’, 2000, 30 );
1 row created.
SQL> INSERT INTO dept
2 VALUES ( &DEPTNO, ‘&DNAME’, ‘&LOC’ );
Enter value for DEPTNO: 60Enter value for DNAME: RESEARCHEnter value for LOC: BOSTON
1 row created.
UPDATE nombre_tablaSET columna1 = valor, columna2 = valor[ WHERE condición ]
SQL> DESCRIBE emp;
Name NULL? Type--------------- --------------- ------------------EMPNO NOT NULL NUMBER(4)ENAME CHAR(10)JOB CHAR(9)MGR NUMBER(4)HIREDATE DATESAL NUMBER(7, 2)COMM NUMBER(7, 2)DEPTNO NOT NULL NUMBER(2)
Introducción a Oracle – SQL-Plus
Formato de reportes 78
Cambiar al empleado 7566 al cargo de vendedor en el departamento 30: LAB77
Actualizar varias columnas en una sola fila: LAB78
Actualización de múltiples filas: Cambiar el cargo ‘SALESMAN’ por ‘SALES’. LAB79
SQL> UPDATE emp
2 SET job = ‘SALESMAN’, deptno = 30
3 WHERE empno = 7566;
1 row updated.
SQL> UPDATE emp
2 SET job = ‘SALESMAN’, sal = sal * 1.05, deptno = 30
3 WHERE empno = 7788;
1 row updated.
SQL> UPDATE emp
2 SET job = ‘SALES’
3 WHERE job = ‘SALESMAN’;
7 rows updated.
SQL> SELECT ename, job, deptno
2 FROM emp
3 WHERE job = ‘SALES’;
ENAME JOB DEPTNO------------- ------------- ---------------ALLEN SALES 30WARD SALES 30JONES SALES 30MARTIN SALES 30SCOTT SALES 30TURNER SALES 30EMMETT SALES 30
7 rows selected.
Introducción a Oracle – SQL-Plus
Formato de reportes 79
Devolver el cargo a su valor original ‘SALESMAN’ LAB80
ELIMINACIÓN DE FILAS
Elimine filas de una tabla específica usando la sentencia DELETE.
Sintaxis
Ejemplo:
Borrar el empleado número 1234 (EMMETT) de la tabla EMP para reflejar su retiro. LAB81
Pregunta:
¿Qué hubiera pasado si se omite la cláusula WHERE en el ejemplo anterior?
SQL> UPDATE emp
4 SET job = ‘SALESMAN’
5 WHERE job = ‘SALES’;
7 rows updated.
DELETE FROM nombre_tabla
[ WHERE condición ]
SQL> DELETE FROM emp
2 WHERE empno = 1234;
1 row deleted.
Introducción a Oracle – SQL-Plus
Formato de reportes 80
UN VISTAZO AL PROCESAMIENTO DE TRANSACCIONES
Una transacción se define como todos los cambios realizados a una base de datos entre COMMIT
sucesivos.
DDEELLEETTEE
TTrraannssaacccciióónn
IINNSSEERRTTIINNSSEERRTT
SSAAVVEEPPOOIINNTT AA
RROOLLLLBBAACCKK TTOO BB
SSAAVVEEPPOOIINNTT BBCCOOMMMMIITT
RROOLLLLBBAACCKK TTOO AA
RROOLLLLBBAACCKK
IINNSSEERRTTIINNSSEERRTT UUPPDDAATTEE DDEELLEETTEE
Introducción a Oracle – SQL-Plus
Formato de reportes 81
PROCESAMIENTO DE TRANSACCIONES
La sentencia COMMIT hace permanentes todos los cambios realizados en la transacción actual.
Tipos de COMMIT
o Explícito
o Implícito
Ejemplo:
Hacer COMMIT en una sentencia SQL. LAB82
Logre que las modificaciones realizadas sean deshechas utilizando la sentencia ROLLBACK
Ejemplo:
Transfiera a JONES al grupo de vendedores: LAB83
SQL> DELETE FROM emp
2 WHERE ename = ‘LERNER’;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> UPDATE emp
2 SET job = ‘SALESMAN’, deptno = 30
3 WHERE ename = ‘JONES’;
1 row updated.
Introducción a Oracle – SQL-Plus
Formato de reportes 82
Todos los empleados de la compañía que se llaman ‘JONES’ fueron transferidos al grupo de
vendedores. Revierta la operación y vuelva a realizar el traslado, utilizando el número de empleado.
Marque un punto en una transacción hasta el cual podría hacer un ROLLBACK sin necesidad de
cancelar toda la transacción, utilizando la sentencia SAVEPOINT.
Ejemplo:
Inserte un nuevo departamento en la tabla DEPT y cree un punto de salva (SAVEPOINT) para la
transacción: LAB84
Use la sentencia ROLLBACK TO SAVEPOINT para cancelar solo una porción de la transacción
(hasta un punto de salva determinado):
SQL> UPDATE emp
2 SET job = ‘SALESMAN’, deptno = 30
3 WHERE empno = 7566;
1 row updated.
SQL> ROLLBACK;
Rollback complete.
SQL> INSERT INTO dept
2 VALUES (70, ‘PERSONNEL’, ‘TAHITI’);
1 row created.
SQL> SAVEPOINT A;
Savepoint created.
Introducción a Oracle – SQL-Plus
Formato de reportes 83
Ejemplo:
Inserte un nuevo departamento en la tabla DEPT y cree un punto de salva (SAVEPOINT) para la
transacción: LAB85
Cambie la ubicación del departamento 80 hacia Kauai: LAB86
Cancele todas las operaciones hasta el punto de salva B.
SQL> INSERT INTO dept
2 VALUES (80, ‘EDUCATION’, ‘MAUI’);
1 row created.
SQL> SAVEPOINT B;
Savepoint created.
SQL> UPDATE dept
2 SET loc = ‘KAUAI’
3 WHERE deptno = 80;
1 row updated.
SQL> ROLLBACK TO B;
Savepoint created.
Introducción a Oracle – SQL-Plus
Formato de reportes 84
LABORATORIO 6-1
Inserción, actualización, eliminación de datos y confirmación de transacciones.
1. Insértese usted mismo como un empleado en la tabla EMP.
2. Confirme la inserción.
3. Inserte otra fila en la tabla EMP pidiendo el nombre del empleado, el número del departamento,
el número de empleado y la fecha de contratación.
4. Cree un punto de salva llamado SP1.
5. Adicione un nuevo departamento en la tabla DEPT con número 99, localizado en Maui y con
nombre EDUCATION.
6. Cancele las operaciones hasta el punto de salva SP1. Escriba una consulta para mostrar todos
los datos de la tabla DEPT para verificar el estado de los nuevos datos ingresados.
7. Confirme los cambios.
8. Actualice sus propios datos, dándose usted mismo un aumento de $1.000 por mes.
9. Bórrese usted mismo de la tabla EMP.
10. Confirme los cambios.
11. Haga de nuevo la vista llamada EMP_DEPTNO_TEN, use WITH CHECK OPTION, la cual
incluye el nombre del empleado, número de departamento y número de empleado de la tabla
EMP. Los únicos empleados que estarán visibles a través de esta vista son los que se encuentran
en el departamento 10.
12. Actualice el departamento del empleado KING de 10 a 20 usando la vista
EMP_DEPTNO_TEN. ¿Qué paso? ________________________________________________
13. Intente ingresar un empleado con un número de empleado duplicado en la tabla EMP para
probar el índice creado en la sección anterior.
14. Inserte dos nuevos departamentos usando la secuencia S_DEPT_DEPTNO, la cual se creó en la
sección anterior.
15. Escriba una consulta para mostrar los nuevos departamentos insertados.