Oracle Database 12c SQL 1z0-071 Exam Questions with Experts Answers Updated Recently

A view is a virtual table based on a SQL query.

A. This is incorrect because a SELECT statement querying a view can contain a WHERE clause, regardless of the view’s defining query. C. This is incorrect because views can join tables from different schemas, not just the same schema. B. Correct. The rows inserted into a base table via a view remain in the table even if the view is dropped because the view is just a window to the data in the base tables. D. Correct. Views do not require storage space other than for the definition of the view in the data dictionary, hence they have no segment. E. Incorrect. Views do not have object numbers because they are not database objects that occupy physical space. F. Correct. You can create a view that references non-existent tables; such a view would be considered invalid until the base table is created.

The Oracle Database Concepts guide provides information about views and their characteristics.

A. True. When using set operators, the number and order of columns must be the same in all SELECT statements involved in the query. The data types of those columns must also be compatible.

E. True. The FOR UPDATE clause cannot be specified in a subquery or a SELECT statement that is combined with another SELECT statement by using a set operator.

B is incorrect because the column names do not need to match; only the number and data types of the columns must be compatible. C is incorrect because only the final SELECT statement in the sequence of UNION/INTERSECT/MINUS operations can have an ORDER BY clause. D is incorrect because, as of Oracle 12c, UNION ALL can be used with CLOB columns, but UNION, INTERSECT, and MINUS cannot.

When the TRUNCATE TABLE command is used:

• C. It retains the indexes defined on the table. This is true. The TRUNCATE TABLE operation does not drop the indexes on the table; it only removes all rows from the table.
• D. It retains the integrity constraints defined on the table. This is correct. The TRUNCATE TABLE operation does not affect the integrity constraints; it retains them.

Options A, B, E, and F are incorrect:

• A is incorrect because the TRUNCATE TABLE operation cannot be rolled back as it is a DDL operation and not a DML operation like DELETE.
• B is incorrect because TRUNCATE TABLE does not drop any triggers; they remain in place.
• E is incorrect as TRUNCATE TABLE may or may not release the space depending on whether the REUSE STORAGE clause is used.
• F is incorrect because FLASHBACK TABLE cannot be used to retrieve data after a TRUNCATE TABLE operation since it does not generate redo logs that FLASHBACK relies upon.

To calculate the number of days between two dates in Oracle SQL, you can simply subtract the earlier date from the later date, and Oracle will return the number of days between them.

Option A uses SYSDATE to get the current date and TO_DATE to convert the string '01-JANUARY-2019' to a date using the default date format, which matches 'DD-MON-RR'. The subtraction here will correctly give the number of days between these two dates.

Option C is similar to A but includes the ROUND function, which is used to round the number to the nearest whole number. Since the difference between two dates in Oracle SQL is a decimal value representing the number of days and fractional days, rounding will give us the whole number of days, which is what we want.

Options B, D, and E are incorrect because they either use an invalid date format (the default format is 'DD-MON-RR', not 'DD/MONTH/YYYY' or 'DD-MON-YYYY'), perform invalid operations (such as subtracting a string from a date), or misuse SYSDATE.