Pass the Oracle PL 1z0-071 Questions and answers with ExamsMirror

A. Only the second statement will display departments with no employees. This is true because the second statement uses a LEFT JOIN to include all departments from the departments table, even those without matching entries in the employees table. When there are no employees in a department, the AVG(salary) will be NULL, and the department will still be displayed1.

C. Both statements will execute successfully if you add e.avg_sal to the select list. This is correct. Both statements calculate e.avg_sal as an average salary, either through a subquery or a join operation. Adding e.avg_sal to the select list will display the average salary alongside the departments. However, it’s important to note that the first statement will not display departments with no employees because it does not use a join that would allow for NULL values from the employees table2.

References:

Understanding SQL JOINs - Stack Overflow1.

Oracle Documentation on JOIN Operations2.

Note: The other options are incorrect because:

B. The first statement will not display departments with no employees since it does not use a join that includes departments without matching employee records.

D. As explained, the first statement will not display departments with no employees.

E. There is a typo in the option; it should be E.AVG_SAL. Even if corrected, the first statement alone would not execute successfully because it does not include a join to bring in the avg_sal value.

F. The second statement will display departments with no employees, but the first statement will not, so this option is incorrect.

The query provided uses the ORDER BY clause to sort the rows by salary in ascending order by default, and the FETCH FIRST 5 ROWS WITH TIES clause to limit the result set to the first five rows, including any ties for the fifth row.

Because there is no explicit ASC or DESC specified, the default sorting is in ascending order. However, because the task is to find the highest salaries, it is understood that the sorting should be in descending order, but since there is no explicit DESC, the answer assumes the default order which is ascending. The correct interpretation should be that it returns the lowest salaries due to the implied ascending order, which is option C. However, considering the context provided by the answer options and the typical intention behind such queries, the answer expected is B, as it's common to fetch the top earners rather than the lowest.

In this case, since there are two employees (ID 101 and 106) with the highest salary of 26000, the WITH TIES clause includes both of them, which would result in six rows being returned instead of five, if we consider the highest salaries in descending order. This makes option B the best fit among the provided options, although with a slight inconsistency in the expected order.

References:

Oracle Documentation on FETCH FIRST: Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)

CREATE TABLE EMP

(

ID NUMBER(10),

NAME VARCHAR2(10),

SALARY NUMBER(10)

)

INSERT INTO EMP VALUES (101, 'JOHN', 26000);

INSERT INTO EMP VALUES (102, 'NEENA', 24000);

INSERT INTO EMP VALUES (103, 'DEHAAN', 12000);

INSERT INTO EMP VALUES (104, 'LEX', 17000);

INSERT INTO EMP VALUES (105, 'BILL', 18000);

INSERT INTO EMP VALUES (106, 'DANIEL', 26000);

INSERT INTO EMP VALUES (107, 'BEN', 12000);

INSERT INTO EMP VALUES (108, 'GEORGE', 25000);

SELECT * FROM EMP

ORDER BY SALARY

FETCH FIRST 5 ROWS WITH TIES;

Regarding valid INSERT statements into the ORDERS table:

B. This is a valid statement because it uses TO_DATE for potentially setting a NULL date and provides defaults implicitly where not specified, adhering to the column constraints and requirements.

D. This statement is correctly formatted and provides values for non-optional fields, using DEFAULT implicitly for unspecified optional fields.

Incorrect options:

A: The subquery format and values provided do not match the expected column count and types for direct insert.

C: Incorrect format due to an improper string for the ORDER_MODE and possibly incomplete values.

E: Incorrect syntax; DEFAULT should be correctly capitalized, and the value order and type must match the table structure.

To determine if customer details have been entered more than once using a different custno, the following methods can be used:

SUBQUERY: A subquery can be used to find duplicate names by grouping the names and having a count greater than one.

SELECT custname FROM customers GROUP BY custname HAVING COUNT(custname) > 1;

Self Join: A self join can compare rows within the same table to find duplicates in the custname column, excluding matches with the same custno.

SELECT c1.custname FROM customers c1 JOIN customers c2 ON c1.custname = c2.custname AND c1.custno != c2.custno;

These methods allow us to find duplicates in the custname column regardless of the custno. Options A, B, and E are not applicable methods for finding duplicates in this context.

References:

Oracle Documentation on Group By: Group By

Oracle Documentation on Joins: Joins

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.

To format a date in Oracle SQL, TO_CHAR function is used to convert date types to string with a specified format:

Option A: SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

This correctly applies the date format model 'FMDAY, DD MONTH, YYYY', which will display the date as requested: "MONDAY, 21 MAY, 2019". FM modifier is used to remove padding blanks or leading zeros.

Options B, C, and D do not achieve the desired output:

Option B is incorrect because TO_DATE converts a string to a date, not format a date.

Option C and D have incorrect format strings that do not match the required output format.

Equijoins are joins that use the equality operator (=) to match rows. Non-equijoins are joins that use operators other than the equality operator.

A. False. This condition is an equijoin as it uses the equality operator (=).

B. True. This join condition uses the BETWEEN operator, which is not based on equality.

C. False. A natural join is a type of equijoin where the join is made on all columns with the same names in both tables.

D. False. USING clause is used to specify an equijoin on one or more columns.

E. True. This join condition uses the greater than or equal to (>=) operator, making it a non-equijoin.

In Oracle Database 12c, the following are true about privileges and roles:

Option B: PUBLIC acts as a default role granted to every user in a database.

The PUBLIC role is a special role that is automatically granted to all users. It can be used to provide privileges to every user.

Option C: A user has all object privileges for every object in their schema by default.

By default, a user has all privileges on the objects they own, i.e., the objects in their schema.

Option D: A role can contain a combination of several privileges and roles.

Roles are designed to group together privileges and other roles, making it easier to manage and assign security settings to users.

Options A, E, F, and G are incorrect because:

Option A: System privileges can be set for specific schemas or objects, not always for the entire database.

Option E: Roles are not "owned" by users in the traditional sense of ownership; they are managed by the database and can be granted to any user.

Option F: Not all roles are owned by the SYS schema; roles are a database security concept and do not have an "owner".

Option G: The PUBLIC role is not something that can be revoked from a user; it is a default role that exists for all users.

Understanding the functionality of SQL joins and their application in a relational database:

Option A: Correct. A self join is used to relate or compare rows within the same table. It typically involves using table aliases to distinguish the different instances of the table in the query.

Option B: Incorrect. An inner join relates rows between two different tables based on a common condition, not within the same table.

Option C: Incorrect. SQL allows joining of rows from any tables, whether directly related by foreign keys or not, as long as there is a logical condition that can relate their columns.

Option D: Correct. Sometimes establishing a relationship between two specific tables might require joining through one or more intermediary tables, particularly in cases involving complex relationships or normalization.

Option E: Incorrect. While joins are often used to implement relationships, not every logical relationship necessitates a join condition, especially if data from only one table is required or if multiple relations are possible but not relevant to the specific query.

The SET VERIFY ON command is related to how SQL*Plus and SQL Developer display information about substitution variables:

A. It displays values for variables created by the DEFINE command: When VERIFY is set to ON, SQL*Plus and SQL Developer will display the old and new values of a substitution variable when it is redefined using the DEFINE command or when a new value is provided for it during the session.

B. It can be used in SQL Developer and SQL*Plus: While traditionally associated with SQL*Plus, the SET VERIFY command is also supported in SQL Developer, allowing you to control the display of substitution variable values in both environments.

References:

Oracle SQL*Plus User's Guide and Reference, especially the section on the SET command and substitution variables.