11 consultas - sql

IBD 2010

SQL

(Structured Query

Language) Lenguaje de consulta estructurado

SQL: Lenguaje formado por

comandos/sentencias, operadores, tipos de

datos, funciones predefinidas, clusulas, etc.

SQL: Lenguaje declarativo de acceso a

bases de datos relacionales que permite

especificar diversos tipos de operaciones

sobre las mismas.

SQL

Numricos: INTEGER, SMALLINT, DECIMAL, FLOAT.

Fecha: DATE, DATETIME, TIME.

Cadena de caracteres: CHAR(N), VARCHAR(N), TEXT,

MEMO.

Cadenas binarias: BINARY, BLOB.

NOTA: No existe el tipo BOOLEAN.

SQL: Tipos de datos

Es compatible con cualquier tipo, NULL

puede indicar: No aplicable, Desconocido.

Para preguntar si un valor es Null,

generalmente se provee la forma especial IS

NULL o IS NOT NULL (en vez de = Null o

Null)

SQL: el valor NULL

SQL tiene dos sub-lenguajes de comandos:

DDL (Data Definition Language): contiene los

comandos usados para crear y destruir objetos de

la base de datos, estos comandos son: CREATE,

DROP, ALTER.

DML (Data Manipulation Language): contiene los

comandos para manipular los datos, estos

comandos son: SELECT, INSERT, UPDATE,

DELETE.

SQL: comandos/sentencias

SELECT lista de expresiones TOP n 6

FROM lista de tablas 1

WHERE lista de condiciones 2

GROUP BY lista de campos de agrupamiento 3

HAVING lista de condicones que deben cumplir los grupos 4

ORDER BY lista de criterios de ordenamiento 5

UNIONALL

Cada lnea se llama clusula. El SELECT y el FROM

son obligatorias, las dems son opcionales.

SQL: Sintaxis general

CURSO = (COD_CURSO, NOMBRE, DESCRIPCIN, COSTO, DURACIN)

PRERREQUISITO = (COD_CURSO, COD_CURSO_REQ, MODO)

EDICIN = (COD_CURSO, FEC_INICIO, COD_EMPLEADO, FECHA_FIN,

HORARIOS)

EMPLEADO = (COD_EMPLEADO, CUIL, APYN, SEXO, FEC_NAC,

SALARIO, CAPACITADO, COD_DTO, F_DESDE, CALLE, NUMERO, PISO,

DTO, LOCALIDAD, CP, TEL, F_INGRESO)

DEPARTAMENTO = (COD_DTO, NOMBRE, TECNICO)

ASISTIO = (COD_CURSO, FEC_INICIO, COD_EMPLEADO)

HISTORIAL_EMPLEADO_DTO = (COD_EMPLEADO, COD_DTO,

F_DESDE, F_HASTA)

Esquema de relaciones

b) Listar el nombre y costo de los cursos cuya duracin sea

menor de 30 horas.

a) Si quiero listar toda la informacin de todos los cursos.

SELECT *

FROM CURSO

SELECT NOMBRE, COSTO

FROM CURSO

WHERE (DURACION < 30)

SQL: Consultas Simples

c) Listar el cdigo de aquellos empleados que

efectivamente dictaron algn curso.

SELECT DISTINCT (COD_EMPLEADO) as cdigo

FROM EDICION

Si hay un empleado que dict ms de una edicin de

un curso aparecera ms de una vez. Entonces, para

que no se repitan utilizamos la clusula DISTINCT.


d) Listar el apellido, nombre y salario de todos los

empleados, adems para cada empleado detallar el sueldo

aumentado en un 10%.

SELECT APYN, SALARIO,( ((SALARIO * 10) / 100)

+ SALARIO) AS SALARIO_AUMENTADO

FROM EMPLEADO


CASE valor WHEN op1 THEN resultado 1

WHEN op2 THEN resultado 2

ELSE opcin else END

e) Listar el apellido, nombre y sexo de todos los

empleados. El detalle del sexo debera aparecer de la

forma Femenino o Masculino, no F o M.

