Manual SQL Plus

Introducción a Oracle

SQL-Plus

INDICE DE CONTENIDO

ACCESO Y VISUALIZACIÓN DE DATOS...4

EL MODELO RELACIONAL...4

VISIÓN GENERAL DE SQL...6

SQL CONTRA SQL*PLUS...6

Comandos SQL...6

Use los comandos de SQL*Plus para:...6

VISIÓN GENERAL DEL INICIO DE UNA SESIÓN SQL...7

ACCESO A LA AYUDA...8

VISTAZO DE LA SENTENCIA SELECT...8

SELECCIÓN DE COLUMNAS...9

SELECCIÓN DE FILAS...12

VER EL DICCIONARIO DE DATOS...20

Nombre de tabla...20

Descripción...20

EDICIÓN DEL BUFFER DE SQL-PLUS...21

LABORATORIO 1-1...24

EJECUCIÓN DE OPERACIONES SOBRE TIPOS DE DATOS Y FUNCIONES...25

VISTAZO A LOS VALORES Y FUNCIONES NUMÉRICOS...25

REFERENCIA DE LOS VALORES Y FUNCIONES NUMÉRICOS...25

VISTAZO A LOS VALORES Y FUNCIONES DE FECHA...30

REFERENCIA DE LOS VALORES Y FUNCIONES DE FECHA...30

REFERENCIA DE LOS VALORES Y FUNCIONES DE CARACTERES...34

VISIÓN GENERAL DE ALGUNAS FUNCIONES ADICIONALES...37

REFERENCIA DE ALGUNAS FUNCIONES ADICIONALES...38

LABORATORIO 2-1...38

VISTAZO A LAS FUNCIONES DE GRUPO...40

LABORATORIO 2-2...45

OBTENIENDO DATOS DE MULTIPLES TABLAS...46

CARACTERÍSTICAS DEL JOIN...46

TIPOS DE JOIN...47 EQUIJOIN...47 OUTER JOIN...48 SEF-JOINS...49 NON-EQUIJOINS...50 OPERADORES DE CONJUNTO...51 EL OPERADOR UNION...51

EL OPERADOR INTERSECCIÓN (INTERSECT)...52

CREACIÓN DE VISTAS...63

VISTAZO AL ACCESO DE TABLAS Y VISTAS...66

UN VISTAZO A LA CONCESIÓN DE ACCESO A TABLAS Y VISTAS...67

VISIÓN GENERAL DE LOS SINÓNIMOS...68

CREACIÓN DE SINÓNIMOS...69

LABORATORIO 4-1...70

VISIÓN GENERAL SOBRE LOS ÍNDICES...71

CREACIÓN DE ÍNDICES...71

CREACIÓN DE GENERADORES DE SECUENCIA...74

LABORATORIO 5-1...75

INSERCIÓN DE FILAS...76

ACTUALIZACIÓN DE FILAS...77

ELIMINACIÓN DE FILAS...79

UN VISTAZO AL PROCESAMIENTO DE TRANSACCIONES...80

PROCESAMIENTO DE TRANSACCIONES...81

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: Tabla EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH RECEPCIONISTA 7902 17/12/1995 800 20 7499 ALLEN VENDEDOR 7698 20/02/1996 1600 300 30 7521 WARD VENDEDOR 7698 22/02/1996 1250 500 30 7566 JONES GERENTE 7839 02/04/1996 2975 20 7654 MARTIN VENDEDOR 7698 28/07/1996 1250 1400 30 7698 BLAKE GERENTE 7839 31/05/1996 2850 30 7782 CLARK GERENTE 7839 09/06/1996 2450 10 7788 SCOTT ANALISTA 7566 09/12/1997 3000 20 7839 KING PRESIDENTE 17/11/1996 5000 10 7844 TURNER VENDEDOR 7698 08/11/1996 1500 0 30 7876 ADAMS RECEPCIONISTA 7788 12/01/1998 1100 20 7900 JAMES RECEPCIONISTA 7698 03/12/1996 950 30 7902 FORD ANALISTA 7566 03/12/1996 3000 20 7934 MILLER RECEPCIONISTA 7782 23/01/1998 1300 10

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 DEPTNO

