Pearson 1z0-071 New Attempt

Page: 17 / 24

Question 68

Which three statements are true regarding single row subqueries?

Options:

They must be placed on the left side of the comparison operator or condition.

They must return a row to prevent errors in the SQL statement.

A SQL statement may have multiple single row subquery blocks.

They can be used in the HAVING clause.

They must be placed on the right side of the comparison operator or condition.

They can be used in the clause.

Question 69

Choose two

Examine the description of the PRODUCT DETALS table:

Options:

PRODUCT_ID can be assigned the PEIMARY KEY constraint.

EXPIRY_DATE cannot be used in arithmetic expressions.

EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it

PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it

PRODUCT_PRICE contains the value zero by default if no value is assigned to it.

PRODUCT_NAME cannot contain duplicate values.

Answer:

A, D

Explanation:

A. PRODUCT_ID can be assigned the PRIMARY KEY constraint.

In Oracle Database 12c, a PRIMARY KEY constraint is a combination of a NOT NULL constraint and a unique constraint. It ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table and not null. Given the PRODUCT_ID is marked as NOT NULL, it is a candidate for being a primary key because we can assume that it is intended to uniquely identify each product in the table.

Reference: [Reference: Oracle 12c documentation states that a column defined with the NOT NULL constraint can be used as a primary key, provided the values in the column are also unique (Oracle Database SQL Language Reference, 12c Release 1 (12.1))., B. EXPIRY_DATE cannot be used in arithmetic expressions. (Incorrect), This statement is not necessarily true. Dates in Oracle can be used in arithmetic expressions, typically to add or subtract days from a date., C. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it. (Incorrect), Unless explicitly specified, a date column does not default to SYSDATE. A default value must be set using the DEFAULT clause during the table creation or altered later., D. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it., This is correct. In Oracle, if a numeric column like PRODUCT_PRICE has a NULL value (meaning no value stored in it), it can still be used in an arithmetic expression. In such expressions, NULL is typically treated as a zero, but the result of any arithmetic with NULL is also NULL., Reference: Oracle 12c SQL Language Reference indicates that if you include a numeric column with NULL in an arithmetic expression, the outcome will be NULL, meaning that the operation considers the NULL but does not necessarily treat it as zero (Oracle Database SQL Language Reference, 12c Release 1 (12.1))., E. PRODUCT_PRICE contains the value zero by default if no value is assigned to it. (Incorrect), Unless a default value is explicitly specified during the table creation or altered later, a numeric column like PRODUCT_PRICE does not automatically have a default value of zero., F. PRODUCT_NAME cannot contain duplicate values. (Incorrect), There is no constraint indicated that would prevent PRODUCT_NAME from containing duplicate values. Without a UNIQUE or PRIMARY KEY constraint, a column can contain duplicates., The correct answers are A and D. PRODUCT_ID can be the primary key because it's specified as NOT NULL, thus it can uniquely identify each row in the table. PRODUCT_PRICE can be used in an arithmetic expression with the understanding that if it's NULL, the result of the expression would be NULL as well., ]

Question 70

Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?

Options:

The first column in the last SELECT of the compound query

The first NUMBER column in the first SELECT of the compound query

The first VARCHAR2 column in the first SELECT of the compound query

The first column in the first SELECT of the compound query

The first NUMBER or VARCHAR2 column in the last SELECTof the compound query

Question 71

Examine this business rule:

Each student can work on multiple projects and earth project can have multiple students.

You must decide an Entity Relationship (ER) model for optional data storage and allow generating reports in this format:

STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true?

Options:

An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships.

PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.

The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities.

STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.