Ejercicios y Evaluaciones.docx

Ejercicios unidad 1: El entorno gráfico SSMS (I)

Ejercicio 1: Crear una base de datos

Se desea implementar una base de datos para el control de una biblioteca. Crea la base de datos con el nombre Biblioteca y las opciones por defecto.

Puedes consultar aquí las soluciones propuestas.

Ejercicio 2: Crear y definir tablas

Crea las tablas Libros, Préstamos y Usuarios de forma que sigan el siguiente esquema:

Tabla Libros

NOMBRE CAMPO TIPO DE DATOS TAMAÑOCodigo Numérico ByteNombre Texto 60Editorial Texto 25Autor Texto 25Genero Texto 20PaisAutor Texto 20Paginas Numérico EnteroAnyEdicion Fecha/Hora Fecha medianaPrecio Moneda Dias Numérico Entero

Tabla Préstamos

NOMBRE CAMPO TIPO DE DATOS TAMAÑOCodigo Autonumérico Libro Numérico ByteUsuario Numérico ByteFSalida Fecha/Hora Fecha medianaFMaxima Fecha/Hora Fecha medianaFDevol Fecha/Hora Fecha mediana

Tabla Usuarios

NOMBRE CAMPO TIPO DE DATOS TAMAÑOCodigo Autonumérico Nombre Texto 15Apellidos Texto 25DNI Texto 12Domicilio Texto 50Poblacion Texto 30Provincia Texto 20FNacim Fecha/Hora Fecha mediana

Maximo_permitido Numérico Entero


Ayuda ejercicios unidad 1: El entorno gráfico SSMS


Para crear la base de datos con el nombre Biblioteca y las opciones por defecto:

1. Abre el SQL Server Management Studio Express que instalaste.

2. Haz clic con el botón secundario del ratón sobre la carpeta Bases de datos del Explorador de objetos.

3. Selecciona Nueva base de datos...

4. Llámala Biblioteca y deja los nombres y características de los archivos con su definición predeterminada. Pulsa Aceptar.

5. Despliega el contenido de la nueva carpeta Biblioteca pulsando sobre el signo + para comprobar que se ha creado correctamente.


Para crear las tablas:

1. Despliega el contenido de la base de datos Biblioteca, en el Explorador de objetos.

2. Despliega el menú contextual de Tablas y escoge la opción Nueva tabla...

3. Escribe el nombre de cada columna y escoge el tipo de datos en el desplegable. En la zona inferior, en la pestaña Propiedades de columna, busca el campo que define el tamaño del dato y cámbialo si es necesario.

4. Cuando hayas acabado, pulsa el botón Guardar o cierra la tabla y dale el nombre correspondiente.

5. Repite los pasos para cada una de las tres tablas.

Ejercicio 3: Insertar datos en las tablas

Para insertar los datos en las tablas.

1. Despliega el contenido de la carpeta Tablas de Biblioteca, en el Explorador de objetos.

2. Despliega el menú contextual de una de las tablas y escoge la opción Abrir tabla. Rellena los campos.

3. Repite la operación para las otras dos tablas.

Ejercicio 4: Modificar la definición de las tablas

Para modificar la definición de las tablas:Ten en cuenta que las claves primarias serán los campos codigo de cada tabla. Además, el campo Libro de la tabla Prestamos es clave ajena, hace referencia al Codigo de Libros. Y el campoUsuario también es clave ajena, hace referencia al Codigo de Usuarios

1. Despliega el contenido de la carpeta Tablas de Biblioteca, en el Explorador de objetos.

2. Despliega el menú contextual de la tabla que quieras modificar y escoge la opción Diseño (o Modificar).

3. Incluye la clave primaria seleccionando la columna Codigo y pulsando el botón en forma de llave de la barra de herramientas.

4. Si la tabla contiene una clave ajena, indícalo desplegando el menú contextual del campo que quieres relacionar y seleccionando Relaciones...

5. No olvides marcar o desmarcar la casilla Permite valores nulos convenientemente.

6. Repite la operación para las otras dos tablas.

Ejercicios unidad 1: El entorno gráfico SSMS (I)


Se desea implementar una base de datos para el control de una biblioteca. Crea la base de datos con el nombre Biblioteca y las opciones por defecto.



Crea las tablas Libros, Préstamos y Usuarios de forma que sigan el siguiente esquema:

Tabla Libros

NOMBRE CAMPO TIPO DE DATOS TAMAÑOCodigo Numérico ByteNombre Texto 60Editorial Texto 25Autor Texto 25Genero Texto 20PaisAutor Texto 20Paginas Numérico EnteroAnyEdicion Fecha/Hora Fecha medianaPrecio Moneda Dias Numérico Entero

Tabla Préstamos

NOMBRE CAMPO TIPO DE DATOS TAMAÑOCodigo Autonumérico

Libro Numérico ByteUsuario Numérico ByteFSalida Fecha/Hora Fecha medianaFMaxima Fecha/Hora Fecha medianaFDevol Fecha/Hora Fecha mediana

Tabla Usuarios

NOMBRE CAMPO TIPO DE DATOS TAMAÑOCodigo Autonumérico Nombre Texto 15Apellidos Texto 25DNI Texto 12Domicilio Texto 50Poblacion Texto 30Provincia Texto 20FNacim Fecha/Hora Fecha medianaMaximo_permitido Numérico Entero


Prueba evaluativa unidad 1: El entorno gráfico SSMS

Sólo una respuesta es válida por pregunta. Haz clic en la respuesta que consideres correcta. Contesta todas las preguntas y haz clic en el botón Corregir para ver la solución.

Si pulsas Restablecer podrás repetir la evaluación.

1. El Explorador de objetos...

a) Es un panel que muestra el contenido de nuestro servidor: bases de datos, tablas, etc. ordenados jerárquicamente.

b) Permite acceder a elementos de nuestro servidor rápidamente y efectuar acciones sobre ellos mediante el menú contextual.

c) A y B son ciertas.

d) A y B son falsas.

2. ¿En qué carpeta se deben, por lo general, crear las bases de datos?

a) Bases de datos > Bases de datos del sistema.

b) Bases de datos.

c) Objetos del servidor.

d) Administración.

3. Como mínimo, una base de datos en SQL Server debe conener los archivos físicos:

a) mdf y ldf, es decir, el principal y el de registro.

b) mdf, ndf y ldf: Principal, secundario y de registro.

c) mdf y ndf: Principal y secundario.

d) mdf: Principal.

4. Para copiar los archivos físicos de una base de datos...

a) Deberemos acceder a la carpeta donde está guardada, mediante el explorador de windows, y copiarlos.

b) Deberemos desconectarla antes de intentar copiarla, si no el sistema no nos dejará hacerlo porque está en uso.

c) Deberemos utilizar la herramienta de copias de seguridad, porque no se puede acceder a ella desde fuera del gestor SSMS.

d) Todas son falsas.

5. Para incluir una base de datos en nuestro servidor...

a) Es necesario crearlo desde el mismo servidor.

b) Es necesario, por seguridad, crear la base de datos en un servidor diferente al que lo ejecutará.

c) Podemos crear una nueva o adjuntar una ya existente.

d) Deberemos hacerlo mediante instrucciones SQL, ya que el entorno gráfico no lo permite.

6. Al definir las columnas de una tabla, ¿qué información deberemos especificar?

a) El rango de valores que soporta.

b) El nombre, tipo y si admite nulos, para cada columna.

c) La longitud de la cadena.

d) Las variables.

7. Si definimos una columna de identidad (IDENTITY)...

a) SQL Server automáticamente la define como clave principal.

b) Ésta admitirá nulos.

c) Ésta deberá ser de tipo nchar.

d) El sistema generará su valor, que será un número incrementado.

8. Para modificar los datos de una tabla:

a) Desde el Explorador de objetos, desplegamos su menú contextual y elegimos la opción Modificar.

b) Desde el Explorador de objetos, desplegamos su menú contextual y elegimos la opción Abrir tabla.



9. Para relacionar dos tablas, por ejemplo empleados y oficinas.

a) Deberemos llamar del mismo modo al campo que las relacionará y el gestor se encarga automáticamente de crear la relación.

b) Deberemos seleccionar el campo de la tabla padre empleados y pulsar el botón definir hijos para añadir el campo de oficinas.

c) Deberemos, necesariamente, escribir instrucciones SQL que se encarguen de establecer las relaciones.

d) Deberemos desplegar el menú contextual del campo que las relaciona y elegir Relaciones.

10. Si pulsamos el botón Nueva consulta...

a) Se abrirá una pestaña nueva en forma de editor, donde podremos escribir consultas SQL.

b) Podremos utilizar la zona de edición para escribir varias consultas, una detrás de otra.

c) Dispondremos de un editor que coloreará las palabras en función de su categoría.

d) Todas son ciertas.

Prueba evaluativa unidad 2: Introducción al SQL. Transact-SQLSólo una respuesta es válida por pregunta. Haz clic en la respuesta que consideres correcta.

Contesta todas las preguntas y haz clic en el botón Corregir para ver la solución. Si pulsas Restablecer podrás repetir la evaluación.

1. El lenguaje SQL es:

a) Propio de SQLServer.

b) Propio de SQLServer y otros sistemas gestores de Microsoft, como Access.

c) Universal, se puede utilizar en cualquier sistema gestor de base de datos relacional.


2. El DDL engloba las instrucciones SQL que permiten:

a) Definir la estructura de una base de datos, como: crear una base de datos o crear y eliminar tablas.

b) Realizar cambios en los datos contenidos en la base de datos: incluir registros, borrarlos, editarlos...

c) Ejercer control sobre los procesos que ejecutan transacciones y los permisos de la base de datos.


3. El DCL engloba las instrucciones SQL que permiten:





4. El DML engloba las instrucciones SQL que permiten:





5. El lenguaje Transact-SQL es una variante del SQL estándar, que incluye caracerísticas como:

a) Herramientas para la creación de interfaces para el usuario de la base de datos.

b) La posibilidad de crear aplicaciones ejecutables.

c) Estructuras de control de flujo.


6. Deberemos cualificar un nombre cuando:

a) Referenciemos un campo, tabla o vista que forme parte de una base de datos externa, es decir, que no forme parte de la base de datos que está activa.

b) Si realizamos una consulta con varias tablas y necesitamos referenciar un campo que se encuentra en más de una de ellas.



7. Para llamar a un campo Fecha contrato deberemos...

a) Escribir (Fecha contrato). Entre paréntesis.

b) Escribir [Fecha contrato]. Entre corchetes.

c) No se puede escribir espacios en los nombres de los campos, deberemos escribir Fecha_contrato.

8. Señala cuál es un comentario válido para SQL.

a) /* Esto es un comentario */

b) - Esto es un comentario.

c) // Esto es un comentario.

d) 

9. Transact-SQL permite definir:

a) Variables.

b) Constantes.

c) Funciones propias.


Ejercicios unidad 3: Consultas simples

Te aconsejamos que, para realizar consultas SQL más fácilmente, te hagas estas preguntas:

¿Dónde están los datos necesarios? La respuesta dará la FROM.

¿Qué columnas quiero que se visualicen en el listado? La respuesta dará la lista de selección (SELECT).

¿Quiero que se ordenen por algún valor? Si la respuesta es sí, necesitarás utilizar ORDER BY.

¿Tienen que aparecer todas las filas del resultado? Si la respuesta es NO, deberás:

o Utilizar TOP para quedarte con las N primeras.

o Utilizar DISTINCT si no quieres que se muestren las repetidas.

o O bien utilizar la cláusula WHERE para expresar la condición que deben cumplir para ser mostradas.

Para realizar los ejercicios, deberás utilizar la base de datos GestionSimples, en la carpeta Ejercicios del curso tienes el PDF Tablas_Gestion para consultar en qué tablas está cada campo.

Ejercicio 1: Listas de selección

1. Listar todos los empleados.

Resultado:

Numemp

Nombre Edad

Oficina

Titulo Contrato Jefe Cuota ventas

101 Antonio Viguer

45 12 representante

1986-10-20 00:00:00.000

104 30000,00

30500,00

102 Alvaro Jaumes

48 21 representante

1986-12-10 00:00:00.000

108 35000,00

47400,00

103 Juan Rovira

29 12 representante

1987-03-01 00:00:00.000

104 27500,00

28600,00

104 José González

33 12 dir ventas 1987-05-19 00:00:00.000

106 20000,00

14300,00

105 Vicente Pantalla

37 13 representante

1988-02-12 00:00:00.000

104 35000,00

36800,00

106 Luis Antonio

52 11 director general

1988-06-14 00:00:00.000

NULL

27500,00

29900,00

107 Jorge Gutiérrez

49 22 representante

1988-11-14 00:00:00.000

108 30000,00

18600,00

108 Ana Bustamante

62 21 dir ventas 1989-10-12 00:00:00.000

106 35000,00

36100,00

109 María Sunta

31 NULL

representante

1999-10-12

106 3000,00

39200,00

00:00:00.000

110 Juan Victor

41 NULL

representante

1990-01-13 00:00:00.000

104 NULL 7600,00

111 Juan Gris 50 NULL

representante

2005-05-01 00:00:00.000

NULL

10000,00

60000,00

112 Julián Martorell

50 NULL

representante

2006-05-01 00:00:00.000

NULL

10000,00

91000,00

113 Juan Gris 18 NULL

representante

2007-01-01 00:00:00.000

NULL

10000,00

0,00

2. Listar todos los empleados, al igual que en el ejercicio anterior, pero cambiando el nombre de la columna contrato por Fecha de contrato.


Ejercicio 2: Utilizando ORDER BY

1. Listar de cada región las oficinas por orden de mejores ventas.

Resultado:

Región Ciudad Ventas

NULL Elx 0,00

NULL Valencia NULL

centro Aranjuez 15000,00

Centro Móstoles 0,00

centro Madrid NULL

este Alicante 73500,00

http://www.aulaclic.es/sqlserver/spr_3_14_1.htm

este Valencia 69300,00

este Castellon 36800,00

este Valencia 2100,00

este Valencia 0,00

norte Pamplona 200000,00

norte pamplona NULL

oeste Badajoz 84400,00

oeste A Coruña 18600,00

2. Saber los productos que tienen un precio superior o igual al precio de la mitad de los productos.

Resultado:

Idfab Idproducto Descripción Precio existencias

rei 2a44l bomba l 45,00 12

rei 2a44r bomba r 45,00 12

imm 779c reostato 3 18,75 0

imm 775c reostato 2 14,25 5

imm 773c reostato 9,75 28

bic 41003 manivela 6,52 3

imm 887x manivela 4,75 32

qsa xk47 red 3,55 38

rei 2a44g pas 3,50 14

fea 114 cubo 2,43 15

bic 41089 rodamiento 2,25 78

bic 41672 plato 1,80 0

fea 112 cubo 1,48 115

qsa xk48a red 1,48 37


Ejercicio 2: Utilizando WHERE

1. Listar los empleados que tienen ventas pero que no han alcanzado su cuota.

Resultado:

Numemp Nombre Ventas cuota

104 José González 14300,00 20000,00

107 Jorge Gutiérrez 18600,00 30000,00

2. Hallar los empleados que no están a cargo del empleado 106.

Resultado:

Numemp nombre

101 Antonio Viguer

102 Alvaro Jaumes

103 Juan Rovira


106 Luis Antonio


110 Juan Victor

111 Juan Gris


113 Juan Gris

3. Listar de cada jefe su código y el código y nombre de sus subordinados ordenados por nombres.

Resultado:

Jefe Numemp nombre

104 101 Antonio Viguer

