Description
This program has as its main objective to give students a good understanding of the Transact-SQL language that is used in all disciplines related to SQL Server; namely, database administration, database development, and business intelligence.
Addressed to
- Database administrators
- Database developers
- BI professionals.
Duration
35 hours
Modalities
-
- Face-to-face
-
- Virtual live through digital platforms
- on site
Related Courses
-
- 20764-C: Administering a SQL Database Infrastructure
- 20762-C: Developing SQL Databases
It includes
-
- 35 hours of training
-
- Trained instructors
-
- Participant manual in digital format
-
- Proof of participation
Temary
Lesson 1: Introduction to Microsoft SQL Server
-
- The basic architecture of SQL Server
-
- SQL Server editions and versions
- Introduction to SQL Server Management Studio
Lab : Working with SQL Server tools
-
- Work with SQL Server Management Studio
-
- Creating and organizing T-SQL scripts
- Use books online
Lesson 2: Introduction to T-SQL Queries
-
- Presentation of T-SQL
-
- Set comprehension
-
- Understanding Predicate Logic
- Understanding the logical order of operations in SELECT statements
Lab: Introduction to T-SQL Queries
-
- Execution of basic SELECT statements
-
- Running queries that filter data using predicates
- Running queries that order data using ORDER BY
Lesson 3: Writing SELECT Queries
-
- Write simple SELECT statements
-
- Removing duplicates with DISTINCT
-
- Use column and table aliases
- Write simple CASE expressions
Lab: Writing Basic SELECT Statements
-
- Write simple SELECT statements
-
- Eliminate duplicates with DISTINCT
-
- Use column and table aliases
- Use a simple CASE expression
Lesson 4: Querying Multiple Tables
-
- Understanding joins
-
- Query with inner joins
-
- Query with outer joins
- Consultation with Cross Joins and Self Joins
Lab : Query Multiple Tables
-
- Writing Queries That Use Inner Joins
-
- Writing queries that use inner joins of multiple tables
-
- Writing queries that use self-joins
-
- Writing queries that use outer joins
- Writing queries that use cross joins
Lesson 5: Data Classification and Filtering
-
- data classification
-
- Filter data with predicates
-
- Data filtering with TOP and OFFSET-FETCH
- Work with unknown values
Lab: Data Classification and Filtering
-
- Writing queries that filter data using a WHERE clause
-
- Writing queries that order data using an ORDER BY clause
-
- Writing queries that filter data with the TOP option
- Writing queries that filter data using the OFFSET-FETCH clause
Lesson 6: Working with SQL Server Data Types
-
- Introduction to SQL Server data types
-
- Work with character data
- Work with date and time data
Lab : Working with SQL Server Data Types
-
- Write queries that return date and time data
-
- Write queries that use date and time functions
-
- Write queries that return character data
- Write queries that return character functions
Lesson 7: Using DML to modify data
-
- Add data to tables
-
- Modify and delete data
- Generating automatic column values
Lab : Using DML to Modify Data
-
- Insert records with DML
- Updating and deleting records using DML
Lesson 8: Using built-in functions
-
- Write queries with built-in functions
-
- Use conversion functions
-
- Use logic functions
- Using functions to work with NULL
Lab : Using Built-in Features
-
- Write queries that use conversion functions
-
- Write queries that use logical functions
- Write queries that test nullability
Lesson 9: Data Grouping and Aggregation
-
- Use added functions
-
- Using the GROUP BY clause
- Filter groups with HAVING
Lab: Data Grouping and Aggregation
-
- Writing queries that use the GROUP BY clause
-
- Write queries that use aggregate functions
-
- Writing queries that use different aggregate functions
- Writing queries that filter groups with the HAVING clause
Lesson 10: Using Subqueries
-
- Write stand-alone subqueries
-
- Write correlated subqueries
- Using the EXISTS predicate with subqueries
Lab : Using Subqueries
-
- Write queries that use separate subqueries
-
- Writing queries that use scalar and multiple-result subqueries
- Writing queries that use correlated subqueries and an EXISTS clause
Lesson 11: Using Table Expressions
-
- use views
-
- Using table-valued functions online
-
- Use derived tables
- Use common table expressions
Lab : Using Table Expressions
-
- Write queries that use views
-
- Write queries that use derived tables
-
- Write queries that use common table expressions (CTEs)
- Writing queries that use inline table-valued expressions (TVFs)
Lesson 12: Using Set Operators
-
- Writing queries with the UNION operator
-
- Using EXCEPT and INTERSECT
- Using APPLY
Lab: Using Set Operators
-
- Writing queries that use UNION and UNION ALL set operators
-
- Writing queries that use CROSS APPLY and OUTER APPLY operators
- Write queries that use the EXCEPT and INTERSECT operators
Lesson 13: Using the Windows Sorting, Compensation, and Aggregation Features
-
- Creating Windows with OVER
- Exploring Window Features
Lab : Using Windows Ranking, Compensation, and Aggregation Features
-
- Write queries that use sort functions
-
- Write queries that use compensation functions
- Writing queries that use window aggregation functions
Lesson 14: Grouping and Pivoting Sets
-
- Writing queries with PIVOT and UNPIVOT
- Work with grouping sets
Lab: Pooling and Pivoting Sets
-
- Write queries that use the PIVOT operator
-
- Writing queries that use the UNPIVOT operator
- Writing queries that use the GROUPING SETS CUBE and ROLLUP subclauses
Lesson 15: Executing Stored Procedures
-
- Query data with stored procedures
-
- Pass parameters to stored procedures
-
- Creating simple stored procedures
- Work with dynamic SQL
Lab : Running Stored Procedures
-
- Using the EXECUTE statement to call stored procedures
-
- Pass parameters to stored procedures
- Execution of stored procedures in the system
