Transcript Clase 15 - Lenguaje de Consultas Estruturado (SQL)

IBD
Clase 15
SQL

Lenguaje de Consultas Estruturado (SQL)

Lenguaje de trabajo estándard para modelo
relacional

Componentes
• DDL: Data Definition Language
• DML: Data Manipulation Language (AR y CRT)
2
IBD - CLASE 15
UNLP - Facultad de Informática
SQL


3
DDL - Lenguaje de definición de datos.
• Definición de esquemas,relaciones, indices y vistas (una
vista es una tabla virtual, ya que sus filas no se
almacenan físicamente, sino que son producto de una
consulta)
• Autorizaciones al acceso a datos
• Definición de reglas de integridad.
• Control de Concurrencia
DML - Lenguaje interactivo de manipulación de datos.
• Consultar datos almacenados.
• Modificar el contenido de los datos almacenados.
IBD - CLASE 15
UNLP - Facultad de Informática
SQL



4
Estructura básica: 3 cláusulas
• Select (equivale a )
• From (equivale a x)
• Where (equivale a )
a1,...,an (p (r1 x ... X rm ) ) equivale a
Select a1,..., an
From r1,..., rm
Where P
Ej1: todas las sucursales de la
relación sucursal
IBD - CLASE 15
UNLP - Facultad de Informática
SQL
Select
• * (incluye todos los atributos de las
tablas que aparecen en el from)
• Distinct (eliminan tuplas
duplicadas)
• All (valor por defecto, aparecen
todas las tuplas)
5
IBD - CLASE 15
UNLP - Facultad de Informática
SQL


Ej2: nombres de las sucursales en la
relación préstamo sin repetición
Operaciones en el select
• Select nombre, saldo * 3
From cliente

Where
• Operadores lógicos
• Ej3: préstamos hechos en sucursal X y monto
superior a 20000$
• Between
• Ej4: préstamos cuyo monto este entre 20000 y
30000$
6
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

From: producto cartesiano
• Ej5: nombre de cliente y # prestamo, de la sucursal X.

Renombre: tanto para relaciones como para
atributos
• Atributos: presentarlo con otro nombre
• Select T1.Pac_numero AS Nro_Paciente,
from Atenciones AS T1
• Relaciones: un producto cartesiano contra si mismo
• Ej 6: nombre de las sucursales que poseen activo mayor
que al menos una sucursal situada en Buenos Aires.
7
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Operaciones sobre strings
• Like, %, _
• “Alfa%”: cualquier cadena que empiece con
Alfa
• “%casa%”: cualquier cadena que tenga casa
en su interior
• “_ _ _”: cualquier cadena con tres caracteres
• “_ _ _%”: cualquier cadena con al menos tres
caracteres.
• Ej7: nombre del clientes cuya domicilio
contenga el string XXX
8
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Ordenamiento de las tuplas resultado
• Order By atributo: especifica el atributo por el
cual las tuplas serán ordenadas
• Ej8: presentar todos los clientes ordenados por
nombre.
• Desc, asc: por defecto ascendente, se puede
especificar descendente.
• Facturas=(Nro,Fecha,Hora)
• Ej9: presentar las facturas del mes de agosto
ordenadas por fecha desde el 31 al 1 de agosto y
por hora de realización.
9
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Operaciones sobre conjuntos
• Unión: agrupa las tuplas resultantes de dos
subconsultas. Union all conserva duplicados
• Ej10: clientes con cuentas o prestamos en un
banco
• Intersección: (intersect) idem anterior.
• Ej11: clientes con cuentas y préstamos en un
banco
• Diferencia: (except)
• Ej12: clientes con cuentas y sin préstamos en un
banco
10
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Funciones de agregación:
• Promedio (avg): aplicable a atributos numéricos,
retorna el promedio de la cuenta
• Mínimo (min): retorna el menor elemento no nulo
dentro de las tuplas para ese atributo
• Máximo (max): retorna el mayor elemento no nulo
dentro de las tuplas para ese atributo
• Total (sum): aplicable a atributos numéricos,
realiza la suma matemática
• Cuenta (count): cuenta las tuplas resultantes.
11
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Agrupamientos (group by):
• Permite agrupar un conjunto de tuplas por algun
criterio
• Ej13: obtener el saldo promedio de las cuentas de
cada sucursal.
• Ej14: contar el número de clientes que tiene cada
sucursal.
• Having: permite aplicar condiciones a los grupos
• Ej15: presentar las sucursales y su saldo promedio
siempre y cuando superen 20000$
• Ej16: saldo promedio de cada cliente que vive en
La Plata, y tienen al menos 3 cuentas.
12
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Valores nulos:
• Ej17: Mostrar aquellos préstamos que
tengan el importe nulo. (no significa 0)