104 103 Juan Rovira


104 110 Juan Victor

104 105 Vicente Pantalla

106 108 Ana Bustamante

106 104 José González

106 109 María Sunta

108 102 Alvaro Jaumes

108 107 Jorge Gutiérrez

Ayuda ejercicios unidad 3: Consultas simples

Ejercicio 1: Listas de selección

Para listar todos los empleados:

SELECT * FROM empleados;

Para listar todos los empleados, cambiando el nombre de la columna contrato por Fecha de contrato: Utilizaremos un alias, y lo incluiremos entre corchetes para que no haya errores con el caracter de espacio en blanco:

SELECT numemp, nombre, edad, oficina, titulo, contrato as [Fecha de contrato], jefe, cuota, ventas

FROM empleados;

Ejercicio 2: Utilizando ORDER BY

Para listar de cada región las oficinas por orden de mejores ventas:

SELECT region, ciudad, ventas

FROM oficinas

ORDER BY region, ventas DESC;

Para saber los productos que tienen un precio superior o igual al precio de la mitad de los productos.

SELECT TOP 50 PERCENT WITH TIES *

FROM productos

ORDER BY precio DESC;

Ejercicio 2: Utilizando WHERE

Para listar los empleados que tienen ventas pero que no han alcanzado su cuota.

SELECT numemp, nombre, ventas, cuota

FROM empleados

WHERE ventas < cuota AND ventas >0;

Para hallar los empleados que no están a cargo del empleado 106.

SELECT numemp, nombre

FROM empleados

WHERE jefe <> 106 OR jefe IS NULL;

Si no añadimos la segunda parte de la pregunta (OR jefe IS NULL) los empleados que no tienen jefe no salen.

Para listar de cada jefe su código y el código y nombre de sus subordinados ordenados por nombres.

SELECT jefe, numemp, nombre

FROM empleados

WHERE jefe IS NOT NULL

ORDER BY jefe, nombre;

Prueba evaluativa unidad 3: Consultas simples

Sólo una respuesta es válida por pregunta. Haz clic en la respuesta que consideres correcta. Contesta todas las preguntas y haz clic en el botón Corregir para ver la solución. Si pulsas Restablecer podrás repetir la evaluación.

1. La cláusula FROM indica el origen de datos para la consulta y puede constar de:

a) Una tabla.

b) Una vista.



2. Si queremos utilizar la tabla de una base de datos externa (Base2) en nuestra consulta...

a) Deberemos cualificarla, de la siguiente forma: FROM Base2.tabla.

b) Podremos indicar el nombre de la tabla directamente : FROM tabla.

c) No podemos utilizar más de una base de datos en la misma consulta.

d) Deberemos desconectar de la base de datos actual y conectar con la Base2.

3. El alias se puede aplicar en:

a) El origen, por ejemplo: tablaempleados as Empleados

b) La lista de selección, por ejemplo: nomemp as [Nombre Empleado]



4. Señala cuál de los siguientes ejemplos es correcto:

a) SELECT nom as Nombre FROM empleados WHERE Nombre='Juan'.

b) SELECT nom Nombre FROM empleados WHERE Nombre='Juan'

c) SELECT nom Nombre FROM empleados ORDER BY Nombre

d) SELECT nom Nombre FROM empleados ORDER BY Apellido='García'

5. Señala cuál de los siguientes ejemplos es incorrecto:

a) SELECT precio_unidad = (importe/cantidad)...

b) SELECT (importe/cantidad) AS precio_unidad...

c) SELECT precio_unidad = ROUND(importe/cantidad)...

d) SELECT precio_unidad = ROUND((importe/cantidad), 2)...

6. ¿Dónde utilizamos la palabra clave *?

a) En la lista de selección, para indicar que queremos mostrar todas las columnas del origen indicado.

b) En el origen de datos, para indicar que queremos utilizar como origen todas las tablas de la base de datos.



7. Si a una consulta aplicamos una ordenación mediante el ORDER BY...

a) Deberemos indicar si queremos ordenar de forma ascendente (ASC) o descendente (DESC) o dará error.

b) Deberemos indicar si queremos ordenar de forma ascendente o descendente. Si no indicamos nada, se ordenará de forma ascendente por defecto.

c) Deberemos indicar ASC o DESC, pero sólo en caso de que se trate de un campo alfabético. Para otros tipos de datos no funciona (como fechas o campos numéricos).


8. La cláusula DISTINCT...

a) Agiliza las consultas, porque debe mostrar menos resultados.

b) Se utiliza para no mostrar las filas que repitan el mismo valor en un determinado campo (columna).

c) Se utiliza para no mostrar las filas que se repitan en todos sus campos, es decir, que sea idénticas. Ralentiza la consulta porque debe evaluar al final si se repiten y eliminarlas del listado generado.

d) Se ejecuta de forma predeterminada. Para que no sea así debemos indicar ALL, por ejemplo: SELECT ALL oficina FROM empleados.

9. La cláusula TOP...

a) Tiene más sentido en consultas con ORDER BY. Al ejecutarse, primero se ordenan según un criterio y luego se extraen las N primeras.

b) Puede ir acompañada de WITH TIES, si queremos mostrar todos los valores iguales al último del ranking.

c) Puede ser porcentual, es decir, mostrar el 8% del total de registros, por ejemplo, para que el listado sea proporcional a su volumen.


10. La cláusula WHERE...

a) Permite indicar una condición para filtrar los resultados. Por ejemplo: WHERE nombre='Juan'.

b) Admite funciones. Por ejemplo: WHERE YEAR(contrato)=1985.

c) Admite intervalos de valores. Por ejemplo: WHERE YEAR(contrato) BETWEEN 1985 AND 1995.


Unidad 3. Ejercicio paso a paso: Consultas con múltiples condiciones

Nota: Para realizar los ejercicios, utiliza la base de datos GestionSimples.

Objetivo

Aprender a utilizar más de una condición en el predicado de una consulta, mediante las operaciones lógicas.

Ejercicio paso a paso

Listar las oficinas que han alcanzado su objetivo.

Es decir, que las ventas sean mayores o iguales que su objetivo, o bien que no tengo un objetivo mínimo y haya realizado alguna venta.

SELECT *

FROM oficinas

WHERE ventas >= objetivo OR (ventas >0 AND objetivo IS NULL)

Resultado:

Oficina Ciudad Región Dir Objetivo ventas

11 Valencia este 106 57500,00 69300,00

13 Castellon este 105 35000,00 36800,00

21 Badajoz oeste 108 72500,00 84400,00

26 Pamplona norte NULL NULL 200000,00

Listar las oficinas cuyas ventas no alcanzan el 50% de su objetivo.

SELECT *

FROM oficinas

WHERE ventas < (.5 * objetivo) OR (ventas IS NULL AND objetivo IS NOT NULL);

Resultado:

Oficina Ciudad Región Dir Objetivo ventas

28 Valencia este NULL 90000,00 0,00

29 Valencia este NULL 10000,00 2100,00

30 pamplona norte NULL 20000,00 NULL

Unidad 4. Ejercicio paso a paso: La intersección INTERSECT

Nota: Para realizar los ejercicios, utiliza la base de datos Gestion (el archivo físico se llamaba GestionA) y GestionSimples.

Objetivo

Comparar dos tablas y crear un listado resultante de la comparación. Los registros mostrados serán los que tengan en común.


Obtener los empleados de GestionSimples que aparecen en Gestion con misma oficina.

SELECT numemp, oficina

FROM gestionsimples.dbo.empleados

INTERSECT

SELECT numemp, oficina

FROM gestion.dbo.empleados;

Resultado:

Numemp oficina

101 12

102 21

103 12

104 12

105 13

106 11

107 22

108 21

109 NULL

110 NULL

111 NULL

112 NULL

113 NULL

Unidad 4. Ejercicio paso a paso: El producto cartesiano CROSS JOIN


Objetivo

Combinar datos de dos tablas en una única tabla.


Listar los empleados que tienen una cuota superior al objetivo de al menos una oficina. La oficina puede ser cualquiera no tiene por que ser la del empleado.

SELECT numemp, nombre, cuota, empleados.oficina AS [Su oficina], oficinas.oficina, objetivo

FROM empleados, oficinas

WHERE cuota > objetivo

Recuerda que se puede utilizar la coma para separar las tablas, ya que esto equivale a escribir CROSS JOIN.

Resultado:

Numemp Nombre Cuota Su oficina Oficina Objetivo

101 Antonio Viguer 30000,00 12 24 25000,00

101 Antonio Viguer 30000,00 12 29 10000,00

101 Antonio Viguer 30000,00 12 30 20000,00

102 Alvaro Jaumes 35000,00 21 22 30000,00

102 Alvaro Jaumes 35000,00 21 24 25000,00

102 Alvaro Jaumes 35000,00 21 29 10000,00

102 Alvaro Jaumes 35000,00 21 30 20000,00

103 Juan Rovira 27500,00 12 24 25000,00

103 Juan Rovira 27500,00 12 29 10000,00

103 Juan Rovira 27500,00 12 30 20000,00

104 José González 20000,00 12 29 10000,00

105 Vicente Pantalla 35000,00 13 22 30000,00




106 Luis Antonio 27500,00 11 24 25000,00

106 Luis Antonio 27500,00 11 29 10000,00

106 Luis Antonio 27500,00 11 30 20000,00

107 Jorge Gutiérrez 30000,00 22 24 25000,00

107 Jorge Gutiérrez 30000,00 22 29 10000,00

107 Jorge Gutiérrez 30000,00 22 30 20000,00

108 Ana Bustamante 35000,00 21 22 30000,00

108 Ana Bustamante 35000,00 21 24 25000,00

108 Ana Bustamante 35000,00 21 29 10000,00

108 Ana Bustamante 35000,00 21 30 20000,00

Unidad 4. Ejercicio paso a paso: La composición interna INNER JOIN


Objetivo

Combinar datos de dos tablas que tienen algún dato en común, con la finalidad de ampliar la información en una única tabla.


Listar los códigos y nombres de los empleados de las oficinas del Este con su oficina y ciudad.

Como la ciudad donde se encuentra la oficina no viene incluida en el listado de empleados, deberemos enlazar ambas tablas (oficinas y empleados) , utilizando como enlace el código de la oficina, que sí que está en las dos.

SELECT numemp, nombre, empleados.oficina, ciudad

FROM oficinas INNER JOIN empleados ON oficinas.oficina = empleados.oficina

WHERE region ='Este';

Resultado:

Numemp Nombre Oficina ciudad

101 Antonio Viguer 12 Alicante

103 Juan Rovira 12 Alicante

104 José González 12 Alicante

105 Vicente Pantalla 13 Castellon

106 Luis Antonio 11 Valencia

Listar todos los pedidos mostrando su número, importe, nombre de cliente, y el límite de crédito del cliente correspondiente.

Como la tabla de pedidos no contiene el nombre del cliente, enlazaremos ambas tablas por el dato en común de referencia, su código.

SELECT numpedido, importe, clientes.nombre AS Cliente, limitecredito

FROM pedidos INNER JOIN clientes ON clie=numclie;

Resultado:

Numpedido Importe Cliente limitecredito

110036 22,50 Julian López 3500

110037 31,50 Carlos Tena 3500

112963 3,276 Jaime Llorens 5000

112968 39,78 Alvaro Rodríguez 6500

112975 21,00 Cristóbal García 500

112979 150,00 Cristina Bulini 2000

112983 7,02 Jaime Llorens 5000

112987 275,00 Jaime Llorens 5000

112989 14,58 Luis García Antón 6500

112992 7,60 Junípero Alvarez 600

112993 18,96 Juan Suárez 650

112997 6,52 Juan Bolto 4000

113003 56,25 Julia Antequera 550

113007 29,25 María Silva 5000

113012 37,45 Cristóbal García 500


113024 71,00 Cristina Bulini 2000

113027 450,00 Jaime Llorens 5000

113034 6,32 Julian López 3500

113042 225,00 Luisa Maron 2000

113045 450,00 María Silva 5000

113048 37,50 Juan Malo 5000




113057 NULL Cristóbal García 500


113062 24,30 Juan Bolto 4000

113065 21,30 Juan Suárez 650

113069 313,50 Alberto Juanes 250

Unidad 4. Ejercicio paso a paso: La composición externa LEFT JOIN y RIGHT JOIN


Objetivo

Combinar datos de dos tablas que tienen algún dato en común, con la finalidad de ampliar la información en una única tabla. Tendremos en cuenta los nulos, de forma que el LEFT o el RIGHT indicarán qué tabla es la que deberá aparecer, en caso de no tener correspondencia en la otra.


Listar todos los empleados y la ciudad y región donde trabaja.

SELECT numemp, nombre, edad, titulo, CONVERT(CHAR(8),contrato,3)AS contrato, jefe, empleados.oficina,ciudad, region

FROM oficinas RIGHT JOIN empleados ON oficinas.oficina = empleados.oficina;

Resultado:

Numemp Nombre Edad Titulo Contrato Jefe Oficina Ciudad Region

101 Antonio Viguer

45 representante 20/10/86 104 12 Alicante este

102 Alvaro Jaumes

48 representante 10/12/86 108 21 Badajoz oeste

103 Juan Rovira 29 representante 01/03/87 104 12 Alicante este

104 José 33 dir ventas 19/05/87 106 12 Alicante este

González


37 representante 12/02/88 104 13 Castellon este

106 Luis Antonio

52 director general

14/06/88 NULL 11 Valencia este


49 representante 14/11/88 108 22 A Coruña oeste

108 Ana Bustamante

62 dir ventas 12/10/89 106 21 Badajoz oeste

109 María Sunta 31 representante 12/10/99 106 NULL NULL NULL

110 Juan Victor 41 representante 13/01/90 104 NULL NULL NULL

111 Juan Gris 50 representante 01/05/05 110 21 Badajoz oeste


50 representante 01/05/06 NULL NULL NULL NULL

113 Juan Gris 18 representante 01/01/07 NULL NULL NULL NULL

Listar todas las oficinas y los nombres y títulos de sus directores.

SELECT oficinas.*, nombre AS director, titulo

FROM oficinas LEFT JOIN empleados ON dir = numemp;

Resultado:

Oficina Ciudad Region Dir Objetivo Ventas Director Titulo

11 Valencia este 106 57500,00 69300,00 Luis Antonio director general

12 Alicante este 104 80000,00 73500,00 José González

dir ventas

13 Castellon este 105 35000,00 36800,00 Vicente Pantalla

representante

21 Badajoz oeste 108 72500,00 84400,00 Ana Bustamante

dir ventas

22 A Coruña oeste 108 30000,00 18600,00 Ana Bustamante

dir ventas

23 Madrid centro 108 NULL NULL Ana Bustamante

dir ventas

24 Aranjuez centro 108 25000,00 15000,00 Ana Bustamante

dir ventas

25 Valencia NULL NULL NULL NULL NULL NULL

26 Pamplona norte NULL NULL 200000,00 NULL NULL

27 Móstoles Centro NULL NULL 0,00 NULL NULL

28 Valencia este NULL 90000,00 0,00 NULL NULL

29 Valencia este NULL 10000,00 2100,00 NULL NULL

30 pamplona norte NULL 20000,00 NULL NULL NULL

31 Elx NULL NULL NULL 0,00 NULL NULL

Listar las oficinas con objetivo superior a 60.000 euros indicando para cada una el nombre de su director.

SELECT oficinas.*, nombre AS director

FROM oficinas LEFT JOIN empleados ON dir = numemp

WHERE objetivo > 60000;

Resultado:

Oficina Ciudad Region Dir Objetivo Ventas Director

