Free Access Oracle.1z0-071.v2023-10-02.q276 Practice Test (Page 39)

Question 186

Evaluate the following SQL statement:
SELECT product_name || 'it's not available for order'
FROM product_information
WHERE product_status = 'obsolete';
You received the following error while executing the above query:
ERROR
ORA-01756: quoted string not properly terminated
What would you do to execute the query successfully?

A.Remove the single quotation marks enclosing the character literal string in the SELECT clause

B.Use the escape character to negate the single quotation mark within the literal character string in the SELECT clause

C.Enclose the character literal string in the SELECT clause within double quotation marks

D.Use the Oracle (q) operator and delimiter to allow the use of a single quotation mark within the literal character string in the SELECT clause

Question 187

Which two tasks can be performed by using Oracle SQL statements? (Choose two.)

A.changing the password for an existing database user

B.connecting to a database instance

C.querying data from tables in different databases

D.starting up a database instance

E.executing operating system (OS) commands in a session

Question 188

Examine the structure of the EMPLOYEEStable.

There is a parent/child relationship between EMPLOYEE_IDand MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_IDis 123.
Which query provides the correct output?

A.SELECT e.last_name, e.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.employee_id = m.employee_id)
WHERE e.employee_id = 123;

B.SELECT m.last_name, e.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.manager_id = m.manager_id)
WHERE e.employee_id = 123;

C.SELECT e.last_name, m.manager_id
FROM employees e LEFT OUTER JOIN employees m
on (e.employee_id = m.manager_id)
WHERE e.employee_id = 123;

D.SELECT e.last_name, m.manager_id
FROM employees e RIGHT OUTER JOIN employees m
on (e.manager_id = m.employee_id)
AND e.employee_id = 123;

Question 189

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?

A.SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)

B.SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN department_details d ON (c.depatrment_id=d.department_id)

C.SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)

D.SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>d. department_id)