CH-7-13 User Creation and Management – Oracle 12c: SQL : Chapter Notes

This Document Contains Chapters 7 to 13 Chapter 7 User Creation and Management At a Glance Instructor’s Notes • Chapter Overview • Chapter Objectives • Instructor Notes • Troubleshooting Tips • Quick Quizzes • Discussion Questions • Key Terms Chapter Overview The purpose of this chapter is to demonstrate the requirements for connecting a new user to an Oracle database. Users need both a valid account and the CREATE SESSION privilege. In addition, account management and security concepts are discussed. Strategies to simplify privilege administration are presented in terms of roles. Students must have the CREATE USER privilege to create the user accounts in this chapter. In addition, students must have been granted CREATE SESSION privilege with the WITH ADMIN OPTION to grant this privilege to new accounts. To create the role examples, the students will need the CREATE ROLE system privilege. Chapter Objectives After completing this chapter, you should be able to do the following: • Explain the concept of data security • Create a new user account • Identify two types of privileges: system and object • Grant privileges to a user • Address password expiration requirements • Change the password of an existing account • Create a role • Grant privileges to a role This Document Contains Chapters 7 to 13 • Assign a user to a role • View privilege information • Revoke privileges from a user and a role • Remove a user and roles Instructor Notes If you issue student Oracle accounts on a school server, assigning users account creation privileges may not be an option. Please review the Instructor’s Setup Guide provided in the Instructor Downloads area for suggestions in providing students limited access enabling experimentation with much of the chapter content. Data Security Recent events have caused more attention to be focused in the areas of cyberterrorism and the need for network security. Due to the type of detailed personal information contained in the databases of government and business organizations, organizations are expected, even required, to protect this information. It is not uncommon for a company whose database has been illegally accessed to be sued for negligence due to lack of proper security measures. Oracle 12c supports various levels of security, especially for verifying user identity, including support for third-party biometric devices. Quick Quiz 1. What is the purpose of authentication? ANSWER: To ensure that users are who they claim they are 2. Why is authentication necessary? ANSWER: To protect the data contained in the database 3. What is a standard authentication procedure used by most organizations before access to the database is allowed? ANSWER: A valid user name and password 4. What term is used to describe individuals who gain illegal access to a system? ANSWER: Hackers Creating a User When a user account is created, a password must be assigned. It is preferable to immediately expire the password, which will force the user to change the DBA assigned password as soon as the user first successfully connects to the database. Although a user may have a valid user account, this does not imply that user will be to access the database. Privileges will still be required, even with a valid account. Troubleshooting Tip Have the students create a user account and then attempt to connect to the database to demonstrate the need for the CREATE SESSION privilege. Quick Quiz 1. What clause is used with the CREATE USER command to specify a password for the user? ANSWER: IDENTIFIED BY 2. How can a user disconnect and reconnect to an Oracle database in one step? ANSWER: Use the CONNECT command 3. What is the maximum number of characters for a user name? ANSWER: 30 4. Does having a valid user account allow a user to connect to a database? ANSWER: No, the correct privilege must be assigned. 5. What symbols can be included in a user name? ANSWER: Underscore, dollar sign, and number sign Assigning User Privileges There are two categories of privileges: object and system privileges. Although the GRANT command is used to assign both types of privileges, they cannot be included in the same GRANT command because object privileges require use of the ON clause. A user will automatically have all privileges associated with objects he or she created. Troubleshooting Tip Remind students that the ADMIN keyword is associated with system privileges, whereas the GRANT keyword is associated with object privileges, and that a cascade effect can occur when revoking object privileges. Quick Quiz 1. What is the difference between system privileges and object privileges? ANSWER: Object privileges are granted on specific objects, whereas system privileges apply across the entire database. 2. Which type of privilege is the WITH ADMIN OPTION used with? ANSWER: System privileges 3. What does the ALL keyword specify? ANSWER: That all privileges of that type (system or object) are being granted to the indicated user(s) 4. What does the PUBLIC keyword specify? ANSWER: That the specified privilege(s) is granted to all users 5. Which privilege is required for a user to connect to a database? ANSWER: CREATE SESSION privilege Utilizing Roles Roles can be used to organize privileges granted to users. They can be organized based on the type of tasks that need to be performed by users. A default role can be established for users. However, users have the ability to enable other assigned roles when needed using the SET ROLE command. Troubleshooting Tip Demonstrate the number of steps that would be required to assign privileges to new users versus assigning the privileges via a role that has already been created. Quick Quiz 1. What is a role? ANSWER: A collection of privileges 2. Which option, the WITH ADMIN OPTION or WITH GRANT OPTION, cannot be used when a privilege is assigned to a role? ANSWER: WITH GRANT OPTION 3. How can a user be assigned a default role? ANSWER: Use the DEFAULT ROLE keywords 4. Can a password be assigned to a role? ANSWER: Yes 5. How many roles can a user have enabled at a time? ANSWER: One Viewing Privilege Information Various data dictionary views can be queried to determine the privileges currently assigned to a user or role. Removing Privileges and Roles When a privilege is revoked from a user or a role, the effect is immediate and the user will no longer be able to use that privilege. However, if a role is revoked from the user and the role was enabled at the time it was revoked, the user will be able to use the privileges assigned to that role for the remainder of the current session. However, it will not be available at the time of the next session. Quick Quiz 1. What command is used to revoke a system privilege from a role? ANSWER: REVOKE 2. How can you change a user name? ANSWER: Drop the user account and create another one using the correct name 3. What command is used to delete a role? ANSWER: DROP ROLE 4. Which type of privilege could have a cascade effect on other users when it is revoked? ANSWER: An object privilege granted with the WITH GRANT OPTION 5. What command is used to remove a user from the database? ANSWER: DROP USER Discussion Questions 1. How could roles be used to simplify the administration of privileges in a large organization that consists of eight departments and a minimum of three levels of management within each department? 2. Describe different roles that an insurance company might create to manage user groups. Key Terms authentication — The process of validating the identity of computer users. coding scheme — When a DBA creates a user account, the user's identity is set by using a code; the "scheme" of the code often consists of the user’s first initial followed by last name. Used widely in industry for part numbers, customer numbers, etc. hackers — A slang term generally applied to computer criminals who gain illegal access to information systems. object privileges — Allow users to perform DML or retrieval operations on the data contained within database objects. privileges — Allow database access to users. Oracle 12c has system privileges and object privileges. role — A group, or collection, of privileges. In most organizations, roles correlate to users' job duties. system privileges — Allow access to the Oracle 12c database and let users perform DDL operations such as CREATE, ALTER, and DROP on database objects. An object privilege combined with the keyword ANY is also considered a system privilege. Chapter 8 Restricting Rows and Sorting Data At a Glance Instructor’s Notes • Chapter Overview • Chapter Objectives • Instructor Notes • Troubleshooting Tips • Quick Quizzes • Discussion Questions • Key Terms Chapter Overview In this chapter, students will learn how to select rows based on a condition specified in a WHERE clause. This will include learning various comparison operators and how to join multiple conditions using logical operators. In addition, the ORDER BY clause can be used to perform primary and secondary sorts to display the output in a specified order. Chapter Objectives After completing this chapter, you should be able to do the following: • Use a WHERE clause to restrict the rows returned by a query • Create a search condition using mathematical comparison operators • Use the BETWEEN...AND comparison operator to identify records within a range of values • Specify a list of values for a search condition using the IN comparison operator • Search for patterns using the LIKE comparison operator • Identify the purpose of the % and _ wildcard characters • Join multiple search conditions using the appropriate logical operator • Perform searches for NULL values • Specify the order for the presentation of query results using an ORDER BY clause Instructor Notes WHERE Clause Syntax The WHERE clause specifies the rows to be displayed in the output. The WHERE clause syntax requires the user to specify the column to be used for the comparison, the comparison operator to indicate the relationship, and the value the column is to be compared against. The condition can be a value or another column stored in the database. If the condition is not a numeric value, it must be enclosed in single quotation marks. Troubleshooting Tip Demonstrate that search conditions are case sensitive by specifying a condition in lowercase characters that is stored in the table in uppercase characters. Quick Quiz 1. Which part of an SQL command is case sensitive? ANSWER: Search condition values 2. When must a search condition value be included in single quotation marks? ANSWER: If it is a nonnumeric value 3. What is special about searching for dates? ANSWER: The date must be in single quotation marks and in the format of DD-MON- YY. 4. When is the WHERE clause required? ANSWER: When searching for rows based on a certain condition 5. What type of data does not need to be included in single quotation marks when searching for specific rows in a table? ANSWER: Numeric data Comparison Operators Comparison operators include arithmetic operators, as well as special comparison operators. The special comparison operators include BETWEEN, IN, LIKE, and NULL. Each special operator has a specific syntax that must be followed or an error message will be returned. Troubleshooting Tip Demonstrate that the BETWEEN operator includes the specified endpoints of the range. The LIKE operator is different from the other basic comparison operators in that it is used to identify character patterns. The % and _ wildcard characters are used to create a pattern. Quick Quiz 1. Which comparison operator can be used to specify a range of values? ANSWER: BETWEEN 2. Which comparison operator is used to indicate a list of values? ANSWER: IN 3. Wildcard characters can be used with which comparison operator? ANSWER: LIKE 4. Which wildcard character represents exactly one character in a search pattern? ANSWER: Underscore 5. Which wildcard character represents one or more characters in a search pattern? ANSWER: Percent sign Logical Operators Logical operators are used to combine search conditions. If the AND operator is used to combine more than one condition, both conditions must be TRUE. If the OR operator is used to combine the conditions, only one condition must be TRUE. The NOT is used to specify that only FALSE evaluations are to be included in the results. Troubleshooting Tip Demonstrate that the logical operators are evaluated in the sequence of NOT, AND, and OR. Present an example and then use parentheses to override the order of evaluation. Treatment of NULL Values Since a NULL value indicates the absence of data, it can only be identified using the IS NULL comparison operator. No condition is specified because it is implied in the operator. To identify rows that do not contain a NULL value in the specified column, use the IS NOT NULL operator. Troubleshooting Tip Demonstrate that the condition of = NULL or =’NULL’ will not return the same results as the IS NULL comparison operator. Quick Quiz 1. Why can’t the equal sign operator be used to search for NULL values? ANSWER: A NULL value is not equal to anything. 2. What is a NULL value? ANSWER: The absence of data 3. What is the purpose of the IS NULL operator? ANSWER: It is used to search for rows containing NULL values in a specified column. 4. When is the IS NOT NULL operator used? ANSWER: When searching for rows that do not contain a NULL value in the specified column 5. Why can’t a blank space be used to search for NULL values? ANSWER: A blank space has a value; a NULL values does not. ORDER BY Clause Syntax The ORDER BY clause is used to indicate that the results are to be presented in a specified order. This makes it easier for management to locate specific rows in the results. The order of the presentation will be ascending by default. In ascending order, any numeric values will be listed first, followed by character values and then NULL values. The listing of NULL values can be overridden using the NULLS FIRST or NULLS LAST keywords. Quick Quiz 1. What is the purpose of the ORDER BY clause? ANSWER: Specifies the presentation order of the output 2. When data is sorted in descending order, are NULL values listed first or last? ANSWER: First 3. Can a column alias be referenced in an ORDER BY clause? ANSWER: Yes 4. How can the position of a column in the SELECT clause be referenced in the ORDER BY clause? ANSWER: Specify the numeric position of the column in the column list given in the SELECT clause 5. When specifying a secondary sort, how are the column names listed in the ORDER BY clause? ANSWER: The column for the primary sort is listed first, followed by a comma, and then the column for the second sort is listed. Discussion Questions 1. Discuss the differences in handling different datatypes when including conditions in a query. 2. Discuss issues regarding the handling of multiple conditions in a query. Key Terms comparison operator — A search condition that indicates how data should relate to a given search value (equal to, greater than, less than, etc.). Common comparison operators include >, =, and <= . condition — A portion of an SQL statement that identifies what must exist, or a requirement that must be met. When a query is executed, any record meeting the given condition will be returned in query results. equality operator — A search condition that evaluates data for exact, or equal, values. The equality operator symbol is the equal sign (=). logical operators — Used to combine two or more search conditions. The logical operators include AND and OR. The NOT operator reverses the meaning of search conditions. NULL value — Means no value has been stored in that particular field. A NULL value indicates the absence of data, not a blank space. primary sort — When only one column is specified in the ORDER BY clause, data is ordered, or sorted, based on the data organization within the specified column. secondary sort — When two or more columns are specified in the ORDER BY clause, data in the second column (or additional columns) provide an alternative field on which to order data if an exact match occurs between two or more rows in the first, or primary, sort. wildcard characters — Symbols used to represent one or more alphanumeric characters. The wildcard characters in Oracle 12c are the percent sign (%) and the underscore symbol ( _ ). The percent sign is used to represent any number of characters; the underscore represents one character. Chapter 9 Joining Data from Multiple Tables At a Glance Instructor’s Notes • Chapter Overview • Chapter Objectives • Instructor Notes • Troubleshooting Tips • Quick Quizzes • Discussion Questions • Key Terms Chapter Overview The normalization process divides data into tables. However, in many cases, the data from related tables will need to be joined together to be useful in daily business activities. This chapter addresses how to join data contained in various tables using conditions specified in the WHERE clause and using the JOIN keyword in the FROM clause. The WHERE clause represents the traditional join approach, whereas the JOIN keyword represents the ANSI join method. Chapter Objectives After completing this chapter, you should be able to do the following: • Identify a Cartesian join • Create an equality join using the WHERE clause • Create an equality join using the JOIN keyword • Create a non-equality join using the WHERE clause • Create a non-equality join using the JOIN...ON approach • Create a self-join using the WHERE clause • Create a self-join using the JOIN keyword • Distinguish an inner join from an outer join • Create an outer join using the WHERE clause • Create an outer join using the OUTER keyword • Use set operators to combine the results of multiple queries Instructor Notes Cartesian Joins Cartesian joins are rarely used in the real world unless a user needs to view every possible combination of rows. In most cases, a Cartesian join is the result of accidentally not including the join condition in a query. Troubleshooting Tip Demonstrate the results of including two tables in the FROM clause of a query with no join condition. Quick Quiz 1. What can cause a Cartesian join? ANSWER: Forgetting to include a join condition 2. What keyword is used in the FROM clause to intentionally create a Cartesian join? ANSWER: CROSS JOIN 3. If one table contains 12 rows and the second table contains 6 rows, a Cartesian join will result in how many rows? ANSWER: 72 Equality Joins An equality join is the type of join most often needed. It requires a value in the row of one table to match a value in a row contained in the other table. In other words, the tables involved share a common column. When created with the WHERE clause, the “equal to” operator must be used to create the joining condition. When the NATURAL JOIN keyword is used in the FROM clause, table aliases cannot be assigned. The JOIN…USING keywords are required when the tables being joined have more than one common column. The JOIN…ON keywords are used with equality joins that do not share the same named column, non-equality, and self joins. There will always be one less joining condition than there are tables to be joined. Count the number of tables and number of join conditions as a quick check of a completed query containing a join. Troubleshooting Tip Highlight that the NATURAL JOIN is often not used, as same- named columns may not always represent common data columns to be used in a join. For example, two tables may have a column named TYPE; however, the column may represent two entirely different data items. Also, table column modifications could lead to unexpected join operations if close attention to related table column names is not considered. Troubleshooting Tip Demonstrate the type of error that will occur if table aliases are assigned in a SELECT statement that joins tables using the NATURAL JOIN or JOIN USING keywords. Quick Quiz 1. What operator is used to create an equality join in the WHERE clause? ANSWER: Equal sign 2. When is it appropriate to use the NATURAL JOIN keywords? ANSWER: When the tables being joined share common columns with the same names and definitions 3. What is another method to accomplish an ANSI join when the two tables involved share common column names and definitions? ANSWER: JOIN … USING 4. When is it appropriate to use the JOIN…ON keywords? ANSWER: When the tables being joined do not have a commonly named and defined column or do not contain equivalent data—allows a condition for the join to be specified 5. What is the purpose of creating an equality join? ANSWER: To reconstruct equivalent data that is stored in separate tables Non-Equality Joins Non-equality joins are used when related data are not equivalent. This type of join is commonly used when searching for freight prices, ideal body weight, or any type of data that is based on a range of values. The key is to make certain the values in the ranges are mutually exclusive or do not overlap. Troubleshooting Tip Highlight the JustLee Books promotion table, which uses book retail value ranges to determine customer gifts. Quick Quiz 1. What is the purpose of a non-equality join? ANSWER: To reconstruct data stored in separate tables that do not contain equivalent values 2. How is a non-equality join constructed in the WHERE clause? ANSWER: Using a condition that does not have an “equal to” relationship 3. What keywords can be used to construct a non-equality join in the FROM clause? ANSWER: Only the JOIN…ON keywords 4. Why can’t the NATURAL JOIN or JOIN…USING keywords be used to construct a non- equality join? ANSWER: They both have an implied “equal to” relationship. 5. When joining more than two tables in the WHERE clause, what logical operator is used to link the conditions? ANSWER: AND Self-Joins Sometimes, data may need to reference other data contained in the same table—two columns within the same table have a relationship. In this case, the software is “tricked” into believing that two copies of the same table exist by assigning two different table aliases to the same table. As an example, this type of join is used in medical offices where patients have dependents on their insurance policy that are also patients. Troubleshooting Tip Ensure that students understand how self-joins work by using the column names to identify which copy of the table contains the displayed column. Quick Quiz 1. When is a self-join appropriate? ANSWER: When the data needed for the join is contained in other rows within the same table 2. How is Oracle 12c “tricked” into creating a self-join? ANSWER: Two copies of the same table are referenced, but each copy is identified through different table aliases. 3. What JOIN keywords can be used to create a self-join? ANSWER: Only the JOIN…ON or JOIN…USING keywords; in either case, at least one table alias must be created. 4. How can you identify whether a query contains a self-join and not some other type of join? ANSWER: The same table name will be listed twice in the FROM clause. Outer Joins Inner joins like equality, non-equality, and self-joins require that equivalent values exist in each table. In some cases, it may be necessary to also display rows that do not have a match in the corresponding table. Basically, a NULL row is created in the deficient table and is then matched with the “dangling” row. Troubleshooting Tip Ensure students understand that a join will drop nonmatching rows by default. Troubleshooting Tip Issue sample queries using the outer join operator and again using the OUTER JOIN keyword. Make certain the student can distinguish between RIGHT and LEFT outer joins using each method. Troubleshooting Tip In previous versions of Oracle an error would be raised if you attempted to assign the outer join operator on the same table in more than one join operation. Quick Quiz 1. How is an outer join different from an inner join? ANSWER: An inner join only includes rows that have a matching value in each table; an outer join will include unmatched rows. 2. What operator is used to create an outer join in the WHERE clause? ANSWER: (+) 3. What type of outer join can be created in the FROM clause that cannot be created in the WHERE clause? ANSWER: A full outer join 4. What is a full outer join? ANSWER: Allows a user to display unmatched rows from each table 5. When an outer join is created in a WHERE clause, the outer join operator is placed next to which table? ANSWER: The table that should have the NULL rows inserted so they can be matched with the “unmatched” rows in the other table Set Operators In some cases, the only way to find a solution is to combine the results of two different queries. Set operators can be used to add the results of several queries or to alter the results of the first query based on the results returned by another query. Quick Quiz 1. Where are set operators used? ANSWER: To combine the results of two or more queries 2. What is the difference between the UNION and UNION ALL set operators? ANSWER: The UNION set operator suppresses duplicates, whereas the UNION ALL set operator does not 3. What is the purpose of the UNION set operator? ANSWER: The UNION set operator is used to return the results of both queries. 4. What is the purpose of the INTERSECT set operator? ANSWER: The INTERSECT set operator will only display the results returned by both queries. 5. What is the purpose of the MINUS set operator? ANSWER: The MINUS set operator will subtract the results of the second query from the results returned by the first query. Discussion Questions 1. Might a user want to create a Cartesian join? 2. Discuss examples of when a user would need to create non-equality joins. Key Terms Cartesian join — Links table data so each record in the first table is matched with each individual record in the second table. Also called a Cartesian product or cross join. column alias — Another name substituted for a column name. A column alias is created in a query and displayed in the results. column qualifier — Indicates the table containing a referenced column. common column — A column that exists in two or more tables and contains equivalent data. equality joins — Links table data in two (or more) tables having equivalent data stored in a common column. These joins might also be called equijoins, inner joins, or simple joins. inner joins — Joins that display data if there were a corresponding record in each table queried. Equality joins, non-equality joins, and self-joins are all classified as inner joins. non-equality join — Links data in two tables that do not have equivalent rows of data. outer join — Links data in tables that do not have equivalent rows. An outer join can be created in either the WHERE clause with an outer join operator (+) or by using the OUTER JOIN keywords. outer join operator — The plus (+) symbol enclosed in parentheses, used in an outer join operation. self-join — Links data within a table to other data within the same table. A self-join can be created with a WHERE clause or by using the JOIN keyword with the ON clause. set operators — Combine the results of two (or more) SELECT statements. Valid set operators in Oracle 12c are UNION, UNION ALL, INTERSECT, and MINUS. table alias — A temporary name for a table, given in the FROM clause. Table aliases are used to reduce memory requirements or the number of keystrokes needed when specifying a table throughout the SQL statement. Chapter 10 Selected Single-Row Functions At a Glance Instructor’s Notes • Chapter Overview • Chapter Objectives • Instructor Notes • Troubleshooting Tips • Quick Quizzes • Discussion Questions • Key Terms Chapter Overview The focus of this chapter is to examine several single-row functions. Single-row functions return one row of results for each record processed. Such functions can be used to perform case conversions, date and numeric calculations, and various other data manipulations. The DUAL table can be used to practice function usage. Chapter Objectives After completing this chapter, you should be able to do the following: • Use the UPPER, LOWER, and INITCAP functions to change the case of field values and character strings • Manipulate character substrings with the SUBSTR and INSTR functions • Nest functions inside other functions • Determine the length of a character string using the LENGTH function • Use the LPAD and RPAD functions to pad a string to a certain width • Use the LTRIM and RTRIM functions to remove specific character strings • Substitute character string values with the REPLACE and TRANSLATE functions • Combine character strings with the CONCAT function • Round and truncate numeric data using the ROUND and TRUNC functions • Return the remainder only of a division operation using the MOD function • Use the ABS function to set numeric values as positive • Use the POWER function to raise a number to a specified power • Calculate the number of months between two dates using the MONTHS_BETWEEN function • Manipulate date data using the ADD_MONTHS, NEXT_DAY, LAST_DAY, and TO_DATE functions • Differentiate between CURRENT_DATE and SYSDATE values • Extend pattern matching capabilities with regular expressions • Identify and correct problems associated with calculations involving NULL values using the NVL function • Manipulate NULL values with the NVL2 and NULLIF functions • Display dates and numbers in a specific format using the TO_CHAR function • Perform condition processing similar to an IF statement with the DECODE function and CASE Expression • Use the SOUNDEX function to identify character phonetics • Convert string values to numeric with the TO_NUMBER function • Use the DUAL table to test functions Instructor Notes Case Conversion Functions Case conversion functions, such as LOWER, UPPER, and INITCAP, are used to alter the case of the character strings. When used in a SELECT clause, these functions only change the way the data is displayed, not how it is stored in the database table. These functions are used frequently to circumvent the case sensitivity issues of conditional expressions. Troubleshooting Tip To quickly present how a function works, use examples with static values and reference the DUAL table. Afterwards, have student create queries using data contained in the JustLee Books database. Quick Quiz 1. Which function is used to convert characters to mixed case characters? ANSWER: INITCAP 2. Which function can be used to convert characters to all uppercase characters? ANSWER: UPPER 3. Which function can be used to convert uppercase characters to lowercase characters? ANSWER: LOWER 4. What happens if a case conversion function is used in the SELECT clause when data is retrieved? ANSWER: The results are displayed in the case indicated by the function. Character Manipulation Functions Character manipulation functions can be used to extract portions of a string, determine the length of a string, or even change the appearance of data. These types of functions can be used to prepare data for inclusion in a preprinted form or to retrieve information needed for utility or database maintenance purposes. One very commonly used function is the RPAD function to add asterisks to fill in blank spaces on checks. This section introduces SUBSTR, INSTR, LENGTH, LPAD/RPAD, LTRIM/RTRIM, REPLACE, TRANSLATE, and CONCAT. Troubleshooting Tip There are numerous functions supported by Oracle 12c. Have the students create an account for the Oracle Technology Network and view some of the function documentation at the Web site. The SQL reference book contains an entire chapter on single-row functions. Troubleshooting Tip Demonstrate that various types of single-row functions can be nested. For example, the INSTR function can be nested in a SUBSTR function. Quick Quiz 1. What is the purpose of the SUBSTR function? ANSWER: It is used to return a portion of a string. 2. Which function can be used to determine the length of a string? ANSWER: LENGTH function 3. Which function can be used to fill in blank spaces in a preprinted form? ANSWER: LPAD or RPAD functions 4. How many character strings can be concatenated with the CONCAT function? ANSWER: 2 5. Which function can be used to substitute one character string for another character string? ANSWER: REPLACE function Number Functions Although the majority of functions that are used to perform calculations are multiple-row functions, there are some single-row functions that are used exclusively with numeric data. The ROUND, TRUNC, MOD, and ABS functions are addressed in this section. The ROUND and TRUNC function are used to alter the precision or scale of a numeric value. Remember when using either of these functions, a negative value represents digits to the left of a decimal position, and a positive value represents digits to the right. The MOD function returns the remainder portion of a division result, and the ABS function calculates the absolute value of the number provided. Quick Quiz 1. If a –3 is assigned as the position to which a value should be rounded by the ROUND function, on which side of the decimal point will the rounding occur? ANSWER: left side 2. What is the difference between rounding a number and truncating a number? ANSWER: With rounding, a value is affected by the number preceding the indicated position, but when truncating, everything beyond the indicated position is simply set to zero. 3. Can the ROUND function be used with characters strings? ANSWER: No Date Functions Oracle 12c data functions can be used to perform calculations with date values or to format an inputted date value to a format that can be interpreted by the server. The TO_DATE function is widely used in application programs to allow users to enter dates in a variety of formats. The function converts the entered date to Oracle 12c internal storage format. The MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, TO_DATE, ROUND, TRUNC, and CURRENT_DATE functions are addressed in this section. Troubleshooting Tip Highlight that the ROUND and TRUNC functions can be used with both dates and numeric data. Quick Quiz 1. What is a Julian date? ANSWER: The number of days that have passed since January 1, 4712, B.C. 2. What is a format model? ANSWER: The format structure indicating the position and context of each data element 3. How do you indicate that the month is spelled out in a format model? ANSWER: Use the MONTH element 4. If a ROUND function is nested inside an ADD_MONTHS function, which is already nested inside a TO_CHAR function, which function is resolved first? ANSWER: The ROUND function 5. If a ROUND function is nested inside an ADD_MONTHS function, which is already nested inside a TO_CHAR function, how many closing parentheses will be required? ANSWER: 3 Regular Expressions Regular expressions allow the identification of complex patterns within data. Many of the regular expressions extend the capabilities of existing single-row functions. For example, the REGEXP_LIKE function provides more functionality than the LIKE operator introduced earlier. The REGEXP_LIKE and REGEXP_SUBSTR functions are introduced in this section. Other Functions This section covers the NVL, NVL2, TO_CHAR, DECODE, SOUNDEX, and TO_NUMBER functions. The NVL and NVL2 functions are widely used when NULL values may affect the results of a query. The NVL function allows another value to be substituted for a NULL during calculations. However, it cannot be used to display a text message in place of the NULL. The NVL2 function is used for this purpose. The TO_CHAR function can be used with an extensive format model to display numeric and date values. The DECODE function is similar to an IF/THEN statement available in most programming languages. The SOUNDEX function assists in searching data based on phonetic pronunciation. The TO_NUMBER function enables string data to be recognized as a numeric value for purposes of a calculation. Troubleshooting Tip Highlight again that functions can be nested. For example, the ADD_MONTHS function can be nested in a TO_CHAR function. Quick Quiz 1. A NULL value can be replaced by a text message, while displaying a different message if there is not a NULL value, using which function? ANSWER: NVL2 function 2. Which function should be used to replace a NULL value with a numeric value for calculation purposes? ANSWER: NVL 3. Which function should be used to display the current time according to the computer? ANSWER: TO_CHAR 4. Which format model element is used to indicate that minutes should be included in the display? ANSWER: MI 5. What is a floating dollar sign? ANSWER: A dollar sign whose exact position will vary depending on the number of digits displayed DUAL Table The DUAL table is a dummy table created by default in Oracle 12c. The table consists of one column and one row and stores no data. Since a SELECT statement must reference a table in the FROM clause, this table is frequently used to return values from a function when the value is not already stored in a database table. Quick Quiz 1. How many columns are in the DUAL table? ANSWER: 1 2. How many rows are in the DUAL table? ANSWER: 1 Discussion Questions 1. Would it be easier to simply store the necessary manipulated data in the database table rather than use functions? Why or why not? 2. Is there an appropriate time to use the TO_DATE function rather than the TO_CHAR function? Key Terms argument — Values listed within parentheses in a function. case conversion functions — Allow a user to temporarily alter the case of data stored in a column or character string. character functions — Used to change the case of characters or manipulate characters. function — A named PL/SQL block or predefined block of code that accepts zero or more input parameters and returns one value. Julian date — Represents the number of days that have passed between a specified date and January 1, 4712, B.C. manipulation functions — Allow the user to control data (e.g., determine the length of a string, extract portions of a string) to yield a desired query output. single-row functions — Return one row of results for each record processed. Chapter 11 Group Functions At a Glance Instructor’s Notes • Chapter Overview • Chapter Objectives • Instructor Notes • Troubleshooting Tips • Quick Quizzes • Discussion Questions • Key Terms Chapter Overview Unlike single-row functions, group functions return one result per group of rows processed. Data can be grouped using the GROUP BY clause. The actual groups displayed in the results can be specified using the HAVING clause. A HAVING clause serves as a WHERE clause for grouped data. Chapter Objectives After completing this chapter, you should be able to do the following: • Differentiate between single-row and multiple-row functions • Use the SUM and AVG functions for numeric calculations • Use the COUNT function to return the number of records containing non-NULL values • Use COUNT(*) to include records containing NULL values • Use the MIN and MAX functions with nonnumeric fields • Determine when to use the GROUP BY clause to group data • Explain when the HAVING clause should be used • List the order of precedence for evaluating WHERE, GROUP BY, and HAVING clauses • State the maximum depth for nesting group functions • Nest a group function inside a single-row function • Calculate the standard deviation and variance of a set of data, using the STDDEV and VARIANCE functions • Understand the concept of multidimensional analysis • Perform enhanced aggregation grouping with GROUPING SETS, CUBE, and ROLLUP • Use composite columns and concatenated groupings in grouping operations Instructor Notes Group Functions Group functions affect multiple rows. The SUM and AVG functions apply to numeric data only, whereas the COUNT, MAX, and MIN functions can be used for any data types. An asterisk is used as the argument for the COUNT function to include rows containing NULL values. NULL values are ignored by the other functions. The GROUP BY clause is used to group data. If an individual column is listed in the SELECT clause, along with a group function, the column must also be listed in a GROUP BY clause. Troubleshooting Tip Highlight the two new clauses being added to the SELECT statement. Troubleshooting Tip All group functions except the COUNT(*) function ignore NULL values. To include NULL values, nest the NVL function within the group function. Quick Quiz 1. Which group functions can be used on date values? ANSWER: COUNT, MAX, and MIN 2. Which group functions can only be used with numeric values? ANSWER: SUM, AVG, STDDEV, and VARIANCE 3. Which group function will include NULL values? ANSWER: COUNT(*) 4. When is the GROUP BY clause used? ANSWER: When data needs to be grouped—required if an individual column is listed in the SELECT clause along with a group function. 5. Which group functions can be used with character data? ANSWER: COUNT, MAX, and MIN Grouping Data To specify that groups should be created, add the GROUP BY clause to the SELECT statement. When using the GROUP BY clause, remember the following: • If a group function is used in the SELECT clause, then any individual columns (nonaggregate) listed in the SELECT clause must be listed in the GROUP BY clause. • Columns used to group data in the GROUP BY clause do not have to be listed in the SELECT clause. They are included in the SELECT clause only to have the groups identified in the output. • Column aliases cannot be used in the GROUP BY clause. • Results returned from a SELECT statement that include a GROUP BY clause will present the results in ascending order of the column(s) listed in the GROUP BY clause. To present the results in a different sort sequence, use the ORDER BY clause. Restricting Aggregated Output Since a WHERE clause cannot contain group functions, the HAVING clause is used to restrict grouped data. When a SELECT statement contains WHERE, GROUP BY, and HAVING clauses in the same statement, they are executed in order of (1) WHERE (to restrict rows retrieved from the table) clause, (2) GROUP BY (to group data) clause, and (3) HAVING (to restrict group data displayed in the output) clause. When a SELECT statement includes a GROUP BY clause and no ORDER BY clause, the output will be sorted by the column specified by the GROUP BY clause. Troubleshooting Tip Demonstrate the error message that will be returned if a required GROUP BY clause is not included in a SELECT statement. Then demonstrate inappropriate use of the HAVING and WHERE clauses. Quick Quiz 1. What is the difference between a WHERE clause and a HAVING clause? ANSWER: The WHERE clause is used to restrict rows retrieved from a table, whereas the HAVING clause is used to restrict grouped data that has already been retrieved. 2. When is the HAVING clause needed? ANSWER: Anytime grouped output needs to be restricted 3. If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, in which order are they evaluated? ANSWER: WHERE, GROUP BY, HAVING Nesting Functions Group functions can be nested inside other group functions or single-row functions. Single-row functions can also be nested inside group functions. However, unlike single-row functions, group functions can only be nested to a depth of two. When functions are nested, the inner function is always resolved first. Troubleshooting Tip Demonstrate that group functions can only be nested to two levels. Quick Quiz 1. Can a group function be nested inside a single row function? ANSWER: Yes 2. Can a group function be nested inside another group function? ANSWER: Yes 3. Can a single-row function be nested inside a group function? ANSWER: Yes 4. To what maximum depth can group functions be nested? ANSWER: 2 5. In what order are nested functions evaluated? ANSWER: The inner function is evaluated first and then the outer function. Statistical Group Functions The statistical functions STDDEV and VARIANCE are based on a normal distribution. With a normal distribution, it is assumed that 68% of the sample is within one standard deviation of the mean, and 95% is within two standard deviations. The average student who has not had previous exposure to statistical calculations will have difficulty understanding the material in this section. Quick Quiz 1. What is the purpose of the STDDEV function? ANSWER: Used to calculate the standard deviation of a set of data—how close the data values are to the average or mean value for the data set 2. What is the purpose of the VARIANCE function? ANSWER: To determine how wide the data range is 3. The STDDEV and VARIANCE functions can be used with what type of data? ANSWER: Numeric data Enhanced Aggregation for Reporting Extensions to the GROUP BY clause are provided to achieve aggregated results across multiple dimensions or increasing levels of aggregation in one dimension. This functionality is quite popular in OLAP and data warehousing development. The grouping sets, cube, and rollup extensions of the GROUP BY are addressed in this section. Troubleshooting Tip Introduce the concepts involved via a demonstration of an Excel spreadsheet Pivot table. Troubleshooting Tip Oracle 12c introduces new pattern matching features targeted to analyze patterns or trends of a specific column of data across many rows. Pattern matching is an advanced topic, however, students should be made aware of this type of capability. Discussion Questions 1. Why does management prefer to view grouped or summarized data rather than individual rows? 2. If individual columns were allowed in the SELECT statements along with group functions without requiring a GROUP BY clause, what would the output look like? Key Terms dimension — Term used to describe any category used in analyzing data, such as time, geography, and product line. group functions — Process groups of rows, returning only one result per group of rows processed. Also called multiple-row functions and aggregate functions. normal distribution — When a large number of data values are obtained for statistical analysis, they tend to cluster around some “average” value. This dispersion of values is called normal distribution. standard deviation — A calculation used to determine how closely individual values are to the mean, or average, of a group of numbers. statistical group functions — Perform basic statistical calculations for data analysis. Oracle 12c's functions include standard deviation and variance. Chapter 12 Subqueries and MERGE Statements At a Glance Instructor’s Notes • Chapter Overview • Chapter Objectives • Instructor Notes • Troubleshooting Tips • Quick Quizzes • Discussion Questions • Key Terms Chapter Overview There are numerous occasions when a query will be based on some unknown value that is already contained in the database. One option is to first look up the unknown value and then issue the query. The alternative is to create a subquery—one query nested inside another query. Subqueries are widely used in application development. This chapter addresses single-row, multiple-row, and multiple-column subqueries. In addition, the rationales for placing subqueries in various clauses of the outer query are presented. The use of subqueries is demonstrated in subsequent chapters for creating tables, views, and so on. The last topic in the chapter introduces the MERGE statement, which enables processing a group of DML actions with one statement. This was not presented in the earlier DML chapter since it involves more complex query construction. Chapter Objectives After completing this chapter, you should be able to do the following: • Determine when it is appropriate to use a subquery • Identify which clauses can contain subqueries • Distinguish between an outer query and a subquery • Use a single-row subquery in a WHERE clause • Use a single-row subquery in a HAVING clause • Use a single-row subquery in a SELECT clause • Distinguish between single-row and multiple-row comparison operators • Use a multiple-row subquery in a WHERE clause • Use a multiple-row subquery in a HAVING clause • Use a multiple-column subquery in a WHERE clause • Create an inline view using a multiple-column subquery in a FROM clause • Compensate for NULL values in subqueries • Distinguish between correlated and uncorrelated subqueries • Nest a subquery inside another subquery • Process multiple DML actions with a MERGE statement Instructor Notes Subqueries and Their Uses A subquery is necessary when a search is based on an unknown value that is contained within a database table. There are three basic types of subqueries: single-row, multiple-row, and multiple- column. Multiple-row and multiple-column subqueries can be nested in FROM, WHERE, and HAVING clauses, while a single-row subquery can be nested in SELECT, FROM, WHERE, and HAVING clauses. When a subquery is used in a FROM clause, it creates a temporary table that can be referenced by other clauses of the same SELECT statement. A subquery in a FROM clause is usually referred to as an inline view. If a subquery is being used to determine the value to be used in a comparison, it must appear on the right side of the comparison operator. In addition, a subquery must be enclosed in a set of parentheses to separate it from the outer query. A subquery cannot contain an ORDER BY clause; any sorting should be performed in the outer query. Single-Row Subqueries A single-row subquery can only return a single data element. A single-row subquery is specified by the use of a single-row operator. Valid single row operators include any of the mathematical comparison operators. A single-row subquery is specified in the WHERE clause when the comparison is not based on a group condition. If the subquery results are used for comparison against grouped data, the subquery must be nested in a HAVING clause. Troubleshooting Tip Demonstrate the use of a subquery in a WHERE clause, and then use the same subquery in a HAVING clause and discuss the error message returned. Quick Quiz 1. How many columns can be returned by a single-row subquery? ANSWER: One 2. A single-row subquery can be used in which clauses of the outer query? ANSWER: SELECT, FROM, WHERE, and HAVING clauses 3. What type of comparison operators can be used with a single-row subquery? ANSWER: Any comparison operator (except ANY and ALL operators); normally uses mathematical comparison operators 4. Can a GROUP BY be included in a single-row subquery? ANSWER: Yes 5. When should a single-row operator be included in a HAVING clause of the outer query? ANSWER: When the result will be compared against a group function or grouped data Multiple-Row Subqueries A multiple-row subquery can return more than one row of results but only one column of output. The most commonly used multiple-row operator is the IN comparison operator. In addition, the ANY and ALL operators can be used with various mathematical operators to specify how the results of the subquery should be evaluated. Note how the MIN and MAX operators can be used to accomplish similar tasks that the ANY and ALL operators are used to resolve. Troubleshooting Tip Create a multiple-row subquery and then re-execute using variations of the ANY and ALL operators and discuss the results. Quick Quiz 1. How many columns can be returned by a single-row subquery? ANSWER: One 2. A multiple-row subquery is most commonly used in which clauses of the outer query? ANSWER: WHERE and HAVING clauses 3. What type of comparison operators can be used with a multiple-row subquery? ANSWER: The IN comparison operator and the ANY and ALL operators combined with mathematical operators 4. If the “greater than” comparison operator is used with a multiple-row subquery, what type of result will be returned? ANSWER: An error message 5. Which of the variations of the ANY operator is the same as using the IN comparison operator? ANSWER: =ANY Multiple-Column Subqueries A multiple-column subquery is a subquery that can return several columns, as well as several rows, in its results. When a multiple-column subquery is used in a FROM clause, it is referred to an inline view. When using a multiple-column subquery for comparison purposes, the column list specified in the subquery must be in the same order as the column list specified in the WHERE or HAVING clause. The column list included in the WHERE or HAVING clause must be enclosed in parentheses. Quick Quiz 1. Multiple-column subqueries can be used in which clauses of the outer query? ANSWER: FROM, WHERE, or HAVING clauses 2. What is an inline view? ANSWER: A temporary table created by using a multiple-column subquery in the FROM clause of the outer query 3. Which operator is used when using the results of a multiple-column subquery for comparison in the outer query? ANSWER: IN 4. The column listed provided in a WHERE clause that is used for comparison to a multiple-column subquery must be enclosed in ________. ANSWER: parentheses 5. How do you reference columns returned through an inline view? ANSWER: Specify a table alias for the subquery NULL Values Because NULL values cannot be used for comparison purposes, a NULL value returned by a subquery will not match to any values in the outer query. If it is possible that NULL values may generate erroneous results from the outer query, the NVL function should be used in the subquery to provide a substitute for the NULL value. A correlated subquery references a column contained in the outer query. During processing, the outer query is executed, and then the inner query is executed once for each row retrieved by the outer query. With an uncorrelated subquery, the inner query is executed first, and the value is returned to the outer query for processing. Quick Quiz 1. What is the result if a subquery returns a NULL value to the outer query? ANSWER: No rows will be returned by the outer query. 2. If a query must be based on whether a NULL value exists, what function can be included in the inner and outer queries to ensure the correct processing will occur? ANSWER: NVL function 3. How is an uncorrelated subquery processed? ANSWER: The inner query is executed, first and the result is returned to the outer query, which is then executed. 4. How is a correlated subquery processed? ANSWER: The outer query is executed first; the inner query is executed once for every row processed by the outer query. 5. How can you identify a correlated subquery? ANSWER: The subquery will reference a column in the outer query. Nested Subqueries Subqueries can be nested in a WHERE or HAVING clause to a maximum depth of 255. There is no depth limit when the subqueries are nested in a FROM clause. In all cases, the innermost query is executed first, and the result is passed to the next level query. Each subquery must be complete with the minimum of a SELECT clause and a FROM clause for each query. The WITH clause is an alternative to using subqueries that offers potential improvements in statement readability and processing efficiency. Troubleshooting Tip Demonstrate how triple nested subqueries work by first executing the individual subqueries, and then nest the subqueries and execute the final version. Quick Quiz 1. Subqueries in a WHERE clause can be nested to a maximum depth of ________. ANSWER: 255 2. When subqueries are nested to a depth of four, which subquery will be executed first? ANSWER: Innermost subquery 3. What is the maximum depth for nesting subqueries in a FROM clause? ANSWER: There is no limit. Discussion Questions 1. Identify a scenario in which a user is forced to use a subquery. 2. What are the alternatives to using a subquery? Joins? Key Terms correlated subquery — A subquery that references a column in the outer query. The outer query executes the subquery once for every row in the outer query. multiple-column subquery — A nested query that returns more than one column of results to the outer query. It can be listed in the FROM, WHERE, or HAVING clause. multiple-row subquery — Nested queries that return more than one row of results to the parent query. They are most commonly used in WHERE and HAVING clauses and require multiple- row operators. single value — The output of a single-row subquery. single-row subquery — A nested subquery that can return to the outer query only one row of results that consists of only one column. The output of a single-row subquery is a single value. uncorrelated subquery — A subquery that follows this method of processing: the subquery is executed, then the results of the subquery are passed to the outer query, and finally the outer query is executed. Chapter 13 Views At a Glance Instructor’s Notes • Chapter Overview • Chapter Objectives • Instructor Notes • Troubleshooting Tips • Quick Quizzes • Discussion Questions • Key Terms Chapter Overview Organizations use views as an added layer of security by restricting the data that users can access. In addition, views can be used to simplify complex data navigation or searches for novice users. A view is a pseudotable. Although it is a database object, it does not actually contain data. The subquery used to generate the view is re-executed each time the view is referenced. The view can be referenced in the FROM of a SELECT statement or in DML commands, just like a table. Students will need the privilege to create views. Chapter Objectives After completing this chapter, you should be able to do the following: • Create a view, using the CREATE VIEW command or the CREATE OR REPLACE VIEW command • Know how to employ the FORCE and NO FORCE options • State the purpose of the WITH CHECK OPTION • Explain the effect of the WITH READ ONLY option • Update records in a simple view • Re-create a view • Explain the implication of an expression in a view for DML operations • Perform an update of a record in a complex view • Identify problems associated with adding records to a complex view • Identify the key-preserved table underlying a complex view • Drop a view • Explain inline views and the use of ROWNUM to perform a “Top-N” analysis • Create a materialized view to replicate data Instructor Notes Creating a View A view can be created using the CREATE VIEW command. Because a view cannot be altered, the CREATE OR REPLACE VIEW command is used to re-create an existing view. The command also has the FORCE option available in the event the underlying table(s) is not available when the view is created. A subquery is used to identify the underlying table(s) and columns. The subquery is identified by the AS keyword. If the WITH CHECK OPTION is specified, then modifications cannot be made to the data that would subsequently make it inaccessible to the view. If the WITH READ ONLY option is included, no DML operations will be permitted. A simple view is based on only one table and cannot include any arithmetic expressions, grouped data, or group functions. Any DML operation is permitted on a simple view unless it violates a constraint on the view (WITH READ ONLY, WITH CHECK OPTION) or underlying table (PRIMARY KEY, CHECK, etc). Troubleshooting Tip Create a view and include the WITH CHECK OPTION. Then demonstrate what occurs if the user attempts to violate the constraint. Quick Quiz 1. What clause cannot be included in the subquery that is used to create a simple view? ANSWER: A GROUP BY clause 2. What is the purpose of the WITH CHECK OPTION? ANSWER: Prevents DML operations that will make the data inaccessible after the change 3. If you realized that you forgot to include a column in a view, how can you go back and make the change? ANSWER: That is not possible; the view will need to be replaced with a new view. 4. What DML operations are permitted on views created with the WITH READ ONLY keywords? ANSWER: No DML operations are permitted. 5. How can new names be assigned to the columns extracted from the underlying table, without specifying a column list in the CREATE VIEW clause? ANSWER: Assign column aliases in the subquery Creating a Complex View A complex view is a view based on one or more underlying tables and may contain arithmetic expressions, grouped data, or group functions. Depending on the structure of the view, certain DML operations will not be allowed. When a complex view is based on more than one table, no DML operations will be allowed on the non-key-preserved table. Troubleshooting Tip Create a simple view and demonstrate basic DML operations. Re- create the view and include group functions in the new view. Demonstrate that certain DML options will not be allowed. Quick Quiz 1. What distinguishes a complex view from a simple view? ANSWER: A complex view may have more than one underlying table, grouped data, or group functions. 2. Why distinguish between simple and complex views? ANSWER: There are restrictions on the DML operations that can be performed on complex views. 3. Can rows be deleted through a complex view that contains grouped data? ANSWER: No 4. Can rows be updated through a complex view that contains a group function? ANSWER: No 5. What is a key-preserved table? ANSWER: It is the underlying table containing the column(s) the view is using as its primary key. Dropping a View As with other database objects, a view is removed from a database using the DROP command. Dropping a view does not affect the data contained in the underlying table. Quick Quiz 1. What command is used to remove a view from the database? ANSWER: DROP VIEW command 2. If a view is dropped, is the underlying table(s) dropped also? ANSWER: No 3. If a view is dropped, what happens to the data previously displayed by the view? ANSWER: Nothing, the data will remain in the underlying tables. Creating an Inline View Unlike simple and complex views, an inline view cannot be referenced at a later time. Instead, it creates a temporary “table” that can only be referenced for the duration of the SELECT statement in which it was created. Oracle 12c introduced the CROSS and OUTER APPLY methods as additional options for performing join operations. These methods allow a column of the joining table to be used to produce the result set of the inline view. An inline view is frequently used to perform “Top-N analysis.” Top-N analysis is performed by including an ORDER BY clause in the subquery. Although an ORDER BY clause technically cannot be used in a subquery, it is permitted in views. When the subquery is included in the FROM clause of a SELECT statement, it is categorized as a view. The ROWNUM pseudocolumn is then used to extract the desired rows. Oracle 12c introduced a new row_limiting_clause which simplifies the TOP-N queries even further. Troubleshooting Tip Demonstrate that the ROWNUM is not changed if a table is simply sorted. It is when the sorted data is passed to an outer query that the ROWNUM will be in the necessary sequence to perform Top-N analysis. Quick Quiz 1. Why is an inline view different from a simple or complex view? ANSWER: It is temporary and cannot be referenced at a later time. 2. When performing “Top-N analysis” to find the three highest values in a column, what is the necessary sort sequence? ANSWER: Descending 3. If you are looking for the three cheapest books in the BOOKS table, what order should the data be sorted in to extract these books using “Top-N analysis”? ANSWER: Ascending 4. What command is used to remove an inline view from the database? ANSWER: None, it is temporary and only exists during the execution of the SELECT statement. 5. Why is an inline view required to perform “Top-N analysis”? ANSWER: The ROWNUM must be re-sequenced, and an ORDER BY clause only is not sufficient. Creating a Materialized View A materialized view allows you to store the data retrieved by the view query and lets you reuse this data without executing the view query again. In other words, a materialized view allows the replication of data. These are often referred to as snapshots, as they take a picture or capture a set of data at a specific point in time. Discussion Questions 1. Select an industry and describe how a view can be used to provide a degree of security. 2. Provide examples of some complex SQL statements that can be simplified through the use of views. Key Terms inline view — A temporary view of underlying database tables that exists only while a command is being executed. It is not a permanent database object and cannot be referenced again by a subsequent query. key-preserved table — A table that contains the primary key that a view uses to uniquely identify each record displayed by the view. materialized view — Stores the data retrieved by the view query non-key-preserved table — Does not uniquely identify the records in a view. pseudotables — Created to present a particular “view” of a database’s contents. Does not actually store data but is referenced like a table in SQL statements. “TOP-N” analysis — When an inline view and a pseudocolumn ROWNUM are merged together to create a temporary list of records in a sorted order, and then the top “n,” or number of records, are retrieved. views — Display data in the underlying base tables. Views are used to provide a shortcut for users not having SQL training or to restrict users’ access to sensitive data. Views are database objects, but they do not store data. Instructor Manual for Oracle 12c: SQL Joan Casteel 9781305251038