Transcript T6_SQL - WordPress.com

Fundamentos de Bases de Datos
El lenguaje estándar para acceso y
manipulación de Bases de Datos: Structured
Query Language (SQL)
© 2007
Fundamentos de Bases de Datos
L. Gómez
1
Base de datos Northwind


Esta BD contiene información de pedidos, clientes, empleados,
productos, proveedores, etc.
Las llaves primarias están en negritas
© 2007
Fundamentos de Bases de Datos
L. Gómez
2
Consultando la información de Northwind


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
© 2007
Fundamentos de Bases de Datos
L. Gómez
3
Ejemplo usando Select *



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
© 2007
Fundamentos de Bases de Datos
L. Gómez
4
Ejecutando las consultas
Utilizando Microsoft SQL Server Management Studio:
Query Base de datos utilizada

Ejecutar query
Resultados
© 2007
Fundamentos de Bases de Datos
L. Gómez
5
SQL: STRUCTURED QUERY LANGUAGE

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]
{* | [columnExpression [AS newName]] [,...] }
FROM
TableName [alias] [, ...]
[WHERE
condition]
[GROUP BY columnList]
[HAVINGcondition]
[ORDER BY columnList]
© 2007
Fundamentos de Bases de Datos
L. Gómez
6
El estatuto SELECT
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.
© 2007
Fundamentos de Bases de Datos
L. Gómez
7
Mostrar solo algunos atributos

Obtener solo algunos atributos y todos los registros de una
tabla
SELECT
A1, A2, …, An
FROM r1
© 2007
Fundamentos de Bases de Datos
L. Gómez
8
Evitar tuplas duplicadas
Mostrar en que ciudades
hay clientes
Se repiten las ciudades
Usar DISTINCT
No se repiten
© 2007
Fundamentos de Bases de Datos
L. Gómez
9
Especificando una condición

Obtener nombre de compañía y del contacto de los
clientes que viven en London
© 2007
Fundamentos de Bases de Datos
L. Gómez
10
Especificando una condición con patrones

Obtener nombre de compañía y del contacto de los clientes cuya
ciudad comienza con B
patrones

%
 Ignora los valores de
cero o más
caracteres

_
 Ignora el valor de un
caracter
© 2007
Fundamentos de Bases de Datos
L. Gómez
11
Especificando una condición con patrones

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
© 2007
Fundamentos de Bases de Datos
L. Gómez
12
Especificando varias condiciones (OR)

Obtener nombre de compañía y del contacto de los clientes cuya
ciudad sea London o Buenos Aires
© 2007
Fundamentos de Bases de Datos
L. Gómez
13
Especificando varias condiciones (AND)

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)
© 2007
Fundamentos de Bases de Datos
L. Gómez
14
Uso de Conjuntos en una Condición (IN)

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)
© 2007
Fundamentos de Bases de Datos
L. Gómez
15
Conjuntos en una condición (NOT IN)

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)
© 2007
Fundamentos de Bases de Datos
L. Gómez
16
Verificando valores NULL

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
© 2007
Fundamentos de Bases de Datos
L. Gómez
17
Verificando valores NOT NULL

Obtener Id del cliente, nombre de la compañía y la Region
para aquellos clientes cuya Region tenga un valor asignado
© 2007
Fundamentos de Bases de Datos
L. Gómez
18
Ordenando los resultados (ascendente)


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
© 2007
Fundamentos de Bases de Datos
L. Gómez
19
Ordenando los resultados (descendente)


Obtener el nombre del producto y su precio unitario
ordenando del producto más caro al más barato
Usar la clausula DESC
© 2007
Fundamentos de Bases de Datos
L. Gómez
20
Funciones en SQL

Las siguientes funciones aplican a valores numéricos y no
numéricos:
COUNT
 Regresa cuantos valores (diferentes de NULL) existen en la
columna especificada.

COUNT(*)



MIN


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

Regresa el máximo de los contenidos
especificada. Primero elimina NULLs
© 2007
Fundamentos de Bases de Datos
L. Gómez
de
la
columna
21
Funciones en SQL


