SQL Queries¶

Our topic for today is the mechanics of working with MySQL client (the mysql command) and SQL queries.

Plan¶

1. (5) Announcements and such
2. (30) quiz questions
3. (10) setup (together)
   • launch VS Code
   • start remote terminal
   • run mysql client
   • copy today's files
4. (20) Pair work
5. (10) Break (together)
   • assess -- how did that work?
   • talk about names
6. (5) Continue group work
   • write some batch files

Announcements¶

• I still need to learn about the integration of Gradescope and Brightspace. Please be patient.
• I'll be sending out the introduction video soon; please do over the weekend
• Next Wednesday, I'll help you set up SSH Keys, or you can just work on the queries assignment
• If you're not a member of CS-Students Google Group, you should join.

Old note: Lasst year I changed the account for this course from cs304 to cs304flask, but there may still be some places (in directions for exercises and assignments) where the old account name is still there. So, if something says cs304 and it looks like it might be an account name (e.g. ~cs304 in a pathname), and it doesn't work, try the other. And let me know!

Roll Call¶

• Gotta do this.

Partners for Today¶

I think it will be useful to get to know some different people, so I'm going to randomly assign partners just for today. Please

Quiz Questions¶

By the way, the quiz questions are a "safe place." While in principle I can see who asks each question, I usually just copy/paste the questions into a file for rearranging and answering, leaving the identity of the student behind. So feel free to ask questions that you are afraid might seem foolish.

I'll address your Unix questions

and then

I'll address your MySQL quiz questions

Start¶

1. Launch VS Code and
2. start a remote terminal

We did this last time. If you have trouble with this, take a look back. You'll get the hang of it very soon.

Running MySQL client¶

We'll do these together.

Try the mysql command to start the MySQL client (connects to MySQL server). Substitute your own database for the your_db;

$ mysql
mysql> help
mysql> select user();
mysql> select database();
mysql> show databases;
mysql> use wmdb;
mysql> show tables;
mysql> use your_db;
mysql> show tables;
mysql> quit

Code for Today¶

You can get started by copying the files for today like this. Notice the dot that is the destination for the cp command. That means "current directory" or just "here". It's a convenient shorthand, but easy to overlook.

cd ~/cs304
cp -r ~cs304flask/pub/downloads/mysql1-queries .

I'll demo some of these, just so people feel more comfortable, but you should do them too.

$ cd mysql1-queries
$ ls
$ more wmdb1-all.sql
$ mysql < wmdb1-all.sql
$ mysql
mysql> source wmdb1-all.sql
mysql> quit

I'll also demo:

• copying a batch file
• modifying it with VS Code using the code command
• saving it
• running the modified file

Editing Batch Files¶

Try editing a batch file to be just a little different

• use the cp command to copy the file, say wmdb1-all.sql to another file, say batch1.sql
• edit the file using VS Code. The code command do this:

code batch1.sql

• edit the file, say to put the columns in a different order or change the limit
• save the file
• run it via the terminal

(20 minutes) Work with your Partner¶

• Introduce yourself to your partner. Get acquainted. Don't just get to work.
• Work through some of these activities, talking out loud

What do to:

• Look in the other batch files at the different WHERE clauses.
• Notice that there is a month() function that extracts the month number from a date. Similarly, there is a year() function. MySQL has lots of such functions.
• discuss these and make sure they are clear to you
• try variations on those, possibly saving the variations to different files

With your partner, write batch files to find

• people born in May of 1961
• people born in 1961 or 1962
• people born in May or June of 1961
• people born in 1961 but not in December

It's probably better to put each of these in its own batch file, suitably named.

Let's discuss the "May or June of 1961" clause:

SELECT nm,name,birthdate
FROM person
WHERE month(birthdate) = 5 or 
      month(birthdate) = 6 and
      year(birthdate) = 1961;

Names are not Unique¶

• I could tell you stories of my name, and I will tell at least one
• IMDB.com lists 3 people named "Mary Moore" (it adds roman numerals)
• Mary Tyler Moore (famous actress from long ago) added her middle name because "there were half a dozen other Mary Moores registered" with the Screen Actors Guild
• If WMDB had multiple people named "George Clooney", all would be reported.
• We'll see examples of multiple matches very soon.
• That's why the IMDB and the WMDB use an ID for each person, which is the NM value. George Clooney is 123.

Homework¶

You're not ready for all parts of the homework yet, but you can start on the first few queries, or you can just play around with MySQL. You'll be ready for more after our next meeting, when we learn about joins.

Finishing Up¶

When you're done, Quit from MySQL, EXIT from the terminal, disconnect from the SERVER, and Quit VSC. With keyboard shortcuts:

1. ^d to quit MySQL
2. ^d to exit terminal
3. click on green icon and choose "Close Remote Connection"
4. command-Q to quit VSC

After Class¶

If you haven't already, please look for emails from me

• office hours
• course introductions