12 Alicante este 104 80000,00 73500,00 José González

21 Badajoz oeste 108 72500,00 84400,00 Ana Bustamante

28 Valencia este NULL 90000,00 0,00 NULL

Ejercicios unidad 4: Consultas multitabla

Para realizar los ejercicios, deberás utilizar la base de datos GestionSimples, en la carpeta Ejercicios del curso tienes el PDF Tablas_Gestion para consultar en qué tablas está cada campo.

Ejercicio 1: La composición de tablas

1. Listar todos los pedidos, mostrando el precio y la descripción del producto.

Resultado:

Codigo

Numpedido

Fechapedido

Clie Rep Fab Producto

Cant

Importe

Precio

descripcion

1 110036 1989-10-12 00:00:00.000

2107

110

aci 4100z 9 22,50

NULL

mont

2 110037 1989-10-12 00:00:00.000

2117

106

rei 2a44l 7 31,50

45,00

bomba l

3 112963 2008-05-10 00:00:00.000

2103

105

aci 41004

28 3,276

NULL

art t4

4 112968 1990-01-11 00:00:00.000

2102

101

aci 41004

34 39,78

NULL

art t4

5 112975 2008-02-11 00:00:00.000

2111

103

rei 2a44g 6 21,00

3,50 pas

6 112979 1989-10-12 00:00:00.000

2114

108

aci 4100z 6 150,00

NULL

mont

7 112983 2008-05-10 00:00:00.000

2103

105

aci 41004

6 7,02 NULL

art t4

8 112987 2008-01-01 00:00:00.000

2103

105

aci 4100y

11 275,00

NULL

extractor

9 112989 2008-12-10 00:00:00.000

2101

106

fea 114 6 14,58

2,43 cubo

10 112992 1990-04-15 00:00:00.000

2118

108

aci 41002

10 7,60 NULL

bisagra

11 112993 2008-03-10 00:00:00.000

2106

102

rei 2a45c 24 18,96

0,79 junta

12 112997 2008-04- 212 10 bic 4100 1 6,52 6,52 manivel

04 00:00:00.000

4 7 3 a

13 113003 2008-02-05 00:00:00.000

2108

109

imm

779c 3 56,25

18,75

reostato 3

14 113007 2008-01-01 00:00:00.000

2112

108

imm

773c 3 29,25

9,75 reostato

15 113012 2008-05-05 00:00:00.000

2111

105

aci 41003

35 37,45

NULL

art t3

16 113013 2008-12-28 00:00:00.000

2118

108

bic 41003

1 6,52 6,52 manivela

17 113024 2008-07-04 00:00:00.000

2114

108

qsa xk47 20 71,00

3,55 red

18 113027 2008-02-05 00:00:00.000

2103

105

aci 41002

54 450,00

NULL

bisagra

19 113034 2008-11-05 00:00:00.000

2107

110

rei 2a45c 8 6,32 0,79 junta

20 113042 2008-01-01 00:00:00.000

2113

101

rei 2a44r 5 225,00

45,00

bomba r

21 113045 2008-07-02 00:00:00.000

2112

110

rei 2a44r 10 450,00

45,00

bomba r

22 113048 2008-02-02 00:00:00.000

2120

102

imm

779c 2 37,50

18,75

reostato 3

23 113049 2008-04-04

2118

108

qsa xk47 2 7,76 3,55 red

00:00:00.000

24 113051 2008-07-06 00:00:00.000

2118

108

qsa xk47 4 14,20

3,55 red

25 113055 2009-04-01 00:00:00.000

2108

101

aci 4100x

6 1,50 NULL

junta

26 113057 2008-11-01 00:00:00.000

2111

103

aci 4100x

24 NULL

NULL

junta

27 113058 1989-07-04 00:00:00.000

2108

109

fea 112 10 14,80

1,48 cubo

28 113062 2008-07-04 00:00:00.000

2124

107

bic 41003

10 24,30

6,52 manivela

29 113065 2008-06-03 00:00:00.000

2106

102

qsa xk47 6 21,30

3,55 red

30 113069 2008-08-01 00:00:00.000

2109

107

imm

773c 22 313,50

9,75 reostato


2. Listar los pedidos superiores a 250 euros, incluyendo el nombre del vendedor que tomó el pedido y el nombre del cliente que lo solicitó.

Resultado:

Numpedido

Fechapedido


Cant

Precio Cliente vendedor

112987 01/01/08 2103

105

aci 4100y 11 275,00

Jaime Llorens

Vicente Pantalla

113027 05/02/08 2103

105

aci 41002 54 450,00

Jaime Llore

Vicente Pantalla

ns113045 02/07/08 211

2110

rei 2a44r 10 450,00

María Silva

Juan Victor

113069 01/08/08 2109

107

imm

773c 22 313,50

Alberto Juanes

Jorge Gutiérrez

3. Listar los pedidos superiores a 250 euros, mostrando el nombre del cliente que solicitó el pedido y el nombre del vendedor asignado a ese cliente.

Resultado:

Codigo

Numpedido

Fechapedido


Cant

Importe

Cliente

Vendedor

asignado8 112987 2008-01-

01 00:00:00.000

2103

105

aci 4100y

11 275,00

Jaime Llorens

Juan Victor

18 113027 2008-02-05 00:00:00.000

2103

105

aci 41002

54 450,00

Jaime Llorens

Juan Victor

21 113045 2008-07-02 00:00:00.000

2112

110

rei 2a44r 10 450,00

María Silva

Ana Bustamante

30 113069 2008-08-01 00:00:00.000

2109

107

imm

773c 22 313,50

Alberto Juanes

Juan Rovira


4. Listar los pedidos superiores a 250 euros, mostrando además el nombre del cliente que solicitó º el pedido y el nombre del vendedor asignado a ese cliente y la ciudad de la oficina donde el vendedor trabaja.

Resultado:

Numpedido Clie Rep Cliente Repclie Vendedor asignado

ciudad

112987 2103 105 Jaime Llorens

105 Juan Victor NULL

113027 2103 105 Jaime 105 Juan Victor NULL

Llorens113045 2112 110 María Silva 110 Ana Bustamante Badajoz113069 2109 107 Alberto

Juanes107 Juan Rovira Alicante

5. Hallar los pedidos recibidos los días en que un nuevo empleado fue contratado.

Resultado:

Numpedido Fechapedido Rep Numemp Nombre Contrato110036 1989-10-12

00:00:00.000110 108 Ana

Bustamante1989-10-12 00:00:00.000

110037 1989-10-12 00:00:00.000


1989-10-12 00:00:00.000

112979 1989-10-12 00:00:00.000


1989-10-12 00:00:00.000

6. Hallar los empleados que realizaron su primer pedido el mismo día que fueron contratados.

Resultado:

Numemp Nombre Contrato Numpedido Rep fechapedido108 Ana

Bustamante1989-10-12 00:00:00.000

112979 108 1989-10-12 00:00:00.000

7. Mostrar de cada empleado su código, nombre, ventas, oficina y ciudad en la que está ubicada su oficina.

Resultado:

Numemp Nombre ventas Oficina ciudad101 Antonio Viguer 30500,00 12 Alicante102 Alvaro Jaumes 47400,00 21 Badajoz103 Juan Rovira 28600,00 12 Alicante104 José González 14300,00 12 Alicante105 Vicente Pantalla 36800,00 13 Castellon106 Luis Antonio 29900,00 11 Valencia107 Jorge Gutiérrez 18600,00 22 A Coruña108 Ana Bustamante 36100,00 21 Badajoz109 María Sunta 39200,00 NULL NULL110 Juan Victor 7600,00 NULL NULL111 Juan Gris 60000,00 NULL NULL112 Julián Martorell 91000,00 NULL NULL113 Juan Gris 0,00 NULL NULL


8. Listar los empleados con una cuota superior a la de su jefe, los campos a obtener son el número, nombre y cuota del empleado y número, nombre y cuota de su jefe.

Resultado:

Numemp Nombre Cuota Jefe Nombre jefe Cuota jefe101 Antonio Viguer 30000,00 104 José González 20000,00103 Juan Rovira 27500,00 104 José González 20000,00105 Vicente Pantalla 35000,00 104 José González 20000,00108 Ana Bustamante 35000,00 106 Luis Antonio 27500,00

9. Desde el entorno gráfico cambia el empleado 111, asígnale el jefe 110 y la oficina 21. Después cambia la sentencia anterior para que salgan también los empleados cuyo jefe no tenga cuota.

Resultado:

Numemp Nombre Cuota Jefe Nombre jefe Cuota jefe101 Antonio Viguer 30000,00 104 José González 20000,00103 Juan Rovira 27500,00 104 José González 20000,00105 Vicente Pantalla 35000,00 104 José González 20000,00108 Ana Bustamante 35000,00 106 Luis Antonio 27500,00111 Juan Gris 10000,00 110 Juan Victor NULL

10. Listar los empleados que no están asignados a la misma oficina que su jefe, queremos número, nombre y número de oficina tanto del empleado como de su jefe.

Resultado:

Numemp Nombre Oficina Jefe Nombre jefe Oficina jefe104 José González 12 106 Luis Antonio 11105 Vicente Pantalla 13 104 José González 12107 Jorge Gutiérrez 22 108 Ana Bustamante 21108 Ana Bustamante 21 106 Luis Antonio 11

11. En el punto anterior no salen los que no tienen oficina, cambiar la sentencia para que aparezcan.

Resultado:

Numemp Nombre Oficina Jefe Nombre jefe Oficina jefe104 José González 12 106 Luis Antonio 11105 Vicente Pantalla 13 104 José González 12107 Jorge Gutiérrez 22 108 Ana Bustamante 21

108 Ana Bustamante 21 106 Luis Antonio 11109 María Sunta NULL 106 Luis Antonio 11110 Juan Victor NULL 104 José González 12111 Juan Gris 21 110 Juan Victor NULL

12. Lo mismo que la anterior pero queremos que aparezca también la ciudad de las oficinas (tanto del empleado como de su jefe).

Resultado:

Numemp

Nombre Oficina Ciudad Jefe Nombre jefe

Oficina jefe

ciudad

104 José González

12 Alicante 106 Luis Antonio

11 Valencia


13 Castellon

104 José González

12 Alicante


22 A Coruña

108 Ana Bustamante

21 Badajoz

108 Ana Bustamante

21 Badajoz 106 Luis Antonio

11 Valencia

109 María Sunta

NULL

NULL 106 Luis Antonio

11 Valencia

110 Juan Victor

NULL

NULL 104 José González

12 Alicante

111 Juan Gris 21 Badajoz 110 Juan Victor

NULL

NULL


Ejercicio 2: Comparar tablas

1. Obtener los empleados de GestionSimples que aparecen en Gestion con otra oficina.

El resultado será que no devuelve filas.


Ayuda ejercicios unidad 4: Consultas multitabla

Ejercicio 1: La composición de tablas

1. Listar todos los pedidos, mostrando el precio y la descripción del producto.

2. SELECT pedidos.*, precio, descripcion

FROM pedidos INNER JOIN productos ON fab = idfab AND producto = idproducto;

3. Listar los pedidos superiores a 250 euros, incluyendo el nombre del vendedor que tomó el pedido y el nombre del cliente que lo solicitó.

4. SELECT numpedido, CONVERT(CHAR(8),fechapedido,3) AS fechapedido, clie, rep, fab, producto, cant, importe, clientes.nombre AS cliente, empleados.nombre AS vendedor

5. FROM (pedidos INNER JOIN empleados ON rep = numemp)

6. INNER JOIN clientes ON clie = numclie

WHERE importe > 250;

7. Listar los pedidos superiores a 250 euros, mostrando el nombre del cliente que solicitó el pedido y el nombre del vendedor asignado a ese cliente.

8. SELECT pedidos.*, clientes.nombre AS cliente, empleados.nombre AS [vendedor asignado]

9. FROM (pedidos INNER JOIN clientes ON clie = numclie)

10. INNER JOIN empleados ON repclie = numemp


11. Listar los pedidos superiores a 250 euros, mostrando además el nombre del cliente que solicitó º el pedido y el nombre del vendedor asignado a ese cliente y la ciudad de la oficina donde el vendedor trabaja.

12. SELECT numpedido, clie, rep, clientes.nombre AS cliente, repclie, empleados.nombre AS vendedor, ciudad

13. FROM ((pedidos INNER JOIN clientes ON clie = numclie)

14. INNER JOIN empleados ON repclie = numemp)

15. LEFT JOIN oficinas ON empleados.oficina=oficinas.oficina


16. Hallar los pedidos recibidos los días en que un nuevo empleado fue contratado.

17. SELECT numpedido, fechapedido, rep, numemp, nombre, contrato

18. FROM pedidos, empleados

WHERE fechapedido=contrato;

19. Hallar los empleados que realizaron su primer pedido el mismo día que fueron contratados.

20. SELECT numemp, nombre, contrato, numpedido, rep, fechapedido

21. FROM pedidos INNER JOIN empleados ON rep = numemp

WHERE fechapedido = contrato;

22. Mostrar de cada empleado su código, nombre, ventas, oficina y ciudad en la que está ubicada su oficina.

23. SELECT numemp, nombre,empleados.ventas,empleados.oficina,ciudad

FROM empleados LEFT JOIN oficinas ON empleados.oficina=oficinas.oficina;

24. Listar los empleados con una cuota superior a la de su jefe, los campos a obtener son el número, nombre y cuota del empleado y número, nombre y cuota de su jefe.

25. SELECT empleados.numemp, empleados.nombre, empleados.cuota, empleados.jefe, jefes.nombre AS [Nombre jefe], jefes.cuota AS [Cuota jefe]

FROM empleados LEFT JOIN empleados jefes ON empleados.jefe = jefes.numemp;

26. Desde el entorno gráfico cambia el empleado 111, asígnale el jefe 110 y la oficina 21. Después cambia la sentencia anterior para que salgan también los empleados cuyo jefe no tenga cuota.

27. SELECT empleados.numemp, empleados.nombre, empleados.cuota, empleados.jefe, jefes.nombre, jefes.cuota

28. FROM empleados INNER JOIN empleados jefes ON empleados.jefe = jefes.numemp

29. WHERE empleados.cuota > jefes.cuota OR

(empleados.cuota IS NOT NULL AND jefes.cuota IS NULL)

30. Listar los empleados que no están asignados a la misma oficina que su jefe, queremos número, nombre y número de oficina tanto del empleado como de su jefe.

31. SELECT e.numemp, e.nombre, e.oficina, e.jefe, j.nombre as [nombre jefe], j.oficina AS [oficina jefe]

32. FROM empleados e INNER JOIN empleados j ON e.jefe = j.numemp

WHERE e.oficina <> j.oficina;

33. En el punto anterior no salen los que no tienen oficina, cambiar la sentencia para que aparezcan.

34. SELECT e.numemp, e.nombre, e.oficina, e.jefe, j.nombre as [nombre jefe], j.oficina AS [oficina jefe]

35. FROM empleados e INNER JOIN empleados j ON e.jefe = j.numemp

WHERE e.oficina <> j.oficina OR e.oficina IS NULL OR j.oficina IS NULL;

36. Lo mismo que la anterior pero queremos que aparezca también la ciudad de las oficinas (tanto del empleado como de su jefe).

37. SELECT e.numemp, e.nombre, e.oficina, ofiemp.ciudad, e.jefe, j.nombre as [nombre jefe], j.oficina AS [oficina jefe], ofijefe.ciudad

38. FROM (oficinas ofiemp RIGHT JOIN empleados e ON ofiemp.oficina= e.oficina)

