Using the Class_Summary view, the user can simply select the desired columns without specifying any join conditions. Listing 8.39 illustrates how a user can select those classes whose titles include the phrase INTRO.

Listing 8.39. Selecting from a view.

SQL> select Class_ID, Class_Building, Class_Room,
    2         Department_ID, Course_ID, Title, Last_Name
   3  from Class_Summary
   4  where
   5  Title like `%INTRO%';

CLASS_ CLASS_BUILDING CLAS DEPART COURS TITLE                LAST_NAME
------ -------------- ---- ------ ----- -------------------- ---------
109100 FLUGLE HALL     180 BIO    101   INTRO TO BIOLOGY     WEISS
108300 FLUGLE HALL     150 ENGL   101   INTRO TO ENGLISH LIT CHERNOW

Summary

This lesson focused on these features of SQL:

• Oracle provides group functions such as MAX and MIN that return a single value for an entire set of rows.
• The SELECT statement has two optional clauses—GROUP BY and HAVING—that return rows grouped by a common value for specified columns.
• Hierarchical data can be organized through the CONNECT BY clause. You must indicate a starting condition with the START WITH clause.
• A join operation is a SELECT statement whose FROM clause has two or more tables and one or more join conditions that typically equate primary and foreign keys.
• The INTERSECT operator returns the set of common rows that are found in two sets of rows.
• The UNION operator returns the distinct rows that exist in two sets of rows.
• The MINUS operator returns the rows in one set that are not found in a second set.

What Comes Next?

On Day 9, "Programming an Oracle Database with PL/SQL," you learn about the basic elements of PL/SQL such as flow-of-control statements, variable declaration, and procedure and function declarations.

Q&A

Q Can two tables, A and B, be joined by columns if the first column's datatype in table A is not the same as the second column's datatype in table B?

A Yes, it's possible. For example, you can use a conversion function, such as TO_CHAR, TO_DATE, or TO_NUMBER, to convert the first column so that the datatype returned by the function is the same as the second column's datatype.

Workshop

The purpose of the Workshop is to allow you to test your knowledge of the material discussed in the lesson. See whether you can correctly answer the questions in the quiz and complete the exercise before you continue with tomorrow's lesson.

Quiz

1. Construct an SQL statement that retrieves the last name of an instructor who is teaching a course with additional fees greater than $50.
2. Construct an SQL statement that retrieves a list of cities in which students reside and the number of students that reside in each city.
3. Create a view that lists each class—its Class_ID, Department_ID, and Course_ID—for those classes that meet on Mondays.

Exercise

The number of instructors is 18. The number of classes being offered is 10. However, the number of distinct Instructor_IDs in the Class table is nine. Using these tables and SQL, provide a complete explanation.