• Author / Uploaded
• franz

Citation preview

INTRODUCCIÓN: EL LENGUAJE DE CONSULTA SQL..............................................................................3 LENGUAJE DE DEFINICIÓN DE DATOS (SQL ESTÁTICO).......................................................................3 CREATE TABLE.......................................................................................................................................................3 Ejemplo........................................................................................................................................................................4

CREATE DOMAIN...................................................................................................................................................5 Ejemplo........................................................................................................................................................................5

CREATE ASSERTION..............................................................................................................................................5 Ejemplo........................................................................................................................................................................5

CREATE VIEW..........................................................................................................................................................5 Ejemplo........................................................................................................................................................................6

CREATE INDEX .......................................................................................................................................................6 Ejemplo........................................................................................................................................................................6

CREATE SYNONYM...............................................................................................................................................6 ALTER TABLE (SQL DE DB2).................................................................................................................................7 Ejemplo........................................................................................................................................................................7

DROP TABLE (SQL DE DB2)...................................................................................................................................7 Ejemplo........................................................................................................................................................................7

DROP VIEW (SQL DE DB2).....................................................................................................................................7 Ejemplo........................................................................................................................................................................7

DROP INDEX (SQL DE DB2)...................................................................................................................................7 Ejemplo........................................................................................................................................................................7

DROP SYNONYM....................................................................................................................................................8 LENGUAJE DE MANIPULACIÓN DE DATOS (SQL DINÁMICO)..............................................................8 SELECT......................................................................................................................................................................8 Cláusula GROUP BY (agrupar por)..............................................................................................................9 Ejemplo........................................................................................................................................................................9

Cláusula HAVING (con).................................................................................................................................9

Ejemplo........................................................................................................................................................................9

Cláusula ORDER BY(Ordenar por).............................................................................................................10 SELECT…INTO......................................................................................................................................................10 Ejemplo......................................................................................................................................................................10

UPDATE...................................................................................................................................................................11 DELETE....................................................................................................................................................................11 INSERT.....................................................................................................................................................................12 Ejemplos....................................................................................................................................................................12

FUNCIONES DE AGREGADOS (DE COLUMNAS)........................................................................................12 FUNCIÓN AVG (MEDIA ARITMÉTICA ).............................................................................................................................13 Ejemplos....................................................................................................................................................................13

FUNCIÓN COUNT (CUENTA)........................................................................................................................................13 Ejemplo......................................................................................................................................................................13

FUNCIÓN MAX............................................................................................................................................................14 Ejemplos....................................................................................................................................................................14

FUNCIÓN MIN.............................................................................................................................................................14 Ejemplos....................................................................................................................................................................14

FUNCIÓN SUM............................................................................................................................................................14 Ejemplos....................................................................................................................................................................15

TIPOS DE DATOS EN SQL....................................................................................................................................15 TIPO................................................................................................................................................................15

SQL3:EL NUEVO SQL.............................................................................................................................................17 PARTE 1ª: MARCO DE TRABAJO ........................................................................................................................................17 PARTE 2ª: FUNDACIÓN...................................................................................................................................................17 PARTE 3ª:SQL/CLI ....................................................................................................................................................17 PARTE 4ª: SQL/PSM (PERSISTENT STORED MODULES )..................................................................................................18 Extensiones para lenguaje procedural ......................................................................................................................18 Procedimientos almacenados y múltiples instrucciones..........................................................................................18 Procedimientos externos y llamadas a procedimientos............................................................................................18

PARTE 5: SQL/BINDINGS..............................................................................................................................................18 PARTE 6: SQL/XA......................................................................................................................................................18 Apuntes de SQL Pág. 1

PARTE 7ª:SQL/TEMPORAL ............................................................................................................................................19 SQL/MM (MULTIMEDIA )............................................................................................................................................19 EJERCICIOS DE SQL RESUELTOS...................................................................................................................20 Creación de tablas.....................................................................................................................................20 Solución.....................................................................................................................................................................20 Consultas simples................................................................................................................................21 Consultas de reunión (con filtrado WHERE) ....................................................................................21 SELECT * FROM PIEZAS WHERE P_NOMBRE LIKE 'B*';...............................................................................23 SELECT * FROM PROVEEDORES WHERE SITUACION < (SELECT MAX(SITUACION) AS MAXIMO FROM PROVEEDORES);............................................................................................................................................24 UPDATE PIEZAS SET COLOR='AMARILLO',PESO=PESO+5,P_CIUDAD='CUBA' WHERE P_CODIGO='P2';...........................................................................................................................................................26

EJERCICIOS PROPUESTOS.................................................................................................................................27 Creación de la base de datos y las tablas en MySQL.......................................................................27 Consultas sencillas...............................................................................................................................28 Consultas con varias tablas.................................................................................................................28 Funciones de agregados (COUNT, SUM, etc)..................................................................................29 Consultas diversas...............................................................................................................................29 Subconsultas(SELECT dentro de SELECT)......................................................................................29 Exists....................................................................................................................................................30 Union....................................................................................................................................................30 Operaciones de actualización..............................................................................................................30

Apuntes de SQL Pág. 2

INTRODUCCIÓN: EL LENGUAJE DE CONSULTA SQL. La mayor parte de BD actuales son relacionales, mas específicamente sistemas SQL. La primera definición del SQL cuyo nombre original era SEQUEL fue realizada por Chamberlain y otros de IBM en el Research Laboratory de San José. La aplicación práctica construida a partir de esta definición se llamó System R. Finalmente el ANSI y la ISO tomaron el SQL como norma y lo definieron como un standard. El SQL tiene dos partes bien diferenciadas: las sentencias que se utilizan par definir los datos que se denominan LDD y las que se emplean para manipularlos que se denominan LMD. En este capitulo se verá el SQL (ISO 9075 DE 1992). Todos los sistemas relacionales actuales utilizan dicho lenguaje con algunas modificaciones o añadidos. Por tanto en este tema estudiaremos este lenguaje.

