Online Access Free Oracle.1z0-071.v2021-11-28.q233 Practice Test (Page 26)

1z0-071 Exam Question 121

View the exhibit and examine the structure in ORDERSand ORDER_ITEMStables.

You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATEVIEWstatement would create the views successfully?

A.CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) ||
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
WHITH CHECK OPTION;

B.CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;

C.CREATE OR REPLACE VIEW ord_vu (order_id, order_date)
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
" NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;

D.CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
" NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;

1z0-071 Exam Question 122

View the Exhibit and examine the structure of the PRODUCT table.

Which two tasks would require subqueries? (Choose two.)

A.display the total number of products supplied by supplier 102 and have product status as 'OBSOLETE'

B.display all products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable

C.display suppliers whose PROD_LIST_PRICE is less than 1000

D.display the minimum PROD_LIST_PRICE for each product status

E.display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.

1z0-071 Exam Question 123

View the Exhibit and examine the data in the PRODUCTS table. (Choose the best answer.)

You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:
SQL > SELECT prod_name, prod_category, prod_min_price FROM products
Where prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR prod_min_price = 4000) AND prod_unit_of_measure <> ' '; Which statement is true?

A.It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.

B.It executes successfully but returns no result.

C.It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

D.It executes successfully and returns the required result.

1z0-071 Exam Question 124

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?

A.The ERD must have a 1:M relationship between the STUDENTSand PROJECTSentities.

B.The ERD must have a M:M relationship between the STUDENTSand PROJECTSentities that must be resolved into 1:M relationships.

C.STUDENT_IDmust be the primary key in the STUDENTSentity and foreign key in the PROJECTSentity.

D.PROJECT_IDmust be the primary key in the PROJECTSentity and foreign key in the STUDENTSentity.

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