Scribd
Cargar
25 vistas23 páginas

Sesion 14

El documento proporciona ejemplos de consultas SQL para gestionar y analizar datos en la base de datos Northwind, incluyendo subconsultas, funciones definidas por el usuario y procedimientos almacenados. Se presentan ejemplos de cómo listar órdenes, productos, empleados y clientes, así como calcular totales y aplicar filtros utilizando cláusulas como GROUP BY y HAVING. Además, se discuten las diferencias entre usar JOINs y subconsultas para obtener resultados similares.

Cargado por

roni vasquez
Derechos de autor
© © All Rights Reserved
Nos tomamos en serio los derechos de los contenidos. Si sospechas que se trata de tu contenido, reclámalo aquí.
Formatos disponibles
Descarga como DOCX, PDF, TXT o lee en línea desde Scribd
25 vistas23 páginas

Sesion 14

El documento proporciona ejemplos de consultas SQL para gestionar y analizar datos en la base de datos Northwind, incluyendo subconsultas, funciones definidas por el usuario y procedimientos almacenados. Se presentan ejemplos de cómo listar órdenes, productos, empleados y clientes, así como calcular totales y aplicar filtros utilizando cláusulas como GROUP BY y HAVING. Además, se discuten las diferencias entre usar JOINs y subconsultas para obtener resultados similares.

Cargado por

roni vasquez
Derechos de autor
© © All Rights Reserved
Nos tomamos en serio los derechos de los contenidos. Si sospechas que se trata de tu contenido, reclámalo aquí.
Formatos disponibles
Descarga como DOCX, PDF, TXT o lee en línea desde Scribd

-- Datos 1 - Sesión 14

-- Subconsultas

-- Funciones definidas por el usuario

-- Procedimientos almacenados

USE Northwind

GO

-- Listar las órdenes de Agosto a Diciembre de 1997

-- Mostrar el detalle, incluida la descripción del producto

select

[Link],

[Link],

[Link],

[Link],

[Link],

[Link] * [Link] As 'Importe',

[Link] As '% Descuento'

from [Link] As O

join dbo.[Order Details] As D on [Link] = [Link]

join [Link] As P on [Link] = [Link]

where [Link] between '01/08/1997' and '31/12/1997'

go

--- Mostrar la orden 10250, mostrar el detalle, Importe (Precio *


Cantidad)
-- el descuento (Precio * Cantidad) * %Descuento

-- mostrar el total: Importe - Descuento

select

[Link]
As 'Cód. Producto',

[Link]
As 'Descripción',

[Link]
As 'Precio',

[Link]
As 'Cantidad',

[Link] * [Link] As
'Importe',

[Link]
As '% Descuento',

([Link] * [Link]) * [Link] As


'Descuento',

([Link] * [Link]) * (1 - [Link]) As 'Subtotal'

from [Link] As O

join dbo.[Order Details] As D on [Link] = [Link]

join [Link] As P on [Link] = [Link]

where [Link] = 10250

go --- Costo: 0.0101512

select

[Link]
As 'Cód. Producto',

[Link]
As 'Descripción',

[Link]
As 'Precio',

[Link]
As 'Cantidad',
[Link] * [Link] As
'Importe',

[Link]
As '% Descuento',

([Link] * [Link]) * [Link] As


'Descuento',

([Link] * [Link]) * (1 - [Link]) As 'Subtotal'

from dbo.[Order Details] As D

join [Link] As P on [Link] = [Link]

where [Link] = 10250

go --- Costo: 0.0068566

-- Comparando

--- Costo: 0.0101512

--- Costo: 0.0068566

/*

AVG Devuelve el Promedio de los valores

MIN Devuelve el valor Mínimo

SUM Suma los valores

COUNT Cuenta la cantidad de celdas

MAX Devuelve el valor Máximo

*/

-- Agrupamientos:

/*

Permite realizar cálculos en los listados.

Uso de Group By y alguna función de agregado


La cláusula Group by se escribe después de Where si hubiera

o de los joins.

Para filtrar los campos con agrupamiento se usa Having

Cuando un campo es un listado tiene función de agregado, los


campos

que no tienen función de agregado debes escribirse en la cláusula


Group by

select

Top n percent

Lista de campos....

from [Link] As O

join dbo.[Order Details] As D on [Link] = [Link]

join [Link] As P on [Link] = [Link]

where [Link] between 10 and 20

Group by ....

Having....

order by [Link] desc

offset..... Fetch next

go

*/

-- Listado de las categorías y la cantidad de productos de cada una

select

[Link] As 'Código',

[Link] As 'Categoría',

