Download Oracle 1z0-071 Mock Test Study Material [Q151-Q173]

NO.151 Examine the structure of the DEPARTMENTS table.

You execute the following command:

Which two statements are true?

NO.152 Which two statements will return the names of the three employees with the lowest salaries?

NO.153 View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the views successfully?

NO.154 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?

NO.155 In which three situations does a transaction complete?

NO.156 View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
ORDERS_MASTER
ORDER_ID
ORDER_TOTAL
1
1000
2
2000
3
3000
4
MONTHLY_ORDERS
ORDER_ID
ORDER_TOTAL
2
2500
3
Evaluate the following MERGE statement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?

NO.157 View the Exhibit and examine the structure of the CUSTOMERS table.

CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table.
CUSTOMERS need to be updated to reflect the latest information about the customers.
What is the error in the following MERGE statement?

NO.158 View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)

Examine this SQL statement:
SELECT department_id “DEPT_ID”, department_name, ‘b’
FROM departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, ‘a’
FROM departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output? (Choose two.)

NO.159 You have the privileges to create any type of synonym.
Which stalement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accesible to all users?

NO.160 Which two statements are true about views?

NO.161 Which two tasks can be performed by using Oracle SQL statements?

NO.162 Examine the structure of the PROMOTIONS table: (Choose the best answer.)

Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?

NO.163 Which two are true about a SQL statement using SET operators such as UNION?

NO.164 Which two statements are true regarding roles? (Choose two.)

NO.165 Which three actions can you perform by using the ORACLE_DATAPUMP access driver?

NO.166 In which normal form is a table, if it has no multi-valued attributes and no partial dependencies?

NO.167 View the Exhibit and examine the structure of CUSTOMERS table.
Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message “Not Available” displayed.
Which SQL statement would produce the required result?

NO.168 Which two statements are true about a self join?

NO.169 Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the creation and storage of data in the above table structure? (Choose two.)

NO.170 Examine the commands used to create the DEPARTMENT_DETAILS and the COURSE-DETAILS tables:
SQL> CREATE TABLE DEPARTMfiNT_DETAILS
DEPARTMENT_ID NUMBER PRIMARY KEY ,
DEPARTMEHT_NAME VARCHAR2(50) ,
HOD VARCHAP2(50));
SQL> CREATE TABLE COURSE-DETAILS
(COURSE ID NUMBER PRIMARY KEY ,
COURS_NAME VARCHAR2 (50) ,
DEPARTMEHT_ID NUMBER REFERENCES DEPARTMENT_DETAIL
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?

NO.171 Which statement is true about transactions?

NO.172 View the Exhibit and examine the data in the EMPLOYEES table.
Exhibit

You want to generate a report showing the total compensation paid to each employee to date.
You issue the following query:

What is the outcome?

NO.173 Examine the commands used to create the DEPARTMENT_DETAILS and the COURSE- DETAILS tables:
SQL> CREATE TABLE DEPARTMfiNT_DeTAILS
DEPARTMENT_ID NUMBER PRIMARY KEY ,
DEPARTMEHT_NAME VARCHAR2(50) ,
HOD VARCHAP2(50));
SQL> CREATE TABLE COURSE-DETAILS
(COURSE ID NUMBER PRIMARY KEY ,
COURS_NAME VARCHAR2 (50) ,
DEPARTMEHT_ID NUMBER REFERENCES DEPARTMENT_DETAIL
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?