• Author / Uploaded
• jjgalactico

• Categories
• Tabla (base de datos)
• SQL
• Archivo de computadora
• Servidor SQL de Microsoft
• Software de gestión de datos

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 través del acceso directo o a través de Inicio, Programas, Microsoft SQL Server 2005, SQL Server Management Studio. Lo primero que deberemos hacer es establecer la conexión con el servidor:

Seleccionamos el nombre del servidor y pulsamos el botón 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 varía según 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 Estándar.

Con las siguientes opciones: 6. Consulta de SQL Server Mobile

1. Nueva consulta 2. Consulta de motor Base de datos 3. Consulta MDX Analysis Services 4. Consulta DMX Analysis Services 5. Consulta MXLA Analysis Services

de de de de

7. Abrir archivo 8. Guardar 9. Guardar todo

11. Resumen 12. Explorador Objetos 13. Explorador Plantillas 14. Ventana Propiedades

10. Servidores registrados

En caso de que utilices la versión Express, es posible que no dispongas de algunos de éstos botones.

Unidad 1. El entorno gráfico SSMS (II)

1.4. Estructura interna de una base de datos Antes de empezar tenemos que tener claro cómo se organiza la información 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 extensión recomendada para los nombres de archivos de datos principales es .mdf.

de de de

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 extensión de nombre de archivo recomendada para los archivos de datos secundarios es .ndf. Además 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 información de registro que se utiliza para recuperar la base de datos, el también denominado registro de transacciones. Como mínimo, tiene que haber un archivo de registro por cada base de datos, aunque puede haber varios. La extensión 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. Sólo las bases de datos de sólo lectura y los grupos de archivos secundarios de sólo 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 después de la carpeta Instantáneas...

Después de la instalación, 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 demás bases de datos forman también parte del diccionario de datos y las utiliza el sistema para llevar a cabo su gestión.

Las bases de datos de los usuarios se deben crear preferentemente fuera de la carpeta Bases de datos del sistema.

Unidad 1. El entorno gráfico SSMS (III) Para crear una nueva base de datos de usuario nos posicionamos sobre la carpeta Bases de datos y con el botón derecho del ratón desplegamos el menú contextual del que elegimos la opción Nueva base de datos…

Se abre a continuación el cuadro de diálogo donde definiremos la base de datos que queremos crear:

Lo mínimo a introducir será el campo Nombre de la base de datos, éste es el nombre de la base de datos lógica, 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 físicos, en la parte inferior aparecen esos archivos. Para facilitarnos la tarea, al teclear el nombre de la bd lógica, se rellenan automáticamente los nombres de los archivos físicos, 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 cambiándolo. Para cada archivo físico podemos definir una serie de parámetros como el tipo de archivo (si es de datos o de transacciones Registro) y su ocupación inicial (Tamaño inicial).

Si no indicamos ninguna ubicación podemos ver que los guarda en la carpeta del SQL Server/MSSQL.n/MSSQL/DATA. n representa un número que puede variar de una instalación a otra.

Estos son los archivos mínimos en los que se almacenará la base de datos, pero como ya vimos anteriormente se puede almacenar en más archivos, los tenemos que definir todos en esta ventana a continuación de los dos obligatorios. Para añadir más archivos físicos disponemos del botón Agregar. Al pulsar el botón Agregar se crea una nueva fila en la tabla de archivos físicos donde deberemos escribir el nombre del archivo, su tipo (desplegando la lista podemos elegir entre de datos o de registro) y demás parámetros.

Al agregar un nuevo archivo se activa el botón 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 gráfico SSMS (IV) Si nos fijamos en la zona de la izquierda, vemos que nos encontramos en la pestaña General, podemos cambiar otros parámetros 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 físicos con los nombres que le hemos indicado.

Unidad 1. El entorno gráfico 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, deberás adjuntar una base de datos ya existente a tu servidor. Para ello, lo que tenemos que hacer es pegar los archivos en la ubicación 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 ubicación y automáticamente se adjuntará la base de datos lógica asociada a este archivo.

Finalmente pulsamos en Aceptar y aparece la base de datos en nuestro servidor.

La opción Adjuntar sólo se utiliza la primera vez, cuando todavía no tenemos la base de datos en el disco. Realiza el siguiente Ejercicio Adjuntar base de datos. En él adjuntarás las bases de datos que vas a utilizar en los ejercicios que se plantearán más 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 información a diferentes programas, por eso sería absurdo que dejara de funcionar cuando cerramos el programa gestor, que sólo 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 opción Poner fuera de conexión:

Aparecerá un símbolo a la izquierda de la base de datos indicándonos 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 opción Poner en conexión:

El caso más inmediato en el que puedes necesitar conectar y desconectar la base de datos es copiar a un pendrive los archivos de las bases que utilizarás en los ejercicios para poder trabajar en diferentes ordenadores con ellos. Para aprender cómo hacerlo, visita el siguiente Ejercicio Trasladar una base de datos a otro equipo. En caso de que tu versión de SQL Server no tenga las opciones Poner en conexión y Poner fuera de conexión, deberás utilizar la opción Separar... y luego volver a adjuntarla.

Unidad 1. El entorno gráfico 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 opción 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 básicas y las demás las veremos con más detalle cuando veamos la instrucción SQL CREATE TABLE. De momento no tenemos definida ninguna columna, al teclear un nombre se crea una primera entrada en esta tabla con la definición de la primera columna. En la columna Tipo de datos elegimos qué tipo de valores se podrán almacenar en la columna.

1.9. Tipos de datos

Podemos elegir entre todos los tipos que aparecen arriba.

Algunos tipos no necesitan más, como por ejemplo el tipo entero (int), y otros se pueden completar con una longitud, como los tipos alfanuméricos:

En este ejemplo hemos definido una columna (Codigo) de tipo Entero corto (Smallint), y una columna (Nombre) que almacenará hasta 20 caracteres alfanuméricos (nchar(20)), en este caso la longitud la indicamos en la pestaña 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 precisión, los valores que permiten definir el tamaño del campo

Unidad 1. El entorno gráfico SSMS (VII)

1.10. Valores nulos También 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 mayoría de los sistemas gestores de bases de datos tenemos un tipo de datos de tipo contador, autonumérico, 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 más filas de datos en la tabla. Las columnas de este tipo se utilizan normalmente para numerar las filas de la tabla, como no habrán 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 numérico y definiendo la columna como columna de identidad. En las propiedades de la columna marcamos Sí en la propiedad (Identidad) y a continuación podemos indicar en qué valor queremos que empiece el contador (Inicialización de identidad) y en cuánto 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 automáticamente esta función, la tenemos que definir nosotros mismos, pero sí fuerza a que sea una columna sin valores nulos. No se puede definir más de una columna de identidad por tabla.

1.12. Clave primaria Para definir una columna como clave primaria, posicionamos el puntero del ratón sobre la columna, desplegamos el menú contextual y seleccionamos la opción Establecer Clave principal:

Aparecerá una llave a la izquierda del nombre, símbolo 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 opción. Para quitar una clave principal, hacemos lo mismo pero en esta ocasión seleccionamos la opción Quitar clave principal.

También podemos utilizar el icono de la barra de herramientas.

Unidad 1. El entorno gráfico SSMS (VIII)

1.13. Añadir o eliminar columnas Una vez definidas algunas columnas, si queremos añadir una nueva columna entre dos, nos posicionamos en la segunda y seleccionamos la opción Insertar columna del menú contextual.

La nueva columna se colocará delante:

Del mismo modo si queremos eliminar la definición de una columna, nos posicionamos en la columna a eliminar y seleccionamos la opción 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 definición de una tabla Para entrar a la ventana de definición de la tabla utilizamos la opción Modificar de su menú contextual (También es posible que se llame Diseño):

Se abrirá la ventana que ya conocemos para definir las columnas de la tabla.

Unidad 1. El entorno gráfico 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 sólo tenemos que rellenar los campos que aparecen en esa fila (la del *), al cambiar de fila los datos se guardarán automáticamente 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 erróneo, si no lo podemos corregir entonces sólo podemos deshacer los cambios.

1.16. Modificar datos Para modificar un valor que ya está en una fila de la tabla sólo tenemos que posicionarnos en el campo y rectificar el valor. En cuanto modificamos un valor, la fila aparece con un lápiz escribiendo (ver imagen), este lápiz

nos indica que la fila se ha modificado y tiene nuevos datos por guardar. Al salir de la fila ésta se guardará automáticamente 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 actualización 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 opción

Eliminar. En cualquiera de los dos casos nos aparece un mensaje de confirmación.

1.18. Relacionar tablas

Como ya hemos visto, en una base de datos relacional, las relaciones entre las tablas se implementan mediante la definición de claves ajenas, que son campos que contienen valores que señalan a un registro en otra tabla, en esta relación así creada, la tabla referenciada se considera principal y la que contiene la clave ajena es la subordinada. Desde el entorno gráfico del SSMS podemos definir claves ajenas entrando en el diseño de la tabla y desplegando el menú contextual del campo que va a ser clave ajena:

Seleccionamos la opción Relaciones y se abre la ventana:

Al pulsar el botón que se encuentra en la fila Especificación de tablas y columnas se abre el diálogo donde definiremos la relación:

Unidad 1. El entorno gráfico SSMS (X) En la parte derecha tenemos la tabla en la que estamos y el campo que va a actuar como clave ajena, sólo 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 relacionarán las tablas. En nuestro caso será:

De esta forma hemos definido una relación 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 opción correspondiente:

Si no tenemos todavía ningún diagrama creado, nos aparece un mensaje:

Elegimos Sí y se crea digamos el soporte donde se pintará el diagrama. A continuación 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 aparecerán en el diagrama las tablas con las relaciones que tengan definidas en ese momento:

La llave indica la tabla principal (padre) y el símbolo infinito señala 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 básico para poder crear una base de datos y rellenarla con tablas relacionadas entre sí y con datos, ahora veamos cómo recuperar esos datos.

Unidad 1. El entorno gráfico SSMS (XI)

1.19. Abrir una nueva consulta Vamos a ver ahora cómo 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 botón de la barra de botones o si queremos realizar la consulta sobre un servidor con el cual todavía no hemos establecido conexión, seleccionando de la barra de menús la opción Nuevo > Consulta de motor de base de datos:

.

En este último caso nos aparecerá el cuadro de diálogo para establecer la conexión (el mismo que vimos al principio del tema). A continuación se abrirá una nueva pestaña donde podremos teclear las sentencias SQL:

Además aparece una nueva barra de botones que nos permitirá ejecutar los comandos más útiles del modo query.