39. INNER JOIN (empleados j LEFT JOIN oficinas ofijefe ON j.oficina = ofijefe.oficina)

40. ON e.jefe = j.numemp

WHERE e.oficina <> j.oficina OR e.oficina IS NULL OR j.oficina IS NULL;

Ejercicio 2: Comparar tablas

1. Obtener los empleados de GestionSimples que aparecen en GestionA con otra oficina.

2. SELECT numemp, oficina

3. FROM gestionsimples.dbo.empleados

4. EXCEPT

5. SELECT numemp, oficina

FROM gestionA.dbo.empleados;

Prueba evaluativa unidad 4: Consultas multitabla


1. Para unir dos tablas con la cláusula UNION, éstas deberán:

a) Tener los datos de una o más columnas en común. Por ejemplo, que en ambas tablas se referencie a un código.

b) Tener el mismo esquema de tabla, es decir, mismo número de columnas y mismo tipo de datos, en el mismo orden.

c) Tener registros (filas) en común.

d) Estar en la misma base de datos.

2. Cuando utilizamos UNION con dos tablas, sin incluir una cláusula ORDER BY, se muestra una única tabla...

a) Situando los registros de la primera tabla de la sentencia SQL a la derecha. Se mantienen los encabezados de ambas.

b) Situando los registros de la primera tabla de la sentencia SQL a la izquierda. Se mantienen los encabezados de ambas.

c) Situando los registros de la primera tabla de la sentencia SQL encima de la segunda. Se utilizan los encabezados de la primera.

d) Situando los registros de la segunda tabla de la sentencia SQL encima de la primera. Se utilizan los encabezados de la segunda.

3. Cuando utilizamos UNION...

a) Es una buena práctica escribir la cláusula DISTINCT, para que se eliminen los registros repetidos.

b) No necesitamos incluir un DINSTINCT, porque lo realiza automáticamente, a menos que indiquemos lo contrario (ALL).

c) No podremos utilizar las cláusulas DISTINCT/ALL.

d) La consulta se realiza más rápidamente porque por defecto ejecuta la cláusula DISTINCT.

4. Las cláusulas INTERSECT y EXCEPT:

a) Tienen una sintaxis similar.

b) Se basan en la comparación de tablas. Una muestra los registros que tienen en común y la otra los que están en la primera tabla pero no en la segunda.



5. UNION, INTERSECT y EXCEPT sólo permiten comparar tablas que tienen el mismo esquema.

a) Verdadero.

b) Falso.

6. Señala la afirmación incorrecta. La composición de tablas...

a) Amplia la información de una tabla con la de otra, emparejando filas.

b) Muestra datos de dos tablas, que pueden tener un esquema diferente.

c) Se consigue indicando más de una tabla de origen en la FROM.

d) Precisa obligatoriamente de la cláusula ON, donde se indica la columna que tienen en común ambas tablas.

7. Indica si la siguiente sentencia es correcta: SELECT * FROM empleados INNER JOIN oficinas ON oficina = oficina;

a) No, porque en las tablas compuestas no se puede utilizar la palabra clave *.

b) No, porque si el campo se llama igual en ambas tablas hay que cualificarlo: empleados.oficina y oficinas.oficina.

c) No, porque la sentencia correcta sería: SELECT * FROM empleados, oficinas WHERE empleados.oficina=oficinas.oficina;.

d) Sí, es correcta.

8. Se pueden incluir varias combinaciones de tablas en una única consulta.

a) Verdadero.

b) Falso.

Unidad 5. Ejercicio paso a paso: La función MAX


Objetivo

Realizar consultas de resumen utilizando funciones de agregado. En este caso MAX.


Hallar el mejor superávit de todos los empleados.

SELECT MAX(ventas - cuota) AS [Mejor superavit]

FROM empleados;

Resultado:

Mejor superavit

81000,00

Unidad 5. Ejercicio paso a paso: La función SUM


Objetivo

Realizar consultas de resumen utilizando funciones de agregado. En este caso SUM.


¿Cuál es el total de cuotas y total de ventas de todos los empleados?

SELECT SUM(cuota) AS [Total cuotas], SUM(ventas) AS [Total ventas]

FROM empleados;

Resultado:

Total cuotas Total ventas

273000,00 440000,00

¿Cuál es el importe total de los pedidos realizados por el empleado Luis Antonio?

SELECT SUM(importe) AS [Importe vendido]

FROM pedidos INNER JOIN empleados ON rep = numemp

WHERE nombre = 'Luis Antonio';

Resultado:

Importe vendido

46,08

Unidad 5. Ejercicio paso a paso: La función AVG


Objetivo

Realizar consultas de resumen utilizando funciones de agregado. En este caso AVG.


¿Cuál es la cuota media y las ventas medias de los empleados?

SELECT AVG(cuota) AS [Cuota media], AVG(ventas) AS [Ventas medias]

FROM empleados;

Al ejecutar la consulta aparecerá una advertencia: valor NULL eliminado por el agregado u otra operación SET.

Este mensaje es debido a que en las columnas hay valores nulos y nos advierte de que no se han tenido en cuenta, en lo sucesivo no se indicará la advertencia en el resultado a obtener aunque sí aparezca cuando se ejecute la consulta.

Resultado:

Cuota media Ventas medias

22750,00 33846,1538

También podríamos presentar los valores de una forma más elegante si realizamos un redondeado a las ventas:

SELECT AVG(cuota) AS [Cuota media], ROUND(AVG(ventas),2) AS [Ventas medias]

FROM empleados;

¿De media, cuánto superávit obtienen los empleados, considerando superávit la diferencia entre lo vendido y su cuota?

SELECT AVG(ventas-cuota) AS [Superávit medio]

FROM empleados;

Resultado:

Superávit medio

13283,3333

Hallar el precio medio de los productos del fabricante bic.

SELECT AVG(precio) AS [Precio medio]

FROM productos

WHERE idfab = 'bic';

Resultado:

Precio medio

3,5233

Calcular el importe medio de los pedidos realizados por el cliente 2103.

SELECT AVG(importe) AS [importe medio]

FROM pedidos

WHERE clie = 2103;

Resultado:

importe medio

183,824

Unidad 5. Ejercicio paso a paso: Agrupar filas con GROUP BY


Objetivo

Realizar consultas de resumen utilizando la cláusula GROUP BY para agrupar filas.


Hallar el importe medio de pedidos realizados por cada empleado.

SELECT rep, AVG(importe) AS [Importe medio]

FROM pedidos

GROUP BY rep;

Resultado:

rep Importe medio

101 88,76

102 25,92

103 21,00

105 154,5492

106 23,04

107 114,7733

108 40,9042

109 35,525

110 159,6066

Repetir la consulta pero para que también salgan los empleados que no han vendido nada.

SELECT numemp, AVG(importe) AS [Importe medio]

FROM pedidos RIGHT JOIN empleados ON rep = numemp

GROUP BY numemp;

Resultado:

rep Importe medio

101 88,76

102 25,92

103 21,00

104 NULL

105 154,5492

106 23,04

107 114,7733

108 40,9042

109 35,525

110 159,6066

111 NULL

112 NULL

113 NULL

114 NULL

¿Cuál es en cada una de las oficinas, el rango de cuotas asignadas a sus vendedores?

SELECT oficinas.oficina, MIN(cuota) AS [Cuota mínima], MAX(cuota) AS [Cuota máxima]

FROM oficinas LEFT JOIN empleados ON empleados.oficina = oficinas.oficina

GROUP BY oficinas.oficina;

Resultado:

oficina Cuota mínima Cuota máxima

11 27500,00 27500,00

12 20000,00 30000,00

13 35000,00 40000,00

21 10000,00 35000,00

22 30000,00 30000,00

23 NULL NULL

24 NULL NULL

25 NULL NULL

26 NULL NULL

27 NULL NULL

28 NULL NULL

29 NULL NULL

30 NULL NULL

31 NULL NULL

Listar cuántos empleados están asignados a cada oficina, incluso las que no tienen.

SELECT oficinas.oficina, COUNT(numemp) AS Empleados

FROM oficinas LEFT JOIN empleados ON empleados.oficina = oficinas.oficina

GROUP BY oficinas.oficina;

Resultado:

oficina Empleados

11 1

12 3

13 2

21 3

22 1

23 0

24 0

25 0

26 0

27 0

28 0

29 0

30 0

31 0

Listar cuántos clientes son atendidos por cada empleado (el cliente ha realizado un pedido con él).

SELECT rep, COUNT(DISTINCT clie) AS [Clientes atendidos]

FROM pedidos

GROUP BY rep;

Resultado:

rep Clientes atendidos

101 3

102 2

103 1

105 2

106 2

107 2

108 3

109 1

110 2

Unidad 5. Ejercicio paso a paso: Selección sobre agrupaciones con HAVING


Objetivo

Filtrar los resultados de una consulta de resumen con GROUP BY, mediante la cláusula HAVING.


Para cada empleado cuyos pedidos suman más de 300 euros, hallar el importe medio vendido.

SELECT rep, AVG(importe) AS [Importe medio]

FROM pedidos

GROUP BY rep

HAVING SUM(importe) > 300;

Resultado:

rep Importe medio

105 154,5492

107 114,7733

110 159,6066

Si quisiéramos que además aparezca el nombre del empleado:

SELECT rep, nombre, AVG(importe) AS [Importe medio]

FROM empleados INNER JOIN pedidos ON numemp = rep

GROUP BY rep, nombre

HAVING SUM(importe) > 300;

rep nombre Importe medio

105 Vicente Pantalla 154,5492

107 Jorge Gutiérrez 114,7733

110 Juan Victor 159,6066

Por cada oficina con 2 o más personas, calcula la cuota total y las ventas totales de todos los empleados que trabajan en la oficina, de la oficina queremos el número y la ciudad.

SELECT oficinas.oficina, ciudad, SUM(cuota) AS [Cuota total], SUM(empleados.ventas) AS ventas

FROM empleados INNER JOIN oficinas ON empleados.oficina = oficinas.oficina

GROUP BY oficinas.oficina, ciudad

HAVING COUNT(*) > 2;

Resultado:

oficina ciudad Cuota total ventas

12 Alicante 77500,00 73400,00

21 Badajoz 80000,00 143500,00

Ejercicios unidad 5: Consultas de resumen

Para realizar los ejercicios, deberás utilizar la base de datos GestionSimples.

Ejercicio 1: Funciones de agregado

1. ¿Cuántas oficinas tenemos en Valencia?

Resultado:

Valencianas4

2. Hallar cuántos pedidos hay de más de 250 euros.

Resultado:

Superiores a 2504

3. ¿Cuántos títulos (cargos) de empleados se usan?

Resultado:

Cuántos títulos3

4. ¿Entre qué cuotas se mueven los empleados?

Resultado:

Cuota mínima Cuota máxima3000,00 35000,00


Ejercicio 2: Agrupamiento de filas: GROUP BY

1. De cada vendedor (todos) queremos saber su nombre y el importe total vendido. En caso de que el importe sea NULL, cambiarlo por 0,00 con la función ISNULL().

Resultado:

numemp nombre Importe vendido101 Antonio Viguer 266,28102 Alvaro Jaumes 77,76103 Juan Rovira 21,00104 José González 0,00105 Vicente Pantalla 772,746106 Luis Antonio 46,08107 Jorge Gutiérrez 344,32108 Ana Bustamante 286,33109 María Sunta 71,05110 Juan Victor 478,82111 Juan Gris 0,00112 Julián Martorell 0,00113 Juan Gris 0,00114 Pablo Moreno 0,00


2. De cada empleado, obtener el importe vendido a cada cliente.

Resultado:

rep Cliente Importe vendido106 2101 14,58101 2102 39,78105 2103 735,296102 2106 40,26110 2107 28,82101 2108 1,50109 2108 71,05107 2109 313,50103 2111 21,00105 2111 37,45108 2112 29,25

110 2112 450,00101 2113 225,00108 2114 221,00106 2117 31,50108 2118 36,08102 2120 37,50107 2124 30,82


3. Repetir la consulta anterior pero ahora deben aparecer también los empleados que no han vendido nada.

Resultado:

rep Cliente Importe vendido101 2102 39,78101 2108 1,50101 2113 225,00102 2106 40,26102 2120 37,50103 2111 21,00104 NULL NULL105 2103 735,296105 2111 37,45106 2101 14,58106 2117 31,50107 2109 313,50107 2124 30,82108 2112 29,25108 2114 221,00108 2118 36,08109 2108 71,05110 2107 28,82110 2112 450,00111 NULL NULL112 NULL NULL113 NULL NULL114 NULL NULL


4. Repetir la consulta pero ahora debe aparecer también el total de cuánto ha vendido cada empleado. (Recuerda una opción de la cláusula GROUP BY)

Resultado:

rep clie Importe vendidoNULL NULL 2364,386101 NULL 266,28101 2102 39,78101 2108 1,50101 2113 225,00102 NULL 77,76102 2106 40,26102 2120 37,50103 NULL 21,00103 2111 21,00104 NULL NULL104 NULL NULL105 NULL 772,746105 2103 735,296105 2111 37,45106 NULL 46,08106 2101 14,58106 2117 31,50107 NULL 344,32107 2109 313,50107 2124 30,82108 NULL 286,33108 2112 29,25108 2114 221,00108 2118 36,08109 NULL 71,05109 2108 71,05110 NULL 478,82110 2107 28,82110 2112 450,00111 NULL NULL111 NULL NULL112 NULL NULL112 NULL NULL113 NULL NULL

113 NULL NULL114 NULL NULL114 NULL NULL


5. En los resultados anteriores no se distinguen bien las líneas que corresponden a totales. Modificar la consulta para obtener este resultado:

rep clie Importe vendidoAgrupa

clieAgrupanumemp

NULL NULL 2364,386 1 1101 NULL 266,28 1 0101 2102 39,78 0 0101 2108 1,50 0 0101 2113 225,00 0 0102 NULL 77,76 1 0102 2106 40,26 0 0102 2120 37,50 0 0103 NULL 21,00 1 0103 2111 21,00 0 0104 NULL NULL 0 0104 NULL NULL 1 0... .... ... (sigue) ... ...

6.Puedes consultar aquí las soluciones propuestas.

7. Ahora modifica la consulta para que las filas de totales aparezcan más claras. (Recuerda la función CASE)

Resultado:

rep clie Importe vendido Agrupa clieAgrupanumemp

NULL NULL 2364,386 Total empleado Total final101 NULL 266,28 Total empleado 101 2102 39,78 101 2108 1,50 101 2113 225,00 102 NULL 77,76 Total empleado 102 2106 40,26 102 2120 37,50 103 NULL 21,00 Total empleado

103 2111 21,00 104 NULL NULL 104 NULL NULL Total empleado ... .... ... (sigue) ... ...


8. Ahora coloca las columnas Agrupa delante de las demás columnas:

Resultado:

Agrupanumemp

Agrupa clie rep clie Importe vendido

Total final Total empleado NULL NULL 2364,386 Total empleado 101 NULL 266,28 101 2102 39,78 101 2108 1,50 101 2113 225,00 Total empleado 102 NULL 77,76 102 2106 40,26 102 2120 37,50 Total empleado 103 NULL 21,00 103 2111 21,00 104 NULL NULL Total empleado 104 NULL NULL... (sigue) ... ... .... ...


9. Ahora queremos que "Total empleado" aparezca en la columna clie. Piensa primero en cuántas columnas quieres y luego en cada columna que tiene que salir.

Resultado:

Agrupanumemp

numemp clie Importe vendido

