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
Subjects
More Information
- NTUC LearningHub
Add a review