Oracle Interview Questions Part 1: SQL

Q: What is the difference between delete and truncate?
Ans:
1. Delete can be rolled back before commiting the transaction whereas a truncate cannot be rolled back
2. Delete is a DML whereas Truncate is DDL
3. Truncate deletes the entire data at once whereas delete does it row by row.
4. Truncate is faster and performance benefit when compated to delete, if the requirement is to flush the table completely.

Q: What is the syntax of DECODE function and what is the use?
Ans: It has the same functionality IF-THEN-ELSE statement.
syntax: decode(expression,search[,result,search,result..][,default])

Example:
select decode (department, 1, 'Finance',2,'Accounts', 'Miscellaneous') from dual;

Q: What does COALESE function does?
Ans:
COALESCE function goes through the given list of values/expressions and returns the first non null expression. If all the values/expressions are null then it returns null. COALESCE is similar to IF-THEN-ELSE statement.

syntax: coalesce( expr1, expr2, ... expr_n )
SELECT coalesce( firstname, middlename, lastname) result
FROM employees;

Its equivalent IF-THEN-ELSE is as below.

IF firstname is not null
THEN
result:=firstname;
ELSIF middlename is not null
THEN
result:=midddlename;
ELSIF lastname is not null
THEN
result:=lastname
ELSE
result:=null;
end if;

Q: A given column has values with NULL data. On sorting the column in ascending order, where does the null value comes? First or Last?

Ans: Last

Q: What is the default sort order in Oracle?

Ans: ascending


Q: Provided a list of numbers or characters or dates. What function helps you to retrieve the highes value from the list?
Ans: greatest(x,y,..)
and for the least it is least(x,y,..)

Q: The column firstname is of datatype varchar2 which has null values as well. What is the output of below function for a null value?
NVL(firstname,0)
Ans: NVL returns the passed value if the expression is null else returns the erpression.
Here the function fails as the column is of datatype varchar2 and the specified value is of type number. Hence it fails.

For NVL to work, the data type of both column and passing value should be same.

Q: How NVL2 works?
Ans: syntax: NVL2(X,Y,Z)
NVL2 returns Y if X is not null, else returns Z

Q: What is the syntax for NULLIF?
Ans: NULLIF(x,y)
function returns NULL if x=y else returns X

Q: Write an SQL query to fetch all the managers from an employee table whose JOB_ID ends with either '_MAN' or '_MGR'

Ans:
SELECT FIRST_NAME, LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE REGEXP_LIKE(JOB_ID, '(_m[an|gr])', 'i');

[parameter i indicates case-insensitive]

Q: Select every employee whose last name has a double vowel
(two adjacent occurrences of the same vowel).
Ans:
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '([AEIOU])\1', 'i');

Q: Exmaple for using REGEXP_REPLACE

Ans:
SELECT PHONE_NUMBER "Old Format",
REGEXP_REPLACE(PHONE_NUMBER,
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') "New Format"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;



Old Format                                              New Format
-----------------------------------------------------------------
515.123.4567 (515) 123-4567
515.123.4568 (515) 123-4568
515.123.4569 (515) 123-4569

The search pattern has three regular expressions, each of which is enclosed in parentheses. The metacharacter [[:digit:]] represents a digit, the metacharacter {n} specifies n occurrences, and the metacharacter \ is an escape character. The character immediately after an escape character is interpreted as a literal. Without the escape character, the metacharacter . represents any character. The replace string uses \1, \2, and \3 to represent the first, second, and third regular expressions in the search pattern, respectively. (In the replace string, \ is not an escape character.)


Q: Example to extract Street number including hyphen from the given street address.
Ans:
select street_address address, REGEXP_SUBSTR(street_address,'[[:digit:]-]+') "Number" from locations

Address                                   Number
-----------------------------------------------
2007              Zagora St 2007
2004              Charade Rd 2004
147                Spadina Ave 147
6092              Boxwood St 6092
40-5-12         Laogianggen 40-5-12



Q: Can we use a oracle keyword as alias in any select statement?
select 1 number from dual;

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:   
*Action:
Error at Line: 1 Column: 9

solution: Use the keyword in double quotes

select 1 "number" from dual;

Output:
number
---------
1

Q: Example to count number of spaces in a street address
Ans:

Q: What is the result of below query? Why?
select 10000 + null from dual;

Ans: null. In oracle any scalar operation with null always results in null.