LENGUAJE DE DEFINICIÓN DE DATOS (SQL estático). Esta constituido básicamente por las siguientes sentencias: CREATE TABLE, CREATE VIEW, CREATE DOMAIN, CREATE ASSERTION, CREATE INDEX, ALTER TABLE, DROP TABLE, DROP VIEW, DROP INDEX. En los siguientes apartados describiremos el lenguaje SQL empleando la notación BNF extendida. Esta notación utiliza unos símbolos que no son propios del SQL y cuyo significado es el siguiente: [] |

… {} ::=

Indican opcionalidad. Lo que se sitúe dentro de los corchetes puede o no escribirse. Indica una alternativa. Se debe elegir entre lo que hay a la derecha de la barra o la izquierda. Lo que hay entre estos corchetes es un símbolo no terminal, es decir que requiere definición. Lo que hay a la izquierda de los puntos suspensivos puede repetirse cero o más veces. Agrupa elementos Este símbolo se emplea para definir .

En la descripción de las instrucciones de SQL todos los símbolos escritos en negrita se consideran terminales y por tanto propios del SQL. CREATE TABLE. Se emplea para crear una tabla de la base de datos. Esta instrucción la tienen todos los SGBD relacionales aunque con modificaciones respecto de la SQL ISO-92

CREATE [ {GLOBAL | LOCAL} TEMPORARY] TABLE Apuntes de SQL Pág. 3

( | [, | ]…);

::= {|} [] […] ::= CHAR[ACTER] | SMALLINT | INTEGER | DECIMAL | NUMERIC | REAL | FLOAT | DOUBLE PRECISION | CHARACTER VARYING | DATE | TIME | BIT | TIMESTAMP | INTERVAL | BIT VARYING

::= DEFAULT {| | USER | SYSTEM | NULL } ::= NOT NULL | |

|

::= UNIQUE | PRIMARY KEY ( [, …] ) NOTA: UNIQUE se emplea para especificar las claves alternativas FOREIGN KEY ( [, …] ) REFERENCES [ ( [, …] ) ] [ { ON UPDATE | ON DELETE } CASCADE | SET NULL | SET DEFAULT ] ::= ::= CHECK () NOTA: se utiliza para expresar una condición que deben cumplir un conjunto de atributos de la tabla ::= []

| |

Ejemplo CREATE TABLE PROFESOR ( Cod_profe Char(5) PRIMARY KEY, Nombre Char(15) NOT NULL, DNI Int NOT NULL, Dirección Char(25) NOT NULL, Materia Char(10), Tipo Char NOT NULL, Cód_profe_e Char(5), Cód_area Char(5), NOT NULL, Catedrático Bit, FOREIGN KEY (Cód_profe_e) REFERENCES PROFESOR ON UPDATE CASCADE, FOREIGN KEY (Cód_area) REFERENCES AREA ON UPDATE CASCADE, Apuntes de SQL Pág. 4

CHECK( ( (Cód_prof_e = NULL) And (Catedrático =1)) NULL) And (Catedrático =0)) CHECK (Cód_profe Cód_profe_e) );

Or

