Oracle 1Z0-071 Quiz:79 Topic:13 Questions:391-395

Free Practice Mock Questions Set 391-395 () for Oracle 1Z0-071 Exam, according to official Oracle Database SQL exam syllabus topic # 13

Question: 1

Examine the business rule:

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

You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:

STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK

Which two statements are true in this scenario?

AThe ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.

BThe ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.

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

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

EAn 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.

Answer : B, E

Show Answer

Hide Answer

Question: 2

View the Exhibit and examine the details of PRODUCT_INFORMATION table.

PRODUCT_NAME

CATEGORY_ID

SUPPLIER_ID

Inkjet C/8/HQ

102094

Inkjet C/4

102090

LaserPro 600/6/BW

102087

LaserPro 1200/8/BW

102099

Inkjet B/6

102096

Industrial 700/ID

102086

Industrial 600/DQ

102088

Compact 400/LQ

102087

Compact 400/DQ

102088

HD 12GB /R

102090

HD 10GB /I

102071

HD 12GB @7200 /SE

102057

HD 18.2GB @10000 /E

102078

HD 18.2GB @10000 /I

102050

HD 18GB /SE

102083

HD 6GB /I

102072

HD 8.2GB@5400

102093

You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:

SELECT product_name

FROM product_information

WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;

Which statement is true regarding the execution of the query?

AIt would not execute because the same column has been used in both sides of the AND logical operator to form the condition.

BIt would not execute because the entire WHERE clause condition is not enclosed within the parentheses.

CIt would execute and the output would display the desired result.

DIt would execute but the output would return no rows.

Answer : D

Show Answer

Hide Answer

Question: 3

View the exhibit and examine the structure of the STORES table.

STORES table

NameNull?Type

---------------------- -------------

STORE_IDNUMBER

NAMEVARCHAR2(100)

ADDRESSVARCHAR2(200)

CITYVARCHAR2(100)

COUNTRYVARCHAR2(100)

START_DATEDATE

END_DATEDATE

PROPERTY_PRICENUMBER

You want to display the NAME of the store along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.

The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.

Which SQL statement would get the desired output?

ASELECT name, concat (address| | ','| |city| |', ', country) AS full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;

BSELECT name, concat (address| | ','| |city| |', ', country) AS full_address,start_date,property_price, property_price*115/100FROM storesWHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

CSELECT name, address||','||city||','||country AS full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

DSELECT name, concat (address||','| |city| |', ', country) AS full_address,start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

Answer : D

Show Answer

Hide Answer

Question: 4

Examine the command:

SQL> ALTER TABLE books_transactions

ADD CONSTRAINT fk_book_id FOREIGN KEY (book_id)

REFERENCES books (book_id) ON DELETE CASCADE;

What does ON DELETE CASCADE imply?

AWhen the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.

BWhen the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.

CWhen a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.

DWhen a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Answer : C

Show Answer

Hide Answer

Question: 5

Which three statements are true about multiple-row subqueries?

AThey can contain a subquery within a subquery.

BThey can return multiple columns as well as rows.

CThey cannot contain a subquery within a subquery.

DThey can return only one column but multiple rows.

EThey can contain group functions and GROUP BY and HAVING clauses.

FThey can contain group functions and the GROUP BY clause, but not the HAVING clause.

Answer : A, B, E

Show Answer

Hide Answer

Quiz 75

Quiz 76

Quiz 77

Quiz 78

Quiz 79

Quiz 80

Quiz 81

Quiz 82