Free 1z0-071 Questions for Oracle 1z0-071 Exam

Question #1

You want to write a query that prompts for two column names and the WHERE condition each time It is executed in a session but only prompts for the table name the first time it is executed. The variables used in your query are never undefined in your session . Which query can be used?

A. SELECT &col1, &col2
FROM "&table"
WHERE &condition;

B. SELECT &&col1,&&col2
FROM &table
WHERE &&condition= &&cond;

C. SELECT &col1, &col2
FROM &&table
WHERE &condition;

D. SELECT&&col1, &&col2
FROM &table
WHERE &&condition;

E. SELECT'&co11','&&co12'
FROM &table
WHERE'&&condition' ='&cond';

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #2

Examine this SQL statement:
SELECT cust_id, cust_last_name "Last Name
FROM customers
WHERE countryid=10
UNION
SELECT custid CUSTNO, cust_last_name
FROM customers
WHERE countryid=30
Identify three ORDER BY clauses, any one of which can complete the query successfully.

A. ORDER BY 2, cust_id

B. ORDER BY "Last Name"

C. ORDER BY"CUST NO"

D. ORDER BY CUSTNO

E. ORDERBY2, 1

Discussion 0

Correct Answer: A,B,E Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #3

Examine the description of the EMPLOYEES table:

Which two queries return rows for employees whose manager works in a different department?

A. SELECT emp. *
FROM employees emp
JOIN employees mgr
ON emp. manager_ id = mgr. employee_ id
AND emp. department_ id<> mgr.department_ id;

B. SELECT emp. *
FROM employees emp
WHERE manager_ id NOT IN (
SELECT mgr.employee_ id
FROM employees mgr
WHERE emp. department_ id < > mgr.department_ id
);

C. SELECT emp.*
FROM employees emp
WHERE NOT EXISTS (
SELECT NULL
FROM employees mgr
WHERE emp.manager id = mgr.employee_ id
AND emp.department_id<>mgr.department_id
);

D. SELECT emp.*
FROM employees emp
LEFT JOIN employees mgr
ON emp.manager_ id = mgr.employee_ id
AND emp. department id < > mgr. department_ id;

E. SELECT emp. *
FROM employees emp
RIGHT JOIN employees mgr
ON emp.manager_ id = mgr. employee id
AND emp. department id <> mgr.department_ id
WHERE emp. employee_ id IS NOT NULL;

Discussion 0

Correct Answer: A,C Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #4

Examine the data in the INVOICES table:

Examine the data in the CURRENCIES table:
CURRENCY_CODE
-------------
JPY
GPB
CAD
EUR
USD
Which query returns the currencies in CURRENCIES that are not present in INVOICES?

A. SELECT currency_ code FROM currencies
MINUS
SELECT currency_ code FROM invoices;

B. SELECT * FROM currencies
WHERE NOT EXISTS (
SELECT NULL FROM invoices WHERE currency_ code = currency_ code);

C. SELECT * FROM currencies
MINUS
SELECT * FROM invoices;

D. SELECT currency_ code FROM currencies
INTERSECT
SELECT currency_ code FROM invoices;

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #5

Choose the best answer.
Examine the description of the EMPLOYEES table:

Which query is valid?

A. SELECT depe_id,join_date,SUM(salary) FROM employees GROUP BY dept_id:

B. SELECT dept_id,MAX(AVG(salary)) FROM employees GROUP BY dept_id;

C. SELECT dept_id,AVG(MAX(salary)) FROM employees GROUP BY dapt_id;

D. SELECT dept_id, join_date,SUM(salary) FROM employees GROUP BY dept_id, join_date;

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #6

Examine the data in the PRODUCTS table:

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?

A. 1, 2 and 3

B. 1 and 3

C. 1 and 2

D. 2 and 3

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #7

Which two are true about global temporary tables?

A. Indexes can be created on them.

B. If the ON COMMIT clause is session-specific,the table is dropped when the session is terminated.

C. If the ON COMMIT clause Is transaction-specific, all rows in the table are deleted alter each COMMIT or ROLLBACK.

D. Backup and recovery operations are available for these tables.

E. Their data is always stored in the default temporary tablespace of the user who created them.

F. They can be created only by a user with the DBA role,but can be accessed by all users who can create a session.

Discussion 0

Correct Answer: A,C Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #8

In your session, the NLS._DAE_FORMAT is DD- MM- YYYY.There are 86400 seconds in a day.Examine this result:
DATE
02-JAN-2020
Which statement returns this?