((Cód_prof_e = NOT

CREATE DOMAIN Se utiliza para crear un dominio, es decir un conjunto de valores que puede tomar un atributo o campo de una tabla. Es algo parecido a los tipos de datos definidos por el usuario en los lenguajes de programación. Esta instrucción no la soportan actualmente todos los SGBD, por ejemplo no la soporta Oracle ni SQLbase. CREATE DOMAIN [AS] [] [] ::= [CONSTRAINT ] [] ::= {INITIALLY DEFERRED | INITIALLY INMEDIATE} [[NOT] DEFERRABLE] | [NOT] DEFERRABLE] {INITIALLY DEFERRED | INITIALLY INMEDIATE}

Ejemplo CREATE DOMAIN C_PER CREATE DOMAIN C_BECA

Char(5); Char(5);

CREATE ASSERTION Se utiliza para establecer restricciones en la base de datos. Por ejemplo para que el sistema compruebe las cardinalidades mínimas o las condiciones que deben cumplir un conjunto de atributos pertenecientes a varias tablas. No está disponible en todos los SGBD CREATE ASSERTION [] Ejemplo Suponemos una base de datos que recoge información de cursos impartidos por profesores. Entonces para indicar al sistema que cada curso puede ser impartido por menos de cuatro profesores escribiríamos, una vez creadas las tablas de la base de datos: CREATE ASSERTION Profesor_curso CHECK NOT EXIST ( SELECT COUNT(*) FROM IMPARTE GROUP BY COD_CURSO HAVING COUNT(*)>=4)

CREATE VIEW Crea una vista de una tabla. Una vista es una tabla virtual que no se sustenta en sus propios datos almacenados y separados físicamente. Lo que se almacena es la definición de la vista en términos de otras tablas en el diccionario de datos del sistema. CREATE VIEW [( [,]…) Apuntes de SQL Pág. 5

AS [WITH CHECK OPTION] La cláusula WITH CHECK OPTION indica que las operaciones de modificación e inserción realizadas con la vista deben verificarse para garantizar que toda fila modificada o insertada cumpla la condición definida en . Ejemplo CREATE VIEW Buenos_proveedores AS SELECT S#, SITUACION, CIUDAD FROM PROVEEDORES WHERE SITUACION>15 WITH CHECK OPTION; Esto define una vista de la tabla PROVEEDORES denominada Buenos_proveedores de la cual sólo se muestran filas compuestas por los campos S#, SITUACION, CIUDAD donde SITUACION>15. Cada vez que se intente modificar o introducir una fila en la tabla de proveedores a través de esta vista se comprobará si el campo SITUACION tiene un valor mayor de 15 denegándose la operación en caso contrario . CREATE INDEX Esta sentencia no forma parte del estándar SQL92 sin embargo la incluyen la mayoría de los SGBD tales como Oracle, Informix, SQLServer, DB2, Access. En concreto aquí veremos la del SQL de DB2. Nos permite crear un índice para una tabla basado en una o varias de sus columnas. CREATE [UNIQUE] INDEX ON ( [ASC | DESC] [, [ASC | DESC]]…) [CLUSTER] • La cláusula CLUSTER indica que se trata de un índice de agrupamiento, es decir que el sistema sitúa los registros relacionados de forma lógica lo más próximos posibles unos de otros en el soporte. • La cláusula UNIQUE se emplea para indicar que no se permite que dos registros de la tabla indicada tengan el mismo valor en el campo o combinación de campos índices. Esto sirve para que se cumpla la unicidad de las claves primarias en el sistema DB2. Ejemplo CREATE UNIQUE INDEX XS ON PROVEEDORES (S#); CREATE UNIQUE INDEX XP ON PIEZAS (P#) CLUSTER; CREATE SYNONYM Esta sentencia no forma parte del estándar SQL92 sin embargo la incorporan la mayoría de las implementaciones. Crea un nombre de alias para una tabla o vista. El alias puede ser útil para proporcionar un nombre corto para un nombre de tabla largo o para tratar la tabla a través de su alias como si fuese una copia lo cual nos puede servir para unir una tabla consigo misma. CREATE SYNONYM FOR {|mostrar de la tabla de piezas el color y la ciudad de todas SELECT P_CIUDAD, COLOR (DISTINT) FROM PIEZAS ;

Apuntes de SQL Pág. 8

>mostrar las piezas rojas y verdes de una ciudad SELECT COLOR,P_CIUDAD FROM PIEZAS WHERE COLOR=’VERDE’ OR COLOR=’ROJO’; Cláusula GROUP BY (agrupar por) GROUP BY { | },… Esta cláusula especifica las columnas usadas para formar grupos con las filas devueltas por la sentencia SELECT. Dentro de cada grupo todas las filas tienen el mismo valor en la o las columnas de GROUP BY. Si está precedida de la cláusula WHERE el sistema agrupa las filas después de aplicar la cláusula WHERE. Todas las columnas de la de la sentencia SELECT deben ser funciones de columna (AVG, COUNT, MAX, MIN, SUM) o formar parte de la cláusula GROUP BY. Ejemplo Obtener la cantidad total suministrada de cada pieza SELECT P#, SUM(CANT) FROM SP GROUP BY P# Resultado:

P# P1 P2 P3 P4 P5 P6

Tabla SP (P1, 100) (P1, 100) (P1, 100) (P1, 100) (P1, 100) (P1, 100) …

SUM(CANT) 600 1000 400 500 500 100

Obsérvese que en la tabla de suministros SP a lo mejor hay seis filas de P1 coda una con una cantidad de 100 pero el resultado consiste en agruparlas por el código de la pieza. Cláusula HAVING (con) HAVING Se utiliza para indicar que los grupos de filas agrupados por GROUP BY que se muestran deben cumplir la . Por lo tanto si se especifica HAVING en una sentencia SELECT deberá haberse especificado también GROUP BY o bien todas las columnas de la de SELECT deben formarse con funciones de columna. HAVING es a los grupos lo que WHERE es a las filas.

Ejemplo Obtener los códigos de las piezas que son suministradas por más de un proveedor. Apuntes de SQL Pág. 9

SELECT P# FROM SP GROUP BY P# HAVING COUNT(*) >1; Cláusula ORDER BY(Ordenar por) ORDER BY {{} [ASC | DESC],… } Especifica el orden en que una sentencia SELECT devuelve las filas. Las filas se pueden ordenar por una columna de forma ascendente ASC o descendente DESC. Si se especifican más de una columna en primer lugar se ordenan según la primera columna especidicada y dentro de esta la segunda y dentro de la segunda la tercera y asi sucesivamente. Ejemplo Seleccionar los clientes cuya primera ficha de negocio fue 1988 y mostrarlos en orden ascendente por el código postal: SELECT Nombre_Cliente, Dirección FROM CLIENTES WHERE Primera_fecha =’1988’ ORDER BY Código_postal ASC; SELECT…INTO Crea una tabla lógica de una fila a lo sumo y asigna los valores de las columnas de la fila a unas variables host especificadas. SELECT [ALL | DISTINCT ] INTO {:},… FROM { { | } []},… [WHERE ] [GROUP BY { | },…] [HAVING ] NOTAS: • Esta sentencia se utiliza sólo dentro de un programa de aplicación. La palabra clave EXEC SQL debe preceder a esta sentencia. • Si existe más de una fila como resultado de la sentencia entonces segenera un error • Los tipos de las variables host deben coincidir con los de los campos de las columnas especificadas en . Ejemplo En un programa C para almacenar el número de precios diferentes de artículos de un inventario actual en la variable declarada en el programa como int número_precios escribiríamos: … EXEC SQL SELECT COUNT (DISTINCT COSTE) INTO: número_precios Apuntes de SQL Pág. 10

FROM INVENTARIO WHERE CANT >0; … UPDATE Modifica las columnas seleccionadas de una tabla en todas las filas que satisfacen una condición de búsqueda. UPDATE { | } SET { = { | NULL }},… [WHERE ] Ejemplo: Modifica sólo las piezas cuyo código es P2 actualizando los campos de color, peso y ciudad. UPDATE PIEZAS SET COLOR=‘AMBAR’, PESO=PESO+5, CIUDAD=NULL WHERE P# = ‘P2’; UPDATE PIEZAS SET P_CIUDAD=’PARIS’ WHERE P_CODIGO=’P4’ OR P_CODIGO=’P6’; SI QUEREMOS A LAS PIEZAS AZULES FABRICADAS EN PARIS INCREMENTARLE 4 UNIDADES DE PESO: UPDATE PIEZAS SET PESO=PESO+4 WHERE COLOR=’AZUL’ AND P_CIUDAD=’PARIS’; SUPONEMOS Q VAMOS A CAMBIAR EL COLOR D TODAS LAS PIEZAS CUYO PESO SEA MAYOR IGUAL Q 17 A AMARILLO: UPDATE PIEZAS SET COLOR=’AMARILLO’ WHERE PESO>=17;

DELETE Borra una o más filas de una tabla o vista. Si no se especifica la cláusula WHERE, se borran todas las filas de la tabla señalada en la cláusula FROM. Si se especifica la cláusula WHERE sólo serán borradas aquellas filas que hagan la condición de búsqueda verdadera. DELETE FROM { | } [] [WHERE ] Ejemplo: Borrar todas las filas de tabla SP cuya CANT sea mayor que 30. Apuntes de SQL Pág. 11

DELETE FROM SP WHERE CANT > 30;

INSERT Inserta una o más filas nuevas dentro de la tabla o vista especificada. Cuando se utilice la cláusula VALUES sólo se insertará una fila. Si se utiliza una el número de filas insertadas será igual al devuelto por la . INSERT INTO { | } [({},…)] { VALUES ({ | : | NULL },…) | } Ejemplos Insertar en la tabla de SUMINISTROS una nueva fila. INSERT INTO SUMINISTROS(S#, P#, CANT) VALUES (‘S20’, ‘P20’, 1000); En este ejemplo se insertan unas filas obtenidas de la tabla de SUMINISTROS en una tabla temporal TEMP. INSERT INTO TEMP (P#, CANTTOTAL) SELECT P#, SUM(CANT) FROM SUMINISTROS GROUP BY P#;

FUNCIONES DE AGREGADOS (DE COLUMNAS) SQL ofrece una serie de funciones que actúan sobre el total de valores en una columna de alguna tabla y producen un sólo valor como resultado. Estas funciones son la suma (SUM), la media (AVG), el máximo de un conjunto de valores (MAX), el mínimo (MIN) y el número de valores de una columna o cuenta (COUNT). Estas funciones se utilizan en consultas SELECT o en subconsultas. Una subconsulta es una consulta SELECT dentro de otra SELECT. Por ejemplo: SELECT JNOMBRE FROM J WHERE J# IN (SELECT J# FROM SPJ WHERE S#=’S1’);

Subconsulta

Apuntes de SQL Pág. 12

Función AVG (media aritmética) La sintaxis es: AVG({[ALL] | DISTINCT }) En una consulta SELECT o una subconsulta esta función crea una columna cuyo valor es el promedio numérico de los valores de la o del . Se puede usar la cláusula GOUP BY para crear el promedio de cada grupo de filas seleccionadas en las tablas o vistas subyacentes. Si se especifica la cláusula DISTINCT entonces los valores duplicados en la columna no se tiene en cuenta a la hora de calcular el promedio. La cláusula ALL si tiene en cuenta todos los valores, aunque esta es la opción por defecto. Ejemplos Obtener el promedio del coste del inventario de artículos sin contar aquellos que no están en stock. SELECT AVG(CANTIDAD*COSTE) FROM INVENTARIO WHWRE CANTIDAD >0; Calcular el promedio de la cantidad debida a cualquier vendedor que envió un acuenta en octubre. SELECT AVG(CANTDEBIDA) FROM CUENTASPAGAR WHER MESFACTURA = 10 AND CANTDEBIDA >0; Función COUNT (Cuenta) COUNT({ * | DISTINCT }) En una consulta SELECT o subconsulta esta función crea una columna cuyo valor es igual al número de filas de la tabla resultado o al número de valores distintos en . Se puede utilizar la cláusula GROUP BY para crear una cuenta por cada grupo de filas. Si se especifica la cláusula DISTINCT entonces lo valores duplicados se eliminan de la cuenta. Ejemplo Obtener el número de clientes de cada provincia que hicieron negocio en una fecha posterior a 1985. SELECT COUNT(*) FROM CLIENTES Apuntes de SQL Pág. 13

WHERE PRIMERFECHA > 1985 GROUP BY PROVINCIA; Función MAX MAX({[ALL] | DISTINCT }) En una consulta o subconsulta crea una columna cuyo valor es el valor máximo de o . Se puede utilizar la cláusula GROPU BY para obtener el máximo de cada grupo de filas seleccionadas en las tablas o filas subyacentes Ejemplos Calcular la máxima cantidad debida a cualquier vendedor que envió una factura en Octubre. SELECT MAX (CANTDEBIDA) FROM CUENTASPAGAR WHERE MESFACTURA =10; Encontrar el artículo del inventario con el máximo coste SELECT MAX(CANT * COSTE) FROM INVENTARIO; Función MIN MIN({[ALL] | DISTINCT }) Es igual que MAX pero en este caso se calcula el valor mínimo. Ejemplos Obtener la mínima cantidad que se le debe a cualquier vendedor que envió una factura en Octubre. SELECT MIN (CANTDEBIDA) FROM CUENTASPAGAR WHERE MESFACTURA =10; Encontrar el mínimo coste de cualquier artículo del inventario SELECT MIN(CANT * COSTE) FROM INVENTARIO; Función SUM SUM({[ALL] | DISTINCT }) Apuntes de SQL Pág. 14

En una tabla lógica devuelta por una consulta o subconsulta crea una columna cuyo valor es la suma numérica de los valores de o . Se puede utilizar la cláusula GROUP BY para obtener la suma de cada grupo de filas.

Ejemplos Obtener la cantidad total debida a todos los vendedores que enviaron una factura en Octubre. SELECT SUM(CANTDEBIDA) FROM CUENTASPAGAR WHERE MESFACTURA = 10; Obtener el total del coste del ineventario de artículos SELECT SUM(CANT * COSTE) FROM INVENTARIO;

TIPOS DE DATOS EN SQL En SQL92 son los siguientes (se debe consultar los tipos de datos para sistemas especificos como Oracle, DB2, etc): ::= CHAR[ACTER] | SMALLINT | INTEGER | DECIMAL | NUMERIC | REAL | FLOAT | DOUBLE PRECISION | CHARACTER VARYING | DATE | TIME | BIT | TIMESTAMP | INTERVAL | BIT VARYING

TIPO CHAR[ACTER](n) CHARACTER VARYING

DESCRIPCIÓN Cadena de caracteres de longitud fija n Cadena de caracteres de longitud variable. En las implementaciones reales se suele indicar la longitud máxima. Así en DB” sería VARCHAR(n). Número entero en el rango –32.768 a 32.767 Número entero con signo con un rango de – 2.147.483.648 a 2.147.483.647 y sin signo de 0 a 4.294.967.295 Decimal empaquetado con un total de p dígitos de los cuales q hay a la derecha del punto decimal. Igual que DECIMAL Número de punto flotante con una precisión de p dígitos binarios Igual que FOLAT pero con una precisión definida por la implementación. Igualque REAL pero con el doble de precisión. Fecha en formato aaaammdd Hora en formato hhmmss “Marca de tiempo”. Es una combinación de fecha y hora con una precisión de microsegundos. Expresa diferencias entre fechas u horas Un bit

SMALLINT INTEGER DEC[IMAL](p,q) NUMERIC(p,q) FLOAT[(p)] REAL DOUBLE PRECISION DATE TIME TIMESTAMP INTERVAL BIT

Apuntes de SQL Pág. 15

BIT VARYING

Cadena de bits de longitud variable.

Apuntes de SQL Pág. 16

SQL3:El nuevo SQL1 La versión actual estándar del SQL, la SQL/92, tiene su origen en la norma ISO 9075 de Julio de 1992. Actualmente la ISO y ANSI están trabajando en una nueva versión del SQL denominada SQL3 cuyo objetivo final es convertir al SQL en un lenguaje computacional completo para la definición y el manejo de objetos complejos. Esto incluye jerarquías de generalización y especialización, herencia múltiple, tipos definidos por el usuario, triggers, aserciones, soporte para sistemas basados en el conocimiento, expresiones recursivas de consultas, herramientas de administración de datos, herencia, polimorfismo, encapsulamiento, capacidades multimedia, etc. En 1993 la ISO y ANSI deciden crear múltiples comités de normalización para definir el SQL3. Cada uno de estos comités se encarga de un aspecto del nuevo SQL. Estos comités son los siguientes. Parte 1ª: marco de trabajo. Es una descripción no técnica de cómo es la estructura del nuevo SQL3 Parte 2ª: Fundación. Es el núcleo de la nueva especificación incluyendo nuevos tipos de datos, triggers, subtablas, tipos de datos abstractos(ADT), capacidades de orientación de objetos, capacidades para definir operaciones complejas, almacenamiento de operaciones complejas en la base de datos, llamadas a procedimientos externos. Como vemos todo está muy relacionado con los modelos orientados a objetos. Por este motivo la mayor parte del trabajo se dedica a redefinir un modelo de objetos SQL-3 compatible con el modelo de objetos propuesto por el ODMG ( Object-oriented Database consortium). Un tipo de dato abstracto consiste en una colección de datos junto con unas operaciones que pueden realizarse sobre ellos y todo ello encapsulado y cuyos elementos pueden tener una visibilidad pública, privada o protegida. En los lenguajes de programación orientada a objetos TAD es una clase. En el nuevo SQL se trataría de definir una tabla junto con las operaciones que se puedan realizar sobre ella. Los atributos de la tabla serían los datos miembro y las operaciones serían las funciones miembro o métodos. La herencia permitiría así crear subtablas. La norma internacional debería estar terminada para mitad de 1999. Parte 3ª:SQL/CLI Es una interfase de programación a nivel de llamadas (Call Level Innterface) para bases de datos SQL diseñada para que las aplicaciones puedan acceder a las bases de datos. Se pretende normalizar: • Una implementación independiente de la interfase de programación a nivel de llamadas para acceso a bases de datos SQL.

1

Este apartado ha sido sacado de la dirección de internet

http://www.jcc.com/SQLPages/jccs_sql.html que recoge el estado actual del SQL y cuya última

actualización fue el 2 de Diciembre de 1999 Apuntes de SQL

Pág. 17

• •

Herramientas cliente-servidor para fácil acceso a bases de datos a través de librerías de enlace dinámico. Soporte y fomento de un rico conjunto de herramientas cliente-servidor.

Este comité ha trabajado con gran rapidez y el estándar es el ISO/IEC 9075-3 terminado en 1995 y actualmente está trabajando para dar soporte a las nuevas características del SQL3. Parte 4ª: SQL/PSM (Persistent Stored Modules) Esta parte se dedica a incorporar al SQL extensiones para lenguaje procedural, procedimientos almacenados y múltiples instrucciones, procedimientos externos y llamadas a procedimientos. Extensiones para lenguaje procedural Consiste en incorporar al SQL características típicas de los lenguajes de programación estructurados: instrucciones de control de flujo: If-then-else, bucles, excepciones, instrucciones case, bloques Begin-end, declaración de variables, etc Procedimientos almacenados y múltiples instrucciones Esto tiene grandes ventajas en los entornos cliente-servidor en los siguientes ámbitos: • Ejecución ya que un procedimiento almacenado puede ejecutar múltiples instrucciones SQL reduciendo así la interacción de la red con el cliente. • Seguridad ya que un usuario puede dar derecho para llamar a un procedimiento almacenado que actualice una tabla o conjunto de tablas pero denegar el derecho para actualizar las tablas directamente sin el procedimiento. • Código compartido ya que el código de un procedimiento almacenado no tiene que ser escrito y depurado para cada cliente que accede a la base de datos. • Control ya que el procedimiento almacenado proporciona un único punto de definición y control para la lógica de la aplicación. Procedimientos externos y llamadas a procedimientos Una de las capacidades mas deseadas es permitir la llamada a funciones y procedimientos externos escritos por el usuario. El objetivo es permitir que las bases de datos puedan utilizar un rico y numeroso conjunto de funciones definidas por el comité estándar. En la actualidad aún no está terminada esta parte de la norma. Parte 5: SQL/Bindings Esta es la parte obligatoria del SQL compuesta por el LDD o parte estático de definición de datos y la parte dinámica de la manipulación de datos LMD. En la actualidad hay que resolver la gran variedad de usos que los diferentes sistemas de bases de datos relacionales hacen tanto de la parte estática como dinámica. Por otro lado el SQL-92 tiene una correspondencia en los tipos de datos con los lenguajes de programación tradicionales pero no con los orientados a objetos que hay que resolver también. Parte 6: SQL/XA Este comité pretende normalizar una Interfase de Programación de Aplicaciones (API) entre un gestor de transacciones global y un gestor de recursos SQL. La idea es que el gestor de recursos de SQL tenga soporte para el protocolo de compromiso de dos fases. El compromiso Apuntes de SQL Pág. 18

de dos fases se basa en el avance al unísono de todos los agentes que intervienen en una transacción o el retroceso al unísono de esos agentes si la transacción no puede realizarse. En concreto se trabaja para especificar los parámetros de entrada y salida en términos de tipos de datos SQL para las funciones de dicho protocolo desarrollado por X/Open. Parte 7ª:SQL/Temporal Se trata de añadir capacidades relacionadas con el tiempo al SQL. La idea es que se puedan realizar consultas a las bases de datos no sólo en el estado o momento actual sino en estados previos de tiempo. Hay varias soluciones para que el nuevo SQL contemple la dimensión temporal: • Añadir dos nuevos atributos INICIO y FINAL a las relaciones base que indiquen respectivamente los momentos en los cuales una tupla se inserta y elimina en el sentido lógico y no físico. Por ejemplo, supongamos que se inserta un nuevo proveedor de código S6 en el momento t1, entonces se creará una nueva tupla para S6 con NINICIO=t1 y FINAL=”infinito”. Si después en el instante t2 la situación (el atributo SITUACION) del proveedor S6 cambia se modificaría la primera tupla de S6 haciendo FINAL= t2 y se insertaría otra nueva tupla para S6 con INICIO=t2 y FINAL= “infinito”. • Mediante relaciones anidadas (subtablas) en las cuales cada tupla de una relación base incluyera un atributo especial, que sería la relación anidada o subtabla que definiese la historia de esa tupla. Se trata por tanto de ponerse de acuerdo en el modelo a elegir. SQL/MM (Multimedia) Intenta normalizar librerías de clase para ciencias e ingeniería, procesamiento de documentos, métodos para manejar objetos multimedia tales como imágenes, sonido, animación, música y vídeo. A su vez este comité ha sido dividido en 4 partes cada una de las cuales trata un aspecto diferente del SQL/MM.

Apuntes de SQL Pág. 19

EJERCICIOS DE SQL RESUELTOS Creación de tablas 1.- A continuación se presenta una muestra de valores de una base de datos compuesta por tres tablas de proveedores, proyectos y piezas. Los proveedores (tabla S) , las piezas (tabla P) y los proyectos (tabla J) se identifican respectivamente por un código de proveedor (S#, un código de pieza (P#) y un número o código de proyecto (J#). El significado de una fila de la tabla de envis (tabla SPJ) es que el proveedor con código S# envía la pieza con código P# al proyecto con código J# en la cantidad especificada en CANTIDAD. La combinación S#P#J# identifica de forma única cada fila de la tabla de envíos. Escribir un conjunto de sentencias CREATE TABLE de SQL92 para esta base de datos. PCODI GO P1 P2 P3 P4 P5 P6

PNOMB RE Tuerca Perno Birlo Birlo Leva Engrane

COLO R Rojo Verde Azul Rojo Azul Rojo

PESO

PCIUDAD

12 17 17 14 12 19

Londres París Roma Londres París Londres

S# S1 S2 S3 S4 S5

SNOMBRE Juan Antonio María Susana Felipe

SITUACION 20 10 30 20 30

J# J1 J2 J3 J4 J5 J6 J7

JNOMBRE Clasificador Perforadora Lectora Consola Terminal Cinta Impresora

JCIUDAD París Roma Atenas Atenas Londres Oslo Londres

SCIUDAD Londres París París Londres Atenas

Solución A) Sin dominios. B) Con dominios.

Apuntes de SQL Pág. 20

S# S1 S1 S2 S2 S2 S2 S2 S2 S2 S2 S3 S3 S4 S4 S5 S5 S5 S5 S5 S5 S5

P# P1 P1 P3 P3 P3 P3 P3 P3 P3 P5 P3 P4 P6 P6 P2 P2 P5 P5 P1 P3 P4

J# J1 J4 J1 J2 J3 J4 J5 J6 J7 J2 J1 J2 J3 J7 J4 J2 J5 J7 J4 J4 J4

CANTIDAD

200 700 800 200 100 100 500 300 500 150 125 200 200 300 8000 500 300 700 900 100 200

Consultas simples 2.- Obtener para todas las piezas, el número de pieza y su peso en gramos ( los pesos se dan en libras en la tabla P. Una libra = 454 gramos). SELECT P_CODIGO PESO * 454 FROM PIEZAS ; 3.- Obtener los datos completos de todos los proveedores SELECT * FROM PROVEEDORES; SELECT PR_CODIGO FROM PROVEEDORES 4,.- Obtener los números de los proveedores de París cuya situación sea mayor que 20. WHERE PR_CIUDAD = “PARIS” AND SITUATION > 20; OR PR_CIDUDAD = LONDRES; PR_CIUDAD = “PARIS” OR “LONDRES”; 5.- Obtener los códigos de proveedor y situación de proveedores de París en orden descendente por situación. SELECT PR_CODIGO, SITUACION FROM PROVEEDORES WHERE PR_CIUDAD = “PARIS” OR BY SITUATION DESC; 6.- Obtener para todas las piezas, su código y su peso en gramos ordenando el resultado por peso y dentro de peso por código de pieza. SELECT P_CODIGO, P_NOMBRE, PESO * 454 AS PESO_ GRAMOS FROM PIEZAS ORDER BY PESO, P_CODIGO;

Consultas de reunión (con filtrado WHERE) 7.-Obtener todas las combinaciones de información de proveedores y piezas tales que el proveedor y la pieza en cuestión estén situados en la misma ciudad. SELECT PROVEEDORES.*, PIEZAS.* FROM PROVEEDORES, PIEZAS, WHERE PROVEEDORES. PR_CIUDAD=PIEZAS. P_CIUDAD; 8.- Obtener todas las combinaciones de información de proveedor y pieza donde la ciudad del proveedor siga a la ciudad de la pieza en orden alfabético. SELECT PROVEEDORES.*, PIEZAS.* FROM PROVEEDORES, PIEZAS, WHERE PROVEEDORES. PR_CIUDAD>PIEZAS. P_CIUDAD; 9.- Obtener todas las combinaciones de información de proveedor y pieza donde el proveedor y la pieza en cuestión estén cosituados, es decir tengan la misma ciudad, pero omitiendo a los proveedores cuya situación sea 20. SELECT PIEZAS.*, PROVEEDORES.* FROM PIEZAS, PROVEEDORES WHERE (((PIEZAS.P_CIUDAD)2; Nombre de la pieza, nº de pedidos, total de piezas y la media de piezas: B) SELECT P_CODIGO, COUNT(*) AS PEDIDOS, SUM(CANTIDAD) AS TOTAL, AVG(CANTIDAD) AS MEDIA FROM RELACION GROUP BY RELACION.P_CODIGO HAVING COUNT(*)>2; C) SELECT RELACION.P_CODIGO, Count(*) AS Expr1, Avg(RELACION.CANTIDAD) AS NUMPEDIDOS, Avg(RELACION.CANTIDAD) AS MEDIAPIEZAS, Sum(RELACION.CANTIDAD) AS TOTALPIEZAS FROM RELACION GROUP BY RELACION.P_CODIGO HAVING (((Count(*))>2));

E) SELECT PIEZAS.P_CODIGO, PIEZAS.P_NOMBRE, COUNT(*) AS PEDIDOS, SUM(CANTIDAD) AS TOTAL, AVG(CANTIDAD) AS MEDIA FROM RELACION, PIEZAS WHERE PIEZAS.P_CODIGO=RELACION.P_CODIGO GROUP BY PIEZAS.P_CODIGO, PIEZAS.P_NOMBRE HAVING COUNT(*) >2; 20.- Obtener todas las piezas cuyos nombres comiencen con la letra B SELECT * FROM PIEZAS WHERE P_NOMBRE LIKE 'B*'; 21.- Obtener los códigos de proveedores cuya situación es nula SELECT * FROM PROVEEDORES WHERE SITUACION IS NULL; ·Obtener la fecha: (ejemplo) SELECT * FROM PROVEEDORES FECHA>=#31/05/2005#;

WHERE

FECHA>=#01/01/2005#

AND

·CON BETWEEN: SELECT * FROM PROVEEDORES WHERE FECHA BETWEEN #01/01/2005# AND #31/05/2005#; ·CON DATE VALUE: SELECT * FROM PROVEEDORES WHERE FECHA = DATEVALUE ('18/10/2004');

Subconsultas (SELECT dentro de SELECT) 22.- Obtener los nombres de los proveedores que suministran la pieza P2. SELECT PR_NOMBRE Apuntes de SQL Pág. 23

FROM PROVEEDORES, RELACION WHERE P_CODIGO='P2' AND PROVEEDORES.PR_CODIGO=RELACION.PR_CODIGO; 23.- Obtener los nombres de los proveedores que por lo menos suministran una pieza de color rojo. A) SELECT P_CODIGO FROM PIEZAS WHERE COLOR='ROJO'; B) SELECT PR_CODIGO FROM RELACION WHERE P_CODIGO IN(SELECT P_CODIGO FROM PIEZAS WHERE COLOR='ROJO'); C) SELECT PR_NOMBRE FROM PROVEEDORES WHERE PR_CODIGO IN (SELECT PR_CODIGO FROM RELACION WHERE P_CODIGO IN(SELECT P_CODIGO FROM PIEZAS WHERE COLOR='ROJO')); D) SELECT PR_NOMBRE FROM PROVEEDORES,RELACION,PIEZAS WHERE RELACION.PR_CODIGO=PROVEEDORES.PR_CODIGO AND PIEZAS.P_CODIGO=RELACION.P_CODIGO AND PIEZAS.COLOR='ROJO'; 23.1. Codigo de proveedores que hayan sudministrado piezas rojas y verdes. SELECT PR_CODIGO FROM RELACION WHERE P_CODIGO IN(SELECT P_CODIGO FROM PIEZAS WHERE COLOR='ROJO' OR COLOR='VERDE'); 23.2 SELECT PR_CODIGO FROM PROVEEDORES WHERE PR_CODIGO IN (SELECT PR_CODIGO FROM RELACION WHERE P_CODIGO IN(SELECT P_CODIGO FROM PIEZAS WHERE COLOR='ROJO')) AND PR_CODIGO IN(SELECT PR_CODIGO FROM RELACION WHERE P_CODIGO IN(SELECT P_CODIGO FROM PIEZAS WHERE COLOR='VERDE')); 23.3 Obtener el nombre de los proveedores que han sudministrado alguna pieza de París y Roma. 23.4 Obtener los datos de las piezas que hayan sido sudministradas por algún proveedor de Atenas y de Londres.

