Fundamentos SQL

Notas de Clase BD

LENGUAJES DE CONSULTA COMERCIALES

Los lenguajes formales proporcionan una notación concisa para representar consultas.Sin embargo los sistemas comerciales de BD requieren un lenguaje de consulta más “amigable para el usuario”.Algunos de estos lenguajes son: SQL, QBE y Quel.QBE (Query By Example) está basado en el cálculo relacional de dominios.Quel está basado en el cálculo relacional de tuplas.SQL (Structured Query Languaje) usa una combinación de construcciones del álgebra y del cálculo relacional.Aunque estos lenguajes se les referencia como “lenguajes de consulta”, SQL, QBE y Quel tienen muchas otras capacidades además de consultar una BD. Estas incluyen características para definir la estructura de los datos, para modificarlos y para especificar restricciones de seguridad.

SQL (Structured Query Languaje)

Existen numerosas versiones de SQL. La versión original fue desarrollada en un laboratorio de IBM (Sn. José Research Laboratory de IBM).Originalmente llamado Sequel, fue implementado como parte del proyecto System R (Sistema de BD relacional de IBM) en los primeros años de la década de los setenta.El lenguaje Sequel ha evolucionado desde entonces y su nombre ha cambiado a SQL. El SQL se ha establecido claramente como el lenguaje de BD relacional estándar.El SQL tiene varias partes:

- DDL (Lenguaje de definición de datos): El SQL-DDL proporciona órdenes para definir esquemas de relaciones, eliminar relaciones, crear índices y modificar esquemas de relaciones. Además incluye órdenes para definir vistas y para especificar derechos de acceso a relaciones y vistas.

- DML (Lenguaje de manipulación de datos): El SQL-DML incluye un lenguaje de consultas basado en el álgebra relacional y en el cálculo relacional de tuplas. También incluye órdenes para insertar, suprimir y modificar tuplas de la BD.

- DML inmerso: La forma inmersa de SQL está diseñada para usar dentro de los lenguajes de programación de propósito general, tales como Cobol, Pascal, C ...

- Control de transacciones: SQL incluye órdenes para especificar el comienzo y el final de transacciones.

- Integridad: Las versiones recientes de SQL proporcionan formas de comprobación de integridad.

SQL es usado para interactuar con una base de datos relacional, está clasificado como el lenguaje estándar para la administración de los datos en estas bases de datos.

José Ignacio Botero O.Margarita María Hincapié V.

59

Creación de un Script

Es un archivo plano con extensión sql, en el cual se almacenan las instrucciones SQL para acceder a los datos de la base de datos.

La característica fundamental es que no posee formato; puede ser creado con cualquier editor de archivos planos: Editor del DOS, Word, Block de Notas, Vi.

Sentencias SQL

Son las instrucciones con las cuales el usuario (Administrador de datos, Administrador de la base de datos, Programador, usuario final) realiza la comunicación entre él y la base de datos.

Tipos de datos.

Para SQL/plus, existen varios tipos de datos, pero trabajaremos básicamente con 3 (Tres) tipos de datos: Varchar2, Number y Date.

Varchar2: Para el almacenamiento de datos tipo Alfanumérico, su formato general es:

Nombre_variable varchar2(n).

Donde n = Número de posiciones que ocupa el dato almacenado.

Ejemplo: create table personal (identificacion varchar2(15),....);

Number: Almacena datos tipo numérico, incluyendo valores con punto flotante. El formato general es:

Nombre_variable number(n) o Nombre_variable number(n,m)

Ejemplo: create table personal (hijos number(3), sueldo number(10,2)...);

Date: Almacena datos tipo fecha, el tamaño del dato es fijo, no hay que definirlo. El Formato general es:

Nombre_variable date.

Ejemplo: create table personal ( fecha_nac date...);

Operadores Aritméticos

Suma “+”Resta “ - “Multiplicación “ * “División “ / ”José Ignacio Botero O. 2005Margarita María Hincapié V.

60

Operadores Relacionales.

Igual a “ = “Mayor que “ > ”Mayor o igual a “ >= “Menor que “ < “Menor o igual “ <= “Diferente “ =! “

Operadores Lógicos

ANDORNOT

Otros Operadores

INNOTBETWEENLIKE

Definición de datos

