Procedimientos Almacenados

[Link] procesos que realizan una tarea especfica con una o ms tablas
de [Link] (User Stored Procedure)

Ejemplos:
-- 1. Crear USP que muestre los productos almacenados u ordenados por
nombre.

Create procedure usp_Listar_Productos

As
select IdProducto,NombreProducto,PrecioUnidad,UnidadesEnExistencia from
Productos
order by NombreProducto
return-- Go
--Prueba
execute usp_Listar_Productos

-- 2. Crear USP con transferencia de parmetros.

Liste los productos segn su categora.
Create procedure usp_Listar_ProductosxCate
@xCate int
As
select IdProducto,NombreProducto,IdCategora from Productos
where IdCategora = @xCate
order by NombreProducto
return
---Prueba
execute usp_Listar_ProductosxCate1

Laboratorios SQL 1
--[Link] USP que liste los productos segn su nombre.

createprocedure usp_Listar_Productos_Nombre
@xN asVarchar(40)
As
select NombreProducto From Productos
where NombreProducto Like @xN +'%'
OrderBy NombreProducto
return
exec usp_Listar_Productos_Nombre'Ca'---''

--[Link] un USP que permita listar los pedidos entre dos fechas enviadas
como parmetros.
createprocedure usp_Pedidos_Fechas
@f1 SmallDateTime,
@f2 SmallDateTime
As
select IdPedido,IdCliente,IdEmpleado ,FechaPedido,Cargo from Pedidos
where FechaPedido between @f1 and @f2
return
---Prueba
execute usp_Pedidos_Fechas'01/01/1995','31/03/1995'

Laboratorios SQL 2
-- 5. Crear un USP que muestra los pedidos de un pas enviados en un ao
especfico.
createprocedure usp_Pedidos_Enviados
@PE varchar(15),
@Ao int
As
Select IdPedido,IdCliente, IdEmpleado,PasDestinatario,FechaEnvo from
Pedidos
whereYEAR(FechaEnvo)= @Ao and PasDestinatario =@PE
orderby IdPedido desc
return
---Prueba
execute usp_Pedidos_Enviados'Alemania',1996

-- 6. Pedidos por Cliente.

createProc usp_Pedidos_Clientes
@IdCli varchar(5)
As
select [Link], [Link],[Link],NombreCompaa,[Link]+' '+
[Link] as Empleado, [Link]
from Pedidos P Join Clientes C on [Link]=[Link]
Join Empleados E on [Link]=[Link]
where [Link] = @IdCli
order by [Link] Desc
return
---Prueba
execute usp_Pedidos_Clientes'BERGS'

Laboratorios SQL 3
--7. Listar Pedidos por el IdEmpleado.
CreateProc usp_Pedidos_Empleado
@IdEmple int
As
select [Link], [Link],[Link],NombreCompaa,
[Link] +' '+ [Link] as Empleado, [Link]
from Pedidos P Join Clientes C on [Link]=[Link]
Join Empleados E on [Link]=[Link]
where [Link] = @IdEmple
return
---Prueba
Execute usp_Pedidos_Empleado5

--8. Listar pedidos por Pas.

Create procedure usp_Pedidos_Pas
@Pas nvarchar(15)
As
Select [Link],[Link],[Link],
[Link]+' '+[Link] as Empleado,[Link],[Link]
From Pedidos P Join Clientes C on [Link] = [Link]
Join Empleados E on [Link] = [Link]
Where [Link]=@Pas
return

---Prueba
Execute usp_Pedidos_Pas 'Alemania'

Laboratorios SQL 4
--[Link] Pedidos_ListarTodos.
Create proc usp_Pedidos_ListarTodos
As
Select [Link], [Link], [Link], [Link],
NombreCompaa,[Link] +' '+ [Link] as Empleado, [Link]
From Pedidos P Join Clientes C on [Link]=[Link]
Join Empleados E on [Link]=[Link]
Return

---Prueba
execute usp_Pedidos_ListarTodos

Laboratorios SQL 5
-------------------------------------------------------------------------
Consulta de datos agrupados
Devuelve un conjunto de filas correspondiente a grupos de datos en comn
en la tabla.

Clase GROUP BY.

Sirve para calcular datos estadsticos con los datos numricos de una columna de la tabla.

[Link] Idpedido ,IdCliente,PasDestinatario From Pedidos

Group by PasDestinatario,Idpedido,IdCliente

[Link] PasDestinatario,COUNT(*)As npedidos From Pedidos

Group by PasDestinatario ---filtrados de una columna.

Modificador [Link] se repitan los datos iguales

Select Distinct PasDestinatario From Pedidos

Select Distinct CargoContacto From Clientes

Select Distinct Nombre ,Cargo ,Ciudad From Empleados

-------------------------------------------------------------------------

Laboratorios SQL 6
Funciones Estadsticas
AVG Promedio / Sum Suma / Max Mnimo
select PasDestinatario,
COUNT(*)As NPedidos,
AVG(Cargo)As PromedioCargo ,
MAX(Cargo)As MayorCargo,
MIN(Cargo)As MenorCargo ,
SUM(Cargo)As Totalcargo From Pedidos
Groupby PasDestinatario
Having PasDestinatario<>'Brail'or PasDestinatario <>'Argentina'

Having
Es una clasula que permite establecer una condicin de datos agrupados

[Link] PasDestinatario,
COUNT(*)As NPedidos,
AVG(Cargo)As PromedioCargo ,
MAX(Cargo)As MayorCargo,
MIN(Cargo)As MenorCargo ,
SUM(Cargo)As Totalcargo From Pedidos
Group by PasDestinatario
Having COUNT(*)>=50 and AVG(Cargo)>=60

[Link] PasDestinatario,
COUNT(*)As NPedidos,
AVG(Cargo)As PromedioCargo ,
MAX(Cargo)As MayorCargo,
MIN(Cargo)As MenorCargo ,
SUM(Cargo)As Totalcargo From Pedidos
Groupby PasDestinatario
Having PasDestinatario In('Brasil','Argentina')
---- Having not PasDestinatario In ('Brasil','Argentina')