SELECT APYN, CASE SEXO WHEN F THEN FEMENINOWHEN M THEN MASCULINOELSE INDEFINIDO END AS Sexo_Detalle

FROM EMPLEADO

SQL: Funcin CASE

f) Listar el apellido y nombre de aquellos empleados que

posean un salario entre 2000 y 3000 pesos.

SELECT APYN

FROM EMPLEADO

WHERE (SALARIO BETWEEN 2000 AND 3000)

SQL: Between

El between incluye los valores de los extremos, es decir, aquellos

empleados con salario 2000 y 3000 tambin aparecen en el listado.

g) Listar el apellido y nombre de aquellos empleados

cuyo cdigo postal sea alguno de lo siguientes: 1900,

2173, 2717.

SELECT APYN

FROM EMPLEADO

WHERE CP IN (1900, 2173, 2717)

h) Listar el cdigo de curso y fecha de finalizacin de

aquellas ediciones que an no finalizaron.

SELECT COD_CURSO, FECHA_FIN

FROM EDICION

WHERE (FECHA_FIN IS NULL)

SQL: IN IS NULL

i) Listar el apellido y nombre de aquellos empleados que

vivan en una localidad cuyo nombre contenga la cadena

MON.SELECT APYN

FROM EMPLEADO

WHERE (LOCALIDAD LIKE %MON%)

j) Listar el apellido y nombre de aquellos empleados que

vivan en una localidad cuyo nombre comience con la

cadena MON.

SELECT APYN

FROM EMPLEADO

WHERE (LOCALIDAD LIKE MON%)

SQL: LIKE

SQL: LIKE

% Cualquier cadena de cero o ms caracteres

WHERE Titulo LIKE '%computadora%' busca todos los ttulos de libros que contengan

la palabra 'computadora' en cualquier parte del ttulo.

_ (subrayado) Cualquier carcter individual.

WHERE Sigla LIKE '_RLA' busca todas los siglas de cuatro letras que finalicen con

RLA.

[ ] Cualquier carcter individual de intervalo ([a-f]) o del conjunto ([abcdef])

especificado.

WHERE Apellido LIKE '[C-P]arrizo' busca apellidos de autores que terminen con arrizo y

comiencen con cualquier carcter individual entre C y P.

k) Listar toda la informacin de aquellos empleados con

fecha de ingreso superior al 01/01/1995. Dicho listadodeber estar ordenado de manera ascendente por

apellido y nombre por salario de manera descendente.

SELECT *

FROM EMPLEADO

WHERE (F_INGRESO > 01/01/1995)ORDER BY APYN Asc, SALARIO Desc

SQL: Order By Asc/Desc

La ordenacin puede ser ascendente (ASC) o descendente (DESC). Si no se

especifica ninguna, se supone que es ASC.

l) Proyectar el salario mximo entre todos los empleados.

SELECT MAX (SALARIO)

FROM EMPLEADO;

SELECT MIN (SALARIO)

FROM EMPLEADO;

m) Proyectar el salario mnimo entre todos los empleados.

El MAX y el MIN pueden juntarse en una sola consulta:

SELECT MAX(SALARIO) AS MAXIMO, MIN(SALARIO) AS

MINIMO

FROM EMPLEADO

SQL: MAX MIN

n) Proyectar la cantidad de empleados existentes.

SELECT COUNT(*) AS CANTIDAD

FROM EMPLEADO

o) Proyectar la cantidad de empleados, el promedio de

salarios, la menor fecha de nacimiento y la suma de los

salarios de aquellos empleados que pertenezcan al

departamento 20.

SELECT COUNT(COD_EMPLEADO) AS cant_emp,

AVG(SALARIO) AS prom_salario, MIN(FEC_NAC) AS

min_fecha, SUM(SALARIO) AS total_salario

FROM EMPLEADO

WHERE (COD_DTO = 20)

SQL: COUNT AVG - SUM

Indica que se han de agrupar las filas de la relacin de tal manera que toda las que tengan

iguales valores en columnas de agrupamiento

formen un grupo.

Puede haber grupos que slo tengan una fila. Los valores nulos en las columnas de agrupamiento se consideran todos iguales, es decir,

