Fundamentos de Bases de Datos El lenguaje estndar

Fundamentos de Bases de Datos El lenguaje estándar para acceso y manipulación de Bases

Base de datos Northwind n n Esta BD contiene información de pedidos, clientes, empleados, productos, proveedores, etc. Las llaves primarias están en negritas 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 2

Consultando la información de Northwind n n Para consultar los datos que están almacenados en una Base de datos se utiliza el lenguaje de manipulación de datos SQL Para obtener la información de las compañías que envían los paquetes se ejecuta el siguiente comando o query Atributos Tabla 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 3

Ejemplo usando Select * n n n La consulta más compacta es obtener todos los atributos de una tabla Por ejemplo para obtener toda la información de los clientes, utilizamos el siguiente query El (*) asterisco indica que se deben obtener todos los atributos del cliente: select * from Customers 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 4

Ejecutando las consultas Utilizando Microsoft SQL Server Management Studio: Query Base de datos utilizada

SQL: STRUCTURED QUERY LANGUAGE n Los ejemplos anteriores muestran un ejemplo sencillo del SQL, para hacer consultas más elaboradas se requiere conocer la sintaxis del SQL: SELECT [DISTINCT | ALL] {* | [column. Expression [AS new. Name]] [, . . . ] } FROM Table. Name [alias] [, . . . ] [WHERE condition] [GROUP BY column. List] [HAVING condition] [ORDER BY column. List] 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 6

El estatuto SELECT Especifica las columnas a mostrar en el resultado FROM Especifica la(s) tabla(s) a utilizar. WHERE Filtra renglones de acuerdo a la condición especificada. GROUP BY Forma grupos de renglones con el mismo valor en la columna HAVING Filtra grupos de acuerdo a la condición especificada. ORDER BY Especifica como ordenar el resultado. 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 7

Mostrar solo algunos atributos n Obtener solo algunos atributos y todos los registros de

Evitar tuplas duplicadas Mostrar en que ciudades hay clientes Se repiten las ciudades Usar

Especificando una condición n Obtener nombre de compañía y del contacto de los clientes

Especificando una condición con patrones n Obtener nombre de compañía y del contacto de los clientes cuya ciudad comienza con B patrones n % n Ignora los valores de cero o más caracteres n _ n Ignora el valor de un caracter 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 11

Especificando una condición con patrones n Obtener nombre de compañía y del contacto de los clientes cuya ciudad tenga como penultima letra una e % cero o más caracteres _ 1 sólo caracter 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 12

Especificando varias condiciones (OR) n Obtener nombre de compañía y del contacto de los

Especificando varias condiciones (AND) n Obtener nombre de compañía y del contacto de los clientes cuya ciudad sea London y que la persona contacto sea un Agente de ventas (Sales Agent) 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 14

Uso de Conjuntos en una Condición (IN) n Obtener nombre de compañía, nombre y titulo del contacto de los clientes cuya persona contacto sea un Agente de ventas (Sales Agent) o un representante de ventas (Sales Representative) o un asociado de ventas (Sales Associate) 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 15

Conjuntos en una condición (NOT IN) n Obtener nombre de compañía, nombre y titulo del contacto de los clientes cuya persona contacto NO sea un Agente de ventas (Sales Agent) o un representante de ventas (Sales Representative) o un asociado de ventas (Sales Associate) 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 16

Verificando valores NULL n Obtener Id del cliente, nombre de la compañía y la Region para aquellos clientes cuya Region sea NULL Null Valor que significa que al atributo no se le asignó un valor o se le asignó NULL 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 17

Verificando valores NOT NULL n Obtener Id del cliente, nombre de la compañía y la Region para aquellos clientes cuya Region tenga un valor asignado 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 18

Ordenando los resultados (ascendente) n n Obtener el nombre del producto y su precio unitario ordenando del producto más barato al más caro El default es ordenar de menor a mayor 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 19

Ordenando los resultados (descendente) n n Obtener el nombre del producto y su precio unitario ordenando del producto más caro al más barato Usar la clausula DESC 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 20

Funciones en SQL n Las siguientes funciones aplican a valores numéricos y no numéricos: COUNT n Regresa cuantos valores (diferentes de NULL) existen en la columna especificada. n COUNT(*) n n n MIN n n Regresa el número de registros en la tabla especificada Regresa el mínimo de los contenidos de la columna especificada. Primero elimina NULLs MAX n Regresa el máximo de los contenidos especificada. Primero elimina NULLs 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez de la columna 21

Funciones en SQL n n Las siguientes funciones aplican SOLO a valores numéricos : SUM n n AVG n n Regresa la suma de los contenidos de la columna especificada. Primero elimina NULLs Regresa el promedio de los contenidos de la columna especificada. Primero elimina NULLs NOTA: En algunos DBMSs, si existe un NULL puede marcar un error de ejecución 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 22

