Alumno: Elen Melgarejo Alcedo

-- Ejercicios de Procedimientos y Triggers

--1. Implemente los ejercicios resueltos (1 al 4) del pdf de Procedimientos
adjunto.
--a) Para cada código muestre 5 ejemplos de la salida de los stores.
--Escribir un procedimiento parametrizado que reciba una cadena de
--caracteres y la visualice al revés.
IF OBJECT_id('AlReves') IS NOT NULL
DROP PROC AlReves
go
--
create proc AlReves(@cadena varchar(20))
as
select reverse(@cadena)
go
--
exec AlReves Adminitrator
exec AlReves melgarejo
exec AlReves chalo
exec AlReves mejor
exec AlReves SqlServer

--b) Ídem pero que muestre el carácter que ocupa la posición central de la
cadena.
if object_id('PosicionCentral') is not null
drop proc PosicionCentral
go
create proc PosicionCentral(@cadena varchar(20))
as
select substring(@cadena,(len(@cadena)/2)+1,1)

exec PosicionCentral melga

exec PosicionCentral chalo
exec PosicionCentral elen
exec PosicionCentral alcedo
exec PosicionCentral escuela
--c) Ídem pero que transforme la cadena de forma que el 1er carácter y el último
se muestren en mayúsculas.
if object_id('prime_ult') is not null
drop proc prime_ult
go
create proc prime_ult(@cadena varchar(20))
as
select upper(left(@cadena,1))+substring(@cadena,2,len(@cadena)-
2)+upper(right(@cadena,1))
go
exec prime_ult melga
exec prime_ult chalo
exec prime_ult elen
exec prime_ult alcedo
exec prime_ult escuela

if object_id('DesgloseDinero') is not null

drop proc DesgloseDinero
go
--
create proc DesgloseDinero (@cantidad money)
as
select cast((round((@cantidad/5),0,1)*5) as int) as billetes,
@cantidad%5 as Monedas
go
exec DesgloseDinero 94852.32
exec DesgloseDinero 74257.32
exec DesgloseDinero 79375.79
exec DesgloseDinero 14525.47
exec DesgloseDinero 12898.97
--
--2. Realice los ejercicios resueltos del pdf de Triggers adjunto. Muestre las
salidas de los ejercicios.
--Un ejemplo de triggers para el control de Stock.
USE master
GO
IF EXISTS (SELECT NAME FROM sys.databases
WHERE name = 'StockArticulos')
BEGIN
DROP DATABASE StockArticulos
END
CREATE DATABASE StockArticulos
GO
--
USE StockArticulos
GO
CREATE TABLE dbo.Articulos
(ID INT PRIMARY KEY,
NOMBRE VARCHAR(100),
STOCK DECIMAL (18,2))
GO
CREATE TABLE dbo.Movimientos
(TRANSACCION INT,
FECHA DATE DEFAULT(GETDATE()),
ARTICULO_ID INT FOREIGN KEY
REFERENCES DBO.ARTICULOS(ID),
CANTIDAD DECIMAL(18,2),
TIPO CHAR(1) CHECK (TIPO ='I' OR TIPO = 'O'))
GO
SELECT * FROM Articulos
SELECT * FROM Movimientos
--
INSERT INTO dbo.Articulos(ID,NOMBRE,STOCK) VALUES
(1,'Monitores',0)--,(2,'CPU',0),(3,'Mouse',0)
GO
--Creamos los triggers para tener actualizado los artículos.
--No retorna el mensaje de cantidad de registros afectados.
CREATE TRIGGER dbo.MovimientosInsert ON dbo.Movimientos
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
UPDATE DBO.ARTICULOS
SET STOCK = STOCK + T.PARCIAL
FROM DBO.ARTICULOS A
INNER JOIN
( SELECT ARTICULO_ID,
SUM(CASE WHEN TIPO='I' THEN CANTIDAD ELSE -
CANTIDAD END)
AS PARCIAL FROM INSERTED
GROUP BY ARTICULO_ID) T
ON A.ID = T.ARTICULO_ID
END
go
--Creamos los triggers para tener actualizado los artículos.
--No retorna el mensaje de cantidad de registros afectados.

CREATE TRIGGER dbo.Movimientosdelete ON dbo.Movimientos

