1z0-071 Exam Dumps - Oracle Database 12c SQL
Which three items does a direction of a relationship contain?
an attribute
a cardinality
label
an optionality
a unique identifier
an entity
Answer:
Explanation:
In data modeling and database design, the direction of a relationship typically includes:
Option B: a cardinality
Cardinality refers to the numerical relationships between two entities, indicating the number of instances of one entity that can or must be associated with each instance of another.
Option C: label
A label is often used to describe the role or purpose of the relationship in a way that clarifies the nature of the interaction between entities.
Option D: an optionality
Optionality indicates whether or not a relationship is mandatory or optional; in other words, it shows if an instance of an entity must be related to an instance of another entity or if it can exist without such a relationship.
Options A, E, and F are not part of the direction of a relationship:
Option A: An attribute is a property or characteristic of an entity, not the direction of a relationship.
Option E: A unique identifier uniquely identifies each instance of an entity, not a relationship's direction.
Option F: An entity is a thing with distinct and independent existence in a database, usually mapped to a table, not a part of the direction of a relationship.
Which two statements are true?
AIl conditions evaluated using DECODE can also be evaluated using CASE.
All conditions evaluated using CASE can also be evaluated using DECODE.
CASE is a function and DECODE is not.
DECODE is a function and CASE is not.
Neither CASE nor DECODE is a function.
Both CASE and DECODE are functions.
Answer:
Explanation:
A. True. The DECODE function can evaluate conditions, but it is limited to equality checks. On the other hand, CASE can evaluate these and a broader range of conditions using different comparison operators.
D. True. DECODE is a function in Oracle SQL that allows for simple conditional query transformation. CASE is not a function but a statement that provides more flexibility and readability when handling conditional logic in SQL queries.
DECODE is more limited compared to CASE since it can't perform logical operations other than equality.
Examine the description of the EMPLOYEES table:
Which two statements will run successfully?
SELECT 'The first_name is '' || first_name || '' FROM employees ;
SELECT 'The first_name is '''||first_name ||'''' FROM employees ;
SELECT 'The first_name is ''' ||first_name||''' FROM employees ;
SELECT 'The first_name is '|| first_name|| '' FROM employees;
SELECT 'The first_name is \'' || first_name || '\'' FROM employees;
Answer:
Explanation:
In Oracle SQL, to include single quotes in a string literal, you need to use two single-quote characters.
A. This statement will not run successfully because the single quotes are not properly escaped. After the text The first_name is , there should be two single quotes to start the string literal for the first name.
B. This statement will run successfully. It properly escapes the single quotes by using two single quotes around first_name. This will result in the output like The first_name is 'John'.
C. This statement will run successfully as well. The two single quotes after The first_name is and before FROM employees are correctly used to delimit the first name.
D. This statement will not run successfully. There is a missing single quote before the double pipe (||) operator, and at the end of the statement, only one single quote is used instead of two for escaping.
E. This statement will not run successfully because the backslash is not used to escape single quotes in Oracle SQL. The proper method is to use two single quotes without a backslash.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Text Literals"
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "String Concatenation Operator"
You execute this command:
TRUNCATE TABIE depts;
Which two are true?
A ROLLBACK statement can be used to retrieve the deleted data.
It drops any triggers defined on the table.
It retains the indexes defined on the table.
It retains the integrity constraints defined on the table,
It always retains the space used by the removed rows.
A FLASHBACK TABLE statement can be used to retrieve the deleted data.
Answer:
Explanation:
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.
Examine the description of the EMPLOYEES table:
Which statement increases each employee's SALARY by the minimum SALARY for their DEPARTM
ENT_ID?
UPDATE employees e1
SET salary =(SELECT e2. salary + MIN(e2.salary)
FROM employees e2
WHERE e1.department_ id = e2. department_id GROUP BY e2. department_id) ;
UPDATE employees e1
SET salary = salary +
(SELECT MIN(e1. salary)
FROM employees e2
WHERE e1.department_id = e2 .department_id);
UPDATE employees e1
SET salary = salary+(SELECT MIN (salary)
FROM employees e2) ;
UPDATE employees e1
SET salary=
(SELECT e1.salary + MIN(e2.salary)
FROM employees e2
WHERE e1. department_ id = e2.department_id);
Answer:
Explanation:
The correct statement to increase each employee's salary by the minimum salary for their department is:
D. This option correctly increases each employee's salary by the minimum salary in their department. The subquery calculates the minimum salary for the department of the current row being updated (e1) in the outer UPDATE statement. The subquery does not use GROUP BY since it returns a single value - the minimum salary for the matching department.
A, B, and C are incorrect. Specifically:
A uses a subquery with an aggregate function combined with GROUP BY, which is not needed and will result in an error.
B incorrectly references MIN(e1.salary) within the subquery, which is incorrect since e1 is the alias for the outer query's table and should not be used within the subquery.
C sets the salary to itself plus the minimum salary across all employees, not filtered by the department.
References:
Oracle Database SQL Language Reference, 12c Release 1 (12.1): "UPDATE"
which three statements are true regarding single row subqueries?
THEY CAN BE USED in the where clause.
A SQL STATEMENT MAY HAVE MULTIPLE SINGLE ROW SUBQUERY BLOCKS.
THEY MUST BE PLACED ON THE RIGHT SIDE OF THE COMPARISON OPERATOR OR CONDITION.
they must be placed on the left side of the comparison operator or condition.
THEY CAN BE USED IN THE HAVING CLAUSE
they must return a row to prevent errors in the SQL statement.
Answer:
Explanation:
Regarding single-row subqueries:
A. THEY CAN BE USED in the where clause: Single-row subqueries can be used in the WHERE clause to compare a column's value against the result of the subquery.
B. A SQL STATEMENT MAY HAVE MULTIPLE SINGLE ROW SUBQUERY BLOCKS: It is possible to use more than one single-row subquery within a SQL statement, each potentially in different parts of the statement (e.g., SELECT clause, WHERE clause).
E. THEY CAN BE USED IN THE HAVING CLAUSE: Similar to the WHERE clause, single-row subqueries can also be used in the HAVING clause to filter groups based on a condition evaluated against a subquery result.
Incorrect options are:
C and D: There is no such limitation on the placement of single-row subqueries relative to the comparison operator; they can be used on either side.
F: Single-row subqueries must return exactly one row to avoid errors, but they do not necessarily have to return a row; if no row is returned, the query may still execute, potentially returning no rows in the outer query.
Examine the description of the EMPLOYEES table:
Which query is valid?
SELECT dept_id, join date, SUM(salary) FROM employees GROUP BY dept_id,join_date;
SELECT dept_id, MAX (AVG(salary)) FROM employees GROUP BY dept_id;
SELECT dept_id, AVG(NAX(salary)) FROM employees GROUP BY dept_id;
SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
Answer:
Explanation:
When using the GROUP BY clause, every column in the SELECT clause that is not an aggregate function must be included in the GROUP BY clause:
A. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date: This is a valid query because all non-aggregate columns in the SELECT list (dept_id and join_date) are included in the GROUP BY clause.
Queries B and C are invalid because they attempt to nest aggregate functions, which is not allowed. Query D is invalid because join_date is not included in the GROUP BY clause.
References:
Oracle Database SQL Language Reference 12c, specifically the section on GROUP BY clause constraints.
Examine the data in the EMPLOYEES table:
Which statement will compute the total annual compensation for each employee?
SELECT last name,
(monthly salary*12) + (monthly_commission_pct * 12) AS
annual comp
FROM employees
;
SELECT last_ name (monthly_ salary+ monthly_ commission _ pct) *12 AS annual_
FROM employees ;
SELECT last name, (monthly_ salary *12) + (monthly_ salary * 12 * NVL
(monthly commission pct,0) ) As annual _ comp
FROM employees;
SELECT last_ name, monthly_ salary*12) + (monthly_ salary * 12 * Monthly commission _Pct) AS
annual_ comp
FROM employees;
Answer:
Explanation:
To calculate the total annual compensation, you need to sum the annual salary with the annual commission. The annual commission is the monthly commission percentage times the monthly salary times 12 (months). The NVL function is used to replace NULL with 0 for commission percentage when calculating the commission.
A: This statement is incorrect because it does not handle NULL values for 'monthly_commission_pct', which will result in NULL for the entire expression if 'monthly_commission_pct' is NULL.
B: This statement is syntactically incorrect because it lacks commas and proper parentheses to separate the column name 'last_ name' from the calculation.
C: This is the correct statement as it uses NVL to replace NULL with 0 and calculates the total annual compensation correctly.
D: This statement is incorrect because, like in option A, it does not handle NULL values in 'monthly_commission_pct', resulting in NULL if 'monthly_commission_pct' is NULL.