[Dec 26, 2023] 1z0-071 Free Exam Questions with Quality Guaranteed [Q85-Q107]

Rate this post

[Dec 26, 2023] 1z0-071 Free Exam Questions with Quality Guaranteed

1z0-071 Free Exam Files Downloaded Instantly

QUESTION 85
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.

You executed this UPDATE statement:

Which statement is true regarding the execution? (Choose the best answer.)

It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

It would not execute because two tables cannot be referenced in a single UPDATE statement.

It would execute and restrict modifications to the columns specified in the SELECT statement.

It would not execute because a SELECT statement cannot be used in place of a table name.

QUESTION 86
Which two are true about granting privilege on objects?

The owner of an object acquires all object privilege on that object by default.

The WITH GRANT OPTION clause can be used only by DBA users.

A table owner must grant the references privilege to allow other users to create FOREIGN KEY constraints using that table.

An object privilege can be granted to a role only by the owner of that object.

An object privilege can be granted to other users only by the owner of object.

QUESTION 87
Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

A sequence can issue duplicate values

A sequence’s unallocated cached value are lost if the instance shuts down

Sequences can always have gaps

Two or more tables cannot have keys generated from the same sequence

A sequence can only be dropped by a DBA

A sequence number that was allocated can be rolled back if a transaction fails

QUESTION 88
Which three statements are true regarding subqueries?

Multiple columns or expressions can be compared between the main query and subquery.

Subqueries can contain ORDER BY but not the GROUP BY clause.

Main query and subquery can get data from different tables.

Subqueries can contain GROUP BY and ORDER BY clauses.

Main query and subquery must get data from the same tables.

Only one column or expression can be compared between the main query and subquery.

QUESTION 89
View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?

UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;

UPDATE dept dSET city = ANY (SELECT cityFROM locations l)

UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

QUESTION 90
View the Exhibit and examine the structure of CUSTOMERStable.

Evaluate the following query:

Which statement is true regarding the above query?

It produces an error because the condition on the CUST_CITYcolumn is not valid.

It produces an error because the condition on the CUST_FIRST_NAMEcolumn is not valid.

It produces an error because conditions on the CUST_CREDIT_LIMITcolumn are not valid.

It executes successfully.

QUESTION 91
View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

You wrote this SQL statement to retrieve EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, for all employees:
SELECT employee_id, first_name, department_name
FROM employees
NATURAL JOIN departments;
The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

The table prefix is missing for the column names in the SELECT clause.

The NATURAL JOIN clause is missing the USING clause.

The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.

The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

QUESTION 92
Examine this query:
SELECT TRUNC(ROUND(156.00, -2), -1) FROM DUAL;
What is the result?

200

100

160

150

QUESTION 93
Which two statements are true about a self join?

It can be a left outer join.

It must be a full outer join.

It can be an inner join.

It must be an equijoin.

The join key column must have an index.

QUESTION 94
n the customers table, the CUST_CITY column contains the value ‘Paris’ for the CUST_FIRST_NAME ‘Abigail’.
Evaluate the following query:

What would be the outcome?

Abigail PA

Abigail Pa

Abigail IS

An error message

QUESTION 95
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.)

ORDER BY DEPT_NAME;

ORDER BY DEPT_ID;

ORDER BY ‘b’;

ORDER BY 3;

QUESTION 96
Examine the description of the PRODUCT_DETAILStable:

Which two statements are true? (Choose two.)

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

PRODUCT_PRICEcan be used in an arithmetic expression even if it has no value stored in it.

EXPIRY_DATEcannot be used in arithmetic expressions.

PRODUCT_IDcan be assigned the PRIMARY KEYconstraint.

EXPIRY_DATEcontains the SYSDATEby default if no date is assigned to it.

PRODUCT_NAMEcannot contain duplicate values.

QUESTION 97
Which two are true about using constraints?

A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parenttable must have the same names.

A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.

A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.

PRIMARY KEY and FOREIGNY constraints can be specified at the column and at the table level

A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

NOT NULL can be specified at the column and at the table level.

QUESTION 98
View the Exhibit and examine the description for the PRODUCTS and SALES table.

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table with ON DELETE CASCADE option. The SALES table contains data for the last three years. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years.
Which is the valid DELETE statement?

DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id – 3*365 = SYSDATE );

DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id – 3*365 );

DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE –
3*365 >= time_id);

DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE – 3*365 );

QUESTION 99
Evaluate the following statement.

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent WHEN clauses.

All rows are evaluated by all the three WHEN clauses.

Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

The INSERT statement will return an error because the ELSE clause is missing.

QUESTION 100
View the Exhibit and examine the structure of CUSTOMERS table.

Evaluate the following query:

Which statement is true regarding the above query?

It produces an error because the condition on the CUST_CITY column is not valid.

It produces an error because the condition on the CUST_FIRST_NAME column is not valid.

It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.

It executes successfully.

QUESTION 101
Examine the structure of the EMPLOYEES table.

You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output? (Choose two.)

SELECT MIN(Salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365
GROUP BY MIN(salary), MAX(salary);

SELECT minsal, maxsal
FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365)
GROUP BY maxsal, minsal;

SELECT minsal, maxsal
FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employees
WHERE hire_date < SYSDATE-365
GROUP BY MIN(salary), MAX(salary);

SELECT MIN(Salary), MAX(salary)
FROM (SELECT salary FROM
employees
WHERE hire_date < SYSDATE-365);

QUESTION 102
The following are the steps for a correlated subquery, listed in random order:
* The WHERE clause of the outer query is evaluated.
* The candidate row is fetched from the table specified in the outer query.
* This is repeated for the subsequent rows of the table, till all the rows are processed.
* Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

2, 1, 4, 3

4, 1, 2, 3

4, 2, 1, 3

2, 4, 1, 3

QUESTION 103
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 output?

ORDER BY DEPT_NAME;

ORDER BY DEPT_ID;

ORDER BY ‘b’;

ORDER BY 3;

QUESTION 104
Examine this statement:
SELECT1 AS id,’ John’ AS first_name, NULL AS commission FROM dual
INTERSECT
SELECT 1,’John’ null FROM dual ORDER BY 3;
What is returned upon execution?[

2 rows

0 rows

An error

1 ROW

QUESTION 105
View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?

UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;

UPDATE dept dSET city = ANY (SELECT cityFROM locations l)

UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

QUESTION 106
Which three statements are true about GLOBAL TEMPORARY TABLES?

GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.

A TRUNCATE command issued in a session causes all rows In a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.

A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

A GLOBAL TEMPORARY TABLE’s definition is available to multiple sessions.

Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.

GLOBAL TEMPORARY TABLE space allocation occurs at session start.

QUESTION 107
You need to list the employees in DEPARTMENT_ID 20 days in a single row, ordered by HIRE_DATE.
Examine the sample output:

Which query will provide the required output?

SELECT LISTAGG(last_name)WITHIN GROUP ORDER BY (hire_date) “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;

SELECT LISTAGG(last_name, ‘; ‘)WITHIN GROUP ORDER BY (hire_date) “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;

SELECT LISTAGG(last_name, ‘; ‘) “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;WITHIN GROUP ORDER BY (hire_date);

SELECT LISTAGG(last_name, ‘; ‘) “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;ORDER BY (hire_date);

Q&As with Explanations Verified & Correct Answers: https://www.actualcollection.com/1z0-071-exam-questions.html

Leave a comment Cancel reply