Las siguientes
numéricos :
SOLO
a
valores
Regresa la suma de los contenidos de la columna
especificada. Primero elimina NULLs
AVG


aplican
SUM


funciones
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
© 2007
Fundamentos de Bases de Datos
L. Gómez
22
Ejemplos del uso del count




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
© 2007
Fundamentos de Bases de Datos
L. Gómez
23
COUNT eliminando valores duplicados

Usar DISTINCT para eliminar regiones que se repiten.
Comparar los resultados obtenidos con cada query
© 2007
Fundamentos de Bases de Datos
L. Gómez
24
Ejemplo de MIN y MAX


Obtener el costo del producto mas barato
Obtener el costo del producto más caro
© 2007
Fundamentos de Bases de Datos
L. Gómez
25
Ejemplo de AVG y SUM


Obtener el precio promedio de todos los productos
Obtener el número total de unidades en inventario de todos los
productos
© 2007
Fundamentos de Bases de Datos
L. Gómez
26
Misceláneo: MIN, MAX, AVG

Varias funciones en el estatuto Select
© 2007
Fundamentos de Bases de Datos
L. Gómez
27
Uso de Funciones
IMPORTANTE

Las funciones COUNT, MIN, MAX, SUM, AVG
sólo
pueden usarse en
 SELECT
 HAVING (se verá más adelante)
© 2007
Fundamentos de Bases de Datos
L. Gómez
28
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 CategoryID
Por ejemplo, tengo 12 productos de
categoría 1
© 2007
Fundamentos de Bases de Datos
L. Gómez
29
group by
GROUP BY CategoryID

Agrupa todos los registros
que tienen el mismo valor
en el atributo especificado
(CategoryID)

Se utiliza con otra función,
por ejemplo COUNT para
saber cuantos registros
fueron agrupados

ID 1
12 prod.
SELECT CategoryID,
count(CategoryID)
FROM Products
GROUP BY CategoryID
© 2007
Fundamentos de Bases de Datos
L. Gómez
30
Count y Group by

Se obtiene el número de productos que existen de cada categoría
© 2007
Fundamentos de Bases de Datos
L. Gómez
31
ID o nombre?


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)
© 2007
Fundamentos de Bases de Datos
L. Gómez
32
Join


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
© 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 categoría
FK
PK
© 2007
Fundamentos de Bases de Datos
L. Gómez
34
Count y Group by

número de productos existentes de cada categoría
© 2007
Fundamentos de Bases de Datos
L. Gómez
35
Count, Group by, Having

número de productos existentes de cada categoría para los
que el número de productos sea menor a 10
© 2007
Fundamentos de Bases de Datos
L. Gómez
36
Count, Group by, Having, alias


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
© 2007
Fundamentos de Bases de Datos
L. Gómez
37
SQL y Algebra Relacional


SQL query tipico
SELECT A1, A2, …, An
FROM r1, r2, …, rm
WHERE P
Es equivalente al siguiente estatuto de álgebra relacional:
πA1, A2,…,A3(σP(r1 x r2 … x rm))
.
© 2007
Fundamentos de Bases de Datos
L. Gómez
38
Operadores Union, Interseccion y Diferencia

