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
