This video is still being processed. Please check back later and refresh the page.

Uh oh! Something went wrong, please try again.

Introduction to SQL

Querying relational databases using Python.

rate limit

Code not recognized.

About this course

SQL (which stands for “structured query language”) is the lingua franca of data across software development, database technologies, and data analytics. As a data science professional, having the means to access data is arguably one of the first practical skills that can be obtained, and this is exactly what SQL provides. Rather than waiting on others to provide a CSV containing data you need, you can perform self-service and send SQL queries directly to your organization’s databases… right within your Python environment!

In this course we will cover SQL fundamental concepts for querying and writing data in relational databases using Python and SQLite. From the basic SELECT query to principles of database design, you can take this knowledge to your favorite database platforms including Oracle, Microsoft SQL Server, PostgreSQL, Apache Spark, and many others that follow the ANSI SQL standard.

What you'll learn—and how you can apply it

By the end of this hands-on course, you’ll understand:

  • The principles behind relational databases and their effectiveness
  • Common JOIN patterns including INNER JOIN and LEFT JOIN
  • How SQL is an effective, and even necessary, part of the data science stack

And you'll be able to:

  • Perform common SQL querying operations like SELECT, WHERE, GROUP BY INNER JOIN, LEFT JOIN, subqueries, and common table expressions
  • Use built-in features with Python and pandas to execute SQL queries easily and integrate them into your application
  • Confidently design relational databases and transform data efficiently on database servers, rather than your local machine

This training is for you because...

  • You’re a data science professional wanting to tap directly into data sources.
  • You work with a software or database engineering group that shares data through a relational database.
  • You want to become a data engineer or data scientist with true self-sufficiency in acquiring data.

Prerequisites

Setup

To follow along using your desktop IDE:

  1. Install or update to the latest version of Anaconda
  2. Launch your command line tool and configure your conda environment

For macOS and Linux users: Search and launch Terminal in your system

For Windows users: Locate and launch Anaconda Prompt in your system

3. (Optional but recommended) From the command line, run the following prompts to create and activate a new environment

conda create --name NEW_ENV_NAME

conda activate NEW_ENV_NAME 

4. Install required packages in the command line

conda install sqlite3 pandas urllib.request

5. Launch JupyterLab from the command line

jupyter lab

To open Anaconda Notebooks:

  1. Go to https://anaconda.cloud
  2. Click on 'Notebooks' from the top navigation menu
  3. Create an account or login if you already have one

The Notebooks for this course are also available at this public GitHub link

Facilitator Bio

Thomas Nield is the founder of Nield Consulting Group and Yawman Flight, as well as an instructor at University of Southern California. He enjoys making technical content relatable and relevant to those unfamiliar or intimidated by it. Thomas regularly teaches classes on data analysis, machine learning, mathematical optimization, and practical artificial intelligence. At USC, he teaches AI System Safety, developing systematic approaches for identifying AI-related hazards in aviation and ground vehicles. He's authored three books, including Essential Math for Data Science (O’Reilly) and Getting Started with SQL (O'Reilly).

He is also the founder and inventor of Yawman Flight, a company developing universal handheld flight controls for flight simulation and unmanned aerial vehicles. You can find him on Twitter | LinkedIn | GitHub | YouTube.

Questions? Issues? Join our Community page to get help.

Curriculum02:14:37

  • Getting Started with Anaconda Notebooks 00:01:02
  • Overview
  • Overview and Setup 00:03:42
  • Connecting to a Database 00:07:06
  • Pulling data with SELECT
  • Preview
    Pulling Data with SELECT 00:05:05
  • Expressions and Functions + Exercise 00:08:06
  • Filtering data with WHERE
  • Filtering Data with WHERE 00:09:35
  • Filtering Text Expressions 00:08:57
  • Exercise: Filtering data with WHERE 00:01:49
  • Aggregating and sorting
  • Aggregating and Sorting 00:07:07
  • Counting Records + Exercise 00:05:33
  • Nulls and Case Expressions
  • Nulls, CASE Expressions 00:10:11
  • The NULL Case Trick + Exercise 00:07:06
  • INNER JOIN and LEFT JOIN
  • INNER JOIN 00:07:21
  • LEFT JOIN 00:09:42
  • Exercise: Joins 00:02:19
  • Subqueries and Common Table Expressions
  • Subqueries 00:09:17
  • Common Table Expressions (CTEs) + Exercise 00:05:26
  • Creating Tables and Writing Data
  • Creating Tables 00:07:01
  • Creating and Dropping Tables 00:10:11
  • Transactions + Exercise 00:05:32
  • Conclusion 00:02:29
  • End of Course Survey
  • Course Completion