Ejemplos del uso del count n n La tabla de Clientes tiene 91 registros Los primeros 2 ejemplos cuentan el número de registros que tienen un valor diferente de NULL en el atributo region. El Ejemplo 3, cuenta los registros que tienen valores NULL en region count(atributo) elimina atributos NULL, count(*) no elimina region con NULL 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 23

COUNT eliminando valores duplicados n Usar DISTINCT para eliminar regiones que se repiten. Comparar

Ejemplo de MIN y MAX n n Obtener el costo del producto mas barato

Ejemplo de AVG y SUM n n Obtener el precio promedio de todos los productos Obtener el número total de unidades en inventario de todos los productos 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 26

Misceláneo: MIN, MAX, AVG n Varias funciones en el estatuto Select 10/2/2020 © 2007

Uso de Funciones IMPORTANTE n Las funciones COUNT, MIN, MAX, SUM, AVG sólo pueden

GROUP BY En el reporte puede verse que tengo varios productos de la misma categoría. Si quisiera saber cuantos productos tengo de cada categoría como podría hacerle? Podría agrupar y contar todos los registros que tienen el mismo valor en Category. ID Por ejemplo, tengo 12 productos de categoría 1 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 29

group by GROUP BY Category. ID n Agrupa todos los registros que tienen el mismo valor en el atributo especificado (Category. ID) n Se utiliza con otra función, por ejemplo COUNT para saber cuantos registros fueron agrupados n ID 1 12 prod. SELECT Category. ID, count(Category. ID) FROM Products GROUP BY Category. ID 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 30

Count y Group by n Se obtiene el número de productos que existen de

ID o nombre? n n El tener un número de categoría no indica nada, es mejor poner el nombre de la categoría como en el resultado siguiente: Pero, como se escribe el query? Con un JOIN entre la llave foránea FK IDCategory y la llave Primaria Categories(IDCategory) 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 32

Join n n El nombre de la categoría está en la tabla Categories y nuestra consulta utiliza la tabla de Productos Poner en el query las 2 tablas y especificar una condición donde la FK=PK 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 33

Join entre 2 tablas Nombre del producto, Id de categoría y Nombre de la

Count y Group by n número de productos existentes de cada categoría 10/2/2020 ©

Count, Group by, Having n número de productos existentes de cada categoría para los

Count, Group by, Having, alias n n número de productos existentes de cada categoría para los que el número de productos sea menor a 10 Se utilizan alias para las tablas 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 37

SQL y Algebra Relacional n n SQL query tipico SELECT A 1, A 2, …, An FROM r 1, r 2, …, rm WHERE P Es equivalente al siguiente estatuto de álgebra relacional: πA 1, A 2, …, A 3(σP(r 1 x r 2 … x rm)). 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 38

Operadores Union, Interseccion y Diferencia n Operaciones con tablas 10/2/2020 © 2007 Fundamentos de

SQL y Algebra Relacional El asterisco (*) se refiere a todos los atributos o columnas de la tabla. Algebra Relacional SQL r. Us (select * from r) union (select * from s) r–s (select * from r) except (select * from s) rxs Select * from r, s σP(r) Select * from r where P πA(r) Select A from r 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 40

Union n Obtener todos los países donde hay un cliente o un proveedor 10/2/2020

Interseccion n Obtener todos los países donde hay proveedores y clientes 10/2/2020 © 2007

Diferencia (Except) n Obtener los países donde hay proveedores, pero no hay clientes 10/2/2020 © 2007 Fundamentos de Bases de Datos n Obtener los países donde sólo hay clientes y no hay provedores L. Gómez 43

Ejemplos en SQL n n cse_majors U eee_majors: n Select * from cse_majors union select * from eee_majors Cse_profs cse_majors – eee_majors: Name Office n Select * from cse_majors Prof 1 Office 1 Prof 2 Office 2 minus select * from eee_majors cse_profs x cse_courses: n Select * from cse_profs, cse_courses CLASS=‘SR’(cse_majors): n Select * from cse_majors where class= ‘SR’ 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez Id Name Class 1111 Student 1 Fr 2222 Student 2 So 3333 Student 3 Jr 4444 Student 4 Sr 5555 Student 5 Gr eee_majors Id Name Class 2222 Student 2 So 4444 Student 4 Sr 6666 Student 6 Sr cse_courses Crsid Crstitle PR 1 Programacion 1 DB 1 Bases de Datos DB 2 Bases de Datos Avanzadas 44

