Part - 3 Oracle Interview

Question: 21.(B)

Examine the data of the EMPLOYEES table.
EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to
the EMPLOYEE_ID)



Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's manager, for all the employees who have a manager and earn more than 4000?

A. SELECT employee_id "Emp_id", emp_name "Employee",
salary,
employee_id "Mgr_id", emp_name "Manager"
FROM employees
WHERE salary > 4000;

B. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e, employees m
WHERE e.mgr_id = m.mgr_id
AND e.salary > 4000;

C. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.employee_id "Mgr_id", m.emp_name "Manager"
FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;

D. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.mgr_id "Mgr_id", m.emp_name "manager"
FROM employees e, employees m
WHERE e.mgr_id = m.employee_id
AND e.salary > 4000;

E. SELECT e.employee_id "Emp_id", e.emp_name "Employee",
e.salary,
m.mgr_id "Mgr_id", m.emp_name "Manager"
FROM employees e, employees m
WHERE e.employee_id = m.employee_id
AND e.salary > 4000;.

Answer: C

Explanation:

This statement lists the ID, name, and salary of the employee, and the ID and name
of the employee's manager, for all the employees who have a manager and earn more than 4000

Incorrect Answers

A: This statement does not check does employee have a manager or not, so it will not provide correct result.
B: Usage of 􀂳e.mgr_id = m.mgr_id􀂴 condition is wrong to achieve required result.
D: This statement uses 􀂳m.mgr_id􀂴 to show manager􀂶s manager, not employ􀂶s manager.
E: Usage of 􀂳WHERE e.employee_id = m.employee_id􀂴 condition is wrong to achieve required result.

Question: 22. (B)

You need to display the last names of those employees who have the letter "A"as the second character in their names.
Which SQL statement displays the required results?


A. SELECT last_name
FROM EMP
WHERE last_name LIKE '_A%';

B. SELECT last_name
FROM EMP
WHERE last name ='*A%'

C. SELECT last_name
FROM EMP
WHERE last name ='_A%';

D. SELECT last_name
FROM EMP
WHERE last name LIKE '*A%'

Answer: A

Explanation:

Statement in this answer will show correct results because usage of operator LIKE and format
mask '_A%' extract the last names of those employees who have the letter "A" as the second
character in their names. Symbol '_' in format mask substitute exactly one symbol and cannot be NULL.

Incorrect Answers

B: This statement will return only names starting from symbol '*'. It cannot be used as substitution symbol.
C: Usage of equity operator here is not appropriate in this case: query will look exact for first
symbol '_', it will not be considered as substitution symbol.
D: This statement will return only names starting from symbol '*'. It cannot be used as substitution symbol.

Question: 23. (B)

In which scenario would TOP N analysis be the best solution?

A. You want to identify the most senior employee in the company.

B. You want to find the manager supervising the largest number of employees.

C. You want to identify the person who makes the highest salary for all employees.

D. You want to rank the top three sales representatives who have sold the maximum number of
products.

Answer: D

Explanation:

If you want to rank the top three sales representatives who have sold the maximum number of
products TOP-N query will be the best solution. TON-N queries use inline views and are handy
for displaying a short list of table data, based on "greatest" or "least" criteria.

Incorrect Answers

A: To complete this request you don􀂶t need to use TOP-N query.
B: It is not necessary to use TOP-N query in this case.
C: To complete this request you don􀂶t need to use TOP-N query.

Question: 24. (B)
Evaluate this SQL statement:
SELECT c.customer_id, o.order_id, o.order_date, p.product_name
FROM customer c, curr_order o, product p
WHERE customer.customer_id = curr_order.customer_id
AND o.product_id = p.product_id
ORDER BY o.order_amount;
This statement fails when executed. Which change will correct the problem?

A. Include the ORDER_AMOUNT column in the SELECT list.

B. Use the table name in the ORDER BY clause.

C. Remove the table aliases from the WHERE clause.

D. Use the table aliases instead of the table names in the WHERE clause.

E. Remove the table alias from the ORDER BY clause and use only the column name.

Answer: D

Explanation:
When an alias is define for a table name in join then you cannot use the table name instead of
alias in the FROM clause while using alias in the SELECT list. An alias should be used in the
WHERE clause also.

Question: 25. (B)

You want to display the titles of books that meet these criteria:

1. Purchased before January 21, 2001
2. Price is less then $500 or greater than $900
You want to sort the results by their data of purchase, starting with the most recently bought
book. Which statement should you use?