forman el mismo grupo.

En el select slo puedo hacer referencia a campos que estn en el group by o a funciones

totalizadoras (count, sum, max, min, avg).

SQL: GROUP BY

p) Proyectar la cantidad de empleados por sexo del

departamento 21.

SELECT SEXO, COUNT(COD_EMPLEADO) AS CANTIDAD

FROM EMPLEADO

WHERE (COD_DTO = 21)

GROUP BY SEXO

q) Proyectar para cada curso el cdigo del mismo y la cantidad

total de asistentes. El listado resultante deber estar ordenado

por cantidad de asistentes en forma descendente.

SELECT COD_CURSO, COUNT(COD_EMPLEADO)

AS CANTIDAD

FROM ASISTIO

GROUP BY COD_CURSO ORDER BY 2 DESC

SQL: GROUP BY

HAVING: clusula que permite aplicar una condicin sobre el

agrupamiento.

r) Proyectar el cdigo de curso y la cantidad de asistentes

para aquellos cursos que posean ms de 20 asistentes.

El listado resultante deber estar ordenado por cantidad

de asistentes en forma descendente.

SELECT COD_CURSO, COUNT(COD_EMPLEADO)

AS CANTIDAD

FROM ASISTIO

GROUP BY COD_CURSO

HAVING COUNT (COD_EMPLEADO) > 20

ORDER BY 2 DESC

SQL: HAVING

Una subConsulta es una sentencia SELECT que

est embebida en alguna clusula dentro de otra

sentencia SELECT, dichas clusulas pueden ser:

WHERE, HAVING, FROM (en esta ltima tambin

recibe el nombre de tabla derivada).

Los operadores para comparar con un subconsulta

en el where (o having) pueden ser: >, >=,

s) Listar el cdigo de empleado, apellido y nombre de

aquellos empleados que poseen un salario mayor que el

empleado con cdigo 800.

Se necesitan dos consultas: una para saber cual es el salario

del empleado con cdigo 800 y la segunda para saber

quines son los que superan dicho salario. Para lograr esto

se ubica una de las consultas dentro de la otra:

SELECT COD_EMPLEADO, APYN

FROM EMPLEADO

WHERE (SALARIO > (SELECT SALARIO FROM

EMPLEADO WHERE (COD_EMPLEADO = 800)))

SQL: SUBCONSULTAS

t) Listar el nombre y apellido de los empleados que

pertenezcan a departamentos tcnicos.

SELECT APYN

FROM EMPLEADO

WHERE COD_DTO IN (SELECT COD_DTO

FROM DEPARTAMENTO

WHERE (TECNICO =SI))

NOTA: Podemos obtener lo contrario utilizando

NOT IN.

SQL: SUBCONSULTAS

u) Listar el nombre y apellido de los empleados cuyo salario

sea menor que el de todos los empleados que ingresaron

antes de 01/03/2010.

SELECT APYN

FROM EMPLEADO

WHERE SALARIO < ALL (SELECT SALARIO

FROM EMPLEADO

WHERE (F_Ingreso < 01/03/2010))

SQL: SUBCONSULTAS

SQL: JOIN

La sentencia JOIN (INNER JOIN) en SQL permite combinar

registros de dos o ms tablas en una base de datos

relacional.

Combinacin interna (INNER JOIN): Con esta operacin se calcula el producto

cruzado de todos los registros. Cada registro en la tabla A es combinado con cada

registro de la tabla B, pero slo permanecen aquellos registros en la tabla combinada

que satisfacen las condiciones que se especifiquen. Este es el tipo de JOIN ms

utilizado por lo que es considerado el tipo de combinacin predeterminado.

Combinacin externa (OUTER JOIN): Mediante esta operacin no se requiere que

cada registro en las tablas a tratar tenga un registro equivalente en la otra tabla. El

registro es mantenido en la tabla combinada si no existe otro registro que le

corresponda.

de tabla izquierda (LEFT OUTER JOIN)

de tabla derecha (RIGHT OUTER JOIN)

combinacin completa (FULL OUTER JOIN)

v) Listar cdigo de curso, nombre y fecha de inicio de todos los

