Guía #3 - SubConsultas

Análisis y Diseño de Bases de Datos con Sql Server 2008

SUBCONSULTAS

Es una SELECT anidada en una instrucción INSERT, DELETE, SELECT o UPDATE. 

Como una tabla derivada

Select e.emp_no as Select e.emp_no as Numero Numero ,e.Apellido from ( from (select select emp_no, emp_no, apellido from from emp) emp) as as e e



Como una expresión

Select emp_no as Select emp_no as [Numero] [Numero] ,Apellido ,Salario ,(select ,(select avg avg(Salario) (Salario) from from emp) emp) as Diferencia as Diferencia from emp from emp where oficio where oficio = 'Empleado'



Para correlacionar datos:

-

Expresión dinámica que cambia en cada fila de una consulta externa Es una combinación entre la subconsulta y la fila de la consulta externa. Dividen consultas complejas con dos o más consultas simples relacionadas

Subconsulta correlacionada

Select apellido, oficio,dept_no Select apellido, from emp from emp as as e e where 20 where 20 < (select select dept_no dept_no from from dept dept as as d d where e.dept_no where e.dept_no = d.dept_no and d.dnombre and d.dnombre = 'Ventas' 'Ventas'))

[email protected]

1


Simulación de una clausula JOIN

Vamos a mostrar los oficios que están en más de un departamento. Select distinct e1.oficio from emp as e1 where e1.oficio in(Select e2.oficio from emp as e2 where e1.dept_no <> e2.dept_no) Se debe utilizar antes una combinación que una subconsulta, la combinación sería asi, dando los mismos resultados: Select distinct e1.Oficio from emp as e1 inner join emp as e2 on e1.oficio = e2.oficio where e1.dept_no <> e2.dept_no Estos son los dos oficios que están en más de un departamento.

Subconsulta para simular una clausula HAVING

select e1.apellido,e1.oficio, e1.salario from emp as e1 where e1.salario > (select avg(e2.salario) from emp as e2 where e1.oficio = e2.oficio)

Esta es la consulta utilizando el HAVING, que es la que deberíamos utilizar antes que una subconsulta de simulación HAVING: SELECT e1.apellido,e1.oficio, e1.salario FROM emp AS e1 INNER JOIN emp AS e2 ON e1.oficio = e2.oficio GROUP BY e1.oficio, e1.salario,e1.apellido HAVING e1.salario > AVG (e2.salario)

[email protected]

2


Clausulas EXISTS Y NOT EXISTS

Comprueba si el dato que buscamos existe en la consulta. Mostrar los empleados que no tienen departamento: select apellido,fecha_alt,salario from emp as e where not exists(select * from dept as o where e.dept_no = o.dept_no)

Recomendaciones:   

Utilizar subconsultas para dividir una consulta compleja. Utilizar Alias para tablas en subconsultas correlacionadas y combinaciones. Utilizar EXISTS en vez de IN SUBCONSULTAS

1. Mostrar el número de empleado, el apellido y la fecha de alta del empleado más antiguo de la empresa 2. Mostrar el número de empleado, el apellido y la fecha de alta del empleado más modernos de la empresa. 3. Visualizar el apellido y el oficio de los empleados con el mismo oficio que Jiménez. 4. Queremos saber el apellido, oficio, salario y número de departamento de los empleados con salario mayor que el mejor salario del departamento 30. 5. Mostrar el apellido, la función, sala o departamento de todos los empleados que trabajen en la empresa. 6. Averiguar el salario de todos los empleados de la empresa, de forma que se aprecien las diferencias entre ellos. 7. Mostrar apellidos y oficio de los empleados del departamento 20 cuyo trabajo sea el mismo que el de cualquier empleado de ventas. 8. Mostrar los empleados que tienen mejor salario que la media de los directores, no incluyendo al presidente. 9. Mostrar el apellido, función, salario y código de hospital de los empleados de la plantilla que siendo enfermeros o enfermeras pertenecen al hospital SAN CARLOS. 10. Visualizar los datos de los hospitales que tienen personal (Doctores) de cardiología. 11. Visualizar el salario anual de los empleados de la plantilla del Hospital Provincial y General. 12. Mostrar el apellido de los enfermos que nacieron antes que el Señor Miller.

[email protected]

3


Variables

-

Definidas en una instrucción DECLARE (Con una sola @) Valores asignados con instrucción SET o SELECT (Con una sola @) Ámbito global o local

Sintaxis: DECLARE @VariableLocal tipodatos,... SET @VariableLocal = expresión SELECT @VariableLocal = Nombre from Empleados WHERE Id = 5 BUCLES

Nivel de instrucción:   

Bloques BEGIN......END Bloques IF.............ELSE Construcciones WHILE

