July 6, 2015

# Oracle SQL Interview Questions

7/06/2015 03:49:00 PM

1)
The EMPLOYEE tables has these columns: LAST_NAME VARCHAR2(35) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(5,2)

You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?

A. SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES;
B. SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;
C. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES;
D. SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;

Answer: D

Explanation:

This SELECT statement provides correct usage of NVL function to calculate columns for all employees. Oracle give you possibility to substitute a value in place of NULL. The basic syntax for NVL() is NVL(column_name,value_if_null) .
Notice that the column specified in NVL() contains an actual value.

That value is what Oracle returns; when the column in NULL, the special string is returned. the value specified to be returned if the column value is NULL must be the same datatype as the column specified.

Incorrect Answers
A This SELECT statement will return NULL value for rows with NULL COMMISION_PCT column.
B There is no IFNULL() function in Oracle.
C The NVL2() function requires 3 parameters, not 2. Function NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL. If expr1 is NULL, it returns expr3.

QUESTION 2

Examine the data from the ORDERS and CUSTOMERS table.

ORDERS
ORD_ID ORD_DATE    CUST_ID ORD_TOTAL

100          12-JAN-2000        15 10000

101          09-MAR-2000      40  8000

102           09-MAR-2000     35              12500

103          15-MAR-2000       15            12000

104           25-JUN-2000        15 6000

105           18-JUL-2000        20 5000

106           18-JUL-2000     35 7000

107           21-JUL-2000         20 6500

108          04-AUG-2000        10 8000

CUSTOMERS
CUST_ID CUST_NAME CITY

10 Smith Los Angeles

15 Bob San Francisco

20 Martin    Chicago

25 Mary New York

30 Rina Chicago

35 Smith New York

40 Linda New York

Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed on the same day that Martin places his orders?

A. SELECT ord_id, cust_id, ord_total FROM orders, customers
WHERE cust_name='Mating'
AND ord _ date IN ('18-JUL-2000','21-JUL-2000');
B. SELECT ord_id, cust_id, ord_total
FROM orders
Where ord_date IN (SELECT ord_date FROM orders
WHERE cust_id = (SELECT cust_id FROM customers WHERE cust _name = 'MARTIN'));
C. SELECT ord_id, cust_id, ord_total FROM orders
Where ord_date IN (SELECT ord_date FROM orders, customers
Where cust _ name = 'Martin');
D. SELECT ord_id, cust_id, ord_total FROM orders
WHERE cust_id IN (SELECT cust_id FROM customers
WHERE cust name = 'Martin');

Answer: B

Explanation:

This query will return the order ID, customer ID, and order total for the orders that are placed on the same day that Martin places his orders.
Incorrect Answers
A: This query returns only Martin's orders for July 18, 2000 and July 21, 2002, not orders of others that were placed on the same day that Martin placed his orders.
C This query uses incorrect sub-query to extract dates when Martin placed his orders.
D This query will return only Martin's orders.

QUESTION 3:

You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.

Which statement accomplishes this task?

A.    ALTER TABLE students ADD PRIMARY KEY student_id;

B.    ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student _ id);
C. ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student _ id);
D. ALTER TABLE students
ADD CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id);
E. ALTER TABLE students
MODIFY CONSTRAINT stud _ id _pk PRIMARY KEY (student _ id);

Answer: D

Explanation:

This statement provides correct syntax to add a primary key on the STUDENT_ID column of the STUDENT table.
Incorrect Answers
A    This ALTER TABLE statement is missing CONSTRAINT keyword and the name of the constraint.
B    This ALTER TABLE statement is missing the name of the constraint.
C    It's incorrect syntax in the ALTER TABLE command: STUDENT_ID must be used with brackets.
E: We need to add constraint, not to modify existing one. Usage of the MODIFY keyword is incorrect in this case.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 239-240 Chapter 5: Creating Oracle Database Objects

QUESTION 4:

Evaluate the SQL statement:

1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal 2 FROM employees a,

3 (SELECT dept_id, MAX(sal) maxsal
4. FROM employees
5 GROUP BY dept_id) b
6    WHERE a.dept_id = b.dept_id
7    AND a. asl < b. maxsal;
What is the result of the statement?

A.    The statement produces an error at line 1.

B.    The statement produces an error at line 3.
C.    The statement produces an error at line 6.
D.    The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary then the maximum salary paid in the company.
E.    The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.

Answer: E

Explanation:

The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. This query is example of an inline view which is the sub-query in the FROM clause of the main query. The sub-query can be a SELECT statement that utilizes joins, the GROUP BY clause, or the ORDER BY clause.

Incorrect Answers
A    The statement does not produce an error at line 1.
B    The statement does not produce an error at line 3.
C    The statement does not produce an error at line 6.
D    The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all EMPLOYEES, NOT DEPARTMENTS, who earn less than the maximum salary in their department.

QUESTION   5
Which three are DATETIME data types that can be used when specifying column definitions? (Choose three.)

A.    TIMESTAMP

B.    INTERVAL MONTH TO DAY
C.    INTERVAL DAY TO SECOND
D.    INTERVAL YEAR TO MONTH
E.    TIMESTAMP WITH DATABASE TIMEZONE

Answer: A, C, D

Explanation:

TIMESTAMP, INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH can be used to specify column definition.
Incorrect Answers
B: The INTERVAL MONTH TO DAY data type cannot be used when specifying column definitions there are only INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH data types.
E: The TIMESTAMP WITH DATABASE TIMEZONE data type cannot be used when specifying column definitions, because there are only TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types.

Part 2
SQL Interview Questions with Answers Part2

Oracle SQL Interview Questions with Answers Part 2

Part 3

SQL Interview questions with Answers part3