T-SQL Programming

Explore SQL Server programming techniques using Transact-SQL programming constructs.

course image

Course details

ceus icon CEUs 3.3

schedule icon Schedule Evenings

location icon Location Online, North Campus

cost icon Cost

$1,045

payment plan

Registration details

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

expand drawer icon collaspe drawer icon

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

expand drawer icon collaspe drawer icon
  • Structured Query Language (SQL) Level 1 or equivalent knowledge 
  • Structured Query Language (T-SQL) Level 2 or equivalent knowledge 

Syllabus

expand drawer icon collaspe drawer icon

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

Online — Evenings

Registration open expand drawer icon collaspe drawer icon
T-SQL Programming
hours icon

Total classroom hours

33 hours

schedule icon

Schedule

Online

Evenings, 6:30 PM - 9:30 PM, 11 T Th - 11/5/2020 - 12/15/2020, No class 11/26

cost icon

Total cost

$1,045

instructor icon

Instructor: Victor Leal

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