Part - 6 Oracle Interview

Question: 51. (C)

Examine the structures of the PATIENT, PHYSICIAN, and ADMISSION tables.
PATIENT Table



Which SQL statement will produce a list of all patients who have more than one physician?

A. SELECT p.patient_id
FROM patient p
WHERE p.patient_id IN (SELECT patient_id
FROM admission
GROUP BY patient_id
HAVING COUNT(*) > 1);

B. SELECT DISTINCT a.patient_id
FROM admission a, admission a2
WHERE a.patient_id = a2.patient_id
AND a.physician_id <> a2.physician_id;

C. SELECT patient_id
FROM admission
WHERE COUNT(physician_id) > 1;

D. SELECT patient_id
FROM patient FULL OUTER JOIN physician;

Answer: B

Explanation:

Self join can be used to find the above desired result to list all patients who have more than one physician as
SELECT DISTINCT a.patient_id
FROM admission a, admission a2
WHERE a.patient_id = a2.patient_id
AND a.physician_id <> a2.physician_id;

Question: 52. (C)

Which clause should you use to exclude group results?

A. WHERE

B. HAVING

C. RESTRICT

D. GROUP BY

E. ORDER BY

Answer: B

Explanation:

HAVING clause is used to weed out unwanted data once the data is grouped using the GROUP BY statement.

Incorrect Answers

A: WHERE clause cannot be used for this purpose.
C: There is no RESTRICT command in Oracle.
D: GROUP BY cannot be used itself to exclude group results.
E: ORDER BY clause may be used only to sort final results, not to exclude group results.

Question: 53. (C)

In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?

A. Immediately after the SELECT clause

B. Before the WHERE clause

C. Before the FROM clause

D. After the ORDER BY clause

E. After the WHERE clause

Answer: E

Explanation:

The GROUP BY clause can be place only after the WHERE clause, or after FROM clause if there is no the WHERE clause in the statement.

Incorrect Answers

A: It is not possible to place the GROUP BY clause immediately after the SELECT clause.
B: It is not possible to place the GROUP BY clause before the WHERE clause, it can be done only after it.
C: It is not possible to place the GROUP BY clause before the FROM clause.
D: It is not possible to place the GROUP BY clause after the ORDER BY clause.

Question: 54. (C)

Which two are character manipulation functions? (Choose two.)

A. TRIM

B. REPLACE

C. TRUNC

D. TO_DATE

E. MOD

F. CASE

Answer: A & B

Explanation:

TRIM() and REPLACE() are character manipulation functions.

Incorrect Answers

C: TRUNC(x,y) is arithmetic function, it truncates x to the decimal precision of y. If y is negative, it truncates to y number of places to the left of the decimal point. This can also be used on DATE columns.
D: TO_DATE(x,[y]) function converts the non-date value x to a date using the format specified by y.
E: MOD(x,y) is arithmetic function, the modulus of x, defined in long division as the integer remainder when x divided by y until no further whole number can be produced.
F: There is no character manipulation function CASE in Oracle.

Question: 55. (C)

The EMPLOYEES table contains these columns:



You need to write a query that will produce these results:
1. Display the salary multiplied by the commission_pct.
2. Exclude employees with a zero commission_pct.
3. Display a zero for employees with a null commission value.
Evaluate the SQL statement:
SELECT LAST_NAME, SALARY*COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
What does the statement provide?

A. All of the desired results

B. Two of the desired results

C. One of the desired results

D. An error statement

Answer: C

Explanation:

This statement will provide only one of the desired results: display the salary multiplied by the commission_pct. It will not exclude employees with a zero commission_pct and display a zero for employees with a null commission value.

Incorrect Answers

A: This statement will provide only one of the desired results, not all.
B: This statement will provide only one of the desired results, not two.
D: This statement will not generate an error, it is correct .

Question: 56. (C)

Examine the structures of the EMPLOYEE and CURR_ORDER tables as shown below:


You queried the database with this SQL statement:
SELECT a.last_name, a.first_name, a.job_id, NVL(a.commission, 0), b.avgcomm
FROM employee a, (SELECT job_id, AVG(commission) AVGCOMM
FROM employee
WHERE commission IS NOT NULL
GROUP BY job_id) b
WHERE a.job_id = b.job_id
AND a.commission < employee_id =" o.employee_id" employee_id =" o.employee_id" employee_id =" o.employee_id" employee_id =" curr_order.employee_id" supplier_id =" a.supplier_id"> a.avg_cost
GROUP BY product_name, cost, p.supplier_id;

C. SELECT product_name, cost, supplier_id, AVG(list_price)
FROM product WHERE supplier_id IN (SELECT supplier_id, AVG(cost) avg_cost
FROM product GROUP BY supplier_id) GROUP BY product_name, cost, supplier_id;

D. SELECT product_name, cost, p.supplier_id, AVG(list_price)
FROM product p, (SELECT supplier_id, AVG(cost) avg_cost
FROM product
GROUP BY supplier_id) a
WHERE p.supplier_id = a.supplier_id
AND p.cost > a.avg_cost
GROUP BY product_name, cost, p.supplier_id;

Answer: D

Explanation:

For the above desired result the subquery will first find the average cost group by supplier_id and then making a join this will give the cost of all products that cost more than the average cost as SELECT product_name, cost, p.supplier_id, AVG(list_price) FROM product p, (SELECT supplier_id, AVG(cost) avg_cost FROM product GROUP BY supplier_id) a WHERE p.supplier_id = a.supplier_id AND p.cost > a.avg_cost GROUP BY product_name, cost, p.supplier_id;

Question: 59. (C)

Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?

A. SELECT TO_CHAR(SYSDATE,'yyyy')
FROM dual;

B. SELECT TO_DATE(SYSDATE,'yyyy')
FROM dual;.

C. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY')
FROM dual;

D. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year')
FROM dual;

E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy')
FROM dual;

Answer: A

Explanation:

Function TO_CHAR(x, y) converts the value x to a character or converts a date to a character string using formatting conventions.

Incorrect Answers

B: Function TO_DATE(x,[y]) converts the non-date value x to a date using the format specified by x.
C: The DECODE function is used as substitution of IF-THEN-ELSE PL/SQL construction in SQL queries. The SELECT statement provides incorrect syntax of it because it cannot have only two parameters.
D: The DECODE function is used as substitution of IF-THEN-ELSE PL/SQL construction in SQL queries. The SELECT statement provides incorrect syntax of it because it cannot have only two parameters.
E: This statement provide incorrect syntax of TO_CHAR() function: it requires only one parameter, not two.

Question: 60. (C)

The CUSTOMERS table has these columns:


The CUSTOMER_ID column is the primary key for the table.
Which two statements find the number of customers? (Choose two.)

A. SELECT TOTAL(*)
FROM customers;

B. SELECT COUNT(*)
FROM customers;

C. SELECT TOTAL(customer_id)
FROM customers;

D. SELECT COUNT(customer_id)
FROM customers;

E. SELECT COUNT(customers)
FROM customers;

F. SELECT TOTAL(customer_name)
FROM customers;

Answer: B & D

Explanation:

These statements provide correct syntax and semantics to show the number of customers. Function COUNT() can be used with substitution symbol of all columns "*" or just with one column name. Last query will be processed a little bit faster.

Incorrect Answers

A: There is no TOTAL() function in Oracle.
C: There is no TOTAL() function in Oracle.
E: You cannot table name as a parameter of COUNT() function.
F: There is no TOTAL() function in Oracle.

Posted in: Oracle