JAVA DATABASE PROGRAMMING - Introduction to Java Programming: Comprehensive Version - page 1188

select lastName, firstName, deptId
from Student
where deptId = 'CS'
order by lastName desc , firstName asc ;

32.3.12 Joining Tables
Often you need to get information from multiple tables, as demonstrated in the next query.
Query 7: List the courses taken by the student Jacob Smith. To solve this query, you need
to join tables Student and Enrollment , as shown in Figure 32.18.

Student Table Enrollment Table

ssn lastName mi firstName … ssn courseId …

A tuple

Equal

F IGURE 32.18

Student and Enrollment are joined on ssn .

You can write the query in SQL:

select distinct lastName, firstName, courseId
from Student, Enrollment
where Student.ssn = Enrollment.ssn and
lastName = 'Smith' and firstName = 'Jacob' ;

The tables Student and Enrollment are listed in the from clause. The query examines
every pair of rows, each made of one item from Student and another from Enrollment and
selects the pairs that satisfy the condition in the where clause. The rows in Student have the
last name, Smith, and the first name, Jacob, and both rows from Student and Enrollment
have the same ssn values. For each pair selected, lastName and firstName from Student
and courseId from Enrollment are used to produce the result, as shown in Figure 32.19.
Student and Enrollment have the same attribute ssn . To distinguish them in a query, use
Student.ssn and Enrollment.ssn .

F IGURE 32.19

Query 7 demonstrates queries involving multiple tables.