Goals
- Describe key features and components of SQL Server
- Describe T-SQL and logical predicates
- Write SELECT queries
- Write queries on multiple tables
- Write SELECT queries to sort and filter data
- Describe usage data types in SQL Server
- Write DML queries
- Write queries that use built-in functions
- Write sub-queries
- Create and implement views and functions of table values
- Use the set of operators to combine query results
- Use classification, shift and aggregation functions
- Transform data using pivot, unpivot, roolup and cube
- Create and execute stored procedures
- Program with T-SQL
- Set up management of error
- Implement transactions
Program
Basic SQL Server Architecture SQL Server
Editions and Versions
Getting Started with SQL Server Management Studio
Workshop – Getting started with SQL Server tools
Introduce T-SQL
Understanding sets
Understanding logical predicates
Understanding the logical order of operations in SELECT statements
Workshop – Understanding the basics of queries
Write simple SELECT statements
Eliminate duplicates with DISTINCT
Use column and table aliases
Write simple CASE expressions
Workshop – Writing basic queries
Understanding Joins
Queries with Inner Joins Queries with
Outer Joins
Queries with Cartesian Products and Self-joins
Workshop – Writing multi-table queries
Sort data
Filter data with predicates
Filter with TOP and OFFSET-FTECH options
Work with unknown values
Workshop – Sorting and filtering data
Introducing SQL Server Data Types
Working with Strings
Working with Dates and Times
Workshop – Management of date types and character strings
Insert data into tables
Edit and delete data
Generate column values
Workshop – Insertion, update and deletion of data
Duration
5 days
Price
£ 2599
Audience
Database administrators
Prerequisites
Knowledge of SQL language and DBMS concepts
Reference
BAS100723-F
Writing queries with built-in functions
Using conversion
functions
Using logical functions Using functions to work with NULL
Workshop – Using the built-in functions
Using aggregation functions
Using the GROUP BY clause
Filtering groups with HAVING
Lab – Using Aggregate Functions and Groupings
Write subqueries
Write correlated subqueries
Use the EXISTS predicate with subqueries
Workshop – Writing subqueries
Use views
Use inline table functions
Use derived tables
Use common table expressions
Workshop – Exercises on table expressions
Write queries with the UNION operator
Use EXCEPT and INTERSECT
Use APPLY
Workshop – Using game operators
Using the OVER clause
Explorer of window functions
Workshop – Writing queries with windowing functions
Writing queries with PIVOT and UNPIVOT
Working with Grouping Sets
Workshop – Using the Pivot and Grouping Set functions
Query data with stored procedures
Pass parameters to stored procedures
Create simple stored procedures
Work with Dynamic SQL
Workshop – Execution of stored procedures
T-SQL Programming Elements
Controlling Program Flow
Workshop – Writing simple T-SQL scripts
Implement error handling in T-SQL queries
Implement structured exception handling
Workshop – Implementation of error management
Transactions and Database Engines
Controlling Transactions
Workshop – Transaction implementation
Sessions
Contact us for more informations about session date