Free Oracle Database SQL 1Z0-071 Exam Questions

Regarding privileges in Oracle:

Option A: Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.

Dropping a user will revoke all privileges that user has granted to others.

Option B: Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.

Since FINANCE granted SELECT on SCOTT.EMP to FIN_CLERK with grant option, revoking the privilege from FINANCE will also cascade and revoke it from FIN_CLERK.

Options C, D, and E are incorrect because:

Option C: User FINANCE can grant privileges it owns, but CREATE SESSION is a system privilege; unless FINANCE has the 'WITH ADMIN OPTION' for CREATE SESSION, it cannot grant this privilege.

Option D: User FIN_CLERK can only grant privileges to others if it has been given those privileges with the 'WITH GRANT OPTION' and it was not given in this case.

Option E: User FINANCE can grant privileges on SCOTT.EMP to other users only if it has those privileges with 'WITH GRANT OPTION'. If the privilege was just SELECT with GRANT OPTION, it cannot grant ALL privileges.

For the given SQL commands:

Option A: ROLLBACK;

A ROLLBACK will undo all changes made in the transaction up to the last COMMIT.

Option C: ROLLBACK TO SAVEPOINT post_insert;

ROLLBACK TO SAVEPOINT will undo changes back to the named savepoint, which in this case is after the first insert.

The function ADD_MONTHS(hire_date, 6) adds 6 months to the hire_date. The function NEXT_DAY(date, 'day_name') finds the date of the first specified day_name after the date given. In this case, 'MONDAY' is used to find the date of the first Monday after the hire_date plus 6 months.

Option A is correct as it accurately composes both ADD_MONTHS and NEXT_DAY functions to fulfill the requirement.

Options B, C, and D do not provide a valid use of the NEXT_DAY function, either because of incorrect syntax or incorrect logic in calculating the required date.

The Oracle Database SQL Language Reference for 12c specifies how these date functions should be used.

When using the TRUNCATE TABLE command in Oracle, several aspects of the table's structure and associated database objects are impacted. Here's an explanation of each option:

A: Incorrect. TRUNCATE TABLE does not drop triggers associated with the table; they remain defined.

B: Correct. Indexes on the table are retained and not dropped when you truncate a table. However, if the index is a domain index, it may be dropped depending on its type.

C: Correct. Integrity constraints such as primary keys, foreign keys, etc., are retained unless they are on a disabled state where truncation can lead to constraint being dropped.

D: Incorrect. A TRUNCATE TABLE operation cannot be rolled back. It is a DDL (Data Definition Language) operation and commits automatically.

E: Incorrect. The TRUNCATE TABLE operation deallocates the space used by the data unless the REUSE STORAGE clause is specified.

F: Incorrect. TRUNCATE TABLE operation removes all the rows in a table and does not log individual row deletions, thus FLASHBACK TABLE cannot be used to retrieve the data.

C: True. To display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE, you would need to use a subquery to first calculate the average PROD_LIST_PRICE and then use that result to compare each product's list price to the average.

E: True. Displaying products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products and whose status is orderable would require a subquery. The subquery would be used to determine the average PROD_LIST_PRICE, and then this average would be used in the outer query to filter the products accordingly.

Subqueries are necessary when the computation of a value relies on an aggregate or a result that must be obtained separately from the main query, and cannot be derived in a single level of query execution.

Reference: Oracle's SQL documentation provides guidelines for using subqueries in scenarios where an inner query's result is needed to complete the processing of an outer query.

A: True, the name of a column can be changed in Oracle using the ALTER TABLE ... RENAME COLUMN command.

B: False, you cannot change a column's data type from NUMBER to VARCHAR2 if the table contains data, unless the change does not result in data loss or inconsistency.

C: True, it is possible to change a DATE data type column to TIMESTAMP because TIMESTAMP is an extension of DATE that includes fractional seconds. This operation is allowed if there is no data loss.

D: False, any column that is not part of a primary key or does not have a non-deferrable constraint can generally be dropped unless it contains data that does not allow for such a change.

E: False, the DEFAULT clause can be added to a column provided there is no data that contradicts the default value or it doesn't violate any constraints.

These statements are verified against the Oracle Database 12c SQL documentation, specifically the sections on data types, the ALTER TABLE command, and the use of literals in SQL expressions.

A: LAST_DAY does not only return the last day of the current month; it returns the last day of the month based on the date argument passed to it, which may not necessarily be the current month. Thus, statement A is incorrect.

B: CEIL requires a numeric argument and returns the smallest integer greater than or equal to that number. Thus, statement B is incorrect.

C: ADD_MONTHS function adds a specified number of calendar months to a date. This statement is correct as per the Oracle documentation.

D: ADD_MONTHS can work with a character string if the string can be implicitly converted to a DATE, according to Oracle SQL data type conversion rules. Therefore, statement D is correct.

E: LAST_DAY does not specifically return the last day of the previous month; it returns the last day of the month for any given date. Thus, statement E is incorrect.

F: CEIL returns the smallest integer greater than or equal to the specified number, not the largest integer less than or equal to it. Hence, statement F is incorrect.

G: LAST_DAY returns the last day of the month for the date argument passed to the function, which aligns with the definition in Oracle's SQL reference. Therefore, statement G is correct.

Based on the given PRODUCTS table and the SQL queries provided:

