T-SQL Programming
Explore SQL Server programming techniques using Transact-SQL programming constructs.
T-SQL Programming
Explore SQL Server programming techniques using Transact-SQL programming constructs.
What you’ll be able to do — competencies
- Automate data insertion and modification using scripts and basic programming constructs
- Convert SQL Scripts into procedures and functions in order to make the functionality available to other database users
- Manage transactions in order to increase concurrent usage of the database
- Create SQL programming functions to provide flexible data integrity and error-handling rules
- Create triggers that enable the SQL database to automatically take action based on a particular event occurring
- Manage database query results row-by-row using SQL cursors
Course description
This course covers Microsoft’s SQL Server database programming techniques using Transact-SQL (T-SQL) programming constructs, with an emphasis on stored procedures and scripts. The curriculum also involves writing SQL scripts of increasing complexity. Additional course topics include creating and implementing functions using T-SQL and SQL CLR; views, triggers and transactions; how to protect your data with constraints; and structured error handling.
Prerequisites
- Structured Query Language (SQL) Level 1 or equivalent knowledge
- Structured Query Language (T-SQL) Level 2 or equivalent knowledge
Syllabus
Lesson 1: Automate data insertion and modification using scripts and basic programming constructs
- Create scripts to perform insert and update tasks within the database
- Add flow-control statements and logic to perform branched processing based on conditions
Lesson 1 Assignments
- Lab #1
Lesson 2: Automate data insertion and modification using scripts and basic programming constructs
- Code loops that make the script execute the workflow repeatedly over a series of data
- Build in error handling routines using Try…Catch clauses
Lesson 2 Assignments
- Lab #2
- Skill check #1
Lesson 3: Convert SQL Scripts into procedures and functions to make the functionality available to other database users
Transform scripts into packaged procedures and functions that can be stored in the database and executed as required
- Use input and output parameters to pass information “on-the-fly” to/from the procedure/function
Lesson 3 Assignments
- Labs #3 and #4
Lesson 4: Convert SQL Scripts into procedures and functions to make the functionality available to other database users
Use the built-in debugging tool to help find errors in newly written procedures and functions.
- Transform scripts into packaged procedures that can be stored in the database and executed as required
Lesson 4 Assignments
- Labs #5–#7
Lesson 5: Convert SQL Scripts into procedures and functions to make the functionality available to other database users
Transform scripts into packaged functions that can be stored in the database and executed as required
- Integrate assemblies from other programming languages into SQL Server
Lesson 5 Assignments
- Lab #8
Lesson 6: Convert SQL Scripts into procedures and functions to make the functionality available to other database users
Lesson 6 Assignments
- Lab #9
- Skill check #2
Lesson 7: Manage transactions in order to increase concurrent usage of the database
- Create transaction-based workflows to ensure all required work is executed completely as a unit and does not produce any errors
- Resolve issues related to lock contention and deadlock
- Apply best practices for T-SQL programming to use transactions effectively and minimize potential concurrency pitfalls
Lesson 7 Assignments
- Lab #10
- Skill check #3
Lesson 8: Create SQL programming functions to provide flexible data integrity and error-handling rules
- Code T-SQL scripts to provide advanced tests for the correctness of information entered into the database by users
Create triggers that enable the SQL database to automatically take action based on a particular event occurring
- Transform a script into a trigger which will execute automatically when the user makes a modification (i.e., insert, update or delete) to the database
Lesson 8 Assignments
- Skill check #4
Lesson 9: Create triggers that enable the SQL database to automatically take action based on a particular event occurring
- Build an audit trail of database inserts, updates and deletes using triggers to keep track of changes and previous values
Lesson 9 Assignments
- Lab #11
- Skill check #5
Lesson 10: Manage database query results row-by-row using SQL Cursors
- Declare cursors to interact with the data a record at a time
- Interact with the cursor using a loop to fetch data
Lesson 10 Assignments
- Lab #12
Lesson 11: Manage database query results row-by-row using SQL Cursors
- Navigate through the cursor results backward and forward in a scrollable fashion
- Detect underlying data changes using a cursor
Lesson 11 Assignments
- Skill check #6
Next available start dates
Main Campus — Evenings
T-SQL Programming
Total classroom hours
33 hours
Schedule
Main Campus
Evenings, 6:00 PM - 9:00 PM, 11 M W - 5/8/2023 - 6/14/2023, No class 5/29
Class Location:
3000 Landerholm Cir. SE Bellevue, WA 98007
Building A, Room 134
Campus Map
Quarter: Spring
Total cost
$1,045
Instructor: Victor Leal
Victor Leal is a Data Engineering manager at Amazon Web Services (AWS). He has an exceptional record of success in developing, enhancing, deploying and managing database designs.
Interested in custom learning solutions for your team?
Tombolo Institute Corporate Solutions offers tailor-made professional development programs to meet your organization’s goals.
Get started