El DDL de SQL permite crear varias estructuras de datos tales como: Relaciones o tablas, índices, vistas, secuencias.

Creación de relaciones o tablas

El SQL-DDL permite la definición no sólo de un conjunto de relaciones, sino también información sobre cada relación incluyendo:- El esquema para cada relación.- El dominio asociado con cada atributo.- El conjunto de limitantes que se va a mantener para cada relación.

Una relación o tabla en SQL se define usando la orden:

Create table nombre_tabla(nombre_campo1 tipo_datos(Dimensión), nombre_campo2 tipo_dato(Dimensión),

…);

Ejemplo creación objeto tabla:

CREATE TABLE Asociados ( id number(8),nombre_a varchar2(30),telefono varchar2(10),cod_c number(3));

José Ignacio Botero O. 2005Margarita María Hincapié V.

61

Una relación recién creada está inicialmente vacía. La orden Insert puede usarse para cargar datos en la relación.

Limitantes de integridad de los datos (Constraints)

Permite establecer la integridad de los datos en la base de datos. Hay varios tipos:

- NOT NULL (NN): especifica que el campo no puede tener valor nulo.

- UNIQUE (UK): indica que el campo debe tener valores únicos para todas las filas de la tabla.

- PRIMARY KEY (PK): quien identifica de una manera única cada fila de una tabla.

- REFERENCES (FK): establece y refuerza la relación existente entre una clave foránea y la clave primaria de una tabla referenciada.

- CHECK (CK): especifica una condición que debe ser verdadera.

Sintaxis:Create table nombre_tabla(nombre_campo1 tipo_datos

Constraint nombre_constraint tipo_constraint, nombre_campo2 tipo_dato

Constraint nombre_constraint tipo_constraint…

);Ejemplo de uso, corresponde al cuadro dado a continuación:

CREATE TABLE Asociados ( id number(8)CONSTRAINT id_pk PRIMARY KEY,nombre_a varchar2(30) CONTRAINT nombre_a_nn CHECK(nombre_a is not null),telefono varchar2(10),cod_c number(3) CONSTRAINT cod_c_fk REFERENCES carreras(codigo)CONTRAINT cod_c_nn CHECK(cod_c is not null));

Tabla: Asociadoscolumnas id nombre_a telefono cod_c tipo clave

PK FK

nulos/unico NN, UK NN NNtabla_de_fk Carrerascol_ref_fk códigotipo_dato num char char numlong_max 8 30 10 3


62

ALTER TABLE: Es una instrucción de DDL-SQL que puede usarse para:

1- Adicionar un campo a una tabla. Sintaxis:ALTER TABLE rADD (nom_campo1 tipo, nom_campo2 tipo, ...)

donde r es una relación o tabla y nom_campo i es el campo a adicionar con su tipo de dato o dominio respectivo.

Ej.: Adicionar una columna para comentarios a la tabla r.ALTER TABLE rADD (comentario varchar2(255))

2- Para modificar la definición de un campo. Sintaxis:ALTER TABLE rMODIFY (nom_campo1 tipo, nom_campo2 tipo, ...)

Ej.: Incrementar el tamaño de la columna nombre de 20 a 30 caracteres en la tabla r.ALTER TABLE rMODIFY(nombre varchar2(30))

Si el tamaño de una columna se va a cambiar a una longitud menor, la tabla debe estar vacía.

3- Adicionar y remover restricciones de los datos. Sintaxis:ALTER TABLE rADD CONSTRAINT nom_constraint

oALTER TABLE rDROP CONSTRAINT nom_constraint

Ej.: Otra forma de definir las claves primarias y foráneas con la instrucción ALTER TABLE a la tabla Asociado, seria:

ALTER TABLE Asociados ADD CONSTRATINT id_pk PRIMARY KEY(id);

Para la clave foránea:

ALTER TABLE Asociados ADD CONSTRATINT cod_c_fk FOREIGN KEY (cod_c)REFERENCES carreras(codigo);

Cuando la clave primaria sea compuesta. Ej. (Supuesto)

ALTER TABLE Asociados ADD CONSTRATINT id_Asoc_cod_c_carrera_pk PRIMARY KEY(id, cod_c);

Ej.: Remover la restricción anterior:


63

ALTER TABLE AsociadosDROP CONSTRAINT id_Asoc_cod_c_carrera_pk;

