Introduction to SQL
This is an introductory course designed to give students an intermediate proficiency in SQL. By the end of this course, students will understand the structure of databases and be able to write queries.
Apply Now
Instructor: Brian Jankowitz
I am text block. Click edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.
Objectives
By taking this course you will learn the following skills:
- To understand relational databases
- To retrieve data from a table
- To aggregate and summarize data
- To work with multiple tables
Course Schedule
Week 1: SQL Set Up, Introduction to Databases, Data Types, Data Retrieval, Filtering Data
Content:
- SQL Set Up
- Overview of Databases
- Data models, relationships, columns, and schemas
- Data Types
- Review different systems
- SQL Syntax
- Best practices for readable queries
- SELECT and FROM
- How DISTINCT is used
- Filtering Data WHERE (AND, OR, NOT)
- Comparison operators (=, <, >, <=, >=, <>)
- BETWEEN, IN, and Like
Application:
- To understand databases, be able to pull data from databases, and use comparison operators
Week 2: Sorting and Limiting Data, Aggregation, Joins
Content:
- Review SQL from previous class
- Quiz 1
- ORDER BY and LIMIT
- Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)
- ROUND
- GROUP BY
- HAVING
- Data cleaning
- How to handle outliers
- Missing data (how to approach this, data exploration)
- Practice Questions
Application:
- To perform calculations on rows and by groups
Week 3: Joins, If Statements
Content:
- Review SQL from previous class
- Quiz 2
- Overview of table relationships
- INNER JOIN, LEFT JOIN, OUTER JOIN, CROSS JOINS
- IFNULL, CASE, and COALESCE (Review differences)
- Practice Questions
Application:
- To combine data from multiple tables, format numerical values, and write conditional statements
Week 4: Data Manipulation
Content:
- Review SQL from previous class
- Quiz 3
- Subqueries
- Overview of Temporary Tables
- WITH
- Overview of Views
- Overview of Query Optimization
- Practice Questions
Application:
- To nest queries within other queries and provide an overview of query optimization to improve performance
Week 5: Exploratory Analysis
Content:
- Graphing in Matplotlib/Seaborn
- Retrieving summary statistics
- Hypothesis testing using SciPy
- Modeling in Statsmodels API
- Detecting outliers
Application:
- Understanding claim distributions
- Describing policyholder characteristics
- Identifying impact of new policies
- Model assumption validation
- Anomaly/novelty detection
Week 6: Data Collection
Content:
- Data storage and retrieval
- Fetching web-page data using web-scraping w/ BeautifulSoup
- Handling pagination and dynamic content
- Handling errors and exceptions
- Storing raw data into a readable file
Application:
- Gathering and formatting data from online sources into a usable file in Python
- Performing a meta-analysis using multiple sources of data
Week 7: Automation
Content:
- Manual vs. automated workflows
- Report generation
- Scheduling tasks
Application:
- Automating the preparation of policyholder reports
- Updating data
- Cleaning and preprocessing claims data
- Generating projections or charts for presentations
Week 8: Advanced Visualization
Content:
- Introduction to Plotly
- User interfaces (UI)
- Basics of dashboards:
- Graphs and charts
- Filter
- Dropdown menus
Application:
- Creating interactive charts and graphs
- Organizing a deliverable result to a relevant stakeholder
- Combining multiple graphs and content into a cohesive layout
Final Project
At the conclusion of the course there will be an assigned project using the 2024 U.S. Life Tables from the CDC where students will perform a mortality analysis in Python.
Prerequisites:
Students should have at least a basic understanding of the following topics and exams.
- Familiarity with basic arithmetic operations
Required Software/Operating System:
- Mac or Windows Computer
- MYSQL
- Slack
Join Computer Programming for Actuaries & Data Scientists and advance your computer skills to a level that will make you a hot commodity in the Actuarial Science and Data Science job markets.
REGISTER
