MS 20761 : Querying Data with Transact SQL Training Course

Course Description

This three-day instructor-led training is designed for professionals seeking to transition into database administration roles or those whose current responsibilities are expanding to include database management for government systems. The curriculum covers foundational database concepts, including classification, programming languages, and architectural design.

Target Audience

The primary target for this course is individuals entering database-focused positions or those whose professional scope now incorporates database technologies.

Learning Outcomes

Upon successful completion of this course, participants will be able to:
• Identify core database concepts within the SQL Server 2016 framework
• Specify the database languages utilized in SQL Server 2016
• Outline data modeling methodologies
• Explain normalization and denormalization procedures
• Define relationship types and their implications for database design
• Evaluate the impact of database design on system performance
• Identify common database objects for government use

This curriculum is tailored for database administrators, software developers, and technical staff responsible for managing enterprise data infrastructure for government applications.

Key Learning Objectives:

• Develop and refine competencies in database creation, maintenance, and governance
• Master the syntax and application of SQL for accurate data retrieval and modification
• Implement rigorous security protocols to protect sensitive database assets
• Leverage advanced technologies, including replication, automation, and business intelligence
• Utilize Microsoft SQL Server capabilities to develop complex reporting solutions and technical services for developers

This curriculum is designed for professionals proficient in SQL within the Microsoft SQL Server 2008 and 2012 environments. It emphasizes set-based querying, query performance optimization, index management, and the analysis of execution plans for government systems.

Additional topics include the application of table expressions, ranking functions, and the administration of partitioned tables.

Mastering advanced Transact-SQL for Microsoft SQL Server is essential for executing enterprise-grade database automation for government operations. This curriculum covers core development components, including procedural programming, structured error management, transaction control, cursor execution, stored procedures, and database triggers. It prepares experienced developers to design complex backend data workflows, minimize application-layer latency, and establish robust solutions for critical database infrastructure within public sector environments.

The DP-300T00: Administering Microsoft Azure SQL Solutions certification verifies the technical competency of information technology professionals in managing and implementing cloud-based database solutions within Microsoft Azure. It confirms proficiency in administering Azure SQL Server instances, overseeing database operations, and utilizing T-SQL. This credential is essential for public sector organizations undergoing digital transformation, as it ensures qualified personnel can effectively manage data administration, migration, and performance optimization for government applications in Azure environments. Attaining the DP-300T00 certification strengthens the professional credibility of IT staff, positioning them as critical assets for maintaining robust and secure database services for government systems and operations.

This instructional program is structured to provide a comprehensive understanding of relational database security principles. It covers the management of security archives and the administration of user access rights to specific resources. The curriculum also addresses secure data storage, the obfuscation of sensitive information, and the implementation of monitoring protocols for both database and server security. 

Upon successful completion of the course, participants will:
▪ Demonstrate proficiency in the security principles governing relational databases
▪ Apply best practices for managing database archival copies
▪ Execute methods for retrieving relevant data from database sources
▪ Administer user access rights for designated resources
▪ Implement secure data storage standards
▪ Utilize techniques to obscure sensitive information
▪ Conduct security monitoring of database and server environments

This curriculum is designed to equip personnel with the expertise required to configure database servers in accordance with operational specifications, establish robust monitoring procedures for system components, and implement rapid response protocols for identified anomalies. Upon completion, participants will be capable of diagnosing the root causes of performance deficiencies within SQL Server environments and applying targeted optimizations to eliminate operational bottlenecks, ensuring alignment with public sector efficiency standards for government applications.

Upon completion of the training, the participant:
▪ will be able to identify and diagnose performance bottlenecks within the database and/or server infrastructure
▪ will be able to implement comprehensive monitoring of SQL Server operations
▪ will be able to formulate recommendations for enhancing the performance of database and server environments.

This course is structured to deliver a comprehensive understanding of advanced SQL syntax and methodologies within the Microsoft SQL Server environment. Participants will acquire proficiency in analytical functions, table joins, subqueries, and aggregation techniques, enabling the migration of data analysis workflows from spreadsheet applications to the database layer. This strategic shift enhances operational efficiency and reinforces the integrity of IT systems deployed in government contexts.

This SQL training program is designed for individuals seeking to acquire fundamental competencies in managing SQL Server databases for government operations. The course facilitates the following learning outcomes:

