1z0-071 Exam Dumps - Oracle Database 12c SQL

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.

In the SQL statement provided, the error is caused by the GROUP BY clause. In Oracle SQL, when using GROUP BY, the expression in the GROUP BY clause must match exactly the expression in the SELECT list. The GROUP BY clause should reference the column name dept_no as mentioned in the SELECT list, not the alias department_id which is defined within the same SELECT statement.

The corrected SQL statement should be:

SELECT dept_no AS department_id, MAX(salary) As max_sal FROM employees WHERE salary > 10000 GROUP BY dept_no -- Changed from 'department_id' to 'dept_no' ORDER BY max_sal;

References:

Oracle Documentation on GROUP BY clause: GROUP BY Clause

Regarding primary and foreign key constraints:

A. A table can have only one primary key but multiple foreign keys. This is true. A table is constrained to have only one primary key, which can consist of multiple columns, but can have several foreign keys referencing primary keys in other tables.

C. Primary key and foreign key constraints can be defined at both the column and table level. True. Constraints can be defined inline with the column definition or separately at the end of the table definition.

E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted. This is also true if the foreign key is defined with the ON DELETE CASCADE option.

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

B is incorrect because if a parent row is deleted, the child rows cannot remain without violating the integrity unless the foreign key is defined with ON DELETE SET NULL or similar behavior.

D is incorrect because both primary and foreign key constraints can be defined at both levels.

F is incorrect as the names of the foreign key columns do not need to match the primary key column names.

G is incorrect as a table can have multiple foreign keys.

When creating a new table with a query, the statements that are true are:

A. SALES1 is created with 1 row.This is incorrect actually; the given query with WHERE 1=1 will retrieve all rows from the sales table, not just one row. Therefore, SALES1 will be created with the same number of rows as in the SALES table, assuming there are no WHERE clause conditions limiting the rows.

D. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.This is true. When a table is created using a CREATE TABLE AS SELECT statement, the NOT NULL constraints on the columns in the selected columns are preserved in the new table.

Options B and C are incorrect:

B is incorrect because the primary key and unique constraints are not carried over to the new table when using the CREATE TABLE AS SELECT syntax.

C is incorrect in the context of this scenario. However, if interpreted as a standalone statement (ignoring A), C would be the correct description of the outcome since WHERE 1=1 will not filter out any rows.

The ORDER BY clause with a CASE statement allows for conditional sorting based on the results of the CASE expression.

A. This is not true, as the 'A' in the THEN clause would cause the employees earning the maximum salary to be sorted at the top, but it does not determine the order in which their last names would appear.

B. This is true, the 'A' in the THEN clause will result in all maximum salary earners to be sorted at the top, and since 'A' comes before any last names alphabetically, they will be in ascending order.

C. This is not true, as the ELSE clause sorts the remaining employees by last_name but does not specify an order; hence, it defaults to ascending which contradicts the second part of the ORDER BY clause which specifies DESC.

D. This is not true, as the order of the remaining employee names is specified by the ORDER BY clause, which states last_name DESC.

E. This is true, after the employees with the maximum salary are sorted, the remaining employees' names will appear in descending order due to the last_name DESC in the ORDER BY clause.

F. This statement is not true as the specified 'A' in the CASE statement provides a definite order for employees earning the maximum salary.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CASE Expression"