Definición de índices: Los índices son un medio para acceder rápidamente la información. Los índices pueden asegurar que no se inserten valores duplicados en una columna. Este tipo de índice es frecuentemente encontrado en la llave primaria de una tabla. Forma general:

Create [unique] index nombre_indice on nombre_tabla (campo1[asc ó desc], campo2[asc ó desc], ...);

Ejemplo creación objeto index:

Create unique index Asoc_id_inx on Asociados(id asc);

Create index Asoc_id_inx on Asociados(id asc);

Nota: Cuando se crea un objeto tipo índex con parámetro unique, es equivalente a definir una clave primaria.

Instrucción Drop.

Permite eliminar los objetos. Sintaxis:

Drop tipo_objeta nombre_objeto;

Ejemplos:

Para eliminar la relación o tabla r de una BD:

DROP TABLE r [cascade constraints]

Esta orden no sólo suprime todas las tuplas en r sino también el esquema de r.

Para eliminar un objeto índice se usa la orden:

DROP INDEX Asoc_id_inx

Sentencia SELECTLa operación más común en SQL es la consulta de datos para recuperar información. La estructura básica de esta instrucción en SQL, está compuesta de tres cláusulas:SELECT (elegir)FROM (de) y WHERE (donde).


64

La cláusula SELECT corresponde a la operación de Proyección (π) del álgebra relacional; sirve para listar todos los atributos que se desean en el resultado de una consulta.La cláusula FROM es una lista de relaciones que se van a examinar durante la ejecución de la expresión.La cláusula WHERE corresponde al predicado de la elección (б) del álgebra relacional. Se compone de un predicado que incluye atributos de las relaciones que aparecen en la cláusula FROM.

Una consulta en SQL tiene la forma: SELECT A1, A2, ... , An FROM r1, r2, ... , rn WHERE p

Las Ai representan atributos, las ri representan relaciones y p es un predicado.Esta consulta es equivalente a la expresión del álgebra relacional:

π a1, a2, ... ,an ( б (r1 x r2 x ... x rn) ) p

Si se omite la cláusula WHERE, el predicado p es verdadero.La relación de atributos A1, A2, ... , An puede sustituirse por un asterisco ‘*’ para elegir todos los atributos de todas las relaciones que aparecen en la cláusula FROM. El SQL forma el Producto Cartesiano de las relaciones que se nombran en la cláusula FROM; realiza una elección del álgebra relacional utilizando el predicado de la cláusula WHERE y proyecta el resultado a los atributos de la cláusula SELECT.El resultado de una consulta en SQL es una nueva relación o tabla.

La sintaxis completa de la instrucción SELECT es:

Select [ * ] ó [nombre_campo] ó [función] ó [String] from nombre_tabla1 [Alias1], nombre_tabla2 [Alias2]

[Where condición ó relacion][Group by nombre_campo][Having by condición][Order by nombre_campo (Asc/Desc)];

Ejemplo:

La tabla empleados tiene los siguientes registros:

ID_EMP NOMBRE_EMP DEPART_EMP SALARIO1011 Pedro 10 10000001012 Juan 20 12000001013 Pedro 30 11000001014 Jorge 10 12000001015 Valentina 10 10000001016 Cristina 20 1000000


65

La tabla departamentos tiene los siguientes registros:

ID_DEPART NOMBRE_DEPART10 División de Sistemas20 Ensamble30 Pintura

Seleccionar todos los registros y campos de cada registro de la tabla empleados.

select * from empleados;

Resultado:

ID_EMP NOMBRE_EMP DEPART_EMP---------- --------------------------------- --------------------1011 Pedro 101012 Juan 201013 Pedro 301014 Jorge 101015 Valentina 101016 Cristina 20

Seleccionar todos los registros y algunos campos de la tabla empleados.

select id_emp, nombre_emp from empleados;

Resultado:

ID_EMP NOMBRE_EMP---------- ----------------------------------------1011 Pedro1012 Juan1013 Pedro1014 Jorge1015 Valentina1016 Cristina

Seleccionar cuántos registros están almacenados en la tabla empleados.

select count(*) from empleados;

Resultado:COUNT(*)---------6

Seleccione los números de departamento en los que están adscritos los empleados SELECT Dep._emp

FROM empleados


66

