Part - 7 Oracle Interview

Question: 61. (C)

Examine the structures of the EMPLOYEES and TAX tables.



You need to find the percentage tax applicable for each employee. Which SQL statement would you use?

A. SELECT employee_id, salary, tax_percent
FROM employees e, tax t
WHERE e.salary BETWEEN t.min_salary AND t.max_salary;

B. SELECT employee_id, salary, tax_percent
FROM employees e, tax t.
WHERE e.salary > t.min_salary, tax_percent

C. SELECT employee_id, salary, tax_percent
FROM employees e, tax t
WHERE MIN(e.salary) = t.min_salary
AND MAX(e.salary) = t.max_salary

D. You cannot find the information because there is no common column between the two tables.

Answer: A

Explanation:
You can find the percentage tax applicable for each employee by using SQL statement in answer A.

Incorrect Answers

B: Syntax "WHERE e.salary > t.min_salary, tax_percent" is incorrect.
C: Functions, like MIN() and MAX(), cannot be used in the WHERE clause.
D: The SQL statement from the answer A will provide requested information.

Question: 62. (C)

Examine the data in the WORKORDER table.



The WORKORDER table contains these columns:
WO_ID NUMBER PK
CUST_ID NUMBER
REQUIRED_DT DATE
COMPL_DT DATE
AMOUNT NUMBER(7,2)
Which statement regarding the use of aggregate functions on the WORKORDER table is true?

A. Using the SUM aggregate function with the AMOUNT column is allowed in any portion of a SELECT statement.

B. Using the AVG aggregate function with any column in the table is allowed.

C. Using the SUM aggregate function on the AMOUNT column will result in erroneous results because the column contains null values.

D. Grouping on the REQUIRED_DT and COMPL_DT columns is NOT allowed.

E. Using the AVG aggregate function on the AMOUNT column ignores null values.

F. Using the MIN aggregate function on the COMPL_DT column will return a null value.

Answer: E

Explanation:

All group functions except COUNT ignore null values. Using AVG aggregate function on the AMOUNT column ignores null values.

Question: 63.

The INVENTORY table contains these columns:
ID_NUMBER NUMBER PK
CATEGORY VARCHAR2(10)
LOCATION NUMBER
DESCRIPTION VARCHAR2(30)
PRICE NUMBER(7,2)
QUANTITY NUMBER

You want to return the total of the extended amounts for each item category and location, including only those inventory items that have a price greater than $100.00. The extended amount of each item equals the quantity multiplied by the price. Which SQL statement will return the desired result?

A. SELECT category, SUM(price * quantity) TOTAL, location
FROM inventory
WHERE price > 100.00
GROUP BY category;

B. SELECT category, location, SUM(price)
FROM inventory
WHERE price > 100.00
GROUP BY category, location;

C. SELECT category, SUM(price * quantity) TOTAL, location
FROM inventory
WHERE price > 100.00;

D. SELECT category, SUM(price * quantity) TOTAL, location
FROM inventory
WHERE price > 100.00
GROUP BY category, location;

Answer: D

Explanation:
You can find total extended amount by using SUM group by function and it can be grouped by catergory and then location with GROUP BY clause.

Question: 64. (C)

The EVENT table contains these columns:
EVENT_ID NUMBER
EVENT_NAME VARCHAR2(30)
EVENT_DESC VARCHAR2(100)
EVENT_TYPE NUMBER
LOCATION_ID NUMBER
You have been asked to provide a report of the number of different event types at each location.
Which SELECT statement will produce the desired result?

A. SELECT UNIQUE(location_id), COUNT(event_type)
FROM event
GROUP BY location_id;

B. SELECT COUNT(*), DISTINCT(location_id)
FROM event;

C. SELECT DISTINCT (event_type)
FROM event
GROUP BY location_id;

D. SELECT location_id, COUNT(DISTINCT event_type)
FROM event
GROUP BY location_id;

E. SELECT location_id, MAX(DISTINCT event_type)
FROM event
GROUP BY location_id;

Answer: D

Explanation:

Unique number of event types at each location can be find by using COUNT to count the number of events and then GROUP BY clause can be used with locatio_id column to make it location wise.

Question: 65. (C)

Which two statements about the evaluation of clauses in a SELECT statement are true? (Choose two.)

A. The Oracle Server will evaluate a HAVING clause before a WHERE clause.

B. The Oracle Server will evaluate a WHERE clause before a GROUP BY clause.

C. The Oracle Server will evaluate a GROUP BY clause before a HAVING clause.

D. The Oracle Server will evaluate an ORDER BY clause before a WHERE clause.

E. The Oracle Server will evaluate an ORDER BY clause before a HAVING clause.

Answer: B & C

Explanation:

In SELECT statement WHERE clause comes after the FROM clause and before GROUP By
clause while HAVING clause use to restrict the data group wise and it comes after the GROUP BY clause and ORDER BY clause comes after at end of SELECT statement.

Question: 66. (C)

