Microsoft SQL Server with Transact-SQL

Syllabus – Key points

Course Contents – DAY 1

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: DATABASE CONCEPTS

• What is a database
• Database management systems
• Databases, Schemas, Tables, rows and columns
• Indexes, primary keys, unique constraints and foreign keys
• Supported data types
• System Tables

Session 2: USING SQL SERVER MANAGEMENT STUDIO

• Getting started with SQL Server Management Studio (SSMS)
• Selecting a database
• Entering and executing SQL statements
• Creating, editing and executing SQL files

Session 3: WRITING SELECT QUERIES

• Writing simple SQL SELECT statement
• Using DISTINCT to remove duplicate data
• Filtering Data with Predicates
• Logical operators
• The ORDER BY clause
• Filtering with the TOP and OFFSET-FETCH
• Using Column aliases
• Arithmetic expressions
• Precedence of operators

Session 4: USING SET OPERATORS

• Writing Queries using the UNION operator
• Using the INTERSECT operator
• Using the EXCEPT operator
• Introducing the APPLY operator

Session 5: GROUPING AND AGGREGATING DATA

• Using aggregate functions
• Using the GROUP BY clause
• Using Rollup with GROUP BY
• Filtering grouped data with the HAVING clause

Course Contents – DAY 2

Session 6: QUERYING MULTIPLE TABLES

• Understanding JOINS
• CROSS Joins
• Using INNER Joins
• Using Table Aliases
• Using OUTER Joins: LEFT, RIGHT and FULL
• Using SELF JOINS

Session 7: NUMERIC AND CHARACTER FUNCTIONS

• Function types
• Testing functions
• Numeric functions
• Character functions

Session 8: DATE AND TIME FUNCTIONS

• Date and time column types
• Date and time formats
• Functions to return Today’s date and time
• Functions to extract components of a date (Day, Month, Year)
• Date time arithmetic
• Displaying Dates in specific Date formats

Course Contents – DAY 3

Session 9: CONVERSION AND MISCELLANEOUS FUNCTIONS

• Converting a string to a date
• Converting a string to a number
• Converting a number to a string
• Using Functions to work with NULL
• Using CASE expressions

Session 10: USING SUBQUERIES

• Using self contained subqueries
• Using correlated subqueries
• Using the EXISTS predicate with subqueries

Session 11: MANAGING DATA

• Inserting rows
• Updating rows
• Deleting rows
• Merging rows
• The truncate statement
• Transaction control

Course Contents – DAY 4

Session 12: USING TABLE EXPRESSIONS

• Creating and using Views
• Using Inline Table-Valued Functions
• Using Derived Tables
• Using Common Table Expressions

Session 13: ENHANCED GROUPING AND PIVOTING

• Working with GROUPING SETS
• Using ROLLUP, CUBE,GROUPING and GROUPING_ID
• Using PIVOT and UNPIVOT in Queries

Session 14: USING WINDOWING FUNCTIONS

• Using Window AGGREGATE functions in Queries
• Using Window RANK functions in Queries
• Using Window OFFSET functions in Queries

Course Contents – DAY 5

Session 15: IMPLEMENTING STORED PROCEDURES

• Overview of Stored Procedures
• Creating simple Stored Procedures
• Executing Stored Procedures
• Passing parameters to Stored Procedures

Session 16: PROGRAMMING WITH TRANSACT-SQL

• Using Programming Elements in Transact-SQL
• Controlling Program Flow with IF/ELSE
• Controlling Program Flow with WHILE
• Working with Dynamic SQL

Session 17: IMPLEMENTING ERROR HANDLING

• Structured error handling with TRY / CATCH block
• Unstructured error handling
• Generating error message using THROW and RAISEERROR
• Error handling in conjunction with Transaction Control

Session 18: IMPROVING QUERY PERFORMANCE

• Factors in Query Performance
• Displaying Query Performance Data