Base de données

Plan de cours formation T-SQL

I. Foundations of Querying

I.1 Logical Query processing phases
I.2 Aliasing

II. Getting Started with the SELECT Statement

II.1. FROM Clause
II.2. SELECT Clause
II.3. Datatypes
II.4. BUILT-IN Functions
II.4.1 Datetime Functions
II.4.2 Characters Functions
II.4.1 Case Expression
II.4.1 Numerical Functions

III. Filtering and Sorting Data

III.1 Filtering data : predicates
III.2 Sorting data
III.3 Filtering with TOP and OFFSET-FETCH

IV. Combining Sets

IV.1 Joins
IV.1.1 Cross Joins
IV.1.2 Inner Joins
IV.1.3 Outer Joins
IV.1.4 Multi-Joins Queries
IV.2 Using Subqueries, Table Expressions, and the APPLY Operator
IV.2.1 Subquery
IV.2.2 Table Expressions (CTE)
IV.2.3 APPLY
IV.3 Using Set Operators
IV.3.1 UNION and UNION ALL
IV.3.2 INTERSECT
IV.3.3 EXPECT

V. Grouping

V.1 Grouping
V.1.1 Single Grouping Set
V.1.2 Multiple Grouping Sets

VI. Creating Tables and Enforcing Data Integrity

VI.1 Creating and Altering Tables
VI.1.1 Creating tables
VI.1.2 Altering Tables
VI.1.3 Choosing Indexes

VI.2 Enforcing Data Integrity

VI.2.1 Using Constraints
VI.2.2 Primary Key Constraints
VI.2.3 Unique Constraints
VI.2.4 Foreign Key Constraints
VI.2.5 Check Constraints
VI.2.6 Default Constraints

VII. Designing and Creating Views, Inline Functions, and Synonyms

VII.1 Designing and Implementing Views and Inline Functions
VII.1.1 Views
VII.1.2 Inline Functions
VII.2 Using Synonyms

VIII. Inserting, Updating, and Deleting Data

VIII.1 Inserting Data
VIII.1.1 INSERT VALUES
VIII.1.2 INSERT SELECT
VIII.1.3 INSERT EXEC
VIII.1.4 SELECT INTO

VIII.2 Updating Data
VIII.2.1 UPDATE Statement
VIII.2.2 UPDATE Based on Join
VIII.2.3 Nondeterministic UPDATE
VIII.2.4 UPDATE and Table Expressions
VIII.2.5 UPDATE Based on a Variable
VIII.2.6 UPDATE All-at-Once
VIII.3 Deleting Data
VIII.3.1 DELETE Statement
VIII.3.2 TRUNCATE Statement
VIII.3.3 DELETE Based on a Join
VIII.3.4 DELETE Using Table Expressions

VIIII. Other Data Modification Aspects

VIIII.1 IDENTITY
VIIII.2 SEQUENCES
VIIII.3 MERGING DATA
VIIII.4 OUPUT OPTION
VIIII.4.1 INSERT with OUTPUT
VIIII.4.2 DELETE with OUTPUT
VIIII.4.3 UPDATE with OUTPUT
VIIII.4.4 MERGE with OUTPUT

X. Implementing Transactions, Error handling, and Dynamic SQL

X.1 Managing Transactions and Concurrency
X.1.1 Understanding Transactions
X.1.2 Types of Transactions
X.1.3 Basic Locking
X.1.4 Transaction Isolation Levels
X.2 Implementing Error Handling
X.2.1 Detecting and Raising Errors
X.2.2 Handling Errors After Detection
X.3 Using Dynamic SQL
X.3.1 Dynamic SQL Overview
X.3.2 SQL Injection
X.3.3 Using sp_executesql

XI. Designing and Implementing T-SQL Routines

XI.1 Designing and Implementing Stored Procedures
XI.1.1 Understanding Stored Procedures
XI.1.2 Executing Stored Procedures
XI.1.3 Branching Logic
XI.1.4 Developing Stored Procedures
XI.2 Implementing User-Defined Functions
XI.2.1 Understanding User-Defined Functions
XI.2.2 Scalar UDFs
XI.2.3 Table-Valued UDFs
XI.2.4 Limitations on UDFs
XI.2.5 UDF Options
XI.2.6 UDF Performance Considerations

XII. Understanding Cursors, Sets, and Temporary Tables

XII.1 Evaluating the Use of Cursor/Iterative Solutions vs. Set-Based Solutions
XII.1.1 The Meaning of “Set-Based”
XII.1.2 Iterations for Operations That Must Be Done Per Row
XII.1.3 Cursor vs. Set-Based Solutions for Data Manipulation Tasks
XII.2 Using Temporary Tables vs. Table Variables
XII.2.1 Scope
XII.2.1 DDL and Indexes
XII.2.3 Transactions
XII.2.4 Statistics