COUNT([Link]) As 'Cantidad de Productos'


from [Link] As C

join [Link] As P on [Link] = [Link]

Group by [Link], [Link]

go

-- Listado de empleados y contar las órdenes registradas por cada uno

select

[Link],

[Link],

[Link],

COUNT([Link]) As 'Cantidad de órdenes',

[Link]

from [Link] As E

join [Link] As O on [Link] = [Link]

-- Where ....

group by [Link], [Link], [Link], [Link]

-- Having ...

go

/*

Filtrado de registros en un listado con agrupamiento

where cuando el filtro es por un campo sin función de agregado

se escribe después de Join

Having cuando el filtro es por un campo que tiene función de


agregado

se escribe después de Group by


*/

-- De la instrucción anterior, filtrar los empleados cuyo nombre

-- inicie con la letra M

select

[Link],

[Link],

[Link],

COUNT([Link]) As 'Cantidad de órdenes',

[Link]

from [Link] As E

join [Link] As O on [Link] = [Link]

Where [Link] like 'M%'

group by [Link], [Link], [Link], [Link]

-- Having ...

go

-- Listar los empleados que registraron mas de 100 órdenes

select

[Link],

[Link],

[Link],

COUNT([Link]) As 'Cantidad de órdenes',

[Link]

from [Link] As E

join [Link] As O on [Link] = [Link]

--Where [Link] like 'M%'

group by [Link], [Link], [Link], [Link]


Having COUNT([Link]) >100

go

-- Listado de clientes (Customers), contar la cantidad de órdenes,

-- sumar el impuesto (campo Freight), mostrar solamente los clientes

-- de Mexico, France, Canada que tengan un impuesto entre 100 y


500

select

[Link],

[Link],

COUNT([Link]) As 'Cantidad de
Órdenes',

[Link],

SUM([Link]) As 'Suma
Impuesto',

[Link]

from [Link] As C

join [Link] As O on [Link] = [Link]

where [Link] in ('Mexico', 'France', 'Canada')

group by [Link], [Link], [Link],


[Link]

Having SUM([Link]) between 100 and 500

go

-- Se puede usar (no hacer) la expresión de filtro del Where en Having,

-- NO SE PUEDE usar la expresión del Having en Where

select

[Link],

[Link],

COUNT([Link]) As 'Cantidad de
Órdenes',
[Link],

SUM([Link]) As 'Suma
Impuesto',

[Link]

from [Link] As C

join [Link] As O on [Link] = [Link]

--where [Link] in ('Mexico', 'France', 'Canada')

group by [Link], [Link], [Link],


[Link]

Having SUM([Link]) between 100 and 500

and [Link] in ('Mexico', 'France', 'Canada')

go

/*

==========================================
==========================================
=

SUBCONSULTAS - OPCIÓN ALTERNATIVA


A JOIN

==========================================
==========================================
=

Las subconsultas pueden utilizarse de dos formas:

1. Dentro de la Lista de campos de la instrucción Select, esta


subconsulta es la que reporta un valor.

Se debe relacionar la tabla después del From

con la tabla de la Subconsulta.

select

ListaCampos,
(Select UnCampo from Tabla_Subconsulta
where ...) ,

(Select UnCampo from Tabla_Subconsulta) ,

from TablaPrincipal

2. En las clásulas Where

Select

ListadeCampos,

OtroCampo,

UltimoCampo

from [Link]

WHERE Campo [NOT] IN (subconsulta, muestra un


campo)

*/

-- Listado de las categorías y la cantidad de productos de cada una

select

[Link] As 'Código',

[Link] As 'Categoría',

COUNT([Link]) As 'Cantidad de Productos'

from [Link] As C

join [Link] As P on [Link] = [Link]

Group by [Link], [Link]

go

-- Listado con Subconsultas

-- Listado de las categorías y la cantidad de productos de cada una


(Subconsulta)
select

[Link] As 'Código',

[Link] As 'Categoría'

from [Link] As C

go

-- Consulta para saber la cantidad de productos de una categoría (Ej.


1)

select COUNT([Link]) from [Link] As P where [Link]


=1

go

-- Armar la consulta incluyendo al subconsulta

select

[Link] As 'Código',

[Link] As 'Categoría',

(select COUNT([Link]) from [Link] As P

where [Link] = [Link])

As 'Cantidad de Productos'

from [Link] As C

go

-- Listado de Shippers (Couriers), la cantidad de órdenes atendidas

-- el monto total del impuesto (Freight).

-- La orden atendida es la que tiene una fecha en el campo


ShippedDate

-- JOINS

select

[Link],
[Link],

[Link],