Laboratorios SQL 7
[Link] una estadstica de los productos agrupados por sus categoras de
solo los productos que no esten suspendidos ,el resultado debe mostrar el
nombre de la categora.
select [Link],
COUNT(*)As nProductos ,
SUM([Link])As TotalStock,
AVG([Link])AS PromedoPrecio,
MAX([Link])As MayorPrecio,
MIN([Link])As MnimoPrecio
From Productos P Inner Join Categoras C -- unir(INNER JOIN) dos tablas
On [Link] = [Link] --con el campos en comn
Where [Link]=0 -- el valor suspendido es dato tipo bye
Group by [Link]

Laboratorios SQL 8
-------------------------------------------------------------------------
Procedimientos Almacenados_Mantenimiento-
Empleados
---[Link] empleados
Create procedure usp_Listar_Empleados
As
select
IdEmpleado,Nombre,Apellidos,Cargo,Tratamiento,Direccin,Ciudad,Pas
from Empleados
order by IdEmpleado
return

---[Link] un empleado
Create procedure usp_Empleado_Buscar
@IdEmpl int
As
select
IdEmpleado,Nombre,Apellidos,Cargo,Tratamiento,Direccin,Ciudad,Pas,TelDo
micilio
from Empleados
where IdEmpleado=@IdEmpl
return
--Prueba
execute usp_Empleado_Buscar6---no olvidarse enviar el parmetro

--- [Link] Empleado

Create procedure usp_Empleado_Adicionar
--parmetro de Salida--
@Id int output,
--parmetros de entrada--
@Nombre varchar(10),
@Apellidos varchar(20),
@Cargo varchar(30),
@Trata varchar(25),
@Tel varchar(60),
@Dir varchar(15),
@Ciudad varchar (20),
@Pas varchar (15)
As
---se escribe en el orden en el que se escribieron los parmetros
Insert into Empleados
(Nombre ,Apellidos ,Cargo ,Tratamiento ,TelDomicilio ,Direccin ,Ciudad ,Pas )
Values (@Nombre,@Apellidos,@Cargo,@Trata,@Tel,@Dir,@Ciudad,@Pas)
--el @Id no se coloca porque es automtico
set @Id=@@IDENTITY
-------------------------------------------------------------------------
Nota:@@IDENTITY es una variable del sistema o tambin Return '@@Identity'
-------------------------------------------------------------------------------------------
--Prueba
Declare @x int-- declaro la variable que se asociara con @Id
Execute usp_Empleado_Adicionar@x
output,'Juber','Ortega','Gerente','Sr.','455825','[Link] 7895 Los
Olivos','Chanchamayo','Per'
Print @x

Laboratorios SQL 9
---[Link] datos del Empleado
Create procedure usp_Empleado_Actualizar
@Id int,
@Nombre varchar(10),
@Apellidos varchar(20),
@Cargo varchar(30),
@Trata varchar(25),
@Tel varchar(60),
@Dir varchar(15),
@Ciudad varchar (20),
@Pas varchar (15)
As
Update Empleados set
Nombre=@Nombre,Apellidos=@Apellidos,Cargo=@Cargo,Tratamiento=@Trata,
TelDomicilio=@Tel,Direccin=@Dir,Ciudad=@Ciudad,Pas=@Pas
where IdEmpleado=@Id
return
---Prueba
Execute USP_Empleado_Actualizar 34,
'Antony','Florencio','Recepcionisto','Ms.', '987654321',
'Av. Marco Polo 789 los jardines','Lima','Per'
Select * From Empleados

---[Link] Empleado
Create procedure usp_Empleado_Eliminar
@Id int
As
Delete from Empleados
where IdEmpleado = @Id
return
---Prueba
execute usp_Empleado_Eliminar 34

-------------------------------------------------------------------------

Laboratorios SQL 10
Procedimientos Almacenados_Mantenimiento -
Productos
--- [Link] Producto
Create procedure usp_Producto_Adicionar
@IdProducto int output,--parametro de salida
@NombreProductoVarchar(40),
@IdProveeint,
@IdCateint,
@Precio money,
@stock smallint
as
insert intoProductos
(NombreProducto,IdProveedor,IdCategora,PrecioUnidad,UnidadesEnExistencia)
values(@NombreProducto,@IdProvee,@IdCate,@Precio,@stock)
set @IdProducto =@@IDENTITY

--- [Link] Producto

Create procedure usp_Producto_Actualizar
@IdProducto int,
@Nombre Varchar(40),
@IdProvee int,
@IdCate int,
@Precio money,
@stock smallint
As
Update Productos set NombreProducto=@Nombre ,IdProveedor =@IdProvee
,IdCategora =@IdCate ,PrecioUnidad=@Precio,UnidadesEnExistencia =@stock
where IdProducto = @IdProducto
return

--- [Link] Producto

Createprocedure usp_Producto_Eliminar
@IdProducto int
as
Delete from Productos where IdProducto=@IdProducto

--- [Link] Mostrar

Create procedure usp_Producto_Mostrar
@IdProducto int
as
select
IdProducto,NombreProducto,IdProveedor,IdCategora,PrecioUnidad,UnidadesEn
Existencia from Productos
Where IdProducto=@IdProducto
go

--- [Link] Listar

Createprocedure usp_Producto_Listar
as
selectIdProducto,NombreProducto,IdProveedor,IdCategora,PrecioUnidad,Unid
adesEnExistenciafromProductos
orderby 2
go

