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

The statements regarding data type conversions and the treatment of literals in SQL expressions involve understanding implicit and explicit data conversions in Oracle SQL.

Statement A is true as invoice_date > '01-02-2019' involves an implicit conversion of the string literal to a date type, based on the NLS_DATE_FORMAT setting, assuming the format matches.

Statement E is true because, similarly to A, invoice_date = '15-march-2019' involves an implicit conversion where the string is automatically converted to a date type according to the Oracle NLS_DATE_FORMAT or an assumed default date format.

Statements B, C, and D involve incorrect or misleading information:

B (qty_sold = '05549821') is misleading and potentially incorrect as leading zeros in a numeric context do not typically require explicit conversion but the presence of spaces might suggest a need for trimming rather than numeric conversion.

C (CONCAT(qty_sold, invoice_date)) would indeed require explicit conversion because CONCAT expects string types, and thus numerical and date values must be explicitly converted to strings before concatenation.

D (qty_sold BETWEEN '101' AND '110') uses implicit conversion where the string literals '101' and '110' are implicitly converted to numbers if qty_sold is a numeric type.

The statement true regarding the CREATE TABLE command:

C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match: The SQL command tries to create a table with columns prod_id, cust_id, and order_date, but the SELECT statement specifies columns prod_id, cust_id, and time_id. The mismatch in column names and the number of columns specified will prevent the table from being created.

Incorrect options:

A: It is possible to specify a DEFAULT value in the column definition when creating a table with the CREATE TABLE AS SELECT syntax.

B: Not all NOT NULL constraints (or any other constraints, for that matter) are automatically passed to the new table unless explicitly stated in the CREATE TABLE statement.

D: FOREIGN KEY constraints are not automatically included when creating a table using the CREATE TABLE AS SELECT syntax; they would need to be added explicitly afterwards.

The LIKE operator is used in SQL to search for a specified pattern in a column. To find all customers residing in cities starting with 'D' followed by at least two characters, you need to use the LIKE operator with the appropriate wildcard pattern:

A. SELECT * FROM customers WHERE city LIKE 'D_%'; The underscore () wildcard character in SQL represents a single character. The percent (%) wildcard character represents zero or more characters. So 'D%' will match any city starting with 'D' followed by at least one character (as there is one underscore).

References:

Oracle Database SQL Language Reference 12c, specifically the section on pattern matching with the LIKE condition.

The statement provided uses the UNION operator, which combines the results of two or more queries into a single result set. However, UNION also eliminates duplicate rows. Both queries in the union are selecting the same values 1 and ' John', thus the result is one row because duplicates will be removed.

SELECT 1 AS id, ' John' AS first name FROM DUAL UNION SELECT 1 , ' John' AS name FROM DUAL ORDER BY 1;

Given that both SELECT statements are actually identical in the values they produce, despite the differing column aliases, the UNION will eliminate one of the duplicate rows, resulting in a single row being returned.

References:

Oracle Documentation on UNION: SQL Language Reference - UNION

An index is created automatically on columns that are defined with a UNIQUE constraint and a PRIMARY KEY constraint.

A. True. Since the serial_no column is defined with a UNIQUE constraint, Oracle will automatically create an index for this column.

E. True. The order_id column is defined with a PRIMARY KEY constraint, and Oracle automatically creates a unique index on the primary key column.

In a SELECT statement with DISTINCT, the ORDER BY clause can only order by expressions that are part of the SELECT list.

A. quantity alone is not sufficient to replace <clause> as it is not included in the SELECT list after DISTINCT.

B. This option can successfully replace <clause> because both quantity and unit_price are used in the SELECT expression, and thus their individual values are valid for the ORDER BY clause.

C. total_paid is an alias for the expression quantity * unit_price, but it cannot be used in the ORDER BY clause because Oracle does not allow aliases of expressions in DISTINCT queries to be used in ORDER BY.

D. product_id is not included in the SELECT list after DISTINCT and thus cannot be used in ORDER BY.

E. The expression quantity * unit_price is exactly what is selected, so it can replace <clause> and the query will complete successfully.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause"

In a CROSS JOIN (also known as a Cartesian join), each row from the first table (emp) is joined to every row of the second table (dept). When we apply the filter for job = 'MANAGER' and dept.deptno IN (10, 20), we are restricting the results to managers in departments 10 and 20.

From the given data:

There are 2 managers in department 10 (CLARK and KING).

There is 1 manager in department 20 (JONES).

With a cross join, each of these emp records will join with each dept record where deptno is 10 or 20. Since there are two departments (10 and 20) in the dept table, each employee will match with two departments.

Therefore, the result set will be:

CLARK with accounting (dept 10) and research (dept 20)

KING with accounting (dept 10) and research (dept 20)

JONES with accounting (dept 10) and research (dept 20)

That gives us a total of 6 rows, which means the correct answer is B: 6.

The SQL query must find all customers with last names starting with A or B, regardless of case, and a credit limit below 1000:

B. WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR INITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000: The INITCAP function initializes the first letter to uppercase for comparison. However, it should be noted that using INITCAP is not necessary when using the LIKE operator with a wildcard % following a single character, because it will not correctly filter all last names that start with an upper or lower case A or B.

D. WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND cust_credit_limit < 1000: This correctly filters last names beginning with A or B in any case and includes only those with a credit limit below 1000. The UPPER function is used to convert cust_last_name to uppercase before comparison.

References:

Oracle Database SQL Language Reference 12c, especially sections on string functions and conditions.

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.

For displaying unique promotion costs in each promotion category from the PROMOTIONS table:

A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1: This query correctly retrieves unique combinations of promo_category and promo_cost, sorting the results based on the promo_category.

D. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1: This query correctly combines the category and cost into a single string for each unique combination, using string concatenation and the DISTINCT keyword to ensure uniqueness, sorting by the concatenated string.

Incorrect options:

B: This does not ensure that the combinations of promo_cost and promo_category are unique because DISTINCT was not specified for both columns together.

C: The syntax is incorrect; DISTINCT cannot be applied to a single column partway through a SELECT clause.

E: This is syntactically incorrect as DISTINCT cannot be used twice in the way shown; it must apply to all columns if used at the beginning of a SELECT statement.