Resultado:

DEPART_EMP---------- -------------102030101020

En los lenguajes de consulta formales se utilizó el concepto matemático de que una relación es un conjunto. Por ello no aparecen tuplas repetidas en una relación. En la práctica, la eliminación de duplicados toma un tiempo relativamente largo. Por tanto, el SQL permiten duplicados en las relaciones. Así la consulta anterior producirá una lista de todos los números de departamento, cada uno tantas veces como aparezca en una tupla de la relación empleados. Cuando se desea forzar la eliminación de duplicados, se inserta la palabra clave distinct después del SELECT. Por tanto, la consulta anterior quedaría así:

SELECT distinct depart_empFROM empleados

Y el resultado es:

DEPART_EMP---------- -------------102030

Sentencia Where:

Corresponde al predicado de la operación de elección (?) del álgebra relacional, coloca condiciones a tuplas. Ademas como SQL no tiene una representación directa del producto natural y puesto que el producto natural se define en términos de un producto cartesiano y una proyección, es relativamente sencillo escribir una expresión en SQL para el producto natural.

Seleccionar la identificación del empleado, el nombre del empleado y el nombre del departamento donde trabaja el empleado: select emp.id_emp, emp.nombre_emp, dep.nombre_depart

from empleados emp, departamentos depwhere emp.depart_emp = dep.id_depart;

Resultado:ID_EMP NOMBRE_EMP NOMBRE_DEPART---------- ---------------------------------------- ----------------------1011 Pedro División de Sistemas1012 Juan Ensamble1013 Pedro Pintura1014 Jorge División de Sistemas1015 Valentina División de Sistemas1016 Cristina Ensamble


67

SQL utiliza la notación nombre_relación.nombre_atributo para evitar ambigüedad en los casos en los que un atributo aparece en el esquema de más de una relación, al igual que en álgebra relacional.

Sentencia Gruop By y las Funciones de grupo

SQL ofrece la posibilidad de calcular funciones en grupos de tuplas usando la cláusula group by.El atributo dado en la cláusula group by se usa para formar grupos. Las tuplas con el mismo valor en el atributo de esta cláusula, se colocan en un grupo. Además, SQL incluye funciones para calcular:Promedio (avg)Mínimo (min)Máximo (max)Total (sum)Contar (count)Las operaciones como avg, sum… se llaman funciones de grupo porque operan sobre grupos de tuplas. El resultado de una función de grupo es un valor único. Ej.:

Seleccione cuántos empleados existen por departamento.

Select count(emp.id_emp), dep.nombre_depart From empleados emp, departamentos depWhere emp.depart_emp = dep.Id_departGroup by dep.nombre_depart;

Resultado:COUNT(EMP.ID_EMP) NOMBRE_DEPART----------------- ----------------------------------------

3 División de Sistemas 2 Ensamble

1 Pintura

Ejs.:1- Encontrar el salario promedio de los empleados por departamento.Select dep.nombre_depart, avg(emp.salario)From empleados emp, departamentos depWhere emp.depart_emp = dep.Id_departGroup By dep.nombre_depart;

Resultado: Nombre_depart avg(emp.salario)División de Sistemas 1066666Ensamble 1100000

Pintura 1100000

Sentencia Having

A veces es útil declarar una condición que se aplica a los grupos más que a las tuplas. Por Ej. Podríamos estar interesados únicamente en seleccionar los departamentos en los que el saldo promedio de salarios sea menor que 1100000.


68

Esta condición no se aplica a una tupla, sino a cada grupo construído mediante la cláusula group by. Para expresar una consulta de este tipo, se usa la cláusula having de SQL. Los predicados de la cláusula Having se aplican después de la formación de grupos, por lo que pueden utilizarse funciones de grupo. La consulta expresada en SQL, sería:

Select dep.nombre_depart, avg(emp.salario)From empleados emp, departamentos depWhere emp.depart_emp = dep.Id_departGroup By dep.nombre_departHaving avg(emp.salario) < 1100000;

Resultado.Nombre_depart avg(emp.salario)

División de Sistemas 1066666

Seleccione cuántos empleados existen por departamento y en cuales departamentos hay mas de 2 empleados.

select count(emp.id_emp), dep.nombre_depart from empleados emp, departamentos depwhere emp.depart_emp = dep.Id_departgroup by dep.nombre_departhaving count(emp.id_emp)>1;

