Jazmine Kane

SQL Server 2008 Portfolio

Email: jazekane@gmail.com

Linked-In: www.linkedin.com/pub/jazmine-kane/38/378/5b7

Table of Contents

Introduction

Jungle Books

Piggy Bank Project

Mini-Adventure Works Project

Introduction

This portfolio is designed to give examples of my SQL Server 2008 skill set. I completed a six month intensive, hands–on, project oriented program through the SetFocus SQL Master’s Program. I gained knowledge and valuable experience in T-SQL, SSIS, SSRS putting the SQL Server skill set to use in a simulated work environment. I also learned administrator responsibilities such as security, mirroring, replication, snapshots and isolation levels.

 

Jungle Books

Jungle Books was an T-SQL assignment where we were given a series of statements and wrote SQL queries. Some were user inputs followed by a button click.

T-SQL ExampleIn this example the user will input five in the txtMinimumToal for field. It displays all the records with a total greater than or equal to 5. The total represents the sum of all quantity order amounts per ISBN.

T-SQL Example

This example gives a report on credit cards that have expired and credit cards that will expire soon. The output displays the expiry date of the card for records with an expiry date prior to 30 days after the current date in descending order.

T-SQL Example

This example displays the largest number of items sold per order. The output displays the total number of items they ordered by the largest quantity first.

Piggy Bank Project The goal of this project was to implement a functional bank database.

The interaction with this database is done with user stored procedures and views.

The customer procedures were built to create and update customer accounts.

The account stored procedures were built to create checking and savings accounts, close or re-activate accounts, deposit, transfer, or withdrawal-purchase funds, and update interest rates.

ATM vies were created so the customer could get their account balance, transactions, account history and customer summary.

DDL triggers were implemented to guard against unwanted table and view changes.

Entity Relationship Diagram

Create Account Transaction

This is an example of a stored procedure to create a checking or savings account. When opening an account, the initial deposit requirement must be greater than $20.

Create Account Transaction

The account table must be updated reflecting that a new account was opened.

Create Account Transaction

The transactions table must also be updated to reflect the deposit that was made into the account. If any errors occur it will be caught by the catch block.

Deposit Transaction

This is an example of a stored procedure to deposit funds in a specific account. The deposit amount must be greater than $0.

Deposit Transaction

The deposit transaction is inserted into the transactions table and the account balance is updated in the account table. If any errors occur it will be caught by the catch block.

Account History ViewThis view allows a customer to view their account history .

Get Balance View

This view allows a customer to get their current account balance.

DoNotDroporAlter Trigger

This is an example of a trigger which does not allow the deletions or alterations of tables or views.

Mini-Adventure Works

The Mini-Adventure Works project is a load process.

The SSIS packages were built to import data from .CSV flat files.

An SSRS report was designed and deployed to the report server.

ImportProducts.dtsxThe Import Products package imports products from a comma delimited file. The packages either inserts or updates rows in the products table. .

ImportOrders.dtsxThe Import Order package imports order details and header information from four comma delimited files to the proper table. The processed files are moved to another folder when the package is ran. The results are emailed to a specified recipient.

PO Header

Data Flow

PO DetailData Flow

rptSalesMatrixByYears.rdl

This report shows the vendor sales per year. The report includes totals per year and per vendor and the grand total. The report can be filtered by shipping method.