�ݺ�ߣ

SQL Portfolio Paul Glaeser [email_address] 610-327-3801

Table of Contents Introduction – 3 BlockFlix Movie Rental -4 BlockFlix SSIS – 6 BlockFlix SSRS - 10 Piggy Bank - 14 Shred XML – 16 View - 17 Triggers – 18 Stored Procedure -19

Introduction This Presentation will illustrate key skills learned in the SQL Masters course such as SSIS, SSRS, SQLServer, T-SQL, Triggers and Views

BlockFlix Movie Rental Introduction: BlockFlix is a new and upcoming Movie Rental company. They have opened a few stores nationwide and are in process of creating a centralized database for tracking inventory, sales, customers and memberships. Audience: 3C executives to technical level employees. Project Goals: Develop the Central Database, Store database and Kiosk Database. Develop the xml file format for the vendor to use to submit movies as well as the SSIS package to read and load the data in the file. Develop the 4 core reports requested in SSRS Most Rented Movie in past month, List of Customers and Membership type Most active Customers in past month, Lost/Damaged movies with related Transactions

IMPORT XML FILE VIA SSIS AFTER CONTROL FLOW EXECUTION ADD MOVIE Demonstration Consisting of 3 linked data flows

IMPORT XML FILE VIA SSIS AFTER MOVIE/TALENT EXECUTION ADD MOVIE Demonstration

Most active customers in the past month

Most rented movies in the past month

Lost / damaged movies and their related transactions

Customers and Membership Types

P I G G Y - B A N K Introduction: Basic banking application with customers, accounts, transactions, interest , overdraft and statements Audience: Technical Management , Developers etc. Project Goals: For Customers Create and update the Customer information Accounts – support Checking and Savings Overdraft – account specific and general Transactions – Deposits, Withdrawals/Purchases, Transfers Interest Rates can be applied Build view for ATM procedures and search engine queries Protective Triggers

