QUERYING DATA WITH MICROSOFT TRANSACT-SQL (SF)

Course Overview

The main purpose of this 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.

Course Objectives

Upon course completion, learners will be able to:

• Describe key capabilities and components of an on-premise and cloud-based MS 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.
• Execute stored procedures
• Program with T-SQL
• Implement error handling
• Implement transactions
• 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.
• Use window ranking, offset and aggregate functions
• Implement pivoting and grouping sets

Pre-requisites

Required Prerequisites:

Basic knowledge of the Microsoft Windows operating system and its core functionality.

Suggested Prerequisites:

• Working knowledge of Microsoft Office Excel
• Working knowledge of relational databases

Course Outline

Module 1: Getting Started with Transact-SQL

• Understand what SQL is and how it is used
• Identify database objects in schemas
• Identify SQL statement types
• Use the SELECT statement to query tables in a database
• Work with data types
• Handle NULLs

Module 2: Writing SELECT Queries

• Sort the results of a query by using the ORDER BY clause
• Limit the sorted results to show only the top n rows
• Return paged results
• Remove duplicates from results
• Filter data with predicates in the WHERE clause

Module 3: Combine multiple tables with JOINs in T-SQL

• Understand join concepts and syntax
• Write queries that use inner joins
• Write queries that use outer joins
• Write queries that use cross joins
• Write queries that use self-joins

Module 4: Write Subqueries in T-SQL

• Understand what subqueries are
• Use scalar or multi-valued subqueries
• Use self-contained or correlated subqueries

Module 5: Use built-in functions and GROUP BY in Transact-SQL

• Categorize built-in functions
• Use scalar functions
• Use ranking and rowset functions
• Use aggregate functions
• Summarize data with GROUP BY
• Filter groups with HAVING

Module 6: Modify data with T-SQL

• Insert data into a table
• Generate automatic values
• Update data in a table
• Delete data from a table
• Merge data based on multiple tables

 Module 7: Explore core data concepts

• Identify common data formats
• Describe options for storing data in files
• Describe options for storing data in databases
• Describe characteristics of transactional data processing solutions
• Describe characteristics of analytical data processing solutions

Module 8: Explore data roles and services

• Identify common data professional roles
• Identify common cloud services used by data professionals

Module 9: Explore fundamental relational data concepts

• Identify characteristics of relational data
• Define normalization
• Identify types of SQL statement
• Identify common relational database objects

Module 10: Explore relational database services in Azure

• Identify options for Azure SQL services
• Identify options for open-source databases in Azure
• Provision a database service on Azure

Module 11: Explore Azure Storage for non-relational data

• Describe features and capabilities of Azure blob storage
• Describe features and capabilities of Azure Data Lake Gen2
• Describe features and capabilities of Azure file storage
• Describe features and capabilities of Azure table storage
• Provision and use an Azure Storage account

Module 12: Explore fundamentals of Azure Cosmos DB

• Describe key features and capabilities of Azure Cosmos DB
• Identify the APIs supported in Azure Cosmos DB
• Provision and use an Azure Cosmos DB instance

Module 13: Explore fundamentals of large-scale data warehousing

• Identify common elements of a modern data warehousing solution
• Describe key features for data ingestion pipelines
• Identify common types of analytical data store and related Azure services
• Provision Azure Synapse Analytics and use it to ingest, process, and query data

Module 14: Explore fundamentals of real-time analytics

• Compare batch and stream processing
• Describe common elements of streaming data solutions
• Describe features and capabilities of Azure Stream Analytics
• Describe features and capabilities of Spark Structured Streaming on Azure
• Describe features and capabilities of Azure Synapse Data Explorer

Module 15: Explore fundamentals of data visualization

• Describe a high-level process for creating reporting solutions with Microsoft Power BI
• Describe core principles of analytical data modeling
• Identify common types of data visualization and their uses
• Create an interactive report with Power BI Desktop

Certificate Obtained and Conferred by

Awarded SF Statement of Attainment (SOA)

Upon completion of the course and assessment, students will also be awarded with this SF SOA:

ICT-DES-3001-1.1 Data Design

Certificate of Completion from NTUC LearningHub

Upon meeting at least 75% attendance and passing the assessment(s), participants will receive a Certificate of Completion from NTUCLearningHub.

Additional Details

Medium of Instruction: English 
Trainer: Trainee Ratio is 1:20

Price

Course Fee and Government Subsidies
	Individual Sponsored		Company Sponsored
			Non-SME		SME
	Before GST	With GST	Before GST	With GST	Before GST	With GST
Full Course Fee (For Foreigners and those not eligible for subsidies)	$1,950.00	$2,125.50	$1,950.00	$2,125.50	$1,950.00	$2,125.50
For Singapore Citizens aged 39 years and below and For all Singapore Permanent Residents (The minimum age for individual sponsored trainees is 21 years)	$585.00	$637.65	$585.00	$637.65	$195.00	$247.65
For Singapore Citizens aged 40 years and above	$195.00	$247.65	$195.00	$247.65	$195.00	$247.65

Funding Eligibility Criteria:

Individual Sponsored Trainee

Company Sponsored Trainee

Singapore Citizens or Singapore Permanent Residents From 1 October 2023, attendance-taking for SkillsFuture Singapore's (SSG) funded courses must be done digitally via the Singpass App. This applies to both physical and synchronous e-learning courses Trainee must achieve at least 75% attendance Trainee must pass all prescribed tests / assessments, and attain 100% competency NTUC LearningHub reserves the right to claw back the funded amount from trainee if he/she did not meet the eligibility criteria

Singapore Citizens or Singapore Permanent Residents From 1 October 2023, attendance-taking for SkillsFuture Singapore's (SSG) funded courses must be done digitally via the Singpass App. This applies to both physical and synchronous e-learning courses Trainee must achieve at least 75% attendance Trainee must pass all prescribed tests / assessments, and attain 100% competency NTUC LearningHub reserves the right to claw back the funded amount from the employer if trainee did not meet the eligibility criteria

Remarks:

Individual Sponsored Trainee	Company Sponsored Trainee
SkillsFuture Credit: Eligible Singapore Citizens can use their SkillsFuture Credit to offset course fee payable after funding UTAP: NTUC Members can enjoy up to 50% funding (capped at $250 per year) under Union Training Assistance Programme (UTAP)	Absentee Payroll (AP) Funding: $4.50 per hour, capped at $100,000 per enterprise per calendar year AP funding will be computed based on the actual number of training hours attended by the trainee Note: Courses / Modules under Professional Conversion Programme (PCP) will not be eligible for AP funding claim.

Terms and conditions apply. NTUC LearningHub reserve the right to make changes or improvements to any of the products described in this document without prior notice.

Prices are subject to other LHUB miscellaneous fees.