View
62
Download
8
Category
Preview:
DESCRIPTION
uni
Citation preview
1.3. Entrada al SQL Server Management Studio
Aunque trabajemos en modo local, la entrada a la herramienta es la
misma. Para empezar entramos a travs del acceso directo o a travs de Inicio, Programas, Microsoft SQL Server 2005, SQL Server Management Studio.
Lo primero que deberemos hacer es establecer la conexin con el servidor:
Seleccionamos el nombre del servidor y pulsamos el botn Conectar. Se abrir la ventana inicial del SQL Server Management Studio (en adelante SSMS):
En la parte izquierda tenemos abierto el panel Explorador de Objetos en el que aparece debajo del nombre del servidor con el que estamos conectados una serie de carpetas y objetos que forman parte del servidor. En el panel de la derecha se muestra la zona de trabajo, que vara segn lo que tengamos seleccionado en el Explorador de objetos, en este caso vemos el contenido de la carpeta que representa el servidor ord01.
En la parte superior tenemos el men de opciones y la barra de herramientas Estndar.
Con las siguientes opciones:
1. Nueva consulta 6. Consulta de SQL Server Mobile
11. Resumen
2. Consulta de motor de Base de datos
7. Abrir archivo 12. Explorador de Objetos
3. Consulta MDX de Analysis Services
8. Guardar 13. Explorador de Plantillas
4. Consulta DMX de Analysis Services
9. Guardar todo 14. Ventana de Propiedades
5. Consulta MXLA de Analysis Services
10. Servidores registrados
En caso de que utilices la versin Express, es posible que no dispongas de algunos de stos botones.
Unidad 1. El entorno grfico SSMS (II)
1.4. Estructura interna de una base de datos
Antes de empezar tenemos que tener claro cmo se organiza la informacin en una base de datos SQL Server 2005.
Las bases de datos de SQL Server 2005 utilizan tres tipos de archivos:
Archivos de datos principales En una base de datos SQLServer los datos se pueden repartir en varios archivos para mejorar el rendimiento de la base de datos. El archivo de datos principal es el punto de partida de la base de datos y apunta a los otros archivos de datos de la base de datos. Cada base de datos tiene obligatoriamente un archivo de datos principal. La extensin recomendada para los nombres de archivos de datos principales es .mdf.
Archivos de datos secundarios Los archivos de datos secundarios son todos los archivos de datos menos el archivo de datos principal. Puede que algunas bases de datos no tengan archivos de datos secundarios, mientras que otras pueden tener varios archivos de datos secundarios. La extensin de nombre de archivo recomendada para los archivos de datos secundarios es .ndf. Adems los archivos de datos se pueden agrupar en grupos de archivos. Para cada base de datos pueden especificarse hasta 32.767 archivos y 32.767 grupos de archivos.
Archivos de registro Los archivos de registro (archivos de log) almacenan toda la informacin de registro que se utiliza para recuperar la base de datos, el tambin denominado registro de transacciones. Como mnimo, tiene que haber un archivo de registro por cada base de datos, aunque puede haber varios. La extensin recomendada para los nombres de archivos de registro es .ldf.
SQL Server 2005 no exige las extensiones de nombre de archivo .mdf, .ndf y .ldf, pero estas extensiones ayudan a identificar las distintas clases de archivos y su uso.
Cada base de datos tiene al menos 2 archivos (un archivo de datos principal y un archivo de registro) y opcionalmente un grupo de archivos.
Los archivos de datos y de registro de SQL Server se pueden colocar en sistemas de archivos FAT o NTFS. Se recomienda utilizar NTFS por los aspectos de seguridad que ofrece. No se pueden colocar grupos de archivos de datos de lectura y escritura, y archivos de registro, en un sistema de archivos NTFS comprimido. Slo las bases de datos de slo lectura y los grupos de archivos secundarios de slo lectura se pueden colocar en un sistema de archivos NTFS comprimido.
1.5. Crear una base de datos en SSMS
En el Explorador de objetos, si desplegamos la carpeta Bases de datos nos aparecen Bases de datos del sistema y las bases de datos de usuario despus de la carpeta Instantneas...
Despus de la instalacin, en la carpeta Bases de datos del sistema se habr creado una especial denominada master se utiliza como base de datos de usuario por defecto. Las dems bases de datos forman tambin parte del diccionario de datos y las utiliza el sistema para llevar a cabo su gestin.
Las bases de datos de los usuarios se deben crear preferentemente fuera de la carpeta Bases de datos del sistema.
Unidad 1. El entorno grfico SSMS (III)
Para crear una nueva base de datos de usuario nos posicionamos sobre la carpeta Bases de datos y con el botn derecho del ratn desplegamos el men contextual del que elegimos la opcin Nueva base de datos
Se abre a continuacin el cuadro de dilogo donde definiremos la base de datos que queremos crear:
Lo mnimo a introducir ser el campo Nombre de la base de datos, ste es el nombre de la base de datos lgica, la base de datos a la que nos referiremos dentro del SSMS, a nivel conceptual (en la imagen Mibase).
Esta base de datos est asociada a dos archivos fsicos, en la parte inferior aparecen esos archivos. Para facilitarnos la tarea, al teclear el nombre de la bd lgica, se rellenan automticamente los nombres de los archivos fsicos, el de datos con el mismo nombre y el del archivo de registro con el mismo nombre seguido de _log. Estos nombres son los nombres que se asumen por defecto pero los podemos cambiar, posicionando el cursor en el nombre y cambindolo.
Para cada archivo fsico podemos definir una serie de parmetros como el tipo de archivo (si es de datos o de transacciones Registro) y su ocupacin inicial (Tamao inicial).
Si no indicamos ninguna ubicacin podemos ver que los guarda en la carpeta del SQL Server/MSSQL.n/MSSQL/DATA. n representa un nmero que puede variar de una instalacin a otra.
Estos son los archivos mnimos en los que se almacenar la base de datos, pero como ya vimos anteriormente se puede almacenar en ms archivos, los tenemos que definir todos en esta ventana a continuacin de los dos obligatorios.
Para aadir ms archivos fsicos disponemos del botn Agregar. Al pulsar el botn Agregar se crea una nueva fila en la tabla de archivos fsicos donde deberemos escribir el nombre del archivo, su tipo (desplegando la lista podemos elegir entre de datos o de registro) y dems parmetros.
Al agregar un nuevo archivo se activa el botn Quitar, siempre que estemos posicionados encima de un archivo secundario para poder as eliminarlo si lo queremos.
No podemos eliminar ni el de datos primario, ni el de registro inicial.
Unidad 1. El entorno grfico SSMS (IV)
Si nos fijamos en la zona de la izquierda, vemos que nos encontramos en la pestaa General, podemos cambiar otros parmetros de la base de datos pulsando en Grupos de archivos o en Opciones:
Al final pulsamos en Aceptar y se crear la base de datos.
Aparecer dentro de la carpeta Bases de datos. Si no se ve pulsa en el
icono Actualizar .
Desde el Explorador de Windows podemos ver que en la carpeta indicada se han creado los archivos fsicos con los nombres que le hemos indicado.
Unidad 1. El entorno grfico SSMS (V)
1.6. Adjuntar una base de datos
En ocasiones no necesitaremos crear la base de datos desde cero, porque sta ya estar creada. ste es el caso de los ejercicios del curso. Para realizarlos, debers adjuntar una base de datos ya existente a tu servidor. Para ello, lo que tenemos que hacer es pegar los archivos en la ubicacin que queramos, y luego indicar al SQL Server que vamos a utilizar esta base de datos, de la siguiente manera: En el Explorador de objetos, sobre la carpeta Bases de datos desplegar el men contextual y elegir Adjuntar...
En la siguiente ventana elegimos la base de datos:
Pulsando en Agregar indicamos el archivo de datos primario en su ubicacin y automticamente se adjuntar la base de datos lgica asociada a este archivo.
Finalmente pulsamos en Aceptar y aparece la base de datos en nuestro servidor.
La opcin Adjuntar slo se utiliza la primera vez, cuando todava no tenemos la base de datos en el disco.
Realiza el siguiente Ejercicio Adjuntar base de datos. En l adjuntars las bases de datos que vas a utilizar en los ejercicios que se plantearn ms adelante.
1.7. Conectar y Desconectar la base de datos
Una vez hemos creado la base de datos o la hemos adjuntado a nuestro servidor, nos daremos cuenta de que no podremos manipular los archivos de la base desde fuera del gestor SSMS, por ejemplo, desde el Explorador de Windows. Es decir, no podremos copiar, cortar, mover o eliminar los archivos fuente mdf, ndf y ldf. Si lo intentamos se mostrar un aviso de que la base de datos est en uso.
sto es as porque SQL Server sigue en marcha, a pesar de que se cierre el gestor. Ten en cuenta que el servidor de base de datos normalmente se crea para que sirva informacin a diferentes programas, por eso sera absurdo que dejara de funcionar cuando cerramos el programa gestor, que slo se utiliza para realizar modificaciones sobre la base.
Para poder realizar acciones sobre la base de datos, sta debe estar desconectada. Para ello, desde el SSMS, desplegamos el men contextual de la base de datos que nos interese manipular y seleccionaremos la opcin Poner fuera de conexin:
Aparecer un smbolo a la izquierda de la base de datos indicndonos que la base de datos est desconectada, a partir de este momento Windows nos dejar manipular los archivos.
Para volver a conectar la base de datos y seguir trabajando con ella, accederemos al mismo men contextual pero elegiremos la opcin Poner en conexin:
El caso ms inmediato en el que puedes necesitar conectar y desconectar la base de datos es copiar a un pendrive los archivos de las bases que utilizars en los ejercicios para poder trabajar en diferentes ordenadores con ellos. Para aprender cmo hacerlo, visita el siguiente Ejercicio Trasladar una base de datos a otro equipo.
En caso de que tu versin de SQL Server no tenga las opciones Poner en conexin y Poner fuera de conexin, debers utilizar la opcin Separar... y luego volver a adjuntarla.
Unidad 1. El entorno grfico SSMS (VI)
1.8. Crear una nueva tabla
Para crear una nueva tabla primero nos tenemos que posicionar en la base de datos donde queremos que se almacene la tabla, desplegar el men contextual y seleccionar la opcin Nueva tabla.
En la ventana que se abre debemos definir las columnas de la tabla:
A cada columna se le asigna un nombre, un tipo de datos, y opcionalmente una serie de propiedades, en este tema veremos las bsicas y las dems las veremos con ms detalle cuando veamos la instruccin SQL CREATE TABLE. De momento no tenemos definida ninguna columna, al teclear un nombre se crea una primera entrada en esta tabla con la definicin de la primera columna. En la columna Tipo de datos elegimos qu tipo de valores se podrn almacenar en la columna.
1.9. Tipos de datos
Podemos elegir entre todos los tipos que aparecen arriba.
Algunos tipos no necesitan ms, como por ejemplo el tipo entero (int), y otros se pueden completar con una longitud, como los tipos alfanumricos:
En este ejemplo hemos definido una columna (Codigo) de tipo Entero corto (Smallint), y una columna (Nombre) que almacenar hasta 20 caracteres alfanumricos (nchar(20)), en este caso la longitud la indicamos en la pestaa Propiedades de columna en la propiedad Longitud. Las propiedades de la columna pueden variar dependiendo del tipo de datos de la columna seleccionada, por ejemplo los campos enteros no tienen la propiedad longitud, ya que el propio tipo define la longitud del campo, en cambio los campos de tipo numeric o decimal no tiene la propiedad longitud pero s las propiedades escala y precisin, los valores que permiten definir el tamao del campo
Unidad 1. El entorno grfico SSMS (VII)
1.10. Valores nulos
Tambin podemos indicar si la columna permitir valores nulos o no, o bien cambiando la propiedad Permitir valores nulos que aparece debajo de la propiedad Longitud, o bien simplemente marcando o desmarcando la casilla de la columna Permitir valores nulos que se encuentra al lado de la columna Tipo de datos. Si la casilla est marcada, el usuario podr no rellenar el campo cuando inserte una fila de datos en la tabla.
1.11. Columna con contador
En la mayora de los sistemas gestores de bases de datos tenemos un tipo de datos de tipo contador, autonumrico, autoincremental, etc. Este tipo hace que el propio sistema es el encargado de rellenar el campo con un valor que va incrementando conforme se crean ms filas de datos en la tabla.
Las columnas de este tipo se utilizan normalmente para numerar las filas de la tabla, como no habrn dos filas con el mismo valor (el sistema se encarga de incrementar el valor cada vez que se crea una nueva fila), estos campos se suelen utilizar como claves primarias.
En SQL Server 2005 no existe el tipo de datos Contador pero se consigue el mismo funcionamiento asignando a la columna un tipo de datos numrico y definiendo la columna como columna de identidad.
En las propiedades de la columna marcamos S en la propiedad (Identidad) y a continuacin podemos indicar en qu valor queremos que empiece el contador (Inicializacin de identidad) y en cunto incrementar cada vez que se cree un nuevo registro (Incremento de identidad).
Aunque este tipo de columnas se utiliza frecuentemente como clave primaria, SQL Server no le asigna automticamente esta funcin, la tenemos que definir nosotros mismos, pero s fuerza a que sea una columna sin valores nulos. No se puede definir ms de una columna de identidad por tabla.
1.12. Clave primaria
Para definir una columna como clave primaria, posicionamos el puntero del ratn sobre la columna, desplegamos el men contextual y seleccionamos la opcin Establecer Clave principal:
Aparecer una llave a la izquierda del nombre, smbolo de las claves principales:
Para definir una clave primaria compuesta por varias columnas,
seleccionamos las columnas manteniendo pulsada la tecla Ctrl y luego
seleccionamos la opcin.
Para quitar una clave principal, hacemos lo mismo pero en esta ocasin seleccionamos la opcin Quitar clave principal.
Tambin podemos utilizar el icono de la barra de herramientas.
Unidad 1. El entorno grfico SSMS (VIII)
1.13. Aadir o eliminar columnas
Una vez definidas algunas columnas, si queremos aadir una nueva columna entre dos, nos posicionamos en la segunda y seleccionamos la opcin Insertar columna del men contextual.
La nueva columna se colocar delante:
Del mismo modo si queremos eliminar la definicin de una columna, nos posicionamos en la columna a eliminar y seleccionamos la opcin Eliminar columna:
O simplemente hacemos clic en la zona a la izquierda del nombre y
pulsamos la tecla Supr.
Finalmente guardamos la tabla, nos pedir el nombre de la tabla:
La nueva tabla aparecer en la lista de tablas de la base de datos:
1.14. Modificar la definicin de una tabla
Para entrar a la ventana de definicin de la tabla utilizamos la opcin Modificar de su men contextual (Tambin es posible que se llame Diseo):
Se abrir la ventana que ya conocemos para definir las columnas de la tabla.
Unidad 1. El entorno grfico SSMS (IX)
1.15. Insertar datos en la tabla
Ahora que tenemos la tabla creada podemos rellenarla con datos. Para eso debemos abrir la tabla:
Se abrir una ventana parecida a esta:
La primera columna sirve para indicarnos el estado de una fila, por ejemplo el * nos indica que es una nueva fila, esta fila realmente no est en la tabla, nos sirve de contenedor para los nuevos datos que queremos insertar.
Para insertar una nueva fila de datos slo tenemos que rellenar los campos que aparecen en esa fila (la del *), al cambiar de fila los datos se guardarn automticamente en la tabla a no ser que alguno infrinja alguna regla de integridad, en ese caso SQL Server nos devuelve un mensaje de error para que corrijamos el dato errneo, si no lo podemos corregir entonces slo podemos deshacer los cambios.
1.16. Modificar datos
Para modificar un valor que ya est en una fila de la tabla slo tenemos que posicionarnos en el campo y rectificar el valor. En cuanto modificamos un valor, la fila aparece con un lpiz escribiendo (ver imagen), este lpiz
nos indica que la fila se ha modificado y tiene nuevos datos por guardar. Al salir de la fila sta se guardar automticamente a no ser que el nuevo valor infrinja alguna regla de integridad. Si queremos salir de la fila sin guardar los cambios, tenemos que cancelar la actualizacin pulsando la
tecla ESC.
1.17. Eliminar filas
Para eliminar una fila completa, la seleccionamos y pulsamos la tecla
Supr o bien desplegamos su men contextual y seleccionamos la opcin
Eliminar.
En cualquiera de los dos casos nos aparece un mensaje de confirmacin.
1.18. Relacionar tablas
Como ya hemos visto, en una base de datos relacional, las relaciones entre las tablas se implementan mediante la definicin de claves ajenas, que son campos que contienen valores que sealan a un registro en otra tabla, en esta relacin as creada, la tabla referenciada se considera principal y la que contiene la clave ajena es la subordinada.
Desde el entorno grfico del SSMS podemos definir claves ajenas entrando en el diseo de la tabla y desplegando el men contextual del campo que va a ser clave ajena:
Seleccionamos la opcin Relaciones y se abre la ventana:
Al pulsar el botn que se encuentra en la fila Especificacin de tablas y columnas se abre el dilogo donde definiremos la relacin:
Unidad 1. El entorno grfico SSMS (X)
En la parte derecha tenemos la tabla en la que estamos y el campo que va a actuar como clave ajena, slo nos queda elegir en el desplegable de la izquierda la tabla a la que hace referencia la clave y al seleccionar una tabla, a la izquierda del campo clave ajena podremos elegir el campo de la otra tabla por el que se relacionarn las tablas. En nuestro caso ser:
De esta forma hemos definido una relacin entre las tablas Facturas y Clientes. Para ver las relaciones existentes entre las diferentes tablas tenemos los diagramas.
Primero debemos definir el diagrama, para ello seleccionamos la opcin correspondiente:
Si no tenemos todava ningn diagrama creado, nos aparece un mensaje:
Elegimos S y se crea digamos el soporte donde se pintar el diagrama.
A continuacin nos aparece el nuevo diagrama ahora si elegimos crear un nuevo diagrama nos preguntar las tablas a incluir en el diagrama:
Seleccionamos cada una y pulsamos Agregar, cuando hayamos agregado al diagrama todas las que queremos pulsamos en Cerrar y aparecern en el diagrama las tablas con las relaciones que tengan definidas en ese momento:
La llave indica la tabla principal (padre) y el smbolo infinito seala la tabla que contiene la clave ajena.
En el examinador de objetos en la carpeta Diagramas de base de datos aparecen todos los diagramas definidos hasta el momento:
Hemos aprendido hasta ahora lo bsico para poder crear una base de datos y rellenarla con tablas relacionadas entre s y con datos, ahora veamos cmo recuperar esos datos.
Unidad 1. El entorno grfico SSMS (XI)
1.19. Abrir una nueva consulta
Vamos a ver ahora cmo crear consultas SQL y ejecutarlas desde el entorno del SSMS.
Para ello debemos abrir la zona de trabajo de tipo Query, abriendo una nueva consulta, seleccionando previamente el servidor y pulsando el
botn de la barra de botones o si queremos realizar la consulta sobre un servidor con el cual todava no hemos establecido conexin, seleccionando de la barra de mens la opcin Nuevo > Consulta de motor de base de datos:
.
En este ltimo caso nos aparecer el cuadro de dilogo para establecer la conexin (el mismo que vimos al principio del tema).
A continuacin se abrir una nueva pestaa donde podremos teclear las sentencias SQL:
Adems aparece una nueva barra de botones que nos permitir ejecutar los comandos ms tiles del modo query.
1.20. Escribir y ejecutar cdigo TRANSACT-SQL
Slo tenemos que teclear la sentencia a ejecutar, por ejemplo empezaremos por crear la base de datos.
Utilizaremos la sentencia CREATE DATABASE mnima:
CREATE DATABASE ventas;
Al pulsar el botn Ejecutar se ejecuta la sentencia y aparece en la parte inferior el resultado de la ejecucin, en la pestaa Mensajes:
Si ahora desplegamos la carpeta Bases de Datos del Explorador de Objetos, observaremos la base de datos que hemos creado:
Si la ejecucin de la sentencia produce un error, el sistema nos devolver el mensaje de error escrito en rojo en la pestaa Mensajes.
Podemos incluir en una misma consulta varias sentencias SQL, cuando pulsamos Ejecutar se ejecutarn todas una detrs de otra. Si tenemos varias consultas y slo queremos ejecutar una, la seleccionaremos antes de ejecutarla.
Unidad 1. El entorno grfico SSMS (XII)
1.21. La base de datos predeterminada
Cuando ejecutamos consultas desde el editor, nos tenemos que fijar sobre qu base de datos se va a actuar. Fijndonos en la pestaa de la consulta, en el nombre aparece el nombre del servidor seguido de un punto y el nombre de la base de datos sobre la que se va a actuar y luego un guin y el nombre de la consulta. En la imagen anterior tenemos ord01.master SQLQuery1.sql, lo que nos indica que la consulta se llama SQLQuery1.sql, y que se va a ejecutar sobre la base de datos master que se encuentra en el servidor ord01.
Cuando creamos una nueva consulta, sta actuar sobre la base de datos activa en ese momento. Por defecto la base de datos activa es la predeterminada (master). Si queremos que la base de datos activa sea por ejemplo la base de datos ventas, hacemos clic sobre su nombre en el Explorador de objetos, y sta pasar a ser la base de datos activa. Si ahora creamos una nueva consulta, sta actuar sobre la base de datos ventas.
Si queremos crear una consulta que siempre acte sobre una determinada base de datos y no nos queremos preocupar de qu base de datos tenemos activa podemos aadir al principio de la consulta la instruccin USE nombreBaseDatos; esto har que todas las instrucciones que aparezcan despus, se ejecuten sobre la base de datos indicada. Por ejemplo:
USE ventas;
SELECT * FROM pedidos;
Obtiene todos los datos de la tabla pedidos que se encuentra en la base de datos ventas. Si no utilizamos USE y almacenamos la consulta, al abrirla otra vez, coger como base de datos la predeterminada (no la activa) y se volver a ejecutar sobre la base de datos master.
Normalmente utilizaremos como base de datos la nuestra y no la base de datos master, por lo que nos ser til cambiar el nombre de la base de
datos por defecto, esto lo podemos hacer cambiando la base de datos por defecto en el id de sesin.
Para ello, cuando vamos a conectar con el servidor:
Pulsamos en el botn Opciones >>
En la pestaa Propiedades de conexin, en el cuadro Conectar con base de datos: Seleccionamos para elegir la base de datos.
La elegimos y aceptamos. A partir de ese momento la base de datos elegida ser la que SQL Server coja por defecto en todas las sesiones de ese usuario.
Unidad 1. El entorno grfico SSMS (XIII)
1.22. El editor de texto
Para facilitarnos la redaccin y correccin de las sentencias, el editor de SQL presenta las palabras de distintos colores segn su categora y podemos utilizar el panel Explorador de Objetos para arrastrar desde l los objetos sobre la zona de trabajo y as asegurarnos de que los nombres de los objetos (por ejemplo nombre de tabla, de columna, etc.) sean los correctos. Como hemos dicho el texto que se escribe en este editor de cdigo se colorea por categora. Los colores son los mismos que se utilizan en todo el entorno SQL Server. En esta tabla aparecen los colores ms comunes.
Color Categora
Rojo Cadena de caracteres
Verde oscuro Comentario
Negro sobre fondo plateado Comando SQLCMD
Fucsia Funcin del sistema
Verde Tabla del sistema
Azul Palabra clave
Verde azulado Nmeros de lnea o parmetro de plantilla
Rojo oscuro Procedimiento almacenado de SQL Server
Gris oscuro Operadores
1.23. Configurar un esquema de colores personalizado
En el men Herramientas > Opciones, desplegando la opcin Entorno, Fuentes y colores, se puede ver la lista completa de colores y sus categoras, as como configurar un esquema de colores personalizado:
En la lista Mostrar valores para, seleccionamos el entorno que se ver afectado.
El botn Usar predeterminados nos permite volver a la configuracin predeterminada.
Ahora slo nos queda aprender a redactar sentencias SQL, cosa que se ver en otro momento, mientras tanto podemos utilizar el Generador de Consulta que incluye SSMS y que veremos a continuacin en el apartado sobre vistas.
1.24. Las Vistas
Las consultas que hemos visto hasta ahora son trozos de cdigo SQL que podemos guardar en un archivo de texto y abrir y ejecutar cuando queramos, pero si queremos que nuestra consulta de recuperacin de datos se guarde en la propia base de datos y se comporte como una tabla (algo parecido a una consulta almacenada de Access), la tenemos que definir como una vista. Esta vista tiene la ventaja entre otras de poder ser utilizada como si fuese una tabla en otras consultas. Realmente al ejecutarla obtenemos una tabla lgica almacenada en memoria y lo que se guarda en la base de datos es su definicin, la instruccin SQL que permite recuperar los datos.
Para definir una vista en el Explorador de Objetos desplegamos la base de datos donde la guardaremos y elegimos la opcin Nueva vista del men contextual de la carpeta Vistas, se pondr en funcionamiento el generador de consultas pidindonos las tablas en las que se basar la
vista. Pulsamos sobre la tabla a aadir al diseo de la vista y pulsamos el botn Agregar, podemos aadir as cuntas tablas queramos.
Despus de Cerrar, vemos a la derecha del Explorador de Objetos la pestaa con la definicin de la vista que puede incluir varios paneles:
La aparicin de estos paneles es configurable, en la barra de herramientas Diseador de vistas los iconos remarcados en azul son los
correspondientes a cada panel:
Unidad 1. El entorno grfico SSMS (XIV)
Nosotros, a lo largo del curso, crearemos las vistas desde el panel SQL que veremos ms adelante.
1.25. El panel de diagrama
Es el primero que aparece, incluye una representacin grfica de las tablas con sus campos y de la forma en que se juntan en la vista. En este caso, como las tablas tienen relaciones definidas (claves ajenas), esta relacin ha aparecido automticamente al aadir la segunda tabla. Pero se puede cambiar el tipo de relacin eligiendo la opcin correspondiente en el men contextual que aparece con el clic derecho sobre la relacin:
Desde el panel diagrama podemos aadir cmodamente campos de las tablas a la consulta marcando la casilla correspondiente. En la imagen anterior la nica casilla seleccionada es la del * en la tabla Libros por lo que se visualizarn todas las columnas de la tabla Libros y ninguna de la tabla Prstamos. Conforme vamos marcando casillas de las tablas del panel diagrama, los cambios se ven reflejados en los dems paneles excepto en el panel de resultados que se actualiza ejecutando la consulta.
1.26. El panel de criterios
Es una rejilla en la que podemos definir las columnas del resultado de la consulta (las columnas de la vista).
En cada fila de la rejilla se define una columna del resultado o una columna que se utiliza para obtener el resultado.
En Columna tenemos el nombre de la columna de la se obtienen los datos o la expresin cuando se trata de una columna calculada.
En Alias escribimos el nombre que tendr la columna en la vista, tambin corresponde con el encabezado de la columna en la rejilla de resultado. Si se deja el campo en blanco, por defecto se asume el mismo nombre que hay en Columna.
En Tabla tenemos el nombre de la tabla del origen de la consulta a la que pertenece la Columna, por ejemplo la primera columna del resultado se saca de la columna Codigo de la tabla LIBROS y se llamar CodLibro. La cuarta columna de la vista coger sus datos de la columna Usuario de la tabla Prestamos y se llamar Usuario (Alias se ha dejado en blanco por lo que asume el nombre que hay en Columna.
En la columna Resultados indicamos si queremos que la columna se visualice o no, las columnas con la casilla marcada se visualizan.
Las columnas Criterio de ordenacin y Tipo de orden permiten ordenar las filas del resultado segn una o ms columnas. Se ordena por las columnas que tienen algo en Tipo de orden y cuando se ordena por varias columnas Criterio de ordenacin indica que primero se ordena por la columna que lleva el n 1 y despus por la columna que lleva el n 2 y as sucesivamente. En el ejemplo las filas del resultado se ordenarn primero por cdigo de libro y despus por cdigo de prstamo, todas las filas dentro del mismo libro se ordenarn por cdigo de prstamo.
Si queremos aadir unos criterios de seleccin tenemos las columnas Filtro y O
En cada celda indicamos una condicin que debe cumplir la columna correspondiente y se puede combinar varias condiciones mediante O (OR) e Y (AND) segn coloquemos las condiciones en la misma columna o en columnas diferentes. En el ejemplo anterior tenemos la condicin compuesta: ((usuario=1) AND (Dias>5)) OR (Usuario=2).
Podemos variar el orden de aparicin de las columnas arrastrando la fila correspondiente de la rejilla hasta el lugar deseado.
Tambin podemos Elimnar filas de la rejilla para eliminar columnas del resultado, lo conseguimos seleccionando la fila haciendo clic sobre su extremo izquierda y cuando aparece toda la fila remarcada pulsamos
Supr o desde el men contextual de la fila.
Podemos definir consultas ms complejas como por ejemplo consultas de resumen, pulsando sobre el botn Agrupar por de la barra de herramientas, se aade a la rejilla una nueva columna Agrupar por con las siguientes opciones:
Unidad 1. El entorno grfico SSMS (XV)
1.27. El panel SQL
En l vemos la instruccin SQL generada, tambin podemos redactar directamente la sentencia SQL en el panel y ver los cambios equivalentes en los distintos paneles. Para ver estos cambios debemos de ejecutar o Comprobar la sintaxis para que se actualicen los dems paneles.
Por defecto el generador aade a la consulta una clusula TOP (100) PERCENT que indica que se visualizarn el 100% de las filas. Esta clusula no la hemos definido nosotros sino que la aade automticamente el generador.
Una vez tenemos la vista definida la guardamos y podremos hacer con ella casi todo lo que podemos hacer con una tabla. De hecho si nos fijamos en el Explorador de objetos, en la carpeta Vistas:
Vemos que la estructura es muy similar a la estrutura de una tabla. Y que podemos modificar su definicin y ejecutarla, igual que con las tablas:
Modificar para modificar la definicin de la vista
Abrir vista para ejecutarla y ver los datos como si fuese una tabla real.
Ejercicios unidad 1: El entorno grfico 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, Prstamos y Usuarios de forma que sigan el siguiente esquema:
Tabla Libros
NOMBRE CAMPO TIPO DE DATOS TAMAO
Codigo Numrico Byte
Nombre Texto 60
Editorial Texto 25
Autor Texto 25
Genero Texto 20
PaisAutor Texto 20
Paginas Numrico Entero
AnyEdicion Fecha/Hora Fecha mediana
Precio Moneda
Dias Numrico Entero
Tabla Prstamos
NOMBRE CAMPO TIPO DE DATOS TAMAO
Codigo Autonumrico
Libro Numrico Byte
Usuario Numrico Byte
FSalida Fecha/Hora Fecha mediana
FMaxima Fecha/Hora Fecha mediana
FDevol Fecha/Hora Fecha mediana
Tabla Usuarios
NOMBRE CAMPO TIPO DE DATOS TAMAO
Codigo Autonumrico
Nombre Texto 15
Apellidos Texto 25
DNI Texto 12
Domicilio Texto 50
Poblacion Texto 30
Provincia Texto 20
FNacim Fecha/Hora Fecha mediana
Maximo_permitido Numrico Entero
Puedes consultar aqu las soluciones propuestas.
Ayuda ejercicios unidad 1: El entorno grfico SSMS
Ejercicio 1: Crear una base de datos
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 botn secundario del ratn sobre la carpeta Bases de datos del Explorador de objetos.
3. Selecciona Nueva base de datos...
4. Llmala Biblioteca y deja los nombres y caractersticas de los archivos con su definicin predeterminada. Pulsa Aceptar.
5. Despliega el contenido de la nueva carpeta Biblioteca pulsando sobre el signo + para comprobar que se ha creado correctamente.
Ejercicio 2: Crear y definir tablas
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 opcin Nueva tabla...
3. Escribe el nombre de cada columna y escoge el tipo de datos en el desplegable. En la zona inferior, en la pestaa Propiedades de columna, busca el campo que define el tamao del dato y cmbialo si es necesario.
4. Cuando hayas acabado, pulsa el botn 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 opcin Abrir tabla. Rellena los campos.
3. Repite la operacin para las otras dos tablas.
Ejercicio 4: Modificar la definicin de las tablas
Para modificar la definicin de las tablas: Ten en cuenta que las claves primarias sern los campos codigo de cada tabla. Adems, el campo Libro de la tabla Prestamos es clave ajena, hace referencia al Codigo de Libros. Y el campo Usuario tambin 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 opcin Diseo (o Modificar).
3. Incluye la clave primaria seleccionando la columna Codigo y pulsando el botn en forma de llave de la barra de herramientas.
4. Si la tabla contiene una clave ajena, indcalo 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 operacin para las otras dos tablas.
Ejercicios unidad 1: El entorno grfico SSMS (II)
Ejercicio 3: Insertar datos en las tablas
Inserta los datos en las tablas de forma que el resultado sea el siguiente:
Puedes consultar aqu las soluciones propuestas.
Ejercicio 4: Modificar la definicin de las tablas
Observando los datos, averigua:
Claves primarias
Claves ajenas
Valores no nulos (supondremos que una columna no admite nulos si en los datos sumnistrados no hay ningn nulo en esa columna).
Modifica la definicin de las tablas para incorporar esta informacin.
Puedes consultar aqu las soluciones propuestas.
Ayuda ejercicios unidad 1: El entorno grfico SSMS
Ejercicio 1: Crear una base de datos
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 botn secundario del ratn sobre la carpeta Bases de datos del Explorador de objetos.
3. Selecciona Nueva base de datos...
4. Llmala Biblioteca y deja los nombres y caractersticas de los archivos con su definicin predeterminada. Pulsa Aceptar.
5. Despliega el contenido de la nueva carpeta Biblioteca pulsando sobre el signo + para comprobar que se ha creado correctamente.
Ejercicio 2: Crear y definir tablas
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 opcin Nueva tabla...
3. Escribe el nombre de cada columna y escoge el tipo de datos en el desplegable. En la zona inferior, en la pestaa Propiedades de columna, busca el campo que define el tamao del dato y cmbialo si es necesario.
4. Cuando hayas acabado, pulsa el botn 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 opcin Abrir tabla. Rellena los campos.
3. Repite la operacin para las otras dos tablas.
Ejercicio 4: Modificar la definicin de las tablas
Para modificar la definicin de las tablas: Ten en cuenta que las claves primarias sern los campos codigo de cada tabla. Adems, el campo Libro de la tabla Prestamos es clave ajena, hace referencia al Codigo de Libros. Y el campo Usuario tambin 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 opcin Diseo (o Modificar).
3. Incluye la clave primaria seleccionando la columna Codigo y pulsando el botn en forma de llave de la barra de herramientas.
4. Si la tabla contiene una clave ajena, indcalo 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 operacin para las otras dos tablas.
Ejercicios unidad 3: Consultas simples
Te aconsejamos que, para realizar consultas SQL ms fcilmente, te hagas estas preguntas:
Dnde estn los datos necesarios? La respuesta dar la FROM.
Qu columnas quiero que se visualicen en el listado? La respuesta dar la lista de seleccin (SELECT).
Quiero que se ordenen por algn valor? Si la respuesta es s, necesitars utilizar ORDER BY.
Tienen que aparecer todas las filas del resultado? Si la respuesta es NO, debers:
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 clusula WHERE para expresar la condicin que deben cumplir para ser mostradas.
Para realizar los ejercicios, debers 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 seleccin
1. Listar todos los empleados.
Resultado:
Nume
mp
Nombre Eda
d
Ofici
na
Titulo Contrato Jefe Cuota ventas
101 Antonio
Viguer
4
5
12 represen
tante
1986-
10-20
00:00:0
0.000
10
4
3000
0,00
3050
0,00
102 Alvaro
Jaume
s
4
8
21 represen
tante
1986-
12-10
00:00:0
0.000
10
8
3500
0,00
4740
0,00
103 Juan
Rovira
2
9
12 represen
tante
1987-
03-01
00:00:0
0.000
10
4
2750
0,00
2860
0,00
104 Jos
Gonzl
ez
3
3
12 dir
ventas
1987-
05-19
00:00:0
0.000
10
6
2000
0,00
1430
0,00
105 Vicente
Pantall
a
3
7
13 represen
tante
1988-
02-12
00:00:0
10
4
3500
0,00
3680
0,00
0.000
106 Luis
Antonio
5
2
11 director
general
1988-
06-14
00:00:0
0.000
NU
LL
2750
0,00
2990
0,00
107 Jorge
Gutirr
ez
4
9
22 represen
tante
1988-
11-14
00:00:0
0.000
10
8
3000
0,00
1860
0,00
108 Ana
Bustam
ante
6
2
21 dir
ventas
1989-
10-12
00:00:0
0.000
10
6
3500
0,00
3610
0,00
109 Mara
Sunta
3
1
NU
LL
represen
tante
1999-
10-12
00:00:0
0.000
10
6
3000,
00
3920
0,00
110 Juan
Victor
4
1
NU
LL
represen
tante
1990-
01-13
00:00:0
0.000
10
4
NULL 7600,
00
111 Juan
Gris
5
0
NU
LL
represen
tante
2005-
05-01
00:00:0
0.000
NU
LL
1000
0,00
6000
0,00
112 Julin
Martore
ll
5
0
NU
LL
represen
tante
2006-
05-01
00:00:0
0.000
NU
LL
1000
0,00
9100
0,00
113 Juan
Gris
1
8
NU
LL
represen
tante
2007-
01-01
00:00:0
0.000
NU
LL
1000
0,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.
Puedes consultar aqu las soluciones propuestas.
Ejercicio 2: Utilizando ORDER BY
1. Listar de cada regin las oficinas por orden de mejores ventas.
Resultado:
Regin Ciudad Ventas
NULL Elx 0,00
NULL Valencia NULL
centro Aranjuez 15000,00
Centro Mstoles 0,00
centro Madrid NULL
este Alicante 73500,00
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 Corua 18600,00
2. Saber los productos que tienen un precio superior o igual al precio de la mitad de los productos.
Resultado:
Idfab Idproducto Descripcin 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
Puedes consultar aqu las soluciones propuestas.
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 Gonzlez 14300,00 20000,00
107 Jorge Gutirrez 18600,00 30000,00
2. Hallar los empleados que no estn a cargo del empleado 106.
Resultado:
Numemp nombre
101 Antonio Viguer
102 Alvaro Jaumes
103 Juan Rovira
105 Vicente Pantalla
106 Luis Antonio
107 Jorge Gutirrez
110 Juan Victor
111 Juan Gris
112 Julin Martorell
113 Juan Gris
3. Listar de cada jefe su cdigo y el cdigo 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 Gonzlez
106 109 Mara Sunta
108 102 Alvaro Jaumes
108 107 Jorge Gutirrez
Puedes consultar aqu las soluciones propuestas.
Ayuda ejercicios unidad 3: Consultas simples
Ejercicio 1: Listas de seleccin
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 regin 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 estn a cargo del empleado 106.
SELECT numemp, nombre
FROM empleados
WHERE jefe 106 OR jefe IS NULL;
Si no aadimos la segunda parte de la pregunta (OR jefe IS NULL) los empleados que no tienen jefe no salen.
Para listar de cada jefe su cdigo y el cdigo y nombre de sus subordinados ordenados por nombres.
SELECT jefe, numemp, nombre
FROM empleados
WHERE jefe IS NOT NULL
ORDER BY jefe, nombre;
Ejercicios unidad 4: Consultas multitabla
Para realizar los ejercicios, debers 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 composicin de tablas
1. Listar todos los pedidos, mostrando el precio y la descripcin del producto.
Resultado:
Codi
go
Numpe
dido
Fechape
dido
Clie Re
p
Fab Produ
cto
Ca
nt
Impo
rte
Prec
io
descrip
cion
1 11003
6
1989-
10-12
00:00:0
0.000
21
07
1
1
0
ac
i
410
0z
9 22,
50
NU
LL
mont
2 11003
7
1989-
10-12
00:00:0
0.000
21
17
1
0
6
rei 2a4
4l
7 31,
50
45,
00
bomb
a l
3 11296
3
2008-
05-10
00:00:0
0.000
21
03
1
0
5
ac
i
410
04
2
8
3,2
76
NU
LL
art t4
4 11296
8
1990-
01-11
00:00:0
0.000
21
02
1
0
1
ac
i
410
04
3
4
39,
78
NU
LL
art t4
5 11297
5
2008-
02-11
00:00:0
0.000
21
11
1
0
3
rei 2a4
4g
6 21,
00
3,5
0
pas
6 11297
9
1989-
10-12
00:00:0
0.000
21
14
1
0
8
ac
i
410
0z
6 150
,00
NU
LL
mont
7 11298
3
2008-
05-10
00:00:0
0.000
21
03
1
0
5
ac
i
410
04
6 7,0
2
NU
LL
art t4
8 11298
7
2008-
01-01
00:00:0
0.000
21
03
1
0
5
ac
i
410
0y
1
1
275
,00
NU
LL
extrac
tor
9 11298
9
2008-
12-10
00:00:0
0.000
21
01
1
0
6
fe
a
114 6 14,
58
2,4
3
cubo
10 11299
2
1990-
04-15
00:00:0
0.000
21
18
1
0
8
ac
i
410
02
1
0
7,6
0
NU
LL
bisagr
a
11 11299
3
2008-
03-10
00:00:0
21
06
1
0
rei 2a4
5c
2
4
18,
96
0,7
9
junta
0.000 2
12 11299
7
2008-
04-04
00:00:0
0.000
21
24
1
0
7
bi
c
410
03
1 6,5
2
6,5
2
maniv
ela
13 11300
3
2008-
02-05
00:00:0
0.000
21
08
1
0
9
im
m
779c 3 56,
25
18,
75
reosta
to 3
14 11300
7
2008-
01-01
00:00:0
0.000
21
12
1
0
8
im
m
773c 3 29,
25
9,7
5
reosta
to
15 11301
2
2008-
05-05
00:00:0
0.000
21
11
1
0
5
ac
i
410
03
3
5
37,
45
NU
LL
art t3
16 11301
3
2008-
12-28
00:00:0
0.000
21
18
1
0
8
bi
c
410
03
1 6,5
2
6,5
2
maniv
ela
17 11302
4
2008-
07-04
00:00:0
0.000
21
14
1
0
8
qs
a
xk47 2
0
71,
00
3,5
5
red
18 11302
7
2008-
02-05
00:00:0
0.000
21
03
1
0
5
ac
i
410
02
5
4
450
,00
NU
LL
bisagr
a
19 11303
4
2008-
11-05
00:00:0
0.000
21
07
1
1
0
rei 2a4
5c
8 6,3
2
0,7
9
junta
20 11304
2
2008-
01-01
00:00:0
0.000
21
13
1
0
1
rei 2a4
4r
5 225
,00
45,
00
bomb
a r
21 11304
5
2008-
07-02
00:00:0
0.000
21
12
1
1
0
rei 2a4
4r
1
0
450
,00
45,
00
bomb
a r
22 11304
8
2008-
02-02
00:00:0
0.000
21
20
1
0
2
im
m
779c 2 37,
50
18,
75
reosta
to 3
23 11304
9
2008-
04-04
00:00:0
0.000
21
18
1
0
8
qs
a
xk47 2 7,7
6
3,5
5
red
24 11305
1
2008-
07-06
00:00:0
0.000
21
18
1
0
8
qs
a
xk47 4 14,
20
3,5
5
red
25 11305
5
2009-
04-01
00:00:0
0.000
21
08
1
0
1
ac
i
410
0x
6 1,5
0
NU
LL
junta
26 11305
7
2008-
11-01
00:00:0
0.000
21
11
1
0
3
ac
i
410
0x
2
4
NU
LL
NU
LL
junta
27 11305
8
1989-
07-04
00:00:0
0.000
21
08
1
0
9
fe
a
112 1
0
14,
80
1,4
8
cubo
28 11306
2
2008-
07-04
00:00:0
0.000
21
24
1
0
7
bi
c
410
03
1
0
24,
30
6,5
2
maniv
ela
29 11306
5
2008-
06-03
00:00:0
0.000
21
06
1
0
2
qs
a
xk47 6 21,
30
3,5
5
red
30 11306
9
2008-
08-01
00:00:0
21
09
1
0
im
m
773c 2
2
313
,50
9,7
5
reosta
to
0.000 7
Puedes consultar aqu las soluciones propuestas.
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:
Numped
ido
Fechape
dido
Clie Rep Fab Produ
cto
Ca
nt
Preci
o
Client
e
vended
or
11298
7
01/01/0
8
21
03
10
5
aci 4100
y
1
1
275,
00
Jaim
e
Llore
ns
Vicent
e
Pantal
la
11302
7
05/02/0
8
21
03
10
5
aci 4100
2
5
4
450,
00
Jaim
e
Llore
ns
Vicent
e
Pantal
la
11304
5
02/07/0
8
21
12
11
0
rei 2a44
r
1
0
450,
00
Mar
a
Silva
Juan
Victor
11306
9
01/08/0
8
21
09
10
7
im
m
773c 2
2
313,
50
Alber
to
Juan
es
Jorge
Gutir
rez
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:
Codi
go
Numpe
dido
Fechape
dido
Clie Re
p
Fab Produ
cto
Ca
nt
Impo
rte
Clien
te
Vended
or
asignad
o
8 1129
87
2008-
01-01
00:00:0
0.000
21
03
1
0
5
ac
i
410
0y
1
1
275
,00
Jai
me
Llor
ens
Juan
Victor
18 1130
27
2008-
02-05
00:00:0
0.000
21
03
1
0
5
ac
i
410
02
5
4
450
,00
Jai
me
Llor
ens
Juan
Victor
21 1130
45
2008-
07-02
00:00:0
0.000
21
12
1
1
0
rei 2a4
4r
1
0
450
,00
Mar
a
Silv
a
Ana
Busta
mante
30 1130
69
2008-
08-01
00:00:0
0.000
21
09
1
0
7
im
m
773
c
2
2
313
,50
Alb
erto
Jua
nes
Juan
Rovira
Puedes consultar aqu las soluciones propuestas.
4. Listar los pedidos superiores a 250 euros, mostrando adems 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
Llorens
105 Juan Victor NULL
113045 2112 110 Mara
Silva
110 Ana
Bustamante
Badajoz
113069 2109 107 Alberto
Juanes
107 Juan Rovira Alicante
5. Hallar los pedidos recibidos los das en que un nuevo empleado fue contratado.
Resultado:
Numpedid
o
Fechapedido Rep Numem
p
Nombre Contrato
110036 1989-10-12
00:00:00.00
0
11
0
108 Ana
Bustamant
e
1989-10-12
00:00:00.00
0
110037 1989-10-12
00:00:00.00
0
10
6
108 Ana
Bustamant
e
1989-10-12
00:00:00.00
0
112979 1989-10-12
00:00:00.00
0
10
8
108 Ana
Bustamant
e
1989-10-12
00:00:00.00
0
6. Hallar los empleados que realizaron su primer pedido el mismo da que fueron contratados.
Resultado:
Numem
p
Nombre Contrato Numpedid
o
Rep fechapedido
108 Ana
Bustamant
e
1989-10-12
00:00:00.00
0
112979 10
8
1989-10-12
00:00:00.00
0
7. Mostrar de cada empleado su cdigo, nombre, ventas, oficina y ciudad en la que est ubicada su oficina.
Resultado:
Numemp Nombre ventas Oficina ciudad
101 Antonio Viguer 30500,00 12 Alicante
102 Alvaro Jaumes 47400,00 21 Badajoz
103 Juan Rovira 28600,00 12 Alicante
104 Jos Gonzlez 14300,00 12 Alicante
105 Vicente Pantalla 36800,00 13 Castellon
106 Luis Antonio 29900,00 11 Valencia
107 Jorge Gutirrez 18600,00 22 A Corua
108 Ana Bustamante 36100,00 21 Badajoz
109 Mara Sunta 39200,00 NULL NULL
110 Juan Victor 7600,00 NULL NULL
111 Juan Gris 60000,00 NULL NULL
112 Julin Martorell 91000,00 NULL NULL
113 Juan Gris 0,00 NULL NULL
Puedes consultar aqu las soluciones propuestas.
8. Listar los empleados con una cuota superior a la de su jefe, los campos a obtener son el nmero, nombre y cuota del empleado y nmero, nombre y cuota de su jefe.
Resultado:
Numemp Nombre Cuota Jefe Nombre jefe Cuota jefe
101 Antonio Viguer 30000,00 104 Jos
Gonzlez
20000,00
103 Juan Rovira 27500,00 104 Jos
Gonzlez
20000,00
105 Vicente
Pantalla
35000,00 104 Jos
Gonzlez
20000,00
108 Ana
Bustamante
35000,00 106 Luis Antonio 27500,00
9. Desde el entorno grfico cambia el empleado 111, asgnale el jefe 110 y la oficina 21. Despus cambia la sentencia anterior para que salgan tambin los empleados cuyo jefe no tenga cuota.
Resultado:
Numemp Nombre Cuota Jefe Nombre jefe Cuota jefe
101 Antonio Viguer 30000,00 104 Jos
Gonzlez
20000,00
103 Juan Rovira 27500,00 104 Jos
Gonzlez
20000,00
105 Vicente
Pantalla
35000,00 104 Jos
Gonzlez
20000,00
108 Ana
Bustamante
35000,00 106 Luis Antonio 27500,00
111 Juan Gris 10000,00 110 Juan Victor NULL
10. Listar los empleados que no estn asignados a la misma oficina que su jefe, queremos nmero, nombre y nmero de oficina tanto del empleado como de su jefe.
Resultado:
Numemp Nombre Oficina Jefe Nombre jefe Oficina
jefe
104 Jos Gonzlez 12 106 Luis Antonio 11
105 Vicente Pantalla 13 104 Jos Gonzlez 12
107 Jorge Gutirrez 22 108 Ana
Bustamante
21
108 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
jefe
104 Jos Gonzlez 12 106 Luis Antonio 11
105 Vicente Pantalla 13 104 Jos Gonzlez 12
107 Jorge Gutirrez 22 108 Ana
Bustamante
21
108 Ana
Bustamante
21 106 Luis Antonio 11
109 Mara Sunta NULL 106 Luis Antonio 11
110 Juan Victor NULL 104 Jos Gonzlez 12
111 Juan Gris 21 110 Juan Victor NULL
12. Lo mismo que la anterior pero queremos que aparezca tambin la ciudad de las oficinas (tanto del empleado como de su jefe).
Resultado:
Numem
p
Nombre Oficin
a
Ciudad Jefe Nombre
jefe
Oficin
a jefe
ciudad
104 Jos
Gonzlez
12 Alicant
e
10
6
Luis
Antonio
11 Valenc
ia
105 Vicente
Pantalla
13 Castell
on
10
4
Jos
Gonzlez
12 Alicant
e
107 Jorge
Gutirrez
22 A
Corua
10
8
Ana
Bustaman
te
21 Badajo
z
108 Ana
Bustaman
te
21 Badajo
z
10
6
Luis
Antonio
11 Valenc
ia
109 Mara
Sunta
NUL
L
NULL 10
6
Luis
Antonio
11 Valenc
ia
110 Juan
Victor
NUL
L
NULL 10
4
Jos
Gonzlez
12 Alicant
e
111 Juan Gris 21 Badajo
z
11
0
Juan
Victor
NUL
L
NULL
Puedes consultar aqu las soluciones propuestas.
Ejercicio 2: Comparar tablas
1. Obtener los empleados de GestionSimples que aparecen en Gestion con otra oficina.
El resultado ser que no devuelve filas.
Puedes consultar aqu las soluciones propuestas.
Ayuda ejercicios unidad 4: Consultas multitabla
Ejercicio 1: La composicin de tablas
1. Listar todos los pedidos, mostrando el precio y la descripcin 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
WHERE importe > 250;
11. Listar los pedidos superiores a 250 euros, mostrando adems 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
WHERE importe > 250;
16. Hallar los pedidos recibidos los das 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 da 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 cdigo, 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 nmero, nombre y cuota del empleado y nmero, 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 grfico cambia el empleado 111, asgnale el jefe 110 y la oficina 21. Despus cambia la sentencia anterior para que salgan tambin 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 estn asignados a la misma oficina que su jefe, queremos nmero, nombre y nmero 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 tambin 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;
Ejercicios unidad 5: Consultas de resumen
Para realizar los ejercicios, debers utilizar la base de datos GestionSimples.
Ejercicio 1: Funciones de agregado
1. Cuntas oficinas tenemos en Valencia?
Resultado:
Valencianas
4
2. Hallar cuntos pedidos hay de ms de 250 euros.
Resultado:
Superiores a 250
4
3. Cuntos ttulos (cargos) de empleados se usan?
Resultado:
Cuntos ttulos
3
4. Entre qu cuotas se mueven los empleados?
Resultado:
Cuota mnima Cuota mxima
3000,00 35000,00
Puedes consultar aqu las soluciones propuestas.
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 funcin ISNULL().
Resultado:
numemp nombre Importe vendido
101 Antonio Viguer 266,28
102 Alvaro Jaumes 77,76
103 Juan Rovira 21,00
104 Jos Gonzlez 0,00
105 Vicente Pantalla 772,746
106 Luis Antonio 46,08
107 Jorge Gutirrez 344,32
108 Ana Bustamante 286,33
109 Mara Sunta 71,05
110 Juan Victor 478,82
111 Juan Gris 0,00
112 Julin Martorell 0,00
113 Juan Gris 0,00
114 Pablo Moreno 0,00
Puedes consultar aqu las soluciones propuestas.
2. De cada empleado, obtener el importe vendido a cada cliente.
Resultado:
rep Cliente Importe vendido
106 2101 14,58
101 2102 39,78
105 2103 735,296
102 2106 40,26
110 2107 28,82
101 2108 1,50
109 2108 71,05
107 2109 313,50
103 2111 21,00
105 2111 37,45
108 2112 29,25
110 2112 450,00
101 2113 225,00
108 2114 221,00
106 2117 31,50
108 2118 36,08
102 2120 37,50
107 2124 30,82
Puedes consultar aqu las soluciones propuestas.
3. Repetir la consulta anterior pero ahora deben aparecer tambin los empleados que no han vendido nada.
Resultado:
rep Cliente Importe vendido
101 2102 39,78
101 2108 1,50
101 2113 225,00
102 2106 40,26
102 2120 37,50
103 2111 21,00
104 NULL NULL
105 2103 735,296
105 2111 37,45
106 2101 14,58
106 2117 31,50
107 2109 313,50
107 2124 30,82
108 2112 29,25
108 2114 221,00
108 2118 36,08
109 2108 71,05
110 2107 28,82
110 2112 450,00
111 NULL NULL
112 NULL NULL
113 NULL NULL
114 NULL NULL
Puedes consultar aqu las soluciones propuestas.
4. Repetir la consulta pero ahora debe aparecer tambin el total de cunto ha vendido cada empleado. (Recuerda una opcin de la clusula GROUP BY)
Resultado:
rep clie Importe vendido
NULL NULL 2364,386
101 NULL 266,28
101 2102 39,78
101 2108 1,50
101 2113 225,00
102 NULL 77,76
102 2106 40,26
102 2120 37,50
103 NULL 21,00
103 2111 21,00
104 NULL NULL
104 NULL NULL
105 NULL 772,746
105 2103 735,296
105 2111 37,45
106 NULL 46,08
106 2101 14,58
106 2117 31,50
107 NULL 344,32
107 2109 313,50
107 2124 30,82
108 NULL 286,33
108 2112 29,25
108 2114 221,00
108 2118 36,08
109 NULL 71,05
109 2108 71,05
110 NULL 478,82
110 2107 28,82
110 2112 450,00
111 NULL NULL
111 NULL NULL
112 NULL NULL
112 NULL NULL
113 NULL NULL
113 NULL NULL
114 NULL NULL
114 NULL NULL
Puedes consultar aqu las soluciones propuestas.
5. En los resultados anteriores no se distinguen bien las lneas que corresponden a totales. Modificar la consulta para obtener este resultado:
rep clie Importe vendido Agrupa
clie
Agrupa
numemp
NULL NULL 2364,386 1 1
101 NULL 266,28 1 0
101 2102 39,78 0 0
101 2108 1,50 0 0
101 2113 225,00 0 0
102 NULL 77,76 1 0
102 2106 40,26 0 0
102 2120 37,50 0 0
103 NULL 21,00 1 0
103 2111 21,00 0 0
104 NULL NULL 0 0
104 NULL NULL 1 0
... .... ... (sigue) ... ...
6. Puedes consultar aqu las soluciones propuestas.
7. Ahora modifica la consulta para que las filas de totales aparezcan ms claras. (Recuerda la funcin CASE)
Resultado:
rep clie Importe vendido Agrupa clie Agrupa
numemp
NULL NULL 2364,386 Total empleado Total final
101 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) ... ...
Puedes consultar aqu las soluciones propuestas.
8. Ahora coloca las columnas Agrupa delante de las dems columnas:
Resultado:
Agrupa
numemp 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) ... ... .... ...
Puedes consultar aqu las soluciones propuestas.
9. Ahora queremos que "Total empleado" aparezca en la columna clie. Piensa primero en cuntas columnas quieres y luego en cada columna que tiene que salir.
Resultado:
Agrupa
numemp 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) ... .... ...
Puedes consultar aqu las soluciones propuestas.
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:
Agrupa
numemp 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 funcin ISNULL() (para la columna importe vendido) y la funcin 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 vendido
Total 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) ... .... ...
Puedes consultar aqu las soluciones propuestas.
Ayuda ejercicios unidad 5: Consultas de resumen
Ejercicio 1: Funciones de agregado
1. Cuntas oficinas tenemos en Valencia?
SELECT COUNT(*) AS Valencianas
FROM oficinas
WHERE ciudad = 'Valencia';
2. Hallar cuntos pedidos hay de ms de 250 euros.
SELECT COUNT(*) AS [Superiores a 250]
FROM pedidos
WHERE importe > 250;
3. Cuntos ttulos (cargos) de empleados se usan?
SELECT COUNT(DISTINCT titulo) AS [Cuntos ttulos]
FROM empleados;
4. Entre qu cuotas se mueven los empleados?
SELECT MIN(cuota) AS [Cuota mnima], MAX(cuota) AS
[Cuota mxima]
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, cmbialo por 0,00 con la funcin 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 tambin los empleados que no han vendido nada.
SELECT numemp, clie, SUM(importe) AS [Importe vendido]
FROM empleados LEFT JOIN pedidos ON numemp = rep
GROUP BY numemp, clie
ORDER BY numemp,clie;
4. Repetir la consulta pero ahora debe aparecer tambin el total de cunto ha vendido cada empleado.
SELECT numemp, clie, SUM(importe) AS [Importe vendido]
FROM empleados LEFT JOIN pedidos ON numemp = rep
GROUP BY numemp, clie WITH ROLLUP
ORDER BY numemp,clie;
5. En los resultados anteriores no se distinguen bien las lneas 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]
FROM empleados LEFT JOIN pedidos ON numemp = rep
GROUP BY numemp, clie WITH ROLLUP
ORDER BY numemp,clie;
6. Ahora modifica la consulta para que las filas de totales aparezcan ms 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]
FROM empleados LEFT JOIN pedidos ON numemp = rep
GROUP BY numemp, clie WITH ROLLUP
ORDER BY numemp,clie;
7. Ahora coloca las columnas Agrupa delante de las dems 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]
FROM empleados LEFT JOIN pedidos ON numemp = rep
GROUP BY numemp, clie WITH ROLLUP
ORDER BY numemp,clie;
8. Ahora queremos que "Total empleado" aparezca en la columna clie.
SELECT CASE GROUPING(numemp) WHEN 0 THEN ' ' ELSE
'Total Final' END AS [Agrupa numemp],
numemp,
CASE GROUPING(clie) WHEN 0 THEN
CONVERT(CHAR(4),clie) WHEN 1 THEN 'Total empleado' END
AS [Clie],
SUM(importe) AS [Importe vendido]
FROM empleados LEFT JOIN pedidos ON numemp = rep
GROUP BY numemp, clie WITH ROLLUP
ORDER BY numemp,clie;
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.
SELECT CASE GROUPING(numemp) WHEN 0 THEN ' ' ELSE
'Total Final' END AS [Agrupa numemp],
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]
FROM empleados LEFT JOIN pedidos ON numemp = rep
GROUP BY numemp, clie WITH ROLLUP
ORDER BY numemp,clie;
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]
FROM empleados LEFT JOIN pedidos ON numemp = rep
GROUP BY numemp, clie WITH ROLLUP
ORDER BY numemp,clie;
Ejercicios unidad 6: Las subconsultas
Para realizar los ejercicios, debers 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 Lpez
2107 Julian Lpez
2121 Vicente Ros
2125 Pepito Grillo
2. Listar los empleados (numemp, nombre) mayores de 40 aos que dirigen a un vendedor con supervit (ha vendido ms que su cuota).
numemp nombre
106 Luis Antonio
108 Ana Bustamante
110 Juan Victor
3. Listar los empleados (cdigo 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
Puedes consultar aqu las soluciones propuestas.
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 ningn empleado. Intentar primero resolver la consulta utilizando >=ALL.
Probar primero con A Corua:
numemp nombre ventas
102 Alvaro Jaumes 47400,00
111 Juan Gris 60000,00
112 Julin 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 Mara Sunta 39200,00
111 Juan Gris 60000,00
112 Julin 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 ningn empleado.
Ahora para Madrid. Como en Madrid slo hay una oficina y no tiene objetivo no tiene que salir ningn 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 Corua
Puedes consultar aqu las soluciones propuestas.
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 aos que dirigen a un vendedor con supervit (ha vendido ms que su cuota).
SELECT numemp, nombre
FROM empleados
WHERE edad > 40
and numemp IN (SELECT jefe FROM empleados
WHERE ventas > cuota );
3. Listar los empleados (cdigo 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 ningn empleado. Intentar primero resolver la consulta utilizando >=ALL.
Probar primero con A Corua:
SELECT numemp, nombre, ventas
FROM empleados
WHERE ventas >= ALL (SELECT objetivo
FROM oficinas
WHERE ciudad = 'A Corua');
Ahora con Pamplona. Pamplona tiene una oficina con objetivo nulo, en este caso no queremos que esa oficina cuente.
SELECT numemp, nombre, ventas
FROM empleados
WHERE ventas >= ALL (SELECT objetivo
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 ningn empleado. Si no queremos que salgan tendremos que aadir una condicin:
SELECT numemp, nombre, ventas
FROM empleados
WHERE ventas >= ALL (SELECT objetivo
FROM oficinas
WHERE ciudad = 'Barcelona' and
objetivo is not null)
AND EXISTS (SELECT *
FROM oficinas
WHERE ciudad = 'Barcelona');
Ahora para Madrid. Como en Madrid slo hay una oficina y no tiene objetivo no tiene que salir ningn empleado.
SELECT numemp, nombre, ventas
FROM empleados
WHERE ventas >= ALL (SELECT objetivo
FROM oficinas
WHERE ciudad = 'Madrid' and objetivo
is not null)
AND EXISTS (SELECT *
FROM oficinas
WHERE ciudad = 'Madrid' and objetivo
is not null);
Esta sera la consulta definitiva que nos servira para cualquier situacin. Pero como se ve el modificador ALL puede darnos problemas. Para solucionarlo, realizamos la siguiente consulta.
Intentar resolver la consulta sin utilizar ALL.
SELECT numemp, nombre, ventas
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.
SELECT oficina, ciudad
FROM oficinas
WHERE (objetivo * .5) < = (SELECT MIN(ventas)
FROM empleados WHERE
empleados.oficina = oficinas.oficina);
Ejercicios unidad 7: Actualizacin de datos (I)
Para realizar los ejercicios, debers utilizar la base de datos Gestion8.
1. Aadir a la oficina 40 otro empleado, Luis Valverde, con nmero de empleado 436, con los mismos datos que el anterior pero su jefe ser el director de la oficina 40 (no sabemos qu nmero 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 tambin 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 1130
Recommended