Table-qualified field names in a join

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which query avoids an ambiguous column error when both tables have a studentId field?

A SELECT studentId, results.mark FROM students JOIN results ON students.studentId = results.studentId;

B SELECT students.studentId, results.mark FROM students JOIN results ON students.studentId = results.studentId;

2. What does students.firstName mean in a query?

A The firstName field that comes from the students table

B A new table called students.firstName

3. True or false: you must write table.field for a field that exists in only one of the joined tables, like mark in results.

A True: it is always required in a join

B False: it is optional when the name is unique

4. Which error does SQL give when you use a field name that exists in both joined tables without a table name?

A Ambiguous column name

B Table not found

5. Which is the correct way to write the table and field together?

A SELECT students.firstName FROM students;

B SELECT students,firstName FROM students;

Match the Pairs

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

Left column		Right column
1students.firstName		AThe ON condition uses studentId on both sides without a table name, so it is ambiguous. It should be students.studentId = results.studentId.
2SELECT students.firstName, results.mark		BNames the students table as the source of the firstName field, so there is no doubt which table it comes from.
3Both students and results have a studentId field, so the join must say which one it means or SQL gives an ambiguous column error.		CstudentId is in both tables but is not qualified in the SELECT, so SQL gives an ambiguous column error. It should be students.studentId.
4Qualifying the fields on both sides of the ON condition names the table for each side of the match.		DSELECT students.studentId, results.mark FROM students JOIN results ON students.studentId = results.studentId;
5SELECT studentId, mark FROM students JOIN results ON students.studentId = results.studentId;		EON orders.productId = products.productId
6SELECT students.firstName, results.mark FROM students JOIN results ON studentId = studentId;		FSelects one field from each of two tables, with each field qualified by its own table name.

Definitions

table.field

Fill in the Blanks

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

SELECT ________.name, classes.room FROM students JOIN classes ON students.classId = classes.classId;

SELECT students.name, classes.____ FROM students JOIN classes ON students.classId = classes.classId;

SELECT students.name, classes.room FROM students JOIN classes ON students.classId = _______.classId;

SELECT students._______, classes.room FROM students JOIN classes ON students.classId = classes.classId;

SELECT students.firstName, results.mark FROM students JOIN results ON students.studentId = results._________;

Spot the Error

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

Question 1

A SELECT students.name,

B classes.room,

C classId

D FROM students JOIN classes

E ON students.classId =

F classes.classId;

Question 2

A SELECT students.name,

B classes,room

C FROM students JOIN classes

D ON students.classId =

E classes.classId;

Question 3

A SELECT productId,

B products.name, orders.quantity

C FROM orders JOIN products

D ON orders.productId =

E products.productId;

Question 4

A SELECT students.name,

B classes.room,

C students.teacher

D FROM students JOIN classes

E ON students.classId =

F classes.classId;

Question 5

A SELECT subjects.name

B FROM results JOIN subjects

C ON result.subjectId =

D subjects.subjectId;

Answer Key

Table-qualified field names in a join

Section A: Quick Questions

B — SELECT students.studentId, results.mark FROM students JOIN results ON students.studentId = results.studentId;

A — The firstName field that comes from the students table

B — False: it is optional when the name is unique

A — Ambiguous column name

A — SELECT students.firstName FROM students;

Section B: Match the Pairs

students.firstName → Names the students table as the source of the firstName field, so there is no doubt which table it comes from.

SELECT students.firstName, results.mark → Selects one field from each of two tables, with each field qualified by its own table name.

Both students and results have a studentId field, so the join must say which one it means or SQL gives an ambiguous column error. → SELECT students.studentId, results.mark FROM students JOIN results ON students.studentId = results.studentId;

Qualifying the fields on both sides of the ON condition names the table for each side of the match. → ON orders.productId = products.productId

SELECT studentId, mark FROM students JOIN results ON students.studentId = results.studentId; → studentId is in both tables but is not qualified in the SELECT, so SQL gives an ambiguous column error. It should be students.studentId.

SELECT students.firstName, results.mark FROM students JOIN results ON studentId = studentId; → The ON condition uses studentId on both sides without a table name, so it is ambiguous. It should be students.studentId = results.studentId.

Section C: Definitions

table.field — Names the table a field comes from, like students.name. Needed in a join when both tables share a field name, or SQL gives an ambiguous column error.

Section D: Fill in the Blanks

SELECT students.name, classes.room
FROM students JOIN classes
ON students.classId = classes.classId;

SELECT students.name, classes.room
FROM students JOIN classes
ON students.classId = classes.classId;

SELECT students.name, classes.room
FROM students JOIN classes
ON students.classId = classes.classId;

SELECT students.classId, classes.room
FROM students JOIN classes
ON students.classId = classes.classId;

SELECT students.firstName, results.mark
FROM students JOIN results
ON students.studentId = results.studentId;

Section E: Spot the Error

C — Both students and classes have a classId field, so a bare classId gives an ambiguous column error. It should be classes.classId.

B — A table-qualified field uses a full stop (dot) between the table and the field, not a comma. It should be classes.room.

A — productId is in both orders and products, so an unqualified productId is an ambiguous column. It should be SELECT products.productId,

C — table.field must name the table the field really comes from. teacher is a field of classes, so it should be classes.teacher.

C — The word before the dot must match the table name exactly. The table is results, so it should be ON results.subjectId =