Total final NULL Total empleado 2364,386 101 Total empleado 266,28 101 2102 39,78 101 2108 1,50 101 2113 225,00 102 Total empleado 77,76 102 2106 40,26

102 2120 37,50 103 Total empleado 21,00 103 2111 21,00 104 NULL NULL 104 Total empleado NULL... (sigue) ... .... ...


10. El empleado 104 (y otros) no ha vendido a nadie y por eso sale en la columna clie la palabra NULL, queremos que en estos casos no aparezca nada (se deje en blanco), y el importe si es NULL que salga un cero.

Resultado:

Agrupanumemp

numemp clie Importe vendido

Total final NULL Total empleado 2364,386 101 2102 39,78 101 2108 1,50 101 2113 225,00 101 Total empleado 266,28 102 2106 40,26 102 2120 37,50 102 Total empleado 77,76 103 2111 21,00 103 Total empleado 21,00 104 0,00 104 Total empleado 0,00... (sigue) ... .... ...

Nota: Recuerda la función ISNULL() (para la columna importe vendido) y la función CASE con diferentes condiciones (para la columna clie).Puedes consultar aquí las soluciones propuestas.

11. Lo rematamos para que el resultado quede así:

numemp clie Importe vendidoTotal final ... ... 2364,386 101 2102 39,78 101 2108 1,50 101 2113 225,00 101 Total empleado 266,28 102 2106 40,26

102 2120 37,50 102 Total empleado 77,76 103 2111 21,00 103 Total empleado 21,00 104 0,00 104 Total empleado 0,00... (sigue) ... .... ...


Ayuda ejercicios unidad 5: Consultas de resumen

Ejercicio 1: Funciones de agregado

1. ¿Cuántas oficinas tenemos en Valencia?

SELECT COUNT(*) AS Valencianas

FROM oficinas

WHERE ciudad = 'Valencia';

2. Hallar cuántos pedidos hay de más de 250 euros.

SELECT COUNT(*) AS [Superiores a 250]

FROM pedidos


3. ¿Cuántos títulos (cargos) de empleados se usan?

SELECT COUNT(DISTINCT titulo) AS [Cuántos títulos]

FROM empleados;

4. ¿Entre qué cuotas se mueven los empleados?

SELECT MIN(cuota) AS [Cuota mínima], MAX(cuota) AS [Cuota máxima]

FROM empleados;

Ejercicio 2: Agrupamiento de filas: GROUP BY

1. De cada vendedor (todos) queremos saber su nombre y el importe total vendido. En caso de que el importe sea NULL, cámbialo por 0,00 con la función ISNULL().

SELECT numemp, nombre, ISNULL(SUM(importe),0) AS [Importe vendido]

FROM empleados LEFT JOIN pedidos ON numemp = rep

GROUP BY numemp, nombre;

2. De cada empleado, obtener el importe vendido a cada cliente.

SELECT rep, clie AS Cliente, SUM(importe) AS [Importe vendido]

FROM pedidos

GROUP BY rep, clie;

3. Repetir la consulta anterior pero ahora deben aparecer también los empleados que no han vendido nada.

SELECT numemp, clie, SUM(importe) AS [Importe vendido]


GROUP BY numemp, clie

ORDER BY numemp,clie;

4. Repetir la consulta pero ahora debe aparecer también el total de cuánto ha vendido cada empleado.

SELECT numemp, clie, SUM(importe) AS [Importe vendido]


GROUP BY numemp, clie WITH ROLLUP


5. En los resultados anteriores no se distinguen bien las líneas que corresponden a totales. Modificar la consulta para indicar con un 1 si es una fila de totales y con un 0 si no lo es.

SELECT numemp, clie, SUM(importe) AS [Importe vendido], GROUPING(clie) AS [Agrupa clie], GROUPING(numemp) AS [Agrupa numemp]




6. Ahora modifica la consulta para que las filas de totales aparezcan más claras, substituyendo el 1 de Agrupa clie por "Total empleado", el 1 de Agrupa numemp por Total final y el valor 0 por espacio en blanco.

SELECT numemp, clie, SUM(importe) AS [Importe vendido],

CASE GROUPING(clie) WHEN 0 THEN ' ' WHEN 1 THEN 'Total empleado' END AS [Agrupa clie],

CASE GROUPING(numemp) WHEN 0 THEN ' ' ELSE 'Total Final' END AS [Agrupa numemp]




7. Ahora coloca las columnas Agrupa delante de las demás columnas.

SELECT CASE GROUPING(numemp) WHEN 0 THEN ' ' ELSE 'Total Final' END AS [Agrupa numemp],

CASE GROUPING(clie) WHEN 0 THEN ' ' WHEN 1 THEN 'Total empleado' END AS [Agrupa clie],

numemp, clie, SUM(importe) AS [Importe vendido]




8. Ahora queremos que "Total empleado" aparezca en la columna clie.


numemp,

CASE GROUPING(clie) WHEN 0 THEN CONVERT(CHAR(4),clie) WHEN 1 THEN 'Total empleado' END AS [Clie],

SUM(importe) AS [Importe vendido]




9. El empleado 104 (y otros) no ha vendido a nadie y por eso sale en la columna clie la palabra NULL, queremos que en estos casos no aparezca nada (se deje en blanco), y el importe si es NULL que salga un cero.


numemp,

CASE WHEN GROUPING(clie) = 1 THEN 'Total empleado' WHEN clie IS NULL THEN ' ' ELSE CONVERT(CHAR(4),clie) END AS [Clie],

ISNULL(SUM(importe),0) AS [Importe vendido]




10. Lo rematamos para que la fila del Total final no muestre los valores "NULL" ni "Total empleado". En cambio, los cambiaremos por tres puntos. Tampoco mostraremos el encabezado "Agrupa numemp".

SELECT CASE GROUPING(numemp) WHEN 0 THEN ' ' ELSE 'Total Final' END AS [ ],

ISNULL(CONVERT(CHAR(3),numemp),'... ') AS [numemp],

CASE WHEN GROUPING(clie) = 1 AND GROUPING(numemp) = 0

THEN 'Total empleado' WHEN GROUPING(clie) = 1

AND GROUPING(numemp) = 1 THEN '...'

WHEN clie IS NULL THEN ' ' ELSE CONVERT(CHAR(4),clie) END AS [Clie],

ISNULL(SUM(importe),0) AS [Importe vendido]




Prueba evaluativa unidad 5: Consultas de resumen


1. Las funciones de columna o de agregado:

a) Realizan un cálculo, operación o comprobación sobre los valores de una columna.

b) Hacen que se ejecute una consulta de resumen, de forma que es posible que no veamos los registros originales, sino el resultado del cálculo que se ha realizado sobre ellos.



2. DISTINCT no se puede utilizar en las consultas de resumen.

a) Verdadero.

b) Falso.

3. Si aplicamos la cláusula WHERE en una consulta cuya lista de selección contiene una función de columna, por ejemplo SUM()...

a) El WHERE se ejecuta antes de realizar el sumatorio.

b) El WHERE se ejecuta después de realizar el sumatorio.

c) El WHERE no se ejecuta.

d) La consulta dará error, porque debemos utilizar HAVING.

4. La función COUNT(numemp) incluye en su recuento los valores NULL.

a) Verdadero.

b) Falso.

5. La función COUNT(*) incluye en su recuento las filas que contienen valores NULL.

a) Verdadero.

b) Falso.

6. Si una consulta contiene en su lista de selección columnas y funciones a la vez, por ejemplo: SELECT oficina, COUNT(numemp) as [Num empleados]...

a) El GROUP BY deberá contener obligatoriamente las columnas no calculadas, en este caso oficina.

b) El GROUP BY deberá contener obligatoriamente las columnas calculadas, en este caso COUNT(numemp) as [Num empleados].



7. CUBE y ROLLUP:

a) Muestran filas de resumen.

b) Se incluyen en la cláusula GROUP BY.



8. La función de agregado GROUPING...

a) Se indica en la lista de selección, y sirve para saber si se trata de una fila de resumen, resultado de un CUBE o un ROLLUP.

b) Muestra una fila más, que incluye el total final de la función de agregado.

c) No se puede utilizar en consultas que incluyan un GROUP BY.

d) Se escribe a continuación del GROUP BY.

9. La cláusula HAVING...

a) Actúa sobre las filas resultantes de una consulta de resumen y funciona igual que la cláusula WHERE.

b) Actúa igual que la cláusula WHERE, sobre las filas origen.

c) Se puede utilizar en consultas sin GROUP BY.

d) Sólo se puede utilizar en consultas con ORDER BY.

Unidad 6. Ejercicio paso a paso: Subconsultas de resultado único


Objetivo

Realizar consultas que incluyan una subconsulta en la cláusula WHERE. Se realizarán utilizando operadores de comparación.


Listar todos los productos (código, descripción y stock) del fabricante ACI y cuyas existencias superan las existencias del producto ACI-41004.

SELECT idfab, idproducto, descripcion, existencias

FROM productos

WHERE idfab = 'ACI'

AND existencias > (SELECT existencias

FROM productos

WHERE idfab = 'ACI' AND idproducto = '41004');

Resultado:

idfab idproducto descripcion existencias

aci 41001 arandela 277

aci 41002 bisagra 167

aci 41003 art 13 207

Listar los empleados (numemp, nombre y edad) que tienen una cuota por encima de la media.

SELECT numemp, nombre, edad

FROM empleados

WHERE cuota > (SELECT AVG(cuota) FROM empleados);

Resultado:

numemp nombre edad

101 Antonio Viguer 45

102 Alvaro Jaumes 48

103 Juan Rovira 29

105 Vicente Pantalla 37

106 Luis Antonio 52

107 Jorge Gutiérrez 49

108 Ana Bustamante 62

114 Pablo Moreno 45

Obtener cuántos empleados tienen una cuota por encima de la media, y la edad media de estos.

SELECT count(*) AS Cuantos, AVG(edad) AS [Edad Media]

FROM empleados

WHERE cuota > (SELECT AVG(cuota) FROM empleados);

Resultado:

Cuantos Edad Media

8 45

Listar todas las oficinas cuyos objetivos superan la suma de las cuotas de sus vendedores.

SELECT oficina, ciudad

FROM oficinas

WHERE objetivo > (SELECT SUM(cuota) FROM empleados WHERE oficina = oficinas.oficina);

Resultado:

oficina ciudad

11 Valencia

12 Alicante

Unidad 6. Ejercicio paso a paso: El operador IN con subconsulta


Objetivo

Realizar consultas que incluyan una subconsulta en la cláusula WHERE, introducida por el operador IN.


Listar los clientes (nombre) que han remitido pedidos del fabricante ACI y nºproducto que empiece por 4100, entre enero y el 15 de abril de 1990.

SELECT numclie,nombre

FROM clientes

WHERE numclie IN (SELECT clie FROM pedidos

WHERE fab = 'ACI'

AND producto LIKE '4100%' AND fechapedido >= '01/01/90' AND fechapedido <'16/04/90');

Resultado:

numclie nombre

2102 Alvaro Rodríguez

2118 Junípero Alvarez

Listar los empleados (numemp, nombre) que han realizado un pedido que represente más del 1% de su cuota.


FROM empleados

WHERE numemp IN (SELECT rep FROM pedidos

WHERE importe > cuota* 0.01);

Resultado:

numemp nombre



109 María Sunta

Unidad 6. Ejercicio paso a paso: Comparación modificada ANY


Objetivo

Realizar una consulta que, en su WHERE, compare con el resultado de una subconsulta mediante el test ANY.


Listar los numclie y nombres de los clientes que tienen asignado el representante Juan Gris.

SELECT numclie, nombre

FROM clientes

WHERE repclie = ANY (SELECT numemp

FROM empleados

WHERE nombre = 'Juan Gris');

Resultado:

numclie nombre

2125 Pepito Grillo

Esta consula también podría haberse realizado con el operador IN.

Listar los empleados (numemp, nombre y oficina) que trabajan en oficinas "buenas" (las que han vendido más que su objetivo).

SELECT numemp, nombre, oficina

FROM empleados

WHERE oficina = ANY (SELECT oficina

FROM oficinas

WHERE ventas > objetivo);

Resultado:

numemp nombre oficina



106 Luis Antonio 11

108 Ana Bustamante 21

111 Juan Gris 21

114 Pablo Moreno 13

Esta consula también podría haberse realizado con el operador IN.

Unidad 6. Ejercicio paso a paso: Subconsultas con EXISTS


Objetivo

Realizar una consulta que, en su WHERE, compruebe si el registro en cuestión existe como resultado de otra consulta. Para ello, utilizaremos una subconsulta precedida de EXISTS o NOT EXISTS.


Listar los empleados (numemp, nombre y oficina) que no trabajan en oficinas dirigidas por el empleado 108.

SELECT numemp, nombre, oficina

FROM empleados

WHERE NOT EXISTS (SELECT *

FROM oficinas

WHERE dir = 108 and empleados.oficina = oficinas.oficina);

Resultado:

numemp nombre oficina


103 Juan Rovira 12

104 José González 12


106 Luis Antonio 11

109 María Sunta NULL

110 Juan Victor NULL

112 Julián Martorell NULL

113 Juan Gris NULL

114 Pablo Moreno 13

Listar los productos (clave y descripcion) para los cuales no se ha recibido ningún pedido de 10 euros o más.

SELECT idfab, idproducto, descripcion

FROM productos

WHERE NOT EXISTS (SELECT * FROM pedidos

WHERE idfab = fab

AND idproducto = producto AND importe >= 10);

Resultado:

idfab idproducto descripcion

aci 41001 arandela

aci 4100x junta

bic 41089 rodamiento

bic 41672 plato

imm 775c reostato 2

imm 887h caja clavos

imm 887p perno

imm 887x manivela

qsa xk48 red

qsa xk8a red

Listar las oficinas (oficina, ciudad) que tienen al menos un empleado cuya cuota represente más del 55% del objetivo de la oficina.


FROM oficinas

WHERE EXISTS (SELECT * FROM empleados

WHERE oficinas.oficina = empleados.oficina AND cuota > (.55 * objetivo));

Resultado:

oficina ciudad

13 Castellón

22 A Coruña

Listar los empleados (numemp, nombre y edad) que no dirigen una oficina.

SELECT numemp, nombre, edad

FROM empleados

WHERE NOT EXISTS (SELECT * FROM oficinas WHERE dir = numemp);

Resultado:

numemp nombre edad



103 Juan Rovira 29

107 Jorge Gutiérrez 49

109 María Sunta 31

110 Juan Victor 41

111 Juan Gris 50

112 Julián Martorell 50

113 Juan Gris 18

114 Pablo Moreno 45

Ejercicios unidad 6: Las subconsultas

Para realizar los ejercicios, deberás utilizar la base de datos GestionSimples.

1. Listar los clientes (numclie, nombre) asignados a Juan que no han remitido un pedido superior a 300 euros.

numclie nombre

2107 Julian López

2107 Julian López

2121 Vicente Ríos

2125 Pepito Grillo

2. Listar los empleados (numemp, nombre) mayores de 40 años que dirigen a un vendedor con superávit (ha vendido más que su cuota).

numemp nombre

106 Luis Antonio

108 Ana Bustamante

110 Juan Victor

3. Listar los empleados (código de empleado) cuyo importe de pedido medio para productos fabricados por ACI es superior al importe medio global (de todos los pedidos de todos los empleados).

rep

105