7369 SMITH RECEPCIONISTA 7902 17/12/1995 800 20 7499 ALLEN VENDEDOR 7698 20/02/1996 1600 300 30 7521 WARD VENDEDOR 7698 22/02/1996 1250 500 30 7566 JONES GERENTE 7839 02/04/1996 2975 20 7654 MARTIN VENDEDOR 7698 28/07/1996 1250 1400 30 7698 BLAKE GERENTE 7839 31/05/1996 2850 30 7782 CLARK GERENTE 7839 09/06/1996 2450 10 7788 SCOTT ANALISTA 7566 09/12/1997 3000 20 7839 KING PRESIDENTE 17/11/1996 5000 10 7844 TURNER VENDEDOR 7698 08/11/1996 1500 0 30 7876 ADAMS RECEPCIONISTA 7788 12/01/1998 1100 20 7900 JAMES RECEPCIONISTA 7698 03/12/1996 950 30 7902 FORD ANALISTA 7566 03/12/1996 3000 20 7934 MILLER RECEPCIONISTA 7782 23/01/1998 1300 10 Tabla EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH RECEPCIONISTA 7902 17/12/1995 800 20 7499 ALLEN VENDEDOR 7698 20/02/1996 1600 300 30 7521 WARD VENDEDOR 7698 22/02/1996 1250 500 30 7566 JONES GERENTE 7839 02/04/1996 2975 20 7654 MARTIN VENDEDOR 7698 28/07/1996 1250 1400 30 7698 BLAKE GERENTE 7839 31/05/1996 2850 30 7782 CLARK GERENTE 7839 09/06/1996 2450 10 7788 SCOTT ANALISTA 7566 09/12/1997 3000 20 7839 KING PRESIDENTE 17/11/1996 5000 10 7844 TURNER VENDEDOR 7698 08/11/1996 1500 0 30 7876 ADAMS RECEPCIONISTA 7788 12/01/1998 1100 20 7900 JAMES RECEPCIONISTA 7698 03/12/1996 950 30 7902 FORD ANALISTA 7566 03/12/1996 3000 20 7934 MILLER RECEPCIONISTA 7782 23/01/1998 1300 10 Tabla DEPT

DEPTNO DNAME LOC

10 FINANZAS NEW YORK

20 INVESTIGACIÓN DALLAS

30 VENTAS CHICAGO

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 y regular el acceso a una base de datos

SQL*Plus Es una aplicación que reconoce y ejecuta comandos SQL y comandos especializados de SQL*Plus que pueden personalizar reportes, proveer facilidades de 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 REVOKE

AUDIT GRANT ROLLBACK

COMMENT INSERT SELECT

COMMIT LOCK UPDATE

CREATE NO AUDIT VALIDATE

DELETE 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

Comandos de SQL-Plus

@ (ejecutar) COPY QUIT / (ejecutar último)

DESCRIBE REMARK ACCEPT DISCONNECT

RUN APPEND EDIT SAVE

BREAK EXIT SET BTITLE

GET SHOW CHANGE HELP

SPOOL CLEAR HOST SQLPLUS

COLUMN INPUT START COMPUTE

LIST TIMING CONNECT NEWPAGE

TTITLE UNDEFINE

VISIÓN GENERAL DEL INICIO DE UNA SESIÓN SQL

a) Interfaz Grafica

b) Desde la línea de Comando

SQL> connect [username[/password[@database]]] username = nombre de usuario

password = clave de acceso

@database = alias de la base de datos

c) Cierre de sesión: SQL> exit <ENTER>

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

