1z0-071 Exam Dumps - Oracle Database 12c SQL
Which two statements are true about a self join?
The join key column must have an index.
It can be a left outer join.
It must be a full outer join.
It can be an inner join.
It must be an equijoin.
Answer:
Explanation:
A self join is a regular join, but the table is joined with itself. This kind of join can take the form of an inner join, a left outer join, or even a full outer join depending on the requirement.
A. The join key column must have an index. (Incorrect)
While indexes can improve the performance of joins by reducing the cost of the lookup operations, they are not a requirement for a self join. A self join can be performed with or without an index on the join key columns.
B. It can be a left outer join. (Correct)
A self join can indeed be a left outer join. This is useful when you want to include all records from the 'left' side of the join (the table itself), even if the join condition does not find any matching record on the 'right' side (the table itself again).
Which two statements are true about single row functions?
CONCAT: can be used to combine any number of values
MOD: returns the quotient of a division operation
CEIL: can be used for positive and negative numbers
FLOOR: returns the smallest integer greater than or equal to a specified number
TRUNC: can be used with NUMBER and DATE values
Answer:
Explanation:
Single-row functions operate on single rows and return one result per row. Let's look at each option in the context of Oracle 12c SQL:
A. CONCAT: This function can only combine two values at a time. If you need to concatenate more than two values, you have to nest CONCAT functions or use the || operator.
B. MOD: The MOD function returns the remainder of a division operation, not the quotient.
C. CEIL: This function returns the smallest integer that is greater than or equal to a specified number. It works with both positive and negative numbers.
D. FLOOR: It returns the largest integer that is less than or equal to the specified number, not greater than or equal to.
E. TRUNC: This function can indeed be used with both NUMBER and DATE values to truncate them to a specified number of decimal places or to a particular component of a date.
References:
Oracle Database SQL Language Reference 12c Release 1 (12.1), Functions
Oracle Database SQL Language Reference 12c Release 1 (12.1), CONCAT Function
Oracle Database SQL Language Reference 12c Release 1 (12.1), MOD Function
Oracle Database SQL Language Reference 12c Release 1 (12.1), CEIL and FLOOR Functions
Oracle Database SQL Language Reference 12c Release 1 (12.1), TRUNC Function
Which two statements are true about external tables?
Indexes can be created on them.
You can populate them from existing data in the database by using the CREATE TABLE AS SELECT command.
DML statements cannot be used on them.
Their data can be retrieved by using only SQL or PL/SQL.
Their metadata and actual data are both stored outside the database.
Answer:
Explanation:
For external tables in Oracle:
C. DML statements cannot be used on them: External tables are read-only in Oracle. They allow access to data in external sources as if it were within a table in the database, but they do not support DML operations (INSERT, UPDATE, DELETE).
D. Their data can be retrieved by using only SQL or PL/SQL: You can query the data in external tables using SQL or PL/SQL, just as you would with regular database tables.
Incorrect options are:
A: You cannot create indexes directly on external tables.
B: External tables cannot be populated using the CREATE TABLE AS SELECT command since they are primarily meant for accessing data stored outside the database.
E: Only the metadata of an external tables is stored in the database; the actual data remains in external files.
Which three statements are true about single-row functions?
The data type returned can be different from the data type of the argument.
They can be nested to any level.
They return a single result row per table.
They can accept only one argument.
The argument can be a column name, variable, literal or an expression.
They can be used only in the WHERE clause of a SELECT statement.
Answer:
Explanation:
Single-row functions in SQL return one result per row, and the following statements are true:
A: The data type of the returned value can indeed be different from the data type of the argument provided to the function. For example, the TO_CHAR function can take a numeric input and return a character string.
B: Single-row functions can be nested within each other to any level that is supported by Oracle. This means you can have a function call as an argument to another function, and so on.
E: The argument to a single-row function can be a column name, a variable, a literal, or an expression. This flexibility allows these functions to be very powerful in SQL expressions.
The incorrect options are:
C: Single-row functions do not return a single result row per table; they return a result for each row that is processed.
D: They are not limited to accepting only one argument. Some functions, like NVL, accept multiple arguments.
F: They are not limited to use in the WHERE clause; single-row functions can be used in any part of a SQL statement, including SELECT and ORDER BY clauses.
References:
Oracle Documentation on Single-Row Functions: SQL Functions
What is true about non-equijoin statement performance?
The between condition always performs less well than using the >= and <= conditions.
The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax.
The join syntax used makes no difference to performance.
The between condition always performs better than using the >= and <= conditions.
Table aliases can improve performance.
Answer:
Explanation:
When comparing the performance of different SQL join types and conditions, Oracle SQL optimizations generally ensure that performance is consistent across different syntactical forms of expressing the same logic:
Option A: False. The BETWEEN condition does not inherently perform worse than using >= and <=. Oracle's optimizer typically evaluates these conditions similarly, optimizing the underlying execution based on the data distribution and available indexes.
Option B: False. Oracle's optimizer is designed to handle both Oracle-specific join syntax and ANSI join syntax with equal competence. Performance differences would typically be negligible because the optimizer translates both into an optimal execution plan based on the same underlying mechanisms.
Option C: True. The join syntax used (whether Oracle's traditional syntax or ANSI standard syntax) generally does not affect the performance. Oracle's query optimizer is adept at translating different syntaxes into efficient execution plans.
Option D: False. The assertion that BETWEEN always performs better than >= and <= is incorrect. Performance depends more on factors like indexing, the specific data and distribution, and the Oracle optimizer's capabilities than on the mere choice of syntax.
Option E: False. While table aliases help improve query readability and can prevent ambiguity in SQL queries, they do not inherently improve performance. Their use is a best practice for code clarity and maintenance, not performance enhancement.
Examine this description of the PRODUCTS table:
You successfully execute this command:
CREATE TALE new_prices(prod_id NUBER(2),price NUMBER(8,2));
Which two statements execute without errors?
MERGE INTO new_prices n
USING(SELECT*FROM products)p
WHEN MATECHED THEN
UPDATE SET n.price=p.cost*.01
WHEN NOT MATCHED THEN
INSERT(n.prod_id,n.price)VALUES (p.prod_id,cost*01)
WHERE(p.cost<200);
MERGE INTO new_prices n
USING(SELECT*FROM product WHERE cost>150) p
ON (n.prod_id=p.prod_id)
WHEN NATCHED THEN
DELETE WHERE(p.cost<200)
WHEN NOT MATCHED THEN
INSERT (n.prod_id,n.price)VALUES (p.prod_id,p.cost*.01);
MERGE INTO new_prices n
USING (SELECT * FROM products WHERE cost>150) p
ON (n.prod_id=p.prod_id)
WHEN NATCHED THEN
UPDATE SET n.price=p.cost*.01
DELETE WHERE (p.cost<200);
MERGE INTO new_prices n
USING products p
WHEN NOT NATCHED THEN
INSERT (n.prod_id, n.price)VALUES (p.prod_id,cost*.01)
WHERE (p.cost <200);
Answer:
Explanation:
In the context of the MERGE statement, certain syntax rules and logical sequences must be observed:
Option B:
Correct use of the MERGE statement includes specifying the source and target correctly, and handling cases for matched and unmatched rows. This option properly structures the MERGE command with correct conditions for both matching (on prod_id) and actions (insert for unmatched, and delete under certain conditions for matched rows).
Option D:
While this option has a typographical error with "WHEN NOT NATCHED", assuming it's intended as "WHEN NOT MATCHED", it correctly describes the action for inserting into new_prices when no match is found in the target table based on the condition specified (product cost less than 200).
Options A and C contain syntax errors and logical contradictions (such as attempting to both update and delete in the same matched clause without proper separation or conditions), which are not allowed in a single WHEN MATCHED clause in Oracle SQL.
Examine the description of the CUSTONERS table:
CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?
LEFT OUTER JOIN with self join
PULL OUTER JOIN with self join
subquery
RIGHT OUTER JOIN with self join
self Join
Answer:
Explanation:
To find duplicate customer details entered under different CUSTNO values, one would typically use methods that compare records within the same table:
C. Subquery: A subquery can be used to compare customer details in the CUSTOMERS table and identify duplicates. This subquery can look for matches in the customer details while having different CUSTNO.
E. Self Join: A self join can also be used for this purpose. This involves joining the CUSTOMERS table to itself on the columns containing customer details to identify rows that have the same information but different CUSTNO.
References:
Oracle Database SQL Language Reference 12c, particularly the sections on self joins and subqueries, as these can be utilized to find duplicate rows based on specified criteria.
Examine this query:
SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL; What is the result?
16
160
150
200
100
Answer:
Explanation:
The query uses two functions: ROUND and TRUNC. The ROUND function will round the number 156.00 to the nearest hundred because of the -2 which specifies the number of decimal places to round to. This will result in 200. Then the TRUNC function truncates this number to the nearest 10, due to the -1 argument, which will give us 200 as the result since truncation does not change the rounded value in this case.
A. 16 (Incorrect)
B. 160 (Incorrect)
C. 150 (Incorrect)
D. 200 (Incorrect)
E. 100 (Incorrect)