Download - Marcus Matthews
SQL Portfolio
Marcus [email protected]://www.linkedin.com/in/marcuswmatthews
Table of Contents
• Introduction 2• Jungle Books 3• Library 7• Piggy Bank 11• Block Flix 19
Introduction
• This portfolio contains examples of my development skills in MS SQL Server. It is a result of my work during an 8-week hands-on experience with The SetFocus Master's Program.
• SetFocus utilizes Microsoft Official Curriculum in conjunction with its own materials to produce some of the following coursework:
– RDBMS and XML– Querying using Transact SQL– Implementing and maintaining a MS SQL Server 2008 Database– Designing an MS SQL Server 2008 Infrastructure– Designing security for MS SQL Server 2008– Designing High Availability Database Solutions using MS SQL Server 2008– Troubleshooting and Optimizing Database Solutions using MS SQL Server 2008– SQL Server Integration Services – SQL Server reporting Services
• In addition to the coursework and programming labs, the program included challenging real-world projects where I have applied the skills experienced in class.
Jungle Books
• Jungle Books is company which uses a database to store data for books, authors, customers that purchase these items and order records.
• This database diagram was used to create the database and show the relationship between entities within it.
• The application developer that we are working with fictitiously will use this design for a client application using .NET.
Jungle BooksThe select statement for the Large Orders report is used to return orders from the Jungle Books' database that contain the highest number of items sold per order, OrderID, CustomerID, Customer Name and the items within their order. The result set is displayed in descending order listing the order with the largest quantity first.
SELECT [o].[OrderID] AS 'Order ID',[o].[CustomerID] AS 'Cust ID',c.Name ,(SELECT SUM([oi].[QuantityOrdered])) AS '# of items'FROM [Orders] o JOIN [OrderItems] oi ON oi.OrderID = o.OrderIDJOIN Customers AS cON c.CustomerID = o.CustomerIDGROUP BY o.OrderID, o.CustomerID, c.Name Order BY (SELECT SUM([oi].[QuantityOrdered])) desc;
Jungle Books
The report named Expired Cards is a select statement used to generate a list of customers whose credit cards have expired and those that will expire within 30 days.
SELECT CustomerID AS 'ID', [Name], expiryDate AS 'Expires'
FROM customers WHERE expirydate < (getDate() +30)
Library
• The Library Database is designed to maintain records of the day-to-day lending operations such: items available or on loan, overdue books, fees, member information, and reservations.
This database has been used for number of projects:
• Create queries against the Library Database to return results using UNION and several join types.
• Search for book titles, by name, ISBN, and author. • Create new member accounts for juveniles and adults. Set up a
reservation for an item that is currently unavailable to loan once it has been returned.
Library
ER Diagram
Library
SELECT lh.member_no, m.lastname, m.firstname, SUM(lh.fine_paid) AS "Fines Paid"
from loanhist lhINNER JOIN member m ON lh.member_no = m.member_noGROUP BY lh.member_no, m.lastname, m.firstnameHAVING SUM(lh.fine_paid) IS NOT NULLORDER BY SUM(lh.fine_paid) DESC, lh.member_no
LibrarySELECT r.isbn, t.title, m.member_no, m.lastname, 'adult' AS [member_type]FROM member m INNER JOIN reservation r ON r.member_no = m.member_noINNER JOIN item i ON i.isbn = r.isbnINNER JOIN title t ON t.title_no = i.title_noINNER JOIN adult a ON a.member_no = m.member_noWHERE r.isbn = 288UNIONSELECT r.isbn, t.title, m.member_no, m.lastname, 'juvenile' AS [member_type]FROM member m INNER JOIN reservation r ON r.member_no = m.member_noINNER JOIN item i ON i.isbn = r.isbnINNER JOIN title t ON t.title_no = i.title_noINNER JOIN juvenile j ON j.member_no = m.member_noWHERE r.isbn = 288GROUP BY r.isbn, t.title, m.member_no, m.lastnameORDER BY [member_type]
Piggy Bank
The Piggy Bank Database is used to simulate the daily operations of a financial institution, providing its user access to their personal information and manage accounts.
This database has been used for a number of projects:• Using stored procedures this database is capable of
accounting for typical banking transactions, such as deposits, funds withdrawal, account transfers or if you are a new customer, create a new account.
• Update account information capabilities to change an address, add another account or member to an existing account transaction history.
Piggy Bank
ER Diagram
Piggy BankCreate new customer Stored procedure
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
/*written by: Marcus MatthewsDate: 05/10/2010Errors'Last name cannot be null', 11, 1'First name cannot be null', 11, 2'Street cannot be null', 11, 3'City cannot be null', 11, 4'State cannot be null', 11, 5'Zip code cannot be null', 11, 6'Home Phone number cannot be null', 11, 7*/
CREATE PROCEDURE [dbo].[usp_CreateNewCustomer]
@CustomerLastName nvarchar(20)=null,@CustomerFirstName nvarchar(20)=null,@CustomerMiddleName nvarchar(20)=null,@Street nvarchar(50) =null,@City nvarchar(20) =null, @State nchar(2) =null,@ZipCode nvarchar(10) =null,@Email nvarchar(50) =null,@HomePhone nvarchar(15) =null,@WorkPhone nvarchar(15) =null,@CellPhone nvarchar(15) =nullASBEGIN TRY
Piggy Bank Create new customer Stored procedure (cont.)
-- Test parameters. All required fields must have a value, only test those that do not allow nulls
If @CustomerLastName is nullBEGIN
RAISERROR ('Last name cannot be null', 11, 1)END
If @CustomerFirstName is nullBEGIN
RAISERROR ('First name cannot be null', 11, 2)END
If @Street is nullBEGIN
RAISERROR ('Street cannot be null', 11, 3)
ENDIf @City is null
BEGINRAISERROR ('City cannot be
null', 11, 4)END
If @State is nullBEGIN
RAISERROR ('State cannot be null', 11, 5)END
If @ZipCode is nullBEGIN
RAISERROR ('Zip code cannot be null', 11, 6)END
Piggy Bank Create new customer Stored procedure (cont.)
If @HomePhone is nullBEGIN
RAISERROR ('Home Phone number cannot be null', 11, 7)END
--Create customer and insert data BEGIN
INSERT INTO dbo.Customer (CustomerFirstName,CustomerLastName,CustomerMiddleName,Street,City,[State],ZipCode,Email,
HomePhone,WorkPhone, CellPhone
)VALUES (
@CustomerFirstName,@CustomerLastName,@CustomerMiddleName,@Street,@City,@State,@ZipCode,@Email, @HomePhone,@WorkPhone, @CellPhone )
Piggy Bank Create new customer Stored procedure (cont.)
ENDRETURNEND TRYBEGIN CATCH -- IF @@TRANCOUNT > 0 -- ROLLBACK TRAN DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage =
ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage,
@ErrorSeverity, @ErrorState);END CATCHGO
Piggy Bank Account Balance Stored procedure
GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*This stored procedure is used to return the
current account balance and last 5 transactions. Error messages for omitted data in required fields.
written by: Marcus MatthewsDate: 05/15/2010Errors'Customer ID cannot be null', 11, 1'Account ID cannot be null', 11, 2'Account numbers provided do not match', 11,
3*/
CREATE PROCEDURE [dbo].[GetBalance]
@CustomerID int =null,@AccountID int =null
ASBEGIN TRYIf @CustomerID is null
BEGINRAISERROR ('Customer ID
cannot be null', 11, 1)END
If @AccountID is nullBEGIN
RAISERROR ('Account ID cannot be null', 11, 2)END
Piggy BankAccount Balance Stored procedure (cont.)
-- Make sure accounts matchIF EXISTS(SELECT a.AccountID FROM Account a
JOIN CustomerAccount caON ca.AccountID = a.AccountIDJOIN Customer cON c.CustomerID = ca.CustomerIDWHERE a.AccountID = @AccountID)
-- Make sure accounts matchIF EXISTS(SELECT a.AccountID FROM Account a
JOIN CustomerAccount caON ca.AccountID = a.AccountIDJOIN Customer cON c.CustomerID = ca.CustomerIDWHERE a.AccountID = @AccountID)
-- shows current balance with last 5 transactions.
BEGINSELECT DISTINCT TOP(5)
c.CustomerID, a.CurrentBalance,
t.transactionID, t.transactionDate, t.TransactionAmount
FROM Account aJOIN [Transaction] tON t.AccountID = a.AccountIDJOIN Customer cON c.customerID=
t.CustomerIDWHERE a.accountID =
@AccountID AND c.CustomerID =
@CustomerIDEND
Piggy BankAccount Balance Stored procedure (cont.)
ELSE BEGIN
RAISERROR ('Account numbers provided do not match', 11, 3)END
RETURNEND TRYBEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage =
ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage,
@ErrorSeverity, @ErrorState);END CATCHGO
BLOCKFLIX PROJECT
Brian HenryMarcus MatthewsRobert Parkin
DATABASE DESIGN
GenreGenreID
Genre
DateEntered
DateModified
LostDamagedLostDamagedID
MemberID
MovieID
StoreID
DateEntered
DateModified
MemberMemberID
MemberTypeID
FirstName
MiddleInitial
LastName
Address
City
State
ZipCode
Phone
MemberStatusID
DateEntered
DateModified
MemberStatusMemberStatusID
MemberStatusDesc
MemberTypeMemberTypeID
MemberTypeDesc
MaxCheckout
Cost
TimePeriod
DateEntered
DateModified
MoviesTalentMovieTalentID
MovieID
TalentTypeID
TalentNameID
DateEntered
DateModified
RatingRatingID
Rating
DateEntered
DateModified
StoresStoreID
StoreTypeID
Region
DateEntered
DateModified
StoreTypeStoreTypeID
StoreTypeDesc
DateEntered
DateModified
TalentTypeTalentTypeID
TalentTypeDesc
DateEntered
DateModified
MemberQueDetailMemberQueDetailID
MemberQueID
SortOrder
MovieID
MemberQueHeaderMemberQueID
MemberID
QueDate
TalentNameTalentNameID
TalentTypeID
TalentFirstName
TalentLastName
DateEntered
DateModified
RentalTransactionHeaderTransHeaderID
TransTypeID
StoreID
MemberID
TransDate
RentalTransactionDetailTransDetailID
TransHeaderID
InventoryID
FineAmt
MovieInventoryInventoryID
MovieID
StoreID
Available
DateEntered
DateModified
MovieMasterMovieID
MovieName
Year
Genre
Rating
DateEntered
DateModified
MemberTransactionsTransID
TransTypeID
MemberID
TransactionDate
TransactionAmount
NewBalance
QtyCheckedOut
TransactionTypeTransTypeID
TransTypeDesc
DateEntered
DateModified
XML DATA
There were a couple issues we had to overcome to process the XML.
One of the challenges of processing the XML was formatting it properly and creating a schema.
Another problem we had to overcome was entering the quantity of the movie into our Inventory Table. This was done by using a OLE DB Command Transformation and calling a proc (LoadMovieInventory) to insert records in a While Loop until it looped the appropriate amount of times.
Original XML
Modified XML
LOADING GENRE DATA (SSIS)
Raw Data
Data in the Genre Table
LOAD INVENTORY/TALENT SSISALSO USED SSIS TO LOAD ALL BASE TABLES
Checking Out an Online Movie
Member David Smith checks out movie Dark Knight(MemberID 2, MovieID 5, InventoryID 50)Rental Transaction Tables:
Checking Out a Movie (Cont.)
Current Member Queue (GetMemberQueView)
When Member returns Dark Night they will receive Saving Private Ryan.
Returning a Movie
David Smith (2) returns (2) the movie Dark Knight (50)Rental Transactions
Showing return of Dark Knight (50) and Shipment (1) of Saving Private Ryan (20)
Returning a Movie (Cont.)
Member Transaction
Showing return of Dark Knight and Shipment of Saving Private Ryan
Returning a Movie (Cont.)
Movie Inventory for Dark Knight
Showing Available (1)
Returning a Movie (Cont.)
Member Queue
Remains the same… when Saving Private Ryan is returned Gold Finger will be shipped.
Returning a Movie Continued
When Saving Private Ryan was returned, it was deleted from the queue.
Movies Checked Out By Store
exec uspGetCheckedOutByStore 1
Available Inventory By Store
exec uspGetInvByStore 1
Update Member StatususpMembershipUpgrade
Pre-Update
Post Update
Update Movie Queue uspUpdateMemberQue
Pre-UpdateJudith HaywardOnly has Goldfinger
Post-UpdateJudith Haywardadded Frankenstein Will not allow
movie to be in the queue more than once
Monthly Member Debit/Credit
uspMonthlyMemberDebituspMonthlyMemberCredituspGetMemberTransactions (Lookup)
TransTypeID 4 is DebitTransTypeID 3 is Credit
Lost/Damage FunctionalityuspCreateLostDamagedTransaction
Pre-Damage – Movie is checked outMovie Rental Transaction
Damage Report – Movie is Lost/Damaged
Lost/Damage Functionality (Cont.)
Member Transactions showing fine of $4.99
LostDamaged Table
Inactivate Member if Balance Over Due
Member’s account is over due
Now the Member’s account is inactive.
uspInactivateMemberIfOverdue
Membership Report
Most Rented Movies
Lost or Damaged Movies
Most Active Customers
Most Rented Movies
Reports on the Report Server
Database Backup (Cont.)
Database Backup
Online Streaming Proposal
• Security is a high priority due to access over the internet (for both ourselves and our customers)
• Create Database to track usage• Master List of Movies as only one movie is needed
for download• Possibly offer unlimited usage per month for mailing
members and one time usage fees for non-members• Possibly offer an Online Only Member Status with its
own fees• Genre/Talent preference list for customer
Phase II
• Due to time constraints and team size only the Online portion was completed.
• We have already designed and created the databases for the In-Store Retail Sales and Kiosks.
• Stored Procedures, Report Packages, SSIS Packages, Test Data are currently in the development phase.