Skip to content

Welcome

Thanks for stopping by! This workbook is a collection of exercises I have developed throughout my journey towards becoming a certified Data Analyst Associate.

Each section of this workbook is organized by course and further broken down by chapter. For each chapter, I have included exercises that apply the concepts and techniques covered. You’ll find exercises on various topics, including SQL queries, data manipulation, and data visualization, each demonstrating my ability to handle real-world data challenges.

Thank you for exploring my work. I hope this workbook provides valuable insight into my data analysis capabilities and approach.

Table of Contents

  • 01. Introduction to SQL (Invalid URL)
    • Querying (Invalid URL)
  • 02. Intermediate SQL (Invalid URL)
    • Selecting Data (Invalid URL)
    • Filtering Records (Invalid URL)
    • Aggregate Functions (Invalid URL)
    • Sorting and Grouping (Invalid URL)
  • 03. Joining Data in SQL (Invalid URL)
    • Introducing INNER JOINs (Invalid URL)
    • Outer Joins, CROSS JOINs, and Self Joins (Invalid URL)
    • Set Theory for SQL Joins (Invalid URL)
    • Subqueries (Invalid URL)
  • 04. Data Manipulation in SQL (Invalid URL)
    • We'll Take the CASE (Invalid URL)
    • Short and Simple Subqueries (Invalid URL)
    • Correlated Queries, Nested Queries, and Common Table Expressions (Invalid URL)
    • Window Functions (Invalid URL)
  • 05. PostgreSQL Summary Stats and Window Functions (Invalid URL)
    • Introduction to Window Functions (Invalid URL)
    • Fetching, Ranking, and Paging (Invalid URL)
    • Aggregate Window Functions and Frames (Invalid URL)
    • Beyond Window Functions (Invalid URL)
  • 06. Functions for Manipulating Data in PostgreSQL (Invalid URL)
    • Overview of Common Data Types (Invalid URL)
    • Working with DATE/TIME Functions and Operators (Invalid URL)
    • Parsing and Manipulating Text (Invalid URL)
    • Full-Text Search and PostgreSQL Extensions (Invalid URL)

01. Introduction to SQL

Querying

Spinner
DataFrameas
df38
variable
-- information for all orders

SELECT *
FROM OT.ORDERS

02: Intermediate SQL

Selecting Data

Spinner
DataFrameas
df1
variable
-- DISTINCT()
-- return the distinct statuses for orders

SELECT DISTINCT STATUS 
FROM OT.ORDERS

Filtering Records Test

Spinner
DataFrameas
df7
variable
-- WHERE
-- return the number of pending orders

SELECT COUNT(*) 
FROM OT.ORDERS 
WHERE STATUS = 'Pending'
Spinner
DataFrameas
df8
variable
-- AND
-- NUMBER OF ORDERS THAT REQUIRED AT LEAST 100 OF A PRODUCT THAT COST OVER $200
SELECT COUNT(DISTINCT ORDER_ID) 
FROM OT.ORDER_ITEMS 
WHERE QUANTITY >= 100 
	AND UNIT_PRICE > 200
Spinner
DataFrameas
df9
variable
-- BETWEEN
-- product where the list price is between $200 and $500
SELECT 
	PRODUCT_ID
	, PRODUCT_NAME
FROM PRODUCTS
WHERE LIST_PRICE BETWEEN 200 AND 500
Spinner
DataFrameas
df10
variable
-- LIKE (%)
-- return products associated with gaming

SELECT 
	PRODUCT_ID
	, PRODUCT_NAME
FROM PRODUCTS
WHERE LOWER(PRODUCT_NAME) LIKE '%gaming%' -- use LOWER to change to lower case
Spinner
DataFrameas
df11
variable
-- LIKE (_)
-- return products from any of the G.Skill Ripjaws series

SELECT 
	PRODUCT_ID
	, PRODUCT_NAME
FROM PRODUCTS
WHERE PRODUCT_NAME LIKE 'G.Skill Ripjaws _ Series'
Spinner
DataFrameas
df12
variable
-- NOT LIKE
-- return all non-Asus products

SELECT 
	PRODUCT_ID
	, PRODUCT_NAME
FROM PRODUCTS
WHERE PRODUCT_NAME NOT LIKE 'Asus%'
‌
‌
‌