Subconsultas anidadas
• Pertenecia a conjuntos: IN
• Ej18: clientes con prestamos y cuentas en el
banco, cualquier sucursal (otra forma)
• Ej19: clientes que tengan prestamo y cuenta
en la sucursal llamada “La Plata”
13
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Comparación de Conjuntos
• > some ( <, =, >=, <=, <>)
• Ej20:presentar las sucursales que tengan
activo mayor que alguna otra (otra forma)
• > all ( <, =, >=, <=, <>)
• Ej21: presentar la sucursal que tenga activo
superior a todas (otra forma)
• Ej22: encontrar la sucursal que tiene el mayor
saldo promedio.
14
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Cláusula Exist: devuelve verdadero si
la subconsulta argumento no es vacía.
• Ej23: obtener los clientes que tienen tanto
una cuenta como un préstamo en el banco.
• Ej24: obtener los clientes que tienen
cuentas en todas las sucursales de la
ciudad de Buenos Aires.
15
IBD - CLASE 15
UNLP - Facultad de Informática
SQL
• Comprobación de tuplas
duplicadas.
• Unique: devuelve verdadero si la
subconsulta argumento no produce
tuplas duplicadas.
• Ej25: clientes que tienen una sola
cuenta en la sucursal llamada XXX.
16
IBD - CLASE 15
UNLP - Facultad de Informática
SQL
 Creación
de vistas
• Una vista es un objeto que no contiene datos
por si mismo. Es una clase de tabla cuyo
contenido es tomado de otras tablas por medio
de la ejecución de una consulta.
• Create View nombre as <expresion>
• Ej26: crea una vista con todos los clientes y
consultar de ahí todos los de sucursal XXX
17
IBD - CLASE 15
UNLP - Facultad de Informática
SQL
 Modificación
de la BD
• Borrado: eliminar una o mas filas de una
tabla:
• DELETE FROM tab_name
[WHERE condición];
• Ej27: borrar las cuentas de una sucursal
• Ej28: borrar las cuentas con saldo entre
100 y 200.
18
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Inserción:
• INSERT INTO tab_name (<column_name>,)
VALUES (<valor>,)
• Existen dos maneras básicas de insertar.
• Insertar la fila completa
• Insertar sólo algunas columnas de una fila
• En el segundo caso se debe necesariamente
especificar los nombres de las columnas que se van
a completar.
• Ej29: agregar una cuenta
19
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Actualización
• Ej30: modificar el saldo de las cuenta incrementar
en un 5%.

Unión de relaciones
• Realizar en cláusula From productos naturales
• Inner Join: producto natural entre atributos que se
indican, quedando el atributo en común repetido
• Ej31: producto entre préstamo y propietarioprestamo
20
IBD - CLASE 15
UNLP - Facultad de Informática
SQL
• Left outer Join: primero se calcula el inner join
(idem anterior) y luego cadat tupla t perteneciente
a la relación de la izquierda que no encontro par
aparece en el resultado con valores nulos en los
atributos del segundo lado.
• Right outer Join: idem anterior pero aparecen las
tuplas t de la relación de la derecha
• Full outer join: aparecen las tuplas colgadas de
ambos lados.
• Otras variantes:
• Natural: evita que el atributo común (por el que se
hace la unión aparezca dos veces)
21
IBD - CLASE 15
UNLP - Facultad de Informática
QBE

Query By Example:
Sintaxis bidimensional: una consulta
se expresa como una tabla
 Se expresa la consulta con un
“ejemplo”
 Se basa en el cálculo relacional de
dominios
 Ejemplos en Access

22
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Ejercicios:

Tablas
• Proveedor=(#prov, prnombre, situación,
ciudad)
• Partes(#par, color, panombre, situación,
ciudad)
• Proyectos=(#proy, proynombre, ciudad)
• RPPP=(#prov, #par, #proy, cantidad)
23
IBD - CLASE 15
UNLP - Facultad de Informática
SQL

Ejercicios
A. Obtener todos los detalles de los proyectos de
Córdoba
B. #prov, que suministre parte al proyecto A1
ordenado por proveedor
C. Envios con cantidad entre 300 y 500
D. #prov, #proy, #par para aquellas tuplas donde
los tres elementos sean de la misma ciudad
E. #parte suministradas por un proveedor de
Córdoba a un Proyecto de Córdoba
F. Cantidad de proyectos que tenga a S1 como
proveedor
G. Cantidad total de partes P1 suminstradas por
S1
24
IBD - CLASE 15
UNLP - Facultad de Informática
SQL
H. Envíos que no tengan la cantidad nula
I. Obtener los colores de las partes suministras
por proveedor S1.
J. Obtener proyectos para los cuales s1 es el
único proveedor
K. Cambiar a Gris el color de las partes Rojas
L. Eliminar el proyecto que no tenga envíos.
M. Proveedor que vivan en igual ciudad que el
proveedor S1.
N. Proveedor que tenga máxima su situación
O. Todos los proveedores menos el que tenga
máxima su situación
P. Nombres de los proveedores que suministran la
parte P2.
25
IBD - CLASE 15
UNLP - Facultad de Informática
SQL
Q. Presentar aquellos proveedores que
suministren todas las partes existentes en la
tabla
R. Obtener los número de partes provistas por más
de un proveedor. Para este caso considerar que
un proveedor sólo participa en un proyecto.
S. Informar el número de parte que se suministre a
un proyecto cualquiera tal que en promedio se
suministro supere 200.
26
IBD - CLASE 15
UNLP - Facultad de Informática