FOR INSERT
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.Articulos
SET STOCK = STOCK - T.PARCIAL
FROM dbo.Articulos A
INNER JOIN
( SELECT ARTICULO_ID,
SUM(CASE WHEN TIPO='I' THEN CANTIDAD ELSE -
CANTIDAD END)
AS PARCIAL FROM DELETED
GROUP BY ARTICULO_ID
) T
ON
A.ID = T.ARTICULO_ID
END
GO
--
SELECT A.ID, A.NOMBRE, A.STOCK FROM dbo.Articulos A

--3 Escribe una función que solicite como parámetros 2 números enteros A y B, y
que devuelva la tabla de multiplicar
--de A del 1 al número B usando un bucle WHILE, por ejemplo para la siguiente
instrucción deberá resolver:
CREATE function [TABLA_MULT](@A INT , @B INT)
RETURNS @TABLA3 TABLE (RESUL VARCHAR(30))
AS
BEGIN
DECLARE @X INT=1
WHILE (@X<=@B)
BEGIN
INSERT INTO @TABLA3(RESUL)
VALUES (CONCAT(@A,' x ',@X,' = ',@A*@X ))
SET @X=@X+1
END
RETURN
END
SELECT * FROM [dbo].[TABLA_MULT](9,10)

USE HR
--4. Construya un store que muestre cuente el número de las filas totales de las
tablas:
--[dbo].[departments]
--[dbo].[countries]
--[dbo].[jobs]
if object_id('SP_FILTAS_TOTAL') is not null
drop proc SP_FILTAS_TOTAL
go
create PROCEDURE SP_FILTAS_TOTAL
AS
BEGIN
WITH FILAS AS
(
SELECT 'departments' AS TABLAS, COUNT(*) AS FILAS FROM departments
UNION
SELECT 'countries' AS TABLAS, COUNT(*) FROM countries
UNION
SELECT 'jobs' AS TABLAS , COUNT(*) FROM jobs
)
--
SELECT * FROM FILAS
END ;
--
EXECUTE SP_FILTAS_TOTAL
----FORMA 2
SP_FILTAS_TOTAL
CREATE PROCEDURE SP_FILTAS_TOTAL2
AS
BEGIN
select SO.Name as Tabla,si.rows as 'filas'
from sysobjects as SO
join sysindexes as SI on SO.Id = SI.id
WHERE SO.Name IN ('departments','countries','jobs')
AND si.rows<>0
order by si.rows desc
END
EXECUTE SP_FILTAS_TOTAL2

---5. Construya un store que muestre los datos de la tabla de empleados

([dbo].[employees]), debera permitir enviar como parametro el job_id
if object_id('Sp_datos_emple') is not null
drop proc Sp_datos_emple
go
create procedure Sp_datos_emple (@job_id varchar(10))
as
begin
SELECT * FROM [dbo].[employees]
WHERE job_id=@job_id
END
EXEC Sp_datos_emple 'PU_CLERK'
--6. Construya un store que muestre los siguientes datos: (Tablas a utilizar
[dbo].[employees] / [dbo].[locations])
if object_id('SP_DATOS_TABLAS') is not null
drop proc SP_DATOS_TABLAS
go
CREATE PROCEDURE SP_DATOS_TABLAS
AS
BEGIN
SELECT [employee_id], [first_name], [last_name], B.[department_id],
B.[department_name],
C.[street_address], C.[city] FROM [dbo].[employees] A
INNER JOIN [dbo].[departments] B ON B.department_id=A.department_id
INNER JOIN [dbo].[locations] C ON C.location_id=B.location_id
END;
EXECUTE SP_DATOS_TABLAS

--7. En base al store del ejericio 3 aplique el filtro para mostras los empleados
de las ciudades: Toronto y Munich (Use ALTER)
alter PROCEDURE SP_DATOS_TABLAS
AS
BEGIN
declare @ciudad1 varchar(15);
declare @ciudad2 varchar(15);
set @ciudad1 ='Toronto'
set @ciudad2='Munich'

SELECT [employee_id], [first_name], [last_name], B.[department_id],

B.[department_name],
C.[street_address], C.[city] FROM [dbo].[employees] A
INNER JOIN [dbo].[departments] B ON B.department_id=A.department_id
INNER JOIN [dbo].[locations] C ON C.location_id=B.location_id
where c.city=@ciudad1 or c.city=@ciudad2
END;
EXECUTE SP_DATOS_TABLAS