4. Listar los empleados (numemp, nombre, ventas) cuyas ventas son iguales o superiores al objetivo de las oficinas de una determinada ciudad (de todas las oficinas de esa ciudad). Las oficinas con objetivo nulo no se deben de tener en cuenta (como si no existiesen). Y si no hay oficinas en la ciudad no queremos que salga ningún empleado. Intentar primero resolver la consulta utilizando >=ALL.

Probar primero con A Coruña:

numemp nombre ventas

102 Alvaro Jaumes 47400,00

111 Juan Gris 60000,00

112 Julián Martorell 91000,00

Ahora con Pamplona. Pamplona tiene una oficina con objetivo nulo, en este caso no queremos que esa oficina cuente.

numemp nombre ventas

101 Antonio Viguer 30500,00

102 Alvaro Jaumes 47400,00

103 Juan Rovira 28600,00

105 Vicente Pantalla 36800,00

106 Luis Antonio 29900,00

108 Ana Bustamante 36100,00

109 María Sunta 39200,00

111 Juan Gris 60000,00

112 Julián Martorell 91000,00


114 Pablo Moreno 37000,00

Para Barcelona. En este caso no tenemos oficinas en Barcelona por lo que no tiene que salir ningún empleado.

Ahora para Madrid. Como en Madrid sólo hay una oficina y no tiene objetivo no tiene que salir ningún empleado.

Intentar resolver la consulta sin utilizar ALL.

5. Listar las oficinas en donde todos los empleados tienen ventas que superan al 50% del objetivo de la oficina.

oficina ciudad

11 Valencia

13 Castellon

22 A Coruña


Ayuda ejercicios unidad 6: Las subconsultas

1. Listar los clientes (numclie, nombre) asignados a Juan que no han remitido un pedido superior a 300 euros.

SELECT numclie, nombre

FROM clientes

WHERE repclie IN (SELECT numemp FROM empleados

WHERE nombre LIKE 'Juan%')

AND NOT EXISTS (SELECT * FROM pedidos

WHERE numclie = clie AND importe > 300);

2. Listar los empleados (numemp, nombre) mayores de 40 años que dirigen a un vendedor con superávit (ha vendido más que su cuota).


FROM empleados

WHERE edad > 40

and numemp IN (SELECT jefe FROM empleados WHERE ventas > cuota );


3. Listar los empleados (código de empleado) cuyo importe de pedido medio para productos fabricados por ACI es superior al importe medio global (de todos los pedidos de todos los empleados).

SELECT rep

FROM pedidos

WHERE fab = 'ACI'

GROUP BY rep

HAVING AVG(importe) > (SELECT AVG(importe) FROM pedidos);

4. Listar los empleados (numemp, nombre, ventas) cuyas ventas son iguales o superiores al objetivo de las oficinas de una determinada ciudad (de todas las oficinas de esa ciudad). Las oficinas con objetivo nulo no se deben de tener en cuenta (como si no existiesen). Y si no hay oficinas en la ciudad no queremos que salga ningún empleado. Intentar primero resolver la consulta utilizando >=ALL.

Probar primero con A Coruña:

SELECT numemp, nombre, ventas

FROM empleados

WHERE ventas >= ALL (SELECT objetivo

FROM oficinas

WHERE ciudad = 'A Coruña');

Ahora con Pamplona. Pamplona tiene una oficina con objetivo nulo, en este caso no queremos que esa oficina cuente.


FROM empleados


FROM oficinas

WHERE ciudad = 'Pamplona' and objetivo is not null);

Para Barcelona. En este caso no tenemos oficinas en Barcelona por lo que no tiene que salir ningún empleado. Si no queremos que salgan tendremos que añadir una condición:


FROM empleados


FROM oficinas

WHERE ciudad = 'Barcelona' and objetivo is not null)

AND EXISTS (SELECT *

FROM oficinas

WHERE ciudad = 'Barcelona');

Ahora para Madrid. Como en Madrid sólo hay una oficina y no tiene objetivo no tiene que salir ningún empleado.


FROM empleados


FROM oficinas

WHERE ciudad = 'Madrid' and objetivo is not null)

AND EXISTS (SELECT *

FROM oficinas

WHERE ciudad = 'Madrid' and objetivo is not null);

Esta sería la consulta definitiva que nos serviría para cualquier situación. Pero como se ve el modificador ALL puede darnos problemas. Para solucionarlo, realizamos la siguiente consulta.

Intentar resolver la consulta sin utilizar ALL.


FROM empleados

WHERE ventas >= (SELECT MAX(objetivo)

FROM oficinas

WHERE ciudad = 'Madrid');

5. Listar las oficinas en donde todos los empleados tienen ventas que superan al 50% del objetivo de la oficina.


FROM oficinas

WHERE (objetivo * .5) < = (SELECT MIN(ventas)

FROM empleados WHERE empleados.oficina = oficinas.oficina);

Prueba evaluativa unidad 6: Las subconsultas


1. Las subconsultas (o consulta interna)...

a) Se escriben entre corchetes.

b) Se escriben entre paréntesis.

c) Se escriben entre llaves.

d) Se finalizan con un punto y coma (;).

2. Las subconsultas no pueden contener la cláusula ORDER BY.

a) A menos que contengan, además, la cláusula TOP.

b) Porque no tendría sentido, ya que la subconsulta sirve para realizar una comprobación y no se va a visualizar.


d) A y B son falsas, ya que no hay problema en incluir un ORDER BY dentro de una subconsulta.

3. La subconsulta...

a) Se ejecuta al final, después de recorrer todos los registros de la consulta principal.

b) Se ejecuta por cada una de las filas de la consulta principal.

4. La referencia externa en una subconsulta es:

a) Un campo que no se encuentra en el origen de datos de la subconsulta, sino en el de la consulta principal.

b) Un campo que no se encuentra en el origen de datos de la consulta principal,

sino que es exclusivo de la propia subconsulta.

5. Las subconsultas normalmente se utilizan en las cláusulas...

a) WHERE y TOP.

b) WHERE y FROM.

c) WHERE y HAVING.

d) WHERE y GROUP BY.

6. En las subconsultas introducidas por la cláusula EXISTS...

a) Normalmente será necesario utilizar una referencia externa.

b) Se utiliza la palabra clave * en la lista de selección, porque lo que se evalúa es si devuelve o no filas, y no nos interesa ningún campo en particular.



7. Utilizar la expresión IN es equivalente a utilizar = ANY.

a) Verdadero.

b) Falso.

8. La subconsulta necesariamente debe tener un origen (FROM) diferente al de la consulta principal (externa).

a) No, son independientes. Pueden ejecutar consultas tanto sobre la misma tabla o vista como sobre orígenes diferentes.

b) No, porque las subconsultas no tienen origen de datos FROM.

c) Sí, porque no se puede ejecutar dos consultas sobre la misma tabla o vista a la vez.


9. Una subconsulta puede contener una subconsulta dentro de ella.

a) Verdadero.

b) Falso.

Unidad 7. Ejercicio paso a paso: Insertar datos creando una nueva tabla

Nota: Para realizar los ejercicios necesitarás la base de datos Gestion8.

Objetivo

Crear una tabla a partir de los datos de otra, con SELECT ... INTO.


Crear una copia de empleados con el nombre Nuevaempleados, lo mismo para la tabla de oficinas (Nuevaoficinas), la de clientes (Nuevaclientes), la de productos (Nuevaproductos) y la de pedidos (Nuevapedidos). A partir de este momento estas tablas servirán para tener una copia original de las tablas que vamos a modificar. Las tablas creadas sólo se utilizarán en el último ejercicio del tema.

SELECT * INTO Nuevaempleados FROM empleados;

SELECT * INTO Nuevaoficinas FROM oficinas;

SELECT * INTO Nuevaclientes FROM clientes;

SELECT * INTO Nuevaproductos FROM productos;

SELECT * INTO Nuevapedidos FROM pedidos;

Crear una tabla (Oeste) con todas las oficinas del Oeste, la tabla tendrá los mismos datos que oficinas. (3 filas afectadas)

SELECT * INTO Oeste

FROM oficinas

WHERE region = 'Oeste';

Unidad 7. Ejercicio paso a paso: Insertar una fila de valores


Objetivo

Insertar una fila de datos en una tabla ya existente.


Añadir una nueva oficina para la ciudad de Elx, con el número de oficina 40, con director 108 y con un objetivo de 100.000€.

INSERT INTO oficinas (oficina, ciudad, dir, objetivo) VALUES (40, 'Elx', 108, 100000);

Añadir un nuevo empleado numemp: 115, nombre: Luis Garcia, oficina: 40, sin objetivo ni ventas ni director.

INSERT INTO empleados (numemp, nombre, oficina) VALUES (115, 'Luis Garcia', 40);

Añadir a la oficina 40 un empleado Antonio García López, con número de empleado 435, contratado hoy sin ventas con cuota 1200,45€ con título Vendedor, de momento no le asignaremos jefe.

INSERT INTO empleados (numemp, nombre, titulo, contrato, ventas, cuota, oficina)

VALUES (435, 'Antonio García López','Vendedor', GETDATE(), 0, 1200.45, 40);

Añadir a la oficina 40 otro empleado, Luis Valverde, con número de empleado 436, con los mismos datos que el anterior pero su jefe será el director de la oficina 40 (no sabemos qué número tiene).

INSERT INTO empleados (numemp, nombre, titulo, contrato, ventas, cuota, oficina, jefe)

SELECT 436, 'Luis Valverde','Vendedor', GETDATE(), 0, 1200.45, 40, dir

FROM oficinas WHERE oficina = 40;

Unidad 7. Ejercicio paso a paso: Insertar varias filas


Objetivo

Copiar un conjunto de filas de una tabla ya existente a otra.


Añadir a la tabla Oeste las oficinas del Este. (6 filas afectadas)

INSERT INTO Oeste SELECT *

FROM oficinas

WHERE region = 'Este';

Unidad 7. Ejercicio paso a paso: Modificar datos con UPDATE


Objetivo

Modificar los datos de una tabla.


Subir un 5% el precio de todos los productos del fabricante QSA. (3 filas afectadas)

UPDATE productos SET precio = ROUND(precio * 1.05,2)

WHERE idfab = 'qsa';

Resultado:

idfab idproducto precio

qsa xk47 3,73

qsa xk48 1,41

qsa xk48a 1,55

Poner a cero las ventas y cuota del empleado Luis Garcia, si hay varios con el mismo nombre actualizarlos todos. (1 filas afectadas)

UPDATE empleados set ventas=0, cuota=0

WHERE nombre = 'Luis Garcia';

Cambiar los empleados de la oficina 40 a la oficina 30. (3 filas afectadas)

UPDATE empleados SET oficina = 30

WHERE oficina = 40;

Actualizar los pedidos del fabricante rei dejando como representante el empleado asignado al cliente (2 filas afectadas). Lo más cómodo es, primero sacar la consulta que obtiene los pedidos a actualizar, y después convertirla a UPDATE.

UPDATE pedidos SET rep=numemp

FROM pedidos inner join (clientes inner join empleados ON repclie=numemp) ON clie=numclie

WHERE rep <> numemp and fab ='rei'

Estos son los pedidos afectados y cómo deben quedar, los pedidos en los que el representante ya es el correcto no se tienen que actualizar:

codigo numpedido clie rep fab

20 113042 2113 104 rei

21 113045 2112 108 rei

Actualizar el campo objetivo de la oficina 30 con las cuotas de los empleados asignados a ella.

UPDATE oficinas set oficinas.objetivo =(SELECT SUM (cuota)

from empleados where oficinas.oficina= empleados.oficina)

where oficina=30;

Actualizar el precio de los productos de BIC obteniendo el nuevo valor del precio medio del artículo vendido en los pedidos (si hay pedidos). Primero sacamos la lista y después redactar la UPDATE. Se puede hacer de dos formas, actualizando únicamente los productos de BIC que tienen pedidos, o actualizando todos los productos de BIC. En cualquiera de los dos casos los productos deberán acabar con el precio que aparece en la columna Nuevo.

UPDATE productos SET precio = round(ISNULL((SELECT avg(importe/cant) FROM pedidos WHERE fab=idfab and producto=idproducto),precio),2)

WHERE idfab='bic';

Estos son los productos que tenemos de BIC, precio es el precio actual, media es el precio medio de pedidos, y nuevo el valor que deberá quedar en precio después de actualizar:

idfab idproducto precio media nuevo

bic 41003 6,52 5,1566 5,16

bic 41089 2,25 NULL 2,25

bic 41672 1,80 NULL 1,80

Unidad 7. Ejercicio paso a paso: Modificar filas con DELETE


Objetivo

Eliminar registros de una tabla utilizando DELETE.


Eliminar el empleado 435.

DELETE empleados

WHERE numemp = 435;

Eliminar los pedidos del representante 105. (5 filas afectadas)

DELETE FROM pedidos

WHERE rep = 105;

Ejercicios unidad 7: Actualización de datos (I)

Para realizar los ejercicios, deberás utilizar la base de datos Gestion8.

1. Añadir a la oficina 40 otro empleado, Luis Valverde, con número de empleado 436, con los mismos datos que el anterior pero su jefe será el director de la oficina 40 (no sabemos qué número tiene).

2. Pasar los pedidos de octubre 1989 a diciembre 2008. (3 filas afectadas)

3. Queremos actualizar el importe de los pedidos del mes actual con el precio almacenado en la tabla productos. Ayuda: En un primer paso obtener los pedidos del mes actual obteniendo también el precio unitario dentro del pedido y el precio del producto de la tabla de productos.

codigo numpedido fechapedido cant importe precio pedido precio

1 110036 2008-12-12 00:00:00.000 9 22,50 2,50 NULL

2 110037 2008-12-12 00:00:00.000 7 31,50 4,50 45,00

6 112979 2008-12-12 00:00:00.000 6 150,00 25,00 NULL

9 112989 2008-12-10 00:00:00.000 6 14,58 2,43 2,43

16 113013 2008-12-28 00:00:00.000 1 6,52 6,52 5,16

Actualizar después la tabla de pedidos cambiando los importes para que el precio unitario corresponda con el precio del producto. Los pedidos de los productos que no tienen precio se quedarán como estaban. (3 filas afectadas)

codigo numpedido fechapedido cant importe precio pedido precio

1 110036 2008-12-12 00:00:00.000 9 22,50 2,50 NULL

2 110037 2008-12-12 00:00:00.000 7 315,00 4,50 45,00

6 112979 2008-12-12 00:00:00.000 6 150,00 25,00 NULL

9 112989 2008-12-10 00:00:00.000 6 14,58 2,43 2,43

16 113013 2008-12-28 00:00:00.000 1 5,16 6,52 5,16


4. Se ven algunos productos que no tienen precio, ahora vamos a actualizar el precio de estos productos con el precio medio utilizado en los pedidos donde aparecen.Ayuda: Primero sacamos los productos que queremos actualizar con los pedidos correspondientes:

idfab idproducto precio codigo numpedido fechapedido importeprecio pedido

aci 41001 NULL NULL NULL NULL NULL NULL

aci 41002 NULL 10 1129921990-04-15 20:00:00.000

7,60 0,76

aci 41002 NULL 18 1130272008-02-05 00:00:00.000

450,00 8,3333

aci 41003 NULL 15 1130122008-05-05 00:00:00.000

37,45 1,07

aci 41004 NULL 3 1129632008-05-10 00:00:00.000

3,276 0,117

aci 41004 NULL 4 1129681990-01-11 00:00:00.000

39,78 1,17

aci 41004 NULL 7 1129832008-05-10 00:00:00.000

7,02 1,17

aci 4100x NULL 25 1130552009-04-01 00:00:00.000

1,50 0,25

aci 4100x NULL 26 1130572008-11-01 00:00:00.000

