SQL Queries for Analysts

This course is designed for programmers and non-programmers to use Microsoft SQL to build reports.

Experienced as well as those new to Microsoft SQL will benefit from this class.

• The class starts at the beginning with a brief overview of database design and how to use SQL Server Management Studio.
• The class teaches students how to build basic SELECT statements.
• Each chapter then adds to the complexity of these SELECT statements, eventually bringing together data stored in multiple tables.
• You will master your skills with a comprehensive lab putting together what has been learned throughout the course, allowing students to apply what they have learned.

Take a look at the course outline to see the specific skills you will gain in each chapter.

Chapter 0: Introduction

• Course Overview
• Data Manipulation Verbs  
• Courseware Overview   
• Datasets Overview    

Chapter 1: 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  

Chapter 2: Database Design Concepts

• What is SQL  
• Database Diagrams  
• Database Terminology Overview    
• Chapter 2 Lab   
• Solution to the Exercise   

Chapter 3: Basic SELECT Statements

• The SELECT Statement 
• Execution Order of SELECT Statements  
• Ordering Results  
• Additional SELECT Options  
• Chapter 3 Lab   
• Answers to Exercises  

Chapter 4: 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   
• Chapter 4 Lab   
• Answers to Exercises  

Chapter 5: Enhanced SELECT Statements

• Expressions  
• Filtering Rows  
• Comparison Operators  
• Logical Operators  
• Chapter 5 Lab   
• Answers to Exercises  

Chapter 6: Handling NULL Data

• NULL vs Blank  
• = vs IS NULL  
• ISNULL function   
• COALESCE  
• Concatenating NULL Data  
• Chapter 6 Lab   
• Answers to Exercises  

Chapter 7: Aggregating and Grouping Data

• Aggregate Functions 
• GROUP BY 
• HAVING   
• HAVING vs WHERE  
• Chapter 7 Lab   
• Answers to Exercises  

Chapter 8: 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  
• Chapter 8 Lab   
• Answers to Exercises  

Chapter 9: Built-in Functions Overview

• Working with Functions  
• Mathematical Function Overview    
• String Function Overview    
• Date Time Function Overview    
• Nesting Functions  
• Chapter 9 Lab   
• Answers to Exercises  

Chapter 10: Subqueries

• Subqueries  
• Nested vs Correlated Subqueries  
• Subqueries in the SELECT Clause   
• Subqueries in the WHERE Clause   
• EXISTS  
• Subqueries in FROM Clause   
• Alternatives to Subqueries  
• Chapter 10 Lab   
• Answers to Exercises  
• Final Lab
• Final Lab Goals 
• Final Lab Resources

Students should have a general familiarity with database concepts.

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.