Resultado:

COUNT(EMP.ID_EMP) NOMBRE_DEPART-------------------------------- ----------------------------------------

3 División de Sistemas 2 Ensamble

Sentencia Order By

SQL ofrece al usuario cierto control sobre el orden en el que se van a presentar las tuplas en una relación. La cláusula order by hace que las tuplas en el resultado de una consulta aparezcan en un orden determinado.

Seleccione cuántos empleados existen por dependencia y en cuales dependencias hay mas de 2 empleados, ordenado por nombre de departamento.

Select count(emp.id_emp), dep.nombre_depart From empleados emp, departamentos depWhere emp.depart_emp = dep.Id_departGroup by nombre_departHaving count(emp.id_emp)>1Order by dep.nombre_depart;

Resultado:


69

COUNT(EMP.ID_EMP) NOMBRE_DEPART-------------------------------- ---------------------------------------- 2 Ensamble 3 División de Sistemas

Por defecto SQL lista los elementos en orden ascendente (asc). Si queremos en orden descendente especificamos desc después del nombre del campo. Además el ordenamiento puede hacerse sobre múltiples atributos.

Ej.: “Listar la relación empleados completa en orden descendente de salario. Si varios salarios tienen el mismo valor, ordenar en forma ascendente por nombre del empleado”.

SELECT *FROM empleadosORDER BY salario desc, nombre_emp asc

Resultado:ID_EMP NOMBRE_EMP DEPART_EMP SALARIO---------- ---------------------------------- ------------------------------------------1014 Jorge 10 12000001012 Juan 20 12000001013 Pedro 30 11000001016 Cristina 20 10000001011 Pedro 10 10000001015 Valentina 10 1000000

Ej.: Hallar el empleado con el mayor salario.SELECT nombre_emp, salarioFROM empleadosWHERE salario = (SELECT max(salario)

FROM empleados)

Resultado:NOMBRE SALARIO------------------------- ----------------Juan 100000Pedro 100000Luis 100000

Operadores aritméticos

En los siguientes ejemplos se muestra la forma de aplicar cada uno de los operadores.

Sumar 2 (Dos) veces el salario de cada empleado.

Select salario+ salario from empleados;

Resultado:


70

SALARIO+SALARIO--------------------- 2000000 2400000 2200000 2400000 2000000 2000000

Al salario sumado 2 (Dos) veces, restarle $100,000.

Select (salario+ salario)-100000 from empleados;

Resultado:

(SALARIO+SALARIO)-100000----------------------------------------------- 1900000 2300000 2100000 2300000 1900000 1900000

Multiplique el salario por 2 (Dos).

Select (salario)*2 from empleados;

Resultado:

(SALARIO)*2------------------- 2000000 2400000 2200000 2400000 2000000 2000000

El salario multiplicado por 2 (Dos), divídalo entre 5.

Select (salario*2)/5 from empleados;

Resultado:

(SALARIO*2)/5---------------- 400000 480000 440000 480000 400000

400000


71

Operadores Relacionales

Seleccionar todos los registros que tengan salario igual a $1,000,000.

Select * from empleados where salario = 1000000;

Respuesta:

ID_EMP NOMBRE_EMP DEPART_EMP SALARIO----------- ---------------------- ------------------------------------------1011 Pedro 10 10000001015 Valentina 10 10000001016 Cristina 20 1000000

Seleccionar todos los registros que tengan salario mayor que $1,000,000.

Select * from empleados where salario > 1000000;

Respuesta:

ID_EMP NOMBRE_EMP DEPART_EMP SUELDO_EMP----------- ---------------------- ------------------------------------------1012 Juan 20 12000001013 Pedro 30 11000001014 Jorge 10 1200000

Seleccionar todos los registros que tengan salario mayor o igual que $1,000,000.

Select * from empleados where sueldo_emp >= 1000000;

Respuesta:

ID_EMP NOMBRE_EMP DEPART_EMP SUELDO_EMP----------- ------------------------------- ------- -- ---------- --------------------1011 Pedro 10 10000001012 Juan 20 12000001013 Pedro 30 11000001014 Jorge 10 12000001015 Valentina 10 10000001016 Cristina 20 1000000

Operadores Lógicos