Tabla EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH RECEPCIONISTA 7902 17/12/1995 800 20 7499 ALLEN VENDEDOR 7698 20/02/1996 1600 300 30 7521 WARD VENDEDOR 7698 22/02/1996 1250 500 30 7566 JONES GERENTE 7839 02/04/1996 2975 20 7654 MARTIN VENDEDOR 7698 28/07/1996 1250 1400 30 7698 BLAKE GERENTE 7839 31/05/1996 2850 30 7782 CLARK GERENTE 7839 09/06/1996 2450 10 7788 SCOTT ANALISTA 7566 09/12/1997 3000 20 7839 KING PRESIDENTE 17/11/1996 5000 10 7844 TURNER VENDEDOR 7698 08/11/1996 1500 0 30 7876 ADAMS RECEPCIONISTA 7788 12/01/1998 1100 20 7900 JAMES RECEPCIONISTA 7698 03/12/1996 950 30 7902 FORD ANALISTA 7566 03/12/1996 3000 20 7934 MILLER RECEPCIONISTA 7782 23/01/1998 1300 10 Tabla DEPT

DEPTNO DNAME LOC

10 FINANZAS NEW YORK

20 INVESTIGACIÓN DALLAS

30 VENTAS CHICAGO

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

DNAME ---FINANZAS INVESTIGACIÓN VENTAS OPERACIONES 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

---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 ---ANALISTA RECEPCIONISTA GERENTE PRESIDENTE VENDEDOR 5 rows selected.

SQL> SELECT DISTINCT job “JOB LIST” FROM EMP;

JOB LIST ---ANALISTA RECEPCIONISTA GERENTE PRESIDENTE VENDEDOR 5 rows selected.

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 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

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 de

la lista

IS NULL

Es un valor nulo

LIKE

Concuerda con un patrón de

caracteres

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 1600 7521 WARD 1250 7654 MARTIN 1250 7698 BLAKE 2850 7844 TURNER 1500 7900 JAMES 950 6 rows selected.

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

SQL> SELECT ename, job, sal FROM emp WHERE job <> ‘MANAGER’;

ENAME JOB SAL

--- ---- ---SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER 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.

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 20 SCOTT ANALYST 20 ADAMS CLERK 20 JAMES CLERK 30 FORD ANALYST 20 MILLER CLERK 10 6 rows selected.

SQL> SELECT ename, job, deptno, hiredate FROM emp WHERE ename LIKE ‘M%’;

ENAME JOB DEPTNO HIREDATE

--- -- ---

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 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

SQL> SELECT ename, job, deptno, hiredate FROM emp WHERE ename LIKE ‘J__ES’;

ENAME JOB DEPTNO HIREDATE

--- -- ---

---JONES MANAGER 20 02-APR-81

JAMES CLERK 30 03-DEC-81

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 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 TURNER 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 1600 JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 TURNER SALESMAN 1500 5 rows selected.

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

--- ----

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 KING

2450 MANAGER CLARK

1300 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 950 BLAKE MANAGER 2850 ALLEN SALESMAN 1600 TURNER SALESMAN 1500 MARTIN SALESMAN 1250 WARD SALESMAN 1250 6 rows selected.

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 CLERK

CLARK 2450 MANAGER

KING 5000 PRESIDENT

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 ---DEPT EMP SALGRADE 3 rows selected.

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 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 TURNER SALESMAN 1500 JAMES CLERK 950 6 rows selected. SQL> 1

1* SELECT ename, job, sal SQL> C / sal / deptno

1* SELECT ename, job, deptno SQL> /

Agregar la columna HIREDATE en la línea actual usando el comando APPEND:

Borrar la línea 3: SQL> 1

1* SELECT ename, job, deptno SQL> A , hiredate

1* SELECT ename, job, deptno, hiredate SQL> /

ENAME JOB DEPTNO

--- --- ---ALLEN SALESMAN 30 WARD SALESMAN 30 MARTIN SALESMAN 30 BLAKE MANAGER 30 TURNER SALESMAN 30 JAMES CLERK 30 6 rows selected.

ENAME JOB DEPTNO HIREDATE

--- - ---

---ALLEN SALESMAN 30 20-FEB-81

WARD SALESMAN 30 22-FEB-81

MARTIN SALESMAN 30 28-SEP-81

BLAKE MANAGER 30 31-MAY-81

TURNER SALESMAN 30 08-SEP-81

JAMES CLERK 30 03-DEC-81