cursos a los que asisti el empleado con nombre y apellido

zzz, yyy.

SELECT A.COD_CURSO, NOMBRE, A.FEC_INICIO

FROM ASISTIO A, EMPLEADO E, CURSO C

WHERE (A.COD_EMPLEADO = E.COD_EMPLEADO) AND

(A.COD_CURSO = C.COD_CURSO) AND (E.APYN = zzz, yyy)

w) Sintaxis alternativa (expresar la condicin de JOIN en el

FROM)

SELECT A.COD_CURSO, A.FEC_INICIO

FROM ASISTIO A INNER JOIN EMPLEADO E ON

(A.COD_EMPLEADO = E.COD_EMPLEADO) INNER JOIN

CURSO C ON (A.COD_CURSO = C.COD_CURSO)

WHERE (E.APYN = zzz, yyy)

SQL: JOIN

x) Listar el cdigo, nombre y apellido de aquellos

empleados que pertenezcan a un departamento tcnico.

SELECT COD_EMPLEADO, APYN

FROM EMPLEADO E

WHERE EXISTS (SELECT * FROM DEPARTAMENTO D

WHERE (D.COD_DTO = E.COD_DTO) AND (TECNICO =

SI))

SQL: EXISTS

Listar el nombre de todos los empleados que hayan

asistido a todos los cursos.

Select E.APyN

From Empleado E

where not exists ( select *

from Curso C

where not exists ( select *

from Asistio A

where (A.Cod_Empleado = E.Cod_Empleado)

and (A.Cod_Curso = C.Cod_Curso ) )

SQL: EXISTS

El resultado de esta operacin siempre contiene todos

los registros de la tabla de la izquierda (la primera tabla

que se menciona en la consulta), an cuando no exista

un registro correspondiente en la tabla de la derecha,

para uno de la izquierda.

La sentencia LEFT OUTER JOIN retorna la pareja de

todos los valores de la tabla izquierda con los valores

de la tabla de la derecha correspondientes, o retorna un

valor nulo (NULL) en caso de no correspondencia.

SQL: LEFT OUTER JOIN

y) Listar el nombre, apellido y cdigo de curso realizado de

aquellos empleados pertenecientes al departamento de

SISTEMAS.

SELECT E.APYN, A.COD_CURSO

FROM EMPLEADO E LEFT OUTER JOIN ASISTIO A ON

(E.COD_EMPLEADO = A.COD_EMPLEADO), DEPARTAMENTO D

WHERE (E.COD_DTO = D.COD_DTO) AND (D.NOMBRE =

SISTEMAS)

NOTA: Qu sucede si usamos un JOIN y un empleado

del departamento de SISTEMAS no asisti a ningn curso todava? No aparece en el listado.

SQL: LEFT OUTER JOIN

Esta operacin es inversa a la anterior; el resultado de

esta operacin siempre contiene todos los registros de la

tabla de la derecha (la segunda tabla que se menciona

en la consulta), aun cuando no exista un registro

correspondiente en la tabla de la izquierda, para uno de

la derecha.

La sentencia RIGHT OUTER JOIN retorna la pareja de

todos los valores de la tabla derecha con los valores de la

tabla de la izquierda correspondientes, o retorna un valor

nulo (NULL) en caso de no correspondencia.

SQL: RIGHT OUTER JOIN

Esta operacin presenta los resultados de la

tabla izquierda y la tabla derecha aunque no

tengan correspondencia en la otra tabla. La

tabla combinada contendr, entonces, todos los

registros de ambas tablas y presentar valores

nulos (NULL) para registros sin pareja.

SQL: FULL OUTER JOIN

Cuando hacemos JOIN en ciertas oportunidades

recuperamos filas de dos relaciones cuya cardinalidad (en el

MER) viene de muchos a muchos (N a N) como por ejemplo

en la relaciones EMPLEADO y ASISTIO, pero otras veces

recuperamos filas de dos relaciones cuya cardinalidad viene

de uno a muchos (1 a N), por ejemplo en las relaciones

