1. Home
  2. Oracle
  3. Oracle PL
  4. 1z0-071
  5. Oracle Database 12c SQL Questions and Answers

Pass the Oracle Oracle PL 1z0-071 Questions and answers with CertsForce

 Oracle Exams      Go to Exam Detail      Get Premium Access
Viewing page 3 out of 10 pages
Viewing questions 21-30 out of questions
Questions # 21:

Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY.

Only the EMPLOYEES_ID column is indexed.

Rows exist for employees 100 and 200.

Examine this statement:

UPDATE employees

SET (job_id, salary) =

(SELECT job_id, salary

FROM employees

WHERE employee_id = 200)

WHERE employee id=100;

Which two statements are true?

Options:
A.

Employees 100 and 200 will have the same SALARY as before the update command.

B.

Employee 100 will have SALARY set to the same value as the SALARY of employee 200.

C.

Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.

D.

Employees 100 and 200 will have the same JOB ID as before the update command.

E.

Employee 200 will have SALARY set to the same value as the SALARY of employee 100.

F.

Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100

Expert Solution
Questions # 22:

You need to allow user ANDREW to:

1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table.

2. GRANT tha permission to other users.

Which statement will do this?

Options:
A.

GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;

B.

GRANT UPDATE ON customers. title, customers.address TO andrew WITH GRANT OPTION;

C.

GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;

D.

GRANT UPDATE (title, address) ON customers TO andrew;

E.

GRANT UPDATE ON customers. title, customers.address TO andrew;

F.

GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION:

Expert Solution
Questions # 23:

Examine this list of requirements for a sequence:

1. Name:EMP_SEQ

2. First value returned:1

3. Duplicates are never permitted.

4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID COLUMN.

5. Reduce the chances of gaps in the values.

Which two statements will satisfy these requirements?

Options:
A.

CREATE SEQUENCE emp_seq START WITH 1 INCRENENT BY 1 NOCACHE;

B.

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;

C.

CREATE SEQUENCE emp_seq NOCACHE;

D.

CREATE SEQUENCE emp_seq START WITH 1 CACHE;

E.

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;

F.

CREATE SEQUENCE emp_seq;

Expert Solution
Questions # 24:

Which two are true about external tables that use the ORACLE _DATAPUMP access driver?

Options:
A.

Creating an external table creates a directory object.

B.

When creating an external table, data can be selected only from a table whose rows are stored in database blocks.

C.

When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks.

D.

Creating an external table creates a dump file that can be used by an external table in the same or a different database.

E.

Creating an external table creates a dump file that can be used only by an external table in the same database.

Expert Solution
Questions # 25:

Which two are true about rollbacks?

Options:
A.

The ROLLBACK statement does not release locks resulting from table updates.

B.

Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.

C.

A transaction interrupted by a system failure is automatically rolled back.

D.

If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .

E.

Data consistency is not guaranteed after a rollback.

Expert Solution
Questions # 26:

Which two statements will do an implicit conversion?

Options:
A.

SELECT * FROM customers WHERE customer_ id = 0001 ;

B.

SELECT * FROM customers WHERE customer id = ‘0001’;

C.

SELECT * FROM customers WHERE insert_ date = DATE ‘2019-01-01’;

D.

SELECT * FROM customers WHERE insert date =’01-JAN-19’

E.

SELECT * FROM customers WHERE TO_ CHAR (customer_ id) =’0001’;

Expert Solution
Questions # 27:

Examine this description of the PRODUCTS table:

You successfully execute this command:

CREATE TALE new_prices(prod_id NUBER(2),price NUMBER(8,2));

Which two statements execute without errors?

Options:
A.

MERGE INTO new_prices n

USING(SELECT*FROM products)p

WHEN MATECHED THEN

UPDATE SET n.price=p.cost*.01

WHEN NOT MATCHED THEN

INSERT(n.prod_id,n.price)VALUES (p.prod_id,cost*01)

WHERE(p.cost<200);

B.

MERGE INTO new_prices n

USING(SELECT*FROM product WHERE cost>150) p

ON (n.prod_id=p.prod_id)

WHEN NATCHED THEN

DELETE WHERE(p.cost<200)

WHEN NOT MATCHED THEN

INSERT (n.prod_id,n.price)VALUES (p.prod_id,p.cost*.01);

C.

MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost>150) p

ON (n.prod_id=p.prod_id)

WHEN NATCHED THEN

UPDATE SET n.price=p.cost*.01

DELETE WHERE (p.cost<200);

D.

MERGE INTO new_prices n

USING products p

WHEN NOT NATCHED THEN

INSERT (n.prod_id, n.price)VALUES (p.prod_id,cost*.01)

WHERE (p.cost <200);

Expert Solution
Questions # 28:

Examine the data in the PRODUCTS table:

Question # 28

Examine these queries:

1. SELECT prod name, prod list

FROM products

WHERE prod 1ist NOT IN(10，20) AND category _id=1;

2. SELECT prod name, | prod _ list

FROM products

WHERE prod list < > ANY (10，20) AND category _id= 1;

SELECT prod name, prod _ list

FROM products

WHERE prod_ list <> ALL (10， 20) AND category _ id= 1;

Which queries generate the same output?

Options:
A.

1 and 3

B.

1, 2 and 3

C.

2 and 3

D.

1 and 2

Expert Solution
Questions # 29:

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

Options:
A.

SELECT last_name, salary

FROM employees

WHERE ROWNUM<=3

B.

SELECT last_name,salary

FROM employees

ORDER BY salary

FETCH FIRST 3 ROWS ONLY;

C.

SELECT last_name,salary

FROM employees

WHERE ROWNUM<=3

ORDER BY (SELECT salary FROM employees);

D.

SELECT last_name,salary

FROM (SELECT * FROM employees ORDER BY salary)

E.

SELECT last_name,salary

FROM employees

FETCH FIRST 3 ROWS ONLY

ORDER BY salary;

Expert Solution
Questions # 30:

Which two are true about creating tables in an Oracle database?

Options:
A.

A create table statement can specify the maximum number of rows the table will contain.

B.

The same table name can be used for tables in different schemas.

C.

A system privilege is required.

D.

Creating an external table will automatically create a file using the specified directory and file name.

E.

A primary key constraint is manadatory.

Expert Solution
Viewing page 3 out of 10 pages
Viewing questions 21-30 out of questions