# 1.2.6 - SQL Refresher

Youtube Video | \~16min

{% embed url="<https://www.youtube.com/watch?v=QEcps_iskgg&list=PL3MmuxUbc_hJed7dXYoJw8DoCuVHhGEQb&index=10&pp=iAQB>" %}
<https://www.youtube.com/watch?v=QEcps_iskgg&list=PL3MmuxUbc_hJed7dXYoJw8DoCuVHhGEQb&index=10&pp=iAQB>
{% endembed %}

:writing\_hand: In this video we go through a SQL refresher with a focus on JOIN and GROUP BY. This example using the yellow\_taxi\_data and zones is helpful for the homework set.

#### SQL Code difference between using JOIN vs. WHERE&#x20;

{% tabs %}
{% tab title=" JOIN" %}

```sql
SELECT 
	tpep_pickup_datetime, 
	tpep_dropoff_datetime, 
	total_amount, 
	CONCAT(zpu."Borough", ' / ', zpu."Zone") AS "pick_up_loc",
	CONCAT(zdo."Borough", ' / ', zdo."Zone") AS "drop_off_loc"
FROM 
	yellow_taxi_data t JOIN zones zpu 
		ON t."PULocationID"=zpu."LocationID" 
	JOIN zones zdo 
		ON t."DOLocationID"=zdo."LocationID"
LIMIT 100;
```

{% endtab %}

{% tab title="WHERE" %}

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

{% endtab %}
{% endtabs %}

#### GROUP BY 1 column vs. multi-column

{% tabs %}
{% tab title="One" %}

```sql
SELECT 
	CAST(tpep_dropoff_datetime AS DATE) as "day", 
	COUNT(1) as "count",
	MAX(total_amount),
	MAX(passenger_count)
FROM 
	yellow_taxi_data t 
GROUP BY
	CAST(tpep_dropoff_datetime AS DATE)
ORDER BY "count" DESC;
```

{% endtab %}

{% tab title="Multi" %}

```sql
SELECT 
	CAST(tpep_dropoff_datetime AS DATE) as "day", 
	"DOLocationID",
	COUNT(1) as "count",
	MAX(total_amount),
	MAX(passenger_count)
FROM 
	yellow_taxi_data t 
GROUP BY
	1, 2
ORDER BY "day" ASC, "DOLocationID" ASC;
```

{% endtab %}
{% endtabs %}

### Resources

:link: Use an app like [Mimo](https://mimo.org/) to review SQL or Python basics


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://data-engineering-zoomcamp-2025-t.gitbook.io/tinker0425/module-1/1.2-docker-and-docker-compose/1.2.6-sql-refresher.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.