Laboratorios SQL 11
-------------------------------------------------------------------------
Procedimientos Almacenados_Mantenimiento -
Pedidos
--- [Link] Pedidos
Create procedure usp_Pedidos_Adicionar
@IdPed int output,variable que devuleva el cdigo del pedido
@IdCliente int,
@IdEmpleado int,
@Fecha_Pedido datetime,
@Cargo money,
@Destinatario varchar (40),
@Direccin varchar (60),
@Pas varchar (10)
as
insert into
Pedidos(IdCliente,IdEmpleado,FechaPedido,Cargo,Destinatario,DireccinDest
inatario,PasDestinatario)
values(@IdCliente,@IdEmpleado ,@Fecha_Pedido ,@Cargo,@Destinatario
,@Direccin,@Pas)
set @IdPed=@@IDENTITY

--- [Link] Pedidos

createprocedureusp_Pedidos_Actualizar
@IdPed int,
@IdCliente int,
@IdEmpleado int,
@Fecha_Pedido datetime,
@Cargo money,
@Destinatario varchar (40),
@Direccin varchar (60),
@Pas varchar (10)
as
Update Pedidos set IdPedido=@IdPed,IdCliente=@IdCliente
,IdEmpleado=@IdEmpleado,FechaPedido=@Fecha_Pedido,Cargo=@Cargo
,Destinatario =@Destinatario,
DireccinDestinatario=@Direccin,PasDestinatario=@Pas
Where IdPedido = @IdPed
Return

--- [Link] Pedidos

Create procedure usp_Pedidos_Eliminar
@IdPed int
as
Delete from Pedidos where IdPedido=@IdPed

--- [Link] Mostrar

Create Proc Usp_Pedidos_Mostrar
@IdPed int
as

Laboratorios SQL 12
Select
IdPedido,IdCliente,IdEmpleado,FechaEntrega,FechaEnvo,FormaEnvo,Cargo,De
stinatario,DireccinDestinatario,CiudadDestinatario,ReginDestinatario,C
dPostalDestinatario,PasDestinatario From Pedidos
Where IdPedido=@IdPed

-------------------------------------------------------------------------
Subconsultas
[Link] subconsulta es una consulta dentro de otra que puede ser incorporada en
una clausula where en una lista de campos ,las subconsultas son aplicadas a
instrucciones select,update,insert into.

Ejercicios
--- [Link] los clientes que le vendieron durante el ao 1996.
Select IdCliente,NombreCompaa,NombreContacto,Pas from Clientes
Where IdCliente in
(select distinct IdCliente from Pedidos where YEAR(FechaPedido)=1996)

--Otra forma
Select distinct [Link],[Link],[Link],[Link]
From Clientes C INNERJOIN Pedidos P
on [Link] =[Link]
where YEAR([Link])=1996
*No se puede relacionar pedidos con producto porque la relacin es muchos
a muchos,se necesita una tabla intermedia.

--- [Link] los clientes que se le enviaron el producto t tharansala

en el ao 1996.
Select distinct IdCliente,NombreCompaa,NombreContacto,Pas
from Clientes
where IdCliente in(select IdCliente from Pedidos
where IdPedido in(select IdPedido from[Detalles de pedidos]
where IdProducto in(select IdProducto from Productos
where NombreProducto='T Dharamsala')) and YEAR(FechaEnvo)=1996)

--- [Link] los productos que tienen un precio mayor a dos veces el
promedio de precio de todos los productos.

Laboratorios SQL 13
Select IdProducto,NombreProducto,CantidadPorUnidad from Productos
Where PrecioUnidad >=(select AVG(PrecioUnidad)from Productos)

EJERCICIOS
--- [Link] los Productos con un campo calculado que muestre el total
de ventas de cada producto que haya tenido descuento.
-------------------------------------------------------------------------
Nota:Se elige un producto luego se utiliza la tabla detalles_Pedidos
-------------------------------------------------------------------------
Select IdProducto as Cdigo,NombreProducto as Producto,
(selectSUM(PrecioUnidad*Cantidad*(1-Descuento))
From[Detalles de pedidos]
whereIdProducto=[Link] and Descuento>0)as Total
From[Productos]

---[Link] los Productos de la categora lcteos que tengan un precio

por debajo de los precios de esa categora.
select [NombreProducto] as Producto,[Link] as categora,
[Link] as precio from
Productos P inner join Categoras C
on [Link] = [Link]
where [Link] = 'Lcteos' and [Link] < (select
AVG(PrecioUnidad)from Productos)

