Curs Microsoft SQL – Advanced

13-15 iul 2022, 09:00-17:00, fizic – Bucuresti

Cursul Microsoft SQL – Advanced este un curs practic, care iti va dezvolta cunostintele si abilitatile SQL prin intermediul multor exercitii.

Vom utiliza Microsoft SQL Server 2019, instalat pe serverul totalquant, participantii urmand sa isi instaleze Microsoft SQL Server Management Studio.

Agenda curs

Introduction

• What is SQL? ANSI SQL Standard
• Microsoft SQL Server concepts
• Database structure
• System Databases
• DB Types: OLTP/OLAP/DW

DDL – Data Definition Language

• How to design a database
• How to CREATE/ALTER/DROP Objects (DB/SCHEMA/TABLE)
• Table Constraints: PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, FOREIGN KEY, CHECK

DCL – Data Control Language

• Authentication (LOGIN / USER)
• Authorization – GRANT, REVOKE, DENY
• Permissions: Server level, Database Level, Object Level
• How to list/display permissions
• How to add/modify/remove permissions
• Managing authorization using roles
• IMPERSONATE and EXECUTE AS USER

DML – Data Manipulation Language

• Recap
• Using MERGE Statement
• Using DELETE FROM / TRUNCATE to empty tables: differences / implications

CTE – Common Table Expressions

• What are table expressions?
• Using the WITH clause

Indexes

• What are indexes?
• Type of indexes: Clustered, nonclustered, unique, filtered
• Index design guidelines
• How to create/drop indexes
• Efficient Use of Indexes for optimal performance

DQL – Data Query Language

• Recap: SELECT statement
• The Relational Model- The Set Theory and Predicate Logic
• Two-Valued (True, False) and Three-Valued (True, False, Unknown) Predicate Logic
• Order of Operations in SELECT Queries
• Filtering data using Three-Valued Predicate Logic – handling unknown(s)
• Filtering Character Data using Pattern Matching. Using collation
• Filter Date/Time
• Using TOP [WITH TIES] / OFFSET.. FETCH
• Set Operators: UNION, UNION ALL, INTERSECT, EXCEPT
• Common Table Expressions – the WITH Clause
• Table Expressions vs temporary tables
• Pivoting and Unpivoting Data
• Using Window Functions – the OVER clause
• Ranking Functions: RANK, DENSE_RANK, NTILE, ROW_NUMBER
• Window Offset Functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE

Data Types & Type Conversions

• Data Types
• Using Implicit / Explicit Conversions
• CAST / CONVERT / PARSE and TRY_* alternatives

User-Defined Functions and Stored Procedures

• Functions that return scalars
• Functions that return tables
• Stored Procedures
• Implement error handling using TRY..CATCH
• Generate error messages using THROW, RAISERROR

TCL – Transaction Control Language

• What are transactions?
• Implicit/Explicit Transactions
• Using TCL Commands (BEGIN TRANSACTION/COMMIT/SAVEPOINT/ROLLBACK/SET TRANSACTION)
• SQL Server Transaction Log

Triggers

• What is a trigger?
• Types of triggers
• How to create a trigger
• Trigger limitations and other considerations

Locks

• What are locks
• Using NOLOCK hint in SELECT Statements. Dirty Reads.

Execution Plans

• Query Profiling
• How to generate and display execution plans

 

Ce include taxa de participare?

Accesul la curs (program 9-17).

Suportul de curs si diploma de participare. Cursul va fi predat in limba romana.

Fiecare participant va utiliza PC-ul/laptopul propriu pe care va avea instalat Microsoft SQL Server Management Studio.

Despre trainer

Money Back Guarantee

Înscrie-te la cursul
Microsoft SQL – Advanced

Pret promotional: 2250 RON