24.- Obtener los códigos de proveedores situados en la misma ciudad que el proveedor con código S1. SELECT PR_CIUDAD FROM PROVEEDORES WHERE PR_CODIGO='S1'; SELECT * FROM PROVEEDORES WHERE PR_CIUDAD=(SELECT PR_CIUDAD FROM PROVEEDORES WHERE PR_CODIGO='S1'); 25.- Obtener los códigos de proveedores cuya situación sea menor que el valor máximo actual de situación de la tabla S(PROVEEDORES) SELECT * FROM PROVEEDORES WHERE SITUACION < (SELECT MAX(SITUACION) AS MAXIMO FROM PROVEEDORES);

Consultas con EXISTS y NOT EXISTS 26.- Obtener los nombres de los proveedores que suministran la pieza P2 Apuntes de SQL Pág. 24

SELECT * FROM PROVEEDORES WHERE EXISTS (SELECT * FROM RELACION WHERE P_CODIGO='P2' AND PROVEEDORES.PR_CODIGO=RELACION.PR_CODIGO); 26.1.Datos de las piezas sudministradas por el proveedor ‘S3’ SELECT * FROM PIEZAS WHERE EXISTS (SELECT * FROM RELACION WHERE PR_CODIGO='S3' AND PIEZAS.P_CODIGO=RELACION.P_CODIGO); 27.- Obtener los nombres de los proveedores que no suministran la pieza P2 SELECT * FROM PROVEEDORES WHERE NOT EXISTS (SELECT * FROM RELACION WHERE P_CODIGO='P2' AND PROVEEDORES.PR_CODIGO=RELACION.PR_CODIGO); 28.- Obtener los nombres de los proveedores que suministran todas las piezas. SELECT * FROM PROVEEDORES WHERE NOT EXISTS (SELECT PIEZAS.* FROM PIEZAS WHERE NOT EXISTS (SELECT RELACION.* FROM RELACION WHERE PIEZAS.P_CODIGO=RELACION.P_CODIGO AND PROVEEDORES.PR_CODIGO=RELACION.PR_CODIGO)); 28.B. Sacar las piezas que no han sido sudministradas. SELECT PIEZAS.* FROM PIEZAS WHERE NOT EXISTS (SELECT RELACION.P_CODIGO FROM RELACION WHERE PIEZAS.P_CODIGO=RELACION.P_CODIGO); 28.C) SELECT PROVEEDORES.* FROM PROVEEDORES WHERE NOT EXISTS (SELECT RELACION.PR_CODIGO FROM RELACION WHERE PROVEEDORES.PR_CODIGO=RELACION.PR_CODIGO); 28.D.Obtener el nombre de la pieza que han sido sudministradas por todos los proveedores.