Laboratorios SQL 14
---[Link] los pases donde se enviaron el producto "pez espada"
durante los meses de Enero a Marzo del ao 1995.
-------------------------------------------------------------------------
Nota:EXISTSes una funcin SQL que devuelve verdadero cuando una
subconsulta retorna al menos una fila.
-------------------------------------------------------------------------
[Link]
whereEXISTS(Select*from[Detalles de pedidos]
whereIdProducto in
(SelectIdProductoFROMProductos whereNombreProducto='Pez
espada'))andFechaEnvoin('01/01/1995','31/03/1995')

--- [Link] los Productos que tengan igual precio que "Te Dharamsa".

---[Link] los clientes de Alemania, y la cantidad de Pedidos realizados

por esos clientes durante el ao 1996.
[Link] asClientes,COUNT([Link])asCantidad,[Link]
FromPedidosPinnerjoinClientesC
[Link]=[Link]
innerjoin[Detalles de pedidos]D
[Link]=[Link]
[Link]='Alemania'andYEAR([Link])=1996
[Link],[Link]
Go

--- [Link] usp que muestre la cantidad de pedido que ha sido vendido un
Producto(X) por un Vendedor(Y).
createprocedureusp_CantidadPedidosVendidos
@NomProdvarchar(40),
@NomVend varchar(20)
as

Laboratorios SQL 15
[Link],
[Link],COUNT([Detalles de pedidos].IdPedido)asCantidad
fromPedidosINNER JOINEmpleados
[Link]=[Link]
INNERJOIN[Detalles de pedidos]
[Link]=[Detalles de pedidos].IdPedido
INNERJOINProductos
on[Detalles de pedidos].IdProducto=[Link]
[Link],[Link]
[Link]=@[Link]=@NomVendGO
-------------------------------------------------------------------------
Nota:Paraaplicar la condicin en los grupos utilizoHAVING no uso WHERE.
-------------------------------------------------------------------------
Execute usp_CantidadPedidosVendidos'Pez espada ','Davolio'

PRCTICA 06/08/11
--- [Link] el Proc q muestre los paises en donde se han enviado un
determinado producto,el Proc recibir como parametro el ID del Producto.
CreateProcUsp_Listar_Producto_xPais
@IdProInt
As
SelectDistinctPasDestinatario
FromPedidos
WhereEXISTS(Select*From[Detalles de pedidos]
WhereIdProducto in(SelectIdProductoFromProductos
whereIdProducto=@IdPro))
--Prueba
executeUsp_Listar_Producto_xPais'1'

--- [Link] el Proc almacenado que actualize el stock de los Productos

segn la cantidad y el Id del Producto que se envia al procedimiento
almacenado.
CreateProcedureUsp_Actualizar_Stock

Laboratorios SQL 16
@IdProint,
@Cantint
As
SelectIdProducto,NombreProducto,PrecioUnidad,UnidadesEnExistenciafromProd
uctos
updateProductosSETUnidadesEnExistencia=UnidadesEnExistencia +@Cant
WHEREIdProducto=@IdPro
go
--Prueba
execUsp_Actualizar_Stock1,60

--- [Link] el Proc almcenado que elimine los pedidos entre 2 Fechas de
un Pas determinado.
CreateProcedure Usp_Eliminar_Entre_Fechas
@PaisChar(15),
@Fech1SmallDateTime,
@Fech2SmallDateTime
As
DeleteFromPedidos
WherePasDestinatario=@PaisandFechaPedidoin(@Fech1,@Fech2)
go
--Prueba
executeUsp_Eliminar_Entre_Fechas'Brasil','1995-08-01','1994-08-12'

--- [Link] el Proc almacenado que permita adicionar un nuevo empleado a

la tabla empleados, considerando que elcampo IDempleado es Identidad y
que dever mostrar el nuevo Id generado por la operacin.

Laboratorios SQL 17
CreateProcedureUsp_Adicionar_Empleado
@IdEmpleintoutput,
@ApellidosChar(30),
@Nombchar(15),
@CargoChar(15),
@Ciudadchar(15)
As
InsertIntoEmpleados (Apellidos,Nombre,Cargo,Ciudad)
values(@Apellidos,@Nomb,@Cargo,@Ciudad)
set@IdEmple=@@IDENTITY
Go
--Prueba
Declare@Idint
execUsp_Adicionar_Empleado @Idoutput,'Juber','Conter','Jefe','Cuzco'
go

EJERCICIOS 07/08/11
---[Link] el usp que liste los productos que comiencen con un texto
enviado como parmetro.

Ifexists(selectnamefromsysobjects--La tabla sysobjects se guardan todos los objetos

que se generan en una base de datos como: tablas,vistas,usp,funciones,triggers,etc.
wherename='usp_Listar_Productos_texto'andtype='P')
dropprocedureusp_Listar_Productos_texto-- drop,elimina el usp
go
createprocedureusp_Listar_Productos_texto
@xDatovarchar(40)=''
as
selectNombreProductofromProductos
whereNombreProductoLike@xDato+'%'
return
----
executeusp_Listar_Productos_texto'e'--si no le envio nada en parntesis.

-- EXISTS .- es una funcin SQL que devuelve verdadero cuando una subconsulta retorna al
menos una fila y devuelve falso cuando no retorna ninguna fila.
--SELECT CO_CLIENTE,NOMBRE FROM CLIENTES
WHERE EXISTS(SELECT * FROM MOROSOS WHERE CO_CLIENTE = CLIENTES.CO_CLIENTE AND PAGADO = 'N')

---[Link] un usp que actualice el stock de los productos segn un

porcentaje enviado como parmetro

Laboratorios SQL 18
IfOBJECT_ID('usp_Productos_Actualizar','P')IsNOTNULL-- verifica si el
procedimiento devuelve un valor
DropProcedureusp_Productos_Actualizar
go
createprocedureusp_Productos_Actualizar
@porcentajereal=0,--si no lo envio el porcentaje lo pone cero,valor por defecto.
@cateint=0 --no se asigna un texto a un nmero
as
updateProductos
setUnidadesEnExistencia=convert(int,
UnidadesEnExistencia+UnidadesEnExistencia*@porcentaje/100 )
whereIdCategora=@cate
return
----
executeusp_Productos_Actualizar10,1

---[Link] desea incrementar los precios de los productos segn las

categoras(1,2,3,...,8),enviando como parmetros los porcentajes que se
deben incrementar en el orden de los cdigos de las categoras

createprocedureusp_Precios_Actualizar
@porcentajereal=0,--si no lo envio el porcentaje lo pone cero,valor por defecto.
@cateint=0 --no se asigna un texto a un nmero
as
updateProductos
setPrecioUnidad=PrecioUnidad+PrecioUnidad*@porcentaje/100
whereIdCategora=@cate
return
----
executeusp_Precios_Actualizar10,1
Creacin deuna Base de Datos
--Archivos: Archivo de datos primario([Link]), Archivo de datos
secundario([Link])Transacciones([Link])
--Tienen nombres : Fsicos
Lgicosnombre(administracion de la base datos)

USEMASTER
GO
createDATABASEMASTER_EMPRESA--Nombre lgico
on
PRIMARY--Se crea con valores por defecto

--CREANDO ARCHIVO PRIMARIO DE DATOS

(NAME=SYSDATOS1,
FILENAME='E:\[Link]',--ARCHIVO FISICO
SIZE=200MB,--Defines tamao de BD en megabytes
MAXSIZE=400MB,
FILEGROWTH=20),--Tamao del incremento-FILEGROWTH(10% del original) del
grupo de archivos.

--CREANDO ARCHIVO SECUNDARIO DE DATOS(se guardan la data pura)

(NAME=SYSDATOS2,
FILENAME='E:\[Link]',
SIZE=200MB,

Laboratorios SQL 19
MAXSIZE=400MB,
FILEGROWTH=20)

--CREANDO ARCHIVO DE TRANSACCIONES (se registra procedimientos

almacenados,vistas,funciones,etc).
LOGON
(NAME=SYSLOG1,
FILENAME='E:\[Link]',
SIZE=200MB,
MAXSIZE=400MB,
FILEGROWTH=20),
(NAME=SYSLOG2,
FILENAME='E:\[Link]',
SIZE=200MB,
MAXSIZE=400MB,
FILEGROWTH=20)
Go

--CREAR TABLA EN EL GRUPO DE VENTAS

CREATETABLECHEQUES
(
IdDOCSmallint,
Ndocvarchar(30),
IdClientevarchar(10),
FechaSmalldatetime,
Verificadobit,
Empleadosmallint
)
onGrupo_Ventas--tabla se crea dentro de este grupo de archivos de la BD.
go

--Nota:Si no se define un tamao para el Archivo primario entonces se

fija un tamao inicial de 3 mb y no se fijar un tamao mximo y el
FILEGROUP ser un 10% del tamao inicial del archivo primario.

--Consulta de devuelve el nombre del grupo

selectFILEGROUP_NAME(1)
selectFILEGROUP_NAME(2)

Unin de tablas
CreateDatabaseBDMASTER
UseBDMASTER
--CREAR TABLAS --
CreateTableTabla01
(IdPedidointPrimaryKey,
IdClientechar (5)notnull,
MontoSmallmoneynotnull,
FechaSmallDateTime
)
go
CreateTableTabla02
(IdPedidointPrimaryKey,
IdClientechar (5)notnull,
MontoSmallmoneynotnull,
FechaSmallDateTime

Laboratorios SQL 20
)
go
CreateTableTabla03
(IdPedidointPrimaryKey,
IdClientechar (5)notnull,
MontoSmallmoneynotnull,
FechaSmallDateTime
)
go

Adicionar data a las tablas

--Tabla 01--
INSERTINTOTabla01values(1,'aaaaa',250,'01/10/2011')
INSERTINTOTabla01values(2,'bbbbb',450,'02/10/2011')
INSERTINTOTabla01values(3,'ccccc',550,'03/10/2011')
go
--Tabla 02--
INSERTINTOTabla02values(4,'ddddd',350,'04/10/2011')
INSERTINTOTabla02values(5,'eeeee',150,'05/10/2011')
INSERTINTOTabla02values(6,'fffff',650,'06/10/2011')
go
--Tabla 03--
INSERTINTOTabla03values(4,'ddddd',1050,'10/10/2011')
INSERTINTOTabla03values(7,'eeeee',1503,'11/10/2011')
INSERTINTOTabla03values(8,'fffff',6050,'12/10/2011')
go

Combinar tablas mediante el operador UNION

--Caso1.- Usando el operador UNION sin quitar las filas duplicadas del
conjunto de resultados.
select*fromTabla01
UNION
(Select*fromTabla02
UNION
select*fromTabla03)
go
---Agregar un registro de la tabla03 a la tabla02
INSERTINTOTabla02values(8,'fffff',6050,'12/10/2011')
--Caso2.- Cuando los datos son iguales el operador UNION quita las filas
duplicadas.
select*fromTabla01
UNION
(Select*fromTabla02
UNION
select*fromTabla03)
go
--Caso3.- Cuando el oprerador incluye todas las filas duplicadas.
select*fromTabla01
UNIONALL-- une todos
(Select*fromTabla02
UNIONALL

Laboratorios SQL 21
select*fromTabla03)
go
select*intoTablaFinalfromTabla01
UNIONALL
(Select*fromTabla02
UNIONALL
select*fromTabla03)

Modificar la estructura de una tabla

--Adicionar una columna
AlterTableTablaFinal
addEstadobitnull
go
execsp_helpTablaFinal

--Modificar una columna--

AlterTableTablaFinal
AlterColumnEstadointnull
go
execsp_helpTablaFinal

--Eliminar una columna--

AlterTableTablaFinal
dropcolumnEstado
go
execsp_helpTablaFinal

Revisin de restricciones con columnas Primary Key y

Foreing Key

--Establece que los datos que existe en una columna tambin debe existir
en la otra tabla.

--Creamos la tabla principal con clave primaria

createtableClientes
(IdClienteintPrimaryKey)
go

--Creamos la tabla secundaria con clave fornea

--Establecer una retriccion de Integridad Referencial
createtablepedidos
(IdPedidointIDENTITYPrimaryKey,
IdClienteintnotnullREFERENCESClientes(IdCliente)
)
go

---Prueba--
InsertIntoPedidos(IdCliente)values(5)
--error porque no existe el cliente(5)

--Si adicionamos el cliente(5)

Laboratorios SQL 22
InsertIntoClientesvalues(5)
InsertIntoClientesvalues(6)
InsertIntoClientesvalues(7)
InsertIntopedidos(IdCliente)values(5)
InsertIntopedidos(IdCliente)values(6)
InsertIntopedidos(IdCliente)values(7)

--otra forma
CreateTableComprobantes
(IdDocumentointIdentityPrimaryKey,
IdPediint
foreignkey(IdPedi)ReferencesPedidos(IdPedido)
)
Go

--Prueba--
InsertIntoComprobantes(IdPedi)values(2)
InsertIntoComprobantes(IdPedi)values(4)
InsertIntoComprobantes(IdPedi)values(5)

select*frompedidos
select*fromComprobantes
select*fromClientes

PRCTICA 02/11/11

--- [Link] el procedimiento almacenado que muestre los pedidos que

pertenecen aun mes y un ao , mostrando el nombre del cliente, el
contacto y el nombre del vendedor.

Createprocusp_pedidos_mostrar
@mesint,
@anoint
As
[Link],[Link],[Link],[Link]
to,[Link]+' '+[Link]
FROMPedidosPInnerJoinClientesC
[Link]=[Link]
InnerJoinEmpleadosE
[Link]=[Link]
WHEREMONTH(FechaPedido)=@mesANDYEAR(FechaPedido)=@ano

[Link]
Go
--- Prueba ---
execusp_pedidos_mostrar5,1996

Laboratorios SQL 23
---[Link] el procedimiento almacenado que muestre la cantidad de pedidos
de un vendedor en donde haya enviado un determinado producto.

CreateprocPedidos_Vendedor
@provarchar(20),
@venvarchar(30)
as
selectcount([Detalles de pedidos].IdPedido)ascantidad
FROMPedidosINNERJOINEmpleados
[Link]=[Link]
innerjoin[Detalles de pedidos]
[Link]=[Detalles de pedidos].IdPedido
innerjoinProductos
on[Detalles de pedidos].IdProducto=[Link]
[Link],[Link]

[Link]=@[Link]=@ven
Go
--Prueba--
execPedidos_Vendedor'Pez espada','King'

Laboratorios SQL 24
---[Link] el procedimiento almacenado que devuelva el total de
descuentos de los pedidos realizados entre dos fechas.

CREATEPROCEDUREusp_Estadistica3
@Rrealoutput,
@f1smalldatetime,
@f2smalldatetime
AS
Select@R=SUM([Link]*[Link]*([Link]))---A que precio fue vendido
From PedidosPinnerjoin[Detalles de pedidos]D
[Link]=[Link]
whereFechaPedidobetween@f1and@f2
Return@R
--Prueba--
Declare@Resulreal
Executeusp_Estadistica3@Resuloutput,'01/01/1995','31/01/1995'
print@Resul

EXMEN PARCIAL 09/11/11

---[Link] una base de datos mediante un script T-SQL, con las siguientes
caractersticas:
Nombre : SysExamen
Archivo de Datos : SysExa_Dat
Tamao : 500MB
Archivo de transacciones: SysExa_Log

CreateDataBaseSysExamen
on
primary
(NAME=SysExa_Dat,
Filename='E:\SysExa_Dat.mdf',
size=500MB,
filegrowth=20)
logon
(NAME=SysExa_log,
Filename='E:\SysExa_log.ldf',
size=50MB,--- El taman es un 10% del tamao del archivo de datos.

Laboratorios SQL 25
filegrowth=20)
go

---[Link] una tabla con la instruccin select into , de los 10 productos

ms carosllamada Productos _Caros copiando: el IdProducto,
NombreProducto,PrecioUnidad y NombreCategora.

selecttop 10
[Link],[Link],[Link],P.
SuspendidoasSituacin,[Link]
fromProductosPinnerjoinCategorasC
[Link]=[Link]
[Link]
select*fromProductosCaros

---[Link] en la base de datos SysMaster,escribir los scripts T-SQL, que

permitas crear las siguientes tablas:
clientes -->datos principales del cliente
cuenta_ahorros -->datos de la cuenta de la cuenta de ahorra del cliente
operaciones -->operaciones que realiza el cliente con una de ahorros en
una fecha determinada.

CreateDataBaseSysMaster
useSysMaster

--tabla clientes
createtableClientes
(IdClienteintprimaryKey,
NombreClientechar(40)notnull,
Direccinchar (50)notnull,
telchar (10)notnull,
)
go

--tabla Cuenta de Ahorros

Laboratorios SQL 26
createtableCuenta_Ahorros
(NroCuentaintprimarykey,
tipoCuentachar (20)notnull,
cargosmallmoneynotnull,
IdClienteint
foreignkey (IdCliente)ReferencesClientes(IdCliente)
)
go

--tabla Operaciones
createtableOperaciones
(CodOpechar(5)primaryKeynotnull,
nomOpevarchar(20)notnull,
Fecha_opesmalldatetimenotnull,
nroCuentaint
foreignkey(NroCuenta)ReferencesCuenta_Ahorros(NroCuenta)
)
go

Funciones definidas por el usuario

---Es una porcin encapsulada de cdigo que puede ser reutilizada por
diferentes programas.

--[Link] obtener tiempos de ejecucin mucho ms rpidos

---[Link] CON VALOR DE TABLA DE VARIAS INSTRUCCIONES

--Creamos la funcin ListadoPais,luego el parmetro de entrada(@pais)con

su tipo de dato

CreatefunctionListadoPais(@paisvarchar(100))

--Con la clsula returns defino el nombre de variable local para la tabla

returns@clientestable-- tipo de dato "table"

--Formato de la tabla
(IdClientevarchar(5),NombreCompaavarchar(50),NombreContacto
varchar(100),Pasvarchar(15))
As

Laboratorios SQL 27
--Cuerpo de la instruccion "begin..end"
begin
--Inserto filas en la variable(tabla que sera retornada)
Insert@clientes
selectIdCliente,NombreCompaa,NombreContacto,Pas fromClientes
wherePas=@pais
Return-- Return indica que las filas insertadas en la variable son retornadas
end

---Prueba---
--Este tipo de funcin puede ser referenciada en el "from" de una consulta.

Select*[Link]('Argentina')

---[Link] CON VALOR DE TABLA EN LNEA

--[Link] funcin con valores de tabla en lnea retorna una

tabla(DataTable) que es el resultado de una nica instruccin "select".

--Creamos la funcion ListadoPais2 luego declaramos el parmetro @pais con

su tipo de dato
CreatefunctionListadoPais2(@paisvarchar(15))
returnstable--- Con return especifico "table" con el tipo de dato a retornar
as
return--- La clsula return contiene una sola instruccion "select" entre parntesis
(
selectIdCliente,NombreCompaa,NombreContacto,Pas fromclientes
wherePas=@pais
)
---Prueba---
Select*fromdbo.ListadoPais2('Argentina')

Laboratorios SQL 28
--- [Link] CON VALOR DE TABLAEN LNEA
IFOBJECT_ID(N'fn_VentasHistoricas',N'IF')ISNOTNULL
DROPFUNCTIONfn_VentasHistoricas;
GO
CREATEFUNCTIONfn_VentasHistoricas(@idchar(5))
RETURNSTABLE
AS
RETURN
([Link],[Link],SUM([Link]*[Link])AS'Tot
al'
FROMPedidosINNERJOINClientes
[Link]=[Link]
INNERJOIN[Detalles de pedidos]D
[Link]=[Link]
INNERJOINProductosP
[Link]=[Link]
[Link]=@id
[Link],[Link]);
GO
---Prueba
SELECT*FROMfn_VentasHistoricas('Bergs')

--- 4. FUNCIN CON VALOR ESCALAR

--- Retorna un resultado con un valor escalar(un dato lgico,un texto,dato de
tipo boolean).

IFOBJECT_ID(N'[Link]',N'FN')ISNOTNULL
[Link];

--- La funcin evala una fecha proporcionada y devuelve un valor

que designa la posicin de esa fecha en una semana.

[Link](@Datedatetime)
RETURNSint
AS
BEGIN
RETURNDATEPART(weekday,@Date)
END
GO

Laboratorios SQL 29
---Prueba
[Link](CONVERT(DATETIME,'20020201',101))ASDayOfWeek;
GO

--- 5. FUNCIN CON VALOR ESCALAR

CREATEFUNCTION[dbo].[CalcularSemanas](@DATEdatetime)
RETURNSint
AS
BEGIN
DECLARE@nSemanasint
SET@nSemanas=DATEPART(wk,@DATE)+1
-DATEPART(wk,CAST(DATEPART(yy,@DATE)
ASCHAR(4))+'0104')
IF (@nSemanas= 0)
SET@nSemanas=[Link](CAST(DATEPART(yy,@DATE)-1
ASCHAR(4))+'12'+CAST(24 +DATEPART(DAY,@DATE)
ASCHAR(2)))+ 1
IF ((DATEPART(mm,@DATE)=12)AND
((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
SET@nsemanas= 1
RETURN(@nSemanas)
END

TRIGGERS
--Un trigger (o disparador), es un procedimiento que se ejecuta cuando se cumple
una condicin establecidaal realizar una operacin de insercin
(INSERT),actualizacin (UPDATE) o borrado (DELETE).

--Tipos:
Data Manipulation Languaje (DML): se ejecutan con las intrucciones INSERT,
UPDATE or DELETE.
Data Definition Languaje (DFL): se ejecutan cuando se crean, alteran o
borran objetos de la base de datos.

--Efectos y Caractersticas:
No aceptan parmetros o argumentos (pero podran almacenar los datos
afectados en tablas temporales)
No pueden ejecutar las operaciones COMMIT o ROLLBACK por que estas son
parte de la sentencia SQL del disparador (nicamente a travs de
transacciones autnomas)

Laboratorios SQL 30
Pueden causar errores de mutaciones en las tablas, si se han escrito de manera
deficiente.

--Por ejemplo, puede crearse un trigger de insercin en la tabla "ventas" que

compruebe el campo "stock" de un artculo en la tabla "articulos"; el disparador
controlara que, cuando el valor de "stock" sea menor a la cantidad que se
intenta vender, la insercin del nuevo registro en "ventas" no se realice.
--Los triggers se crean con la instruccin [Link] instruccin
especifica la tabla en la que se define el disparador,los eventos para los que se
ejecuta y la instrucciones que contiene.

Mantenimiento de la Tabla Productos

--- [Link] Producto---

CreateTriggerT_Prod_Adicionar---Nombre del disparador

ONProductos--nombre de la tabla para la cual se establece el trigger
FORINSERT-- se define el evento que activar el trigger
AS-- se especifican las condiciones y acciones del disparador
BEGIN
SELECTNombreProducto,IdProveedor,IdCategora,PrecioUnidad,UnidadesEnExi
stenciaFROMINSERTED
END
---Prueba---
InsertintoProductos(NombreProducto,IdProveedor,IdCategora,PrecioUnidad,U
nidadesEnExistencia)
values('Mostaza',20,3,19,16)

---[Link] Producto---

a.CreateTriggerT_Prod_Actualizar
OnProductos
FORUPDATE
AS
IFUPDATE(NombreProducto)
BEGIN
SELECTNombreProductoFROMINSERTED
END
---Prueba---
UpDateProductos
SetNombreProducto='Chokolate Choko'
WhereIdProducto=85

Laboratorios SQL 31
[Link]
OnProductos
FORUPDATE
AS
IFUPDATE(PrecioUnidad)
BEGIN
SELECT'EL PRECIO DEBE SER MAYOR 0.00'asMensaje
SELECTPrecioUnidadasNuevoPrecioFROMINSERTED
END
---Prueba---
UpdateProductos
setPrecioUnidad=4.5
whereIdProducto=74

--- 3. Eliminar Producto---

CREATETRIGGERControlEliminarProducto
OnProductos
FORDELETE
AS
BEGIN
INSERTINTOEmpleados(Apellidos,Nombre,Notas)
values('Choque','Alexander','Elimino un Producto')
END
---Prueba---
deletefromProductos
whereIdProducto=80

Laboratorios SQL 32
--- 4. El siguiente trigger DML imprime un mensaje en el cliente cuando
alguien intenta agregar o cambiar datos en la tabla Clientes.

CREATETRIGGERtr_Alerta
ONClientes
AFTERINSERT,UPDATE
AS
RAISERROR ('Notificando Atencin al Cliente', 16, 10)
GO
---Prueba---
InsertintoClientes(IdCliente,NombreCompaa,NombreContacto)
Values('ISTBP','Instituto El Buen Cordero','Jaime Saucedo')

--- 5. En el ejemplo siguiente se utiliza un trigger DDL para imprimir un

mensaje si se produce un evento CREATE DATABASE en la instancia actual
del servidor y se utiliza la funcin EVENTDATA para recuperar el texto de
la instruccin Transact-SQL correspondiente.

IFEXISTS(SELECT*FROMsys.server_triggers
WHEREname='ControlBD')
DROPTRIGGERControlBD
ONALLSERVER
GO
----
CREATETRIGGERControlBD
ONALLSERVER
FORCREATE_DATABASE

Laboratorios SQL 33
AS
PRINT'Base deDatos ha sido Creada.'
SELECTEVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'
,'nvarchar(max)')asBaseDatos
GO
---Prueba---
CreateDatabaseEclipse

Triggers DML

CREATEDATABASECONSORCIO
/*CREANDO TABLA en db_trigger*/

CREATETABLERegistro_Operacion )
(
Tipo_tCHAR(1)NOTNULL, CREATETABLETransaccion_Clientes
FechaDATETIMENOTNULL, (
EstacionVARCHAR(30)NOTNULL, IdCHAR(1)NOTNULL,
NumclieINTNOTNULL, FechaDATETIMENOTNULL,
NombreVARCHAR(30)NULL, EstacionVARCHAR(30)NOTNULL,
RepclieINTNULL, NumclieINTNOTNULL,
LimitecreditoMONEYNULL, NombreVARCHAR(30)NULL,

Laboratorios SQL 34
RepclieINTNULL, )
LimitecreditoMONEYNULL,
/*CREANDO UN TRIGGER PARA REGISTRAR UNA INSERCCION*/
--- 1.CreateTRIGGERtr_InsertCliente
ONTransaccion_Clientes
FORINSERT
AS
Begin

INSERTINTORegistro_Operacion(Tipo_t,Fecha,Estacion,Numclie,Nombre,Repclie,Limite
credito)
SELECT'I',getdate(),host_name(),
Numclie,Nombre,Repclie,LimitecreditoFROMINSERTED
End
go
/*INSERTANDO DATOS EN LA TABLA c_cliente*/
INSERTINTOTransaccion_ClientesVALUES('M','14/05/1989','INVIERNO',2100,'JU
AN',129,9999.99)
INSERTINTOTransaccion_ClientesVALUES('J','08/10/1988','OTOO',3588,'JOSE'
,105,99999.99)

SELECT*FROMTransaccion_Clientes

SELECT*FROMRegistro_Operacion

/*CREANDO UN TRIGGER PARA ACTUALIZAR UN REGISTRO DE CLIENTE*/

--- 2.createTRIGGERtr_Updatecliente
ONTransaccion_Clientes
FORUPDATE
AS
BEGIN
INSERTINTORegistro_Operacion(Tipo_t,Fecha,Estacion,Numclie,
Nombre,Repclie,Limitecredito)SELECT'A',getdate(),host_name(),
Numclie,Nombre,Repclie,LimitecreditoFROMINSERTED
End
Go
/*ACTUALIZANDO DATOS EN LA TABLA Transaccion_Clientes*/
UPDATETransaccion_ClientesSETLimitecredito=0 WHEREId='J'

Laboratorios SQL 35
SELECT*FROMTransaccion_Clientes

SELECT*FROMRegistro_Operacion

/*Triggers DDL*/
CREATETRIGGERSEGURIDAD
ONDATABASE
FORCREATE_TABLE,DROP_TABLE,ALTER_TABLE
AS
BEGIN
RAISERROR ('Imposible crear, borrar ni modificar tablas,Faltan
permisos', 16, 1)
ROLLBACKTRANSACTION
END
---Prueba----
DROPTABLEPRODUCTOS

[Link] de recuperacin de datos

createdatabasePRUEBA
usePRUEBA
CreatetableUSUARIOS( )
CODIGOnchar(5)notnull, CreatetableNIVEL
LOGInchar (30)null, (
PASSnchar (30)null, IdUsuariointIDENTITY(1,1)notnull,
EMAILnchar (30)null, IdNivelbigint,
FECHAsmalldatetimenull, constraintPK_IdUsuarioPRIMARYKEY
CONSTRAINTPK_CODIGOPRIMARYKEY(COD (IdUsuario)
IGO) )

Laboratorios SQL 36
--Crear usp utlizando una transacciones
createproceduresp_AdicionarUsuario
@CODIGOnchar(5),
@LOGInchar (30),
@PASSnchar (30),
@EMAILnchar (30),
@IdNivelbigint,
@MSGvarchar(100)output
AS
BEGIN--- Se utiliza cuando se realiza varias intrucciones
SETNOCOUNTon;
BEGINTRANtr_Adicionar
BeginTry
InsertIntoUSUARIOS(CODIGO,LOGI,PASS,EMAIL,FECHA)
Values(@CODIGO,@LOGI,@PASS,@EMAIL,GETDATE())
InsertIntoNIVEL(IdNivel)VALUES (@IdNivel)
Set@MSG='El usuario registro datos correctamente..'
CommitTrantr_Adicionar---confirmar la transaccion
EndTry
BeginCatch
set@MSG='Ocurrio un error:'+ERROR_MESSAGE()+'en la
lnea:'+CONVERT(varchar(255),ERROR_LINE())+'.'
--[Link] los cambios propuestos en una transaccin de base de datos en espera
ROLLBACKTRANtr_Adicionar
EndCatch
End
--Prueba--
DECLARE@mesgvarchar(100)
EXECsp_AdicionarUsuario'US002','HULK','114','hulk@[Link]',10,@mesgou
tput
print@mesg

DECLARE@mesgvarchar(100)
EXECsp_AdicionarUsuario'US0020000','IROMAN','115','IROMAN@[Link]',9,
@mesgoutput
print@mesg

---[Link] el procedimiento almacenado que permita modificar el precio

unitario de los productos que hayan tenido descuento, de los pedidos
enviados en un determinado ao. El nuevo precio sera : el precio actual
menos el porcentaje de descuento, que se encuentra en la tabla detalle de
pedido.

---[Link] una tabla con los pedidos que pertenecen a un vendedor

realizados durante un ao.