20761 Querying Data with Transact SQL
About this course
This course provides an understanding of the Transact-SQL language which is used by all SQL Server-related disciplines such as Database Administration, Database Development and Business Intelligence.
The main purpose of the course is to give students a good understanding of the Transact-SQL language which is used by all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. As such, the primary target audience for this course is: Database Administrators, Database Developers and BI professionals.
1 - INTRODUCTION TO MICROSOFT SQL SERVER 2016
The Basic Architecture of SQL Server
SQL Server Editions and Versions
Getting Started with SQL Server Management Studio
Lab: Working with SQL Server 2016 Tools
2 - INTRODUCTION TO T-SQL QUERYING
Understanding Predicate Logic
Understanding the Logical Order of Operations in SELECT statements
Lab: Introduction to Transact-SQL Querying
3 - WRITING SELECT QUERIES
Writing Simple SELECT Statements
Eliminating Duplicates with DISTINCT
Using Column and Table Aliases
Writing Simple CASE Expressions
Lab: Writing Basic SELECT Statements
4 - QUERYING MULTIPLE TABLES
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins
Lab: Querying Multiple Tables
5 - SORTING AND FILTERING DATA
Filtering Data with Predicates
Filtering with TOP and OFFSET-FETCH
Working with Unknown Values
Lab: Sorting and Filtering Data
6 - WORKING WITH SQL SERVER 2016 DATA TYPES
Introducing SQL Server 2016 Data Types
Working with Character Data
Working with Date and Time Data
Lab: Working with SQL Server 2016 Data Types
7 - USING DML TO MODIFY DATA
Modifying and Deleting Data
Lab: Using DML to Modify Data
8 - USING BUILT-IN FUNCTIONS
Writing Queries with Built-In Functions
Using Conversion Functions
Using Logical Functions
Using Functions to Work with NULL
Lab: Using Built-In Functions
9 - GROUPING AND AGGREGATING DATA
Using Aggregate Functions
Using the GROUP BY Clause
Filtering Groups with HAVING
Lab: Grouping and Aggregating Data
10 - USING SUBQUERIES
Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries
Lab: Using Subqueries
11 - USING TABLE EXPRESSIONS
Using Inline Table-Valued Functions
Using Derived Tables
Using Common Table Expressions
Lab: Using Table Expressions
12 - USING SET OPERATORS
Writing Queries with the UNION Operator
Using EXCEPT and INTERSECT
Lab: Using Set Operators
13 - USING WINDOWS RANKING, OFFSET, AND AGGREGATE FUNCTIONS
Creating Windows with OVER
Exploring Window Functions
Lab: Using Windows Ranking, Offset, and Aggregate Functions
14 - PIVOTING AND GROUPING SETS
Writing Queries with PIVOT and UNPIVOT
Working with Grouping Sets
Lab: Pivoting and Grouping Sets
15 - EXECUTING STORED PROCEDURES
Querying Data with Stored Procedures
Passing Parameters to Stored procedures
Creating Simple Stored Procedures
Working with Dynamic SQL
Lab: Executing Stored Procedures
16 - PROGRAMMING WITH T-SQL
T-SQL Programming Elements
Controlling Program Flow
Lab: Programming with T-SQL
17 - IMPLEMENTING ERROR HANDLING
Implementing T-SQL error handling
Implementing structured exception handling
Lab: Implementing Error Handling
18 - IMPLEMENTING TRANSACTIONS
Transactions and the database engines
Lab: Implementing Transactions
After completing this course, students will be able to:
- Describe key capabilities and components of SQL Server.
- Describe T-SQL, sets, and predicate logic.
- Write a single table SELECT statement.
- Write a multi-table SELECT statement.
- Write SELECT statements with filtering and sorting.
- Describe how SQL Server uses data types.
- Write DML statements.
- Write queries that use built-in functions.
- Write queries that aggregate data.
- Write subqueries.
- Create and implement views and table-valued functions.
- Use set operators to combine query results.
- Write queries that use window ranking, offset, and aggregate functions.
Transform data by implementing pivot, unpivot, rollup and cube.
- Create and implement stored procedures.
- Add programming constructs such as variables, conditions, and loops to T-SQL code.
Key Highlights and USPs Enumerates Why to Choose Us.
• Committed professionals with global experience
• Pan India reach, strong in Northern Region of India and expanding
• Strong Eco-System with industry connect and linkages with all stake holders
• Collaborative Model to offer the best solutions
• Value based organization with high integrity and transparency
• All our Competence Offering are world wide based
• Practical, field exposure and on-the-Job-Training
• Innovative solution offerings for Corporate Training.
Why Choose Leelajay Technologies?
Certified & Industry Experts Trainers
Multiple Training Delivery Models
Assessments and Mock Tests
Advance Analytical Reports
Contact our Relationship Managers for Corporate Packages requirements.