A. SELECT To CHAR(TO _DATE('29-10-2019') + INTERVAL '2' NONTH + INTERVAL '5' DAY INEERVAL '120' SECOND, ' DD-MON-YYY) AS "date" FROM DUAL;

B. SELECT TO_ CHAR(TO_ DATE('29-10-2019') +INTERVAL '2'; MONTH + INTERVAL '5'; DAY - INTERVAL '86410' SECOND, ' DD-MON-YYYY') AS "date" FROM DUAL;

C. SELECT-TO_CHAR(TO _DATE('29-10-2019'+ INTERVAL '2' MONTH+INTERVAL '4' DAYINTERVAL
'120' SECOND, 'DD-MON-YY') AS "daTe"
FROM DUAL;

D. SELECT TO_ CHAR(TO_ DATE('29-10-2019') + INTERVAL '3' MONTH + INTERVAL '7' DAY - INTERVAL '360' SECOND, ' DD-MON-YYYY') AS "date" FROM DUAL;

E. SELECT-TO_CHAR(TO _DATE('29-10-2019'+ INTERVAL '2' MONTH+INTERVAL '6' DAYINTERVAL
'120' SECOND, 'DD-MON-YY') AS "daTe"
FROM DUAL;

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #9

The STORES table has a column START_DATE of data type DATE, containing the datethe row was inserted.
You only want to display details of rows where START_DATEis within the last 25 months.which WHERE clause can be used?

A. WHERE MONTHS_BETWEEN (start_date, SYSDATE)<=25

B. WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25

C. WHERE ADD_MONTHS (start date , 25)<= SYSDATE

D. WHERE TO_NUMBER(start_date - SYSDATE)<=25

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #10

Examine the description of the PRODUCT_ STATUS table:

The STATUS column contains the values IN STOCK or OUT OF STocK for each row.
Which two queries will execute successfully?

A. SELECT PROD_ID||q'('s not available)' "CURRENT AVAILABILITY"
FROM product_ status WHERE status = 'OUT OF STOCK';

B. SELECT prod_id "CURRENT AVAILABILITY"||q' ('s not available)' from product_ status WHERE status
= 'OUT OF STOCK';

C. SELECT prod_id ||q'(' s not available)' 'CURRENT AVAILABILITY' FROM
product_ status WHERE status = 'OUT OF STOCK'

D. SELECT prod_id ||q"' s not available" FROM
product_ status WHERE status = 'OUT OF STOCK';

E. SELECT PROD_ID||q'('s not available)' FROM
product_ status WHERE status = 'OUT OF STOCK';

F. SELECT prod_id q's not available" from product_ status WHERE status = 'OUT OF STOCK';

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #11

Examine this query:
SELECT employee_id,first_name,salary
FROM employees
WHERE hire_date>'&1';
Which two methods should you use to prevent prompting for a hire date value when this query is executed?

A. Store the query in a script and pass the substitution value to the script when executing it.

B. Execute the SET VERIFY ON command before executing the query.

C. Use the DEFINE command before executing the query.

D. Execute the SET VERIFY OFF command before executing the query.

E. Use the UNDEFINE command before executing the query.

F. Replace'&1' with'&&1' in the query.

Discussion 0

Correct Answer: C,D Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #12

Which three statements are true about an ORDER BY clause?

A. An ORDR BY clause will always precede a HAVI NG clause if both are used in the same top-level

B. An ORDER BY clause can perform a binary sort

C. By default an ORDERBY clause sorts rows in ascending order

D. An ORDER BY clause always sorts NULL values last.

E. An ORDER BY clause can perform a linguistic sort

Discussion 0

Correct Answer: B,C,E Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #13

which three statements are true regarding single row subqueries?

A. A SQL STATEMENT MAY HAVE MULTIPLE SINGLE ROW SUBQUERY BLOCKS.

B. THEY CAN BE USED in the where clause.

C. they must return a row to prevent errors in the SQL statement.

D. THEY CAN BE USED IN THE HAVING CLAUSE

E. they must be placed on the left side of the comparison operator or condition.

F. THEY MUST BE PLACED ON THE RIGHT SIDE OF THE COMPARISON OPERATOR OR CONDITION.

Discussion 0

Correct Answer: A,B,D Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #14

BOOK_SEQ is an existing sequence in your schema.
Which two CREATE TABLE commands are valid?

A. CREATE TABLE bookings ( bk_id NUMBER(4),
start_date DATE DEFAULT SYSDATE,
end_date DATE DEFAULT (end_date >= start_date));

B. CREATE TABLE bookings (
bk_id NUMBER(4) NOT NULL DEFAULT book_seq.CURRVAL,
start_date DATE NOT NULL,
end_date DATE DEFAULT SYSDATE);

C. CREATE TABLE bookings (
bk_id NUMBER(4) DEFAULT book_seq.CURRVAL,
start_date DATE DEFAULT SYSDATE,
end_date DATE DEFAULT start date);

D. CREATE TABLE bookings (
bk_id NUMBER(4) NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
end_date DATE DEFAULT SYSDATE);

E. CREATE TABLE bookings (
bk_id NUMBER(4) DEFAULT book_seq.NEXTVAL PRIMARY KEY,
start_date DATE DEFAULT SYSDATE,
end_date DATE DEFAULT SYSDATE NOT NULL);

Discussion 0

Correct Answer: D,E Vote an answer

Explanation: Only visible for ExamDiscuss members. You can sign-up / login (it's free).

Question #15

Examine the description of the countries table: