Introduction: Querying MS SQL Server 2012 Training Course

For Microsoft SQL Server 2014, this course provides delegates with the knowledge needed to perform basic Transact-SQL queries. It forms the foundation for all SQL Server-related disciplines; which includes Business Intelligence, Database Development and Administration.

Outline: Querying MS SQL Server 2012 Training Course

Johannesburg (Sandton), Cape Town, Durban, Port Elizabeth, Pretoria – South Africa

Key outcomes of the course include:
Perform SELECT queries
Multiple table queries
Filtering and sorting data
In SQL Server Utilize data types
Using Transact-SQL modify data
Perform built-in functions
Aggregate data
Sub query utilization
Use table expression and set operators
Perform ranking, aggregate functions and offset
Grouping sets and implement pivoting
Perform stored procedures
T-SQL programming
Skills in error handling
Transactions

Module 10: Using Subqueries
This module will introduce the use of subqueries in various parts of a SELECT statement. It will include the use of scalar and multi-result subqueries, and the use of the IN and EXISTS operators.
Lessons
Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries
Lab : Using Subqueries
Write queries which use self-contained subqueries
Write queries which use scalar and multi-result subqueries
Write queries which use correlated subqueries and EXISTS predicate
After completing this module, you will be able to:
Describe the uses of queries which are nested within other queries.
Write self-contained subqueries which return scalar or multi-valued results.
Write correlated subqueries which return scalar or multi-valued results.
Use the EXISTS predicate to efficiently check for the
existence of rows in a subquery.

Module 11: Using Table Expressions
This module introduces T-SQL expressions which return a valid relational table, typically for further use in the query. The module discusses views, derived tables, common table expressions and inline table-valued functions.
Lessons
Using Derived Tables
Using Common Table Expressions
Using Views
Using Inline Table-Valued Functions
Lab : Using Table Expressions
Write Queries Which Use Views
Write Queries Which Use Derived Tables
Write Queries Which Use Common Table Expressions
Write Queries Which Use Inline Table-Valued Functions
After completing this module, you will be able to:
Write queries which use derived tables.
Write queries which use common table expressions.
Create simple views and write queries against them.
Create simple inline table-valued functions and write queries against them.

Module 12: Using Set Operators
This module introduces Microsoft SharePoint Server as a platform for BI, and then focuses on building BI dashboards and scorecards with PerformancePoint Services.
Lessons
Writing Queries with the UNION Operator
Using EXCEPT and INTERSECT
Using APPLY
Lab : Using Set Operators
Write queries which use UNION set operators and UNION ALL multi-set operators
Write queries which use CROSS APPLY and OUTER APPLY operators
Write queries which use EXCEPT and INTERSECT operators
After completing this module, you will be able to:
Write queries which combine data using the UNION operator
Write queries which compare sets using the INTERSECT and EXCEPT operators
Write queries which manipulate rows in a table by using APPLY with the results of a derived table or function

Module 13: Using Window Ranking, Offset, and Aggregate Functions
This module introduces window functions including ranking, aggregate and offset functions. Much of this functionality is new to SQL Server 2012. It will cover the use of T-SQL functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE and LAST_VALUE to perform calculations against a set, or window, of rows.
Lessons
Creating Windows with OVER
Exploring Window Functions
Lab : Using Window Ranking, Offset and Aggregate Functions
Write queries which use ranking functions
Write queries which use offset functions
Write queries which use window aggregate functions
After completing this module, you will be able to:
Describe the benefits to using window functions.
Restrict window functions to rows defined in an OVER clause, including partitions and frames.
Write queries which use window functions to operate on a window of rows and return ranking, aggregation and offset comparison results.

Module 14: Pivoting and Grouping Sets
This module discusses techniques for pivoting data in T-SQL as well to introduce the fundamentals of the GROUPING SETS clause. It will also cover the use of GROUP BY ROLLUP and GROUP BY CUBE syntax in SQL Server.
Lessons
Writing Queries with PIVOT and UNPIVOT
Working with Grouping Sets
Lab : Pivoting and Grouping Sets
Write queries which use the PIVOT operator
Write queries which use the UNPIVOT operator
Write queries which use the GROUPING SETS subclause
After completing this module, you will be able to:
Write queries which pivot and unpivot result sets.
Write queries which specify multiple groupings with grouping sets.