EMPLEADO y DEPARTAMENTO (un empleado pertenece a

un nico departamento, con lo cual una fila de empleado se

relacionar como mximo con una fila de departamento). La

mayora de los ejemplos para esta ltima situacin es con

relaciones que representan codificaciones (en este caso DEPARTAMENTO codifica los nmeros de departamentos

posibles en la empresa). En estos casos, una alternativa de

planteo de consulta es utilizar una subconsulta relacionada.

SQL: Select a nivel de Select

1) Listar apellido, nombre y nombre del departamento de

todos los empleados.

SELECT E.APYN, (SELECT NOMBRE FROM

DEPARTAMENTO D

WHERE D.COD_DTO = E.COD_DTO) AS

Nombre_Departamento

FROM EMPLEADO E

SQL: Select a nivel de Select

2) Listar todos los departamentos en los que trabaj

(inclusive el actual) el empleado con cdigo 50, con el

detalle de la fecha hasta y NULL para el caso del

departamento vigente en el que trabaje.

(SELECT COD_DTO, NULL AS Fecha_HastaFROM EMPLEADO

WHERE (COD_EMPLEADO = 50)

UNION

SELECT COD_DTO, F_HASTA AS Fecha_Hasta

FROM HISTORIAL_EMPLEADO_DTO

WHERE (COD_EMPLEADO = 50))

ORDER BY 2

SQL: UNION UNION ALL

Es importante notar que para todos los operadores de

conjunto (Union, Union All, etc.) las relaciones deben tener

ESQUEMA COMPATIBLE (misma cantidad de columnas con

el mismo dominio/tipo).

EL operador UNION excluye las tuplas repetidas en forma

automtica, en este caso puede ser que el empleado 50 haya

pasado por el departamento 10, luego por el 20 y nuevamente

por el 10, o sea que si quiero obtener los repetidos debera

usar el UNION ALL que no elimina los repetidos (es ms

eficiente que el UNION por no hacer testeo de repeticiones).

Otras operaciones de conjunto: INTERSECT, EXCEPT (o

MINUS):

SQL: UNION UNION ALL

SQL: INSERT UPDATE - DELETE

3) Insertar en la relacin asisti que el empleado con

cdigo 50 a asistido a la edicin del curso 30 que empez

el 01/09/2010.

INSERT INTO ASISTIO (COD_CURSO, FEC_INICIO,

COD_EMPLEADO)

VALUES (30, 01/09/2010, 50)

4) Registrar que el empleado zzz, yyy a asistido al cursoIBD, a la edicin que empez el 07/04/2010.


COD_EMPLEADO)

VALUES ((SELECT Cod_Curso FROM CURSO WHERE (Nombre =

IBD)), 07/04/2010, (SELECT Cod_Empleado FROM EMPLEADO WHERE (APYN = zzz, yyy)));


5) Registrar que al curso con cdigo 20 y edicin que

empez el 06/03/2010 asistieron todos los empleados del departamento 30, excepto el empleado con cdigo 10.


COD_EMPLEADO)

SELECT 20, 06/03/2010, Cod_EmpleadoFROM EMPLEADO

WHERE (COD_DTO = 30) and (COD_EMPLEADO 10)


6) Modificar los siguientes datos del empleado con

cdigo 20: en fecha de nacimiento asignarle

02/11/1972, aumentarle en $100 el salario y que ahora va a trabajar en el departamento de Recursos Humanos.

UPDATE EMPLEADO

SET Fec_Nac = 02/11/1972,Salario = Salario + 100,

COD_DTO = (SELECT COD_DTO FROM

DEPARTAMENTO WHERE (Nombre = Recursos Humanos)WHERE (Cod_Empleado = 20)


7) Dar de baja toda la informacin de la edicin del

curso con cdigo 50 que empez el 20/02/2010.

DELETE FROM ASISTIO

WHERE (cod_curso = 50) and (fec_inicio = 20/02/2010)

(tantas filas como asistentes hubo a esa edicin)

DELETE FROM EDICION

WHERE (cod_curso = 50) and (fec_inicio = 20/02/2010)

(1 sola fila)

PREGUNTAS?

11 consultas - sql

Documents