Examine the structures of the EMPLOYEE and DEPARTMENT tables:
EMPLOYEE
------------------
EMP_ID NUMBER NOT NULL PK
NAME VARCHAR(30) NOT NULL
FNAME VARCHAR(25) NOT NULL
DEPT_NO NUMBER
TITLE VARCHAR2(25)
DEPARTMENT
------------------------
DEPT_ID NUMBER NOT NULL PK
DEPT_NAME VARCHAR2(25)

You need to produce a list of departments, including the department names, that have more than three administrative assistants. Which SELECT statement will produce the desired result?

A. SELECT dept_name
FROM employee JOIN department
ON employee.dept_id = department.dept_id
WHERE UPPER(title) = 'ADMINISTRATIVE ASSISTANT'
GROUP BY dept_name
HAVING emp_id > 3;

B. SELECT dept_name
FROM employee GROUP
BY dept_no
HAVING LOWER(title) = 'administrative assistant' AND COUNT(*) > 3;

C. SELECT dept_name
FROM employee NATURAL JOIN department
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3;

D. SELECT dept_name
FROM employee e JOIN department d
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
AND COUNT(*) > 3;

E. SELECT d.dept_name
FROM employee e JOIN department d
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3;

F. SELECT d.dept_name
FROM e.employee JOIN d.department
ON (e.dept_no = d.dept_id)
WHERE LOWER(title) = 'administrative assistant'
GROUP BY dept_name
HAVING COUNT(emp_id) > 3;

Answer: E

Explanation:

To obtain the above desired result LOWER function is used to used with the TITILE column value to find it whether values stored is in the lower case or in upper case it will convert it in to lower and compare it. COUNT function will count the whole values and restricted the result to three values by using HAVING clause.

Question: 67. (C)

Which two tasks can you perform by using the TO_CHAR function? (Choose two)

A. Convert 10 to 'TEN'

B. Convert '10' to 10

C. Convert '10' to '10'

D. Convert 'TEN' to 10

E. Convert a date to a character expression

F. Convert a character expression to a date

Answer: C & E

Explanation:

TO_CHAR(x) function is used to convert the value x to a character or converts a date to a character string using formatting conventions.

Incorrect Answers

A: This function cannot convert the number to the string representing number spelled out.
B: TO_CHAR() function cannot convert the character value to a number. TO_NUMBER() function does this.

D: This function is not able to convert the string representing number spelled out to the number itself.

F: TO_CHAR() function cannot convert a character expression to a date. TO_DATE() function does this.

Question: 68. (C)

Examine the data in the LINE_ITEM table.




You query the database and return the value 23. Which script did you use?

A. SELECT SUBSTR(product_id, 3)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;

B. SELECT SUBSTR(product_id, 3, -2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;

C. SELECT SUBSTR(product_id, -3, 2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;

D. SELECT SUBSTR(product_id, 3, 2)
FROM line_item
WHERE line_item_id = 2
AND order_id = 1494;

Answer: D

Explanation:

A SUBSTR function can be used statement to extract a string of determined length.

Question: 69. (C)

Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?

A. SELECT ename, salary*12 􀂵Annual Salary􀂶
FROM employees;

B. SELECT ename, salary*12 􀂳Annual Salary􀂴
FROM employees;

C. SELECT ename, salary*12 AS Annual Salary
FROM employees;

D. SELECT ename, salary*12 AS INITCAP(􀂳ANNUAL SALARY􀂴)
FROM employees

Answer: B

Explanation:

This SQL statement provides correct syntax to generate the alias Annual Salary for the calculated column SALARY*12.

Incorrect Answers

A: Alias can be surrounded with double quotation marks, not with single. Oracle error will be generated in this case.
C: Alias needs to be surrounded with double quotation marks, it cannot just follow by the AS keyword.
D: You cannot use any function as alias, so this SQL statement will fail.

Question: 70. (C)

Examine the structure of the EMPLOYEES table:



What is the correct syntax for an inline view?

A. SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a,
(SELECT department_id, max(salary)maxsal
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;

B. SELECT a.last name, a.salary, a.department_id
FROM employees a
WHERE a.department_id IN
(SELECT department_id
FROM employees b
GROUP BY department_id having salary =
(SELECT max(salary) from employees))

C. SELECT a.last_name, a.salary, a.department_id
FROM employees a
WHERE a.salary =
(SELECT max(salary)
FROM employees b
WHERE a.department_id = b.department_id);

D. SELECT a.last_name, a.salary, a.department_id
FROM employees a
WHERE (a.department_id, a.salary) IN
(SELECT department_id, a.salary) IN
(SELECT department_id max(salary)
FROM employees b
GROUP BY department_id
ORDER BY department_id);

Answer: A

Explanation:

This SQL statement shows correct syntax to build inline views. You must enclose the query text for the inline view in parentheses and also give a label for the inline view so that columns in it can be referenced later. In answer A inline view is marked as B.

Incorrect Answers

B: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
C: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.
D: This SQL statement will fail because it is not correct syntax for inline views. There is no a label for this inline view also.