COUNT([Link]) As 'Cantidad de Órdenes',

SUM([Link]) As 'Total Impuesto'

from [Link] As S

join [Link] As O on [Link] = [Link]

where [Link] is not null

Group by [Link], [Link], [Link]

go -- Costo: 0.0454935

-- SUBCONSULTAS

select

[Link],

[Link],

[Link],

(Select COUNT([Link]) from [Link] As O

where [Link] = [Link] and [Link] is not


null )

As 'Cantidad de Órdenes',

(select SUM([Link]) from [Link] As O

where [Link] = [Link] and [Link] is not


null )

As 'Total Impuesto'

from [Link] As S

go -- Costo: 0.0491835

--
==========================================
====================== --

-- FUNCIONES DEFINIDAS POR EL USUARIO


--
==========================================
====================== --

/*

1. Las que retornan UN VALOR – InLine,se utilizan en otras


instrucciones

2. Las que retornan UNA TABLA

Se pueden encriptar: Vistas, FDU, Triggers, Procedimientos


almacenados

Para crear las que devuelven un valor.

Create or alter function [Link]([Parámetros])

Returns TipoDato with encryption

As

Begin

Instrucciones….

Return ValorRetornado

End

go

Para crear las FDU que retornan UNA TABLA

Create or alter function [Link]([Parámetros])

Returns Table with encryption

As

Return (Select….)

go
*/

-- Listado de Shippers (Couriers), la cantidad de órdenes atendidas

-- el monto total del impuesto (Freight).

-- La orden atendida es la que tiene una fecha en el campo


ShippedDate

-- FDU

-- FDU que retorna la cantidad de órdenes por Shipper

Create or alter function


[Link](@CodigoShipper int)

returns int with encryption

As

Begin

Declare @CantidadOrdenes int = 0

set @CantidadOrdenes =

(Select COUNT([Link]) from


[Link] As O

where [Link] =
@CodigoShipper and [Link] is not null)

Return @CantidadOrdenes

End

go

-- FDU que retorna la suma del impuesto por Shipper

Create or alter function


[Link](@CodigoShipper int)

returns Numeric(19,2) with encryption

As

Begin

Declare @SumaImpuesto Numeric(19,2) = 0


set @SumaImpuesto =

(Select SUM([Link]) from [Link]


As O

where [Link] =
@CodigoShipper and [Link] is not null)

Return @SumaImpuesto

End

go

-- Usar las FDU

-- A partir de SQL Server 2019, las FDU se insertan en la consulta, lo


que hace que funcionen

-- mas lento, al usar FDU se debe cambiar a SQL Server 2017

-- Para cambiar a SQL Server 2017, solamente se cambia el Nivel de


Compatibilidad

-- FDU - SQL Server 2022

select

[Link],

[Link],

[Link],

[Link]([Link]) As
'Cantidad de Órdenes',

[Link]([Link]) As 'Total
Impuesto'

from [Link] As S

go -- Costo: 0.0491835

-- JOINS

select

[Link],

[Link],
[Link],

COUNT([Link]) As 'Cantidad de Órdenes',

SUM([Link]) As 'Total Impuesto'

from [Link] As S

join [Link] As O on [Link] = [Link]

where [Link] is not null

Group by [Link], [Link], [Link]

go -- Costo: 0.0454935

-- SUBCONSULTAS

select

[Link],

[Link],

[Link],

(Select COUNT([Link]) from [Link] As O

where [Link] = [Link] and [Link] is not


null )

As 'Cantidad de Órdenes',

(select SUM([Link]) from [Link] As O

where [Link] = [Link] and [Link] is not


null )

As 'Total Impuesto'

from [Link] As S

go -- Costo: 0.0491835

-- FDU - SQL Server 2017

ALTER DATABASE [Northwind] SET COMPATIBILITY_LEVEL = 140

GO
select

[Link],

[Link],

[Link],

[Link]([Link]) As
'Cantidad de Órdenes',

[Link]([Link]) As 'Total
Impuesto'

from [Link] As S

go -- Costo: 0.0032856

-- Joins -- Costo: 0.0454935

-- Subconsultas -- Costo: 0.0491835

-- FDU -- Costo: 0.0032856

-- Listado de Clientes, contar cuantas órdenes registradas en 1997

-- Calcular el monto total de todas las órdenes.

-- Primero: FDU que retorne el monto total de una orden

-- Ejemplo, orden 10250

select

Round(sum(([Link] * [Link]) * (1 - [Link])),2)

from dbo.[Order Details] As D where [Link] = 10250

go

-- FDU

Create or alter function


[Link]( @NumeroOrden int)