Ejemplos adicionales n n cse_majors πID, NAME(cse_majors): n Select id, name from cse_majors teaches cse_majors ∩ eee_majors: name crsid n Select * from cse_majors Prof 1 PR 1 intersect Prof 1 DB 1 select * from eee_majors Prof 2 DB 2 Prof 1 DB 2 cse_profs teaches: n Select * from cse_profs P, teaches T where P. name = T. name cse_profs cse_courses teaches: n Select P. name, P. office, C. crsid, C. crstitle from cse_profs P, cse_courses C, teaches T where P. name = T. name and Cse_profs Crsid PR 1 Name Office T. crsid = C. crsid 10/2/2020 © 2007 Fundamentos de Bases de Datos Id Name Class 1111 Student 1 Fr 2222 Student 2 So 3333 Student 3 Jr 4444 Student 4 Sr 5555 Student 5 Gr eee_majors Id Name Class 2222 Student 2 So 4444 Student 4 Sr 6666 Student 6 Sr cse_courses Crstitle Programacion 1 Prof 1 Office 1 DB 1 Bases de Datos Prof 2 Office 2 DB 2 Bases de Datos Avanzadas L. Gómez 45

Ejercicio 1 Obtenga el nombre de los empleados que tomaron el curso BD 1 en Junio 25, 1990 emp(id, name) takes( id, crsid, date) course(crsid, name) n SELECT name FROM emp E, takes T WHERE E. id = T. id AND T. date = ’ 25 Jun 90’ AND T. crsid = ‘BD 1’ 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 46

Ejercicio SQL Quien es el Jefe del empleado ‘John Smith’ ? Obtenga el nombre y el departamento del jefe. Emp(id, nombre, dnum) Dept(dnum, nombre, id. Jefe) n SELECT M. nombre, D. nombre FROM emp E, emp M, dept D WHERE E. nombre= ‘John Smith’ and E. dnum = D. dnum AND D. id. Jefe = M. id 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 47

SQL n Encuentre los empleados con los salarios mas altos emp(ID, NOMBRE, SALARIO, SUPERID, DNO) Select id, nombre, salario From emp Where salario = (select max(salario) from emp) 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 48

Queries Anidados n Encuentre los alumnos que tienen una especialidad (major) en computer science y en electrical engineering. select id, name from cse_majors where id in ( select id from eee_majors) Evaluacion: (1) query anidado (2) query externo utilizando los resultados del query interno 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 49

Subqueries n n Encuentre los empleados que no han tomado algun curso SQL: select name from emp E emp(id, name) takes( id, crsid, date) where not exists course(crsid, name) (select * from takes T where T. id = E. id) Para cada tupla de empleado, el query anidado selecciona todas las tuplas de TAKES cuyo ID es igual al ID del empleado; Si el resultado del subquery está vacío, entonces significa que el empleado no ha tomado algun curso y entonces esa tupla se incluye en el resultado. EXISTS subquery: VERDADERO si el subquery SI regresa tuplas FALSO si el subquery no regresa tuplas NOT EXISTS subquery = NOT(EXISTS subquery) 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 50

DB cse_majors Cse_profs Id Name Class 1111 Student 1 Fr Name Office 2222 Student 2 So Prof 1 Office 1 3333 Student 3 Jr Prof 2 Office 2 4444 Student 4 Sr 5555 Student 5 Gr ee_majors Id Name Class 2222 Student 2 So 4444 Student 4 Sr 6666 Student 6 Sr 10/2/2020 © 2007 Has_taught name crsid Prof 1 PR 1 Prof 1 DB 1 Prof 2 DB 2 Prof 1 DB 2 cse_courses Crsid Crstitle PR 1 Programacion 1 DB 1 Bases de Datos DB 2 Bases de Datos Avanzadas Fundamentos de Bases de Datos L. Gómez 51

Definición de tablas en SQL(Create) n Crear una relación o tabla CREATE TABLE <tablename> (<colname> <coltype> [NOT NULL], …); ej. create table cse_majors (id char(9) not null, name varchar(24) not null, class char(2) not null). 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 52

Borrar, Modificar estructura n n Borrar una tabla DROP TABLE <tablename> Agregar un atributo a una tabla ALTER TABLE <tablename> ADD <colname> <coltype> 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 53

SQL- Modificar la estructura de la BD n n n INSERT INTO <tablename> VALUES (…) INSERT INTO <tablename> <s-f-w> DELETE FROM <tablename> WHERE <condition> UPDATE <tablename> SET <set-clause> Ej. update enrollment set grade = ‘E’ where grade = ‘I’ CREATE VIEW <view-name> AS <s-f-w> View – tabla virtual. CREATE SNAPSHOT <view-name> AS <s-f-w> Snapshot – Vista materializada 10/2/2020 © 2007 Fundamentos de Bases de Datos L. Gómez 54