CSE 190M Web Programming

Lecture 12: More SQL, JOINS

Reading: 13.2 - 13.5

Except where otherwise noted, the contents of this document are Copyright 2012 Marty Stepp, Jessica Miller, Victoria Kirst and Roy McElmurry IV. All rights reserved. Any redistribution, reproduction, transmission, or storage of part or all of the contents in any form is prohibited without the author's expressed written permission.

Valid HTML5 Valid CSS

13.3: Multi-table Queries

Related tables and keys

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 190M9012
10004Informatics 1001234
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+
teachers
idname
1234Krabappel
5678Hoover
9012Stepp

Querying multi-table databases

When we have larger datasets spread across multiple tables, we need queries that can answer high-level questions such as:

To do this, we'll have to join data from several tables in our SQL queries.

Exercise: Multi-table query

  • Use your existing SQL knowledge to determine how well Bart did in CSE 142.

Cross product with JOIN

SELECT column(s) FROM table1 JOIN table2;
SELECT * FROM students JOIN grades;
idnameemailstudent_idcourse_idgrade
123Bartbart@fox.com12310001B-
404Ralphralph@fox.com12310001B-
456Milhousemilhouse@fox.com12310001B-
888Lisalisa@fox.com12310001B-
123Bartbart@fox.com12310002C
404Ralphralph@fox.com12310002C
... (24 rows returned)

Joining with ON clauses

SELECT column(s)
FROM table1
JOIN table2 ON condition(s)
...
JOIN tableN ON condition(s);
SELECT *
FROM students
JOIN grades ON id = student_id;

Join example

SELECT *
FROM students
JOIN grades ON id = student_id;
idnameemailstudent_idcourse_idgrade
123Bartbart@fox.com12310001B-
123Bartbart@fox.com12310002C
404Ralphralph@fox.com40410004D+
456Milhousemilhouse@fox.com45610001B+
888Lisalisa@fox.com88810002A+
888Lisalisa@fox.com88810003A+

Filtering columns in a join

SELECT name, course_id, grade
FROM students
JOIN grades ON id = student_id;
namecourse_idgrade
Bart10001B-
Bart10002C
Ralph10004D+
Milhouse10001B+
Lisa10002A+
Lisa10003A+

Filtered join (JOIN with WHERE)

SELECT name, course_id, grade
FROM students
JOIN grades ON id = student_id
WHERE name = 'Bart';
namecourse_idgrade
Bart10001B-
Bart10002C

What's wrong with this?

SELECT name, id, course_id, grade
FROM students
JOIN grades ON id = 123
WHERE id = student_id;
nameidcourse_idgrade
Bart12310001B-
Bart12310002C

Giving names to tables

SELECT s.name, g.*
FROM students s
JOIN grades g ON s.id = g.student_id
WHERE g.grade <= 'C';
namestudent_idcourse_idgrade
Bart12310001B-
Bart12310002C
Milhouse45610001B+
Lisa88810002A+
Lisa88810003A+

Multi-way join

SELECT c.name
FROM courses c
JOIN grades g ON g.course_id = c.id
JOIN students bart ON g.student_id = bart.id
WHERE bart.name = 'Bart' AND g.grade <= 'B-';
name
Computer Science 142

Exercise: Incremental JOIN query

  • Use the same approach as before to determine what courses have been taken by both Bart and Lisa?
    SELECT bart.course_id
    FROM grades bart
    JOIN grades lisa ON lisa.course_id = bart.course_id
    WHERE bart.student_id = 123
    AND lisa.student_id = 888;
    

    problem: requires us to know Bart/Lisa's Student IDs, and only spits back course IDs, not names.

  • Write a version of this query that gets us the course names, and only requires us to know Bart/Lisa's names, not their IDs.

Improved query

Designing a query

Exercise: More JOIN queries

  • What are the names of all teachers Bart has had?
    SELECT DISTINCT t.name
    FROM teachers t
    JOIN courses c ON c.teacher_id = t.id
    JOIN grades g ON g.course_id = c.id
    JOIN students s ON s.id = g.student_id
    WHERE s.name = 'Bart';
    
  • How many total students has Ms. Krabappel taught, and what are their names?
    SELECT DISTINCT s.name
    FROM students s
    JOIN grades g ON s.id = g.student_id
    JOIN courses c ON g.course_id = c.id
    JOIN teachers t ON t.id = c.teacher_id
    WHERE t.name = 'Krabappel';
    

Example imdb database

