B3T4 Lenguajes BD SQL Past Paper PDF 2015-2016

2015-2016 Bloque 3 - Tema 4 LENGUAJES DE INTERROGACIÓN DE BASES DE DATOS. ESTÁNDAR ANSI SQL. PROCEDIMIENTOS ALMACENADOS. EVENTOS Y DISPARADORES PREPARACIÓN OPOSICIONES TÉCNICOS AUXILIARES DE INFORMÁTICA B3T4...

2015-2016 Bloque 3 - Tema 4 LENGUAJES DE INTERROGACIÓN DE BASES DE DATOS. ESTÁNDAR ANSI SQL. PROCEDIMIENTOS ALMACENADOS. EVENTOS Y DISPARADORES PREPARACIÓN OPOSICIONES TÉCNICOS AUXILIARES DE INFORMÁTICA B3T4 SQL TAI ÍNDICE ÍNDICE............................................................................................................................................................ 2 1. ESTÁNDAR ANSI SQL.................................................................................................................................. 3 1. Lenguaje de definición de datos (DDL)................................................................................................. 4 2. Lenguaje de manipulación de datos (DML)........................................................................................ 11 3. Lenguaje de control de datos (DCL).................................................................................................... 27 2. PROCEDIMIENTOS ALMACENADOS......................................................................................................... 29 1. SQL Server........................................................................................................................................... 29 2. MySQL................................................................................................................................................ 32 3. Oracle................................................................................................................................................. 34 3. EVENTOS Y DISPARADORES..................................................................................................................... 36 PABLO ARELLANO www.theglobeformacion.com Página 2 B3T4 SQL TAI 1. ESTÁNDAR ANSI SQL SQL es el lenguaje estándar ANSI/ISO de definición, manipulación y control de bases de datos relaciones. Es un lenguaje declarativo, ya que se indica qué datos obtener, en contraposición con los lenguajes procedimentales, en los que hay que especificar cómo se obtienen los datos. SQL (STRUCTURES QUERY LANGUAJE, lenguaje estructurado de consultas) es un lenguaje de consulta utilizado para manipular bases de datos relacionales. De los distintos lenguajes que presentaban los primeros SGBDR (Sistemas de Gestión de Bases de Datos Relacionales), ha acabado imponiéndose como estándar, tanto de iure como de facto, el SQL, lenguaje que actualmente ofrecen, aunque con variaciones, la mayor parte de los productos comerciales y que viene siendo objeto, desde hace varios años, de un intenso proceso de normalización. Fue desarrollado a mediado de los años 70 por IBM. Por esos años se llamaba SEQUEL (Structured English QUEry Language) y servía como interfaz para un sistema experimental de base de datos llamado SYSTEM R. Fue introducido por Oracle por primera vez en una base de datos comercial en el año 1979. El lenguaje se utiliza para definir, consultar y actualizar la base de datos, y es el más popular de su estilo. En 1982, el ANSI (American National Standars Institute) escogió SQL como lenguaje estándar en 1986 y e ISO (Internacional Standars Organization) en 1987. En 1989, el estándar fue objeto de una revisión y una ampliación que dieron lugar al lenguaje que se conoce con el nombre de SQL1 o SQL:1989. En 1992, el estándar volvió a ser revisado y ampliado considerablemente para cubrir carencias de la versión anterior. Esta nueva versión de SQL se conoce con el nombre de SQL2 o SQL:1992. En 1999, apareció la esperada versión SQL3 o SQL:1999, en la que, entre otras cosas, se definía formalmente uno de los componentes lógicos de control más útiles: los disparadores. AÑO NOMBRE ALIAS COMENTARIOS 1986 SQL-86 SQL-87 Primera publicación por ANSI, confirmada por ISO 1989 SQL-89 SQL1 Revisión menor 1992 SQL-92 SQL2 Revisión mayor Expresiones regulares, consultas recursivas, 1999 SQL:1999 SQL3 disparadores y orientación a objetos Características XML, objetos sequence y columnas 2003 SQL:2003 autonuméricas 2005 SQL:2005 Integración con XML 2008 SQL:2008 Disparadores INSTEAD OF PABLO ARELLANO www.theglobeformacion.com Página 3 B3T4 SQL TAI 2011 SQL:2011 Datos temporales 2016 SQL:2016 Compatibilidad ficheros JSON La última publicación de estándar es ISO/IEC 9075:2016 (IEC significa International Electrotechnical Commission). Esquema sentencias básicas SQL Creación à CREATE Modificación à ALTER DDL Eliminación à DROP Eliminación datos (no tabla) y reseteo contadores à TRUNCATE Selección à SELECT Inserción à INSERT DML Modificación à UPDATE Eliminación à DELETE Validación transacciones à COMMIT, ROLLBACK, SAVEPOINT DCL Autorización à GRANT, REVOKE 1. Lenguaje de definición de datos (DDL) Permite la especificación de un conjunto de relaciones y además de información sobre cada una de las relaciones, incluyendo: - El esquema de cada relación. - El dominio de los datos asociados a cada atributo. - Restricciones de integridad. - El conjunto de índices que se debe mantener para cada relación. - Información de seguridad y autorización para cada relación. - La estructura de almacenamiento físico de cada relación en disco. Para las sentencias de definición utilizaremos una extensión de la Forma Normal de Backus (BNF) para especificar las cláusulas del lenguaje donde: - < > representa los símbolos no terminales del lenguaje PABLO ARELLANO www.theglobeformacion.com Página 4 B3T4 SQL TAI - ::= es el operador de definición - [] indica elementos opcionales - {} agrupa elementos en una fórmula - | indica una alternativa -... indica repetición De los distintos elementos que puede contener un esquema relacional en SQL, estudiaremos los dominios, aserciones y tablas y se presentarán las opciones y posibilidades que resultan más interesantes. Tipos de datos TIPO DESCRIPCIÓN CHARACTER (longitud) Cadena de caracteres de longitud fija CHAR(longitud) CHARACTER VARYING (longitud) Cadena de caracteres de longitud variable VARCHAR(longitud) BIT (longitud) Cadena de bits de longitud fija BIT VARYING (longitud) Cadena de bits de longitud variable NUMERIC Número decimal INT DECIMAL Número decimal INTEGER Número entero SMALLINT Número entero pequeño Número con coma flotante con precisión REAL predefinida Número con coma flotante con la precisión FLOAT (precisión) especificada Número con coma flotante con más precisión DOUBLE PRECISION predefinida que la de tipo REAL DATE Fecha. Se compone de YEAR, MONTH, DAY TIME Horas. Se compone de HOUR, MINUT, SECOND Fechas y horas. Se compone de YEAR, MONTH, TIMESTAMP DAY, HOUR, MINUT, SECOND PABLO ARELLANO www.theglobeformacion.com Página 5 B3T4 SQL TAI 1. Creación de un esquema El estándar SQL no dispone de sentencias para la creación de una base de datos. Partiendo de que lo que se pretende es disponer de un conjunto de tablas relacionadas entre sí, se propone una sentencia de creación de esquemas denominada CREATE SCHEMA. Así pues, un esquema agrupa un conjunto de elementos de la base de datos que son propiedad de un usuario. La sintaxis es: CREATE SCHEMA {|AUTHORIZATION } []; Esta sentencia permite que un conjunto de tablas se agrupen bajo un mismo nombre, , y que tengan un propietario,. 2. Eliminación de un esquema La sentencia para borrar un esquema es: DROP SCHEMA {RESTRICT|CASCADE}; La opción RESTRICT borra el esquema solo si no contiene elemento alguno. Y la opción CASCADE, borra el esquema independientemente de si está vacío o no. 3. Creación de una tabla Para crear una tabla hay que utilizar la sentencia CREATE TABLE con el siguiente formato: CREATE TABLE nombre_tabla ( [, …] [, ] ); Donde definicion_columna es: {|} [] [] El proceso que hay que seguir para crear una tabla es: - Decidir el nombre de la tabla. - Dar nombre a cada uno de los atributos que formarán las columnas. - Asignar a cada una de las columnas un tipo de datos predefinido o bien un dominio definido por el usuario, así como el valor por defecto y, si existen, las restricciones de columna. - Por último, se especifican las restricciones de la tabla. PABLO ARELLANO www.theglobeformacion.com Página 6 B3T4 SQL TAI Definiciones por defecto La opción permite especificar qué valor dar a una columna cuando no se especifica dato alguno. Así DEFAULT tiene el siguiente formato: DEFAULT {||NULL} Ejemplo: columna sueldo con valor por defecto cero. sueldo INTEGER DEFAULT 0 Ejemplo: columna sueldo con valor por defecto nulo. sueldo INTEGER DEFAULT NULL Restricciones de columna A cada una de las columnas de una tabla, una vez asignado nombre y definido el dominio en el que toma valores, se pueden imponer ciertas restricciones a satisfacer mediante la opción : - NOT NULL: la columna no puede tener valores nulos. - UNIQUE: la columna no puede tener valores repetidos (clave candidata). - PRIMARY KEY: la columna no puede tomar valores repetidos ni nulos. Es la clave primaria. - REFERENCES [()]: la columna es clave foránea de la columna de la tabla especificada. - CONSTRAINT [] CHECK (): la columna tiene que cumplir las condiciones especificadas Restricciones de tabla Además de las restricciones que se definen para las columnas de una tabla, también es posible aplicar restricciones a nivel de tabla mediante , las cuales siempre se tendrán que cumplir. Dichas restricciones pueden ser: - UNIQUE ( [, …]): el conjunto de las columnas especificadas no puede tener valores repetidos (clave candidata). - PRIMARY KEY ( [, …]): el conjunto de las columnas especificadas no puede tener valores nulos ni repetidos. Es la clave primaria. - FOREIGN KEY ( [, …]) REFERENCES ([ [, …])]: el conjunto de las columnas especificadas es una clave ajena que referencia a la clave primaria formada por el conjunto de las columnas de la tabla. - CONSTRAINT [] CHECK (): la tabla tiene que cumplir las condiciones especificadas. PABLO ARELLANO www.theglobeformacion.com Página 7 B3T4 SQL TAI Tratamiento de claves ajenas cuando las claves primarias referenciadas cambian o se eliminan SQL nos ofrece la posibilidad de especificar cuando definimos una clave foránea, la política de actuación al modificar o eliminarse la clave primaria a la que hace referencia. Acabamos de ver la definición de claves foráneas. Ahora ampliamos su definición: FOREIGN KEY REFERENCES [)] [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] Donde: - NO ACTION: no se ejecutará ninguna acción. - CASCADE: se actualizará en cascada la clave ajena. - SET DEFAULT: se actualizará la clave ajena al valor por defecto. - SET NULL: se actualizará la clave ajena al valor nulo. Aserciones Vistas las restricciones que se pueden especificar al definir una tabla, existen otro tipo de restricciones a nivel global, que se incluirán en este epígrafe. Son las llamadas aserciones, que hacen referencia a una o varias columnas de una o más tablas. Para definir una aserción utilizamos la sentencia CREATE ASSERTION, con el siguiente formato: CREATE ASSERTION CHECK (); Para borrar una aserción utilizamos la sentencia DROP ASSERTION, con el formato: DROP ASSERTION ; 4. Modificación de una tabla Para modificar una tabla utilizaremos la sentencia ALTER TALBLE. El formato es el que sigue: ALTER TABLE {| }; Modificación de una columna se define como: {ADD [COLUMN] | ALTER [COLUMN] {SET |DROP DEFAULT} | DROP [COLUMN] {RESTRICT|CASCADE} } PABLO ARELLANO www.theglobeformacion.com Página 8 B3T4 SQL TAI Modificación de una restricción a nivel de tabla tiene el formato que sigue: {ADD | DROP CONSTRAINT {RESTRICT|CASCADE} } 5. Eliminación de una tabla Para borrar una tabla, hay que utilizar la sentencia DROP TABLE: DROP TABLE {RESTRICT|CASCADE}; Con la opción RESTRICT la tabla no se borrará si está referenciada desde otra tabla o vista. Con la opción CASCADE todo lo que referencie la tabla se borrará. 6. Creación de una vista Según la arquitectura ANSI/X3/SPARC y los niveles que define, las tablas forman parte del esquema conceptual. Ahora en este apartado, utilizamos el concepto de vista para definir los esquemas externos o lógicos. Para crear una vista es necesario hacer uso de la sentencia CREATE VIEW: CREATE VIEW [lista_columnas] AS (consulta) [WITH CHECK OPTION]; La cláusula WITH CHECK OPTION permite controlar que sólo se admiten operaciones de inserción y modificación que no atenten contra la expresión de consulta que define la vista. Otro aspecto a tener en cuenta es que, en la actualidad, sólo son actualizables las vistas que proceden de una única tabla (en cuya definición no aparezcan las cláusulas GROUP BY o HAVING). Las vistas no existen realmente como un conjunto de valores almacenados en la base de datos, son tablas ficticias o derivadas o también denominadas no materializadas, es decir son tablas virtuales. Las vistas se construyen a partir de tablas reales o materializadas que se almacenan en base de datos. Las ventajas que ofrecen las vistas son: - Seguridad: el usuario final solo percibe una parte de la información. - Utilidad: simplifica la construcción de consultas complejas, es decir, mayor legibilidad. PABLO ARELLANO www.theglobeformacion.com Página 9 B3T4 SQL TAI 7. Eliminación de una vista Para eliminar una vista es necesario hacer uso de la sentencia DROP VIEW: DROP VIEW {RESTRICT|CASCADE}; Con la opción RESTRICT la vista no se borrará si está referenciada desde otra tabla o vista. Con la opción CASCADE todo lo que referencie la vista se borrará. 8. Creación de un dominio Para crear un dominio utilizaremos la sentencia CREATE DOMAIN: CREATE DOMAIN [AS] [] []; Donde restricciones_dominio tiene el siguiente formato: CONSTRAINT [] CHECK () Ejemplo: Creación de un dominio para ciudades siendo solo válidas las capitales de Andalucía. CREATE DOMAIN dom_ciudadades AS CHAR(50) CONSTRAINT ciudades_andaluzas CHECK (VALUE IN (“Almeria”, “Cádiz”, “Córdoba”, “Granada”, “Huelva”, “Jaén”, “Málaga”, “Sevilla”)); 9. Modificación de un dominio Para modificar un dominio se utiliza la sentencia ALTER DOMAIN con el siguiente formato: ALTER DOMAIN {| } Donde: - := SET { =3; Ejemplo: Actualizar el sueldo de todos los empleados un 1,25%. UPDATE Empleados SET sueldo=sueldo*1,0125 4. Recuperación de datos: SELECT Para llevar a cabo consultas sobre una base de datos relacional, el lenguaje SQL propone la sentencia SELECT, que consta de las siguientes cláusulas: SELECT [ [AS] ] [, nombre_columna_seleccionada> [ [AS] ]…] FROM [ [AS] ] [WHERE ] La opción AS permite renombrar una columna (operador renombramiento), es decir, actúa como un alias. Por otro lado, el asterisco (*) después de la cláusula SELECT indica que queremos visualizar todas las columnas de la tabla. Ejemplo: Mostrar todos los clientes. SELECT * FROM Clientes; Ejemplo: Mostrar el nombre, apellidos y dirección de los clientes. SELECT nombre, apellidos, dirección FROM Clientes; Ahora bien, si con SELECT seleccionamos las columnas de una tabla, con WHERE se determinan las filas a seleccionar, es decir, las filas que cumplen la condición especificada. Para definir las condiciones de la cláusula WHERE, usamos los operadores. Operadores Tanto en la cláusula WHERE (condiciones de fila) como en la cláusula HAVING (condiciones de grupo) se pueden utilizar una serie de operadores: - De comparación: o =: mayor o igual que o =: igual que o !=: distinto - Lógicos: o AND: conjunción de condiciones. o OR: disyunción de condiciones. o NOT: negación de condiciones. El orden de precedencia o evaluación de los operadores es el siguiente salvo expresiones con paréntesis: - Primero: todos los operadores de comparación. - Segundo: NOT - Tercero: AND - Cuarto: OR Por tanto, se aconseja el uso de paréntesis para forzar la prioridad deseada de los operadores. Predicados Se detallan a continuación predicados a utilizar en la cláusula WHERE: - BETWEEN valor AND valor: intervalos de valores. Establece una comparación dentro un intervalo cerrado. Se puede utilizar NOT BETWEEN valor AND valor para establecer una comparación fuera del intervalo. SELECT FROM WHERE BETWEEN AND ; Ejemplo: Obtener los expedientes con entrada en 2018. SELECT * FROM expedientes WHERE fecha_entrada BETWEEN ‘01/01/2018’ AND ‘31/12/2018’; - LIKE: búsqueda por patrones. Establece una comparación entre cadenas de caracteres (NOT LIKE como contraposición), empleando los siguientes comodines: o %: sustituye a una cadena de caracteres de cualquier longitud, de cero a varios caracteres. o _: sustituye a un único carácter. SELECT FROM PABLO ARELLANO www.theglobeformacion.com Página 14 B3T4 SQL TAI WHERE LIKE ; Ejemplo: Obtener los ciudadanos cuyo primer apellido comienza por G. SELECT * FROM ciudadanos WHERE apellido1 LIKE “G%”; - IN: comprueba la pertenencia de un valor a un conjunto dado. NOT IN como contraposición. SELECT FROM WHERE [NOT] IN (, …, ); Ejemplo: Obtener las licencias que están denegadas o archivadas. SELECT * FROM licencias WHERE estado IN (“DENEGADO”, “ARCHIVADO”); - IS NULL: establece la comparación de un atributo con el valor nulo. IS NOT NULL para la comparación contraria. SELECT FROM WHERE IS [NOT] NULL; Ejemplo: Obtener los números de expediente que se han resuelto. SELECT num_expediente FROM expedientes WHERE fecha_resolucion IS NOT NULL; Operador CASE Es el operador estándar de elección entre múltiples valores. Admite dos sintaxis distintas: - Devuelve el valor ret1 si expresión se evalúa a val1 y a continuación termina. Si no, devuelve el valor ret2 si expresion se evalúa a val2 y así sucesivamente. Si ninguna de las igualdades anteriores se satisface, se devuelve retn. Si no existe la cláusula else y ninguna de las igualdades anteriores se ha cumplido, entonces devuelve null. CASE expresion WHEN val1 THEN ret1 WHEN val2 THEN ret2 … ELSE retn END - Devuelve el valor ret1 si expresion1 se evalúa a cierto y a continuación termina. Si no, devuelve el valor ret2 si expresión se evalúa a cierto y así sucesivamente. Si no existe la cláusula else y ninguna de las expresiones anteriores se ha evaluado a cierto devuelve el valor null. PABLO ARELLANO www.theglobeformacion.com Página 15 B3T4 SQL TAI CASE WHEN expresion1 THEN ret1 WHEN expresion2 THEN ret2 … ELSE retn END Ejemplo: Mostrar el nombre descriptivo “Ingreso Libre” si el campo idFormaAcceso toma el valor “L” o “Promoción Interna” si toma el valor “P”. SELECT IdProceso, CASE IdFormAcceso WHEN ‘L’ THEN ‘Ingreso Libre’ WHEN ‘P’ THEN ‘Promoción interna’ END AS FormaAcceso FROM Proceso; DISTINCT Para que en una consulta no aparezcan filas repetidas, usamos la palabra clave DISTINCT inmediatamente después de SELECT. ALL Si optamos porque aparezcan filas repetidas (opción por defecto) la palabra clave ALL será la usada. Si tras SELECT no especificamos nada será ALL la palabra clave por defecto. SELECT [ALL|DISTINCT] FROM [WHERE ] Funciones de agregación SQL ofrece una serie de funciones de agregación para realizar diferentes operaciones con los datos: - COUNT: número total de filas seleccionadas. - SUM: suma los valores de una columna. - MIN: valor mínimo de una columna. - MAX: valor máximo de una columna. - AVG: valor medio de una columna. Las funciones de agregación se aplican a una columna concreta, excepto la función de agregación COUNT que se aplica todas las columnas. - COUNT(*): número de filas que cumplan las condiciones. - COUNT(DISTINCT ): número de filas no nulo ni repetidos. - COUNT(): número de filas con valor no nulo. PABLO ARELLANO www.theglobeformacion.com Página 16 B3T4 SQL TAI Ejemplo: obtener el número de clientes de Granada. SELECT COUNT(*) AS num_clientes FROM clientes WHERE ciudad="Granada"; Subconsultas Una subconsulta es una consulta incluida dentro de una cláusula WHERE (o HAVING) de otra consulta. Ejemplo: Obtener los clientes que más han comprado. SELECT * FROM clientes WHERE comprado = (SELECT MAX(comprado) FROM clientes). Predicados aplicables a subconsultas: - Cuantificadores: ANY (alguno), ALL (todos). Se utiliza para comprobar si alguno o todos los valores de la consulta satisface o satisfacen la comparación con la consulta interna definida. SELECT FROM WHERE {ALL|ANY} ; Ejemplo: Obtener los nombres de los proyectos en la que los sueldos de todos los empleados asignados no superen el precio del proyecto. SELECT nombre_proyecto FROM proyectos WHERE precio > ALL ( SELECT sueldo FROM empleado WHERE proyectos.idproyecto = empleado.idproyecto); Ejemplo: Obtener los nombres de los proyectos en la que algún empleado tiene un sueldo superior al precio del proyecto al que está asignado. SELECT nombre_proyecto FROM proyectos WHERE precio < ANY ( SELECT sueldo FROM empleado WHERE proyectos.idproyecto = empleado.idproyecto); - Existencial: EXISTS. Indica la existencia o no de un conjunto. Se permite utilizar NOT EXISTS. Se utiliza para comprobar si la consulta interna devuelve algún resultado o no. SELECT FROM WHERE [NOT] EXISTS ; Ejemplo: Obtener los funcionarios asignados a algún proyecto. SELECT * FROM funcionarios WHERE EXISTS (SELECT * FROM proyectos WHERE proyectos.idproyecto=funcionarios.idproyecto); PABLO ARELLANO www.theglobeformacion.com Página 17 B3T4 SQL TAI Ordenación de datos ORDER BY Hasta ahora los datos obtenidos en la cláusula SELECT son mostrados en un orden preestablecido. Sin embargo, si queremos definir un orden concreto hay que utilizar la cláusula ORDER BY, que especifica la columna (o columnas) sobre la cual se deben ordenar las filas obtenidas por la consulta. El sentido de la ordenación puede ser ascendente (ASC) o descendente DESC, estableciéndose como sentido por defecto el ascendente. SELECT FROM [WHERE ] ORDER BY