6 rows selected.

SQL> 3

3* WHERE deptno = 30 SQL> del

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-80

ALLEN SALESMAN 30 20-FEB-81

WARD SALESMAN 30 22-FEB-81

JONES MANAGER 20 02-APR-81

MARTIN SALESMAN 30 28-SEP-81

BLAKE MANAGER 30 31-MAY-81

CLARK MANAGER 10 09-JUN-81

SCOTT ANALYST 20 09-DEC-82

KING PRESIDENT 10 17-NOV-81

TURNER SALESMAN 30 08-SEP-81

ADAMS CLERK 20 12-JAN-83

JAMES CLERK 30 03-DEC-81

FORD ANALYST 20 03-DEC-81

MILLER CLERK 10 23-JAN-82

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 1750

MARTIN 1250 1400 2650

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 DATE SAL 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 1900 WARD SALESMAN 1250 500 1750 JONES SALESMAN 2975 MARTIN SALESMAN 1250 1400 2650 BLAKE MANAGER 2850 SCOTT SALESMAN 3150 TURNER SALESMAN 1500 0 1500 JAMES CLERK 950 8 rows selected.

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 1900 WARD SALESMAN 1250 500 1750 JONES SALESMAN 2975 2975 MARTIN SALESMAN 1250 1400 2650 BLAKE MANAGER 2850 2850 SCOTT SALESMAN 3150 3150 TURNER SALESMAN 1500 0 1500 JAMES CLERK 950 950 8 rows selected.

Orden de evaluación

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 18000 WARD 1250 500 21000 ALLEN 1600 300 22800 MARTIN 1250 1400 31800 JONES 2975 SCOTT 3150 6 rows selected.

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 15500 MARTIN 1250 1400 16400 TURNER 1500 0 18000 JONES 2975 300 19500 SCOTT 3150 ALLEN 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 73

JONES 2975 135.227273 135

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 punto

decimal.

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

MONTHS_BETWEEN MONTHS_BETWEEN (SYSDATE, HIREDATE)

Devuelve la cantidad de meses transcurridos entre HIREDATE y la

fecha actual.

GREATEST(FECHA1, FECHA2...)

GREATEST(HIREDATE, SYSDATE)

Devuelve la fecha más tardía de una lista de fechas

FUNCIÓN EJEMPLO RESULTADO

ADD_MONTHS

ADD_MONTHS (HIREDATE, 6) ADD_MONTHS (HIREDATE, -6)

Devuelve la fecha 6 meses después de HIREDATE

Devuelve la fecha 6 meses antes de HIREDATE

LAST_DAY LAST_DAY (HIREDATE) Devuelve el último día del mes que contiene HIREDATE

NEXT_DAY NEXT_DAY (HIREDATE,_{‘FRIDAY’)} Devuelve la fecha del siguiente viernes 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/1981 KING 17/11/1981 15/02/1982 MILLER 23/01/1982 23/04/1982 3 rows selected.

LEAST(FECHA1, FECHA2...)

LEAST(HIREDATE, SYSDATE)

