New Release 1z0-071 Oracle PL Questions

The COUNT function is one of the most commonly used aggregate functions in SQL for determining the number of items in a group. Here's why the correct answers are B and C:

• A: Incorrect. COUNT can be used with any data type, not just NUMBER. It counts rows without regard to data type.
• B: Correct. COUNT(DISTINCT inv_amt) counts the number of unique non-null values in the column inv_amt. It excludes duplicates and ignores NULL values.
• C: Correct. COUNT(*) counts all rows in a table, including those with duplicates and those with NULLs in any column. It is a total row count regardless of content.
• D: Incorrect. You can use a WHERE clause with COUNT. The WHERE clause filters rows before counting, which is standard in SQL.
• E: Incorrect. COUNT(inv_amt) counts the rows where inv_amt is not NULL. Rows with NULL in the inv_amt column are not counted.

In SQL, the ORDER BY clause can refer to columns by their alias defined in the SELECT clause or by their positional number in the SELECT list. It's important to understand that after a UNION, the column names in the ORDER BY clause refer to the first SELECT statement's column names and aliases:

• Option A: ORDER BY 2, 1This is correct because it refers to the second and first columns in the first SELECT clause, which correspond to the aliases "Last Name" and cust_id, respectively.
• Option E: ORDER BY "Last Name"This is correct because "Last Name" is a valid alias defined in the first SELECT clause.

The other options fail for the following reasons:

• Option B: "CUST_NO" is not recognized in the ORDER BY clause because it's not an alias used in the first SELECT statement.
• Option C: Incorrect because 2, cust_id mixes positional reference with a column name that doesn't apply to both SELECT statements consistently.
• Option D: ORDER BY CUST_NO fails because CUST_NO is not an alias in the first SELECT clause.

Roles are named collections of privileges in Oracle databases.

A. False. Roles cannot be assigned to other roles using the ALTER ROLE statement.

B. False. Roles can be assigned to both users and other roles.

C. True. Roles are assigned to users using the ALTER USER statement, but this is not the only method.

D. True. A single role can be assigned to multiple users, simplifying the management of user privileges.

E. True. A single user can be assigned multiple roles.

F. False. Privileges are not assigned to a role using the ALTER ROLE statement.

G. True. Privileges are assigned to a role using the GRANT statement.

C. True. The error occurs because the alias "Department" used in the GROUP BY clause is enclosed in double quotes, which makes it case-sensitive. However, the column deptno is not originally created with double quotes in the table definition, so you cannot refer to it with a case-sensitive alias in the GROUP BY clause. Oracle interprets "Department" as a string literal, not a column alias, in the GROUP BY clause.

A is incorrect because you can use an alias in an ORDER BY clause. B is incorrect because an alias can contain space characters if it is quoted. D is incorrect because an alias does not always have to be specified in quotes, only when it includes special characters or spaces or if it is case-sensitive.