Free Training & Career Tips... Subscribe to Get Weekly Career Tips
By Subscribing You are Agreeing to Terms and Conditions
Be A Guru of Databases with a The Microsoft Sql Server Courses (or Sql Course ; Sql Training Courses Johannesburg, Cape Town- South Africa) provides delegates with the technical knowledge to write basic Transact-SQL queries for Microsoft SQL Server 2014. This course is the basis for all SQL Server-related disciplines; in particular, Database Development, Database Administration and Business Intelligence.
This module introduces the SQL Server platform and major tools. It discusses editions, versions, tools used to query, documentation sources, and the logical structure of databases.
Johannesburg (Sandton), Cape Town, Durban, Port Elizabeth, Pretoria – South Africa
Key outcomes of the Microsoft Sql Server Courses (or Sql Course ; Sql Training Courses Johannesburg, Cape Town- South Africa) include:
SELECT queries formulation
Filtering and sorting data
Utilizing data types in SQL Server
Use sub-queries, table expressions, set operators, window ranking, offset and aggregate functions
Pivoting and grouping set implementation
Stored procedures execution
T-SQL programming
Error handling
Multiple tables querying
Transact-SQL to modify data
Utilizing built-in functions
Transactions implementation
Aggregate and Group data
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
Our two day Microsoft Sql Server Courses (or Sql Course ; Sql Training Courses Johannesburg, Cape Town- South Africa) 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.
The course (Querying MS SQL Server 2014 Training Course or Microsoft Sql Server Courses (or Sql Course ; Sql Training Courses Johannesburg, Cape Town- South Africa) ) is designed for the following attendees:
Targeted to Database Developers, Database Administrators, and Business Intelligence professionals. SQL power users who aren’t necessarily database will benefit and who are focused or plan on taking the exam; i.e. business analysts, report writers and client application developers.
For the Querying MS SQL Server 2014 Training Course it is advisable that you have the following experience/knowledge:
Microsoft Windows operating system basic knowledge
Working knowledge of relational databases
In addition to the related public courses, we offer the above course across the country: Anytime, Anywhere. Click on the link to get an instant proposal or book your course NOW:
Or alternatively click on the button below to view our full Public Course Calendar of close to 100 events:
Copyright text 2024 by Business Optimization Training Institute.