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