Seleccionar todos los registros que tengan salario mayor que $1,000,000 y menor a $2,000,000.

Select * from empleados where salario > 1000000 and salario <2000000;


72

Resultado:

ID_EMP NOMBRE_EMP DEPART_EMP SUELDO_EMP----------- ---------------------- -------------------- --------------------1012 Juan 20 12000001013 Pedro 30 11000001014 Jorge 10 1200000

Otros Operadores

IN

Seleccione los empleados que pertenecen a los departamentos 10 y 20.

Select * from empleadosWhere depart_emp in (‘10’,’20’);

Resultado:

ID_EMP NOMBRE_EMP DEPART_EMP SALARIO---------- ---------------------- ------------------ ---------------------1011 Pedro 10 10000001012 Juan 20 12000001014 Jorge 10 12000001015 Valentina 10 10000001016 Cristina 20 1000000

NOT IN

Seleccione los empleados que no pertenecen a los departamentos 10 y 20.

Select * from empleadosWhere depart_emp not in (‘10’,’20’);

Resultado:

ID_EMP NOMBRE_EMP DEPART_EMP SALARIO------------ ------------------------- ------------------- --------------------1013 Pedro 30 1100000

BETWEEN

Seleccione los empleados que tienen un salario entre $1,000,000 y $1,100,000.

Select * from empleadosWhere sueldo_emp between 1000000 and 1100000;

Resultado:


73

ID_EMP NOMBRE_EMP DEPART_EMP SALARIO---------- ----------------------- --------------------- ---------------------1011 Pedro 10 10000001013 Pedro 30 11000001015 Valentina 10 10000001016 Cristina 20 1000000

LIKE

Seleccione los empleados cuyo nombre inicie por “p”.

Select * from empleadosWhere nombre_emp like (‘P%’);

Resultado:ID_EMP NOMBRE_EMP DEPART_EMP SALARIO

------------ ---------------------- --------------------- ---------------------- 1011 Pedro 10 1000000

1013 Pedro 30 1100000

Utilización de Funciones

Las funciones que se utilizan con frecuencia son:

Numéricas Carácter Retornan valores

numéricos

Grupo Fecha Otras

ABS() CHR() ASCII() AVG() ADD_MONTHS() DECODE()CEIL() INITCAP() INSTR() COUNT() LAST_DAY() NVL()

FLOOR() LOWER() LENGTH() MAX() MONTHS_BETWEEN

USER()

MOD() LPAD() MIN() NEW_TIME()POWER() LTRIM() STDDEV() NEXT_DAY()ROUND() REPLACE() SUM() ROUND()

SIGN() RPAD() VARIANCE() SYSDATE()SQRT() RTRIM() TO_CHAR() TRUNC()

TRUNC() SUBSTR() TO_DATE()UPPER() TO_NUMBER

()


74

Select Avanzados o Subconsultas

Son consultas contenidas dentro de la cláusula WHERE de otra consulta. Las consultas anidadas (subconsultas) pueden retornar un solo valor, múltiples valores, una o más columnas.

Sintaxis:

Select col1,col2,...,col3 from nombre_tableWhere col= (select col from nombre_tabla where condición);

Si la subconsulta retorna:

Una fila, utilizamos el conector “=”.Mas de una fila, utilizamos los conectores ANY, ALL, IN, NOT INANY: Compara un valor con cada valor que retorna la subconsulta.ALL: Compara un valor con todos los valores que retorna la subconsulta.

Pertenencia a un conjunto: SQL se basa en el cálculo relacional para operaciones que permiten probar la pertenencia de tuplas a una relación. El conector IN prueba si se es miembro de un conjunto, donde el conjunto es una colección de valores producidos por una cláusula SELECT. El conector NOT IN prueba la no pertenencia al conjunto.

Para ilustrar esto, considere la consulta: “Seleccionar el nombre de los empleados cuyo salario sea igual al salario de los empleados del departamento 10”.Solución:

Select depart_emp,salario,nombre_emp from empleadosWhere salario IN (select salario from empleados

Where depart_emp = ‘10’);

Resultado:

DEPART_EMP SALARIO NOMBRE_EMP------------------------ ------------------------ ---------- ---------10 1000000 Pedro10 1000000 Valentina20 1000000 Cristina20 1200000 Juan10 1200000 Jorge

