Data Analysis with SQL

SQL (Structured Query Language) is a programming language designed for managing data in a relational database. It’s been around since the 1970s and is the most common method of accessing data in databases today. SQL has a variety of functions that allow its users to read, manipulate, and change data. Though SQL is commonly used by engineers in software development, it’s also popular with data analysts for a few reasons:

  • It’s semantically easy to understand and learn.
  • Because it can be used to access large amounts of data directly where it’s stored, analysts don’t have to copy data into other applications.
  • Compared to spreadsheet tools, data analysis done in SQL is easy to audit and replicate. For analysts, this means no more looking for the cell with the typo in the formula.

SQL is great for performing the types of aggregations that you might normally do in an Excel pivot table—sums, counts, minimums and maximums, etc.—but over much larger datasets and on multiple tables at the same time.

Course Module

Module 1: Understanding and Describing Data

Module 2: The Basics of SQL for Analytics

Module 3: SQL for Data Preparation

Module 4: Aggregate Functions for Data Analysis

Course Outline

Introduction & Setup

  • Understanding SQL and databases
  • Key concepts of schema, tables, columns, primary keys, and foreign keys
  • Installation of SQL Server
  • Installation of SQL Server Management Studio (SSMS)
  • Sample company-wide DB setup (Adventure Works Cycle)
  • Introduction to relational and non-relational databases
  • Relational databases like MS SQL, MySQL, and PostgreSQL
  • Non-relational databases
  • SQL is useful across all,  including big data platforms
  • Building a relational SQL DB Table from scratch
  • CREATE TABLE to build from scratch
  • ALTER TABLE to modify table structure
  • DROP TABLE to delete table
  • INSERT INTO statement to add new rows of records
  • UPDATE statement modify existing rows of records
  • DELETE statement to remove rows of records
  • SELECT statement for data analysis
  • Basic SELECT statement
  • SELECT DISTINCT
  • SELECT TOP
  • WHERE clause
  • Multiple WHERE conditions
  • ORDER BY
  • GROUP BY
  • HAVING
  • Understanding when to use WHERE vs. HAVING
  • Logical Operators
  • AND
  • OR
  • NOT
  • IN
  • BETWEEN
  • LIKE
  • CASE
  • Advanced data manipulation
  • Carrying out mathematical operations
  • Using alias
  • JOIN and the different types
  • UNION, INTERSECT and EXCEPT
  • LIKE wildcards
  • Subqueries
  • Common Table Expressions (CTE)
  • String functions
  • Numbers formatting
  • Date formatting
  • Views and stored procedures
  • Use of views
  • How to create views
  • Use of stored procedures
  • How to create stored procedures
  • Exporting tables as stored procedures