Devuelve la fecha más temprana de una 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/1981 KING 17/11/1981 17/05/1982 MILLER 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 1098 KING 1075 MILLER 1066 3 rows selected.

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 9 caracteres (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 9

caracteres (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 1980 12 of JANUARY 1983 3 of DECEMBER 1981

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 YORK RESEARCH – DALLAS

SALES – CHICAGO OPERATIONS – BOSTON FINANCE – LOS ANGELES RESEARCH – BOSTON PERSONNEL – TAHITI EDUCATION - MAUI 8 rows selected.

FUNCIÓN EJEMPLO RESULTADO

INITCAP INITCAP (ENAME)

Devuelve el nombre con su primera letra en mayúscula, y las

demá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) CONCAT

CONCAT(ENAME,JOB)

Concatena dos cadenas

LPAD LPAD(sal,5,'*')

muestra un número de caracteres 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 cadena

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 YORK

RESEA DALLAS

SALES CHICAGO

OPERA BOSTON

FINAN LOS ANGELES

RESEA BOSTON

PERSO TAHITI

EDUCA MAUI

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.00 WARD $500.00 JONES MARTIN $1,400.00 BLAKE SCOTT TURNER $0.00 JAMES 8 rows selected.

REFERENCIA DE ALGUNAS FUNCIONES ADICIONALES

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 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 1600 WARD 1250 500 1250 JONES 2975 0 2975 MARTIN 1250 1400 1400 SCOTT 3150 0 3150 TURNER 1500 0 1500 6 rows selected.

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'

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 (sal0029 Devuelve 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) --- ---

---SINTAXIS DE LA SENTENCIA SELECT

SELECT ...

FROM ...

WHERE ...

GROUP BY …

HAVING …

ORDER BY …

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

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 3 20 3 30 8 3 rows selected.

SQL> SELECT ename, MIN (hiredate) 2 FROM emp;

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 1 10 MANAGER 1 10 PRESIDENT 1 20 ANALYST 1 20 CLERK 2 30 CLERK 1 30 MANAGER 1 30 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 12450

SALESMAN 23450

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

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 Emp

3. Seleccionar la fecha de ingreso mínima y máxima de la tabla empleados. 4. Seleccionar el numero de empleados por cada Departamento

5. Seleccionar de cada departamento la suma de los salarios, y el No. De Empleados 6. Contar el numero de empleados del mismo cargo por departamento

7. Contar el numero de 'Analistas y Gerentes de Cada Departamento

8. 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 sueldo 10. 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.

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ánea respectivamente

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

•

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.column

FROMtable1, table2

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 especificar criterio 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 ...

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 contenga valores nulos en uno de los criterios del JOIN coincida con cada valor de la segunda tabla sobre la cual ordinariamente no estaría esa coincidencia.

Sintaxis

Ejemplo: Lista la localización de todos los departamentos así como los empleados que trabajan en estos departamentos, 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.column

FROMtable1, table2

WHERE table1.column1(+) = table2.column2;

SQL> SELECT loc, ename 2 FROM emp, dept

3 WHERE emp.deptno(+) = dept.deptno;

SQL> SELECT empno, dept.deptno, loc 2 FROM emp, dept

3 WHERE dept.deptno= emp.deptno(+) and empno is null; SQL> select ename, emp.deptno, loc

2 From emp, dept

Ejemplo: Liste el nombre y la localización del departamento de todos los CLERK, incluya una lista de 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 ese registro no aparece en el resultado. El OUTER JOIN, permite que un registro con estas características aparezcan en el resultado.

• el OUTER JOIN a través SQL proporciona registros temporales para seleccionar los datos, los datos de la tablas.

• Se coloca un (+) después de la combinación de tabla/columna (en la cláusula WHERE) sobre la que se necesita los registros falsos para realizar el OUTER JOIN (WHERE EMP.DEPTNO(+)=DEPT.DEPTNO).

• Si se requiere múltiples columnas para dar la condición JOIN, todas o ninguna de ellas puede llevar el (+).

• Solo una de las tablas en un JOIN, pueden escogerse para realizar el OUTER JOIN, es decir no se puede 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 la misma tabla.

Sintaxis

SQL> SELECT ename, dept.deptno, loc 2 FROM emp, dept

3 WHERE dept.deptno= emp.deptno(+) and (job='CLERK' OR empno is null);

SELECT alias1.column, alias2.column FROMtable1 alias1, table2 alias 2

Ejemplo: Liste el número, nombre y cargo de cada empleado, así como el numero nombre y cargo del jefe correspondiente. 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 se determinan los EQUIJOINS. Los NON-EQUIJOINS por consiguiente se basan en los siguientes operadores.

!= < <= > >= between

Ejemplo: Ordene el grado del salario (salgrade), el nombre, salario y cargo de cada empleado. Ordene el resultado por grado de salario y trabajo. LAB48

SELECT w.empno, w.ename, w.job,w.mgr, m.empno,m.ename,m.job

FROMemp w, emp m WHERE w.mgr = m.empno;

SQL> SELECT GRADE, ENAME, SAL, JOB 2 FROM EMP, SALGRADE

3 WHERE SAL BETWEEN LOSAL AND HISAL 4 ORDER BY GRADE, SAL;

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 segundo query se eliminan duplicados

INTERSECCIÓN Los registros que las dos tablas tienen en común MENOS 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 columna

FROMtabla

WHERE condiciones ciertas UNION

SELECT columna

FROMtabla

WHERE condiciones ciertas

SQL> SELECT ENAME,SAL 2 FROM ACCOUNTING 3 WHERE SAL >2000 4 UNION 5 SELECT ENAME,SAL 6 FROM RESEARCH 7 WHERE SAL >2000 8 UNION 9 SELECT ENAME,SAL 10 FROM SALES 11 WHERE SAL >2000

EL OPERADOR INTERSECCIÓN (INTERSECT)

Ejemplo

EL OPERADOR MENOS (MINUS)

El operador MINUS. Retorna todos los registros seleccionados por el primer query que no fueron también seleccionados por el segundo query

SELECT columna

FROMtabla

WHERE condiciones ciertas INTERSECT

SELECT columna

FROMtabla

WHERE condiciones ciertas

SELECT columna FROM tabla

WHERE condiciones ciertas MINUS

SELECT columna

FROMtabla

WHERE condiciones ciertas SQL> SELECT JOB 2 FROM ACCOUNTING 3 UNION 4 SELECT JOB 5 FROM RESEARCH 6 UNION 7 SELECT JOB 8 FROM SALES;

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 departamento para todos los empleados.

ENAME DEPTNO DNAME

KING 10 ACCOUNTING

BLAKE 30 SALES

CLARK 10 ACCOUNTING

JONES 20 RESEARCH

MARTIN 30 SALES

2) Crear un listado de todos los cargos cuya localización departamento es Chigaco

