sql mater program
TRANSCRIPT
Introduction
• Introduction: This Project is a sample of some topics studied during the Setfocus SQL Master Program, many topics such as High Availability, Security and Database Tuning could not be implemented into this presentation .
• Project Goals: To demonstrate how to use TSQL, Store Procedures, XML and Triggers in SQL Server 2005 and to demonstrate the use of SSRS.
4/29/2009 2
Project List
• Library Database Forms• Piggy Bank Database Store Procedures,
Triggers and XML • Create and using XML with North Wind
Database
4/29/2009 3
Project : Library Database Forms
Library Database • Adult member information• Display available books• Search member by member number • Use CASE, list all member reserve the specific book• Create temporary table• Display member with past due loan from temporary table• Display members who pay highest fine• List of member who want to reserve the specific books
4/29/2009 4
Library Diagram
4/29/2009 5
Adult member information
4/29/2009 6
Display available books
4/29/2009 7
Search member by member number
4/29/2009 8
Use CASE, list all member reserve the specific book
4/29/2009 9
Create temporary table
4/29/2009 10
Display member with past due loan from temporary table
4/29/2009 11
Display member pay highest fine
4/29/2009 12
List of member who want to reserve the specific books
4/29/2009 13
Piggy Bank Database Store Procedures
• Shredding XML to populate database• Add New Customer• Add Overdraft Account• Deposit• Withdraw• Transfer• DDL Trigger• DML Trigger• Account History By Month & Year
4/29/2009 14
Piggy Bank Database ERD
4/29/2009 15
Shredding XML to populate database
4/29/2009 16
Add New Customer
4/29/2009 17
Add overdraft account
4/29/2009 18
Deposit
4/29/2009 19
Withdraw
4/29/2009 20
Transfer
4/29/2009 21
DDL Trigger drop_table & alter_table
4/29/2009 22
Instead of delete & update Trigger
4/29/2009 23
Account history by month and year
4/29/2009 24
Create and using XML with NorthWind Database
• Create XML Using Sub Query and Path Mode• Create XML Using Sub Query and Auto Mode• Insert XML• Value Method• Modify Method
4/29/2009 25
Create XML Using Sub Query and Path Mode
4/29/2009 26
Create XML Using Sub Query and Path Mode results
4/29/2009 27
Create XML Using Sub Query and Auto Mode
4/29/2009 28
Create XML Using Sub Query and Auto Mode result
4/29/2009 29
Insert XML
4/29/2009 30
Value Method
4/29/2009 31
Modify Method
4/29/2009 32
Library Database SSRS
4/29/2009 33
• Expired Member• Kids Club• Most Active Member• Total Fine By Member
Library Diagram
4/29/2009 34
Expired Member
4/29/2009 35
Kids Club
4/29/2009 36
Most Active Member
4/29/2009 37
Total Fine By Member
4/29/2009 38
Total Fine By Member Linked
4/29/2009 39