1.20. Escribir y ejecutar código TRANSACT-SQL

Sólo tenemos que teclear la sentencia a ejecutar, por ejemplo empezaremos por crear la base de datos. Utilizaremos la sentencia CREATE DATABASE mínima: CREATE DATABASE ventas; Al pulsar el botón Ejecutar se ejecuta la sentencia y aparece en la parte inferior el resultado de la ejecución, en la pestaña Mensajes:

Si ahora desplegamos la carpeta Bases de Datos del Explorador de Objetos, observaremos la base de datos que hemos creado:

Si la ejecución de la sentencia produce un error, el sistema nos devolverá el mensaje de error escrito en rojo en la pestaña Mensajes.

Podemos incluir en una misma consulta varias sentencias SQL, cuando pulsamos Ejecutar se ejecutarán todas una detrás de otra. Si tenemos varias consultas y sólo queremos ejecutar una, la seleccionaremos antes de ejecutarla.

Unidad 1. El entorno gráfico 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. Fijándonos en la pestaña 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 guión 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 actúe sobre una determinada base de datos y no nos queremos preocupar de qué base de datos tenemos activa podemos añadir al principio de la consulta la instrucción USE nombreBaseDatos; esto hará que todas las instrucciones que aparezcan después, 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 sesión. Para ello, cuando vamos a conectar con el servidor:

Pulsamos en el botón Opciones >>

En la pestaña Propiedades de conexión, 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 gráfico SSMS (XIII)

1.22. El editor de texto Para facilitarnos la redacción y corrección de las sentencias, el editor de SQL presenta las palabras de distintos colores según su categoría 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 código se colorea por categoría. Los colores son los mismos que se utilizan en todo el entorno SQL Server. En esta tabla aparecen los colores más comunes. Color

Categoría

Rojo

Cadena de caracteres

Verde oscuro

Comentario

Negro sobre fondo plateado

Comando SQLCMD

Fucsia

Función del sistema

Verde

Tabla del sistema

Azul

Palabra clave

Verde azulado

Números de línea o parámetro 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 opción Entorno, Fuentes y colores, se puede ver la lista completa de colores y sus categorías, así como configurar un esquema de colores personalizado:

En la lista Mostrar valores para, seleccionamos el entorno que se verá afectado. El botón Usar predeterminados nos permite volver a la configuración predeterminada. Ahora sólo 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 continuación en el apartado sobre vistas.

1.24. Las Vistas

Las consultas que hemos visto hasta ahora son trozos de código SQL que podemos guardar en un archivo de texto y abrir y ejecutar cuando queramos, pero si queremos que nuestra consulta de recuperación 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 lógica almacenada en memoria y lo que se guarda en la base de datos es su definición, la instrucción 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 opción Nueva vista del menú contextual de la carpeta Vistas, se pondrá en funcionamiento el generador de consultas pidiéndonos las tablas en las que se basará la

vista. Pulsamos sobre la tabla a añadir al diseño de la vista y pulsamos el botón Agregar, podemos añadir así cuántas tablas queramos. Después de Cerrar, vemos a la derecha del Explorador de Objetos la pestaña con la definición de la vista que puede incluir varios paneles:

La aparición de estos paneles es configurable, en la barra de herramientas Diseñador de vistas los iconos remarcados en azul son los correspondientes a cada panel:

Unidad 1. El entorno gráfico SSMS (XIV) Nosotros, a lo largo del curso, crearemos las vistas desde el panel SQL que veremos más adelante.

1.25. El panel de diagrama Es el primero que aparece, incluye una representación gráfica 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 relación ha aparecido automáticamente al añadir la segunda tabla. Pero se puede cambiar el tipo de relación eligiendo la opción correspondiente en el menú contextual que aparece con el clic derecho sobre la relación:

Desde el panel diagrama podemos añadir cómodamente 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 visualizarán todas las columnas de la tabla Libros y ninguna de la tabla Préstamos. Conforme vamos marcando casillas de las tablas del panel diagrama, los cambios se ven reflejados en los demás 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 expresión cuando se trata de una columna calculada. En Alias escribimos el nombre que tendrá la columna en la vista, también 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 ordenación y Tipo de orden permiten ordenar las filas del resultado según una o más columnas. Se ordena por las columnas que tienen algo en Tipo de orden y cuando se ordena por varias columnas Criterio de ordenación indica que primero se ordena por la columna que lleva el nº 1 y después por la columna que lleva el nº 2 y así sucesivamente. En el ejemplo las filas del resultado se ordenarán primero por código de libro y después por código de préstamo, todas las filas dentro del mismo libro se ordenarán por código de préstamo.

Si queremos añadir unos criterios de selección tenemos las columnas Filtro y O…

En cada celda indicamos una condición que debe cumplir la columna correspondiente y se puede combinar varias condiciones mediante O (OR) e Y (AND) según coloquemos las condiciones en la misma columna o en columnas diferentes. En el ejemplo anterior tenemos la condición compuesta: ((usuario=1) AND (Dias>5)) OR (Usuario=2). Podemos variar el orden de aparición de las columnas arrastrando la fila correspondiente de la rejilla hasta el lugar deseado. También 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 más complejas como por ejemplo consultas de resumen, pulsando sobre el botón Agrupar por de la barra de herramientas, se añade a la rejilla una nueva columna Agrupar por con las siguientes opciones:

Unidad 1. El entorno gráfico SSMS (XV)

1.27. El panel SQL En él vemos la instrucción SQL generada, también 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 demás paneles. Por defecto el generador añade a la consulta una cláusula TOP (100) PERCENT que indica que se visualizarán el 100% de las filas. Esta cláusula no la hemos definido nosotros sino que la añade automáticamente 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 definición y ejecutarla, igual que con las tablas: Modificar para modificar la definición de la vista Abrir vista para ejecutarla y ver los datos como si fuese una tabla real.

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ÑO

Codigo

Numérico

Byte

Nombre

Texto

60

Editorial

Texto

25

Autor

Texto

25

Genero

Texto

20

PaisAutor

Texto

20

Paginas

Numérico

Entero

AnyEdicion

Fecha/Hora

Fecha mediana

Precio

Moneda

Dias

Numérico

Entero

Tabla Préstamos NOMBRE CAMPO

TIPO DE DATOS

TAMAÑO

Codigo

Autonumérico

Libro

Numérico

Byte

Usuario

Numérico

Byte

FSalida

Fecha/Hora

Fecha mediana

FMaxima

Fecha/Hora

Fecha mediana

FDevol

Fecha/Hora

Fecha mediana

TIPO DE DATOS

TAMAÑO

Tabla Usuarios NOMBRE CAMPO

Codigo

Autonumérico

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

Numérico

Entero

Puedes consultar aquí las soluciones propuestas.

Ayuda ejercicios unidad 1: El entorno gráfico 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 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.

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 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 campo Usuario 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 (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 definición 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 ningún nulo en esa columna).

Modifica la definición de las tablas para incorporar esta información. Puedes consultar aquí las soluciones propuestas.

Ayuda ejercicios unidad 1: El entorno gráfico 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 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.

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 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 campo Usuario 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 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: Nume Nombre Eda Ofici mp d na

Titulo

Contrato Jefe Cuota ventas

101

Antonio Viguer

4 5

12

represen tante

198610-20 00:00:0 0.000

10 4

3000 0,00

3050 0,00

102

Alvaro Jaume s

4 8

21

represen tante

198612-10 00:00:0 0.000

10 8

3500 0,00

4740 0,00

103

Juan Rovira

2 9

12

represen tante

198703-01 00:00:0 0.000

10 4

2750 0,00

2860 0,00

104

José Gonzál ez

3 3

12

dir ventas

198705-19 00:00:0 0.000

10 6

2000 0,00

1430 0,00

105

Vicente Pantall a

3 7

13

represen tante

198802-12 00:00:0

10 4

3500 0,00

3680 0,00

0.000 106

Luis Antonio

5 2

11

director general

198806-14 00:00:0 0.000

NU LL

2750 0,00

2990 0,00

107

Jorge Gutiérr ez

4 9

22

represen tante

198811-14 00:00:0 0.000

10 8

3000 0,00

1860 0,00

108

Ana Bustam ante

6 2

21

dir ventas

198910-12 00:00:0 0.000

10 6

3500 0,00

3610 0,00

109

María Sunta

3 1

NU LL

represen tante

199910-12 00:00:0 0.000

10 6

3000, 00

3920 0,00

110

Juan Victor

4 1

NU LL

represen tante

199001-13 00:00:0 0.000

10 4

NULL

7600, 00

111

Juan Gris

5 0

NU LL

represen tante

200505-01 00:00:0 0.000

NU LL

1000 0,00

6000 0,00

112

Julián Martore ll

5 0

NU LL

represen tante

200605-01 00:00:0 0.000

NU LL

1000 0,00

9100 0,00

113

Juan Gris

1 8

NU LL

represen tante

200701-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 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

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

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é 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 101

nombre Antonio Viguer

3.

102

Alvaro Jaumes

103

Juan Rovira

105

Vicente Pantalla

106

Luis Antonio

107

Jorge Gutiérrez

110

Juan Victor

111

Juan Gris

112

Julián Martorell

113

Juan Gris

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

Puedes consultar aquí las soluciones propuestas.

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;

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: Codi Numpe Fechape Clie Re Fab Produ Ca Impo Prec descrip go dido dido p cto nt rte io cion 1

11003 6

198910-12 00:00:0 0.000

21 07

1 1 0

ac i

410 0z

9

22, 50

NU LL

mont

2

11003 7

198910-12 00:00:0 0.000

21 17

1 0 6

rei

2a4 4l

7

31, 50

45, 00

bomb al

3

11296 3

200805-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

199001-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

200802-11 00:00:0 0.000

21 11

1 0 3

rei

2a4 4g

6

21, 00

3,5 0

pas

6

11297 9

198910-12 00:00:0 0.000

21 14

1 0 8

ac i

410 0z

6

150 ,00

NU LL

mont

7

11298 3

200805-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

200801-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

200812-10 00:00:0 0.000

21 01

1 0 6

fe a

114

6

14, 58

2,4 3

cubo

10

11299 2

199004-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

200803-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

200804-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

200802-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

200801-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

200805-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

200812-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

200807-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

200802-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

200811-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

200801-01 00:00:0 0.000

21 13

1 0 1

rei

2a4 4r

5

225 ,00

45, 00

bomb ar

21

11304 5

200807-02 00:00:0 0.000

21 12

1 1 0

rei

2a4 4r

1 0

450 ,00

45, 00

bomb ar

22