JOB LOC

CLERK CHICAGO

MANARGER CHICAGO

SALESMAN CHICAGO

3. Escribir una consulta que muestre el nombre del empleado, el nombre del departamento y la localización de todos los empleados que ganan una comisión

ENAME DNAME LOC

ALLEN SALES CHICAGO

WARD SALES CHICAGO

MARTIN 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 DNAME

ALLEN SALES

WARD SALES

MARTIN SALES

SQL> SELECT * FROM ACCOUNTING

2 WHERE JOB (SELECT JOB FROM ACCOUNTING 3 MINUS SELECT JOB FROM SALES)

5. Escribir una consulta que muestre el nombre, el cargo, el numero del departamento y el nombre de todos los empleados que trabajan en DALLAS.

ENAME JOB DEPTNO DNAME

SMITH CLERK 20 RESEARCH

ADAMS CLERK 20 RESEARCH

FORD ANALYST 20 RESEARCH

6. Mostrar el nombre y el numero del empleado al igual que el nombre y el numero de su respectivo jefe. Salvar la sentencia SQL en el archivo p4q6.sql

ENAME EMPNO EMGR MGR

SCOTT 7788 JONES 7566

FORD 7902 JONES 7566

ALLEN 7499 BLAKE 7698

JAMES 7900 BLAKE 7698

7. Modificar el punto anterior incluyendo los empleados que no tienen jefe

ENAME EMPNO EMGR MGR

SCOTT 7788 JONES 7566

FORD 7902 JONES 7566

ALLEN 7499 BLAKE 7698

JAMES 7900 BLAKE 7698

KING 7839

8. Crear un query que muestre el nombre del empleado, el numero del departamento y todos los empleados que trabajan en el mismo departamento como un empleado determinado

DEPTNO EMPLOYEE COLLEAGUE

10 CLARK KING 10 CLARK MILLER 10 KING CLARK 10 MILLER CLARK 10 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.

SMITH CLERK RESEARCH 800 1

10. Cree una consulta que muestre el nombre del empleado y la fecha de ingreso de los empleados que ingresaron después que BLAKE

ENAME HIREDATE

KING 17-NOV-81

CLARK 09-JUN-81