Shred Xml into Database Table /* Shred XML Data into appropriate tables */ -- Declare xml variable to handle xml input DECLARE @doc xml SET @doc = '<?xml version="1.0" encoding="utf-8"?> <Bank> <Customer ID="1" FirstName="John" LastName="Doe" Street="123, Fake Street" City="Anytown" State="NY" Zip="11280" HomePhone="555-123-4567" WorkPhone="555-456-7890" MobilePhone="555-111-2345" EMail="jDoe@someweb.com"> <Accounts> <Saving ID="100000" Balance="548.25" Active="1"> <Transactions> <Transaction ID="1" Type="Initial Deposit" Date="4/01/2009" Amount="500.00" NewBalance="500.00" /> <Transaction ID="5" Type="Deposit" Date="4/01/2009" Amount="50.00" NewBalance="550.00" /> </Transactions> </Saving> </Accounts> </Customer> -- Declare variable to handle the xml document in the sp_xml_preparedocument DECLARE @docHandle int -- Put XML into memory EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc --toggle identity on Set identity_insert dbo.Transactions on insert into dbo.Transactions (TransactionID, AccountID, TransactionTypeID, CustomerID, TransactionDate, TransactionAmount, NewBalance) SELECT TransactionID, AccountID, CASE WHEN [Type] = 'Initial Deposit' THEN 1 WHEN [Type] = 'Deposit' THEN 2 WHEN [Type] = 'Withdrawal' THEN 3 WHEN [Type] = 'Transfer-Withdrawal' THEN 4 WHEN [Type] = 'Transfer-Deposit' THEN 5 WHEN [Type] = 'Checkcard Purchase' THEN 6 ELSE 7 -- [Type] = 'Check' END, CustomerID, TransactionDate, TransactionAmount,NewBalance FROM OPENXML (@docHandle, '/Bank/Customer/Accounts/*/Transactions/Transaction', 1) WITH ( TransactionID int '@ID' , AccountID int '../../@ID' , [Type] varchar(20) '@Type' , CustomerID int '../../../../@ID' , TransactionDate datetime '@Date' , TransactionAmount money '@Amount' , NewBalance money '@NewBalance' ) -- toggle identity back off Set identity_insert dbo.Transactions off -- remove xml information from memory execute sp_xml_removedocument @docHandle

Create View for Statements CREATE VIEW [dbo].[AccountStatement] WITH SCHEMABINDING AS SELECT dbo.Account.CurrentBalance, dbo.Account.AccountID, dbo.TransactionType.TransactionTypeName, dbo.AccountStatus.AccountStatus, dbo.AccountType.AccountTypeName, dbo.AccountType.InterestRate, dbo.Customer.CustomerID, dbo.Customer.CustomerFirstName, dbo.Customer.CustomerLastName, dbo.Customer.CustomerMiddleInitial, dbo.Customer.Street, dbo.Customer.City, dbo.Customer.State, dbo.Customer.ZipCode, dbo.Customer.HomePhone, dbo.Transactions.TransactionDate, dbo.Transactions.TransactionAmount,dbo.Transactions.NewBalance FROM dbo.Account INNER JOIN dbo.AccountStatus ON dbo.Account.AccountStatusID = dbo.AccountStatus.AccountStatusID INNER JOIN dbo.AccountType ON dbo.Account.AccountTypeID = dbo.AccountType.AccountTypeID INNER JOIN dbo.CustomerAccount ON dbo.Account.AccountID = dbo.CustomerAccount.AccountID INNER JOIN dbo.Customer ON dbo.CustomerAccount.CustomerID = dbo.Customer.CustomerID INNER JOIN dbo.Transactions ON dbo.Account.AccountID = dbo.Transactions.AccountID AND dbo.Customer.CustomerID = dbo.Transactions.CustomerID INNER JOIN dbo.TransactionType ON dbo.Transactions.TransactionTypeID = dbo.TransactionType.TransactionTypeID

Triggers to protect Data CREATE TRIGGER PreventChange ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable Trigger "PreventChange" to drop or alter tables!' ROLLBACK CREATE TRIGGER DelTrans ON [dbo].[Transactions] INSTEAD OF DELETE AS BEGIN Raiserror('Deletes of Transactions not allowed', 13,1); ROLLBACK TRAN; END Create Trigger DelAccount on [dbo].[Account] INSTEAD OF DELETE AS BEGIN Raiserror('Deletes of Accounts not allowed', 13,1); ROLLBACK TRAN; END CREATE TRIGGER UpdTran ON [dbo].[Transactions] INSTEAD OF UPDATE AS BEGIN Raiserror('Updates of Transactions not allowed', 14,1); ROLLBACK TRAN; END

Stored Procedures with T-SQL CREATE PROCEDURE [dbo].[Deposit] -- Add the parameters for the stored procedure here @CustomerID int = NULL, @AccountID int = NULL, @TransactionAmount money = NULL AS BEGIN TRY BEGIN TRAN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Check for null values if @AccountID is null BEGIN raiserror('Account ID must be provided', 12, 1); END if @CustomerID is null BEGIN raiserror('Customer ID must be provided', 12, 2); END If @TransactionAmount Is Null BEGIN raiserror('Transaction Amount must be provided', 12, 3); END

--check valid Custid and accountid if @AccountID != (SELECT AccountID FROM Account WHERE AccountID = @AccountID) BEGIN raiserror('Account ID is invalid', 12, 4); END if @CustomerID != (SELECT CustomerID FROM Customer WHERE CustomerID = @CustomerID) BEGIN raiserror('Customer ID is invalid', 12, 5); END --Fetch current balance to calculate new balance DECLARE @CURRBAL money = (SELECT CurrentBalance FROM Account WHERE AccountID = @AccountID), @NewBalance money = NULL, @TransactionTypeID tinyint = 2, @AcctStatID tinyint = 1 SET @NewBalance = @CURRBAL + @TransactionAmount;

-- Insert statements for procedure here -- check account active if @AcctStatID != (SELECT AccountStatusID FROM Account WHERE AccountID = @AccountID) BEGIN Raiserror('Can not Deposit for Inactive Account', 12, 6); END BEGIN INSERT INTO [PIGGYBank].[dbo].[Transactions] ([AccountID] ,[TransactionTypeID] ,[CustomerID] ,[TransactionDate] ,[TransactionAmount] ,[NewBalance]) VALUES (@AccountID ,@TransactionTypeID ,@CustomerID ,CURRENT_TIMESTAMP ,@TransactionAmount ,@NewBalance); UPDATE [PIGGYBank].[dbo].[Account] SET [CurrentBalance] = @NewBalance WHERE AccountID = @AccountID; END COMMIT TRAN END TRY

BEGIN CATCH if @@TRANCOUNT > 0 ROLLBACK TRAN DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; --populate vars with error info SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- rethrow pass to front end or another proc RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH

�ݺ�ߣ

Presentation Paul

More Related Content

Presentation Paul