Exactprep 1z0-071 Questions

The queries that will return all the rows from the COLORS table are those that ensure every record from COLORS is selected, regardless of whether there's a matching record in the BRICKS table:

Option A:

SELECT * FROM bricks b RIGHT JOIN colors c ON b.color_rgb_hex_value = c.rgb_hex_value;

A right join will return all the rows from the right table (COLORS), with the matching rows from the left table (BRICKS). If there is no match, NULL will be returned for columns from BRICKS.

Option B:

SELECT * FROM colors c LEFT JOIN bricks b USING (rgb_hex_value);

A left join will return all the rows from the left table (COLORS), with the matching rows from the right table (BRICKS). If there is no match, NULL will be returned for columns from BRICKS. The USING clause indicates a join condition where columns with the same names are compared for equality.

Options C, D, and E will not return all the rows from the COLORS table:

Option C: The full join will return all rows from both tables, but it is not restricted to only the rows from COLORS.

Option D: An inner join will return only the matching rows between both tables, not all rows from COLORS.

Option E: This is a left join, which would typically return all rows from COLORS, but the WHERE clause restricts the result set to only those rows from COLORS that have a matching BRICK_ID in BRICKS which is greater than 0, potentially excluding rows from COLORS.

To include employees with no manager in the query results, a LEFT OUTER JOIN should be used. This type of join returns all records from the left table (employees), and the matched records from the right table (managers). The result is NULL from the right side if there is no match.

Here's the modified query:

SELECT e.last_name AS emp, m.last_name AS mgr FROM employees e LEFT OUTER JOIN managers m ON e.manager_id = m.employee_id ORDER BY emp;

This ensures that even if an employee does not have a manager (i.e., e.manager_id is NULL or there is no corresponding m.employee_id), that employee will still be included in the results.

In Oracle SQL, when performing a JOIN operation, the ON clause is used to specify the condition that relates the two tables being joined. The WHERE clause can be used to further filter the result set.

A) This is a valid join condition using the WHERE clause to filter the rows after the join has been made.

B) This statement will fail because the ON clause should only contain conditions that relate the two tables. The condition for filtering the departments table should be in the WHERE clause, not in the ON clause. This is a common mistake when writing JOIN statements.

C) This is a correct statement. The ON clause specifies how the tables are related and the WHERE clause specifies an additional filtering condition for the query.

D) This statement is also correct. It's similar to the first statement (A) and properly places the department_id filter in the ON clause, which is acceptable though not typically best practice as it can be less readable than using a WHERE clause for non-join conditions.

When the JOIN operation is executed, the database first pairs rows from the joined tables that meet the join condition specified by the ON clause. Then, it filters the result of the JOIN operation based on the condition specified in the WHERE clause.

References:

Oracle Documentation on Joins: https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52359

A. True - The TO_CHAR function is used correctly here to convert the numeric value to a string, and NVL handles the case where cust_credit_limit might be NULL. The expression inside NVL computes 15% of the credit limit or displays 'Not Available' if the credit limit is NULL. The syntax is correct.

B. False - The NVL2 function requires three parameters: the expression to check for NULL, the value to return if it's not NULL, and the value to return if it is NULL. The given usage lacks the required parameters and syntax.

C. False - The NVL function expects both parameters to be of the same data type. Since the second parameter 'Not Available' is a string, it causes a data type conflict with the numerical result of the first parameter.

D. False - The keyword SELECT is misspelled as SLECT, making the syntax incorrect.

E. True - This query uses NVL2 correctly by checking if cust_credit_limit is not NULL, then applying TO_CHAR to compute 15% of it and converting it to string, or returning 'NOT Available' if it is NULL. The syntax and function usage are correct.