© 2007 Fundamentos de Bases de Datos L. Gómez1 Fundamentos de Bases de Datos El lenguaje estándar...

© 2007 Fundamentos de Bases de Datos L. Gómez 1

Fundamentos de Bases de Datos

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

Structured Query Language (SQL)


Base de datos Northwind

Esta BD contiene información de pedidos, clientes, empleados, productos, proveedores, etc.

Las llaves primarias están en negritas


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


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


Ejecutando las consultas

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

Ejecutar query

Resultados


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] [HAVING condition][ORDER BY columnList]


El estatuto SELECT

SELECTEspecifica las columnas a mostrar en el resultado

FROMEspecifica la(s) tabla(s) a utilizar.

WHEREFiltra renglones de acuerdo a la condición

especificada.GROUP BY

Forma grupos de renglones con el mismo valor en la columna

HAVINGFiltra grupos de acuerdo a la condición especificada.

ORDER BY Especifica como ordenar el resultado.


Mostrar solo algunos atributos

Obtener solo algunos atributos y todos los registros de una tablaSELECT

A1, A2, …, An

FROM r1


Evitar tuplas duplicadas

Mostrar en que ciudades hay clientes

Se repiten las ciudades

Usar DISTINCTNo se repiten


Especificando una condición

Obtener nombre de compañía y del contacto de los clientes que viven en London


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


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


Especificando varias condiciones (OR)

Obtener nombre de compañía y del contacto de los clientes cuya ciudad sea London o Buenos Aires


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)


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)


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)


Verificando valores NULL

Obtener Id del cliente, nombre de la compañía y la Region para aquellos clientes cuya Region sea NULL

NullValor que significaque al atributo no se le asignó un valoro se le asignó NULL


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


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


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


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(*) Regresa el número de registros en la tabla

especificada

MIN Regresa el mínimo de los contenidos de la columna

especificada. Primero elimina NULLs

MAX Regresa el máximo de los contenidos de la columna

especificada. Primero elimina NULLs


Funciones en SQL

Las siguientes funciones aplican SOLO a valores numéricos :

SUM

Regresa la suma de los contenidos de la columna especificada. Primero elimina NULLs

AVG

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


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


COUNT eliminando valores duplicados

Usar DISTINCT para eliminar regiones que se repiten. Comparar los resultados obtenidos con cada query


Ejemplo de MIN y MAX

Obtener el costo del producto mas barato Obtener el costo del producto más caro


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


Misceláneo: MIN, MAX, AVG

Varias funciones en el estatuto Select


Uso de Funciones

IMPORTANTE

Las funciones COUNT, MIN, MAX, SUM, AVG

sólo pueden usarse en SELECT HAVING (se verá más adelante)


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


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

SELECT CategoryID, count(CategoryID)FROM ProductsGROUP BY CategoryID

ID 112 prod.


Count y Group by

Se obtiene el número de productos que existen de cada categoría


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)


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 dondela FK=PK


Join entre 2 tablas

Nombre del producto, Id de categoría y Nombre de la categoría

FK

PK


Count y Group by

número de productos existentes de cada categoría


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


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


SQL y Algebra Relacional

SQL query tipicoSELECT 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)).


Operadores Union, Interseccion y Diferencia

Operaciones con tablas


SQL y Algebra Relacional

El asterisco (*) se refiere a todos los atributos o columnas de la tabla.

Algebra Relacional

SQL

r U s (select * from r) union (select * from s)

r – s (select * from r) except (select * from s)

r x s Select * from r,s

σP(r) Select * from r where P

πA(r) Select A from r


Union

Obtener todos los países donde hay un cliente o un proveedor


Interseccion

Obtener todos los países donde hay proveedores y clientes


Diferencia (Except)

Obtener los países donde hay proveedores, pero no hay clientes

Obtener los países donde sólo hay clientes y no hay provedores


Ejemplos en SQL

cse_majors U eee_majors: Select * from cse_majors

unionselect * from eee_majors

cse_majors – eee_majors: Select * from cse_majors

minusselect * 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’

Id Name Class

1111

Student1

Fr

2222

Student2

So

3333

Student3

Jr

4444

Student4

Sr

5555

Student5

GrId Name Class

2222

Student2

So

4444

Student4

Sr

6666

Student6

Sr

cse_majors

eee_majors

cse_courses

Crsid Crstitle

PR1 Programacion 1

DB1 Bases de Datos

DB2 Bases de Datos Avanzadas

Cse_profsName

Office

Prof1 Office1

Prof2 Office2


Ejemplos adicionales

πID, NAME(cse_majors): Select id, name from cse_majors

cse_majors ∩ eee_majors: Select * from cse_majors

intersectselect * from eee_majors

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.crstitlefrom cse_profs P, cse_courses C, teaches Twhere P.name = T.name and

T.crsid = C.crsid

Id Name Class

1111

Student1

Fr

2222

Student2

So

3333

Student3

Jr

4444

Student4

Sr

5555

Student5

GrId Name Class

2222

Student2

So

4444

Student4

Sr

6666

Student6

Sr

cse_majors

eee_majors

cse_coursesCrsid Crstitle

PR1 Programacion 1

DB1 Bases de Datos


teaches

name

crsid

Prof1 PR1

Prof1 DB1

Prof2 DB2

Prof1 DB2

Cse_profsName

Office

Prof1 Office1

Prof2 Office2


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 nameFROM emp E, takes TWHERE E.id = T.id AND

T.date = ’25Jun90’ AND T.crsid = ‘BD1’


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.nombreFROM emp E, emp M, dept DWHERE E.nombre= ‘John Smith’ and

E.dnum = D.dnum AND D.idJefe = M.id


SQL

Encuentre los empleados con los salarios mas altos

emp(ID, NOMBRE, SALARIO, SUPERID, DNO)

Select id, nombre, salarioFrom empWhere salario =

(select max(salario) from emp)


Queries Anidados

Encuentre los alumnos que tienen una especialidad (major) en computer science y en electrical engineering.

select id, namefrom cse_majorswhere id in

( select id from eee_majors)

Evaluacion: (1) query anidado(2) query externo utilizando los resultados del query interno


Subqueries Encuentre los empleados que no han tomado algun curso SQL: select name

from emp Ewhere not exists

(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)

emp(id, name)takes( id, crsid, date)course(crsid, name)


DB

Id Name Class

1111

Student1

Fr

2222

Student2

So

3333

Student3

Jr

4444

Student4

Sr

5555

Student5

GrId Name Clas

s

2222

Student2

So

4444

Student4

Sr

6666

Student6

Sr

cse_majors

ee_majors

Cse_profs

cse_courses

Has_taughtName

Office

Prof1 Office1

Prof2 Office2

Crsid Crstitle

PR1 Programacion 1

DB1 Bases de Datos


name crsid

Prof1 PR1

Prof1 DB1

Prof2 DB2

Prof1 DB2


Definición de tablas en SQL(Create)

Crear una relación o tablaCREATE 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)

.


Borrar, Modificar estructura

Borrar una tablaDROP TABLE <tablename>

Agregar un atributo a una tablaALTER TABLE <tablename>ADD <colname> <coltype>


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 enrollmentset 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ómez1 Fundamentos de Bases de Datos El lenguaje estándar...

Documents

Transcript of © 2007 Fundamentos de Bases de Datos L. Gómez1 Fundamentos de Bases de Datos El lenguaje estándar...