--8. Construya una función escalar que reciba el salario anual ([salary]) y el
porcentaje de comisión ([comission_pct]),
--y calcule el salario total neto (si el porcentaje de comisión es nulo, asumir
que es cero).
--Descontar el 20% por concepto de impuestos, siempre que el salario total sea
mayor a 30000 soles.
--Finalmente, si el empleado es de ventas (donde el job_id empiece con "SA_")
adicionar una bonificación del 10%
--después del decuento anterior.
if object_id('Fn_SALARIO','FN') is not null
drop function Fn_SALARIO
go
create FUNCTION Fn_SALARIO
(@SALARY MONEY,
@COMI MONEY
)
RETURNS FLOAT
as
begin
declare @SALDO FLOAT
BEGIN
SELECT @SALDO =E.salary+E.salary*(E.COMI/100) FROM
(SELECT *, COMI= CASE WHEN commission_pct IS
NULL THEN 0
ELSE commission_pct
END FROM EMPLOYEES ) E
WHERE E.SALARY=@SALARY AND E.COMI=@COMI
END ;
BEGIN
DECLARE @TRA VARCHAR(20)
SELECT DISTINCT @TRA=[job_id] FROM (SELECT *, COMI= CASE WHEN
commission_pct IS NULL THEN 0
ELSE commission_pct
END FROM EMPLOYEES) F
WHERE F.JOB_ID LIKE 'SA_%' AND F.SALARY=@SALARY AND
F.COMI=@COMI
END;
IF(@SALDO>30000 AND @TRA LIKE 'SA_%')
SET @SALDO=(@SALDO-0.2*@SALDO)+0.1*(@SALDO-0.2*@SALDO)
ELSE IF (@SALDO>30000 AND @TRA NOT LIKE 'SA_%')
SET @SALDO=(@SALDO-0.2*@SALDO)
ELSE IF (@SALDO<30000 AND @TRA LIKE 'SA_%')
SET @SALDO=@SALDO+@SALDO*0.1
ELSE IF (@SALDO<30000 AND @TRA NOT LIKE 'SA_%')
SET @SALDO=@SALDO
RETURN @SALDO
END

---9 Construya una función que reciba un [employee_id] y devuelva un mensaje como
el siguiente:
--"El empleado STEVEN KING con cargo PRESIDENT se dio de alta el 17 de
junio del 2003."

create function Fn_mensaje (@employee_id int)

returns table
 as
return
(select 'El empleado '+ UPPER(first_name)+' '+UPPER(last_name)+
' con cargo '+upper(job_title)+' se dio de alta el '+
cast(day(hire_date) as char(2))+' de '+
rtrim(cast(datename(month,hire_date) as char(10)))+' del
'+cast(YEAR(hire_date)as char(4)) as Datos

from [dbo].[employees] a
inner join [dbo].[jobs] b on A.job_id=b.job_id
where A.employee_id=@employee_id
)
select * from Fn_mensaje(100)

---10 Construya un disparador que cree una tabla de auditoria a la hora de

adicionar, eliminar o actualzar registros de
--la tabla de empleados([dbo].[employees]). Guíese de lo que vimos en clase.

--
CREATE TABLE [dbo].[AUDI_employees](
[employee_id] [int] NOT NULL,
[first_name] [varchar](20) NOT NULL,
[last_name] [varchar](25) NOT NULL,
[email] [varchar](25) NOT NULL,
[phone_number] [varchar](20) NOT NULL,
[hire_date] [date] NOT NULL,
[job_id] [varchar](10) NOT NULL,
[salary] [money] NOT NULL,
[commission_pct] [money] NULL,
[manager_id] [int] NULL,
[department_id] [int] NULL,
Operacion varchar(5),
FechaOperacion datetime)

CREATE TRIGGER trg_tb_carga ON [dbo].[employees]

