INF2603 Exam prep.

Chapter 8 Advanced SQL NOTE Several points are worth emphasizing:  We have provided the SQL scripts for the chapters. These scripts are intended to facilitate the flow of the material presented to the class. However, given the comments made by our students, the scripts should not replace the manual typing of the SQL commands by students. Some students learn SQL better when they have a chance to type their own commands and get the feedback provided by their errors. We recommend that the students use their lab time to practice the commands manually.  In this chapter, most of the queries are executed in the Oracle RDBMS. This chapter uses features that are restricted to client/server DBMSes. Such features are not commonly available in a desktop DBMS such as Microsoft Access. Answers to Review Questions 1. The relational set operators UNION, INTERSECT, and MINUS work properly only if the relations are union-compatible. What does union-compatible mean, and how would you check for this condition? Union compatible means that the relations yield attributes with identical names and compatible data types. That is, the relation A(c1,c2,c3) and the relation B(c1,c2,c3) have union compatibility if the columns have the same names, are in the same order, and the columns have “compatible” data types. Compatible data types do not require that the attributes be exactly identical -- only that they are comparable. For example, VARCHAR(15) and CHAR(15) are comparable, as are NUMBER (3,0) and INTEGER, and so on. 2. What is the difference between UNION and UNION ALL? Write the syntax for each. UNION yields unique rows. In other words, UNION eliminates duplicates rows. On the other hand, a UNION ALL operator will yield all rows of both relations, including duplicates. Notice that for two rows to be duplicated, they must have the same values in all columns. To illustrate the difference between UNION and UNION ALL, let’s assume two relations: A (ID, Name) with rows (1, Lake, 2, River, and 3, Ocean) and B (ID, Name) with rows (1, River, 2, Lake, and 3, Ocean). Given this description, Chapter 8 Advanced SQL 272 SELECT * FROM A UNION SELECT * FROM B will yield: ID Name 1 Lake 2 River 3 Ocean 1 River 2 Lake while SELECT * FROM A UNION ALL SELECT * FROM B will yield: ID Name 1 Lake 2 River 3 Ocean 1 River 2 Lake 3 Ocean 3. Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION query? (List the query output.) The query output will be: Alice Cordoza John Cretchakov Anne McDonald Mary Chen