University of Florida_ECON 1029780538469685_IM_ch08_REVIEWED AND EDITED BY EXPERTS 2021

Document Content and Description Below

Chapter 8 Advanced SQL 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. What does it mean to say that SQL operators are set oriented? The description of SQL operators as set oriented means that the commands work over entire tables at a time, not row by row. 2. 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 both relations have the same number of attributes and corresponding attributes in the relations 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. 3. 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). 24Chapter 8 Advanced SQL Given this description, 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 4. 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 [Show More]

Last updated: 1 year ago

Preview 1 out of 32 pages