T10. Introducción a SQL - Past Paper PDF

Tema 10: Introducción a SQL Introducción a la Ingeniería del Software y los Sistemas de Información I Ingeniería Informática – Tecnologías Informáticas Departamento de Lenguajes y Sistemas Informáticos Índice 1. Introducción 2. Lenguaje de definición de dato...

Tema 10: Introducción a SQL Introducción a la Ingeniería del Software y los Sistemas de Información I Ingeniería Informática – Tecnologías Informáticas Departamento de Lenguajes y Sistemas Informáticos Índice 1. Introducción 2. Lenguaje de definición de datos (DDL) 3. Lenguaje de manipulación de datos (DML) 4. Lenguaje de consulta de datos (DQL) 5. Consultas complejas 2 Introducción Trazabilidad de modelos A partir del modelo conceptual se puede obtener un modelo relacional que se implementa posteriormente en SQL para obtener el esquema de la base de datos. c l a ss Ca t á l ogo de pr oduc t os restricciones Laboratorios(laboratorioId, cif, nombre, dirección) La bor a t or i o Ca t á l ogo publ i c a PK(laboratorioId) cif f echa nom bre * AK(cif) dirección create table Laboratories ( ? Almacenes(almacenId, nombre, dirección) Requisitos a l m a c e na Pr oduc t osEn MC→ MR PK(almacenId) MR→ SQL laboratoryId Autoincrement, * * cif String UNIQUE, AlmacenesLaboratorios(almacenLaboratioId, almacenId, Al m a c é n Lí ne a D e Ca t á l ogo r e f e r e nc i a Pr oduc t o laboratorioId) nombre String, nom bre precioMenosDeCien código dirección precioMásDeCien * 1 nom bre descripción PK(almacenLaboratorioId) ) FK(almacenId)/Almacenes FK(laboratorioId)/Laboratorios …. Modelo conceptual … AK(almacenId, laboratorioId) Código SQL 3 Introducción SQL (Structured Query Language) es el lenguaje estándar para definir, manipular y consultar bases de datos relacionales. Se puede distinguir: DDL (Data Definition Language): gestión del esquema de la base de datos (creación, modificación y borrado de tablas, claves, etc.). CREATE, ALTER, DROP DML (Data Manipulation Language): gestión de los datos. INSERT, UPDATE, DELETE DCL (Data Control Language): Control de acceso y permisos. GRANT y REVOKE DQL (Data Query Language): Gestión de consultas. SELECT TCL (Transaction Control Language): gestión de transacciones. COMMIT, ROLLBACK, TRANSACTION 4 Introducción Tutoriales SQL  http://www.w3schools.com/sql/  https://www.tutorialspoint.com/sql/index.htm  https://mariadb.com/kb/en/library/basic-sql-statements/  https://www.hcoe.edu.np/uploads/attachments/r96oytechsac gzi4.pdf 5 Lenguaje de definición de datos (DDL) Creación de tablas Departamentos(departamentoId, nombreDep, localidad) PK(departamentoId) AK(nombreDep, localidad) CREATE TABLE Departamentos( departamentoId INT NOT NULL AUTO_INCREMENT, nombreDep VARCHAR(32), localidad VARCHAR(64), PRIMARY KEY(departamentoId), UNIQUE(nombreDep, localidad) ); 6 Lenguaje de definición de datos (DDL) Creación de tablas Empleados(empleadoId, departamentoId, jefeId, nombre, salario, fechaInicial, fechaFinal, comision) PK(empleadoId) FK(departamentoId)/Departamentos FK(jefeId)/Empleados 7 Lenguaje de definición de datos (DDL) Creación de tablas Empleados(empleadoId, departamentoId, jefeId, nombre, salario, fechaInicial, fechaFinal, comision) PK(empleadoId) CREATE TABLE Empleados( FK(departamentoId)/Departamentos empleadoId INT NOT NULL AUTO_INCREMENT, departamentoId INT, FK(jefeId)/Empleados jefe INT, nombre VARCHAR(64) NOT NULL, salario DECIMAL(6,2) DEFAULT 2000.00, fechaInicial DATE, fechaFinal DATE, comision DOUBLE, PRIMARY KEY(empleadoId), FOREIGN KEY(departamentoId) REFERENCES Departamentos(departamentoId) ON DELETE SET NULL, FOREIGN KEY(jefe) REFERENCES Empleados(empleadoId), UNIQUE(nombre), CHECK (comision >=0 AND comision =0 AND comision FROM < T1, T2,.. ,Tn > 𝛱𝑐𝑜𝑙𝑢𝑚𝑛𝑎𝑠 WHERE < condición > SELECT nombre, salario FROM Empleados 𝛱𝑛𝑜𝑚𝑏𝑟𝑒,𝑠𝑎𝑙𝑎𝑟𝑖𝑜 𝜎𝑠𝑎𝑙𝑎𝑟𝑖𝑜 2000; 𝑹∪𝑺 𝑹∩𝑺 28 Lenguaje de consulta de datos (DQL) Cláusula Exists: SELECT * FROM Departamentos D WHERE NOT EXISTS ( SELECT * FROM Empleados E WHERE D.departamentoId=E.departamentoId ); SELECT * FROM Departamentos D WHERE EXISTS ( SELECT * FROM Empleados E WHERE D.departamentoId=E.departamentoId ); 29 Consultas complejas Funciones de agregación COUNT devuelve el número de filas o valores especificados en una consulta. SUM, MAX, MIN, AVG se aplican a un conjunto o multiconjunto de valores numéricos y devuelven, respectivamente, la suma, el valor máximo, el mínimo y el promedio de dichos valores. Estas funciones se pueden usar con la cláusula SELECT. Ejemplo: SELECT COUNT(*), MIN(salario), MAX(salario), AVG(salario), SUM(salario) FROM Empleados; 𝑐𝑜𝑢𝑛𝑡 ∗ ,𝑚𝑖𝑛 𝑠𝑎𝑙𝑎𝑟𝑖𝑜 ,𝑚𝑎𝑥 𝑠𝑎𝑙𝑎𝑟𝑖𝑜 ,𝑎𝑣𝑔 𝑠𝑎𝑙𝑎𝑟𝑖𝑜 ,𝑠𝑢𝑚 𝑠𝑎𝑙𝑎𝑟𝑖𝑜 𝛾 𝐸𝑚𝑝𝑙𝑒𝑎𝑑𝑜𝑠 30 Consultas complejas Cláusula GROUP BY Agrupa las tuplas que tienen el mismo valor para ciertos atributos. Permite aplicar las funciones de agregación (sum, max, min, avg, count, etc.) a cada uno de dichos grupos. Los atributos de agrupación pueden aparecer en la cláusula SELECT. Es el equivalente al operador de Álgebra Relacional: 𝛾G𝐹 R Ejemplo: SELECT departamentoId, COUNT(*), AVG(salario) salarioMedio, AVG(salario * (1+comision)) salarioConComision, SUM(salario) gastoSalarios FROM Empleados GROUP BY departamentoId; 𝑑𝑒𝑝𝑎𝑟𝑡𝑎𝑚𝑒𝑛𝑡𝑜𝐼𝑑,𝑐𝑜𝑢𝑛𝑡 ∗ ,𝑎𝑣𝑔 𝑠𝑎𝑙𝑎𝑟𝑖𝑜 … 𝛾𝑑𝑒𝑝𝑎𝑟𝑡𝑎𝑚𝑒𝑛𝑡𝑜𝐼𝑑 𝐸𝑚𝑝𝑙𝑒𝑎𝑑𝑜𝑠 31 Consultas complejas Cláusula HAVING Especifica una condición sobre el grupo de tuplas asociado a cada valor de los atributos de agrupación (clases de equivalencia). Sólo los grupos que cumplan la condición entrarán en el resultado de la consulta. Primero se filtran las filas mediante WHERE, luego se agrupan, y luego se filtran los grupos mediante HAVING. SELECT * FROM ( SELECT departamentoId, SELECT departamentoId, COUNT(*), COUNT(*) numEmpleados, AVG(salario) salarioMedio, AVG(salario) salarioMedio, AVG(salario * (1+comision)) AVG(salario * (1+comision)) salarioConComision, salarioConComision, SUM(salario) gastoSalarios SUM(salario) gastoSalarios FROM Empleados FROM Empleados GROUP BY departamentoId HAVING GROUP BY departamentoId COUNT(*)>1; ) Estadistica WHERE numEmpleados>1; 𝜎𝑐𝑜𝑢𝑛𝑡 ∗ >1 𝛾 𝑑𝑒𝑝𝑎𝑟𝑡𝑎𝑚𝑒𝑛𝑡𝑜𝐼𝑑 𝑑𝑒𝑝𝑎𝑟𝑡𝑎𝑚𝑒𝑛𝑡𝑜𝐼𝑑,𝑐𝑜𝑢𝑛𝑡 ∗ ,𝑎𝑣𝑔 𝑠𝑎𝑙𝑎𝑟𝑖𝑜 … 𝐸𝑚𝑝𝑙𝑒𝑎𝑑𝑜𝑠 32 Consultas complejas Cláusulas ALL y ANY Permite comparar un valor individual v (nombre de atributo) con un conjunto de valores V (consulta anidada). Por ejemplo: SELECT * FROM Empleados SELECT * FROM Empleados WHERE salario > WHERE salario > ALL (SELECT AVG(salario) ANY (SELECT AVG(salario) FROM Empleados FROM Empleados GROUP BY departamentoId); GROUP BY departamentoId); 33 Consultas complejas Cláusulas ALL y ANY SELECT departamentoId FROM Empleados GROUP BY departamentoId HAVING COUNT(*)>= ALL ( SELECT COUNT(*) FROM Empleados GROUP BY departamentoId); SELECT departamentoId FROM Empleados GROUP BY departamentoId HAVING COUNT(*) = ( SELECT MAX(total) FROM ( SELECT COUNT(*) AS total FROM Empleados GROUP BY departamentoId ) NumEmpleados); 34 Consultas complejas Vistas Son tablas virtuales creadas en base al resultado de una consulta. Pueden optimizar el espacio de almacenamiento y el tiempo de CPU. Una vez creadas, se utilizan de forma análoga a una tabla. CREATE OR REPLACE VIEW EstadísticasEmpleados AS SELECT departamentoId, COUNT(*) AS numEmpleados, AVG(salario) salarioMedio, SUM(salario) gastoSalarios FROM Empleados GROUP BY departamentoId; SELECT MAX(numEmpleados) FROM EstadísticasEmpleados; 35 Lenguaje de manipulación de datos (DML) Inyección SQL ¡Ojo con incorporar datos suministrados por el usuario directamente en una sentencia SQL! query = "INSERT INTO Multas VALUES (" + matricula + ")" Sanitize 36  Tema 10: Introducción a SQL Introducción a la Ingeniería del Software y los Sistemas de Información I Ingeniería Informática – Tecnologías Informáticas Departamento de Lenguajes y Sistemas Informáticos