returns Numeric(19,2) with encryption

As
Begin

Declare @MontoOrden Numeric(19,2)

Set @MontoOrden =

(select

Round(sum(([Link] *
[Link]) * (1 - [Link])),2)

from dbo.[Order Details] As D


where [Link] = @NumeroOrden)

Return @MontoOrden

End

go

-- Listado de Clientes, contar cuantas órdenes registradas en 1997

-- Calcular el monto total de todas las órdenes.

-- Joins

select

[Link],

[Link],

[Link],

COUNT([Link]) As 'Cantidad de Órdenes',

Round(sum(([Link] * [Link]) * (1 - [Link])),2) As


'Monto total'

from [Link] As C

join [Link] As O on [Link] = [Link]

join dbo.[Order Details] As D on [Link] = [Link]

where year([Link]) = 1997

--where [Link] between '01/01/1997' and '31/12/1997'

Group by [Link], [Link], [Link]

go
-- Resultado incorrecto

-- Joins

select

[Link],

[Link],

[Link],

COUNT([Link]) As 'Cantidad de Órdenes',

select

Round(sum(([Link] * [Link]) * (1 - [Link])),2)

from dbo.[Order Details] as D

where

[Link] in (select [Link] from [Link] As O


where [Link] = [Link]

and year([Link]) = 1997)

As 'Monto total'

from [Link] As C

join [Link] As O on [Link] = [Link]

where year([Link]) = 1997

--where [Link] between '01/01/1997' and '31/12/1997'

Group by [Link], [Link], [Link]

go

-- Resultado Correcto

-- Órdenes de ALFKI

select [Link] from [Link] As O where [Link] = 'ALFKI'


and year([Link]) = 1997

go

-- 10643 10692 10702 10835 10952 11011

select

Round(sum(([Link] * [Link]) * (1 - [Link])),2)

from dbo.[Order Details] as D

where

[Link] in (select [Link] from [Link] As O


where [Link] = 'ALFKI'

and year([Link]) = 1997)

go

-- Subconsultas

select

[Link],

[Link],

[Link],

(select COUNT([Link]) from [Link] As O where


[Link] = [Link]

and year([Link]) = 1997 )

As 'Cantidad de Órdenes',

select

Round(sum(([Link] * [Link]) * (1 - [Link])),2)

from dbo.[Order Details] as D

where

[Link] in (select [Link] from [Link] As O


where [Link] = [Link]

and year([Link]) = 1997)


)

As 'Monto total'

from [Link] As C

join [Link] As O on [Link] = [Link]

where year([Link]) = 1997

--where [Link] between '01/01/1997' and '31/12/1997'

Group by [Link], [Link], [Link]

go

-- FDU que cuenta la cantidad de órdenes por cliente y por año

Create or alter function [Link]


(@CodigoCliente nchar(5), @Anio int)

returns int with encryption

As

Begin

Declare @Cantidad int

Set @Cantidad =

(select COUNT([Link]) from [Link] As


O where [Link] = @CodigoCliente

and year([Link]) = @Anio )

Return @Cantidad

End

go

-- FDU que sume los monto de las órdenes por cliente y por año

Create or alter function [Link]


(@CodigoCliente nchar(5), @Anio int)

returns Numeric(19,2) with encryption

As

Begin

Declare @Cantidad Numeric(19,2)


Set @Cantidad =

select

Round(sum(([Link] * [Link]) *
(1 - [Link])),2)

from dbo.[Order Details] as D

where

[Link] in (select [Link] from


[Link] As O where [Link] = @CodigoCliente

and year([Link]) =@Anio)

Return @Cantidad

End

go

-- FDU

select

[Link],

[Link],

[Link],

[Link]([Link],
1997)As 'Cantidad de Órdenes' ,

[Link]([Link], 1997) As
'Monto total'

from [Link] As C

go -- Costo: 0.0048927

--- Procedimientos almacenados

-- son código guardados en el servidor, reutilizables


/* Crear

Create or alter procedure [Link]

@Parametro1 tipo,

@Parametro2 tipo

with encryption

As

Instrucciones del SP

go

*/

-- Crear un SP (Store Procedure) que liste las categorías

Create or alter procedure [Link]

with encryption

As

select * from [Link]

go

-- Ejecutar

execute [Link]

go

-- SP que liste las órdenes de un cliente

Create or alter procedure [Link]

(
@CodigoCliente nchar(5)

with encryption

As

select

[Link],

[Link],

[Link],

[Link]

from [Link] As O

where [Link] = @CodigoCliente

go

-- Ordenes de ALFKI

Execute [Link] 'ALFKI'

go

También podría gustarte