SQL Queries for Analysts

Overview

Welcome to the SQL Queries for Analysts course, a comprehensive journey designed to empower you with the expertise in SQL for effective data analysis. As you embark on this educational adventure, you’ll gain a deep understanding of how SQL query writing can transform raw data into actionable insights.

This course is meticulously crafted to cater to both experienced professionals and newcomers in the field of data analysis. Regardless of your programming background, you will find the content both accessible and challenging. The curriculum starts with the fundamentals of SQL, using Microsoft SQL as the platform for practical learning.

You’ll be guided through a series of progressively complex scenarios, where you’ll learn to construct and refine SQL queries to extract meaningful information from databases. By the conclusion of this course, you will have developed a robust skill set in SQL query writing, equipping you to tackle real-world data analysis challenges with confidence and proficiency.

Whether your goal is to enhance your existing database skills or to start a new journey in data analysis, this course will be a pivotal step in your professional development.

Audience Profile

This course is intended for information workers and database professionals who have a general familiarity with databases prior to the class.
Working knowledge of Microsoft SQL Server or Microsoft Office applications is beneficial.

At Course Completion

Course Highlights
Introduction to SQL and database design concepts.
Mastering SQL Server Management Studio (SSMS) for query writing.
Building basic SELECT statements and gradually advancing to complex queries.
Working with data types, expressions, and logical operators.
Effective handling of NULL data.
Aggregating and grouping data using aggregate functions.
Joining multiple tables for comprehensive data analysis.
Exploring built-in SQL functions for mathematical, string, and date-time operations.
Harnessing the power of subqueries for in-depth data analysis.

Outline

Introduction
Course Overview
Data Manipulation Verbs
Courseware Overview
Datasets Overview
Overview of SSMS and Query Writing
Working with SSMS
Using the SQL Editor
Creating SSMS Script Projects
Tips and Tricks with SSMS
Adding Comments to Queries
Understanding Batches and Scripts
Database Design Concepts
What is SQL
Database Diagrams
Database Terminology Overview
Lab
Solution to the Exercise
Basic SELECT Statements
The SELECT Statement
Execution Order of SELECT Statements
Ordering Results
Additional SELECT Options
Lab
Answers to Exercises
Working with Data Types
Data Type Precedence
String Related Data Types
Numeric Data Types
Date and Time Data Types
Converting Between Data Types
How to Find Help on Functions
Understanding Data Type Conversion
Lab
Answers to Exercises
Enhanced SELECT Statements
Expressions
Filtering Rows
Comparison Operators
Logical Operators
Lab
Answers to Exercises
Handling NULL Data
NULL vs Blank
= vs IS NULL
ISNULL function
COALESCE
Concatenating NULL Data
Lab
Answers to Exercises
Aggregating and Grouping Data
Aggregate Functions
GROUP BY
HAVING
HAVING vs WHERE
Lab
Answers to Exercises
Joining Multiple Tables
JOINS
INNER JOIN
OUTER JOIN
CROSS JOIN
Joining Three or More Tables
Self-join
Alternate Syntax, Implicit Joins
Set Operations
Working with Multiple Set Operators
Viewing Graphical Execution Plans
Lab
Answers to Exercises
Built-in Functions Overview
Working with Functions
Mathematical Function Overview
String Function Overview
Date Time Function Overview
Nesting Functions
Lab
Answers to Exercises
Subqueries
Subqueries
Nested vs Correlated Subqueries
Subqueries in the SELECT Clause
Subqueries in the WHERE Clause
EXISTS
Subqueries in FROM Clause
Alternatives to Subqueries
Lab
Answers to Exercises
Final Lab
Final Lab Goals
Final Lab Resources

Prerequisites

General familiarity with database concepts. Working knowledge of Microsoft SQL Server or Microsoft Office applications is beneficial.