SQL
COMIT: Ingeniería de información
Ingeniería de Sistemas y Computación
Universidad de Los Andes
SQL
Lenguajes:
→ Cálculo Relacional
→ Álgebra Relacional
→ SQL
Concepto: Lenguaje estructurado de consulta.
Estándares SQL86, SQL89,SQL92, SQL1999 y SQL2003.
SQL
Componentes:
• DDL
• Esquemas
• Reglas de integridad
• Vistas
• Acceso a relaciones, vistas, etc.
• DML
• Control de transacciones
• SQL embebido y SQL dinámico.
Restricciones
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
[Link]
Ejercicios
Modelo relacional Parranderos
SQL: Creación de la BD
• Crear tablas
• Definir restricciones
• Crear secuencias
• Crear índices
Crear tablas
CREATE TABLE FRECUENTAN
( ID_BAR NUMBER(3,0),
ID_BEBEDOR NUMBER(5,0),
HORARIO VARCHAR2(20),
FECHA_ULTIMA_VISITA DATE
)
Creación de tablas
Vamos a crear las tablas BEBEDORES, BARES y FRECUENTAN (por ahora no
se preocupen por restricciones FK y CHECK)
CREATE TABLE BEBEDORES
( ID NUMBER PRIMARY KEY,
NOMBRE VARCHAR2(20) NOT NULL,
EDAD NUMBER DEFAULT 25 NOT NULL
)
CREATE TABLE BEBEDORES
( ID NUMBER NOT NULL,
NOMBRE VARCHAR2(20) NOT NULL,
EDAD NUMBER DEFAULT 25 NOT NULL,
CONSTRAINT BEB_PK PRIMARY KEY (ID)
)
CREATE TABLE FRECUENTAN
( IDBEB NUMBER NOT NULL,
IDBAR NUMBER NOT NULL,
CONSTRAINT FREC_PK PRIMARY KEY (IDBEB, IDBAR)
)
Definición de restricciones
ALTER TABLE FRECUENTAN
ADD CONSTRAINT FK_F_BARES
FOREIGN KEY ( ID_BAR ) REFERENCES BARES ( ID )
ALTER TABLE FRECUENTAN
ADD CONSTRAINT FK_F_BEBEDOR
FOREIGN KEY ( ID_BEBEDOR) REFERENCES BEBEDORES( ID )
ALTER TABLE FRECUENTAN
ADD CONSTRAINT CK_HORARIO
CHECK (horario IN ('diurno', 'nocturno', 'todos'))
ALTER TABLE FRECUENTAN
ADD CONSTRAINT PK_FRECUENTAN
PRIMARY KEY ( ID_BAR , ID_BEBEDOR , HORARIO )
Creación de índices
CREATE INDEX INDICE_BEBEDOR
ON FRECUENTAN (ID_BEBEDOR)
Manejo de secuencias
• CREATE SEQUENCE seqName;
• SELECT [Link]
FROM DUAL;
• INSERT INTO <table_name> (<column_name>)
VALUES ([Link]);
Borrar tablas
DROP TABLE Frecuentan;
DROP TABLE Bebedor CASCADE CONSTRAINTS;
SQL: Modificación de la BD
• Inserción
• Actualización
• Borrado
SQL: Modificación de la BD - Inserción
INSERT INTO R [(A1,…, An)]
VALUES (v1,…, vn)
INSERT INTO R S
Añadir a la base de datos de parranderos, el bebedor 5,
“María Sánchez”, que vive en Bogotá y tiene presupuesto
alto
INSERT INTO Bebedor (id, nombre, ciudad, presupuesto)
VALUES (5, ‘María Sánchez’, ’Bogotá, ‘Alto’)
SQL: Modificación de la BD - Actualización
UPDATE R
SET A1 = V1 [,… An = Vn]
[WHERE Predicado]
Sume un 1 al grado de alcohol de las bebidas, para
aquellas que tienen un grado de alcohol entre 20º y 50º
UPDATE Bebidas
SET grado = grado + 1
WHERE grado BETWEEN 20 AND 50
SQL: Modificación de la BD - Borrado
DELETE FROM R
[WHERE Predicado]
Borrar todas las cervezas con grado de alcohol mayor a 50
DELETE FROM Cerveza
WHERE grado > 50
SQL: Modificación de la BD - Borrado
DELETE FROM R
[WHERE Predicado]
Borrar todas las cervezas con grado de alcohol mayor a
50, que no son servidas por ningún bar ni gustan a ningún
bebedor
DELETE FROM Cerveza
WHERE grado > 50
SI FUNCIONA !!! ¿POR QUÉ?
Select
SELECT <atributos>
FROM <Relación>
[WHERE <Condición>]
[ORDER BY <atributos>]
[GROUP BY <atributos>
[HAVING <Condición> ]]
Funciones de cadenas de caracteres
• [Link]
• [Link]
Funciones de fechas
• [Link]
• [Link]
Ejercicios
Ejercicios: Se quiere saber…
1. Toda la información de todos los bebedores
2. El nombre y el presupuesto de todos los bebedores
3. Toda la información de todos los bares de Bogotá
4. El nombre y el presupuesto de los bares de Bogotá
5. Los nombres de los bebedores de quienes no se conoce su
presupuesto
6. Los nombres de los bares que no son ni de Medellín ni de Bogotá
Ejercicios: Se quiere saber…
1. Toda la información de frecuentan, ordenada por el identificador del
bar (ascendente) y por la fecha de la última visita (descendente)
2. El id, nombre y presupuesto de los bebedores, ordenado por el
nombre (ascendente) y por presupuesto (descendente)
3. Todos los datos de los bebedores cuyos alias comienzan con la letra
‘A’
4. Todos los datos de frecuentan donde la fecha de la última visita es
después del 13 de diciembre de 2011
5. Todos los datos de frecuentan donde la fecha de la última visita es
después del 13 de diciembre de 2011 o que fueron visitados en
horario 'diurno‘
Funciones de agregación
• Funciones de agregación
[ AVG | SUM ] ( expresión | [DISTINCT] columna )
[ MIN | MAX ] ( expresión )
COUNT ( [DISTINCT] columna | * )
✓No pueden utilizarse en la cláusula WHERE
✓ Aplican sobre un conjunto, no sobre una tupla…
Ejercicios
Ejercicios – Se quiere saber …
1. Cuántos bebedores hay
2. El promedio del grado de alcohol de las bebidas
3. El número total de sedes de bares
4. Cuántas sedes tiene el bar que más sedes tiene
5. La fecha más antigua de una última visita a un bar
6. Cuántos valores diferentes de grado de alcohol hay
Grupos
• Un grupo esta formado de tuplas que tienen el mismo
valor para una columna específica
• Cláusula GROUP BY
• Cada grupo se trata como un subconjunto de la
respuesta,
• Sobre el cual pueden hacerse operaciones
• Aplican las funciones de agregación, que se evalúan para cada
grupo
• Pueden seleccionarse grupos en la respuesta
• Cláusula HAVING
CBBD
MPVG
Ejercicios – Se quiere saber …
1. El número de bares que hay en cada ciudad
2. El número de sedes de bares que hay en cada ciudad
3. El número de bares y el número de sedes que hay en
cada ciudad
4. El número de bares que hay en cada ciudad, ordenado
descendente por ese número de bares y
ascendentemente por el nombre de la ciudad
Ejercicios – Se quiere saber …
1. Las ciudades que tienen 6 o más bares
2. Las ciudades que tienen 6 o más bares y entre 30 y 50
sedes
3. El nombre de la(s) bebida(s) con mayor grado de
alcohol
SQL: Reunión de relaciones - JOIN
• Join: Selección aplicada al producto cartesiano de
dos tablas
• Reunión interna
• Reunión interna
• Reunión natural interna
• Reunión externa
• Reunión externa por la izquierda/ Reunión externa por la derecha /
Reunión externa completa
• Reunión natural externa por la izquierda/ Reunión natural externa
por la derecha / Reunión natural externa completa
Modelo relacional Parranderos
SQL: Reunión interna
R INNER JOIN S ON Predicado
Se quiere conocer la información de los bebedores junto
con los identificadores de los bares que frecuentan
SELECT Bebedores.*, id_Bar
FROM Bebedores, Frecuentan
WHERE [Link] = Frecuentan.id_Bebedor;
SELECT Bebedores.*, id_Bar
FROM Bebedores INNER JOIN Frecuentan ON
[Link] = Frecuentan.id_Bebedor;
SQL: Reunión natural interna
R NATURAL INNER JOIN S
• La condición de join es la igualdad de los valores de
los atributos que tienen el mismo nombre en R y en S
Dar el identificador de los bebebores que frecuentan
bares que sirven bebidas que les gustan. Incluir el
identificador de los bares y de las bebidas
correspondientes. El resultado debe estar ordenado por
bebedores, bares, bebidas
SELECT id_bebedor, id_bar, id_Bebida
FROM [Link] NATURAL INNER JOIN
[Link] NATURAL INNER JOIN
[Link]
ORDER BY id_Bebedor, id_Bar, id_Bebida;
SQL: Reunión natural interna
Dar el identificador de los bebebores que frecuentan
bares que sirven bebidas que les gustan. Incluir el
identificador de los bares y de las bebidas
correspondientes. El resultado debe estar ordenado por
bebedores, bares, bebidas
SELECT idBebedor, idBebida, idBar
FROM Frecuentan F , Gustan G, Sirven S
WHERE [Link] = [Link] AND
[Link] = [Link] AND
[Link] = [Link]
ORDER BY idBebedor, idBebida, idBar
SQL: Reunión externa – Outer join
por la izquierda/derecha/completa
• Mismo principio que el inner join
• Se incluye en el resultado las tuplas de la tabla A
aunque no cumplan la condición deseada, y se
completa los atributos de la tabla B con NULL
R LEFT OUTER JOIN S ON Predicado
R RIGHT OUTER JOIN S ON Predicado
R FULL OUTER JOIN S ON Predicado
Outerjoins
Outerjoins - Full
Outer joins
Se quiere conocer la información de los bares,
ordenada por su identificador, junto con los
identificadores de los bebedores que los frecuentan,
aún si no son frecuentados por ningún bebedor
SELECT Bares.*, Frecuentan.id_bebedor
FROM [Link] LEFT OUTER JOIN
[Link]
ON [Link] = Frecuentan.id_bar
ORDER BY [Link]
Outer joins
Se quiere conocer la información de los bares que
no son frecuentados por ningún bebedor, ordenada
por su identificador
SELECT Bares.*, Frecuentan.id_bebedor
FROM [Link] LEFT OUTER JOIN
[Link]
ON [Link] = Frecuentan.id_bar
WHERE Frecuentan.id_Bebedor IS NULL
ORDER BY [Link]
SQL: Reunión natural externa por la
izquierda/derecha/completa
R NATURAL LEFT OUTER JOIN S
R NATURAL RIGHT OUTER JOIN S
R NATURAL FULL OUTER JOIN S
Seleccionar los bares junto con los identificadores
de los bebedores que los frecuentan, inclusive los
que no son frecuentados por ningún bebedor
SELECT Bares.*, Frecuentan.id_bebedor
FROM [Link] NATURAL LEFT OUTER JOIN
[Link]
SQL: Vistas
Relación/Tabla “virtual”
Consulta con datos que se utilizan con mucha frecuencia
create view X as <expresión SQL>
Crear una Vista con los bebedores mayores que todos los demás:
CREATE VIEW BebedoresMayores as
(SELECT *
FROM Bebedores
WHERE edad >= (SELECT MAX (edad)
FROM Bebedores) )
SQL - Vistas
• Después de definida, la vista se usa como si fuera una
tabla “normal, común y corriente”
SELECT *
FROM BebedoresMayores
WHERE ciudad = ‘Bogotá’
• Por eficiencia, las vistas se pueden materializar
• Se comportan como tablas dinámicas de Excel
CREATE MATERIALIZED VIEW BebedoresMayores as
(SELECT *
FROM Bebedores
WHERE edad >= (SELECT MAX (edad)
FROM Bebedores) )
Modelo relacional Parranderos
SQL – Ejercicios – Se quiere conocer…
1. El nombre y el presupuesto de los bares que sirven Mojito Yerbabuena
2. El nombre de las bebidas que le gustan al bebedor ‘Sebastian Zamora’, ordenadas
alfabéticamente
3. El nombre de los Bares que sirven al menos una bebida que le gusta al bebedor
“Sebastián Zamora”, ordenados por el nombre del bar y sin repetir
SQL – Ejercicios – Se quiere saber…
1. El nombre de los bebedores que no frecuentan ningún bar
2. El nombre de los bebedores y el de los bares que frecuenta. Si el bebedor no frecuenta ningún bar
debe aparecer
3. El nombre de los bebedores y el número de bares que frecuenta. Si el bebedor no frecuenta ningún
bar debe aparecer indicando que frecuenta 0 bares.
4. El nombre del bar que es frecuentado por más bebedores
SQL: Funciones de conjuntos
Tupla vs conjunto
• IN
• EXISTS
• NOT IN / NOT EXISTS
Conjunto vs conjunto
• R INTERSECT S
• R UNION S
• R MINUS S
• R y S deben ser UNION_COMPATIBLES
• Mismo número de atributos
• El tipo de cada atributo debe
corresponder
SQL – Ejercicios – Se quiere saber…
1. El nombre de los Bebedores que frecuentan bares que sirven alguna bebida que les gusta
2. El nombre de los Bebedores que solo frecuentan bares que NO sirven bebidas que le gusten.
3. El nombre de los bebedores que solo frecuentan bares que sirven alguna bebida que les guste
4. El nombre de las bebidas que les gustan a todos los bebedores
5. El nombre de los bares frecuentados por todos los bebedores
FIN DE LA PRESENTACIÓN
