Free Oracle 1z0-071 Practice Test & Real Exam Questions, Page 5

Question #57

Evaluate the following query:

What would be the outcome of the above query?

A. It executes successfully and introduces an 's at the end of each promo_name in the output.

B. It produces an error because flower braces have been used.

C. It executes successfully and displays the literal " {'s start date was \> " for each row in the output.

D. It produces an error because the data types are not matching.

Discussion 0

Correct Answer: A Vote an answer

Question #58

In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10 years.
Evaluate the following SQL statement:

What would be the result?

A. It gives an error because NVL function cannot be used with UPDATE.

B. It executes successfully but no rows updated.

C. It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.

D. It gives an error because multiple NVL functions are used in an expression.

Discussion 0

Correct Answer: C Vote an answer

Question #59

Which two are true? (Choose two.)

A. ADD_MONTHS adds a number of calendar months to a date.

B. CEIL returns the largest integer less than or equal to a specified number.

C. LAST_DAY returns the date of the last day of the month for the date argument passed to the function.

D. LAST_DAY returns the date of the last day of the current month only.

E. LAST_DAY returns the date of the last day of the previous month only.

F. CEIL requires an argument which is a numeric data type.

Discussion 0

Correct Answer: A,B Vote an answer

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

Question #60

The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully? (Choose two.)

A. SELECT NVL2 (cust_credit_limit * .15, `Not Available') FROM customers;

B. SELECT TO_CHAR(NVL(cust_credit_limit * .15, `Not Available')) FROM customers;

C. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), `Not Available') FROM customers;

D. SELECT NVL (cust_credit_limit * .15, `Not Available') FROM customers;

E. SELECT NVL(TO_CHAR(cust_credit_limit * .15), `Not Available') FROM customers;

Discussion 0

Correct Answer: A,E Vote an answer

Question #61

View the exhibit and examine the structure of ORDERSand CUSTOMERStables.

Which INSERT statement should be used to add a row into the ORDERStable for the customer whose CUST_LAST_NAMEis Robertsand CREDIT_LIMITis 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

A. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600) .order_total) VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000);

B. INSERT INTO orders
VALUES (1,'10-mar-2007', 'direct',
(SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND
credit_limit=600), 1000);

C. INSERT INTO orders (order_id.order_date.order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit _limit=600) .order_total) VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000);

D. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts'ANDc. Credit_limit=600) VALUES (1,'10-mar-2007',
'direct',(SELECT customer_id FROM customers
WHERE cust_last_name='Roberts' AND
Credit_limit=600), 1000);

Discussion 0

Correct Answer: B Vote an answer

Question #62

Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)

A. The output is sorted by the UNION ALL operator

B. Duplicates are eliminated automatically by the UNION ALL operator

C. The names of columns selected in each SELECT statement must be identical

D. NULLS are not ignored during duplicate checking

E. The number of columns selected in each SELECT statement must be identical

Discussion 0

Correct Answer: D,E Vote an answer

Question #63

Examine the data in the EMPtable:

You execute this query:

Why does an error occur?

A. An alias name must always be specified in quotes.

B. An alias name must not be used in a GROUP BY clause.

C. An alias name must not contain space characters.

D. An alias name must not be used in an ORDER BY clause.

Discussion 0

Correct Answer: D Vote an answer

Question #64

An Oracle database server session has an uncommitted transaction in progress which updated
5000 rows in a table.
In which three situations does the transactions complete thereby committing the updates?
(Choose three.)

A. when a COMMIT statement is issued by the same user from another session in the same database instance

B. when the session logs out successfully

C. when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

D. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT

E. when a CREATE INDEX statement is executed successfully in the same session

F. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT

Discussion 0

Correct Answer: B,D,E Vote an answer

Question #65

View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

Evaluate this SQL statement:
SELECT TO_CHAR (list_price, '$9,999')
From product_information;
Which two statements are true regarding the output? (Choose two.)

A. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.

B. A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.

C. A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.

D. A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.

Discussion 0

Correct Answer: C,D Vote an answer

Question #66

View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)

You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result? (Choose two.)

A. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

B. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.

D. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.

E. The values in all columns would be returned in descending order.

Discussion 0

Correct Answer: C,D Vote an answer

Question #67

Examine the description of the EMPLOYEES table:

Which two statements will run successfully? (Choose two.)

A. SELECT `The first_name is `'' || first_name || `'' FROM employees;

B. SELECT `The first_name is ` || first_name || `' FROM employees;

C. SELECT `The first_name is `' || first_name || `' FROM employees;

D. SELECT `The first_name is `'' || first_name || ``'' FROM employees;

E. SELECT `The first_name is \`' || first_name || `\`' FROM employees;

Discussion 0

Correct Answer: B Vote an answer

Question #68

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?

A. It generates an error because the alias is not valid.

B. It generates an error because the concatenation operator can be used to combine only two items.

C. IT executes successfully and gives the correct output.

D. It generates an error because the usage of the ROUND function in the expression is not valid.

E. It executes successfully but does not give the correct output.

Discussion 0

Correct Answer: E Vote an answer

Question #69

Which two are true about scalar subquery expressions? (Choose two.)

A. You must enclose them in parentheses.

B. They can return at most one row.

C. They can return two columns.

D. You cannot correlate them with a table in the parent statement.

E. You can use them as a default value for a column.

Discussion 0

Correct Answer: A,B Vote an answer

Question #70

Examine the description of the EMPLOYEES table:

Which query is valid?

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

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

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

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

Discussion 0

Correct Answer: C Vote an answer

Free Oracle 1z0-071 Practice Test & Real Exam Questions

Download Free Oracle 1z0-071 Demo