Part - 4 Oracle Interview

Question: 31. (B)

You query the database with this SQL statement:
SELECT bonus
FROM salary
WHERE bonus BETWEEN 1 AND 250
OR (bonus IN(190, 500, 600)
AND bonus BETWEEN 250 AND 500);
Which value could the statement return?

A. 100

B. 260

C. 400

D. 600

Answer: A

Explanation:

For the above statement the value of bonus return will be 100. After resolving the above written operators the result return would be 100.

Question: 32. (B)

Examine the structure of the STUDENTS table:



You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?

A. SELECT student_ id, marks, ROWNUM "Rank"
FROM students
WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC; B. SELECT student_id, marks, ROWID "Rank" FROM students WHERE ROWID <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks; C. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC); D. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students ORDER BY marks DESC) WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL'; Answer: D Explanation: This statement using inline sub-query will provide correct results to show 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. Incorrect Answers A: This view will just show first 10 students from the STUDENTS table with limitation on the course INT SQL and who completed the course in the year 1999. B: It's wrong to use here ROWID to achieve desired results. C: This statement will provide wrong result due to wrong conditions in the inline sub-query. Question: 33. (B) Examine the structure of the LINE_ITEM table.

You must display the order number, line item number, product identification number, and quantity of each item where the quantity ranges from 10 through 100. The order numbers must be in the range of 1500 through 1575. The results must be sorted by order number from lowest to highest and then further sorted by quantity from highest to lowest.
Which statement should you use to display the desired result?

A. SELECT order_id, line_item_id, product_id, quantity
FROM line_item
WHERE quantity BETWEEN 9 AND 101
AND order_id BETWEEN 1500 AND 1575
ORDER BY order_id DESC, quantity DESC;

B. SELECT order_id, line_item_id, product_id, quantity
FROM line_item
WHERE (quantity > 10 AND quantity <> 9 OR quantity < item_id =" 783920;" item_id =" 783920;" item_id =" 783920;" item_id =" 783920;" item_id =" 783920;" department_id =" NULL;"> credit_limit
AND account_id BETWEEN 1500 AND 2000;
Which statement about this SELECT statement is true?

A. The statement calculates the finance charge incorrectly.

B. The statement calculates the current balance incorrectly.

C. The statement returns only accounts that have NO previous balance.

D. The statement returns only accounts that have new purchases, previous balance, and
payments values.

Answer: D

Incorrect answers

A. The statement calculates the finance charge incorrectly.
B. The statement calculates the current balance incorrectly.
C. The statement returns only accounts that have NO previous balance.

Question: 40.(C)

Examine the description of the EMPLOYEES table:



Which statement shows the maximum salary paid in each job category of each department?

A. SELECT dept_id, job_cat, MAX(salary)
FROM employees
WHERE salary > MAX(salary);

B. SELECT dept_id, job_cat, MAX(salary)
FROM employees
GROUP BY dept_id, job_cat;

C. SELECT dept_id, job_cat, MAX(salary)
FROM employees;

D. SELECT dept_id, job_cat, MAX(salary)
FROM employees
GROUP BY dept_id;

E. SELECT dept_id, job_cat, MAX(salary)
FROM employees
GROUP BY dept_id, job_cat, salary;

Answer: B

Explanation:

This answer provides correct syntax and semantics to show the maximum salary paid in each job category of each department.

Incorrect Answers

A: This query will not return any row because condition SALARY > MAX(SALARY) is FALSE.
C: This query will return error because you cannot show maximum salary with DEPT_ID and JOB_CAT without grouping by these columns.
D: The GROUP BY clause is missing JOB_ID column.
E: You don't need to group results of query by SALARY in the GROUP BY column.

Posted in: Oracle