NULL NULL

aci 4100y NULL 8 1129872008-01-01 00:00:00.000

275,00 25,00

aci 4100z NULL 1 1100362008-12-12 00:00:00.000

22,50 2,50

aci 4100z NULL 6 1129792008-12-12 00:00:00.000

150,00 25,00


Vemos que el producto ACI 41001 no se podrá actualizar porque no tiene pedidos. Pero los demás se actualizarán con el precio medio de sus pedidos, deberán quedar así (7 filas afectadas):

idfab idproducto precio

aci 41001 NULL

aci 41002 4,55

aci 41003 1,07

aci 41004 0,82

aci 4100x 0,25 *

aci 4100y 25,00

aci 4100z 13,75

* aci 4100x tiene 2 pedidos pero uno sin precio por lo que no cuenta


Ayuda ejercicios unidad 7: Actualización de datos (I)

1. Añadir a la oficina 40 otro empleado, Luis Valverde, con número de empleado 436, con los mismos datos que el anterior pero su jefe será el director de la oficina 40 (no sabemos qué número tiene).

INSERT INTO empleados (numemp, nombre, titulo, contrato, ventas, cuota, oficina, jefe)

SELECT 436, 'Luis Valverde','Vendedor', GETDATE(), 0, 1200.45, 40, dir

FROM oficinas WHERE oficina = 40;

2. Pasar los pedidos de octubre 1989 a diciembre 2008. (3 filas afectadas)

UPDATE pedidos SET fechapedido=DATEADD(month,230,fechapedido)

WHERE year(fechapedido)=1989 and month(fechapedido)=10;

3. Queremos actualizar el importe de los pedidos del mes actual con el precio almacenado en la tabla productos.

SELECT codigo, numpedido,fechapedido,cant, importe,importe/cant AS [precio pedido],precio


FROM pedidos inner join productos ON fab=idfab and producto = idproducto

WHERE YEAR(fechapedido)=YEAR(GETDATE()) and MONTH(fechapedido)=MONTH(GETDATE())

Actualizar después la tabla de pedidos cambiando los importes para que el precio unitario corresponda con el precio del producto. Los pedidos de los productos que no tienen precio se quedarán como estaban. (3 filas afectadas)

UPDATE pedidos SET importe=cant*precio

FROM pedidos inner join productos ON fab=idfab and producto = idproducto

WHERE year(fechapedido)=2008 and month(fechapedido)=12 AND precio IS NOT NULL;

4. Se ven algunos productos que no tienen precio, ahora vamos a actualizar el precio de estos productos con el precio medio utilizado en los pedidos donde aparecen. La primera SELECT saca los productos que queremos actualizar con los pedidos correspondientes.

SELECT idfab, idproducto, precio, codigo, numpedido,fechapedido,importe, importe/cant

FROM productos left join pedidos on idfab=fab AND idproducto=producto

WHERE precio IS NULL;

UPDATE productos SET precio = (SELECT ROUND(AVG(importe/cant),2) FROM pedidos WHERE fab=idfab AND producto=idproducto)

WHERE precio IS NULL

Prueba evaluativa unidad 7: Actualización de datos


1. Podemos insertar datos en una tabla nueva mediante la cláusula INSERT INTO.

a) Verdadero.

b) Falso.

2. La cláusula SELECT ... INTO requiere especificar una definición para la nueva tabla.

a) Verdadero.

b) Falso.

3. En las consultas INSERT ...

a) Es obligatorio utilizar la palabra INTO de la siguiente forma: INSERT INTO.

b) No podemos utilizar vistas como origen de datos.

c) No se puede introducir valores nulos (NULL).


4. El peligro de insertar datos con la cláusula INSERT INTO es que no podemos saber si estamos infringiendo una regla de integridad.

a) Verdadero.

b) Falso.

5. ¿Se puede actualizar (UPDATE) una columna de identidad?

a) Sí, siempre que le asignemos un valor que no exista.

b) Sí, siempre que asignemos el valor que debería tener: si estamos actualizando el registro con identidad 7 y el valor máximo de la tabla es 30, deberíamos asignar el 31.

c) No, porque este campo lo gestiona el sistema y daría error.


6. Para indicar qué filas queremos modificar, podemos aplicar la cláusula...

a) WHERE, para aplicar una condición de filtro y modificar las que coincidan con el criterio.

b) TOP, para indicar cuántas filas queremos modificar, aleatoriamente.



7. Si una consulta UPDATE falla porque infringe una regla de integridad o hay un problema aritmético...

a) El proceso de actualización continua, y se crea un listado con las filas que han sufrido algún error.

b) El proceso de actualización continua y se añaden valores nulos a las columnas que generan errores.

c) El proceso hace dos barridos: en el primero comprueba si podrá ejecutar el UPDATE completo y si hay errores te pregunta si deseas continuar, a pesar de que es posible que la integridad de los datos se comprometa o que se pierda información.

d) El proceso se interrumpe y muestra un error.

8. DELETE permite eliminar...

a) Una fila por cada consulta.

b) Una fila o más.

c) Un campo en concreto de una fila en particular.

d) Una columna completa.

9. Indica cuál de las siguientes consultas no borrará todos los registros de tabla1:

a) DELETE * FROM tabla1;

b) DELETE tabla1;

c) DELETE FROM tabla1 ;

d) DELETE TOP (100) PERCENT FROM tabla1;

10. Para borrar todos los datos de una tabla sin restricciones, lo más rápido es:

a) Utilizar DELETE.

b) Utilizar UPDATE y actualizar todos los registros a NULL.

c) Utilizar TRUNCATE.

Unidad 8. Ejercicio paso a paso: ALTER DATABASE

Objetivo

Modificar las características de la base de datos, más concretamente el tipo de intercalación.


Cambiar la intercalación de la base de datos Gestion para que el sistema sea sensible a las mayúsculas y minúsculas.

ALTER DATABASE Gestion COLLATE Modern_Spanish_CS_AI

GO

Para volver a dejarla como estaba.

ALTER DATABASE Gestion COLLATE Modern_Spanish_CI_AI

GO

Ejercicios unidad 8: El DDL, Lenguaje de Definición de Datos

1. Crea una nueva base de datos Gestion2, los archivos físicos se llamarán también Gestion2 y se creará en la misma carpeta que la base de datos Gestion.

2. En Gestion2, crea las mismas tablas que tenemos en Gestion con las mismas relaciones entre ellas. Este ejercicio se resuelve con varias sentencias.

3. La tabla Productos de Gestion2 consideramos que sufre pocas operaciones de nuevos registros o eliminación de registros y el único campo que sufre actualizaciones frecuentes es el campo existencias; por otro lado es frecuente consultar los productos por el campo descripcion (aunque varios productos puedan tener la misma descripción). ¿Se podría mejorar la base de datos? Si es que sí indica de qué forma y escribe la instrucción correspondiente; en cualquier caso justifica la respuesta.

4. ¿Qué se puede hacer para que no puedan existir en la tabla de clientes dos clientes con el mismo nombre?

5. En nuestro sistema vamos a efectuar muchas consultas recuperando a la vez los pedidos y los productos asociados a cada uno de ellos, por eso sería bueno…. Completa la frase y escribe la sentencia SQL correspondiente.

6. Elimina la tabla clientes dejando en pedidos el campo cliente a NULL. Este ejercicio se resuelve con varias sentencias y se puede resolver de varias maneras. Resuélvelo de distintas formas.

7. Ahora queremos permitir que dos clientes tengan el mismo nombre.

8. Elimina la base de datos Gestion2, así podrás volver a hacer los ejercicios.


Ayuda ejercicios unidad 8: El DDL, Lenguaje de Definición de Datos

1. Crea una nueva base de datos Gestion2, los archivos físicos se llamarán también Gestion2 y se creará en la misma carpeta que la base de datos Gestion.

CREATE DATABASE Gestion2; -- La crea en la carpeta por defecto de SQL Server.

CREATE DATABASE Gestion2 ON (NAME='Gestion2' , FILENAME='F:\SQL\Gestion2.mdf')

LOG ON (NAME='Gestion2_log' , FILENAME='F:\SQL\Gestion2.ldf')GO

2. En Gestion2, crea las mismas tablas que tenemos en Gestion con las mismas relaciones entre ellas. Este ejercicio se resuelve con varias sentencias.

USE Gestion2

CREATE TABLE dbo.empleados(

numemp INT NOT NULL CONSTRAINT pk_empleados PRIMARY KEY CLUSTERED,

nombre CHAR(20) NULL,

edad INT NULL,

oficina INT NULL,

titulo CHAR(20) NULL,

contrato datetime NULL,

jefe INT NULL CONSTRAINT FK_empleados_jefe REFERENCES dbo.empleados,

cuota MONEY NULL,

ventas MONEY NULL

)

CREATE TABLE dbo.oficinas(

oficina INT NOT NULL,


ciudad CHAR(20) NULL,

region CHAR(20) NULL,

dir INT NULL,

objetivo MONEY NULL,

ventas MONEY NULL CONSTRAINT d1 DEFAULT (0),

CONSTRAINT PK_oficinas PRIMARY KEY (oficina),

CONSTRAINT FK_oficinas_dir FOREIGN KEY (dir) REFERENCES empleados

)

ALTER TABLE dbo.empleados WITH CHECK ADD CONSTRAINT FK_empleados_oficina

FOREIGN KEY(oficina) REFERENCES dbo.oficinas (oficina)

CREATE TABLE dbo.clientes(

numclie INT NOT NULL,


repclie INT NULL CONSTRAINT FK_clientes_repclie FOREIGN KEY REFERENCES dbo.empleados,

limitecredito INT NULL, CONSTRAINT PK_clientes PRIMARY KEY (numclie)

)

CREATE TABLE dbo.productos(

idfab CHAR(5) NOT NULL,

idproducto CHAR(10) NOT NULL,

descripcion CHAR(20) NULL,

precio MONEY NULL,

existencias INT NULL,

CONSTRAINT PK_productos PRIMARY KEY (idfab,idproducto)

)

CREATE TABLE dbo.pedidos(

codigo INT IDENTITY NOT NULL,

numpedido DEC(10,0) NOT NULL,

fechapedido DATETIME NOT NULL,

clie INT NOT NULL,

rep INT NOT NULL,

fab CHAR(5) NOT NULL,

producto CHAR(10) NOT NULL,

cant SMALLINT NULL,

importe MONEY NULL,

CONSTRAINT PK_pedidos PRIMARY KEY (codigo),

CONSTRAINT FK_pedidos_rep FOREIGN KEY (rep) REFERENCES empleados,

CONSTRAINT FK_pedidos_clie FOREIGN KEY (clie) REFERENCES clientes,

CONSTRAINT FK_pedidos_productos FOREIGN KEY (fab,producto) REFERENCES productos)

3. La tabla Productos de Gestion2 consideramos que sufre pocas operaciones de nuevos registros o eliminación de registros y el único campo que sufre actualizaciones frecuentes es el campo existencias; por otro lado es frecuente consultar los productos por el campo descripcion (aunque varios productos puedan tener la misma descripción). ¿Se podría mejorar la base de datos? Si es que sí indica de qué forma y escribe la instrucción correspondiente; en cualquier caso justifica la respuesta.

CREATE INDEX IX_descripcion ON Productos (descripcion);

4. ¿Qué se puede hacer para que no puedan existir en la tabla de clientes dos clientes con el mismo nombre?

CREATE UNIQUE INDEX IX_nombre ON Clientes (nombre)

-- o bien

ALTER TABLE Clientes ADD CONSTRAINT IX_nombre UNIQUE (nombre);

5. En nuestro sistema vamos a efectuar muchas consultas recuperando a la vez los pedidos y los productos asociados a cada uno de ellos, por eso sería bueno…. Completa la frase y escribe la sentencia SQL correspondiente.

CREATE VIEW pedidos_productos AS (SELECT * FROM pedidos INNER JOIN productos ON fab=idfab AND producto=idproducto);

6. Elimina la tabla clientes dejando en pedidos el campo cliente a NULL. Este ejercicio se resuelve con varias sentencias y se puede resolver de varias maneras. Resuélvelo de distintas formas.

ALTER TABLE pedidos ALTER COLUMN clie INT NULL; -- Esto de todas formas

-- Opcion 1

ALTER TABLE pedidos DROP CONSTRAINT FK_pedidos_clie;

ALTER TABLE pedidos ADD CONSTRAINT FK_pedidos_clie FOREIGN KEY (clie) REFERENCES Clientes ON DELETE SET NULL;

DELETE Clientes;

SELECT * FROM pedidos -- Para comprobar el resultado;

-- Opcion 2

UPDATE pedidos SET clie = NULL;

DELETE Clientes

SELECT * FROM pedidos;

7. Ahora queremos permitir que dos clientes tengan el mismo nombre.

DROP INDEX IX_nombre ON Clientes;

--o bien (según se haya resuelto el apartado 5

ALTER TABLE Clientes DROP CONSTRAINT IX_nombre;

8. Elimina la base de datos Gestion2, así podrás volver a hacer los ejercicios.

USE Gestion

DROP DATABASE Gestion2;

GO

Prueba evaluativa unidad 8: El DDL, Lenguaje de Definición de Datos


1. El DDL normalmente es una función propia de:

a) El programador.

b) El analista.

c) El usuario.

d) El administrador.

2. Las sentencias propias del DDL son:

a) SELECT, INSERT, DELETE y UPDATE.

b) ORDER BY y GROUP BY.

c) CREATE, DROP y ALTER.


3. Podemos crear una base de datos mediante código SQL, sin necesidad de utilizar los menús de SSMS.

a) Verdadero.

b) Falso.

4. Para eliminar una base de datos, utilizamos:

a) DELETE DATABASE.

b) DROP DATABASE.

c) REMOVE DATABASE.


5. Para establecer una clave primaria formada por varias columnas, al crear una tabla.

a) Indicamos PRIMARY KEY en cada una de las columnas.

b) Indicamos PRIMARY KEY y a continuación, entre paréntesis, las columnas que la forman.

c) Debemos hacerlo con los menús de SSMS, porque no se puede a nivel de SQL simplemente.


6. Una vez hemos creado una tabla ya no podremos incluir más columnas.

a) Deberemos crear una nueva tabla que incluya todas las columnas y volcar los datos de una a otra. Luego, borramos la original y modificamos el nombre de la nueva para que sea el mismo.

b) A menos que se trate de una FOREIGN KEY.

c) A menos que se trate de una columna IDENTITY o calculada, porque es el propio sistema el que inserta los valores.

d) La afirmación es falsa, porque podemos realizar un ALTER TABLE e incluir la cláusula ADD para añadir nuevas columnas.

7. Una vista:

a) Es el resultado de una consulta que se guarda como si fuese una tabla más y cuyo contenido siempre está actualizado con los datos de las tablas origen.

b) Se escribe como una SELECT normal, pero indicamos CREATE VIEW nombre AS antes, para indicar que queremos que se cree una vista del resultado de esa consulta.



8. Sólo se puede crear vistas con una select que referencia a más de una tabla, porque de otro modo no tendría sentido y sería información redundante.

a) Verdadero.

b) Falso.

9. Si la tabla de pedidos tiene muchos registros, ¿es recomendable crear un índice en ella?

a) Probablemente no, porque es una tabla que normalmente se actualiza mucho y actualizar los índices sería una carga.

b) No, porque los índices están especialmente pensados para tablas con pocos registros.

c) Sí.

d) Ninguna es cierta.

10. ¿Y si la tabla no se actualizara apenas?