11304 8

200802-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

200804-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

200807-06 00:00:0 0.000

21 18

1 0 8

qs a

xk47

4

14, 20

3,5 5

red

25

11305 5

200904-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

200811-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

198907-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

200807-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

200806-03 00:00:0 0.000

21 06

1 0 2

qs a

xk47

6

21, 30

3,5 5

red

30

11306 9

200808-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 Fechape Clie Rep Fab Produ Ca Preci Client vended ido dido cto nt o e 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 Gutiér 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 Numpe Fechape Clie Re Fab Produ Ca Impo Clien Vended go dido dido p cto nt rte te or asignad o 8

1129 87

200801-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

200802-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

200807-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

200808-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 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 Llorens

105

Juan Victor

NULL

113045

2112

110

María Silva

110

Ana Bustamante

Badajoz

113069

2109

107

Alberto Juanes

107

Juan Rovira

Alicante

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

Resultado: Numpedid Fechapedido Rep Numem o 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 día que fueron contratados.

Resultado: Numem p 108

Nombre

Contrato

Ana Bustamant e

1989-10-12 00:00:00.00 0

Numpedid Rep fechapedido o 112979

10 8

1989-10-12 00:00:00.00 0

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

ciudad

101

Antonio Viguer

30500,00

12

Alicante

102

Alvaro Jaumes

47400,00

21

Badajoz

103

Juan Rovira

28600,00

12

Alicante

104

José González

14300,00

12

Alicante

105

Vicente Pantalla

36800,00

13

Castellon

106

Luis Antonio

29900,00

11

Valencia

107

Jorge Gutiérrez

18600,00

22

A Coruña

108

Ana Bustamante

36100,00

21

Badajoz

109

María Sunta

39200,00

NULL

NULL

110

Juan Victor

7600,00

NULL

NULL

111

Juan Gris

60000,00

NULL

NULL

112

Julián 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 número, nombre y cuota del empleado y número, nombre y cuota de su jefe.

Resultado: Numemp

Nombre

Cuota

Jefe

Nombre jefe Cuota jefe

101

Antonio Viguer

30000,00

104

José González

20000,00

103

Juan Rovira

27500,00

104

José González

20000,00

105

Vicente Pantalla

35000,00

104

José González

20000,00

108

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 jefe

101

Antonio Viguer

30000,00

104

José González

20000,00

103

Juan Rovira

27500,00

104

José González

20000,00

105

Vicente Pantalla

35000,00

104

José González

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 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 jefe

104

José González

12

106

Luis Antonio

11

105

Vicente Pantalla

13

104

José González

12

107

Jorge Gutiérrez

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é González

12

106

Luis Antonio

11

105

Vicente Pantalla

13

104

José González

12

107

Jorge Gutiérrez

22

108

Ana Bustamante

21

108

Ana Bustamante

21

106

Luis Antonio

11

109

María Sunta

NULL

106

Luis Antonio

11

110

Juan Victor

NULL

104

José González

12

111

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: Numem p

Nombre

Oficin Ciudad Jefe a

Nombre jefe

Oficin ciudad a jefe

104

José González

12

Alicant e

10 6

Luis Antonio

11

Valenc ia

105

Vicente Pantalla

13

Castell on

10 4

José González

12

Alicant e

107

Jorge Gutiérrez

22

A Coruña

10 8

Ana Bustaman te

21

Badajo z

108

Ana Bustaman te

21

Badajo z

10 6

Luis Antonio

11

Valenc ia

109

María Sunta

NUL L

NULL

10 6

Luis Antonio

11

Valenc ia

110

Juan Victor

NUL L

NULL

10 4

José González

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 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 idfab AND producto = idproducto;

fab =

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 numemp) 6.

INNER

JOIN

clientes

ON

rep = 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 numclie) 10. repclie = numemp WHERE importe >

INNER

JOIN

clie =

empleados

ON

250;

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, clientes.nombre AS cliente, empleados.nombre AS vendedor, ciudad 13. FROM ((pedidos clie = numclie)

INNER

14. repclie = numemp)

INNER

JOIN JOIN

15. LEFT JOIN empleados.oficina=oficinas.oficina

WHERE importe >

rep, repclie,

clientes

ON

empleados

ON

oficinas

ON

250;

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

17. SELECT numpedido, numemp, nombre, contrato 18.

fechapedido,

rep,

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, numpedido, rep, fechapedido

contrato,

21. FROM pedidos INNER JOIN empleados ON = numemp

rep

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 empleados.oficina=oficinas.oficina;

ON

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 empleados.jefe = jefes.numemp;

jefes

ON

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. ON 29.

FROM empleados INNER JOIN empleados jefes empleados.jefe = jefes.numemp WHERE empleados.cuota > jefes.cuota

(empleados.cuota jefes.cuota IS NULL)

IS

NOT

NULL

OR AND

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 NULL OR j.oficina IS NULL;

OR

e.oficina

IS

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 JOIN oficinas ofijefe ofijefe.oficina) 40. j.numemp

(empleados j LEFT ON j.oficina = ON

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

e.jefe

OR

e.oficina

=

IS

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, deberás utilizar la base de datos GestionSimples.

Ejercicio 1: Funciones de agregado 1. ¿Cuántas oficinas tenemos en Valencia?

Resultado: Valencianas 4

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

Resultado: Superiores a 250 4

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

Resultado: Cuántos títulos 3

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

Resultado: Cuota mínima Cuota máxima 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 función ISNULL().

Resultado: numemp

nombre

Importe vendido

101

Antonio Viguer

266,28

102

Alvaro Jaumes

77,76

103

Juan Rovira

21,00

104

José González

0,00

105

Vicente Pantalla

772,746

106

Luis Antonio

46,08

107

Jorge Gutiérrez

344,32

108

Ana Bustamante

286,33

109

María Sunta

71,05

110

Juan Victor

478,82

111

Juan Gris

0,00

112

Julián 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 también 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 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 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 líneas que corresponden a totales. Modificar la consulta para obtener este resultado: Importe vendido

Agrupa Agrupa clie numemp

NULL

2364,386

1

1

101

NULL

266,28

1

0

101

2102

39,78

0

0

101

2108

1,50

0

0

rep

clie

NULL

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 más claras. (Recuerda la función CASE)

Resultado: rep

clie

Importe vendido

Agrupa clie

NULL

NULL

2364,386

Total empleado

101

NULL

266,28

Total empleado

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

Total empleado

Total empleado

Agrupa numemp Total final

104

NULL

NULL

104

NULL

NULL

Total empleado

...

....

... (sigue)

...

...

Puedes consultar aquí las soluciones propuestas. 8. Ahora coloca las columnas Agrupa delante de las demás columnas:

Resultado: Agrupa numemp Total final

Agrupa clie

rep

clie

Total empleado

NULL

NULL

2364,386

Total empleado

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

Total empleado

104

NULL

NULL

...

...

....

...

Total empleado

Total empleado

... (sigue)

Puedes consultar aquí las soluciones propuestas.

Importe vendido

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: Agrupa numemp numemp Total final

... (sigue)

clie

Importe vendido

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

...

....

...

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

... (sigue)

0,00

104

Total empleado

0,00

...

....

...

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 Total final

clie

Importe vendido

...

...

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

... (sigue)

0,00

104

Total empleado

0,00

...

....

...

Puedes consultar aquí las soluciones propuestas.

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 WHERE importe > 250; 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) [Cuota máxima]

AS

[Cuota

mínima],

MAX(cuota)

AS

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, [Importe vendido]

ISNULL(SUM(importe),0)

AS

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] FROM empleados LEFT JOIN pedidos ON numemp = rep 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] 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 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] 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 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] 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 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] 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 empleado' WHEN clie IS NULL THEN CONVERT(CHAR(4),clie) END AS [Clie],

THEN 'Total ' ' ELSE

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) 'Total Final' END AS [ ],

WHEN

0

THEN

'

'

ELSE

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

')

CASE WHEN GROUPING(numemp) = 0

1

THEN

GROUPING(clie) 'Total

=

empleado'

AS AND WHEN

GROUPING(clie) = 1 AND GROUPING(numemp) = 1 THEN '...' WHEN clie IS NULL THEN CONVERT(CHAR(4),clie) END AS [Clie],

'

'

ELSE

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, 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

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

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 clie AND importe > 300);

numclie

=

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). SELECT numemp, nombre 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) pedidos);

>

(SELECT

AVG(importe)

FROM

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.

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 ningún empleado. Si no queremos que salgan tendremos que añadir una condición: SELECT numemp, nombre, ventas FROM empleados WHERE ventas >= ALL (SELECT objetivo FROM oficinas WHERE objetivo is not null)

ciudad

=

'Barcelona'

and

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. 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 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. 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 empleados.oficina = oficinas.oficina);

WHERE

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

Puedes consultar aquí las soluciones propuestas. 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 importe

precio pedido

aci

41001

NULL

NULL

NULL

NULL

NULL

NULL

aci

41002

NULL

10

112992

1990-04-15 20:00:00.000

7,60

0,76

aci

41002

NULL

18

113027

2008-02-05 00:00:00.000

450,00

8,3333

aci

41003

NULL

15

113012

2008-05-05 00:00:00.000

37,45

1,07

aci

41004

NULL

3

112963

2008-05-10 00:00:00.000

3,276

0,117

aci

41004

NULL

4

112968

1990-01-11 00:00:00.000

39,78

1,17

aci

41004

NULL

7

112983

2008-05-10 00:00:00.000

7,02

1,17

aci

4100x

NULL

25

113055

2009-04-01 00:00:00.000

1,50

0,25

aci

4100x

NULL

26

113057

2008-11-01 00:00:00.000

NULL

NULL

aci

4100y

NULL

8

112987

2008-01-01 00:00:00.000

275,00

25,00

aci

4100z

NULL

1

110036

2008-12-12 00:00:00.000

22,50

2,50

aci

4100z

NULL

6

112979

2008-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 Puedes consultar aquí las soluciones propuestas.

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, contrato, ventas, cuota, oficina, jefe)

titulo,

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 fechapedido=DATEADD(month,230,fechapedido)

SET

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 producto = idproducto

productos

ON

fab=idfab

and

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

and

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 producto = idproducto

productos

ON

fab=idfab

and

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, numpedido,fechapedido,importe, importe/cant FROM productos left idproducto=producto

join

pedidos

on

codigo,

idfab=fab

AND

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

Ayuda ejercicios unidad 7: Actualización de datos (II) 5. Eliminar los pedidos del representante Luis Antonio. (2 filas afectadas) DELETE FROM pedidos WHERE rep IN (SELECT numemp FROM empleados WHERE nombre = 'Luis Antonio');