Ejercicio: Seleccionar a todos los empleados que tienen un salario igual al salario del empleado Jorge.

Comparación de conjuntos: SQL permite emplear las comparaciones >any, <any, >=any, <=any, <>any e =any el cual es equivalente a IN.

Ej.: Seleccionar los nombres de todos los empleados que tienen un salario mayor al de algún empleado del departamento 10.


75

Select depart_emp,salario,nombre_emp From empleadosWhere salario >any (SELECT salario

FROM empleados WHERE dept_emp = 10)

Resultado:

DEPART_EMP SALARIO NOMBRE_EMP------------------------ ------------------------ ---------- ---------20 1200000 Juan10 1200000 Jorge

La subconsulta genera el conjunto de todos los valores de salario del departamento 10. La comparación >any es verdadera si el valor de salario de la tupla es mayor que al menos un miembro del conjunto de todos los valores de salario del departamento 10.

SQL también admite las comparaciones <all, <=all, >=all, =all, <>all.Si la consulta se modifica: “Seleccionar los nombres de todas los empleados que tienen un salario mayor que todos los salarios del departamento 10”.

El conector >all corresponde a la frase “mayor que todos”:

SELECT depart_emp,salario,nombre_empFROM empleadosWHERE salario >all (SELECT salario

FROM empleados WHERE dept_emp = 10)

Los conectores IN, =any, >all, etc. nos permiten probar un único valor con los miembros de un conjunto completo.Nota: La(s) columna(s) retornadas por la subconsulta deben coincidir en número y tipo con las columnas con las cuales ellas son comparadas.

Operaciones de conjuntos (Unión, Intersección Diferencia)

Para este tipo de operaciones se deben cumplir cierto tipo de restricciones sobre las consultas que ellos operan:

¨ Las consultas deben seleccionar (SELECT) igual número de columnas.¨ Las columnas correspondientes deben ser del mismo tipo.¨ La cláusula ORDER BY sólo puede se usada una (1) vez (al final) en una consulta

que emplee estos operadores.¨ Los nombres de las columnas no pueden ser especificados en la cláusula ORDER

BY. Estas deben ser referidos mediante números, de acuerdo a la posición que ocupen en el SELECT.

Unión


76

El operador UNION retorna sin repetir, las filas comunes y no comunes a todas las consultas.

Ejemplo:

Select depart_emp from empleadosUnionSelect id_depart from departamentos;

Resultado: DEPART_EMP ------------ 10 20 30

Intersección

El operador INTERSECT retorna aquellas filas que sean comunes en todas las consultas.

Ejemplo:

Select depart_emp from empleadosIntersectSelect id_depart from departamentos;

Resultado:

DEPART_EMP ------------ 10 20 30

Minus

El operador MINUS retorna aquellas filas que existen en la primera consulta y que además no existen en la segunda consulta.

Ejemplo:

En el siguiente ejemplo que ilustra el uso del operador MINUS, se observa que aunque aparentemente la primera consulta es igual a la segunda, los resultados en cada caso no se comportan igual.

Select depart_emp from empleadosMinusSelect id_depart from departamentos;


77

Resultado:

ninguna fila seleccionada

Select id_depart from departamentos MinusSelect depart_emp from empleados;

Resultado:

ID_DEPART ----------------- 40

Modificación de la base de datos

El DML de SQL incluye tres operaciones de modificación: Eliminación (Delete), Inserción (Insert), Actualización (Update).

Eliminación: Sólo se pueden suprimir tuplas completas.

Sintaxis:

Delete from nombre_tabla [where condición];

Ejemplo: Borrar los empleados que trabajan en el departamento 20.

Delete from empleados where depart_emp = ‘20’;

Una orden DELETE opera sobre una sola relación. Si queremos eliminar tuplas de varias relaciones debemos usar una orden DELETE para cada relación. La cláusula WHERE puede ser tan compleja como la de un SELECT o podemos tener una cláusula WHERE vacía.

Ejemplo: Borrar todos los registros de la tabla empleados.

Delete from empleados;

Inserción: Permite insertar registros en las tablas.Para insertar datos en una relación, especificamos una tupla que se va a insertar o escribimos una consulta cuyo resultado es un conjunto de tuplas a insertar. Lógicamente los valores de atributos para las tuplas insertadas deben ser miembros del dominio de los atributos y deben tener el número correcto de atributos.