Module 15: Executing Stored Procedures
This module introduces the use of existing stored procedures in a T-SQL querying environment. It discusses the use of EXECUTE, how to pass input and output parameters to a procedure, and how to invoke system stored procedures.
Lessons
Querying Data with Stored Procedures
Passing Parameters to Stored Procedures
Creating Simple Stored Procedures
Working with Dynamic SQL
Lab : Executing Stored Procedures
Use the EXECUTE statement to invoke stored procedures
Pass parameters to stored procedures
Execute system stored procedures
After completing this module, you will be able to:
Return results by executing stored procedures.
Pass parameters to procedures.
Create simple stored procedures which encapsulate a SELECT statement.
Construct and execute dynamic SQL with EXEC and sp_executesql.

Module 16: Programming with T-SQL
This module provides a basic introduction to T-SQL programming concepts and objects. It discusses batches, variables, control of flow elements such as loops and conditionals, how to create and execute dynamic SQL statements, and how to use synonyms.
Lessons
T-SQL Programming Elements
Controlling Program Flow
Lab : Programming with T-SQL
Declaring Variables and Delimiting Batches
Using Control-of-Flow Elements
Generating Dynamic SQL
Using Synonyms
After completing this module, you will be able to:
Describe the language elements of T-SQL used for simple programming tasks.
Describe batches and how they are handled by SQL Server.
Declare and assign variables and synonyms.
Use IF and WHILE blocks to control program flow.

Module 17: Implementing Error Handling
This module introduces the use of error handlers in T-SQL code. It will introduce the difference between compile errors and run-time errors, and will cover how errors affect batches. The module will also cover how to control error handling using TRY/CATCH blocks, the use of the ERROR class of functions, and the use of the new THROW statement.
Lessons
Using TRY / CATCH Blocks
Working with Error Information
Lab : Implementing Error Handling
Redirecting Errors with TRY / CATCH
Using THROW to Pass an Error Message Back to a Client
After completing this module, you will be able to:
Describe SQL Server’s behavior when errors occur in T-SQL code.
Implement structured exception handling in T-SQL.
Return information about errors from system objects.
Raise user-defined errors and pass system errors in T-SQL code.

Module 18: Implementing Transactions
This module introduces the concepts of transaction management in SQL Server. It will provide a high-level overview of transaction properties, cover the basics of marking transactions with BEGIN, COMMIT and ROLLBACK.
Lessons
Transactions and the Database Engine
Controlling Transactions
Isolation Levels
Lab : Implementing Transactions
Controlling transactions with BEGIN, COMMIT, and ROLLBACK
Adding error handling to a CATCH block
After completing this module, you will be able to:
Describe transactions and the differences between batches and transactions.
Describe batches and how they are handled by SQL Server.
Create and manage transactions with transaction control language statements.
Use SET XACT_ABORT to define SQL Server’s handling of transactions outside TRY / CATCH blocks.
Describe the effects of isolation levels on transactions
 

Course duration and fees: Querying MS SQL Server 2012
Training Course

Our two day training course is designed so that the knowledge acquired is applied practically, so that the business environment can be enhanced.

Please consult schedule for course costs or contact BOTI for a quote.

Recommended Prior Experience and Knowledge: Querying MS SQL Server 2012
Training Course

The course (Querying MS SQL Server 2012
Training Course) is designed for the following attendees:

Designed for professionals who are Database Developers or Administrators , and Business Intelligence professionals. This is suitable for SQL power users who not necessarily database-focused or intend taking the exam; i.e. business analysts, report writers, and client application developers.
 

For the Querying MS SQL Server 2012 Training Course it is advisable that you have the following experience/knowledge:
Relational databases working knowledge.
Microsoft Windows operating system basic knowledge.

Please click on link below for our related public course/s

Book Now or Obtain Instant Quote

We also offer customized courses across the country: Anytime, Anywhere. Click on the link to get get instant proposal or book you course:

Book This Course Or Obtain Quote – Now

Browse All Our Courses

Please Phone Us Now To Speak to One of Our Friendly Consultants

Tel:011-882-8853

OR

FOR CELL PHONES CLICK TO CALL

Please Fill in the Form – We Will Get Back to You Within 15 minutes

Please Email Us Now  – We Will Get Back to You Within 15 minutes

 [email protected]