NotJustExam Interactive Question Bank | [Oracle] 1z0-071

Question 1

Examine the description of the PROMOTIONS table:

You want to display the unique promotion costs in each promotion category.
Which two queries can be used? (Choose two.)

A. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
B. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
D. SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;
E. SELECT promo_cost, promo_category FROM promotions ORDER BY 1;

Correct Answer: AC

Explanation:

The AI assistant agrees with the suggested answer of AC.
Reasoning:

Options A and C both use the DISTINCT keyword to retrieve unique combinations of promotion category and cost.
Option A concatenates the promo_category and promo_cost which is acceptable, and DISTINCT will ensure only unique concatenations are returned.
Option C selects promo_category and promo_cost as separate columns and uses DISTINCT to retrieve unique combinations of these two columns.

Reasons for not choosing the other options:

Option B is syntactically incorrect. The DISTINCT keyword cannot be used multiple times within the same SELECT clause like that.
Option D is syntactically incorrect. The DISTINCT keyword should be placed immediately after SELECT, not after a column name. Also, the syntax `promo_category DISTINCT promo_cost` is invalid. Additionally, the comma before FROM is a syntax error.
Option E does not use the DISTINCT keyword at all, so it will return all rows without filtering for unique combinations.

In summary, options A and C are the only valid queries that return unique promotion costs in each promotion category.

Question 2

Examine the description of the PRODUCTS table:

Which three queries use valid expressions? (Choose three.)

A. SELECT product_id, unit_price, S "Discount", unit_price + surcharge - discount FROM products;
B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
C. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;
D. SELECT product_id, unit_price || 5 "Discount", unit_price + surcharge - discount FROM products;
E. SELECT product_id, expiry_date * 2 FROM products;
F. SELECT product_id, unit_price, unit_price + surcharge FROM products;

Correct Answer: BCF

Explanation:

The AI assistant agrees with the suggested answer of BCF. Here's a detailed breakdown of why these options are valid and why the others are not:

B: SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
Reason: This query uses a valid arithmetic expression. It performs multiplication and division on the unit_price, which is a NUMBER data type as shown in the PRODUCTS table description. The order of operations is correctly specified using parentheses, ensuring a valid numerical result.
C: SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;
Reason: This query calculates the difference between two DATE columns (expiry_date and delivery_date), and then multiplies the result by 2. In Oracle, subtracting one date from another results in the number of days between them. Multiplying this number by 2 is a valid operation. This calculates double the number of days between the two dates.
F: SELECT product_id, unit_price, unit_price + surcharge FROM products;
Reason: This query adds the unit_price and surcharge. The PRODUCTS table description shows unit_price is NUMBER, and surcharge is VARCHAR2. Oracle will attempt implicit conversion of the VARCHAR2 column Surcharge to NUMBER when it is used in numeric operations. If all values of SURCHARGE are convertible to NUMBER, this will execute successfully.

Here's why the other options are invalid:

A: SELECT product_id, unit_price, S "Discount", unit_price + surcharge - discount FROM products;
Reason: The query references a column named "discount" which does not exist in the PRODUCTS table and it is also trying to use column alias "S" as "Discount" in the expression, this is not allowed in the same SELECT clause. Therefore, it will cause an error.
D: SELECT product_id, unit_price || 5 "Discount", unit_price + surcharge - discount FROM products;
Reason: The || operator is used for string concatenation. While `unit_price || 5` might implicitly convert `unit_price` to a string and concatenate '5', the "discount" column doesn't exist in the PRODUCTS table, also as in option A, you can not use column alias "Discount" in the same SELECT clause. This expression will therefore result in an error.
E: SELECT product_id, expiry_date * 2 FROM products;
Reason: Multiplying a DATE column (expiry_date) by a number (2) is not a valid operation in Oracle SQL. While Oracle might implicitly convert the date to a number representing the number of days since a fixed date, directly multiplying a DATE by a number is not the intended use and can lead to unexpected results or errors.

Therefore, the correct answer is BCF.

Citations:

Oracle Documentation on Data Types, https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datatype.htm
Oracle Documentation on Operators, https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators001.htm

Question 3

What is true about non-equijoin statement performance? (Choose two.)

A. The BETWEEN condition always performs less well than using the >= and <= conditions.
B. The BETWEEN condition always performs better than using the >= and <= conditions.
C. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
D. Table aliases can improve performance.
E. The join syntax used makes no difference to performance.

Correct Answer: DE

Explanation:

The AI agrees with the suggested answer.
The correct answers are D and E.
D. Table aliases can improve performance.
E. The join syntax used makes no difference to performance.

