Microsoft SQL Server with Transact-SQL

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

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

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

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

Session 15: CREATE AND MANAGE TABLES

• Creating tables
• Datatypes and Constraints
• Altering and Dropping tables
• Creating temporary tables
• Copying tables
• Creating and using Sequences and Synonyms
• Obtaining metadata

Session 16: INDEXES

• Index types
• Creating indexes
• Performance considerations
• Filtered indexes
• Indexes on View
• Indexes with INCLUDE
• Dropping indexes
• Obtaining metadata

Session 17: METADATA

• INFORMATION_SCHEMA views
• Sys views
• Comparison between INFORMATION_SCHEMA and sys views
• Obtaining metadata in SSMS

Session 18: IMPROVING QUERY PERFORMANCE

• Factors in Query Performance
• Displaying Query Performance Data