About this course

SQL (which stands for “structured query language”) is the lingua franca of data across software development, database technologies, and data analytics. As a data science professional, having the means to access data is arguably one of the first practical skills that can be obtained, and this is exactly what SQL provides. Rather than waiting on others to provide a CSV containing data you need, you can perform self-service and send SQL queries directly to your organization’s databases… right within your Python environment!

In this course we will cover SQL fundamental concepts for querying and writing data in relational databases using Python and SQLite. From the basic SELECT query to principles of database design, you can take this knowledge to your favorite database platforms including Oracle, Microsoft SQL Server, PostgreSQL, Apache Spark, and many others that follow the ANSI SQL standard.

What you'll learn—and how you can apply it

By the end of this hands-on course, you’ll understand:

  • The principles behind relational databases and their effectiveness
  • Common JOIN patterns including INNER JOIN and LEFT JOIN
  • How SQL is an effective, and even necessary, part of the data science stack

And you'll be able to:

  • Perform common SQL querying operations like SELECT, WHERE, GROUP BY INNER JOIN, LEFT JOIN, subqueries, and common table expressions
  • Use built-in features with Python and pandas to execute SQL queries easily and integrate them into your application
  • Confidently design relational databases and transform data efficiently on database servers, rather than your local machine

This training is for you because...

  • You’re a data science professional wanting to tap directly into data sources.
  • You work with a software or database engineering group that shares data through a relational database.
  • You want to become a data engineer or data scientist with true self-sufficiency in acquiring data.

Prerequisites

Setup

To follow along using your desktop IDE:

  1. Install or update to the latest version of Anaconda
  2. Launch your command line tool and configure your conda environment

For macOS and Linux users: Search and launch Terminal in your system

For Windows users: Locate and launch Anaconda Prompt in your system

3. (Optional but recommended) From the command line, run the following prompts to create and activate a new environment

conda create --name NEW_ENV_NAME

conda activate NEW_ENV_NAME 

4. Install required packages in the command line

conda install sqlite3 pandas urllib.request

5. Launch JupyterLab from the command line

jupyter lab

To open Anaconda Notebooks:

  1. Go to https://anaconda.cloud
  2. Click on 'Notebooks' from the top navigation menu
  3. Create an account or login if you already have one

The Notebooks for this course are also available at this public GitHub link

Facilitator Bio

Thomas Nield is the founder of Nield Consulting Group and Yawman Flight, as well as an instructor at University of Southern California. He enjoys making technical content relatable and relevant to those unfamiliar or intimidated by it. Thomas regularly teaches classes on data analysis, machine learning, mathematical optimization, and practical artificial intelligence. At USC, he teaches AI System Safety, developing systematic approaches for identifying AI-related hazards in aviation and ground vehicles. He's authored three books, including Essential Math for Data Science (O’Reilly) and Getting Started with SQL (O'Reilly).

He is also the founder and inventor of Yawman Flight, a company developing universal handheld flight controls for flight simulation and unmanned aerial vehicles. You can find him on Twitter | LinkedIn | GitHub | YouTube.

Questions? Issues? Join our Community page to get help.

Curriculum02:14:37

  • Getting Started with Anaconda Notebooks 00:01:02
  • Overview
  • Overview and Setup 00:03:42
  • Connecting to a Database 00:07:06
  • Pulling data with SELECT
  • Preview
    Pulling Data with SELECT 00:05:05
  • Expressions and Functions + Exercise 00:08:06
  • Filtering data with WHERE
  • Filtering Data with WHERE 00:09:35
  • Filtering Text Expressions 00:08:57
  • Exercise: Filtering data with WHERE 00:01:49
  • Aggregating and sorting
  • Aggregating and Sorting 00:07:07
  • Counting Records + Exercise 00:05:33
  • Nulls and Case Expressions
  • Nulls, CASE Expressions 00:10:11
  • The NULL Case Trick + Exercise 00:07:06
  • INNER JOIN and LEFT JOIN
  • INNER JOIN 00:07:21
  • LEFT JOIN 00:09:42
  • Exercise: Joins 00:02:19
  • Subqueries and Common Table Expressions
  • Subqueries 00:09:17
  • Common Table Expressions (CTEs) + Exercise 00:05:26
  • Creating Tables and Writing Data
  • Creating Tables 00:07:01
  • Creating and Dropping Tables 00:10:11
  • Transactions + Exercise 00:05:32
  • Conclusion 00:02:29
  • End of Course Survey
  • Course Completion