Notes taken by Horeb S.

Links

🔗 Link of the video

🔗 Link of an interesting website for SQL commands

Table of contents

Here, we’ll cover essential SQL concepts and practical examples to help refresh our knowledge of database querying. This guide will serve as a quick reference for common SQL operations and best practices.

To do so, we will use the Taxi Zone Lookup Data 🔗 here.

We first download it and then ingest it on the database. The code is in our notebook file. It looks like that :

image.png

Now, we’ll restart the pgAdmin and Postgres containers in order to make some SQL queries. and gradually move to more complex queries. We’ll work with both tables : yellow_taxi_data and zones.

Some basic queries

View the first 100 lines of yellow_taxi_data

SELECT *
FROM yellow_taxi_data
LIMIT 100;

This query will display all columns and the first 100 rows from our yellow_taxi_data table, giving us a good overview of the data structure and content.

Joining tables

Now, we want to do a join with the location table and we want to display the actual name, the district of the location, instead of seeing the numbers. The first way :

SELECT 
	tpep_pickup_datetime,
	tpep_dropoff_datetime,
	total_amount,
	CONCAT(zpu."Borough", ' / ', zpu."Zone") AS "pickup_loc",
	CONCAT(zdo."Borough", ' / ', zdo."Zone") AS "pickup_loc"
FROM 
	yellow_taxi_data t ,
	zones zpu ,
	zones zdo
WHERE 
	t."PULocationID" = zpu."LocationID" AND
	t."DOLocationID" = zdo."LocationID"
LIMIT 100