Query 1: Excludes rows where prod_list is 10 or 20 and category_id is 1.

Query 2: Includes rows where prod_list is neither 10 nor 20 and category_id is 1.

Query 3: Excludes rows where prod_list is both 10 and 20 (which is not possible for a single value) and category_id is 1.

The correct answer is A, queries 1 and 3 will produce the same result. Both queries exclude rows where prod_list is 10 or 20 and include only rows from category_id 1. The NOT IN operator excludes the values within the list, and <> ALL operator ensures that prod_list is not equal to any of the values in the list, which effectively excludes the same set of rows.

Query 2, using the <> ANY operator, is incorrect because this operator will return true if prod_list is different from any of the values in the list, which is not the logic represented by the other two queries.

The query uses the syntax GROUP | BY 1 which is not correct. The pipe symbol | is not a valid character in the context of the GROUP BY clause. Additionally, when using GROUP BY with a number, it refers to the position of the column in the SELECT list, which should be written without a pipe symbol and correctly as GROUP BY 1. Since the syntax is incorrect, the database engine will return an error.

Regarding INTERVAL data types in Oracle Database 12c:

A . INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years. This is true. The INTERVAL YEAR TO MONTH data type stores a period of time using years and months.

E . INTERVAL DAY TO SECOND columns support fractions of seconds. This is true. The INTERVAL DAY TO SECOND data type can store days, hours, minutes, seconds, and fractional seconds.

Options B, C, D, and F are incorrect:

B is incorrect because data types between INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH are not compatible.

C is incorrect as it incorrectly limits INTERVAL YEAR TO MONTH to a single year.

D is incorrect; the YEAR field can be negative to represent a past interval.

F is incorrect as INTERVAL YEAR TO MONTH supports intervals that can span multiple years, not just annual increments.

Regarding Oracle join and ANSI join syntax:

B . The Oracle join syntax supports the creation of a Cartesian product of two tables. This is true. In Oracle, if you list tables in the FROM clause without a join condition, it creates a Cartesian product.

C . The SQL:1999 compliant ANSI join syntax supports natural joins. This is true. ANSI syntax supports natural joins, which join tables based on columns with the same names in the joined tables.

F . The SQL:1999 compliant ANSI join syntax supports the creation of a Cartesian product of two tables. This is true. The ANSI standard allows for Cartesian products when tables are listed in the FROM clause without a join condition.

Options A, D, E, and G are incorrect:

A is incorrect because the Oracle join syntax supports all types of joins, including right outer joins.

D is incorrect because Oracle's proprietary join syntax does not use the term 'natural join.'

E is incorrect because there is no inherent performance difference between Oracle join syntax and ANSI join syntax; performance depends on how the query is written and how the database optimizer handles it.

G is incorrect for the same reason as E.

The statement provided uses the UNION operator, which combines the results of two or more queries into a single result set. However, UNION also eliminates duplicate rows. Both queries in the union are selecting the same values 1 and ' John', thus the result is one row because duplicates will be removed.

SELECT 1 AS id, ' John' AS first name FROM DUAL UNION SELECT 1 , ' John' AS name FROM DUAL ORDER BY 1;

Given that both SELECT statements are actually identical in the values they produce, despite the differing column aliases, the UNION will eliminate one of the duplicate rows, resulting in a single row being returned.

Oracle Documentation on UNION: SQL Language Reference - UNION

In Oracle SQL, the WITH CHECK OPTION clause in a CREATE VIEW statement ensures that all data manipulation statements performed through the view must conform to the view's defining query.

Option A: DELETE FROM emp80 WHERE department_id = 90;

This will violate the CHECK constraint because the WHERE clause condition does not meet the view's restriction (department_id = 80). This statement attempts to delete rows that the view is not supposed to have access to (since it should only include rows where department_id is 80).

Options B, C, and D do not violate the CHECK constraint because:

Option B: A SELECT statement does not modify data, so it does not violate the CHECK OPTION.

Option C: Selecting rows with department_id = 80 is consistent with the view's definition.

Option D: There is a syntax error, but assuming it meant to set department_id to 80 where it is currently 90, it still would not violate the CHECK constraint because the CHECK OPTION on a view does not prevent updates that do not change the rows to fall outside the view's filter. However, since department_id is supposed to be 80 as per the view definition, this update operation doesn't make logical sense, as there should be no rows with department_id = 90 to update.

A: This statement is false. A table can only have one primary key, although the primary key can consist of multiple columns (composite key).

B: This statement is true. A table can have multiple foreign keys referencing the primary keys of other tables or the same table.

C: This statement is false. A NUMBER column without data is NULL, not zero.

D: This statement is false. A column definition must specify exactly one data type.

E: This statement is true. A VARCHAR2 column without data defaults to NULL, not an empty string.

A: True. TRUNCATE is generally faster than DELETE for removing all rows from a table because TRUNCATE is a DDL (Data Definition Language) operation that minimally logs the action and does not generate rollback information. TRUNCATE drops and re-creates the table, which is much quicker than deleting rows one by one as DELETE does, especially for large tables. Also, TRUNCATE does not fire triggers.

Oracle documentation specifies that TRUNCATE is faster because it doesn't generate redo logs for each row as DELETE would.

TRUNCATE cannot be rolled back once executed, since it is a DDL command and does not generate rollback information as DML commands do.