QUESTION: 198

Examine the structure of the EMPLOYEES table:

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.

You want to display the name, joining date, and manager for all employees. Newly hired employees are yet to be assigned a department or a manager. For them, ‘No Manager’ should be displayed in the MANAGER column.

Which SQL query gets the required output?

SELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) Manager
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);
SELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) Manager
FROM employees e LEFT OUTER JOIN employees m
ON (e.manager_id = m.employee_id);
SELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) Manager
FROM employees e RIGHT OUTER JOIN employees m
ON (e.manager_id = m.employee_id);
SELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) Manager
FROM employees e NATURAL JOIN employees m
ON (e.manager_id = m.employee_id).

Answer(s): B

Show Answer Next Question

QUESTION: 197

View the Exhibit and examine the structure of the PRODUCT table.

Which two tasks would require subqueries? (Choose two.)

display all products whose minimum list price is more than the average list price of products having the status ‘orderable’
display the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’
display the number of products whose list prices are more than the average list price
display all suppliers whose list price is more than 1000
display the minimum list price for each product status

Answer(s): A,C

Show Answer Next Question

QUESTION: 196

Evaluate the following CREATE TABLE commands:

CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER (4));
CREATE TABLE ord_items
(ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );

Why would the ORD_ITEMS table not get created?

SYSDATE cannot be used with the CHECK constraint.
The BETWEEN clause cannot be used twice for the same table.
The CHECK constraint cannot be placed on columns having the DATE data type.
ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer(s): A

Show Answer Next Question

QUESTION: 195

View the Exhibit and examine the structure of the CUSTOMERS table.

You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10,000.
Evaluate the following two queries:

SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR
UPPER (cust_last_name) LIKE ‘B%’ OR UPPER (cust_last_name) LIKE ‘C%’)
AND cust_credit_limit < 10000;

SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER (cust_last_name) BETWEEN ‘A’ AND ‘C’
AND cust_credit_limit < 10000;

Which statement is true regarding the execution of the above queries?

Only the second query gives the correct result
Both execute successfully but do not give the required result
Only the first query gives the correct result
Both execute successfully and give the same result

Answer(s): C

Show Answer Next Question

QUESTION: 194

You executed the following CREATE TABLE statement that resulted in an error:

SQL> CREATE TABLE employees(emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2(500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOY NULL(ename));

Identify two reasons for the error.

The NOT NULL constraint on the ENAME column must be defined as the column level
FOREIGN KEY defined on the DEPT_ID column must be at the table level only
Only one LONG column can be used per table
The FOREIGN KEY keyword is missing in the constraint definition
The PRIMARY KEY constraint in the EMP_ID column must have a name and must be defined at the table level only

Answer(s): A,C

Show Answer Next Question

Free Oracle 1Z0-071 Exam Questions (page: 14)

QUESTION: 198

QUESTION: 197

QUESTION: 196

QUESTION: 195

QUESTION: 194

1Z0-071 Exam Discussions & Posts