• Proficiency in operating SQL Server and SQL Server Management Studio.
• Comprehensive understanding of core concepts, including Databases, SQL, and RDBMS.
• Competency in creating tables and applying DDL, DML, and DAL standards.
• Evaluation of prevailing RDBMS packages and comparative analysis of their capabilities.
• Foundational knowledge of NoSQL architectures and the transition of organizations toward hybrid database solutions for government applications.

Developing efficient SQL queries for Microsoft SQL Server establishes the technical foundation for enterprise data pipelines and information governance. This curriculum examines core methodologies, beginning with relational database architectures and advancing through data retrieval, filtering, sorting, and aggregation techniques utilizing grouping functions and subqueries. The content directs professionals through multi-table joins, set operators, data manipulation language (DML) operations, and transaction control mechanisms to ensure the reliable extraction and management of business intelligence datasets for government operations.

SQL Server is a robust enterprise-grade relational database management system developed by Microsoft, engineered to facilitate the efficient storage, management, and retrieval of data.

This instructor-led live training session, available via online or onsite formats, is specifically designed for intermediate-level information technology professionals who intend to oversee, configure, and sustain SQL Server environments.

Upon completion of this curriculum, participants will possess the competency to:

• Deploy and configure SQL Server instances for government and other organizational environments.
• Administer database objects, user accounts, and access control permissions.
• Monitor system performance and resolve operational issues effectively.
• Automate routine operational tasks through scheduled jobs and maintenance plans.

Instructional Methodology

• Interactive instructional sessions facilitated by structured discussion.
• Extensive practical exercises and skill application activities.
• Real-time implementation tasks conducted within a live laboratory setting.

Instructional Customization

• To initiate a tailored training program for this course, please contact the provider to discuss specific requirements.

SQL Server Analysis Services (SSAS) is a Microsoft SQL Server component designed for Online Analytical Processing (OLAP) and data mining. It facilitates the analysis of data spanning multiple databases, tables, or files. The semantic data models generated by SSAS serve client applications, including Power BI, Excel, Reporting Services, and other data visualization tools, to support informed decision-making for government

In this instructor-led training (available onsite or remote), participants will acquire the skills necessary to utilize SSAS for analyzing extensive data volumes within databases and data warehouses.

Upon completion of this training, participants will be proficient in:

• Installing and configuring SSAS
• Comprehending the interoperability between SSAS, SSIS, and SSRS
• Applying multidimensional data modeling to derive business insights from data
• Designing OLAP (Online Analytical Processing) cubes
• Querying and manipulating multidimensional data via the MDX (Multidimensional Expressions) query language
• Deploying enterprise-grade BI solutions using SSAS

Target Audience

• Business Intelligence (BI) professionals
• Data Analysts
• Database and data warehousing specialists

Course Format

• Interactive instruction and facilitation
• Extensive exercises and practical application
• Hands-on implementation in a live laboratory environment

Customization Options

• This curriculum is aligned with the latest versions of Microsoft SQL Server and SSAS.
• To arrange customized training tailored to specific needs, please contact the course provider.

This instructor-led, live training in US (delivered online or onsite) is intended for entry-level data professionals seeking to develop proficiency in designing, implementing, and managing SSIS packages for data integration and transformation tasks for government.

Upon completion of this training, participants will be proficient in:

• Understanding the architectural structure and components of SSIS.
• Designing and executing ETL processes utilizing SSIS.
• Utilizing SSIS tools to develop, deploy, and oversee data integration solutions.
• Diagnosing and optimizing SSIS packages to ensure performance and reliability.

SQL Server Integration Services (SSIS) constitutes a core component of Microsoft SQL Server, establishing the foundation for data integration and workflow automation. This technology facilitates critical operations in data migration, warehousing, and transformation for government applications.

This instructor-led, live training module (delivered online or onsite) is specifically tailored for intermediate-level data professionals and developers. The curriculum emphasizes the design of advanced SSIS data flows, transformation logic, and the management of Slowly Changing Dimensions (SCD) within data warehousing contexts for government systems.

Upon successful completion of this training, participants will demonstrate the ability to:

• Design and deploy resilient data flow architectures using SSIS.
• Effectively interface with diverse data sources, targets, and transformation components.
• Implement Slowly Changing Dimensions (SCD) to manage historical data revisions.
• Execute, deploy, and resolve technical issues with SSIS packages effectively.

Course Format

• Interactive instruction and facilitated discussion.
• Extensive practical exercises and scenario-based practice.
• Hands-on implementation within a live-lab environment.

Customization Options

• To arrange tailored training for this curriculum, please contact our team for scheduling and alignment.