sql square

Goals


- Write SELECT queries

- Write queries on multiple tables

- Sort and filter data

- Modify data with Transact-SQL

- Program with T-SQL

Program

Basic SQL Server Architecture SQL Server
Editions and Versions
Getting Started with SQL Server Management Studio

Practical workshops:

  • Describe the architecture and editions of SQL Server 2012
  • Working with SQL Server Management Studio

Introduce T-SQL
Understanding sets
Understanding logical predicates
Understanding the logical order of operations in SELECT statements

Practical workshops:

  • Describe the elements of T-SQL and their role in writing queries
  • Describe the use of sets in SQL Server
  • Describe the use of predicate logic in SQL Server
  • Describe the logical order of operations in SELECT statements

Write simple SELECT statements
Eliminate duplicates with DISTINCT
Use column and table aliases

Practical workshops:

  • Writing simple SELECT statements
  • Eliminating duplicates with DISTINCT
  • Using column and table aliases

Understanding Joins
Queries with Inner Joins Queries with
Outer Joins

Practical Workshops:

  • Describe how multiple tables can be queried in a SELECT statement using joins
  • Write queries that use inner and outer joins
  • Write queries that use self-joins and cross joins

Sort data
Filter data with a WHERE clause
Filter with the TOP and OFFSET-FTECH options
Work with unknown and missing values

Practical workshops:

  • Filter the data with predicates in the WHERE clause
  • Sort the data using ORDER BY
  • Filter the data in the SELECT clause with TOP
  • Filter the data with OFFSET and FETCH

Introducing SQL Server 2014 Data Types
Working with Strings
Working with Dates and Times

Practical Workshops:

  • Writing queries using character data types
  • Writing queries using date and time data types

Insert data
Edit and delete data

Practical workshops:

  • Insert new data into your tables
  • Update and delete existing records in your tables

Writing queries with built-in functions
Using conversion
functions
Using logical functions Using functions to work with NULL

Practical workshops:

  • Writing queries with built-in scalar functions
  • Using the conversion functions
  • Using the logical functions
  • Using the functions that work with NULL

Duration

5 days

Price

£ 1634

Audience

Database administrators and developers

Prerequisites

Knowledge of relational databases

Basic knowledge of using Windows

Reference

BAS1116-F

Using aggregation functions
Using the GROUP BY clause
Filtering groups with HAVING

Practical Workshops:

  • Write queries that summarize data using the built-in help in aggregate functions
  • Use the GROUP BY clause to organize rows into groups
  • Use the HAVING clause to filter groups based on a search condition

Writing subqueries
Writing correlated subqueries
Using the EXISTS predicate with subqueries

Practical Workshops:

  • Describe the uses of queries that are nested in other queries
  • Write stand-alone subqueries that return scalar or multi-valued results
  • Write correlated sub-queries that return scalar results or multiple values
  • Use the EXISTS predicate to efficiently check the existence of rows in a subquery

Use derived tables
Use common table expressions
Use views
Use inline table functions

Practical Workshops:

  • Write queries that use derived tables
  • Write queries that use common table expressions
  • Create simple views and write queries against them
  • Create simple inline table value functions and write queries against them

Write queries with the UNION operator
Use EXCEPT and INTERSECT
Use APPLY

Practical workshops:

  • Writing queries combining data using the UNION operator
  • Writing queries that compare sets using the INTERSECT and EXCEPT
  • Writing queries that manipulate the rows of a table using APPLY with the results of a table or a derivative function

Using the OVER clause
Explorer of window functions

Practical Workshops:

  • Describe the advantages of using window functions
  • Restrict window functions to lines defined in an OVER clause, including partitions and frames
  • Write queries that use window functions to run on a window rows and return the ranking, aggregation and offset comparison results

Writing queries with PIVOT and UNPIVOT
Working with Grouping Sets

Practical Workshops: Writing queries that rotate and UNPIVOT result sets – Writing queries that specify multiple groups with grouping sets

Query data with stored procedures
Pass parameters to stored procedures
Create simple stored procedures
Work with Dynamic SQL

Practical Workshops:

  • Executing stored procedures
  • Returning results by executing stored procedures
  • Passing parameters to procedures
  • Creating simple stored procedures that encapsulate a SELECT statement
  • Building and executing dynamic SQL with EXEC and sp_executesql

T-SQL Programming Elements
Controlling Program Flow

Practical Workshops:

  • 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 IFs and time blocks to control the progress of the program

Use TRY and CATCH blocks
Work with error information

Practical Workshops:

  • Describe SQL Server behavior when errors occur in T code SQL
  • Implement T-structured exception handling SQL
  • Raise user-defined errors and pass system errors to T code SQL

Transactions and Database Engine
Control Transactions
Isolation Levels

Practical Workshops:

  • 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 manipulation of transactions outside of try / catch blocks
  • Describe the effects of isolation levels on transactions

Query performance factors
Viewing query performance data

Practical Workshops:

  • Describe the components of high-performance queries
  • Display and interpret basic data of the performance of queries

Query System Catalog Views and Functions
Execute Systems Stored Procedures
Query Dynamic Management Objects

Hands-on Workshops:

  • Write queries that extract system metadata using system views and functions
  • Execute system stored procedures to return system information
  • Write queries that extract system metadata and status information using dynamic management system views and functions

Sessions

Contact us for more informations about session date