Reasoning:
* **Table aliases can improve performance**: Aliases can help in reducing the amount of parsing required, although the actual impact on performance might be marginal. Using shorter aliases makes the SQL statements less verbose, which can improve readability and slightly reduce parsing overhead.
* **The join syntax used makes no difference to performance**: The SQL:1999-compliant ANSI join syntax does not inherently offer any performance benefits over the Oracle-proprietary join syntax or vice versa. The optimizer focuses on the logic, and both syntaxes are transformed into a common execution plan.

Reasons for excluding other options:
* **A & B: The BETWEEN condition always performs less/better well than using the >= and <= conditions:** The performance of `BETWEEN` vs. `>=` and `<=` depends on the specific database system, indexing, and data distribution. There is no definitive rule that `BETWEEN` is always better or worse.
* **C: The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax:** There is no significant performance difference between Oracle join syntax and ANSI join syntax. The optimizer should produce similar execution plans for both. While there might be specific cases where one performs slightly better, it is not a general rule.

Citations:
- SQL:1999: https://en.wikipedia.org/wiki/SQL:1999

Question 4

Which two are true? (Choose two.)

A. ADD_MONTHS adds a number of calendar months to a date.
B. CEIL requires an argument which is a numeric data type.
C. CEIL returns the largest integer less than or equal to a specified number.
D. LAST_DAY returns the date of the last day of the current month only.
E. LAST_DAY returns the date of the last day of the month for the date argument passed to the function.
F. LAST_DAY returns the date of the last day of the previous month only.

Correct Answer: AE

Explanation:

The AI concurs with the suggested answer of AE.
Here's a breakdown of why:

A. ADD_MONTHS adds a number of calendar months to a date. This statement is correct. The ADD_MONTHS function in Oracle SQL is specifically designed to add a specified number of months to a date value.
E. LAST_DAY returns the date of the last day of the month for the date argument passed to the function. This statement is also correct. The LAST_DAY function takes a date as input and returns the date of the last day of the month containing that date.

Let's examine why the other options are incorrect:

B. CEIL requires an argument which is a numeric data type. While CEIL primarily works with numeric data types, this statement is not entirely precise in the context of Oracle SQL. The function is designed to work with numeric values and will implicitly convert some data types to number if possible. However, it doesn't directly support date or interval types. The focus should be on the function's *purpose* which is to round numbers, making this option less directly relevant.
C. CEIL returns the largest integer less than or equal to a specified number. This is incorrect. CEIL returns the *smallest* integer greater than or equal to a specified number. This is the definition of the CEIL function.
D. LAST_DAY returns the date of the last day of the current month only. This is incorrect because LAST_DAY function requires a date parameter, then it returns the date of the last day of the month for the date argument passed to the function.
F. LAST_DAY returns the date of the last day of the previous month only. This is incorrect. LAST_DAY returns the last day of the month *of the date provided as an argument*, not necessarily the *previous* month.

Therefore, options A and E are the only correct statements.

Question 5

Which three statements are true about Oracle synonyms? (Choose three.)

A. A synonym cannot be created for a PL/SQL package.
B. A synonym can be available to all users.
C. A SEQUENCE can have a synonym.
D. Any user can drop a PUBLIC synonym.
E. A synonym created by one user can refer to an object belonging to another user.

Correct Answer: BCE

Explanation:

The AI assistant agrees with the suggested answer, which is BCE.

Reasoning:

B: A synonym can be available to all users. Public synonyms are accessible to all users.
C: A SEQUENCE can have a synonym. Synonyms can be created for sequences.
E: A synonym created by one user can refer to an object belonging to another user. If a user has the necessary privileges on an object owned by another user, they can create a synonym for it.

Reasons for not choosing the other options:

A: A synonym cannot be created for a PL/SQL package. This is incorrect because synonyms can be created for PL/SQL packages. "You can create synonyms for tables, views, sequences, stored procedures, functions, packages, materialized views, Java class schema objects, user-defined types, or other synonyms" (see citation 1).
D: Any user can drop a PUBLIC synonym. This statement is false. Only users with the necessary privileges (such as the creator or a user with the DROP ANY SYNONYM privilege) can drop a public synonym. "To drop a public synonym, you must have the DROP PUBLIC SYNONYM system privilege" (see citation 2).

In summary, options B, C, and E are the only correct statements regarding Oracle synonyms.

Citation 1: Oracle Documentation, Creating Synonyms, https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_775.htm
Citation 2: Oracle Documentation, DROP SYNONYM, https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7014.htm

Question 6

Which two are true? (Choose two.)

