Oracle 1Z0-071 Quiz 78 Topic 10 Questions 386-390

Question: 1

Examine the structure of the EMPLOYEES table.

NameNull?Type

---------------------- ------------

EMPLOYEE_IDNOT NULLNUMBER(6)

FIRST_NAMEVARCHAR2(20)

LAST_NAMENOT NULLVARCHAR2(25)

EMAILNOT NULLVARCHAR2(25)

PHONE NUMBERVARCHAR2(20)

HIRE_DATENOT NULLDATE

JOB_IDNOT NULLVARCHAR2(10)

SALARYNUMBER(8,2)

COMMISSION_PCTNUMBER(2,2)

MANAGER_IDNUMBER(6)

DEPARTMENT_IDNUMBER(4)

There is a parent/child relationship between EMPLOYEE_ID and 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_ID is 123.

Which query provides the correct output?

ASELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;

BSELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;

CSELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;

DSELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;

Show Answer

Question: 2

Sales data of a company is stored in two tables, SALES1 and SALES2, with some data being duplicated across the tables. You want to display the results from the SALES1 table, which are not present in the SALES2 table.

SALES1 table

NameNullType

-----------------------------------------------

SALES_IDNUMBER

STORE_IDNUMBER

ITEMS_IDNUMBER

QUANTITYNUMBER

SALES_DATEDATE

SALES2 table

NameNullType

----------------------------------------------

SALES_IDNUMBER

STORE_IDNUMBER

ITEMS_IDNUMBER

QUANTITYNUMBER

SALES_DATEDATE

Which set operator generates the required output?

AINTERSECT

BUNION

CPLUS

DMINUS

ESUBTRACT

Show Answer

Question: 3

Which three statements are true regarding the data types?

AThe minimum column width that can be specified for a VARCHAR2 data type column is one.

BOnly one LONG column can be used per table.

CA TIMESTAMP data type column stores only time values with fractional seconds.

DThe BLOB data type column is used to store binary data in an operating system file.

EThe value for a CHAR data type column is blank-padded to the maximum defined column width.

Show Answer

Question: 4

Which three statements are true regarding subqueries?

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

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

CMain query and subquery can get data from different tables.

DSubqueries can contain GROUP BY and ORDER BY clauses.

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

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

Show Answer

Question: 5

Examine the structure of the MEMBERS table.

NameNull?Type

------------------------------------------------------------

MEMBER_IDNOT NULLVARCHAR2 (6)

FIRST_NAMEVARCHAR2 (50)

LAST_NAMENOT NULLVARCHAR2 (50)

ADDRESSVARCHAR2 (50)

CITYVARCHAR2 (25)

STATENOT NULL VARCHAR2 (3)

Which query can be used to display the last names and city names only for members from the states MO and MI?

ASELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';

BSELECT last_name, city FROM members WHERE state LIKE 'M%';

CSELECT last_name, city FROM members WHERE state IN ('MO', 'MI');

DSELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

Show Answer

Oracle 1Z0-071 Quiz:78 Topic:10 Questions:386-390