Sintaxis:Insert into nombre_tabla [col1, col2,col3,....colN]values (valor1,valor2,valor3,...,valorN)[consulta]

Ejemplos: Insertar un registro completo a la tabla empleados.


78

Insert into empleados values(‘10201020’,’Pedro Perez’,’10’,1100000,’01’Jan’2000’);

Insertar un registro a empleados con algunos campos.

Insert into empleados (id_emp,nombre_emp)values(‘10201020’,’Pedro Perez’);

Insertar los registros resultantes de una consulta.

Insert into empleados select identif,nombre from empl where estado = ‘1’;

Update: Permite modificar la información almacenada en los campos de las tablas.

Sintaxis:Update nombre_tabla set campo1 = valor ó consulta,campo2=valor ó exp[where condicion];

Ejemplos: Incremente el salario de los empleados del departamento 10, en un 10%.

Update empleados set salario = salario + salario * .10Where depart_emp = ‘10’;

Incrementar los salarios de los empleados en un 5%, si son menores de 1100000.

UPDATE empleados SET salario = salario * 1.05WHERE salario < 1100000

Creación de otras estructuras de datos

Vistas: Son subconjuntos lógicos de datos, procedentes de una tabla o combinación de ellas.No contienen datos físicamente; éstos son derivados de tablas subordinadas. Se crean por seguridad y conveniencia. Sintaxis:

CREATE VIEW nom_v [(alias [, alias ...] ]AS consultaWITH CHECK OPTION

La cláusula WITH CHECK OPTION asegura que actualizaciones e inserciones ejecutadas a través de la vista no crearán registros por fuera del alcance de ella.

Ej.: Crear una vista con los empleados del departamento 10.

CREATE VIEW emp_10AS SELECT * from empleadosWHERE depart_emp = ‘10’;


79

Para ver el contenido de la vista tenemos:SELECT * FROM emp_10;

así creada deja insertar y actualizar tuplas para la tabla empleados en cualquier departamento.

Ej.: Crear una vista que contenga todas las columnas de la tabla empleados para el departamento 20, utilizando la cláusula With check option.

CREATE VIEW emp_20AS SELECT * FROM empleadosWHERE depart_emp = ‘20’;WITH CHECK OPTION

Si se intenta modificar un dato, por Ej. el salario para el empleado Jorge:UPDATE emp_20SET salario = 1500000WHERE nombre_emp = ‘Jorge’

la actualización no es admitida porque la vista sólo puede ver empleados del departamento 20.

NOTA: Se pueden actualizar e insertar datos a una tabla a través de una vista si ésta es creada a partir de una tabla, pero no de un join de tablas.

Para borrar una vista se utiliza la orden de SQL :DROP VIEW nom_vista

Secuencias: Son estructuras de datos Oracle que permiten generar automáticamente valores para campos secuenciales.Se pueden utilizar en la instrucción Insert para generar el siguiente valor del campo, referenciando el nombre de la secuencia. Ej.: Si para el campo id_depart en la tabla departamentos se hubiese creado una secuencia llamada s_id_depart, la inserción de un nuevo departamento se haría así:

INSERT INTO departamentos (id_depart, nombre_depart)VALUES (s_id_depart.NEXTVAL, ‘Reparacion’);

Para saber cuál es el valor actual de una secuencia puedo averiguarlo siguiendo los siguientes pasos:1- SELECT nom_seq.NEXTVAL

FROM dual

2- SELECT nom_seq.CURRVALFROM dual

Para crear una secuencia se utiliza la siguiente orden:CREATE SEQUENCE nom_seq[increment by n ][start with n]


80

[{maxvalue n | nomaxvalue }][{cycle | nocycle}][{cache n | nocache }]

Si increment by y start with no se especifican, por defecto toman el valor de 1.nomaxvalue especifica un máximo valor de 10 elevado a la 27.

Ej.: Crear una secuencia para la tabla Departamentos comenzando en 10 con incrementos de 10 .

CREATE SEQUENCE id_departminvalue 10maxvalue 1000increment by 10start with 50nocachenocycle

Para borrar una secuencia creada se utiliza la orden:DROP SEQUENCE nom_seq;


81

Documents

Fundamentos SQL