Lecture 2 - (03/02/2026)

Today’s Topics:

• SQL + Python

• Introduction to SQL and Relational Databases

• SQL Visualization

Bridging SQL and Python¶

We can start by creating a connection to the database. The code below doesn’t actually connect just yet – we’re just creating the engine with which we will connect to the database.

We can use our engine to write tables to a database. We do this using the to_sql Data Frame method in pandas. Let’s try this out by putting a new state’s data into our lodes.db database.

SQL by default lowercases everything, so if we import a database with uppercase column names, we will need to use quotes to access everything, to save ourselves the headache:

Here, to_sql() takes the Data Frame and puts it into the database in the connection conn, naming it as the value given in the first argument (in this case, we named it md_wac_2015. The last argument, if_exists='replace', makes it so that we replace the table if it exists already.

Some other fun datasets to try yourself!

• Pokemon

• Marvel

• UFO Sightings

• Spotify most popular songs

Introduction to SQL and Relational Databases¶

Python is very useful and powerful for doing data analysis. However, it is not meant to be used with extremely large datasets, or to be used as a method of storing data. This is where SQL and relational databases come in. Today we will explore how relational databases work to store data, and how to use SQL to organize and extract the data that we want.

The LODES dataset stands for LEHD Origin-Destination Employment Statistics, produced by the U.S. Census Bureau.

• Tracks employment flows

  • Where people live (residence)

  • Where people work (workplace)

• Covers all jobs covered by unemployment insurance in the USA

We want to explore this, so that we can characterize the data that is available to us. Namely:

• How many census blocks contain workplaces?

• What were the most jobs in a census block?

• How many census blocks had over 50 jobs? Over 100?

• Among census blocks containing worplaces, what is the average number of jobs per census block?

Relational Databases¶

SQL is a language designed for a very specific purpose: to interact with relational databases.

• Database: A database is a structured collection of data. There are various different ways of structuring the database, and there may or may not be information about the relationship between entities in the database.

• Query: A query is a request for data from the database.

• Database Management System (DBMS): A DBMS is a system of storing and managing databases, including querying the database.

• Relational Database Management System (RDBMS): In an RDBMS, data records are stored in tables, each of which has a predefined set of columns, the pieces of information captured for each record in a table, and rows in the table, where each row has a place to store a value for every column in the table.

Tables, including their columns, column types and relationships with other tables, are defined in a database schema. Many times, tables will contain a primary key, one or more columns that uniquely define a row. You can think of the primary key as a kind of ID, in which each row is given a unique ID. Tables can also contain foreign keys, which are column(s) that comprise the primary key in another table and, thus, provides a way of matching between multiple tables.

Now, let’s start with some basics. We’ll start by retrieving all columns from the California Workplace Area Characteristic (ca_wac_2015) table. Try running the following query:

This gave us the entire database! Now let’s try to make more specific requests. To do so, we’ll need to go over the basics of a SQL command:

• SELECT: We start out with the SELECT statement. The SELECT statement specifies which variables (columns) you want.

  • Here, we used SELECT *. The * just says that we want all the variables.

  • If we wanted a few columns, we would use the column names separated by commas instead of * (for example, w_geocode, createdate).

• FROM: Now, let’s look at the next part of the query, FROM md_wac_2015. This part of the query specifies the table, ca_wac_2015, from which we want to retrieve the data. Most of your queries will begin in this fashion, describing which columns you want and from which table.

• LIMIT: We typically include a LIMIT statement at the end of our query so that we don’t get overloaded with rows being output. Here, LIMIT 10 means that we just want the first ten rows. Many times, the LIMIT that you want will be higher than 10 – you might generally prefer to use 1000 or so. Having a LIMIT for all queries is highly recommended even if you know only a few rows will be shown, since it acts as a safety precaution against (for example) displaying millions of rows of data.

You may have notived that we chose to use all caps for the commands and lowercase for the data table and schema names. This is just a convention, as SQL is not case sensitive, for example select * from ca_wac_2015 limit 10 would work just as fine.

Try it yourself!

Go to “https://www.sql-practice.com/” and try to get the first 10 patient ids from the patient database.

Checking for duplicates¶

Let’s say we want to find out how many rows there are. You can do this by using a COUNT.

Here, we used COUNT(*), which does a count of all rows, regardless of NULL values. We can instead do a count of all non-NULL values of a certain variable by including that variable instead of *.

We can also look at both of these at the same time. This can be helpful for checking for NULL values of multiple columns at a time, since we’ll be able to see which ones have less than the total number of rows.

While this does verify that there are no NULL values, we don’t know if there are duplicates. We can verify by using DISTINCT

This shows all the distinct values of w_geocode, now we can count to check how many distinct values there are

Since the count is the same as the number of distinct values, there should be no duplicates in our database.

Also, by chaining together multiple commands we were able to build up a query. When writing a query, start by thinking about the basic parts, and feel free to run any intermediate steps as necessary.

Try it yourself!

Go to “https://www.sql-practice.com/” and try to figure out how many unique allergies there are in the patients dataset.

Using Conditional Statements¶

Suppose we want to look at a subset of the data. We can use the WHERE statement to create conditional statements to do this.

We can also use more complicated conditional statements.

This query finds all the values where c000 is less than 10 and ca01 is equal to 1 and takes a count of it.

Using OR behaves the same way

Common Comparison Operators¶

Though there are some more complicated comparison operators (if you’re curious, feel free to look up what LIKE and IN do), these should cover most of what you want to do.

• =: equal to

• != or “<>”: not equal to

• <: less than

• <=: less-than-or-equal-to

• >: greater than

• >=: greater-than-or-equal-to

• IS NULL and IS NOT NULL: The signifier of a row in a column not having a value is a special keyword: NULL. To check for NULL, you use IS NULL or IS NOT NULL, rather than “=” or “!=”. For example, to count the number of rows with NULL values for c000 we might use the following:

Try it yourself!

Go to “https://www.sql-practice.com/” and try to figure out how many people are allergic to “Penicillin” or “Honey”

SQL Visualization¶