actors
idfirst_namelast_namegender
433259WilliamShatnerM
797926BritneySpearsF
831289SigourneyWeaverF
...
movies
idnameyearrank
112290Fight Club19998.5
209658Meet the Parents20007
210511Memento20008.7
...
roles
actor_idmovie_idrole
433259313398Capt. James T. Kirk
433259407323Sgt. T.J. Hooker
797926342189Herself
...
movies_genres
movie_idgenre
209658Comedy
313398Action
313398Sci-Fi
...
directors
idfirst_namelast_name
24758DavidFincher
66965JayRoach
72723WilliamShatner
...
movies_directors
director_idmovie_id
24758112290
66965209658
72723313398
...

IMDb table relationships / ids

IMDb tables tree

IMDb query example

[stepp@webster ~]$ mysql -u myusername -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use imdb_small;
Database changed

mysql> select * from actors where first_name like '%mick%';
+--------+------------+-----------+--------+
| id     | first_name | last_name | gender |
+--------+------------+-----------+--------+
|  71699 | Mickey     | Cantwell  | M      |
| 115652 | Mickey     | Dee       | M      |
| 470693 | Mick       | Theo      | M      |
| 716748 | Mickie     | McGowan   | F      |
+--------+------------+-----------+--------+
4 rows in set (0.01 sec)

Exercise: 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?

Learning about databases and tables

SHOW DATABASES;
SHOW TABLES;
DESCRIBE table;
SHOW TABLES;
+-----------+
| students  |
| courses   |
| grades    |
| teachers  |
+-----------+    4 rows in set

The SQL INSERT statement

INSERT INTO table
VALUES (value, value, ..., value);
INSERT INTO students
VALUES (789, "Nelson", "muntz@fox.com", "haha!");

More about INSERT

INSERT INTO table (columnName, columnName, ..., columnName)
VALUES (value, value, ..., value);
INSERT INTO students (name, email)
VALUES ("Lewis", "lewis@fox.com");

The SQL REPLACE statement

REPLACE INTO table (columnName, columnName, ..., columnName)
VALUES (value, value, ..., value);
REPLACE INTO students
VALUES (789, "Martin", "prince@fox.com");

The SQL UPDATE statement

UPDATE table
SET column = value,
    ...,
    column = value
WHERE column = value;
UPDATE students
SET email = "lisasimpson@gmail.com"
WHERE id = 888;

The SQL DELETE statement

DELETE FROM table
WHERE condition;
DELETE FROM students
WHERE id = 888;

Creating and deleting an entire database

CREATE DATABASE name;
DROP DATABASE name;
CREATE DATABASE warcraft;

Creating and deleting a table

CREATE TABLE name (
	columnName type constraints,
	...
	columnName type constraints
);
DROP TABLE name;
CREATE TABLE students (
	id INTEGER,
	name VARCHAR(20),
	email VARCHAR(32),
	password VARCHAR(16)
);

SQL data types

Column constraints

CREATE TABLE students (
	id INTEGER UNSIGNED NOT NULL PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	email VARCHAR(32),
	password VARCHAR(16) NOT NULL DEFAULT "12345"
);

Rename a table

ALTER TABLE name RENAME TO newName;
ALTER TABLE students RENAME TO children;

Add/remove/modify a column in a table

ALTER TABLE name
	ADD COLUMN columnName type constraints;

ALTER TABLE name DROP COLUMN columnName;

ALTER TABLE name
	CHANGE COLUMN oldColumnName newColumnName type constraints;

Database Design

Database design principles

First database design

student_grades
nameemailcourseteachergrade
Bartbart@fox.comComputer Science 142KrabappelB-
Bartbart@fox.comComputer Science 143HooverC
Milhousemilhouse@fox.comComputer Science 142KrabappelB+
Lisalisa@fox.comComputer Science 143HooverA+
Lisalisa@fox.comComputer Science 190MSteppA+
Ralphralph@fox.comInformatics 100KrabappelD+

Improved database design

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 190M9012
10004Informatics 1001234
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+
teachers
idname
1234Krabappel
5678Hoover
9012Stepp

Database design exercise

amazon cookie

Suppose we want to write a web store like Amazon.com. The store sells products that can be purchased by customers online. The customer can add items to their shopping cart and then order them. The customer can also check the order's status, whether it has shipped, etc.

Connecting to a server in a terminal

screenshot screenshot

Using the MySQL console

Once connected via SSH, type the following command at the prompt:

mysql -u username -p

Type in your MySQL password (should have been emailed to you). The screen will not show anything as you type the password.

Now at the mysql> prompt, you can type SQL commands. End each with a semicolon ;.

screenshot