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 < 100) AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id ASC, quantity; C. SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE (quantity > 9 OR quantity < 101) AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity; D. SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE quantity BETWEEN 10 AND 100 AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity DESC; Answer: D Explanation: Following query will give all the desire result by using to condition with ORDER BY clause with order_id by default will be sorted in ascending order and quantity will be sorted in escending order SELECT order_id, line_item_id, product_id, quantity FROM line_item WHERE quantity BETWEEN 10 AND 100 AND order_id BETWEEN 1500 AND 1575 ORDER BY order_id, quantity DESC; Question: 34. (B) The ITEM table contains these columns: ITEM_ID NUMBER(9) COST NUMBER(7,2) RETAIL NUMBER(7,2) You need to create a report that displays the cost, the retail price, and the profit for item number 783920. To calculate the profit, subtract the cost of the item from its retail price, and then deduct an administrative fee of 25 percent of this derived value. Which SELECT statement produces the desired results? A. SELECT cost, retail, (retail - cost) - ((retail - cost) * .25) "Profit" FROM item WHERE item_id = 783920; B. SELECT cost, retail, (retail - cost) - retail - (cost * .25) "Profit" FROM item WHERE item_id = 783920; C. SELECT cost, retail, (retail - cost - retail - cost) * .25 "Profit" FROM item WHERE item_id = 783920; D. SELECT cost, retail, retail - cost - retail - cost * .25 "Profit" FROM item WHERE item_id = 783920; Answer: A Explanation: To get a complete desired report you can use the following query with expressions SELECT cost, retail, (retail - cost) - ((retail - cost) * .25) "Profit" FROM item WHERE item_id = 783920; Question: 35. (B) The ITEM table contains these columns: ITEM_ID NUMBER(9) COST NUMBER(7,2) RETAIL NUMBER(7,2) The RETAIL and COST columns contain values greater than zero. Evaluate these two SQL statements: 1. SELECT item_id, (retail * 1.25) + 5.00 - (cost * 1.10) - (cost * .10) AS Calculated Profit FROM item; 2. SELECT item_id, retail * 1.25 + 5.00 - cost * 1.10 - cost * .10 "Calculated Profit" FROM item; What will be the result? A. Statement 1 will display the 'Calculated Profit' column heading. B. Statement 1 and statement 2 will return the same value. C. Statement 1 will return a higher value than statement 2. D. One of the statements will NOT execute. Answer: D Explanation: For the above written statements the first statement will not execute because the column alias contain spaces so it should be place in the a double quotation marks for the statement to execute successful. Question: 36. (B) The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6) You need to display the employees who have not been assigned to any department. You write the SELECT statement: SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement? A. The SQL statement displays the desired results. B. The column in the WHERE clause should be changed to display the desired results. C. The operator in the WHERE clause should be changed to display the desired results. D. The WHERE clause should be changed to use an outer join to display the desired results. Answer: C Explanation: The operator in the WHERE clause should be changed to display the desired results. There are times when you want to substitute a value in place of NULL. Oracle provides this functionality with a special function, called NVL(). You cannot use operation equal with NULL, but you can achieve desired results using NVL() function after the WHERE clause. Incorrect Answers A: The SQL statement will generate an error because you cannot use operation equal with NULL. B: The column in the WHERE clause should not be changed to display the desired results. D: Since there is only one table used in this query you don't need to use outer join to display the desired results. Question: 37. (B) Which two statements are true about WHERE and HAVING clauses? (Choose two) A. A WHERE clause can be used to restrict both rows and groups. B. A WHERE clause can be used to restrict rows only. C. A HAVING clause can be used to restrict both rows and groups. D. A HAVING clause can be used to restrict groups only. E. A WHERE clause CANNOT be used in a query of the query uses a HAVING clause. F. A HAVING clause CANNOT be used in subqueries. Answer: B, C Explanation : HAVING clause to specify which groups are to be displayed and thus further restrict the groups on the basis of aggregate information.The Oracle server performs the following steps when you use the Having clause 1. rows are grouped 2. the group function is applied to the group 3. the group that match the criteria in the Having clause are displayed. WHERE clause cannot be use to restrict groups HAVING clause use to restrict groups WHERE clause cannot be use when there is group functions. Incorrect Answers : A. Where clause cannot be use to restrict groups D. When HAVING clause is use rows are grouped as well. E. WHERE clause cannot be use when there is group function, instead HAVING is to be use. F. There is no constraint to use HAVING clause in a subqueries. Question: 38. (B) You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears? A. At the beginning of the list. B. At the end of the list. C. In the middle of the list. D. At the same location they are listed in the unordered table. Answer: A Explanation: When sorting a column with null values in ascending order then the oracle places the Null values at the end of the list if the sorting is in descending order the oracle places the null values at the start of the list. Question: 39. (B) The ACCOUNT table contains these columns: ACCOUNT_ID NUMBER(12) PREVIOUS_BALANCE NUMBER(7,2) PAYMENTS NUMBER(7,2) NEW_PURCHASES NUMBER(7,2) CREDIT_LIMIT NUMBER(7) You need to display the account number, finance charge, and current balance for accounts 1500 through 2000 with a current balance greater than the account's credit limit. The finance charge is .9 percent (.009) of the previous balance. Adding the previous balance value, new purchases value, and finance charge value, and then subtracting the payments value yields the current balance value. Evaluate this statement: SELECT account_id, NVL(previous_balance, 0) * .009 finance_charge, NVL(new_purchases, 0) + (NVL(previous_balance, 0) * 1.009) - NVL(payments, 0) current balance FROM account WHERE (new_purchases + (previous_balance * 1.009)) - payments > 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.