Web Programming Step by Step

Lecture 23
SQL Joins; HTML Tables

Reading: 2.2.2; 11.4 - 11.5; Appendix A

References: SQL syntax reference, w3schools tutorial

Except where otherwise noted, the contents of this presentation are Copyright 2010 Marty Stepp and Jessica Miller.

Valid XHTML 1.1 Valid CSS!

Appendix A: Database Design

HTML tables: <table>, <tr>, <td>

A 2D table of rows and columns of data (block element)

<table>
	<tr><td>1,1</td><td>1,2 okay</td></tr>
	<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
1,11,2 okay
2,1 real wide2,2

Table headers, captions: <th>, <caption>

<table>
	<caption>My important data</caption>
	<tr><th>Column 1</th><th>Column 2</th></tr>
	<tr><td>1,1</td><td>1,2 okay</td></tr>
	<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
My important data
Column 1Column 2
1,11,2 okay
2,1 real wide2,2

Styling tables (3.2.6)

table { border: 2px solid black; caption-side: bottom; }
tr { font-style: italic; }
td { background-color: yellow; text-align: center; width: 30%; }
My important data
Column 1Column 2
1,11,2 okay
2,1 real wide2,2

The border-collapse property

table, td, th { border: 2px solid black; }
table { border-collapse: collapse; }
Without border-collapse
Column 1Column 2
1,11,2
2,12,2
With border-collapse
Column 1Column 2
1,11,2
2,12,2

The rowspan and colspan attributes

<table>
	<tr><th>Column 1</th><th>Column 2</th><th>Column 3</th></tr>
	<tr><td colspan="2">1,1-1,2</td>
		<td rowspan="3">1,3-3,3</td></tr>
	<tr><td>2,1</td><td>2,2</td></tr>
	<tr><td>3,1</td><td>3,2</td></tr>
</table>

Column styles: <col>, <colgroup>

<table>
	<col class="urgent" />
	<colgroup class="highlight" span="2"></colgroup>
	
	<tr><th>Column 1</th><th>Column 2</th><th>Column 3</th></tr>
	<tr><td>1,1</td><td>1,2</td><td>1,3</td></tr>
	<tr><td>2,1</td><td>2,2</td><td>2,3</td></tr>
</table>

Don't use tables for layout!


11.4: Multi-table Queries

Database design principles (Appendix A)

First database design

student_grades
nameemailcoursegrade
Bartbart@fox.comComputer Science 142B-
Bartbart@fox.comComputer Science 143C
Milhousemilhouse@fox.comComputer Science 142B+
Lisalisa@fox.comComputer Science 143A+
Lisalisa@fox.comComputer Science 190MA+
Ralphralph@fox.comInformatics 100D+

Second database design

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
courses
idname
10001Computer Science 142
10002Computer Science 143
10003Computer Science 190M
10004Informatics 100
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+

Related tables and keys

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
courses
idname
10001Computer Science 142
10002Computer Science 143
10003Computer Science 190M
10004Informatics 100
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+

Design question

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
courses
idname
10001Computer Science 142
10002Computer Science 143
10003Computer Science 190M
10004Informatics 100
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+

Design answer

teachers
idname
1234Krabappel
5678Hoover
9012Stepp
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 190M9012
10004Informatics 1001234

11.4: Multi-table Queries

Example simpsons database

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

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.

Cross product with JOIN (11.4.1)

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 (11.4.2)

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) (11.4.3)

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

A suboptimal query

Improved query

Practice queries

Designing a query (11.4.4)

Example imdb database (11.1.2)

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 (11.4.3)

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)

IMDb practice queries