A. SELECT book_title FROM books
WHERE price between 500 and 900
AND purchase_date < 􀂶21-JAN-2001􀂶 ORDER BY purchase_date; B. SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date < '21-JAN-2001' ORDER BY purchase date ASC; C. SELECT book_title FROM books WHERE price <> 900
AND purchase_date < '21-JAN-2001' ORDER BY purchase date DESC; D. SELECT book_title FROM books WHERE (price <> 900)
AND purchase_date < '21-JAN-2001' ORDER BY purchase date DESC; Answer: D Explanation: This statement provides required results. Incorrect Answers A: This query will show books with price in range $500 and $900, not less then $500 or greater than $900. B: This query will show books with prices exactly $500 or $900, not less then $500 or greater than $900. C: This order will not show correct rows because of incorrect syntax in the WHERE clause. Questions 26. (B) For which task would you use the WHERE clause in a SELECT statement? A. to designate the ORDER table location B. to compare PRODUCT_ID values to 7382 C. to display only unique PRODUCT_ID values D. to restrict the rows returned by a GROUP BY clause Answer: B Explanation: You can use the WHERE clause in the SELECT statement to implement the condition on the statement by comparing values. Question: 27. (B) The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) The registrar has requested a report listing the students' grade point averages (GPA), sorted from highest grade point average to lowest within each semester, starting from the earliest date. Which statement accomplishes this? A. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC; B. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end ASC, gpa ASC; C. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa DESC; D. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end DESC; E. SELECT student_id, semester_end, gpa FROM student_grades ORDER BY gpa DESC, semester_end ASC;. Answer: C Explanation: This answer shows correct syntax and semantics to receive desired result. Incorrect Answers A: Semesters will be sorted started from the oldest date, not the earliest. B: GPA data will be sorted in ascending order, what is opposite to our task. D: Semesters will be sorted started from the oldest date, not the earliest. Only difference with answer A is order of columns in the ORDER BY clause. E: This query has wrong order of columns to sort: results need to be sorted first by semester, than by grade point average. Question: 28. (B) The ORDERS table has these columns: ORDER_ID NUMBER(4) NOT NULL CUSTOMER_ID NUMBER(12) NOT NULL ORDER_TOTAL NUMBER(10,2) The ORDERS table tracks the Order nnmher, the order total, and the customer to whom the Order belongs. Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 2000.00 dollars? (Choose two.) A. SELECT customer_id, order_id, order_total FROM orders RANGE ON order_total (100 AND 2000) INCLUSIVE; B. SELECT customer_id, order_id, order_total FROM orders HAVING order_total BETWEEN 100 and 2000; C. SELECT customer_id, order_id, order_total FROM orders WHERE order_total BETWEEN 100 and 2000; D. SELECT customer_id, order_id, order_total FROM orders WHERE order_total >= 100 and <= 2000; E. SELECT customer_id, order_id, order_total FROM orders WHERE order_total >= 100 and order_total <= 2000; Answer: C & E Explanation: Answers C and E provide correct results to show. You can use BETWEEN or comparison operations to retrieve data. Incorrect Answers A: There is no RANGE ON or INCLUSIVE keyword in Oracle. B: HAVING clause can be use only in conjunction with the GROUP BY clause. D: Syntax "order_total >= 100 and <= 2000􀂴 is incorrect. Question: 29. (B) Examine the structure of the PRODUCT table.

You want to display all product identification numbers of products for which there are 500 or more available for immediate sale. You want the product numbers displayed alphabetically by supplier, then by product number from lowest to highest. Which statement should you use to achieve therequired results?

A. SELECT product_id
FROM product
WHERE qty_per_unit >= 500
ORDER BY supplier_id, product_id;

B. SELECT product_id
FROM product
WHERE qty_per_unit >= 500
SORT BY supplier_id, product_id;

C. SELECT product_id
FROM product
WHERE qty_per_unit >= 500
ORDER BY supplier_id, product_id DESC;

D. SELECT product_id
FROM product
WHERE qty_per_unit > 500
SORT BY supplier_id, product_id;

Answer: A

Explanation:

SELECT product_id
FROM product
WHERE qty_per_unit >= 500
ORDER BY supplier_id, product_id;
This statement will give the product_id from product table where qty_per_unit will be equal to and greater than 500 and it will sort it in ascending order by default.

Question: 30. (B)

Examine the data in TEACHER table.



Which query should you use to return the following values from the TEACHER table?
Name Subject

------------------------------------- -------------------
Jones, Karen HST_REVOL
Hopewell, Mary Elizabeth HST_RELIG

A. SELECT last_name||', '||first_name "Name", subject_id "Subject"
FROM teacher
WHERE subject_id LIKE 'HST\_%' ESCAPE '\';

B. SELECT last_name||', '||first_name "Name", subject_id "Subject"
FROM teacher
WHERE subject_id = 'HST\_R%';

C. SELECT last_name||', '||first_name "Name", subject_id "Subject"
FROM teacher
WHERE subject_id LIKE '%HST\_R%' ESC '\';

D. SELECT last_name||', '||first_name "Name", subject_id "Subject"
FROM teacher
WHERE subject_id LIKE 'HST_%';

Answer: A

Explanation:
When you need to have an exact match for the actual '%' and '_' characters use the ESCAPE option. This option specifies what the ESCAPE character is.