A. CONCAT joins two character strings together.
B. CONCAT joins two or more character strings together.
C. FLOOR returns the largest positive integer less than or equal to a specified number.
D. INSTR finds the offset within a character string, starting from position 0.
E. INSTR finds the offset within a string of a single character only.
F. FLOOR returns the largest integer less than or equal to a specified number.

Correct Answer: AF

Explanation:

The AI assistant agrees with the suggested answer of AF.
Reasoning:

Option A is correct: The CONCAT function in Oracle is used to join two character strings together.
Option F is correct: The FLOOR function returns the largest integer less than or equal to a specified number. For example, FLOOR(7.9) returns 7, and FLOOR(-7.9) returns -8.

Reasons for excluding other options:

Option B is incorrect: While newer versions of Oracle (23c onwards) allow CONCAT to take multiple arguments, the question doesn't specify a version, and the most common understanding of CONCAT involves two strings.
Option C is incorrect: FLOOR returns the largest integer (not necessarily positive) less than or equal to a specified number. For negative numbers, this means it rounds *down* to a more negative number.
Option D is incorrect: INSTR finds the offset within a character string, but the offset starts from position 1, not 0, in Oracle.
Option E is incorrect: INSTR can find the offset of a substring, not just a single character.

Citations:

CONCAT function - Oracle, https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CONCAT.html
FLOOR function - Oracle, https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/FLOOR.html
INSTR function - Oracle, https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/INSTR.html

Question 7

Examine these SQL statements which execute successfully:

Which two statements are true after execution? (Choose two.)

A. The primary key constraint will be enabled and IMMEDIATE.
B. The foreign key constraint will be enabled and DEFERRED.
C. The primary key constraint will be enabled and DEFERRED.
D. The foreign key constraint will be disabled.
E. The foreign key constraint will be enabled and IMMEDIATE.

Correct Answer: AD

Explanation:

Based on the question and discussion, the AI agrees with the suggested answer of AD.

Reasoning:
The SQL statements provided disable and then enable the primary key constraint, and also create a foreign key constraint with the `DEFERRED` option. Let's break down why AD is correct:

**A: The primary key constraint will be enabled and IMMEDIATE.** The prompt shows that the primary key is re-enabled using the `ENABLE VALIDATE` clause. When a constraint is enabled using `ENABLE VALIDATE`, it is enabled in `IMMEDIATE` mode. This means that Oracle will immediately enforce the constraint at the end of each SQL statement.
**D: The foreign key constraint will be disabled.** The `DEFERRED` option on the foreign key constraint means that the constraint is checked at the end of the transaction, not at the end of each statement. However, if the primary key that the foreign key refers to is disabled, the foreign key would also be effectively disabled until the primary key is re-enabled.

Reasons for not choosing other answers:

**B: The foreign key constraint will be enabled and DEFERRED.** While the foreign key is created with the `DEFERRED` option, it is not necessarily "enabled" in the sense that it's actively being validated if the primary key it refers to is disabled. Once the primary key is re-enabled, the foreign key constraint is effectively enabled in deferred mode.
**C: The primary key constraint will be enabled and DEFERRED.** The primary key constraint after the `ENABLE VALIDATE` command is `IMMEDIATE`, not `DEFERRED`.
**E: The foreign key constraint will be enabled and IMMEDIATE.** The foreign key is explicitly created as `DEFERRED INITIALLY DEFERRED`. Therefore, it cannot be `IMMEDIATE`.

Citations:
Oracle Documentation on Constraint States, https://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm

Question 8

Examine this SQL statement:

Which two are true? (Choose two.)

A. All existing rows in the ORDERS table are updated.
B. The subquery is executed before the UPDATE statement is executed.
C. The subquery is not a correlated subquery.
D. The subquery is executed for every updated row in the ORDERS table.
E. The UPDATE statement executes successfully even if the subquery selects multiple rows.

Correct Answer: AD

Explanation:

The suggested answer is AD.
The AI agrees with the suggested answer AD.
Here's the detailed reasoning:
The SQL statement updates the ORDERS table, setting the CUSTOMER_ID for certain orders based on a subquery that looks up the CUSTOMER_ID from the CUSTOMERS table using the customer's NAME.

A. All existing rows in the ORDERS table are updated. This is true because the UPDATE statement does not have a WHERE clause to restrict which rows are updated. Therefore, every row in the ORDERS table will be considered for updating.
D. The subquery is executed for every updated row in the ORDERS table. This is true because the subquery is correlated. It depends on the ORDER_ID from the outer query (the ORDERS table being updated). For each row being updated in ORDERS, the subquery will be executed to find the corresponding CUSTOMER_ID.