AFTER INSERT,UPDATE,DELETE
AS
BEGIN
declare @employee_id int,@first_name varchar(30),@last_name
varchar(30),@email varchar(30),
@phone_number varchar(30),@hire_date date,@job_id varchar(15),@salary money,
@commission_pct money,@manager_id int,@department_id int
---obtener la data inserted/updated
select @employee_id=employee_id , @first_name=first_name, @last_name=last_name
,@email=email ,
@phone_number=phone_number , @hire_date=hire_date ,
@job_id=job_id , @salary=salary, @commission_pct=commission_pct
,@manager_id=manager_id ,
@department_id=department_id from inserted
--obtener la data de deleted
select @employee_id=employee_id , @first_name=first_name, @last_name=last_name
,@email=email ,
@phone_number=phone_number , @hire_date=hire_date ,
@job_id=job_id, @salary=salary, @commission_pct=commission_pct
,@manager_id=manager_id ,
@department_id=department_id from deleted
--insertar caso
if exists(select * from inserted) and not exists(select * from deleted)
BEGIN
insert into [dbo].[AUDI_employees] ([employee_id] , [first_name],
[last_name] , [email] ,
[phone_number] , [hire_date] , [job_id] , [salary],
[commission_pct] , [manager_id] ,
[department_id] , Operacion , FechaOperacion )
VALUES( @employee_id , @first_name, @last_name , @email ,
@phone_number , @hire_date , @job_id, @salary,
@commission_pct , @manager_id ,
@department_id , 'INS', GETDATE() )
END
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
BEGIN
insert into [dbo].[AUDI_employees] ([employee_id] , [first_name],
[last_name] , [email] ,
[phone_number] , [hire_date] , [job_id] , [salary],
[commission_pct] , [manager_id] ,
[department_id] , Operacion , FechaOperacion )
VALUES( @employee_id , @first_name, @last_name , @email ,
@phone_number , @hire_date , @job_id, @salary,
@commission_pct , @manager_id ,
@department_id , 'UPD', GETDATE() )
END
IF EXISTS(SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM
inserted)
BEGIN
insert into [dbo].[AUDI_employees] ([employee_id] , [first_name],
[last_name] , [email] ,
[phone_number] , [hire_date] , [job_id] , [salary],
[commission_pct] , [manager_id] ,
[department_id] , Operacion , FechaOperacion )
VALUES( @employee_id , @first_name, @last_name , @email ,
@phone_number , @hire_date , @job_id, @salary,
@commission_pct , @manager_id ,
@department_id , 'DEL', GETDATE() )
END
END

INSERT INTO employees

SELECT 212, 'CHALO', last_name , email , phone_number ,
hire_date , job_id , salary, commission_pct , manager_id ,
department_id FROM employees WHERE [employee_id]=100
--
DELETE FROM employees WHERE employee_id=211
--
UPDATE employees
SET first_name='ELEN1'
WHERE employee_id=212
--
SELECT * FROM [AUDI_employees]
use Northwind
--11. Contruya un store que muestre las Ordenes ([dbo].[Orders]) y que tenga como
parametro la fecha de la orden
if object_id('Sp_muestra_orde') is not null
drop proc Sp_muestra_orde
go
CREATE PROCEDURE Sp_muestra_orde( @fecha datetime)
as
BEGIN
select * from [dbo].[Orders]
where OrderDate=@fecha
END
EXEC Sp_muestra_orde '04/07/1996'
--

--12. Contruya un store que muestre las Ordenes ([dbo].[Orders]) por Empleado y
que tenga como parametro el año de la orden
if object_id('Sp_muestra_orde_anio') is not null
drop proc Sp_muestra_orde_anio
go
create PROCEDURE Sp_muestra_orde_anio( @anio char(4))
as
begin
select OrderID,employeeS.Lastname,OrderDate from [dbo].[Orders]
INNER JOIN employeeS ON employeeS.EmployeeID=orders.EmployeeID
where cast(year(OrderDate) as char(4))=@anio
order by employeeS.EmployeeID asc
end
---
exec Sp_muestra_orde_anio '1996'

--13. Construya una función que reciba un [department_id] y muestre una tabla que
contenga todos los empleados de ese departamento
-- Se debe mostrar el nombre completo de cada empleado, antigüedad del
empleado en meses, salario final ([salary] y [commission_pct])
--y la descripción del empleo. De no existir un departamento con el
[department_id] ingresado que muestre un mensaje de error.
use HR
go
if object_id('dbo.pregunta_13','FN') is not null
drop function pregunta_13
go

CREATE function dbo.pregunta_13 (@department_id int)

returns @tabla table (EmployeeName nvarchar(50), antiguedad int,salario_final
float, job_title varchar(50))
as
begin
if @department_id = any(select distinct department_id from
departments)
insert @tabla
select CONCAT(a.first_name,' ',a.last_name) as
Nombre_Completo, DATEDIFF(month,a.hire_date,getdate()) as Antiguedad_meses,
((a.salary)*(1+isnull(a.commission_pct,0)*0.01)) as
salario_final, b.job_title
from employees a
inner join jobs b on a.job_id=b.job_id
inner join departments c on
a.department_id=c.department_id
where c.department_id=@department_id
else
insert @tabla
select 'DEPARTAMENTO NO EXISTE',NULL,NULL,NULL

