Course Outline
Module 1: Introduction to TSQL for Business Intelligence for Government
This module discusses the differences between writing analytical queries and transactional DML queries. It provides an overview of the typical architecture of a business intelligence environment, emphasizing its relevance for government operations. The module delves into the role of SELECT queries in retrieving data for analysis from relational databases, introduces the sample database to be used in the course, and begins a presentation of the SELECT query.
Lessons
- Two Approaches to SQL Programming
- TSQL Data Retrieval in an Analytics Environment for Government
- The Database Engine for Government Use
- SQL Server Management Studio and the CarDeal Sample Database for Government
- Identifying Variables in Tables for Government Analysis
- SQL as a Declarative Language for Government Applications
- Introduction to the SELECT Query for Government Use
Lab : Lab 1
- Create a database diagram for government use
- Create and execute basic SELECT queries for government datasets
After completing this module, students will be able to:
- Describe the purpose of analytical queries in government contexts
- Explain the function of TSQL data retrieval in an analytics/business intelligence environment for government
- Outline the primary functions of the database engine in government systems
- Discuss TSQL as a declarative language suitable for government applications
- Identify variables of interest in database tables for government analysis
- Write basic SELECT queries for government datasets
Module 2: Turning Table Columns into Variables for Analysis: SELECT List Expressions, WHERE, and ORDER BY
This module covers the identification of and relationship between levels of measurement and column data types. It continues a discussion of the SELECT query and introduces the WHERE and ORDER BY clauses, focusing on their application in government datasets.
Lessons
- Turning Columns into Variables for Government Analysis
- Column Expressions, Data Types, and Built-in Functions for Government Use
- Column Aliases for Government Queries
- Data Type Conversions for Government Datasets
- Built-in Scalar Functions for Government Applications
- Table Aliases for Government Queries
- The WHERE Clause in Government Contexts
- ORDER BY for Government Data Analysis
Lab : Lab 2 - Write queries using:
- Column and table aliases for government datasets
- DISTINCT for government data retrieval
- WHERE for filtering government data
- ORDER BY for organizing government data
- Built-in functions for government applications
- Explicit and implicit data type conversion for government datasets
After completing this module, students will be able to:
- Implement column expressions in SELECT queries for government use
- Use column and table aliases in government queries
- Describe data types and implement data type conversions for government datasets
- Apply built-in functions in government TSQL queries
- Use WHERE and ORDER BY clauses in SELECT queries for government analysis
Module 3: Combining Columns from Multiple Tables into a Single Dataset: The JOIN Operators
This module discusses creating single datasets for analysis by combining results from multiple database tables using JOIN operations, tailored for government data environments.
Lessons
- Primary Keys, Foreign Keys, and Joins in Government Databases
- Understanding Joins, Part 1: CROSS JOIN and the Full Cartesian Product for Government Use
- Understanding Joins, Part 2: The INNER JOIN for Government Queries
- Understanding Joins, Part 3: The OUTER JOINS for Government Data Analysis
- Understanding Joins, Part 4: Joining More Than Two Tables in Government Datasets
- Understanding Joins, Part 5: Combining INNER and OUTER JOINs for Government Use
- Combining JOIN Operations with WHERE and ORDER BY for Government Queries
Lab : Lab 3 - Write SELECT queries using:
- Inner join for government datasets
- Left, right, and full join for government data analysis
- Joins of more than two tables for comprehensive government data retrieval
- Join operators, in addition to WHERE and ORDER BY, for government queries
After completing this module, students will be able to:
- Discuss and describe the conceptual flow of JOIN operations in government contexts
- Implement INNER and OUTER JOIN operations on two or more tables for government data analysis
- Describe the order of operations of SELECT clauses in government queries
- Combine JOIN operations with WHERE and ORDER BY for government datasets
Module 4: Creating an Appropriate Aggregation Level Using GROUP BY
This module covers the aggregation of quantitative column values across grouping factors to facilitate group-wise comparisons and/or change the granularity of a dataset, specifically for government applications.
Lessons
- Identifying Required Aggregation Level and Granularity for Government Data
- Aggregate Functions for Government Use
- GROUP BY for Government Data Analysis
- HAVING for Filtering Government Data by Aggregate Values
- Order of Operations in SELECT Queries for Government Applications
Lab : Lab 4 - Write queries using:
- Aggregate functions for government datasets
- Aggregate function with HAVING for government data filtering
- Aggregate function with GROUP BY and HAVING for government analysis
- Aggregate function with GROUP BY, HAVING, WHERE, and ORDER BY for comprehensive government queries
After completing this module, students will be able to:
- Describe the row granularity of result sets in government contexts
- Discuss and implement aggregate functions to achieve required row granularity for government data
- Use GROUP BY to calculate aggregate values for groups in government datasets
- Use HAVING to filter records in the result set by aggregate value for government analysis
- Combine GROUP BY and HAVING with WHERE and ORDER BY for government queries
Module 5: Subqueries, Derived Tables, and Common Table Expressions
This module covers the use of subqueries, derived tables, and common table expressions in SELECT queries as techniques for creating intermediate result sets, particularly for government data analysis.
Lessons
- Non-correlated and Correlated Subqueries for Government Use
- Derived Tables for Government Queries
- Common Table Expressions for Government Data Analysis
Lab : Lab 5 - Write queries using:
- Non-correlated subqueries for government datasets
- Correlated subqueries for government data analysis
- Derived tables for government queries
- Common table expressions for government applications
- Subqueries, derived tables, and common table expressions in combination with other topics for government use
After completing this module, students will be able to:
- Describe and discuss the rationale for creating intermediate result sets within SELECT queries for government applications
- Implement non-correlated and correlated subqueries for government data analysis
- Use derived tables in government TSQL queries
- Create common table expressions for government datasets
- Develop intermediate to advanced TSQL queries to retrieve result sets for government analysis
Module 6: Encapsulating Data Retrieval Logic
This module discusses the encapsulation of data retrieval logic in views, table-valued functions, and stored procedures. It also describes scenarios where these techniques are useful for producing datasets for government analysis, and addresses database security issues involved in creating and using these objects while maintaining current permission sets on source data.
Lessons
- Views for Government Data Retrieval
- Table-valued Functions for Government Queries
- Stored Procedures for Government Applications
- Creating Objects for Read-access Users in Government Databases
- Creating Database Accounts for Analytical Client Tools in Government Environments
Lab : Lab 6
- Create a SQL login for government users
- Create a database user and assign required permissions for government access
- Create a database schema for views, functions, and stored procedures for government use
- Create a view for government data retrieval
- Create a table-valued function for government queries
- Create a stored procedure for government applications
- Allow users with read-only access to use views, table-valued functions, and stored procedures in government databases
After completing this module, students will be able to:
- Identify scenarios where views, table-valued functions, and stored procedures simplify data retrieval for government applications
- Compare and contrast views, table-valued functions, and stored procedures in government contexts
- Create views, table-valued functions, and stored procedures for government use
- Describe the security requirements for creating database objects in government systems
- Implement views, table-valued functions, and stored procedures for users with read-only access to source data in government databases
Module 7: Getting Your Dataset to the Client
This module covers common techniques for making datasets produced by SELECT queries available to analytical client tools such as SQL Server Reporting Services, PowerBI, Excel, and R. It discusses running queries directly from the client tool and exporting datasets to text files that can then be accessed by the client tool, all tailored for government use.
Lessons
- Connecting to SQL Server and Submitting Queries from Client Tools in Government Environments
- Connecting and Running SELECT Queries from:
- Excel for Government Data Analysis
- PowerBI for Government Applications
- RStudio for Government Data Science
- Exporting Datasets to Files Using:
- Results Pane from SSMS for Government Use
- The bcp Utility for Government Data Export
- The Import/Export Wizard for Government Data Management
Lab : Lab 7
- Retrieving the results of a view in Excel for government analysis
- Running an ad-hoc SELECT query from Excel for government data retrieval
- Running an ad-hoc query from PowerBI for government applications
- Running an ad-hoc query from RStudio for government data science
- Using the Import/Export wizard to write the results of a query to a text file for government use
After completing this module, students will be able to:
- Describe the properties of database connection strings for government applications
- Run queries from and return results to Excel, PowerBI, and RStudio for government data analysis
- Export query results to external text files using the SSMS results pane, the bcp utility, and the Import/Export Wizard for government use
Requirements
Before attending this course for government, students must have:
- A foundational understanding of data analysis and business intelligence scenarios. For example, familiarity with a work-related business intelligence project or requirement within the public sector.
- Basic knowledge of the Windows operating system and its core functionalities, including file system navigation.
- An elementary understanding of the purpose of relational database management systems, such as SQL Server, in supporting government operations.
Testimonials (4)
Deepthi was super attuned to my needs, she could tell when to add layers of complexity and when to hold back and take a more structured approach. Deepthi truly worked at my pace and ensured I was able to use the new functions /tools myself by first showing then letting me recreate the items myself which really helped embed the training. I could not be happier with the results of this training and with the level of expertise of Deepthi!
Deepthi - Invest Northern Ireland
Course - IBM Cognos Analytics
Share example of application
Course - Alteryx for Data Analysis
Very clearly articulated and explained
Harshit Arora - PwC South East Asia Consulting
Course - Alteryx for Developers
Linear regression - the algorithm to predict the trend