Reasons for not choosing the other options:

B. The subquery is executed before the UPDATE statement is executed. This is incorrect. While non-correlated subqueries are typically executed once before the outer query, this is a correlated subquery. Correlated subqueries are executed for each row processed by the outer query.
C. The subquery is not a correlated subquery. This is incorrect. The subquery is correlated because it references the ORDERS table (aliased as O) in its WHERE clause (O.ORDER_ID = O.ORDER_ID). Although it's referencing the same table, the subquery needs a value from each row of the ORDERS table to process.
E. The UPDATE statement executes successfully even if the subquery selects multiple rows. This is incorrect. If the subquery returns more than one CUSTOMER_ID for a given ORDER_ID, the UPDATE statement will fail with an error like "single-row subquery returns more than one row." The assignment `CUSTOMER_ID = (subquery)` expects the subquery to return at most one value.

Question 9

Which two statements are true about TRUNCATE and DELETE? (Choose two.)

A. DELETE can use a WHERE clause to determine which row(s) should be removed.
B. TRUNCATE can use a WHERE clause to determine which row(s) should be removed.
C. TRUNCATE leaves any indexes on the table in an UNUSABLE state.
D. The result of a TRUNCATE can be undone by issuing a ROLLBACK.
E. The result of a DELETE can be undone by issuing a ROLLBACK.

Correct Answer: AE

Explanation:

The AI agrees with the suggested answer.

The correct options are A and E.

Reasoning:

Option A is correct because the DELETE statement allows the use of a WHERE clause to specify the rows to be removed. This provides flexibility in removing specific data based on conditions.
Option E is correct because DELETE operations are DML (Data Manipulation Language) operations, and thus can be undone using a ROLLBACK command, provided the operation was performed within a transaction.

Reasons for Incorrect Options:

Option B is incorrect because TRUNCATE is a DDL (Data Definition Language) command that removes all rows from a table. It does not support a WHERE clause.
Option C is incorrect because TRUNCATE resets the table's high-water mark and typically rebuilds or maintains indexes, rather than leaving them in an unusable state. According to Oracle documentation, TRUNCATE TABLE statement deallocates the space used by the table and removes all rows. It also resets the storage parameters.
Option D is incorrect because TRUNCATE is a DDL command, and DDL commands implicitly commit; therefore, a TRUNCATE operation cannot be rolled back.

Citations:

Oracle TRUNCATE TABLE Documentation, https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_901.htm

Question 10

The STORES table has a column START_DATE of data type DATE, containing the date the row was inserted.
You only want to display details of rows where START_DATE is within the last 25 months.
Which WHERE clause can be used?

A. WHERE TO_NUMBER(start_date - SYSDATE) <= 25
B. WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25
C. WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25
D. WHERE ADD_MONTHS(start_date, 25) <= SYSDATE

Correct Answer: C

Explanation:

The AI agrees with the suggested answer.
The suggested answer is C: WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25.
Reasoning:
The question asks for rows where `START_DATE` is within the last 25 months. This means we need to find the difference in months between the current date (`SYSDATE`) and the `START_DATE` and check if it's less than or equal to 25.
Option C, `WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25`, correctly calculates the number of months between `SYSDATE` and `start_date` and ensures that the difference is within the desired range.

Why other options are incorrect:
* **Option A:** `WHERE TO_NUMBER(start_date - SYSDATE) <= 25` subtracts the dates and converts the result to a number, representing the difference in days, not months. This is incorrect because the question asks for a 25-month period. * **Option B:** `WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25` calculates the months between `start_date` and `SYSDATE`, which would return negative values if the `start_date` happened before `SYSDATE` and positive values otherwise. While technically this could work with absolute values, it isn't the most direct solution. In addition, `START_DATE` stores "the date the row was inserted" and therefore cannot be greater than `SYSDATE` (which represents the current date and time). * **Option D:** `WHERE ADD_MONTHS(start_date, 25) <= SYSDATE` checks if `start_date` plus 25 months is before or equal to the current date. This is incorrect because the date could be more than 25 months ago and still satisfy the condition. For example, if the `start_date` was 30 months ago, adding 25 months would result in a date 5 months in the past, which is still less than or equal to `SYSDATE`. Thus, it doesn't correctly filter for rows within the *last* 25 months.

MONTHS_BETWEEN - calculates the number of months between two dates, https://www.techonthenet.com/oracle/functions/months_between.php

[Oracle] 1z0-071 - Oracle SQL Certified Associate Exam Dumps & Study Guide

Free [Oracle] 1z0-071 - Oracle SQL Certified Associate Practice Questions Preview

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10