Custom Search

Saturday, November 15, 2008

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 < b.avgcomm;
Which is a result of this query?

A. The AVG function's DISTINCT keyword must be used in the inner query or the statement will fail when executed.

B. The employee information displayed will be for employees that have a commission that is less than the average commission of all employees who have the same job.

C. The employee information displayed will be displayed in numeric order by the JOB_ID and in alphabetical order by the LAST_NAME where the JOB_IDs are the same.

D. A self join CANNOT be used in an outer query when the inner query is an inline view. The self join must be placed in the inner query for the statement to execute successfully.

Answer: B

Explanation:

For the above query the information of only those employee will be displayed that have a commission that is less than the average commission of all employees who have the same job.

Question: 57. (C)

Examine the structures of the EMPLOYEE and CURR_ORDER tables.


To keep your top sales representatives motivated, your company plans to increase the bonuses of employees. You need to create a SELECT statement that returns the name, bonus, and maximum order amount associated with each employee for all employees whose bonus is less than 8 percent of their maximum order amount.
Which SELECT statement should you use?

A. SELECT e.last_name, e.first_name, e.commission, o.maxamt
FROM employee e, (SELECT employee_id, MAX(order_amt) MAXAMT
FROM curr_order
GROUP BY employee_id) o
WHERE e.employee_id = o.employee_id
AND e.commission < .08 * o.maxamt;

B. SELECT e.last_name, e.first_name, e.commission, MAX(o.order_amount) MAXAMT FROM employee e, curr_order o WHERE e.employee_id = o.employee_id AND e.commission < .08 * o.maxamt GROUP BY e.last_name, e.first_name, e.commission;

C. SELECT e.last_name, e.first_name, e.commission, MAX(o.order_amt) FROM employee e, curr_order WHERE e.employee_id = o.employee_id AND e.commission < .08 * (SELECT employee_id, MAX(order_amt) MAXAMT FROM curr_order GROUP BY employee_id);

D. SELECT last_name, first_name, commission, max_amt FROM employee, (SELECT employee_id, MAX(order_amt) MAXAMT FROM curr_order GROUP BY employee_id) WHERE employee.employee_id = curr_order.employee_id AND commission < .08 * maxamt;

Answer: A

Explanation:

Ans A contain the complete statement that will give you all the desire result needed from the query.

Question: 58. (C)

The PRODUCT table contains these columns: PRODUCT_ID NUMBER(9) PRODUCT_NAME VARCHAR2(25) COST NUMBER(5,2) LIST_PRICE NUMBER(5,2) SUPPLIER_ID NUMBER(9) You need to display product names, costs, supplier ids, and average list prices for all the products that cost more than the average cost of products provided by the same supplier. Which SELECT statement will achieve these results?

A. SELECT product_name, cost, supplier_id, AVG(list_price) FROM product p, product a WHERE p.supplier_id = a.supplier_id GROUP BY product_name, cost, supplier_id;

B. 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.cost > 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.

No comments:

Add to Google Reader or Homepage

Cheapest predictive dialer for callcenter power dial

Power Dial predictive dialer for callcenter with less investment and more features based in the heart of Hyderabad AP India.Power Dial has setup more then 200 centers and more then 5000 seats supports is provided.
For best quotes, pricing and other details mail me asap at powerdial.hyd@gmail.com

Vivaan Kumar
http://powerdial.blogspot.com/
Cheapest Predictive Dialer power Dial
cheap pd power dial
predictive dialer
predictive dialer for callcenter with less investment power dial
predictive dialer for callcenter with low price
predictive dialer form india
predictive dialer from hyderabad
power dial predictive dialer