Interrogating an SQL Database With SQL, Power BI & Excel

Summary

This course is suitafghble" - what level it's at, what previous experience/knowledge if any, is required. We want to target our message that actually speaks to a segment of the membership. Type of roles it's applicable would work too.

Venue details:  
Belfast, 8-10 Amelia Street, Belfast
Start date & time:  
19 November 2019 09:30
End date & time:  
19 November 2019 16:30
Price: €270.53  (€257.00 Member price)
By registering for this course you have accepted the terms and conditions
Training ticket cost:  
10.00
CPD hours:  
7.00
Speaker details
First nameLast name
Mullan Training

Description

Product type:  
CPD course
Category:  
Information technology

Who should attend?

The course is aimed at anyone who:

  • Has a good understanding of data and needs to know what is possible in terms of connecting to, extracting and reporting on data that is stored in a SQL database.
  • Wants to present the SQL data (for themselves or other users) in an informative dashboard style – using Power BI & Excel 

Course overview 

Designing and Creating a Database:

  • Relational Database Design Principles
  • The Origins of Relational Design
  • Data Normalization
  • Understanding the Referential Integrity
  • Beyond Normalization
  • Implementing the Design
  • Database Storage
  • Creating Databases
  • Modifying Database Options
  • Creating Tables
  • Creating Constraints
  • Using Database Diagrams

Data Selection Queries:

  • Understanding SQL
  • The SELECT Statement
  • Selecting All Columns
  • Concatenating Columns
  • Naming Columns
  • Using DISTINCT to Limit Values
  • The WHERE Clause
  • Transact-SQL Comparison Operators
  • The LIKE Operator
  • The BETWEEN Operator

Data Selection Queries:

  • Using IS NULL to Test for Nulls
  • Multiple Conditions with AND, OR, and NOT
  • Operator Precedence
  • Using the IN Operator
  • Using ORDER BY to Sort Data
  • Sorting on a Single Column
  • Sorting by Multiple Columns
  • Sorting with Expression
  • The GROUP BY Clause
  • Aggregate Functions
  • Counting Rows
  • Counting Columns
  • Counting with a WHERE Clause
  • Using GROUP BY
  • Using GROUP BY with GROUP BY
  • Using HAVING with GROUP BY
  • TOP Values Queries
  • Joining Tables
  • Cross Joins (Cartesian Products)
  • The Use of Keys in Joining
  • Join Notation
  • Inner Joins
  • Outer Joins
  • Self Joins

Modifying Data:

  • Modifying Data
  • Inserting Data
  • Inserting a Single Value
  • Inserting Multiple Values
  • Inserting Multiple Rows
  • Creating a New Table Using SELECT INTO
  • Temporary Tables
  • Using Bulk Copy to Insert Data
  • Updating Data
  • Updating a Single Row
  • Updating Multiple Rows and Columns
  • Updating from Another Table
  • Updating with TOP
  • Deleting Data
  • Deleting a Single Row
  • Deleting Multiple Rows

Data Analysis:

  • Data import and data export
  • Using Power Bi to analyze data
  • Using Excel to analyze data

Pivot Tables and Charts

Key learning outcomes

Users will learn how to:

  1. Connect to an SQL database & prepare the data for analysis (connect & transform)
  2. Analyse data  within SQL
  3. Extract data from an SQL database (to Excel, CSV)
  4. Connect directly to an SQL database from Excel & Power BI
  5. Present the SQL data in an informative way with dashboards, using Excel & Power BI