Table aliases in a join

Tip: Choose Save as PDF in the print dialog to download. Use A4 paper size.

Table aliases in a join

Name:
Date:
Class:
A

Quick Questions

Shade in the A or B circle for each question.

1. Which line gives the students table the short name s?
A FROM students s
B FROM s students
2. Once you write FROM students s, how should you refer to the firstName column?
A students.firstName
B s.firstName
3. Which ON condition is shorter and uses aliases correctly? (students s, results r)
A ON students.studentId = results.studentId
B ON s.studentId = r.studentId
4. True or false: you can set an alias in the FROM clause and still write the full table name in the SELECT.
A False
B True
5. Which query is written correctly?
A SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId
B SELECT s.firstName, r.mark FROM students JOIN results ON s.studentId = r.studentId
B

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left columnRight column
1FROM students s AGives the students table the short name s (an alias) so the rest of the query can use s instead of students
2JOIN results r ON s.studentId = r.studentId BThe alias r is used in the ON condition, but it was never created after JOIN results. It should be JOIN results r.
3After you give a table an alias, use that alias to qualify its columns in the SELECT list CON s.studentId = r.studentId
4An alias makes the ON condition shorter than using the full table names DThe students table was given the alias s, but the SELECT still uses the full name students. Use s.firstName so the alias is used everywhere.
5SELECT students.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId EJoins the results table, calls it r, and uses the short names s and r to match the foreign key to the primary key
6SELECT s.firstName, r.mark FROM students s JOIN results ON s.studentId = r.studentId FSELECT s.firstName, r.mark
C

Definitions

FROM students s
D

Fill in the Blanks

Complete each line of code by filling in the missing parts.

1.
SELECT s.firstName, r.mark FROM students _ JOIN results r ON s.studentId = r.studentId;
2.
SELECT _.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;
3.
SELECT s.firstName, r.mark FROM students s JOIN results r ON _.studentId = r.studentId;
4.
SELECT s.firstName, r.mark FROM students s JOIN results _ ON s.studentId = r.studentId;
5.
SELECT p.name, o.quantity FROM orders o JOIN products p ON o.productId = _.productId;
E

Spot the Error

Shade in the letter of the line that contains an error.

Question 1
A SELECT students.house, r.mark
B FROM students s
C JOIN results r
D ON s.studentId = r.studentId
Question 2
A SELECT o.orderId, pr.name
B FROM orders o
C JOIN products p
D ON o.productId = p.productId
Question 3
A SELECT s.firstName, r.mark
B FROM students s
C JOIN r results
D ON s.studentId = r.studentId
Question 4
A SELECT s.firstName, r.mark
B FROM students s
C JOIN results r
D ON s.studentId = t.studentId
Question 5
A SELECT s.firstName, r.mark
B FROM students, s
C JOIN results r
D ON s.studentId = r.studentId
E WHERE r.mark > 80

Answer Key

Table aliases in a join
Section A: Quick Questions
Q1
A — FROM students s
Q2
B — s.firstName
Q3
B — ON s.studentId = r.studentId
Q4
A — False
Q5
A — SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId
Section B: Match the Pairs
Q1
FROM students s → Gives the students table the short name s (an alias) so the rest of the query can use s instead of students
Q2
JOIN results r ON s.studentId = r.studentId → Joins the results table, calls it r, and uses the short names s and r to match the foreign key to the primary key
Q3
After you give a table an alias, use that alias to qualify its columns in the SELECT list → SELECT s.firstName, r.mark
Q4
An alias makes the ON condition shorter than using the full table names → ON s.studentId = r.studentId
Q5
SELECT students.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId → The students table was given the alias s, but the SELECT still uses the full name students. Use s.firstName so the alias is used everywhere.
Q6
SELECT s.firstName, r.mark FROM students s JOIN results ON s.studentId = r.studentId → The alias r is used in the ON condition, but it was never created after JOIN results. It should be JOIN results r.
Section C: Definitions
Q1
FROM students s — Gives the students table the short name s after FROM, so the rest of the query can write s.firstName instead of students.firstName.
Section D: Fill in the Blanks
Q1
SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;
Q2
SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;
Q3
SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;
Q4
SELECT s.firstName, r.mark FROM students s JOIN results r ON s.studentId = r.studentId;
Q5
SELECT p.name, o.quantity FROM orders o JOIN products p ON o.productId = p.productId;
Section E: Spot the Error
Q1
A — The students table was given the alias s in the FROM line, so the rest of the query must use s. The first line should be SELECT s.house, r.mark.
Q2
A — The products table was given the alias p, but the SELECT uses pr, which was never defined. It should be p.name.
Q3
C — The alias goes after the table name, not before it. The line should be JOIN results r.
Q4
D — The alias t was never defined. The results table has the alias r, so the ON condition should be s.studentId = r.studentId.
Q5
B — The alias follows the table name with just a space. The comma makes s look like a second table. It should be FROM students s.