Zenler Player
Your course is loading. Hang tight.
SQL for Data Analysis
Back to curriculum
0% Complete
0% Complete
Resources
How to be successful in anything
How to be successful in this course
Reinforcing the suggestions
About the instructor
Quiz
Why learn SQL?
Is SQL overrated?
Common terminologies
Initial feedback survey
Live
Offie Hours #1 (Aug 27, 2022)
Office Hours #3 (Oct 1, 2022)
Office Hours #4 (Oct 23, 2022)
Office Hours #5 (Nov 12, 2022)
Office Hours #6 (Jan 21, 2023)
CSV files
SQL Codes
MIMIC III Datasets
ZIP files
Installing PostgreSQL and pgAdmin in Windows
Installing DBeaver in Windows
Using DBeaver in Windows
Installing PostgreSQL and pgAdmin in Mac
Installing DBeaver in Mac
Using DBeaver in Mac
Six keywords to memorize
Creating your first table
Exercises
More on SELECT statement
WHERE clause
GROUP BY clause Part 1
GROUP BY clause Part 2
HAVING and ORDER BY clauses
LIMIT clause
Exercise (COVID19 data by US State)
Quiz (COVID19 by US States)
Downloads for this module
Understanding user permissions
Exporting data
Importing data using INSERT
Importing CSV data using COPY
Import CSV data using DBeaver
Downloads for this Module
Intuition for JOIN vs UNION
Inner join
Left join
Right Join
Left or Right - which one is what?
Full Join
Code along: creating the tables
Code along: Inner Join
Code along: Left Join
Code along: Right Join
Code along: Full Join
Codea long: UNION, UNION ALL
Code along: Custom Table
Code along: Temporary Table
Exercise: Counting Records
Exercise: Subquery
Exercise: Common Table Expression
Exercise: Count Distinct
Quiz
Downloads for this module
Module Intro - Do not skip
Numeric data type (Part 1)
Numeric data type (Part 2)
Business case explanations
Visually explore US County data
SQL Query to explore US County data
LIKE and ILIKE operators
Basic math operations
CAST to convert data type
Calculate natural increase (US County data)
Population total validation Part 1
Population total validation Part 2
Calculate percent land-area
ROUND function
CASE WHEN
Quiz
Problem statement
Intro to Window Functions
Window vs Group By: Distinction
How Window Function Works
Syntax of Window Function
List of Window Functions
Code along: Window vs Group By
Aggregate Window Functions (Part 1)
Aggregate Window Functions (Part 2)
Frame Clause Intro
Code along: Frame Clause
Quiz (Window Function Part 1)
Office Hour: Frame Clause Explained
Value Window Functions
FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()
LAG()
LEAD()
Ranking Window Functions
ROW_NUMBER()
RANK()
DENSE_RANK()
PERCENT_RANK()
NTILE()
CUME_DIST()
Window Summary
Problem statement (PDF) Updated April 2, 2023
Data sets
SQL: Solutions to Practical Challenge #2
Module introduction and motivation
Module topic: Data types
Module topic: Time zone
Module topic: Date arithmetic
Module topic: Extract date components
Module topic: Make date from other columns
Module topic: Further topic and motivation
Date time data types concepts (part 1)
Date time data types concepts (part 2)
Input Examples of data types (part 1)
Input Examples of data types (part 2)
SQL: CREATE TABLE and INSERT VALUES
SQL: Alternative ways to create table on the fly
SQL: List of time zones
SQL: Date arithmetic
SQL: Extraction of date components
SQL: Make Date
Quiz (date/time data types)
Quiz (date/time arithmetic)
Quiz (writing queries with date/time)
SQL: Calculate date difference (Part 1)
SQL: Calculate date difference (Part 2)
SQL: Calculate date difference (Part 3)
SQL: Calculate date difference (Part 4)
SQL: Calculate date difference (Part 5)
Quiz (date difference)
Pattern matching intro
Three ways to pattern matching
Pattern matching described
Key point to remember in pattern matching
LIKE operator fundamentals
LIKE operator examples (1)
LIKE operator examples (2)
LIKE operator use cases
Pattern matching SQL demo - intro
Creating a temporary table in SQL
LIKE: pattern starts with
LIKE: pattern ends with
LIKE: pattern matches anywhere in string
LIKE: starts with a specific character
SIMILAR TO operator intro
SIMILAR TO: examples
String manipulation (1)
String manipulation (2)
String manipulation (3)
String manipulation SQL demo
Data and code for this module
Module Introduction
SQL statements for modifying tables
ALTER TABLE add and drop columns
ALTER TABLE modify data type
UPDATE to modify values
SQL Demo - about the data for this module
SQL Demo - Import data
SQL Demo - Exploratory Data Analysis (1)
SQL Demo - Exploratory Data Analysis (2)
SQL Demo - Exploratory Data Analysis (3)
SQL Demo - Exploratory Data Analysis (4)
SQL Demo - To Do for fixing the issues found
SQL Demo - Standardize the company names
SQL Demo - fixing the zip codes
Conclusion and next steps - Practical Challenge
Practical Challenge #3
Solutions: Practical Challenge #3
About this module
What are advanced topics?
About this module
About this module
About this module
TBA
Frame clause explained (Oct 23, 2022)
Multiple join explained (Oct 23, 2022)
RANK vs DENSE RANK explained (Oct 23, 2022)
LIKE vs ILIKE explained (Oct 23, 2022)
1. Setting up for Success
Resources
How to be successful in anything
How to be successful in this course
Reinforcing the suggestions
About the instructor
Quiz
Why learn SQL?
Preview
Is SQL overrated?
Preview
Common terminologies
Preview
Initial feedback survey
Office Hours
Live
Offie Hours #1 (Aug 27, 2022)
Office Hours #3 (Oct 1, 2022)
Office Hours #4 (Oct 23, 2022)
Office Hours #5 (Nov 12, 2022)
Office Hours #6 (Jan 21, 2023)
1. Downloadable Data and Codes
CSV files
SQL Codes
MIMIC III Datasets
ZIP files
2. Setting up Environment in Windows
Installing PostgreSQL and pgAdmin in Windows
Installing DBeaver in Windows
Using DBeaver in Windows
3. Setting up the Environment in Mac
Installing PostgreSQL and pgAdmin in Mac
Installing DBeaver in Mac
Using DBeaver in Mac
4. Querying Data with SELECT Statement
Six keywords to memorize
Creating your first table
Exercises
More on SELECT statement
WHERE clause
GROUP BY clause Part 1
GROUP BY clause Part 2
HAVING and ORDER BY clauses
LIMIT clause
Exercise (COVID19 data by US State)
Quiz (COVID19 by US States)
5. Importing and Exporting Data
Downloads for this module
Understanding user permissions
Exporting data
Importing data using INSERT
Importing CSV data using COPY
Import CSV data using DBeaver
6. SQL Joins for Combining Multiple Tables
Downloads for this Module
Intuition for JOIN vs UNION
Inner join
Left join
Right Join
Left or Right - which one is what?
Full Join
Code along: creating the tables
Code along: Inner Join
Code along: Left Join
Code along: Right Join
Code along: Full Join
Codea long: UNION, UNION ALL
Code along: Custom Table
Code along: Temporary Table
Exercise: Counting Records
Exercise: Subquery
Exercise: Common Table Expression
Exercise: Count Distinct
Quiz
7. Calculated / Derived Columns
Downloads for this module
Module Intro - Do not skip
Numeric data type (Part 1)
Numeric data type (Part 2)
Business case explanations
Visually explore US County data
SQL Query to explore US County data
LIKE and ILIKE operators
Basic math operations
CAST to convert data type
Calculate natural increase (US County data)
Population total validation Part 1
Population total validation Part 2
Calculate percent land-area
ROUND function
CASE WHEN
Quiz
8. Practical Challenge #1
Problem statement
9. Window Functions
Intro to Window Functions
Window vs Group By: Distinction
Preview
How Window Function Works
Syntax of Window Function
List of Window Functions
Code along: Window vs Group By
Aggregate Window Functions (Part 1)
Aggregate Window Functions (Part 2)
Frame Clause Intro
Code along: Frame Clause
Quiz (Window Function Part 1)
Office Hour: Frame Clause Explained
Value Window Functions
FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()
LAG()
LEAD()
Ranking Window Functions
ROW_NUMBER()
RANK()
DENSE_RANK()
PERCENT_RANK()
NTILE()
CUME_DIST()
Preview
Window Summary
10. Practical Challenge #2
Problem statement (PDF) Updated April 2, 2023
Preview
Data sets
SQL: Solutions to Practical Challenge #2
11. Working with Date and Time variables
Module introduction and motivation
Preview
Module topic: Data types
Preview
Module topic: Time zone
Module topic: Date arithmetic
Module topic: Extract date components
Module topic: Make date from other columns
Module topic: Further topic and motivation
Preview
Date time data types concepts (part 1)
Date time data types concepts (part 2)
Input Examples of data types (part 1)
Input Examples of data types (part 2)
SQL: CREATE TABLE and INSERT VALUES
SQL: Alternative ways to create table on the fly
SQL: List of time zones
SQL: Date arithmetic
SQL: Extraction of date components
SQL: Make Date
Quiz (date/time data types)
Quiz (date/time arithmetic)
Quiz (writing queries with date/time)
SQL: Calculate date difference (Part 1)
SQL: Calculate date difference (Part 2)
Preview
SQL: Calculate date difference (Part 3)
SQL: Calculate date difference (Part 4)
SQL: Calculate date difference (Part 5)
Quiz (date difference)
12. Pattern Matching and String Manipulation
Pattern matching intro
Three ways to pattern matching
Pattern matching described
Key point to remember in pattern matching
LIKE operator fundamentals
LIKE operator examples (1)
LIKE operator examples (2)
LIKE operator use cases
Pattern matching SQL demo - intro
Creating a temporary table in SQL
LIKE: pattern starts with
LIKE: pattern ends with
LIKE: pattern matches anywhere in string
LIKE: starts with a specific character
SIMILAR TO operator intro
SIMILAR TO: examples
String manipulation (1)
String manipulation (2)
String manipulation (3)
String manipulation SQL demo
13. Modifying Table and Records
Data and code for this module
Module Introduction
Preview
SQL statements for modifying tables
Preview
ALTER TABLE add and drop columns
Preview
ALTER TABLE modify data type
UPDATE to modify values
SQL Demo - about the data for this module
SQL Demo - Import data
Preview
SQL Demo - Exploratory Data Analysis (1)
SQL Demo - Exploratory Data Analysis (2)
SQL Demo - Exploratory Data Analysis (3)
SQL Demo - Exploratory Data Analysis (4)
SQL Demo - To Do for fixing the issues found
SQL Demo - Standardize the company names
SQL Demo - fixing the zip codes
Conclusion and next steps - Practical Challenge
Practical Challenge #3
Solutions: Practical Challenge #3
ADVANCED TOPICS: Table structure and Constraints (incomplete)
About this module
What are advanced topics?
ADVANCED TOPICS: Three types of table relationships (incomplete)
About this module
ADVACNED TOPICS: Subquery (incomplete)
About this module
ADVANCED TOPICS: Query Optimization (incomplete)
About this module
Common Table Expressions (CTE) (incomplete)
TBA
EXPLAINED in Office Hours
Frame clause explained (Oct 23, 2022)
Multiple join explained (Oct 23, 2022)
RANK vs DENSE RANK explained (Oct 23, 2022)
LIKE vs ILIKE explained (Oct 23, 2022)
×
This is an unpublished lesson. This lesson will not be shown for students unless you set it as Public.
Back to Dashboard
No contents are available in this lesson!
No lessons available !
Back to Dashboard
Lesson contents locked
Enroll to unlock this lesson.
Enroll to unlock
Mark as Complete