Operaciones con tablas
© 2007
Fundamentos de Bases de Datos
L. Gómez
39
SQL y Algebra Relacional
El asterisco (*) se refiere a todos los atributos o columnas de la tabla.
Algebra Relacional SQL
rUs
(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
© 2007
Fundamentos de Bases de Datos
L. Gómez
40
Union

Obtener todos los países donde hay un cliente o un
proveedor
© 2007
Fundamentos de Bases de Datos
L. Gómez
41
Interseccion

Obtener todos los países donde hay proveedores y clientes
© 2007
Fundamentos de Bases de Datos
L. Gómez
42
Diferencia (Except)

Obtener los países donde hay
proveedores, pero no hay
clientes
© 2007
Fundamentos de Bases de Datos

Obtener los países donde sólo
hay clientes y no hay provedores
L. Gómez
43
Ejemplos en SQL




cse_majors
cse_majors U eee_majors:
 Select * from cse_majors
union
select * from eee_majors
Cse_profs
cse_majors – eee_majors:
Name Office
 Select * from cse_majors
Prof1
Office1
minus
Prof2
Office2
select * from eee_majors
cse_profs x cse_courses:
 Select * from cse_profs, cse_courses
CLASS=‘SR’(cse_majors):

Select * from cse_majors
where class= ‘SR’
© 2007
Fundamentos de Bases de Datos
L. Gómez
Id
Name
Class
1111
Student1
Fr
2222
Student2
So
3333
Student3
Jr
4444
Student4
Sr
5555
Student5
Gr
eee_majors
Id
Name
Class
2222
Student2
So
4444
Student4
Sr
6666
Student6
Sr
cse_courses
Crsid
Crstitle
PR1
Programacion 1
DB1
Bases de Datos
DB2
Bases de Datos Avanzadas
44
Ejemplos adicionales




cse_majors
πID, NAME(cse_majors):
 Select id, name from cse_majors
teaches
cse_majors ∩ eee_majors:
name
crsid
 Select * from cse_majors
Prof1
PR1
intersect
Prof1
DB1
select * from eee_majors
Prof2
DB2
Prof1
DB2
cse_profs
teaches:
 Select * from cse_profs P, teaches T
where P.name = T.name
cse_profs cse_courses
teaches:
 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
PR1
Name Office
T.crsid = C.crsid
© 2007
Fundamentos de Bases de Datos
Id
Name
Class
1111
Student1
Fr
2222
Student2
So
3333
Student3
Jr
4444
Student4
Sr
5555
Student5
Gr
eee_majors
Id
Name
Class
2222
Student2
So
4444
Student4
Sr
6666
Student6
Sr
cse_courses
Crstitle
Programacion 1
Prof1
Office1
DB1
Bases de Datos
Prof2
Office2
DB2
Bases de Datos Avanzadas
L. Gómez
45
Ejercicio 1
Obtenga el nombre de los empleados que tomaron el curso
BD1 en Junio 25, 1990
emp(id, name)
takes( id, crsid, date)
course(crsid, name)

SELECT name
FROM emp E, takes T
WHERE E.id = T.id AND
T.date = ’25Jun90’ AND
T.crsid = ‘BD1’
© 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, idJefe)

SELECT M.nombre, D.nombre
FROM emp E, emp M, dept D
WHERE E.nombre= ‘John Smith’ and
E.dnum = D.dnum AND
D.idJefe = M.id
© 2007
Fundamentos de Bases de Datos
L. Gómez
47
SQL

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)
© 2007
Fundamentos de Bases de Datos
L. Gómez
48
Queries Anidados

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
© 2007
Fundamentos de Bases de Datos
L. Gómez
49
Subqueries


Encuentre los empleados que no han tomado algun curso
SQL:
select name
from emp E
emp(id, name)
where not exists
takes( id, crsid, date)
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)
© 2007
Fundamentos de Bases de Datos
L. Gómez
50
DB
cse_majors
Cse_profs
Id
Name
Class
1111
Student1
Fr
Name
Office
2222
Student2
So
Prof1
Office1
3333
Student3
Jr
Prof2
Office2
4444
Student4
Sr
5555
Student5
Gr
ee_majors
Id
Name
Class
2222
Student2
So
4444
Student4
Sr
6666
Student6
Sr
© 2007
Has_taught
name
crsid
Prof1
PR1
Prof1
DB1
Prof2
DB2
Prof1
DB2
cse_courses
Crsid
Crstitle
PR1
Programacion 1
DB1
Bases de Datos
DB2
Bases de Datos Avanzadas
Fundamentos de Bases de Datos
L. Gómez
51
Definición de tablas en SQL(Create)

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)
.
© 2007
Fundamentos de Bases de Datos
L. Gómez
52
Borrar, Modificar estructura


Borrar una tabla
DROP TABLE <tablename>
Agregar un atributo a una tabla
ALTER TABLE <tablename>
ADD <colname> <coltype>
© 2007
Fundamentos de Bases de Datos
L. Gómez
53
SQL- Modificar la estructura de la BD






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
© 2007
Fundamentos de Bases de Datos
L. Gómez
54