6. Eliminar las oficinas que no tengan empleados. (11 filas afectadas) DELETE oficinas WHERE NOT EXISTS (SELECT * FROM empleados empleados.oficina = oficinas.oficina);

WHERE

7. Intenta eliminar el empleado 102. Te saldrá un error : "Instrucción DELETE en conflicto con la restricción..." DELETE empleados WHERE numemp=102; Reflexiona sobre el error y elabora una consulta que liste los empleados que pueden dar problemas (aquí no se trata de utilizar operaciones aprendidas en este tema sino de practicar la integridad referencial y de paso recordar instrucciones vistas en temas anteriores). SELECT * from empleados WHERE numemp IN (select jefe from empleados) UNION ALL SELECT * FROM empleados WHERE numemp IN (SELECT dir FROM oficinas) UNION ALL SELECT * FROM empleados WHERE numemp IN (SELECT rep FROM pedidos) UNION ALL SELECT * FROM empleados WHERE numemp IN (SELECT repclie FROM clientes) ORDER BY numemp Elabora ahora una consulta que liste los que se pueden borrar sin problemas. SELECT numemp FROM empleados EXCEPT SELECT numemp from empleados

WHERE numemp IN (select jefe from empleados) EXCEPT SELECT numemp FROM empleados WHERE numemp IN (SELECT dir FROM oficinas) EXCEPT SELECT numemp FROM empleados WHERE numemp IN (SELECT rep FROM pedidos) EXCEPT SELECT numemp FROM empleados WHERE numemp IN (SELECT repclie FROM clientes) ORDER BY numemp Borra el primero de la lista, éste sí lo puedes borrar. DELETE empleados WHERE numemp=112; 8. Eliminar los pedidos de productos de ACI cuyo precio de venta en el pedido no corresponda con el precio unitario del producto de la tabla de productos. (4 filas afectadas) DELETE FROM pedidos WHERE fab = 'ACI' AND Importe/cant (SELECT precio FROM productos WHERE fab = idfab AND idproducto = producto); 9. Ahora vamos a recuperar las tablas tal cual estaban al principio, para ello utilizaremos las copias realizadas al principio de Nuevaoficinas, etc. Lo más cómodo será vaciar las tablas y rellenarlas de nuevo con los datos de las tablas Nueva... Empieza por eliminar los datos de las tablas. Utilizamos TRUNCATE en la tabla pedidos porque no está referenciada y para que el contador empiece desde 1 otra vez TRUNCATE TABLE pedidos DELETE productos DELETE clientes

UPDATE oficinas SET dir = NULL; -- Para poder borrar los empleados UPDATE empleados SET oficina=NULL, jefe=NULL; -- Para poder borrar las oficinas y los empleados respectivamente. DELETE oficinas; DELETE empleados;

Ayuda ejercicios unidad 7: Actualización de datos (III) Inserta los datos de las tablas Nueva.. a las tablas normales. INSERT INTO oficinas region,objetivo,ventas) SELECT region,objetivo,ventas

(oficina,

ciudad,

oficina,

ciudad,

FROM Nuevaoficinas; No podemos rellenar todavía la columna Dir ya que no tenemos los empleados dado de alta. INSERT INTO empleados (numemp, oficina,titulo,contrato,cuota,ventas)

nombre,

SELECT numemp, nombre, oficina,titulo,contrato,cuota,ventas

edad, edad,

FROM NuevaEmpleados; Lo mismo pasa con la columna jefe. UPDATE empleados SET jefe=nueva.jefe FROM empleados INNER JOIN NuevaEmpleados empleados.numemp=nueva.numemp

nueva

Estas dos últimas sentencias se podían haber resumido en una: INSERT INTO empleados SELECT *

ON

FROM NuevaEmpleados; Las hemos dejado en dos porque con otros SQLs no se podría hacer en un sólo paso. UPDATE oficinas SET Nuevaoficinas nueva

dir

=

(SELECT

dir

FROM

WHERE oficinas.oficina=nueva.oficina)

INSERT INTO (numclie,nombre,repclie,limitecredito)

clientes

SELECT numclie,nombre,repclie,limitecredito FROM Nuevaclientes;

INSERT INTO productos SELECT * FROM Nuevaproductos;

INSERT INTO pedidos (numpedido,fechapedido,clie,rep,fab,producto,cant,impor te) SELECT numpedido,fechapedido,clie,rep,fab,producto,cant,import e FROM Nuevapedidos;

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. Puedes consultar aquí las soluciones propuestas.

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; por defecto de SQL Server.

-- La crea en la carpeta

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

(NAME='Gestion2'

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 edad

CHAR(20) NULL, INT NULL,

oficina titulo contrato

INT NULL, CHAR(20) NULL, datetime NULL,

jefe INT NULL CONSTRAINT REFERENCES dbo.empleados, cuota ventas

MONEY MONEY

FK_empleados_jefe

NULL, NULL

) CREATE TABLE dbo.oficinas( oficina

INT NOT NULL,

ciudad

CHAR(20) NULL,

region

CHAR(20) NULL,

dir INT

NULL,

objetivo MONEY ventas

NULL,

MONEY NULL

CONSTRAINT d1

DEFAULT

(0), CONSTRAINT PK_oficinas PRIMARY CONSTRAINT FK_oficinas_dir REFERENCES empleados

KEY (oficina), FOREIGN

KEY

(dir)

) ALTER TABLE dbo.empleados FK_empleados_oficina

WITH CHECK ADD CONSTRAINT

FOREIGN KEY(oficina) REFERENCES dbo.oficinas CREATE TABLE

dbo.clientes(

numclie INT NOT nombre

(oficina)

NULL,

CHAR(20) NULL,

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

limitecredito INT NULL, PK_clientes PRIMARY KEY (numclie)

CONSTRAINT

) 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 (idfab,idproducto)

PK_productos

PRIMARY

KEY

) CREATE TABLE dbo.pedidos( codigo numpedido

INT IDENTITY NOT NULL, DEC(10,0) NOT NULL,

fechapedido DATETIME

NOT NULL,

clie

INT NOT NULL,

rep

INT NOT NULL,

fab

CHAR(5) NOT NULL,

producto cant importe CONSTRAINT

CHAR(10) NOT NULL, SMALLINT NULL, MONEY NULL, PK_pedidos PRIMARY KEY

(codigo),

CONSTRAINT FK_pedidos_rep FOREIGN KEY REFERENCES empleados, CONSTRAINT FK_pedidos_clie FOREIGN REFERENCES clientes, CONSTRAINT FK_pedidos_productos (fab,producto) REFERENCES productos)

(rep)

KEY (clie) FOREIGN

KEY

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

IX_descripcion

ON

Productos

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

IX_nombre ON Clientes (nombre)

bien

ALTER TABLE Clientes ADD (nombre);

CONSTRAINT IX_nombre UNIQUE

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 Esto de todas formas

clie INT NULL;

--