Sacar Números pares con IF --IF declare @n int set @n = 5 if (@n % 2)= 0 print 'PAR' else print 'IMPAR' --BUCLES Declare @n int set @n = 0 while @n < 10 begin if (@n % 2) = 0 select @n as Numero set @n = @n + 1 end

[email protected]

4


EJEMPLO: Sacar los numeros pares hasta 10 dentro de un bucle en SQL

Nivel De fila

Las expresiones a nivel de fila evaluan un resultado devuelto por la consulta y dependiendo de los valores que utilicemos lo sustituyen para mejorar la presentación de los datos. CASE expresion WHEN valor1 THEN resultado1 ELSE resultadoN END CASE WHEN verdadero THEN resultado1 ELSE resultado2 END --CASE DECLARE @N INT SET @N = 1 WHILE (@N<100) BEGIN SELECT @N AS 'NUMERO', CASE WHEN (@N % 2) = 1 THEN 'IMPAR' ELSE 'PAR' END AS 'TIPO' SET @N = @N + 1

[email protected]

5

Análisis y Diseño de Bases de Datos con Sql Server 2008 END

EJEMPLO: Esto se puede utilizar también para inicializar variables. Es el mismo caso pero utilizando el CASE para inicializar una variable. DECLARE @N INT,@TIPO CHAR(10),@VALOR CHAR(11) SET @N = 1 WHILE (@N < 100) BEGIN IF (@N < 50) SET @VALOR = 'MENOR DE 50' ELSE SET @VALOR = 'MAYOR DE 50' SET @TIPO = CASE (@N % 2) WHEN 1 THEN 'IMPAR' ELSE 'PAR' END SELECT @N AS 'NUMERO', @TIPO AS 'TIPO',@VALOR AS VALOR SET @N = @N + 1 END

[email protected]

6


CONVERT se usa para convertir el número a una cadena de dos caracteres y lo concatenamos con @tipo. CAST realiza lo mismo que CONVERT DECLARE @N INT,@TIPO CHAR(10) SET @N = 5 IF (@N BETWEEN 4 AND 6) BEGIN WHILE (@N > 0) BEGIN SET @TIPO = CASE (@N % 2) WHEN 1 THEN 'IMPAR' ELSE 'PAR' END SELECT @N AS 'NUMERO', @TIPO AS 'TIPO' PRINT CONVERT (CHAR(2),@N) + @TIPO PRINT CAST (@N AS CHAR(2)) + @TIPO SET @N = @N - 1 END END

Ponemos Cast y Convert para poder concatenar un valor de tipo Int con un valor de tipo Char, sino intentaría sumarlos y daría error.

[email protected]

7


EJERCICIOS CON SENTENCIAS

1. Queremos saber a qué empleados eliminaríamos si quitásemos los departamentos 10 y 30 y cuáles se mantendrían. Mostrar un informe con el apellido, salario, oficio y fechas de alta en la empresa. 2. Debemos hacer recortes de salario en la empresa, para ello debemos saber a qué personas recortaremos el sueldo, cuales se mantendrán y cuales subiremos el puesto. Utilizar todos los empleados de la empresa (Plantilla y Empleados) cuando el salario sea menor de 100000, Subiremos sueldo, cuando esté entre 100000 y 250000 lo mantendremos y cuando sea superior, lo bajaremos. 3. Queremos saber que empleados de la plantilla trabajan en turno de tarde, noche o en otros, para ello mostraremos ‘Tarde’ o ‘Noche’ dependiendo de sus valores.

4. Queremos cambiar de localidad en Barcelona, para ello tenemos que saber qué empleados cambiarían de localidad y cuáles no. Combinar tablas y mostrar el nombre del departamento junto a los datos del empleado. 5. Queremos saber el número de trabajadores que cambiarían de localidad si cambiásemos a Barcelona y que número de trabajadores no cambiarían de localidad. 6. Mostrar el apellido, la dirección, la fecha de nacimiento mostrando la década en la que está cada persona y el sexo mostrando si es masculino o femenino de la tabla enfermo. 7. Mostrar el apellido, el salario, el oficio y el nombre del departamento de todos los empleados aunque no tengan departamento. Si no tienen departamento mostraré que no tienen departamento. Mostraré además si tienen comisión o si no tienen comisión. 8. Mostrar todas las camas que existen para cada hospital y cada sala. Mostraré el nombre del hospital, las salas y su número de camas. Si no hubiese camas para algún hospital las dejaré a 0. También mostraré que son muchas camas cuando sean más de 90, buen número cuando sean mayores de 40 y pocas camas para las demás. 9. Seleccionar qué empleados están dentro de la media y cuales están por debajo de la media, mostrando el apellido, oficio, salario, comisión y el nombre de los departamentos. No dejar ningún campo a NULL.

[email protected]

8

Guía #3 - SubConsultas

Recommend Documents