MARTIN 28-SEP-81

TURNER 08-SEP-81

11. Mostrar los nombres de los empleados, la fecha de ingreso, el nombre de su jefe y la fecha de ingreso de su jefe. Etiquete los nombres de las columnas respectivamente.

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 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.

CREACIÓN DE TABLAS

Tipo de datos Descripción

CHAR(tamaño) Almacena datos de tipo carácter de longitud fija. Con un tamaño máximo de 2000 bytes

VARCHAR2(Tamaño) Almacena datos de tipo carácter de longitud variable, con un tamaño máximo de 4.000

VARCHAR(Tamaño) Actualmente es igual al Char

LONG Almacena datos de tipo carácter de longitud variable hasta 2 gigabytes NUMBER(L,D) Almacena datos numéricos siendo L la longitud y D el numero de dígitos

decimales

INTEGER 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 RAW CLOB Almacena objetos de caracteres grandes de un único byte

BLOB 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 Uso

PRIMARY 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.

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 hdates

2 AS SELECT empno, ename, hiredate 3 FROM emp

4 WHERE empno = 10; Table created.

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_emp 5 AS SELECT *

6 FROM emp 7 WHERE 1 = 2; Table created.

ALTER TABLE nombre_tabla

ADD ( { nombre_columna tipo | restricción }

[, { nombre_columna tipo | restricción } ] ... )

SQL> ALTER TABLE hdates

2 ADD (manager NUMBER(4) ); Table altered.

ALTER TABLE nombre_tabla

MODIFY ( { nombre_columna tipo | restricción } [, { nombre_columna tipo | restricción } ] ... )

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:

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_tabla

DROP COLUMN ( nombre_columna, nombre_columna ... )

SQL> ALTER TABLE HDATES 2 DROP COLUMN ename ; Table altered.

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_temp

2 AS SELECT columna1, columna2, columna4, columna5 3 FROM tabla_original;

Table created.

SQL> DROP TABLE tabla_original; Table dropped.

SQL> RENAME tabla_temp TO tabla_original; Table renamed.

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 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

E

E

M

M

P

P

T

T

a

a

b

b

l

l

e

e

--- - -- ---- -- --- --- ---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

E

E

M

M

P

P

V

V

U

U

1

1

0

0

V

V

i

i

e

e

w

w

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 la cláusula where.

La cláusula WITH READ ONLY se utiliza cuando se requiere crear una vista de solo lectura, es decir no 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;

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 MANAGER

7839 KING PRESIDENT

7934 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, job

3 FROM emp

4 WHERE deptno = 10; View created.

SQL> SELECT * FROM empvu11; ID_NUMBER EMPLOYEE TITLE -

---7782 CLARK MANAGER

7839 KING PRESIDENT

7934 MILLER CLERK

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 empvu20 2 AS SELECT * FROM emp 3 WHERE deptno = 20 4 WITH CHECK OPTION; View created.

SQL> CREATE VIEW salvu10

2 AS SELECT empno, ename, sal * 12 annual_salary 3 FROM emp

4 WHERE deptno = 10; View created.

SQL> SELECT * FROM salvu10;

EMP_NO ENAME ANNUAL_SALARY

--- ---

---7782 CLARK 29400

7839 KING 60000

7934 MILLER 15600

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 empvu20 2 SET deptno = 30 3 WHERE empno = 7369; UPDATE empvu20 * ERROR at line 1:

UN VISTAZO A LA CONCESIÓN DE ACCESO A TABLAS Y VISTAS

Privilegios de acceso a tablas

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_vista TO usuario1, usuario2, ... [ o PUBLIC ] [ WITH GRANT OPTION ]

SQL> GRANT ALL 2 ON emp 3 TO PUBLIC; Grant succeeded. SQL> GRANT SELECT 4 ON dept 5 TO jones; Grant succeeded.

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 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 SELECT

2 ON dept 3 TO jones

4 WITH GRANT OPTION; Grant succeeded.

SQL> GRANT CONNECT 2 TO scott

3 IDENTIFIED BY leopard; Grant succeeded.