Title: CS 304 Joins activities

# Joins

Today we'll learn about queries involving multiple tables using *joins*.

## Plan

1. (15) recap
1. (15) quiz questions
1. (5) setup (together)
    * launch VS Code
    * start remote terminal
    * run mysql client
    * copy today's files
1. (20) Group work
1. (5) Class Discussion (together)
1. (15) Continue group work

[TOC]

## Announcements

* Please do the courseintros.com if you haven't;
* Try to come visit me in OH
* OH are *either* on zoom or in person, whichever you prefer, but I like in person when possible.
* SSH Keys reading for tomorrow

## Today's Partners

* 2 Chloe+Winnie
* 4 Ashley You+Makana
* 6 Sana+Yuneydy
* 8 Ali+Claire
* 10    Fangzhong+Sophia
* 12    Flora+Linh
* 14    Diya+Sophie
* 16    Malinda+Vaishu
* 18    Hannah+Jasmine
* 20    Ariel+Mukhlisa
* 22    Bella+Maddie
* 24    Ashley Cong+Mira
* 26    Daisy+Kathy
* 28    Anjali+Maya
* 30    Indira+Nicole


## Last Time

Last time, we learned a few things about:

* using the MySQL client
* running batch files
* editing batch files
* writing queries

Questions on any of that?

## Comfort

A lot of you aren't feeling comfortable with this material yet, which
is fine. Let's take some time to review the reading

* primary keys
* foreign keys
* owners and pets
* wmdb
    * movie directors
    * cast
* joins as a subset of a cross product
* self-crosses: pairs from the same set
    * pairs of people
    * pairs of pets
    * pairs of movies
    * pairs of cars
* special syntax

[joins](../../readings/joins/)


## Quiz Questions about Joins

Let's review question 3, which was a tough one:

```
We might JOIN the Movie and Staff tables on addedby=uid

    A. to find movies added by a particular staff member
    B. to find what staff member added a given movie
    C. either A or B
    D. none of the above
```

Let's try both of the following queries:

```sql
select * 
from movie inner join staff on (addedby=uid)
where staff.name = 'Scott D. Anderson';
```

That shows the movies that I added. 

Now, let's try the following query:

```sql
select * 
from movie inner join staff on (addedby=uid)
where movie.title = 'Knives Out';
```

That shows who added "Knives Out" (excellent movie, filmed near here!).

So, the answer is C: either A or B

I'll address your [quiz questions](../../quizzes/quiz03.html)

## Counting Results

Here's a nice trick. Instead of 

```sql
use wmdb;

select * from person;
```

You can do:

```sql
use wmdb;

select count(*) from person;
```

The latter just tells you how many there are. 

Try:

```sql
use wmdb;
select count(*) from person;
```

and 

```sql
use wmdb;
select count(*) from person A, person B;
```

and 

```sql
select 1829*1829;
```

## Code for Today

You can get started by copying the files for today like this:

```shell
cd ~/cs304
cp -r ~cs304flask/pub/downloads/mysql2-joins mysql2-joins
cd mysql2-joins
```

(You can also use `.` for the second argument of the `cp` command,
which will save you some typing. I didn't use it above because
students often overlook it. It turns out that you can also copy/paste
that block of code, though I think it's useful to learn to type the
commands.)

## Breakout 1

Chat with your partner.

There are many examples in this folder. Explore and experiment:

* `director1.sql` shows one way to find all movies and their directors
* `director2.sql` shows movies directed by Kathryn Bigelow and Peter Jackson
* `flavor-pairs1.sql` shows pairs of ice cream flavors
* `credits1.sql`, `credits2.sql` and `credits3.sql` show variations on the Hepburn & Stewart example

Also, start up MySQL and look at the `credit` table in the `wmdb`
database. Do those entries make sense?

## Exercise

Try writing a variant of the `flavor-pairs1.sql` batch file that
avoids any repeated flavors: no chocolate-chocolate pairs, for example.

One possible solution has repeated pairs: both chocolate-vanilla and
vanilla-chocolate.  Maybe that's okay, but maybe not. Write a batch
file that avoids those.

## Discussion

With your partner, discuss how we might handle the many-many
relationship between students and courses: each student takes multiple
courses and each course has multiple students enrolled.

For example: Hermione is taking ['Arithmancy', 'Potions', 'Transfiguration' ...]

## Additional Options

How might you find *triples* of ice cream flavors? How would you avoid
different kinds of repeats?

How could you write a WMDB query that finds "May-December" pairs of people:
one person is born in May and the other in December.

How many pairs of people are there *without* repeats? Should be N
choose 2, right? Can you confirm that using SQL?

## Together

We'll come together and discuss these examples and exercises.

## The LIKE Operator

Let's look ahead at a topic from next time, namely the LIKE
operator. We'll also look at DISTINCT.

## Activities

You can spend the rest of class continuing with this work, above,
chatting among yourselves, or working on homework.


## Solutions
<script>revealAt = '9/10/2024 17:00';</script>

<div markdown=1 class="hidden_from_student">
The following has only 3 results, not 6.

```sql
use cs304_db;

select A.name as 'scoop1', B.name as 'scoop2' 
from flavor A, flavor B
where A.name < B.name;
```

The following finds May-December pairs of people:

```sql
use wmdb;

select A.name as 'May Person', B.name as 'Dec Person'
from person A, person B
where A.nm <> B.nm and
      month(A.birthdate) = 5 and 
      month(B.birthdate) = 12;
```

How about this:

```sql
use wmdb;
select count(*) from person A, person B where A.nm < B.nm;
```

While N choose 2 is N*(N-1)/2. If N=2200, we get:

```sql
select 2200 * (2200-1) / 2;
```

</div>