Querying Microsoft SQL Server with Transact-SQL

Inquire now

Duration  8 days – 56 hrs.

 

Overview

 

This comprehensive 8 days training course is designed for individuals seeking proficiency in querying Microsoft SQL Server databases using Transact-SQL (T-SQL). Participants will embark on a journey from fundamental query concepts to advanced data retrieval techniques. The course combines theoretical knowledge with hands-on exercises to ensure a practical understanding of Transact-SQL.

 

Objectives

 

  • Comprehend the basics of relational databases and SQL.
  • Develop proficiency in writing basic T-SQL queries.
  • Explore advanced querying techniques, including subqueries and joins.
  • Utilize set operators for data retrieval.
  • Master the manipulation of SQL Server data types.
  • Leverage T-SQL functions for efficient data retrieval and manipulation.
  • Learn to insert, update, and delete data using T-SQL.
  • Manage transactions to maintain data integrity.
  • Create and modify tables with T-SQL.
  • Implement data integrity using constraints.
  • Delve into advanced querying techniques such as Common Table Expressions (CTEs).
  • Optimize queries for performance using indexing strategies and execution plans.
  • Gain proficiency in database programming concepts.
  • Use stored procedures, functions, and triggers effectively.
  • Understand error handling mechanisms in T-SQL.
  • Implement exception management to handle unexpected situations.
  • Explore security features in SQL Server.
  • Implement secure authentication and authorization practices.
  • Utilize window functions for advanced data retrieval and analysis.
  • Implement ranking and aggregate window functions.
  • Understand and implement dynamic SQL for flexible query construction.
  • Safely execute dynamic SQL statements.
  • Identify and resolve performance bottlenecks.
  • Apply optimization techniques for efficient query execution.
  • Learn to query and manipulate XML and JSON data in SQL Server.
  • Understand the integration of XML and JSON with T-SQL.
  • Implement full-text search capabilities.
  • Optimize full-text queries for efficient information retrieval.
  • Embrace best practices for T-SQL development.
  • Apply industry standards for maintainable and efficient code.

 

Audience

 

  • Database Developers: Developers who are responsible for creating and optimizing queries to interact with SQL Server databases.
  • Database Administrators (DBAs): DBAs who manage SQL Server databases and need a deep understanding of T-SQL for query optimization and troubleshooting.
  • SQL Server Developers: Developers who specifically work with SQL Server as their database platform and want to enhance their T-SQL querying skills.
  • Data Analysts: Data analysts seeking to retrieve and analyze data from SQL Server databases using T-SQL.
  • Business Intelligence (BI) Professionals: I professionals who work with SQL Server as part of their data analysis and reporting tasks.
  • IT Professionals and System Administrators: IT professionals and system administrators who want to understand and support SQL Server databases in their environments.
  • Technology Managers: Managers overseeing technology teams involved in database development and management.
  • Software Engineers and Architects: Software engineers and architects interested in database design and interaction with SQL Server databases.
  • Students and Aspiring Database Professionals: Students pursuing a career in database management or related fields.
  • Business Analysts: Business analysts involved in querying databases to extract meaningful insights for decision-making.
  • Anyone Working with SQL Server Databases: Individuals from various roles who interact with SQL Server databases and want to improve their T-SQL querying skills.

Pre- requisites 

  • Must know how to use computer 
  • Proficiency in spreadsheets is an advantage.
  • No prior knowledge of SQL Server required.
  • SSMS installed on Windows PC/Laptop

 

Course Content

 

Module 1: Introduction to SQL, SQL Server, MS SQL

 

  • Database Normalization
  • Types and functions in Querying in SSMS

Module 2: Table Manipulation

 

  • SELECT statement
  • Summarizing and Ordering 
  • Summarizing with conditions
  • Adding Table
  • Find, delete and update data

Module 3: Constraints and Indexing

 

  • Constraints
  • Create, alter and securing views
  • Adding and deleting index
  • Create and alter triggers

Module 3: Constraints and Indexing

 

  • Constraints
  • Create, alter and securing views
  • Adding and deleting index
  • Create and alter triggers

 

Module 4: Working with datasets and procedures.

 

  • UNION and UNION ALL
  • CASE statement
  • MERGE Statement
  • Implementing TRY/CATCH/THROW

Module 5: Query Functions

 

  • Implementing aggregate queries
  • Ranking functions
  • Analytic functions
  • Grouping and spatial aggregates

 

Module 6: Query Techniques and Common Table Expressions

 

  • Sub queries clauses (WHERE, NOT, ANY, SOME, ALL, FROM)
  • Corelated subqueries
  • Grouping numbers
  • Common table expressions (CTE) and recursive CTE

 

Module 7: Advanced Data Manipulation and Query Techniques in Database Systems

 

  • Scalar Functions
  • Synonyms and Dynamics
  • GUIDs and sequences
  • Query and manage XML data converting tables to XML
  • Shredding XML data
  • Manipulating JSON
  • Creating, dropping temporal tables
  • Querying temporal data at a point of time

Module 8: Database Optimization Techniques

 

  • Manage transactions.
  • Query plans, heaps and scans
  • Clustered, non-clustered and filtered indexes
  • Different join types and HASH join in action
  • Nested Loops and Merge Joins
  • Other query optimization 
Inquire now

Best selling courses

This site uses cookies to offer you a better browsing experience. By browsing this website, you agree to our use of cookies.