Lecture 20 - SQL: Multi-Table Queries (JOINS)

Starting with a Quick Followup on PHP/PDO

Inserting through PHP


$count = $db->exec("INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
                    VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00);");

PHP

exec runs the given SQL code and returns the number of rows affected.

Exceptions for PDO Errors


          $db = new PDO("mysql:dbname=imdb_small", "jessica", "guiness");
          $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          $rows = $db->query("SEEELECT * FROM movies WHERE year = 2000");
                             # kaboom!

PHP

Using setAttribute, you can tell PDO to throw (generate) a PDOException when an error occurs.

The exceptions will appear as error messages on the page output.

You can catch the exception to gracefully handle the error.

SQL So Far

Database querying

Database manipulation (create/insert)

Accessing SQL from PHP with the PDO object

Multi-Table Queries (JOINS)

joins

Related Tables and Keys

table keys

  • Primary Key: a column guaranteed to be unique for each record (e.g. Lisa Simpson's ID 888)
  • Foreign Key: a column in Table A storing a primary key from table B
    • (e.g. records with grades with student_id)
  • Normalizing: Splitting tables to improve structure/redundancy (linked by unique IDs)
    • This is covered more in depth in database courses (e.g. CSE 344 (CSE majors) and CSE 414 (non-CSE-majors))

JOIN Template


          SELECT col(s)
          FROM table1, table2, ...
          WHERE table1.a = table2.b
          AND table2.c > '42';

SQL (template)


          SELECT students.name, grades.*
          FROM students, grades
          WHERE students.id = grades.student_id
          AND grades.grade > 'C';

SQL (example)

join example

Result table

Giving Names to Tables


          SELECT students.name, grades.*
          FROM students, grades
          WHERE students.id = grades.student_id
          AND grades.grade > 'C';

SQL (example from previous slide)

A more compact solution (giving variable names to tables)


          SELECT s.name, g.*
          FROM students s, grades g
          WHERE s.id = g.student_id
          AND g.grade > 'C';

SQL (alternative example)

  • You can give names to tables, like a variable in Java.
  • To specify all columns in a table, write table.*

Note: An Equivalent Way to JOIN Tables


          SELECT s.name, g.*
          FROM students s, grades g
          WHERE s.id = g.student_id
          AND g.grade > 'C';

SQL (example from previous slide)


          SELECT s.name, g.*
          FROM students s
          JOIN grades g ON s.id = g.student_id
          WHERE g.grade > 'C';

SQL (alternative example, using the JOIN keyword)

The JOIN keyword is another way to join multiple tables. Some people find this more intuitive, while others find joining tables on multiple WHERE conditions more intuitive. Use whichever form you prefer most!

Note: There are other types of JOINS you may find on the recommended SQL query practice sites. You may learn these for fun if you'd like, but we don't cover it in this class (you will learn about them in other database courses). The JOIN keyword in this slide's example is also known as an "INNER JOIN"

A Suboptimal Query

Exercise: What courses have been taken by both Bart and Lisa?


          SELECT bart.course_id
          FROM grades bart, grades lisa
          WHERE lisa.course_id = bart.course_id
          AND bart.student_id = 123
          AND lisa.student_id = 888;

SQL

What's wrong here?

Improved Query

What courses have been taken by both Bart and Lisa?


          SELECT DISTINCT c.name
          FROM courses c, students lisa, students bart, grades g1, grades g2
          WHERE g1.course_id = c.id 
          AND g1.student_id = bart.id
          AND g2.course_id = c.id 
          AND g2.student_id = lisa.id
          AND bart.name = 'Bart'
          AND lisa.name = 'Lisa';

Why so many tables?

Practice Queries

What are the names of all teachers Bart has had?

How many total students has Ms. Krabappel taught, and what are their names?

Designing a Query (Refer to this slide often!)

Figure out the proper SQL queries in the following way:

  • Which table(s) contain the critical data? (FROM)
  • Which columns to I need in the result set? (SELECT)
  • How are tables connected (JOIN and/or WHERE) and values filtered (WHERE)?
  • Do I need to return only DISTINCT records?
  • Do I care about the order of records returned? If so, which columns do I need to sort by and in what precedence?

Example Database (also on query tester)

imdb schema

  • imdb-small is also provided on the query tester page for testing queries with a smaller dataset

IMDB Table Relationships/ids

imdb schema

IMDB Practice Queries

What are the names of all movies released in 1995?

How many people played a part in the movie "Lost in Translation"?

What are the names of all the people who played a part in the movie "Lost in Translation"?

Who directed the movie "Fight Club"?

How many movies has Clint Eastwood directed?

What are the names of all movies Clint Eastwood has directed?

What are the names of all directors who have directed at least one horror film?

What are the names of every actor who has appeared in a movie directed by Christopher Nolan?

Practice at Home

Tips for practicing SQl for your final HW and the exam:

  • Write a simple create.sql file, creating a few tables of your choice.
  • Use INSERT statements to populate these tables with different data (as Kyle did for his todolist example on Friday
  • In Cloud9, start mysql and use source create.sql to create your tables in your mysql database.
  • Write a PHP file that sets up a PDO object, and then executes a few more INSERT statements on your tables to add a few more records
  • Write a simple SELECT query in your PHP file
  • Now use JOIN to join your tables based on criteria you come up with (e.g. make your own query questions!)

This exercise is actually not too long, but will be very helpful for HW7 and your creative projects (and could be a good piece in your software development portfolio