NTUC LearningHub

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.

Categories
More Information
  • NTUC LearningHub
Sponsored Content
Location
  • NTUC Trade Union House, 73 Bras Basah Road. S.189556, Singapore, Central Singapore Community Development Council 189556

  • No comments yet.
  • Add a review
    error: Content is protected !!