return
end

-- Base AdventureWorks
--14. Construya un store que muestre las ventas ([Sales].[SalesOrderHeader]) por
mes teniendo como parametro el año de la venta
use AdventureWorks2014
go
--
if object_id('dbo.pregunta_14') is not null
drop procedure pregunta_14
go
create procedure dbo.pregunta_14(@anio char (4))
as
begin
SELECT * FROM [Sales].[SalesOrderHeader]
WHERE cast(year(OrderDate) as char(4))=@anio
end
--

--15. Construya un store que muestre las ventas ([Sales].[SalesOrderHeader])

acumuladas totales de un cliente en un determinado año
--teniendo como parámetros al cliente y el año de venta.

if object_id('dbo.pregunta_15') is not null

drop procedure pregunta_15
go
create procedure dbo.pregunta_15(@cliente int,@anio char (4))
as
begin
SELECT * FROM [Sales].[SalesOrderHeader]
WHERE CustomerID=@cliente AND cast(year(OrderDate) as char(4))=@anio
end
--
execute pregunta_15 30114,'2013'

--16. Implemente los ejercicios (5 y 7 resueltos, 6 y 8 propuestos) del pdf de

Procedimientos adjunto utilizando tablas de esta base de datos.
Ayuda: Tabla de productos: [Production].[Product]
Tabla de empleados: [Person].[Person]
Tabla de oficinas o puntos de venta: [Sales].[Store]
Tabla de pedidos o ventas: [Sales].[SalesOrderDetail]
Tabla de clientes: [Sales].[Customer]
---------
--5//
IF OBJECT_ID('cliente') IS NOT NULL DROP PROC cliente
GO
CREATE PROCEDURE cliente (@cliente VARCHAR (20) )
AS
BEGIN
SELECT TOP 1 pedidos.UnitPrice, productos.NAME,
pedidos.ModifiedDate, empleados.FirstName AS representante,ST.NAME AS CIUDAD
FROM [Sales].[SalesOrderDetail] PEDIDOS
INNER JOIN [Production].[Product] productos ON
productos.ProductID=pedidos.ProductID
INNER JOIN [Person].[Person] empleados ON
pedidos.OrderQty=empleados.BusinessEntityID
INNER JOIN Sales.SalesOrderHeader sO on PEDIDOS.SalesOrderID = so.SalesOrderID
inner join Sales.SalesTerritory st on st.TerritoryID = SO.TerritoryID
WHERE empleados.FirstName=@cliente
END

--6///

IF OBJECT_ID('PRODU_CLI') IS NOT NULL

DROP PROC PRODU_CLI
GO
CREATE PROCEDURE PRODU_CLI (@PRODU VARCHAR (40) )
AS
BEGIN
select TOP 1 ppER.FirstName, count(ss.ProductID) as veces_vendido
from Sales.SalesOrderDetail ss
inner join Production.Product pp on pp.ProductID = ss.ProductID
INNER JOIN [Person].[Person] PPER ON PPER.BusinessEntityID=SS.OrderQty
WHERE pp.Name ='Sport-100 Helmet, Black'
group by ppER.FirstName
order by count(ss.ProductID) desc;
END
--

--

--7//
IF OBJECT_ID('ClientesAtendidos') IS NOT NULL
DROP PROC ClientesAtendidos
GO
CREATE PROC ClientesAtendidos @nombre VARCHAR(20)
AS
BEGIN
SELECT COUNT(*) CANT FROM [Person].[Person] PP
INNER JOIN [Sales].[SalesOrderDetail] SD ON PP.BusinessEntityID=SD.OrderQty
WHERE FirstName = @nombre
GROUP BY FirstName
END
--
EXEC ClientesAtendidos 'Terri'

--8
ALTER TABLE [Sales].[SalesOrderDetail]ADD pagado BIT;
ALTER TABLE [Sales].[SalesOrderDetail] ADD fecha_pago DATETIME;
UPDATE [Sales].[SalesOrderDetail] SET pagado = 0;
--
IF OBJECT_ID('SP_UPDATE') IS NOT NULL
DROP PROC SP_UPDATE
GO
CREATE PROC SP_UPDATE (@nombre INT)
AS
BEGIN
UPDATE [Sales].[SalesOrderDetail]
SET pagado=1, FECHA_PAGO=GETDATE()
WHERE SalesOrderID=43659
END
--
EXECUTE SP_UPDATE 43659