a) No, porque no importa cuánto se actualice, el problema es que tiene muchos

registros y el índice ocuparía mucho.

b) Sí, entonces sí que podría ser recomendable.

Unidad 9. Ejercicio paso a paso: Procedimientos

Objetivo

Realizar procedimientos en que utilizaremos estructuras de control de flujo como BEGIN... END, IF y GO. Además, se hará un repaso a lo que ya hemos aprendido a lo largo del curso, puesto que los procedimientos, al fin y al cabo sirven para ejecutar sentencias SQL. Repasaremos cómo crear tablas e insertar datos en ellas.


Utilizar las sentencias desarrolladas en un ejercicio propuesto del tema anterior para crear la base de datos Gestion2 a partir de Gestion8 y crear otra base datos Gestion10 sobre la que realizarás todos los ejercicios de esta unidad.

Para ello vamos a definir tres procedimientos, CreaBase que permita crear una base de datos con el nombre que le indiquemos en la llamada, otro, BorrarBase que borre una determinada base de datos, y por último RellenaBase para rellenar con las tablas de Gestion8 la base de datos que indiquemos en la llamada.Los procedimientos se crean en Gestion8.Como no se puede utilizar una variable en un CREATE DATABASE, utilizamos la función EXEC('cadena SQL') que ejecuta cualquier cadena SQL.

PRINT 'Empieza el ejercicio de procedimientos' -- Para visualizar un mensaje que indique lo que realiza esta sentencia.

USE Gestion8 -- Para crear los proc. en Gestion8

if object_id('BorraBase') IS NOT NULL DROP PROC BorraBase

GO -- Necesario igual que el siguiente GO para delimitar el CREATE PROC

CREATE PROCEDURE BorraBase @base sysname

AS

BEGIN TRY -- Para que si no existe la base de datos no mande ningún mensaje

EXEC('DROP DATABASE ' + @base) -- Si en @base se pasa el valor Gestion10, se ejecutará: DROP DATABASE Gestion10

END TRY

BEGIN CATCH -- Si se produce un error no hará nada

END CATCH -- no mandará ningún mensaje de error y seguirá

GO

if object_id('CreaBase') IS NOT NULL DROP PROC CreaBase

GO

CREATE PROCEDURE CreaBase @base sysname

AS

BEGIN TRY -- Para que si ya existe la base de datos la no mande ningún mensaje

EXEC('CREATE DATABASE '+ @base) -- Creamos la bd con los parámetros por defecto para simplificar

END TRY

BEGIN CATCH

END CATCH -- Si se produce un error no hará nada (por ej. si ya existe la base no mandará ningún mensaje de error y seguirá

GO

if object_id('RellenaGestion') IS NOT NULL DROP PROC RellenaGestion

GO

CREATE PROC RellenaGestion @base sysname

AS

BEGIN

EXEC('CREATE TABLE '+ @base+'.dbo.empleados(

numemp INT NOT NULL PRIMARY KEY CLUSTERED,


edad INT NULL,

oficina INT NULL,

titulo CHAR(20) NULL,

contrato datetime NULL,

jefe INT NULL,

cuota MONEY NULL,

ventas MONEY NULL

)

CREATE TABLE '+@base+'.dbo.oficinas(

oficina INT NOT NULL,

ciudad CHAR(20) NULL,

region CHAR(20) NULL,

dir INT NULL,

objetivo MONEY NULL,

ventas MONEY NULL CONSTRAINT d1 DEFAULT (0),

CONSTRAINT PK_oficinas PRIMARY KEY (oficina),

)

CREATE TABLE '+@base+'.dbo.clientes(

numclie INT NOT NULL,


repclie INT NULL,

limitecredito INT NULL,

CONSTRAINT PK_clientes PRIMARY KEY (numclie)

)

CREATE TABLE '+@base+'.dbo.productos(

idfab CHAR(5) NOT NULL,

idproducto CHAR(10) NOT NULL,

descripcion CHAR(20) NULL,

precio MONEY NULL,

existencias INT NULL,

CONSTRAINT PK_productos PRIMARY KEY (idfab,idproducto)

)

CREATE TABLE '+@base+'.dbo.pedidos(

codigo INT IDENTITY NOT NULL,

numpedido DEC(10,0) NOT NULL,

fechapedido DATETIME NOT NULL,

clie INT NOT NULL,

rep INT NOT NULL,

fab CHAR(5) NOT NULL,

producto CHAR(10) NOT NULL,

cant SMALLINT NULL,

importe MONEY NULL,

CONSTRAINT PK_pedidos PRIMARY KEY (codigo),

)

INSERT INTO '+@base+'.dbo.Productos SELECT * FROM Gestion8.dbo.Productos;

INSERT INTO '+@base+'.dbo.Empleados SELECT * FROM Gestion8.dbo.Empleados;

INSERT INTO '+@base+'.dbo.Clientes SELECT * FROM Gestion8.dbo.Clientes;

INSERT INTO '+@base+'.dbo.Oficinas SELECT * FROM Gestion8.dbo.Oficinas;

SET IDENTITY_INSERT '+@base+'.dbo.Pedidos ON;

INSERT INTO '+@base+'.dbo.Pedidos (codigo,numpedido,fechapedido,clie,rep,fab,producto,cant,importe)

SELECT * FROM Gestion8.dbo.Pedidos;

SET IDENTITY_INSERT '+@base+'.dbo.Pedidos OFF;

ALTER TABLE '+@base+'.dbo.Empleados

ADD CONSTRAINT FK_empleados_jefe FOREIGN KEY (jefe) REFERENCES empleados,

CONSTRAINT FK_empleados_oficinas FOREIGN KEY (oficina) REFERENCES oficinas;

ALTER TABLE '+@base+'.dbo.Oficinas

ADD CONSTRAINT FK_oficinas_dir FOREIGN KEY (dir) REFERENCES empleados

ALTER TABLE '+@base+'.dbo.Clientes

ADD CONSTRAINT FK_clientes_repclie FOREIGN KEY (repclie) REFERENCES empleados;

ALTER TABLE '+@base+'.dbo.Pedidos

ADD CONSTRAINT FK_pedidos_rep FOREIGN KEY (rep) REFERENCES empleados,

CONSTRAINT FK_pedidos_clie FOREIGN KEY (clie) REFERENCES clientes,

CONSTRAINT FK_pedidos_productos FOREIGN KEY (fab,producto) REFERENCES productos;')

END

GO

Empezar:

USE Gestion8

EXEC BorraBase 'Gestion10'

GO

EXEC CreaBase 'Gestion10'

GO

EXEC RellenaGestion 'Gestion10'

Ejercicios unidad 9: Programación en TRANSACT SQL

Nota: Para realizar los ejercicios, utiliza Gestion10 que has creado en los ejercicios paso a paso de este tema.

1. Añadir a la tabla de productos un campo StockMinimo, y rellenarlo de tal forma que el stock mínimo valorado de cada artículo sea de 100€. El stock mínimo valorado es el resultado de multiplicar el stock mínimo por el precio del producto. No hace falta incluir estas sentencias en un procedimiento ya que sólo nos sirven para preparar la tabla productos pero redactarlas en Transact-SQL para repasar temas anteriores. Seguidamente, escribir un procedimiento que se pueda ejecutar en cualquier momento y que actualice una lista de productos bajo stock. Esta lista se guarda en una tabla ProductosAPedir y contiene el código completo del producto, su descripción, existencias y cantidad a pedir (para que el producto supere en 5 unidades su stock mínimo). Si un producto bajo mínimo (cuyas existencias no llegan al stock mínimo) no está en esa tabla, insertarlo, si está actualizar el campo CantidadAPedir, y si ya no está bajo mínimo y está en la tabla, eliminar el registro de ProductosAPedir.

2. Obtener un listado de las oficinas de una determinada región con ventas superiores a un determinado importe.

3. Crea un nuevo procedimiento parecido al anterior pero que nos devuelva también el número de oficinas recuperadas. Utilizar un parámetro de salida.

4. Crea un nuevo procedimiento parecido al anterior pero que nos devuelva también el número de oficinas recuperadas. Sin utilizar parámetros de salida.

5. Crea un procedimiento que muestre los n productos más caros, n es un valor que se indicará en la llamada.

6. Crea otro procedimiento que muestre los n productos más caros con empates y nos devuelva cuántos hay.

7. No dejar eliminar productos que tengan existencias.

8. Hacer que no se pueda insertar un pedido si no hay suficiente stock.

9. Crea un procedimiento que impida eliminar varias oficinas en una sóla operación DELETE.


Ayuda ejercicios unidad 9: Programación en TRANSACT SQL (I)

Consejo: Es recomedable copiar el código en la consulta del SSMS y tratar de entenderlo desde ahí, porque son consultas extensas y se ven más claras con las palabras coloreadas.

1. Añadir a la tabla de productos un campo StockMinimo, y rellenarlo de tal forma que el stock mínimo valorado de cada artículo sea de 100€. El stock mínimo valorado es el resultado de multiplicar el stock mínimo por el precio del producto. No hace falta incluir estas sentencias en un procedimiento ya que sólo nos sirven para preparar la tabla productos pero redactarlas en Transact-SQL para repasar temas anteriores.


PRINT 'Empieza el ejercicio 1'

USE Gestion10;

Alter TABLE Productos ADD StockMinimo INTEGER DEFAULT 0;

GO

UPDATE Productos SET StockMinimo= 100/(CASE WHEN Precio IS NULL OR Precio=0 THEN 100 ELSE Precio END);

-- Utilizamos CASE para que si el producto no tiene precio o un precio igual a cero que asuma StockMinimo 1.

Seguidamente, escribir un procedimiento que se pueda ejecutar en cualquier momento y que actualice una lista de productos bajo stock. Esta lista se guarda en una tabla ProductosAPedir y contiene el código completo del producto, su descripción, existencias y cantidad a pedir (para que el producto supere en 5 unidades su stock mínimo). Si un producto bajo mínimo (cuyas existencias no llegan al stock mínimo) no está en esa tabla, insertarlo, si está actualizar el campo CantidadAPedir, y si ya no está bajo mínimo y está en la tabla, eliminar el registro de ProductosAPedir.

PRINT 'Empieza el ejercicio 1 bis'

USE Gestion10

IF OBJECT_ID('ActualizaProductosAPedir','P') IS NOT NULL DROP PROC ActualizaProductosAPedir

GO

CREATE PROC ActualizaProductosAPedir

AS

BEGIN

IF OBJECT_ID('ProductosAPedir','U') IS NULL

CREATE TABLE ProductosAPedir (

Afab CHAR(5),

Aproducto CHAR(10),

Adescripcion CHAR(20),

Aexistencias INT,

Apedir SMALLINT,

PRIMARY KEY (Afab,Aproducto)

)

DELETE FROM ProductosAPedir WHERE EXISTS (SELECT * FROM Productos WHERE idfab=Afab AND idproducto=Aproducto AND existencias >= StockMinimo)

--Borramos los ProductosAPedir de los productos que rebasan el stock mínimo, si rebasa el stock el producto ya no tiene que estar en la tabla

UPDATE ProductosAPedir SET Apedir=StockMinimo+5-existencias

FROM ProductosAPedir INNER JOIN Productos ON idfab=Afab AND idproducto=Aproducto

WHERE existencias < Stockminimo

-- Actualizamos los ProductosAPedir de productos que no llegan al stock mínimo

INSERT INTO ProductosAPedir

SELECT Idfab,Idproducto,Descripcion,existencias,StockMinimo+5-existencias

FROM Productos

WHERE existencias < Stockminimo

AND NOT EXISTS (SELECT * FROM ProductosAPedir WHERE idfab=Afab AND idproducto=Aproducto);

-- Insertamos nuevos ProductosAPedir de productos que no llegan al stock mínimo y que no están ya en ProductosAPedir (para no repetirlos).

END;

GO

USE Gestion10

SELECT * FROM Productos WHERE existencias < Stockminimo

EXEC ActualizaProductosAPedir

SELECT * FROM ProductosAPedir

2. Obtener un listado de las oficinas de una determinada región con ventas superiores a un determinado importe.


USE Gestion10

IF OBJECT_ID('Listado_Oficinas1','P') IS NOT NULL DROP PROCEDURE Listado_Oficinas1

GO

CREATE PROCEDURE Listado_Oficinas1 @region CHAR(20),@ventas MONEY

AS

SELECT * FROM oficinas WHERE region= @region AND ventas > @ventas

GO

EXEC Listado_Oficinas1 Este, 1000

EXEC Listado_Oficinas1 Este, 100000

EXEC Listado_Oficinas1 Norte, 0

EXEC Listado_Oficinas1 Norte, 10000

3. Crear un nuevo procedimiento parecido al anterior pero que nos devuelva también el número de oficinas recuperadas. Utilizar un parámetro de salida.


USE Gestion10

IF OBJECT_ID('Listado_Oficinas2','P') IS NOT NULL DROP PROCEDURE Listado_Oficinas2

GO

CREATE PROCEDURE Listado_Oficinas2 @region CHAR(20),@ventas MONEY, @cuantas INT OUTPUT

AS

SELECT * FROM oficinas WHERE region= @region AND ventas > @ventas

SET @cuantas=(SELECT COUNT(*) FROM oficinas WHERE region= @region AND ventas > @ventas)

GO

DECLARE @resultado INT

EXEC Listado_Oficinas2 Este, 1000 ,@resultado OUTPUT

SELECT @resultado

EXEC Listado_Oficinas2 Este, 100000 ,@resultado OUTPUT

PRINT @resultado

EXEC Listado_Oficinas2 Norte, 0 ,@resultado OUTPUT

PRINT @resultado

EXEC Listado_Oficinas2 Norte, 10000 ,@resultado OUTPUT

PRINT @resultado

Prueba evaluativa unidad 9: Programación en TRANSACT SQL


1. Existen dos tipos de procedimientos: los del sistema (que se encuentran en la base master) y los del usuario (los que creamos).

a) Verdadero.

b) Falso.

2. El ámbito de un procedimiento puede ser local o global.

a) Verdadero.

b) Falso.

3. Para eliminar un procedimiento utilizaremos:

a) DROP PROC.

b) DROP PROCEDURE.



4. Los procedimientos pueden contener:

a) Parámetros de entrada.

b) Parámetros de salida.



5. Un procedimiento puede contener bucles, condiciones y control de excepciones.

a) Verdadero.

b) Falso.

6. Cuál de las siguientes cláusulas no es un tipo de desencadente:

a) FOR.

b) WAITFOR.

c) AFTER.

d) INSTEAD OF.

7. Para evitar que se borren registros en una tabla al lanzar una sentencia DELETE, utilizamos:

a) FOR.

b) WAITFOR.

c) AFTER.

d) INSTEAD OF.

8. Cuando lanzamos un trigger sobre una sentencia UPDATE:

a) Deberemos trabajar con los datos de la tabla Updated.

b) Deberemos trabajar con los datos de las tablas Deleted e Inserted.



9. Cuál de los triggers siguientes sería posible realizar:

a) Sobre una cláusula ALTER DATABASE.

b) Sobre una cláusula CREATE INDEX.

c) Sobre un ALTER TABLE que pretenda insertar una columna (ADD).

d) Sobre un ALTER TABLE que pretenda cambiar el nombre de una columna.

10. En un trigger, DROP y DISABLE se distinguen en que:

a) DROP lo elimina permanentemente y DISABLE lo desactiva temporalmente.

b) DISABLE lo elimina permanentemente y DROP lo desactiva temporalmente

Ejercicios y Evaluaciones.docx

Documents

Transcript of Ejercicios y Evaluaciones.docx