28.E; Sacar los datos de los pedidos que la cantidad sea 100, 200 o 500. SELECT * FROM RELACION WHERE CANTIDAD IN(100,200,500); 28.F; Obtener los datos de las piezas que no han sido sudministradas con ‘exists’ e ‘in’: SELECT * FROM piezas WHERE not exists (select * from relacion where piezas.p_codigo=relacion.P_codigo); SELECT * FROM PIEZAS WHERE P_CODIGO NOT IN (SELECT P_CODIGO FROM RELACION); 29.- Obtener los códigos de los proveedores que suministran por lo menos todas las piezas suministradas por el proveedor S2.

Apuntes de SQL Pág. 25

Operaciones de actualización 30.-Cambiar a amarillo el color de la pieza ‘P2’, aumentar su peso en 5 e indicar que su ciudad es desconocida (NULL). UPDATE PIEZAS SET COLOR='AMARILLO',PESO=PESO+5,P_CIUDAD='CUBA' WHERE P_CODIGO='P2'; 30.B.Proveedores cuya situación sea >=20 le vamos a tripiclar la situación: UPDATE PROVEEDORES SET SITUACION=SITUACION*3 WHERE SITUACION=20; UPDATE PROVEEDORES SET SITUACION=SITUACION*3 WHERE SITUACION>20; 30.C A las piezas cuya fecha este dentro de este año hay q decrementar el peso en 5 unidades UPDATE PIEZAS SET PESO = PESO-5 WHERE FECHA>=#1/17/2005# And FECHA