¡Descarga Consultas SQL en la base de datos Northwind y más Apuntes en PDF de Programación de Bases de Datos solo en Docsity!
Base de Datos Nortwind
Resumen del desarrollo de la práctica.
Analizar la información almacenada en la base de datos NorthWind haciendo uso de las
distintas funciones de MS-SQL server.
Desarrollo de la práctica.
Comentarios En una línea
USE northwind SELECT unitprice, (unitprice * 1.1), -- precio incrementado en 10% productname FROM products GO
Comentarios En un bloque
/* Este código devuelve todas las filas de la tabla products y muestra el precio por unidad, el
precio umentado en un 10 por ciento y el nombre del producto. */
USE northwind SELECT unitprice, (unitprice * 1.1), productname FROM products GO
Variables
Sintaxis DECLARE { @ variableLocal tipoDatos} [,... n ] SET @nombreVariableLocal =
expresión
En este ejemplo se crean las variables locales @EmpID y @vlname , se asigna un valor a
@vlname y, a continuación, se asigna un valor a @EmpID al consultar en la base de datos
NorthWind para seleccionar el registro que contiene el valor de la variable local @vlname.
USE northwind DECLARE @EmpID varchar(11) ,@vlName char(20) SET @vlname = 'Dodsworth' SELECT @EmpID = employeeid FROM employees WHERE LastName = @vlname SELECT @EmpID AS EmployeeID
Funciones del sistema
Ejemplo 1
En este ejemplo se determina el promedio de la columna unitprice de todos los productos de
la tabla products.
USE northwind SELECT AVG(unitprice) AS AvgPrice FROM products
Ejemplo 2
En este ejemplo de función de metadatos se devuelve el nombre de la base de datos que se
está utilizando actualmente.
USE northwind SELECT DB_NAME() AS 'database'
Ejemplo 3
En este ejemplo se demuestra cómo puede convertir fechas a distintos estilos.
SELECT 'ANSI:', CONVERT (varchar(30), GETDATE(), 102) AS Style UNION SELECT 'Japanese:', CONVERT(varchar(30), GETDATE(), 111) UNION SELECT 'European:', CONVERT(varchar(30), GETDATE(), 113)
Ejemplo 4
En este ejemplo se utiliza la opción DATEFORMAT de la instrucción SET para dar formato
a las fechas de la duración de una conexión. Esta configuración sólo se utiliza en la
interpretación de las cadenas de caracteres cuando se convierten a valores de fecha. No tiene
efecto al mostrar los valores de fecha.
SET DATEFORMAT dmy DECLARE @vdate datetime SET @vdate = '29/11/98' SELECT @vdate
Nota : En el ejercicio 15 la función EXEC ejecútala por separado, del bloque de consulta del
CREATE PROC porque es la forma de mandar a llamar a ese procedimiento que ya creaste.
El @ es el parámetro que espera ese procedimiento para ejecutar la consulta que lleva dentro.
El doble guión es la forma de comentar líneas en el Analizador de consultas.
Ejemplo 10
Realice el siguiente ejercicio.
--Creación de BD CREATE Database ventas GO USE ventas GO ---tabla vendedor CREATE TABLE VENDEDOR( idvendedor INT PRIMARY KEY , nombre VARCHAR(20), apellido VARCHAR(10), ); GO ---tabla ventas CREATE TABLE VENTAS( Idventa INT PRIMARY KEY , nombre VARCHAR(20), apellido VARCHAR(10), idvendedor INT foreign key (idvendedor) references VENDEDOR(idvendedor))
Ejemplo 11
Realice los siguientes ejercicios sobre la Base de Datos Nortwind
--INSTRUCCIONES SELECT ELEMENTALES--
--1. Mostrar todas (*) las columnas de una tabla (from). select * from dbo.Products
--2. Mostrar solo las columnas especificadas. select ProductID, ProductName, UnitPrice, UnitsInStock from dbo.Products
--3. Incluir (ALL) filas duplicadas (valor predeterminado). select all * from dbo.Products -- Es exactamente lo mismo que la instrucción 1.
--4. No incluir (DISTINCT) filas duplicadas. select distinct * from dbo.Products
--5. Renombrar (AS) columnas. select ProductID as Identificador, ProductName as [Nombre Producto], Unit Price as [Precio Unidad], UnitsInStock as UnidadesStock from dbo.Products
--6. Renombrar (AS) tablas. select * from dbo.Products as Productos
--INSTRUCCIONES WHERE (Condiciones en Select)--
--
- Condiciones con expresiones matemáticas boleanas (>, <, =, >=, <=, != ó <>). --(Mostrar solo los productos con un precio mayor que 25) select * from dbo.Products where UnitPrice > 25
--(Mostrar el nombre de los productos con precio igual a 21) select ProductName as Nombre from dbo.Products where UnitPrice = 21
--2. Condiciones de intervalos numéricos (BETWEEN AND). --(Mostrar los productos cuyo precio esta entre 25 y 35) select * from Products where UnitPrice between 25 and 35
--3. Condiciones de cadenas de caracteres (LIKE _ , %).
(Mostrar todos los datos de los clientes cuyo nombre comienza por A) select * from Customers where ContactName like 'A%' -
- El '%' significa cualquier cadena de caracteres.
(Mostrar los Paises que comienzan por U y después un solo caracter). select Country as Paises from Customers where Country like 'U_' --El '_' significa cualquier caracter.
--
- Mostrar solamente las filas con valores nulos (IS NULL) en una columna determinada. select * from dbo.Customers where Region is null
--
- Mostrar solamente las filas con valores no nulos (IS NOT NULL) en una columna determinada. select * from Customers where Fax is not null
--6. Mostrar valores determinados (IN). select * from Products where UnitPrice in( 10 , 20 , 30 , 40 )
- Mostrar valores condicionalmente en función de un grupo de valores de una columna (ANY/SOME) --La clausula ANY es equivalente a SOME. --Cuando tenemos una consulta que devuelve un conjunto de resultados podemos necesitar compararlos -- todos ellos y de una vez con otro valor. Lo mejor es ver un ejemplo de es tas expresiones:
-- Condición Expresión Equivalente -- x > ANY (3, 5, 9) x > 3 -- x > ANY (1, 2) x > 1 -- x < ANY (1, 2) x < 2 -- x = ANY (1, 2) (x = 1) OR (x = 2) -- x <> ANY (1, 2) (x <> 1) OR (x <> 2)
-- Mostrar los productos cuando su precio es mayor que los precios de los de
--3. Combinaciones de orden en distintas filas. select ProductID, ProductName, UnitPrice, UnitsInStock from dbo.Products order by UnitPrice, UnitsInStock desc
--SUBCONSULTAS EN SELECT O CONSULTAS ANIDADAS--
- Una subconsulta es una consulta SELECT dentro de un WHERE en una consul ta previa SELECT. Es decir, realizamos
- dentro de una consulta, otra consulta para mostrar datos especificos de la primera consulta. Ejemplos:
- Mostrar todos los empleados que tengan el mismo sexo (TitleOfCourtesy) que el empleado 9 (EmployeeID). select * from dbo.Employees where TitleOfCourtesy = (select TitleOfCourtesy from dbo.Employees where EmployeeID = 9) -- Esta subconsulta devuelve 'Ms'.
--
- Mostrar todos los productos de las categorías (bdo.Categories) bebidas (Beverages) y condimentos (Condiments). select * from dbo.Products where CategoryID in (select CategoryID from dbo.Categories where CategoryName in ('Beverages','Condiments')) -- Esta subconsulta devuelve 1 y 2.
--FUNCIONES DE AGREGACIÓN EN SELECT--
--1. Obtener el número de filas de una tabla --> COUNT(). select count() from dbo.Customers -- Se cuentan todos los valores, incluidos los NULL.
-- Se puede dar nombre a esta columna de la siguiente forma. select count(*) as [Número de filas] from dbo.Customers
--2. Obtener el múmero de filas de una columna determinada --
COUNT(NombreColumna). select count(Region) as [NºFilas no NULL]from dbo.Customers -- No se incluyen los valores NULL.
--3. Obtener número de filas para varias columnas determinadas. select count(Region) as FilasNoNULL_Region, count(Fax) as FilasNoNULL_Fax from dbo.Customers
--4. Obtener el valor máximo (MAX) y mínimo (MIN) de una columna. select max(UnitPrice) as [Precio máximo], min(UnitsInStock) as [Stock mín imo] from dbo.Products
--5. Obtener un sumatorio (SUM) de una columna determinada. select sum(Freight) as [Peso Total] from dbo.Orders
--6. Obtener la media (AVG) de los valores de una columna determinada. select avg(Freight) as [Peso Medio] from dbo.Orders
INSTRUCCIÓN GROUP BY (Agrupamiento de filas en funciones de agregación)--
-- La instrucción GROUP BY permite especificar las columnas sobre las que se quieren aplicar las funciones de -- agregación en lugar de hacerlo en una tabla completa. Es decir, muestra u n resultado de la función de agregación --para cada elemento de la columna que especifiquemos. Ejemplos:
--1. Mostrar cuantas veces se repite cada precio de los productos. select UnitPrice, count(*) as [Filas con este precio] from Products group by UnitPrice
--2. Mostrar número de clientes de cada region y ciudad de USA. select Region, City, count(*) as [Nº Clientes] from Customers where Country = 'USA' group by Region, City order by [Nº Clientes] desc
NOTA 1: Siempre debe especificarse las mismas columnas en SELECT que en G ROUP BY. -- NOTA 2: Es una buena práctica ordenar (ORDER BY) los elementos cuando se
Ejemplo 13
En este ejercicio se debe escribir sentencias SQL. Cada declaración sólo utilizará una sola tabla. Pasar por correo al profesor todas las sentecias.
- Una lista de todos los detalles de la tabla de empleados.
- Una lista de los nombres y apellidos de todos los empleados.
- Una lista de todos los nombres de las ciudades que aparecen en la tabla de empleados. No mostrar 2 veces un mismo nombre de ciudad.
- Una lista de los nombres de productos y precios unitarios.
- En la tabla de empleados: una lista de los detalles completos de los empleados que viven en EE.UU.
- A partir de la tabla Pedidos, listar todos los pedidos que tienen un gasto de envio > 50.
- De la tabla de clientes: listar nombre de la empresa de todos los clientes donde el cargo es igual a Propietario.
- A partir de los clientes una lista de todos donde el nombre del cliente comienza con la letra "A”.
- Una lista de los nombres de clientes donde la región no está en blanco.
- Una lista de todos los productos, ordenado por precio unitario (el más barato primero).
- Una lista de todos los productos, ordenado por precio unitario (el más caro primero).
- El número total de empleados. Nombre de la columna de salida "TotalEmpleados".
- De la tabla de Pedidos, el pedido con el menor gasto de envió, el gasto de envió promedio y el máximo gasto de envió (puede estar separado en 3 consultas distintas).
- Utilizando la tabla de clientes una lista de los nombres de ciudades y el número de clientes en cada ciudad.
- Utilizando la tabla de clientes una lista de los nombres de ciudades y el número de clientes en cada ciudad. Solo las ciudades con al menos 2 clientes deben aparecer en la lista.
- Insertar el cliente nuevo completando todos los campos de la tabla.
- Actualizar el nombre del nuevo cliente pasando número de ID obtenido en el punto 16.
- Eliminar el cliente insertado en el punto 16.
Procedimientos para agrupar y resumir datos 1
Práctica A: Procedimientos para agrupar y resumir datos
Objetivos
Después de realizar esta práctica, el alumno será capaz de:
Utilizar las cláusulas GROUP BY y HAVING para resumir datos por grupos. Utilizar los operadores ROLLUP y CUBE, y la función GROUPING para generar datos de resumen.
Utilizar las cláusulas COMPUTE y COMPUTE BY para generar informes con secciones, totales y promedios.
Requisitos previos
El archivo lab052.zip contiene los archivos de comandos necesarios para la realización de la práctica, así como las correspondientes soluciones.
Ejercicio 1
Uso de la palabra clave TOP n
En este ejercicio, va a utilizar la palabra clave TOP n y la cláusula WITH TIES para obtener las primeras filas, o un porcentaje de ellas, de un conjunto de resultados. La carpeta Soluciones contiene las secuencias de comandos completas para este ejercicio.
Ë Para utilizar la palabra clave TOP n y presentar las primeras filas de
un conjunto de resultados En este procedimiento, debe modificar una secuencia de comandos para que devuelva las diez primeras filas de una consulta. La secuencia de comandos completa para este procedimiento es Answer_TopN1.sql.
Abra el Analizador de consultas de SQL Server y, si así se le pide, inicie una sesión en el servidor local con autenticación de Microsoft Windows®.
En la lista BD , haga clic en northwind. Abra y examine la secuencia de comandos TopN.sql, que corresponde a una consulta que calcula el importe total de las ventas de cada pedido de la tabla order details y devuelve los resultados en orden decreciente. Modifique la consulta descrita en el paso 4 para que sólo devuelva las diez primeras filas. USE northwind SELECT TOP 10 orderid ,(unitprice * quantity) AS totalsale FROM [order details] ORDER BY (unitprice * quantity) DESC GO Ejecute la consulta para comprobar que sólo devuelve diez filas.
Resultado Su resultado será similar al siguiente conjunto de resultados.
Procedimientos para agrupar y resumir datos 3
Su resultado será similar al siguiente conjunto de resultados.
orderid totalsale 10865 15810. 10981 15810. 10353 10540. 10417 10540. 10889 10540. 10424 10329. 10897 9903. 10372 8432. 10540 7905. 10816 7905. 10817 7905.
(11 filas afectadas)
Resultado
4 Procedimientos para agrupar y resumir datos
Ejercicio 2
Uso de las cláusulas GROUP BY y HAVING
En este ejercicio, va a utilizar las cláusulas GROUP BY y HAVING para resumir datos de la base de datos Northwind. La carpeta Soluciones contiene las secuencias de comandos completas para este ejercicio.
Ë Para utilizar la cláusula GROUP BY para resumir los datos
En este procedimiento va a abrir una secuencia de comandos que contiene una consulta con la cláusula GROUP BY. A continuación, modificará la consulta para obtener resultados diferentes.
Abra y examine la secuencia de comandos Groupby.sql. Se trata de una consulta que calcula la cantidad total de artículos pedidos de dos categorías distintas de artículos de la tabla order details.
Ejecute la consulta para revisar los resultados.
Su resultado será similar al siguiente conjunto de resultados.
categoryid total_quantity 1 9532 2 5298
(2 filas afectadas)
Ë Para calcular la cantidad total para cada una de las categorías
Answer_Groupby1.sql es la secuencia de comandos completa de este procedimiento. Modifique la secuencia de comandos descrita en el paso 1 del procedimiento anterior para resumir la cantidad por categoría para todos los productos, sea cual sea su categoría. USE northwind SELECT categoryid, SUM(quantity) AS total_quantity FROM [order details] AS od INNER JOIN products AS p ON od.productid = p.productid GROUP BY categoryid GO
Ejecute la consulta para revisar los resultados.
Resultado
6 Procedimientos para agrupar y resumir datos
Ë Para calcular el número de pedidos con más de 250 unidades pedidas
En este procedimiento calculará el número de pedidos con más de 250 unidades pedidas. Answer_Groupby3.sql es la secuencia de comandos completa de este procedimiento.
Modifique la secuencia de comandos descrita en el paso 1 del procedimiento anterior para resumir la cantidad por orderid (pedido) para todos los productos, sea cual sea su categoría, y devolver sólo los pedidos que tengan más de 250 unidades pedidas USE northwind SELECT orderid, SUM(quantity) AS total_quantity FROM [order details] AS od INNER JOIN products AS p ON od.productid = p.productid GROUP BY orderid HAVING SUM(quantity) > 250 GO
Ejecute la consulta para revisar los resultados.
Su resultado será similar al siguiente conjunto de resultados.
orderid total_quantity 10515 286 10612 263 10658 255 10678 280 10847 288 10895 346 10990 256 11030 330
(8 filas afectadas)
Resultado
Procedimientos para agrupar y resumir datos 7
Ejercicio 3
Uso de los operadores ROLLUP y CUBE
En este ejercicio, va a utilizar los operadores ROLLUP y CUBE para generar datos de resumen. También va a utilizar la función GROUPING para determinar las filas del resultado que son resúmenes. La carpeta Soluciones contiene las secuencias de comandos completas para este ejercicio.
Ë Para utilizar el operador ROLLUP con el fin de generar resultados de resumen
En este procedimiento va a utilizar el operador ROLLUP con las cláusulas GROUP BY y HAVING para generar resultados de resumen. La secuencia de comandos completa para este procedimiento es Answer_Rollup1.sql.
Abra y examine la secuencia de comandos Answer_Rollup1.sql. Se trata de una consulta que resume la cantidad de artículos pedidos por cada productid y ordered , y realiza un cálculo acumulativo. Modifique la consulta descrita en el paso 1 para que en el resultado sólo aparezca el número de producto 50, con una cláusula WHERE, y después ejecute la consulta. USE northwind SELECT productid, orderid, SUM(quantity) AS total_quantity FROM [order details] WHERE productid = 50 GROUP BY productid, orderid WITH ROLLUP ORDER BY productid, orderid GO
Ejecute la consulta para revisar los resultados. Cuente las filas que tienen valores nulos.
Su resultado será similar al siguiente conjunto de resultados.
productid orderid total_quantity NULL NULL 235 50 NULL 235 50 10350 15 50 10383 15 50 10429 40 50 10465 25 50 10637 25 50 10729 40 50 10751 20 50 10920 24 50 10948 9 50 11072 22
(12 filas afectadas)
Resultado
