After completing the course, the participant will be able to:

• Create queries and work with objects contained in databases using the T-SQL language.
• Properly design a database, choose the right data types to match the requirements of the database structure.
• Create, modify and delete tables and data using T-SQL
• Design aggregate and group queries, use subqueries.

The training is intended for people who want to learn the technique of using relational databases. The skills acquired during the training can be useful in the daily work of a programmer, database administrator as well as an analyst creating various reports and statements.

Participants of the training are required to know the Windows environment. Familiarity with basic relational database topics will be an added advantage.

The training is conducted in the form of alternating mini lectures and practical exercises. It combines technical expertise with practical examples of its use in the work environment.

3 days, 24 training hours

Relational databases

• basic concepts (table, column, row, relation, data integrity, data normalization, primary key, foreign key).

Microsoft SQL Server tools for working with T-SQL language

SQL Server Management Studio

Selecting data - SELECT command

Filtering data - the WHERE clause

Formatting the result of a query

• Column aliases,
• sorting data,
• TOP clause,
• removing duplicates

Creating expressions in T-SQL language

• Data types used by SQL Server
• explicit and implicit conversion

Use of functions that operate on values:

• text
• dates and time periods.
• Grouping and aggregating data.

Connections

• principle of operation,
• types of joins,
• most common problems and errors faced when performing table joins.

Table operators

• PIVOT
• UNPIVOT
• APPLY

Combining sets

• UNION
• EXCEPT
• INTERSECT

Use of ordinary and correlated subqueries.

Common Table Expressions (CTE) - ordinary and recursive.

Data modification - commands:

• INSERT
• UPDATE
• DELETE
• MERGE

Transactions, data locking.

Use of indexes to optimize query performance

• Query processing,
• principles of query plan analysis,
• Design and establishment of indexes,
• types of indexes,
• evaluation of the use of indexes.

Creating tables

• defining columns,
• selection of data type,
• defining rules for data values (constraints)

Creating and using views.

• Microsoft SQL Server - Transact-SQL language. Advanced Level