-- 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 --o bien (según se haya ALTER TABLE Clientes DROP

Clientes; resuelto el apartado 5 CONSTRAINT IX_nombre;

8. Elimina la base de datos Gestion2, así podrás volver a hacer los ejercicios. USE Gestion DROP DATABASE Gestion2; GO

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. Puedes consultar aquí las soluciones propuestas.

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 0;

StockMinimo INTEGER DEFAULT

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 Apedir=StockMinimo+5-existencias

SET

FROM ProductosAPedir INNER JOIN Productos ON idfab=Afab AND idproducto=Aproducto WHERE existencias
@ventas WHERE

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

GO DECLARE @resultado INT EXEC Listado_Oficinas2 Este, 1000 ,@resultado OUTPUT SELECT @resultado EXEC OUTPUT

Listado_Oficinas2

PRINT @resultado

Este,

100000

,@resultado

EXEC Listado_Oficinas2 Norte, 0 ,@resultado OUTPUT PRINT @resultado EXEC OUTPUT

Listado_Oficinas2

Norte,

10000

,@resultado

PRINT @resultado

Ayuda ejercicios unidad 9: Programación en TRANSACT SQL (II) 4. Crear un nuevo procedimiento parecido al anterior pero que nos devuelva también el número de oficinas recuperadas. Sin utilizar parámetros de salida PRINT 'Empieza el ejercicio 4' USE Gestion10 IF OBJECT_ID('Listado_Oficinas3','P') DROP PROCEDURE Listado_Oficinas3

IS

NOT

NULL

GO CREATE PROCEDURE CHAR(20),@ventas MONEY

Listado_Oficinas3

@region

AS SELECT * FROM oficinas WHERE region= @region AND ventas > @ventas RETURN (SELECT COUNT(*) FROM region= @region AND ventas > @ventas)

oficinas

WHERE

GO DECLARE @resultado INT EXEC @resultado=Listado_Oficinas3 Este, 1000 SELECT @resultado EXEC @resultado=Listado_Oficinas3 Este, 100000 PRINT @resultado EXEC @resultado=Listado_Oficinas3 Norte, 0 PRINT @resultado EXEC @resultado=Listado_Oficinas3 Norte, 10000 PRINT @resultado 5. Crea un procedimiento que muestre los n productos más caros, n es un valor que se indicará en la llamada.

PRINT 'Empieza el ejercicio 5' USE Gestion10 IF OBJECT_ID('ProductosMasCaros','P') DROP PROC ProductosMasCaros

IS

NOT

NULL

GO CREATE PROCEDURE

ProductosMasCaros @num int

AS SELECT TOP Idproducto,idfab,descripcion,precio FROM

(@num)

Productos

ORDER BY Precio DESC; GO EXEC ProductosMasCaros 5 EXEC ProductosMasCaros 10 6. Crear otro procedimiento que muestre los n productos más caros con empates y nos devuelva cuántos hay. PRINT 'Empieza el ejercicio 6' USE Gestion10 IF OBJECT_ID('ProductosMasCaros2','P') DROP PROC ProductosMasCaros2

IS

NOT

NULL

GO CREATE PROCEDURE

ProductosMasCaros2 @num int

AS SELECT TOP (@num) Idproducto,idfab,descripcion,precio FROM ORDER

WITH

TIES

Productos BY Precio DESC;

RETURN @@ROWCOUNT -- variable del sistema que devuelve el número de filas de la última operación. DECLARE @resul INT EXEC @resul=ProductosMasCaros2 13

SELECT @resul AS Resultado EXEC @resul=ProductosMasCaros2 10 SELECT @resul AS

Resultado

7. No dejar eliminar productos que tengan existencias. PRINT 'Empieza el ejercicio 7' USE Gestion10 IF OBJECT_ID('Controlexistencias','P') DROP PROC Controlexistencias

IS

NOT

NULL

GO CREATE TRIGGER

Controlexistencias

ON Productos INSTEAD OF DELETE -- Tenemos que comprobar el stock antes de que se realice el borrado AS BEGIN DELETE

Productos

FROM Productos P INNER JOIN Deleted D P.idfab=D.idfab AND P.idproducto=D.idproducto

ON

-- Aquí hay que utilizar alias de tablas porque los campos se llaman igual en las 2 tablas WHERE D.existencias 0 el producto borra.

tiene stock, no se

END; GO SELECT 'Antes',* FROM productos WHERE idfab='bic' AND idproducto=41003; -- Para comprobar las existencias del producto DELETE Productos WHERE idfab='bic' AND idproducto=41003; -- un producto que tenga existencias -- Intentamos borrar el producto. SELECT 'Después DELETE',* FROM idfab='bic' AND idproducto=41003;

productos

WHERE

-- Comprobamos que el producto sigue en la tabla DELETE Productos WHERE idfab='bic' AND idproducto=41672; -- un producto que no tiene stock (existencias=0) -- Intentamos borrar el producto. SELECT 'Después DELETE',* FROM idfab='bic' AND idproducto=41672;

productos

WHERE

-- Comprobamos que el producto ya no está en la tabla

Ayuda ejercicios unidad 9: Programación en TRANSACT SQL (III) 8. Hacer que no se pueda insertar un pedido si no hay suficiente stock. PRINT 'Empieza el ejercicio 8' USE Gestion10 IF OBJECT_ID('ControlStock','P') PROC ControlStock

IS

NOT

NULL

DROP

GO CREATE TRIGGER ON pedidos comprobar el inserción

ControlStock

INSTEAD OF INSERT -- Tenemos stock antes de que se realice

que la

AS BEGIN INSERT INTO Pedidos (numpedido,fechapedido,rep,clie,fab,producto,cant,impor te) SELECT numpedido,fechapedido,rep,clie,fab,producto,cant,import e FROM Productos INNER JOIN Inserted idfab=fab AND idproducto=producto WHERE existencias-cant

>= 0

ON

suficiente pedido.

-- Si existencias-cant < 0 es que no hay stock, por lo que no se insertará el

END; GO SELECT 'Antes',* FROM productos WHERE idfab='bic' AND idproducto=41003; -- Para comprobar las existencias del producto INSERT INTO Pedidos (numpedido,fechapedido,rep,fab,producto,clie,cant,impor te) VALUES (010101123,GETDATE(),108,'bic',41003,2103,10,1000); del

-- Intentamos insertar un pedido de 10 unidades producto en cuestion (no hay suficiente stock).

SELECT 'Después INSERT',* FROM WHERE idfab='bic' AND idproducto=41003; -- Comprobamos que el campo ha reducido

productos

existencia no se

SELECT 'Después INSERT',* FROM pedidos WHERE numpedido=010101123; -introducido

Comprobamos

que

el

pedido

no

se

ha

INSERT INTO Pedidos (numpedido,fechapedido,rep,fab,producto,clie,cant,impor te) VALUES (010101123,GETDATE(),108,'bic',41003,2103,2,1000); -- Intentamos insertar un pedido de 2 unidades del producto en cuestion (hay suficiente stock). SELECT 'Después INSERT',* FROM WHERE idfab='bic' AND idproducto=41003;

productos

-- Comprobamos que el campo existencia se ha reducidoen 2 unidades (por el otro trigger) SELECT 'Después INSERT',* FROM pedidos WHERE numpedido=010101123; -- Comprobamos que el pedido ahora sí se ha introducido

9. Crea un procedimiento que impida eliminar varias oficinas en una sóla operación DELETE. PRINT 'Empieza el ejercicio 9' USE Gestion10 IF OBJECT_ID('Borrarvarias','P') PROC Borrarvarias

IS

NOT

NULL

DROP

GO CREATE TRIGGER Borrarvarias ON Oficinas INSTEAD OF

DELETE

AS BEGIN IF (SELECT COUNT(*) FROM Deleted) (SELECT oficina

DELETE Oficinas FROM Deleted)

= 1

WHERE

oficina

IN

ELSE PRINT 'ERROR, varias oficinas a la vez'

no

se

pueden

eliminar

END; GO DELETE Oficinas WHERE oficina intentamos eliminar 2 oficinas SELECT 'Después

DELETE',* FROM

IN

(31,32)

--

Oficinas;

-- Comprobamos que las oficinas siguen en la tabla DELETE Oficinas WHERE eliminar 1 oficina SELECT 'Después

oficina = 31

DELETE',* FROM

-- intentamos

Oficinas;

-- Comprobamos que la oficina ya no está

Unidad 2. Introducción al SQL. Transact-SQL (I)

2.1. Conceptos básicos de SQL

SQL (Structured Query Language), Lenguaje Estructurado de Consulta es el lenguaje utilizado para definir, controlar y acceder a los datos almacenados en una base de datos relacional. Como ejemplos de sistemas gestores de bases de datos que utilizan SQL podemos citar DB2, SQL Server, Oracle, MySql, Sybase, PostgreSQL o Access. El SQL es un lenguaje universal que se emplea en cualquier sistema gestor de bases de datos relacional. Tiene un estándar definido, a partir del cual cada sistema gestor ha desarrollado su versión propia. En SQL Server la versión de SQL que se utiliza se llama TRANSACTSQL. EL SQL en principio es un lenguaje orientado únicamente a la definición y al acceso a los datos por lo que no se puede considerar como un lenguaje de programación como tal ya que no incluye funcionalidades como son estructuras condicionales, bucles, formateo de la salida, etc. (aunque veremos que esto está evolucionando). Se puede ejecutar directamente en modo interactivo, pero también se suele emplear embebido en programas escritos en lenguajes de programación convencionales. En estos programas se mezclan las instrucciones del propio lenguaje (denominado anfitrión) con llamadas a procedimientos de acceso a la base de datos que utilizan el SQL como lenguaje de acceso. Como por ejemplo en Visual Basic, Java, C#, PHP .NET, etc. Las instrucciones SQL se clasifican según su propósito en tres grupos: El DDL (Data Description Language) Lenguaje de Descripción de Datos. El DCL (Data Control Language) Lenguaje de Control de Datos. El DML (Data Manipulation Language) Lenguaje de Manipulación de Datos.

El DDL, es la parte del SQL dedicada a la definición de la base de datos, consta de sentencias para definir la estructura de la base de datos, permiten crear la base de datos, crear, modificar o eliminar la estructura de las tablas, crear índices, definir reglas de validación de datos, relaciones entre las tablas, etc. Permite definir gran parte del nivel interno de la base de datos. Por este motivo estas sentencias serán utilizadas normalmente por el administrador de la base de datos. El DCL (Data Control Language) se compone de instrucciones que permiten: Ejercer un control sobre los datos tal como la asignación de privilegios de acceso a los datos (GRANT/REVOKE). La gestión de transacciones (COMMIT/ROLLBACK).

Una transacción se puede definir como un conjunto de acciones que se tienen que realizar todas o ninguna para preservar la integridad de la base de datos. Por ejemplo supongamos que tenemos una base de datos para las reservas de avión. Cuando un usuario pide reservar una plaza en un determinado vuelo, el sistema tiene que comprobar que queden plazas libres, si quedan plazas reservará la que quiera el usuario generando un nuevo billete y marcando la plaza como ocupada. Aquí tenemos un proceso que consta de dos operaciones de actualización de la base de datos (crear una nueva fila en la tabla de billetes y actualizar la plaza reservada en el vuelo, poniéndola como ocupada) estas dos operaciones se tienen que ejecutar o todas o ninguna, si después de crear el billete no se actualiza la plaza porque se cae el sistema, por ejemplo, la base de datos quedaría en un estado inconsistente ya que la plaza constaría como libre cuando realmente habría un billete emitido para esta plaza. En este caso el sistema tiene el mecanismo de transacciones para evitar este error. Las operaciones se incluyen las dos en una misma transacción y así el sistema sabe que las tiene que ejecutar las dos, si por lo que sea no se pueden ejecutar las dos, se encarga de deshacer los cambios que se hubiesen producido para no ejecutar ninguna. Las instrucciones que gestionan las autorizaciones serán utilizadas normalmente por el administrador mientras que las otras, referentes a proceso de transacciones serán utilizadas también por los programadores. No todos los sistemas disponen de ellas. El DML se compone de las instrucciones para el manejo de los datos, para insertar nuevos datos, modificar datos existentes, para eliminar datos y la más utilizada, para recuperar datos de la base de datos. Veremos que una sola instrucción de recuperación de datos es tan potente que permite recuperar datos de varias tablas a la vez, realizar cálculos sobre estos datos y obtener resúmenes. El DML interactúa con el nivel externo de la base de datos por lo que sus instrucciones son muy parecidas, por no decir casi idénticas, de un sistema a otro, el usuario sólo indica lo que quiere recuperar no cómo se tiene que recuperar, no influye el cómo están almacenados los datos. Es el lenguaje que utilizan los programadores y los usuarios de la base de datos. A lo largo del curso se explicarán cada una de las formas de explotación de la base de datos. Dependiendo de tu perfil profesional (programador o administrador) o de tu interés personal te resultará más útil un bloque u otro.

2.2. Introducción al TRANSACT-SQL

Como hemos dicho, el sistema gestor de base de datos SQL-Server 2005 utiliza su propia versión del lenguaje SQL, el TRANSACT-SQL. TRANSACT-SQL es un lenguaje muy potente que nos permite definir casi cualquier tarea que queramos efectuar sobre la base de datos. En este tema veremos que TRANSACT-SQL va más allá de un lenguaje SQL cualquiera ya que incluye características propias de cualquier lenguaje de programación, características que nos permiten definir la lógica necesaria para el tratamiento de la información: Tipos de datos. Definición de variables. Estructuras de control de flujo. Gestión de excepciones. Funciones predefinidas.

Sin embargo no permite: Crear interfaces de usuario. Crear aplicaciones ejecutables, sino elementos que en algún momento llegarán al servidor de datos y serán ejecutados.

Debido a estas restricciones se emplea generalmente para crear procedimientos almacenados, triggers y funciones de usuario. Puede ser utilizado como cualquier SQL como lenguaje embebido en aplicaciones desarrolladas en otros lenguajes de programación como Visual Basic, C, Java, etc. Y por supuesto los lenguajes incluidos en la plataforma .NET. También lo podremos ejecutar directamente de manera interactiva, por ejemplo desde el editor de consultas de SSMS (SQL Server Management Studio) el entorno de gestión que ya conocemos. Esta es la forma en que lo utilizaremos nosotros.

Unidad 2. Introducción al SQL. Transact-SQL (II)

2.3. Características generales del lenguaje Transact-SQL El lenguaje SQL se creó con la finalidad de ser un lenguaje muy potente y a la vez muy fácil de utilizar, se ha conseguido en gran medida ya que con una sola frase (instrucción) podemos recuperar datos complejos (por ejemplo datos que se encuentran en varias tablas, combinándolos, calculando resúmenes), y utilizando un lenguaje muy cercano al lenguaje hablado (¡suponiendo que hablamos inglés, claro!).

Por ejemplo: SELECT codigo, nombre localidad=’Valencia’;

FROM

Clientes

WHERE

Esta instrucción nos permite SELECCIONAR el código y nombre DE los Clientes CUYA localidad sea Valencia. La sencillez también radica en que lo que indicamos es lo que queremos obtener, no el cómo lo tenemos que obtener, de eso se encargará el sistema automáticamente. Las sentencias SQL además siguen todas el mismo patrón: Empiezan por un verbo que indica la acción a realizar, completado por el objeto sobre el cual queremos realizar la acción, seguido de una serie de cláusulas (unas obligatorias, otras opcionales) que completan la frase, y proporcionan más detalles acerca de lo que se quiere hacer.

Si sabemos algo de inglés nos será más fácil interpretar a la primera lo que quiere decir la instrucción, y de lo contrario, como el número de palabras que se emplean es muy reducido, enseguida nos las aprenderemos. Por ejemplo en el DDL (acciones sobre la definición de la base de datos), tenemos 3 verbos básicos: CREATE (Crear) DROP (Eliminar) ALTER (Modificar) Completados por el tipo de objeto sobre el que actúan y el objeto concreto: CREATE DATABASE mibase .......; Permite crear una base de datos llamada mibase, a continuación escribiremos las demás cláusulas que completarán la acción, en este caso dónde se almacenará la base de datos, cuánto ocupará, etc... CREATE TABLE mitabla (.....); Permite crear una nueva tabla llamada mitabla, entre paréntesis completaremos la acción indicando la definición de las columnas de la tabla. CREATE INDEX miindex...;

Lo mismo para crear un índice (¿a que lo habíais adivinado?). DROP DATABASE

mibase;

Permite borrar, eliminar la base de datos mibase. DROP TABLE mitabla; Elimina la tabla mitabla. ALTER TABLE mitabla.....; Permite modificar la definición de la tabla mitabla. En el DML (acciones sobre los datos almacenados) utilizaremos los verbos: INSERT (Crear, es decir, insertar una nueva fila de datos) DELETE (Eliminar filas de datos) UPDATE (Modificar filas de datos) SELECT (Seleccionar, obtener) Por ejemplo: INSERT INTO mitabla ..... Inserta nuevas filas en mitabla DELETE FROM mitabla Eliminar filas de mitabla UPDATE mitabla ....... Actualiza filas de mitabla Como ejemplo de cláusula dentro de una instrucción tenemos: SELECT codigo, FROM WHERE localidad=’Valencia’;

nombre Clientes

En esta sentencia nos aparecen dos cláusulas, la cláusula FROM que nos permite indicar de dónde hay que coger los datos y la cláusula WHERE que permite indicar una condición de selección. Otra característica de una sentencia SQL es que acaba con un punto y coma (;) originalmente éste era obligatorio y servía para indicar el fin de la instrucción, pero ahora se puede omitir, aunque se recomienda su uso. En una sentencia utilizaremos palabras reservadas (las fijas del lenguaje), y nombres de objetos y variables (identificadores).

Las palabras reservadas no se pueden utilizar para otro propósito, por ejemplo una tabla no se puede llamar FROM, y los nombres (los identificadores) siguen las reglas detalladas en el punto siguiente. Nombres cualificados. En ocasiones deberemos utilizar nombres cualificados, por ejemplo cuando se escribe un nombre de tabla, SQL presupone que se está refiriendo a una de las tablas de la base de datos activa, si queremos hacer referencia a una tabla de otra base de datos utilizamos su nombre cualificado nombrebasedatos.nombredeesquema.nombretabla, utilizamos el punto para separar el nombre del objeto y el nombre de su contenedor. O por ejemplo si en una consulta cuyo origen son dos tablas, queremos hacer referencia a un campo y ese nombre de campo es un nombre de campo en las dos tablas, pues utilizaremos su nombre cualificado nombretabla.nombrecampo. El valor NULL. Puesto que una base de datos es un modelo de una situación del mundo real, ciertos datos pueden inevitablemente faltar, ser desconocidos o no ser aplicables, esto se debe de indicar de alguna manera especial para no confundirlo con un valor conocido pero que sea cero por ejemplo, SQL tiene para tal efecto el valor NULL que indica precisamente la ausencia de valor. Por ejemplo: no es lo mismo que el alumno no tenga nota a que tenga la nota cero, esto afectaría también a todos los cálculos que se pueden realizar sobre la columna nota.

Unidad 2. Introducción al SQL. Transact-SQL (III)

2.4. Reglas de formato de los identificadores Los identificadores son los nombres de los objetos de la base de datos. Cualquier elemento de Microsoft SQL Server 2005 puede tener un identificador: servidores, bases de datos, tablas, vistas, columnas, índices, desencadenadores, procedimientos, restricciones, reglas, etc. Las reglas de formato de los identificadores normales dependen del nivel de compatibilidad de la base de datos, que se establecía con el parámetro sp_dbcmptlevel pero que ahora Microsoft aconseja no utilizar ya que desaparecerá en versiones posteriores en vez de eso se tiene que utilizar la cláusula SET COMPATIBILITY_LEVEL de la instrucción ALTER TABLE. Cuando el nivel de compatibilidad es 90, (el asignado por defecto) se aplican las reglas siguientes para los nombres de los identificadores: No puede ser una palabra reservada.

El nombre debe tener entre 1 y 128 caracteres, excepto para algunos tipos de objetos en los que el número es más limitado. El nombre debe empezar por: o

Una letra, como aparece definida por el estándar Unicode 3.2. La definición Unicode de letras incluye los caracteres latinos de la "a" a la "z" y de la "A" a la "Z".

o

El carácter de subrayado ( _ ), arroba ( @ ) o número ( # ).

Ciertos símbolos al principio de un identificador tienen un significado especial en SQL Server. Un identificador que empieza con el signo de arroba indica un parámetro o una variable local. Un identificador que empieza con el signo de número indica una tabla o procedimiento temporal. Un identificador que empieza con un signo de número doble (##) indica un objeto temporal global. Algunas funciones de Transact-SQL tienen nombres que empiezan con un doble signo de arroba (@@). Para evitar confusiones con estas funciones, se recomienda no utilizar nombres que empiecen con @@. No se permiten los caracteres especiales o los espacios incrustados.

Si queremos utilizar un nombre que no siga estas reglas, normalmente para poder incluir espacios en blanco, lo tenemos que escribir encerrado entre corchetes [ ] (también se pueden utilizar las comillas pero recomendamos utilizar los corchetes).

2.5. Tipos de datos En SQL Server 2005, cada columna, expresión, variable y parámetro está asociado a un tipo de datos. Un tipo de datos, realmente define el conjunto de valores válidos para los campos definidos de ese tipo. Indica si el campo puede contener: datos numéricos, de caracteres, moneda, fecha y hora, etc. SQL Server proporciona un conjunto de tipos de datos del sistema que define todos los tipos de datos que pueden utilizarse. También podemos definir nuestros propios tipos de datos en Transact-SQL o Microsoft .NET Framework. Los tipos de datos más utilizados son: Los numéricos: int, decimal, money Los de fecha y hora: datetime Y las cadenas de caracteres: varchar

Si quieres conocer todos los tipos de datos disponibles en SQLServer 2005, visita el siguiente avanzado .

2.6. Las constantes Una constante es un valor específico o un símbolo que representa un valor de dato específico. El formato de las constantes depende del tipo de datos del valor que representan. En este apartado veremos las más utilizadas. Las constantes numéricas se escriben mediante una cadena de números, con la consideración de que el separador decimal es un punto, no una coma, y que si se trata de un valor monetario deberemos incluir la moneda al inicio de la constante. Por ejemplo: 85.90 y €85.90, el primero sería un valor decimal y el segundo un valor money. De forma predeterminada, los valores serán positivos. Para indicar lo contrario escribimos el signo - al principio. Las constantes de fecha y hora van entre comillas simples y con un formato de fecha y hora adecuado. Por ejemplo: '03/10/90'. Y las constantes en cadenas de caracteres van entre comillas simples. Por ejemplo: 'Juan García López'.

Para indicar valores negativos y positivos añadimos el prefijo + o según sea el valor positivo o negativo. Sin prefijo se entiende que el valor es positivo. Si quieres ver cómo definir constantes para otros tipos de datos, visita el siguiente avanzado .

Unidad 2. Introducción al SQL. Transact-SQL (IV)

2.7. Las expresiones Una expresión es una combinación de símbolos y operadores que el motor de base de datos de SQL Server evalúa para obtener un único valor. Una expresión simple puede ser una sola constante, variable, columna o función escalar. Los operadores se pueden usar para combinar dos o más expresiones simples y formar una expresión compleja. Dos expresiones pueden combinarse mediante un operador si ambas tienen tipos de datos admitidos por el operador y se cumple al menos una de estas condiciones: Las expresiones tienen el mismo tipo de datos. El tipo de datos de menor prioridad se puede convertir implícitamente al tipo de datos de mayor prioridad. La función CAST puede convertir explícitamente el tipo de datos con menor prioridad al tipo de datos con mayor prioridad o a un tipo de datos intermedio que pueda convertirse implícitamente al tipo de datos con la mayor prioridad.

Tipos de operadores: - Operadores numéricos: suma

+

resta

-

multiplicación

*

división

/

módulo (resto de una división)

%

- Operadores bit a bit: realizan manipulaciones de bits entre dos expresiones de cualquiera de los tipos de datos de la categoría del tipo de datos entero. AND

&

OR

|

OR exclusivo

^

- Operadores de comparación: Igual a

=

Mayor que

>

Menor que


=

Menor o igual que

- Operadores lógicos:

Aquí sólo los nombraremos ya que en el tema de consultas simples los veremos en detalle. ALL

IN

AND

LIKE

ANY

NOT

BETWEEN

OR

EXISTS

SOME

- Operadores de cadenas: Concatenación

+

Resultados de la expresión - Si se combinan dos expresiones mediante operadores de comparación o lógicos, el tipo de datos resultante es booleano y el valor es uno de los siguientes: TRUE, FALSE o UNKNOWN. - Cuando dos expresiones se combinan mediante operadores aritméticos, bit a bit o de cadena, el operador determina el tipo de datos resultante. Las expresiones complejas formadas por varios símbolos y operadores se evalúan como un resultado formado por un solo valor. El tipo de datos, intercalación, precisión y valor de la expresión resultante se determina al combinar las expresiones componentes de dos en dos, hasta que se alcanza un resultado final. La prioridad de los operadores de la expresión define la secuencia en que se combinan las expresiones.

Unidad 2. Introducción al SQL. Transact-SQL (V)

2.8. Funciones SQL Server 2005 proporciona numerosas funciones integradas y permite crear funciones definidas por el usuario. Existen diferentes tipos de funciones: Funciones de conjuntos de filas: devuelven un objeto que se puede utilizar, en instrucciones Transact-SQL, en lugar de una referencia a una tabla.

Funciones de agregado (también llamadas funciones de columna): Operan sobre una colección de valores y devuelven un solo valor de resumen. Por ejemplo, la función de suma sobre la columna importe para conocer el importe total: SUM(importe) Funciones de categoría: Devuelven un valor de categoría para cada fila de un conjunto de filas, por ejemplo devuelve el número de la fila, el ranking de la fila en una determinada ordenación, etc. Funciones escalares: Operan sobre un valor y después devuelven otro valor. Son las funciones que estamos acostumbrados a utilizar. Las funciones escalares se clasifican según el tipo de datos de sus operandos

Las variables En Transact-SQL podemos definir variables, que serán de un tipo de datos determinado, como tipos de datos podemos utilizar los propios de la base de datos SQL-SERVER, pero también podemos utilizar tipos propios del lenguaje que no pueden ser utilizados en DDL. El tipo Cursor y el tipo Table son dos de estos tipos. Las variables se definen utilizando la instrucción DECLARE con el siguiente formato: DECLARE @nbvariable tipo El nombre de la variable debe empezar por el símbolo @, este símbolo hace que SQL interprete el nombre como un nombre de variable y no un nombre de objeto de la base de datos. Por ejemplo: DECLARE @empleados INT Con esto hemos definido la variable @empleados de tipo entero. Para asignar un valor a una variable, la asignación se realiza con la palabra SELECT y el signo igual con el formato: SELECT @nbvariable = valor El valor puede ser cualquier valor constante, otro nombre de variable, una expresión válida o algo más potente, parte de una sentencia SELECT de SQL. Por ejemplo: SELECT @empleados = 0; SELECT @empleados = @otra * 100; SELECT @EMPLEADOS = COUNT(numemp) FROM empleados;

El valor almacenado en la variable se puede visualizar mediante la orden PRINT. o SELECT PRINT @nbvariable o SELECT @nbvariable El valor almacenado en la variable se visualizará en la pestaña de resultados. También se puede usar para escribir mensajes: PRINT 'Este es el mensaje'

Otros elementos del lenguaje Comentarios. Como en cualquier otro lenguaje de programación, debemos utilizar comentarios destinados a facilitar la legibilidad del código. En SQL se insertan comentarios con los signos: /* */

Varias líneas

--

Una línea

única

/* Esto es en varias líneas */ -- Esto

un

comentario

es un comentario en una única

línea.

USE. Cambia el contexto de la base de datos al de la base de datos especificada. USE nbBaseDeDatos Hace que la base de datos activa pase a ser la base de datos indicada en la instrucción, las consultas que se ejecuten a continuación se harán sobre tablas de esa base de datos si no se indica lo contrario. Es una instrucción útil para asegurarnos de que la consulta se ejecuta sobre la base de datos correcta. GO GO no es una instrucción Transact-SQL, sino un comando reconocido por las utilidades sqlcmd y osql, así como por el Editor de código de SQL Server Management Studio. Las utilidades de SQL Server interpretan GO como una señal de que deben enviar el lote actual de instrucciones Transact-SQL a una instancia de SQL Server. El lote actual de instrucciones está formado por todas las instrucciones específicadas desde el último comando GO o desde el comienzo de la sesión o script si se trata del primer comando GO. Por ejemplo si queremos crear una consulta para crear una base de datos y sus tablas, después del CREATE DATABASE…; tenemos que poner GO antes del primer CREATE TABLE para que el sistema efectúe la primera operación y la base de datos esté creada antes de ejecutar el primer CREATE TABLE.

BEGIN...END Encierra un conjunto de instrucciones Transact-SQL de forma que estas instrucciones formen un bloque de instrucciones.

Unidad 3. Consultas simples (I) 3.1. Introducción Vamos a empezar por la instrucción que más se utiliza en SQL, la sentencia SELECT. La sentencia SELECT es, con diferencia, la más compleja y potente de las sentencias SQL, con ella podemos recuperar datos de una o más tablas, seleccionar ciertos registros e incluso obtener resúmenes de los datos almacenados en la base de datos. Es tan compleja que la estudiaremos a lo largo de varias unidades didácticas incorporando poco a poco nuevas funcionalidades. El resultado de una SELECT es una tabla lógica que alberga las filas resultantes de la ejecución de la sentencia. La sintaxis completa es la siguiente: SELECT sentencia::=[WITH [,...n]]



[ORDER BY {expression_columna|posicion_columna [ASC|DESC] } [ ,...n ]] [COMPUTE {{AVG|COUNT|MAX|MIN|SUM} ,...n ] [BY expression[ ,...n ]]

(expression)}[

] [] [OPTION ([ ,...n ])] ::= { expresion_consulta > ) }

|

(


] Debido a la complejidad de la sentencia (en la sintaxis anterior no se han detallado algunos elementos), la iremos viendo poco a poco, empezaremos por ver consultas básicas para luego ir añadiendo más cláusulas. Empezaremos por ver las consultas más simples, basadas en una sola tabla y nos limitaremos a la siguiente sintaxis: SELECT [ALL|DISTINCT] [TOP expresion [PERCENT] [WITH TIES]]

FROM [WHERE

]

[ORDER {expression_columna|posicion_columna [ ,...n ]]

BY [ASC|DESC]}

3.2. Origen de datos FROM De la sintaxis anterior, el elemento indica de dónde se va a extraer la información y se indica en la cláusula FROM, es la única cláusula obligatoria. En este tema veremos un origen de datos basado en una sola tabla. La sintaxis será la siguiente: ::= nb_tabla | nb_vista [[ AS ] alias_tabla ] nb_tabla representa un nombre de tabla. nb_vista un nombre de vista. Tanto para las tablas como para las vistas, podemos hacer referencia a tablas que están en otras bases de datos (siempre que tengamos los permisos adecuados), en este caso tenemos que cualificar el nombre de la tabla, indicando delante el nombre de la base de datos (Lógica) y el nombre del esquema al que pertenece la tabla dentro de la base de datos. Por ejemplo: MiBase.dbo.MiTabla se refiere a la tabla MiTabla que se encuentra en el esquema dbo de la base de datos MiBase. Cuando no se definen esquemas, SQL-Server crea uno por defecto en cada base de datos denominado dbo. Opcionalmente podemos definir un nombre de alias. Un nombre de alias (alias_tabla) es un nombre alternativo que se le da a la tabla dentro de la consulta. Si se define un nombre de alias, dentro de la consulta, será el nombre a utilizar para referirnos a la tabla, el nombre original de la tabla ya no tendrá validez.

Se utilizan los nombres de alias para simplificar los nombres de tablas a veces largos y también cuando queremos combinar una tabla consigo misma; ya volveremos sobre los alias de tabla cuando veamos consultas multitabla. La palabra AS no añade ninguna operatividad, está más por estética. Podemos escribir: SELECT ... FROM tabla1 tabla1

Sacamos los datos de la tabla

SELECT ... FROM tabla1 t1 Sacamos los datos de la tabla tabla1 y le asignamos un alias de tabla: t1 SELECT ... FROM tabla1 AS t1 sentencia anterior.

Es

equivalente

a

la

Si la tabla o la vista están en otra base de datos del mismo equipo que está ejecutando la instancia de SQL Server, se utiliza el nombre cualificado con el formato nbBaseDatos.nbEsquema.nbTabla. Si la tabla o la vista están fuera del servidor local en un servidor vinculado, se utiliza un nombre de cuatro partes con el formato nbservidor.catalogo.nbEsquema.nbTabla. Volveremos más adelante sobre las conexiones remotas.

Unidad 3. Consultas simples (II) 3.3. La lista de selección En la lista de selección indicamos las columnas que se tienen que visualizar en el resultado de la consulta. ::=

{

*

| {nombre_tabla|nombre_vista|alias_tabla}.* | [{nombre_tabla|nombre_vista|alias_tabla}.]

{

{nb_columna|$IDENTITY|$ROWGUID} | }[[AS] alias_columna] | alias_columna = } [ ,...n ]

Separamos la definición de cada columna por una coma y las columnas del resultado aparecerán en el mismo orden que en la lista de selección. Para cada columna del resultado su tipo de datos, tamaño, precisión y escala son los mismos que los de la expresión que da origen a esa columna. Podemos definir las columnas del resultado de varias formas, mediante: Una expresión simple: o

una referencia a una función.

o

una variable local

o

una constante

o

una columna del origen de datos,

Una subconsulta escalar, que es otra instrucción SELECT que devuelve un único valor y se evalúa para cada fila del origen de datos (esto no lo veremos de momento). Una expresión compleja generada al usar operadores en una o más expresiones simples. La palabra clave *.

La asignación de variables con el formato @variable_local = expresión. La palabra clave $IDENTITY. La palabra clave $ROWGUID.

3.4. Columnas del origen de datos Cuando queremos indicar en la lista de selección una columna del origen de datos, la especificamos mediante su nombre simple o nombre cualificado. El nombre cualificado consiste en el nombre de la columna precedido del nombre de la tabla donde se encuentra la columna. Si en el origen de datos hemos utilizado una vista o un nombre de alias, deberemos utilizar ese nombre. Es obligatorio utilizar el nombre cualificado cuando el nombre de la columna aparece en más de una tabla del origen de datos. Ejemplos de consulta simple. Listar nombres, oficinas, y fechas de contrato de todos los empleados: SELECT FROM

nombre, oficina, contrato empleados;

El resultado sería: nombre

oficina

contrato

Antonio Viguer

12

1986-10-20

Alvaro Jaumes

21

1986-12-10

Juan Rovira

12

1987-03-01

José González

12

1987-05-19

Vicente Pantalla

13

1988-02-12

Luis Antonio

11

1988-06-14

Jorge Gutiérrez

22

1988-11-14

Ana Bustamante

21

1989-10-12

María Sunta

11

1999-10-12

Juan Victor

NULL

1990-01-13

Listar una tarifa de productos: SELECT idfab, productos.precio FROM

idproducto,

descripcion,

productos;

Hemos cualificado la columna precio aunque no es necesario en este caso. El resultado sería: Idfab

idproducto

descripcion

precio

aci

41001

arandela

0,58

aci

41002

bisagra

0,80

aci

41003

art t3

1,12

aci

41004

art t4

1,23

aci

4100x

junta

0,26

aci

4100y

extractor

28,88

aci

4100z

mont

26,25

bic

41003

manivela

6,52

bic

41089

rodamiento

2,25

Unidad 3. Consultas simples (III) 3.5. Alias de columna Por defecto, en el encabezado de cada columna del resultado, aparece el nombre de la columna origen, pero esto se puede cambiar definiendo un alias de columna, el alias de columna es un nombre alternativo que se le da a esa columna. El alias de columna se indica mediante la cláusula AS. Se escribe el nuevo texto tal cual sin comillas siguiendo las reglas de los identificadores. Ejemplo: SELECT numclie,nombre AS FROM clientes; El resultado será : Numclie nombrecliente 2101

Luis García Antón

2102

Alvaro Rodríguez

2103

Jaime Llorens

en vez de: Numclie

nombre

2101

Luis García Antón

2102

Alvaro

nombrecliente

Rodríguez 2103

Jaime Llorens

La palabra AS es opcional. SELECT numclie,nombre nombrecliente FROM clientes; Sería equivalente a la consulta anterior Si queremos incluir espacios en blanco en el nombre lo debemos encerrar entre corchetes. SELECT numclie,nombre AS [nombre cliente] FROM clientes; Nota importante: Este nombre de alias se podrá utilizar en la lista de selección y en la cláusula ORDER BY pero no en la cláusula WHERE.

3.6. Funciones Existen funciones que podemos utilizar en la lista de selección, e incluso en otras cláusulas que veremos más adelante, como el WHERE. Las principales funciones son las siguientes: Funciones de fecha: Función

Descripción

GETDATE

Devuelve la fecha actual.

GETUTCDATE

Devuelve la hora UTC.

DATEPART

Devuelve un entero que corresponde a la parte de la fecha

solicitada. DAY

Devuelve el día de la fecha indicada.

MONTH

Devuelve el mes de la fecha indicada.

YEAR

Devuelve el año de la fecha indicada.

DATENAME

Devuelve una cadena de caracteres que representa el valor de la unidad especificada de una fecha especificada.

DATEADD

Devuelve un valor datetime nuevo que resulta de sumar un intervalo de tiempo a una fecha especificada.>

DATEDIFF

Devuelve el nº de intervalos que hay entre dos fechas.

@@DATEFIRST

Devuelve el primer día de la semana establecido con SET DATEFIRST.

SET DATEFIRST

Establece el primer día de la semana en un número del 1 al 7.

Unidad 3. Consultas simples (IV) Funciones de cadena: Función

Descripción

ASCII

Devuelve el valor de código ASCII del carácter situado más a la izquierda de una expresión de caracteres.

CHAR

Devuelve el carácter ASCII del entero indicado.

NCHAR

Devuelve el carácter Unicode del entero indicado.

UNICODE

Devuelve el entero que se corresponde al carácter Unicode indicado.

LEN

Devuelve el total de caracteres de una cadena, excluidos los espacios en blanco finales.

LTRIM

Devuelve una cadena tras quitarle los espacios en blanco iniciales.

RTRIM

Devuelve una cadena tras quitarle los espacios en blanco finales.

LEFT

Devuelve los N últimos caracteres de una cadena.

RIGHT

Devuelve los N primeros caracteres de una cadena.

SUBSTRING

Devuelve parte de una expresión.

LOWER

Devuelve la cadena convertida a minúsculas.

UPPER

Devuelve la cadena convertida a mayúsculas.

REPLACE

Reemplaza una determinada cadena.

STUFF

Elimina el número de caracteres especificado e inserta otro conjunto de caracteres en el punto de inicio indicado.

QUOTENAME

Devuelve una cadena Unicode con los delimitadores agregados para convertirla en un identificador delimitado válido de Microsoft SQL Server 2005.

SPACE

Devuelve una cadena de espacios repetidos.

STR

Devuelve una cadena de caracteres a partir de datos numéricos.

REPLICATE

Repite una cadena N veces.

REVERSE

Devuelve una cadena invertida.

CHARINDEX

Devuelve la posición inicial de la expresión especificada en una cadena de caracteres.

PATINDEX

Devuelve la posición inicial de la primera repetición de un patrón en la expresión especificada, o ceros si el patrón no se

encuentra, en todos los tipos de datos de texto y caracteres. Otras funciones: Función

Descripción

ROUND CAST CONVERT

Redondea un valor a la longitud y precisión indicadas. y

Convierten de un tipo de datos a otro de forma explícita.

CASE

Evalúa una lista de condiciones.

ISNULL

Reemplaza el valor NULL por otro especificado.

COALESCE

Devuelve la primera expresión distinta de NULL entre sus argumentos.

Unidad 3. Consultas simples (V) 3.7. Columnas calculadas Además de las columnas que provienen directamente de la tabla origen, una consulta SQL puede incluir columnas calculadas cuyos valores se evalúan a partir de una expresión. La expresión puede contener cualquier operador válido (+, -, *, /, &…), cualquier función válida, nombres de columnas del origen de datos, nombres de parámetros o constantes y para combinar varias operaciones se pueden utilizar los paréntesis. Ejemplos de columnas calculadas: Listar la ciudad, región y el superávit de cada oficina. Consideraremos el superávit como el volumen de ventas que se encuentran por encima o por debajo del objetivo de la oficina. SELECT ciudad, superavit

region,

(ventas-objetivo)

AS

FROM

oficinas;

El resultado será: ciudad

region

superavit

Valencia

este

11800,00

Alicante

este

-6500,00

Castellon

este

1800,00

Badajoz

oeste

11100,00

A Coruña

oeste

-11400,00

Madrid

centro

NULL

Madrid

centro

-10000,00

Pamplona

norte

NULL

Valencia

este

-90000,00

De cada producto queremos saber el id de fabricante, id de producto, su descripción y el valor de sus existencias. SELECT idfab,idproducto,descripcion,(existencias*precio) AS valoracion FROM productos; El resultado sería: Idfab idproducto

descripcion

valoracion

aci

41001

arandela

160,66

aci

41002

bisagra

133,60

aci

41003

art t3

231,84

aci

41004

art t4

170,97

aci

4100x

junta

9,62

aci

4100y

extractor

722,00

aci

4100z

mont

735,00

bic

41003

manivela

19,56

bic

41089

rodamiento

175,50

Para practicar puedes realizar este Ejercicio Columna calculada.

Unidad 3. Ejercicio paso a paso: Columna calculada Nota: Para realizar los ejercicios tienes que abrir una nueva consulta sobre la base de datos GestionSimples. En el Explorador de objetos selecciona la base de datos y elige Nueva Consulta. En la carpeta Ejercicios del curso tienes el PDF Tablas_Gestion para consultar en qué tablas está cada campo.

Objetivo Mostrar una columna que sea el resultado de un cálculo aritmético.

Ejercicio paso a paso Listar de cada pedido, su número, código completo del artículo vendido y precio unitario al que se ha vendido. No es necesario redondear el precio. La información precio unitario no se encuentra en la tabla, pero sí que disponemos del importe total del pedido y la cantidad, así que podemos calcularlo.

SELECT numpedido, fab, producto, importe/cant AS Precio FROM pedidos; Si quisiéramos redondear el precio a dos decimales, por ejemplo, podríamos englobar la operación importe/cant en la función ROUND() de la siguiente forma: ROUND(valor, nºdecimales) --> ROUND(importe/cant, 2) Resultado (sin redondear): Numpedido Fab Producto Precio 110036

aci

4100z

2,50

110037

rei

2a44l

4,50

112963

aci

41004

0,117

112968

aci

41004

1,17

112975

rei

2a44g

3,50

112979

aci

4100z

25,00

112983

aci

41004

1,17

112987

aci

4100y

25,00

112989

fea

114

2,43

112992

aci

41002

0,76

112993

rei

2a45c

0,79

112997

bic

41003

6,52

113003

imm

779c

18,75

113007

imm

773c

9,75

113012

aci

41003

1,07

113013

bic

41003

6,52

113024

qsa

xk47

3,55

113027

aci

41002

8,3333

113034

rei

2a45c

0,79

113042

rei

2a44r

45,00

113045

rei

2a44r

45,00

113048

imm

779c

18,75

113049

qsa

xk47

3,88

113051

qsa

xk47

3,55

113055

aci

4100x

0,25

113057

aci

4100x

NULL

113058

fea

112

1,48

113062

bic

41003

2,43

113065

qsa

xk47

3,55

113069

imm

773c

14,25

De cada producto queremos saber el id de fabricante, id de producto, su descripción y el valor de sus existencias. El valor de las existencias es un campo calculado, tendremos que multiplicar el número de existencias por su precio para ver en cuánto se valora el stock. SELECT idfab,idproducto,descripcion,(existencias*precio) AS valoracion

FROM productos; Resultado: Idfab Idproducto Descripción Valoracion aci

41001

arandela

NULL

aci

41002

bisagra

NULL

aci

41003

art t3

NULL

aci

41004

art t4

NULL

aci

4100x

junta

NULL

aci

4100y

extractor

NULL

aci

4100z

mont

NULL

bic

41003

manivela

19,56

bic

41089

rodamiento

175,50

bic

41672

plato

0,00

fea

112

cubo

170,20

fea

114

cubo

36,45

imm 773c

reostato

273,00

imm 775c

reostato 2

71,25

imm 779c

reostato 3

0,00

imm 887h

caja clavos

120,42

imm 887p

perno

6,00

imm 887x

manivela

152,00

qsa

xk47

red

134,90

qsa

xk48

red

272,02

qsa

xk48a

red

54,76

rei

2a44g

pas

49,00

rei

2a44l

bomba l

540,00

rei

2a44r

bomba r

540,00

rei

2a45c

junta

165,90

http://www.aulaclic.es/sqlserver/t_3_6.htm co9ntinuar salta este