Q1. From the following SQL Statements which one is the most reliable?
Options:
A] update table1
set field1 in (select field01
from table2),
field2 in (select field02
from table2)
where (field1, field2) in
(select field01, field02
from table2);
B] update table1
set field1 = (select field01
from table2),
field2 = (select field02
from table2)
where (field1, field2) in
(select field01, field02
from table2);
C] update table1
set field1 = (select field01
from table2
where condition1),
field2 = (select field02
from table2
where condition2)
where (field1, field2) in
(select field01, field02
from table2);
D] update table1
set field1 = (select field01
from table2
where rownum = 10),
field2 = (select field02
from table2
where rownum = 10)
where (field1, field2) in
(select field01, field02
from table2);
E] update table1
set field1 = (select field01
from table2
where rownum = 1),
field2 = (select field02
from table2
where rownum = 1)
where (field1, field2) in
(select field01, field02
from table3);Answer. E] is the most reliable statement.
Q2. From the following statements which one will you prefer to use & why?
Options:
A] declare
dt Date;
begin
select sysdate
into dt
from dual;
end;
B] declare
dt Date;
begin
dt := sysdate;
end;Answer. B] as select will consume more time than PL/SQL code.
Q3. Which relational operators in where clause results in non-usage of indexes?
Answer. <>, like ‘%…’, is NOT, functions, field + constant, field||”;
Q4. Considering following table, write result of the following SQL statement.
| Table: T1 | |||
| F1 | F2 | F3 | F4 |
| 1 | 2 | 3 | 4 |
| 2 | 3 | 4 | 5 |
| 4 | 5 | 6 | |
| 7 | 8 | 9 | |
| 2 | 3 | 4 | |
| 5 | 1 | 0 | 7 |
Select Count(F1), Count(1), Count(*), Sum(f3), Count(f3),
Count(nvl(f4,0))
from dual;Answer. Error as fields F1, F3 & F4 are not present in dual table
Q5. Considering following table, write result of the following SQL statement.
Select Count(F1), Count(1), Count(*), sum(f3), count(f3),
count(nvl(f4,0))
from (select 1 as f1, 2 as f2, 3 as f3, 4 as f4 from dual union
select 2, 3, 4, 5 from dual union
select null, 4, 5, 6 from dual union
select 7, 8, 9, null from dual union
select 2, null, 3, 4 from dual union
select 5, 1, 0, 7 from dual);Answer.
| Count(F1) | Count(1) | Count(*) | Sum(f3) | Count(f3) | Count(nvl(f4,0)) |
| 5 | 6 | 6 | 24 | 6 | 6 |
Q6. Write an Update query to replace all null values with 1 & all 1 with 10 in the above table.
Answer. Update T1
set f1 = case when f1 is null then 1
when f1 = 1 then 10
end,
f2 = case when f2 is null then 1
when f2 = 1 then 10
end,
f3 = case when f3 is null then 1
when f3 = 1 then 10
end,
f4 = case when f4 is null then 1
when f4 = 1 then 10
end;Q7. Write the output of the following query.
select trunc(9.347), ceil(9.347), floor(9.347), round(9.347,2)
from dual;Answer.
| trunc(9.347) | ceil(9.347) | floor(9.347) | round(9.347,2) |
| 9 | 10 | 9 | 9.35 |
Q8. Write a date validation function with input parameter as string and return value as correct or incorrect.
Answer.
Create or replace function F_Validate_Date(Param1 Varchar2)
return varchar2 is
dt Date;
Begin
dt := To_date(Param1,’DD-Mon-YYYY’);
Return ‘Correct’;
Exception
When others then
Return ‘Incorrect’;
End F_Validate_Date;Q9. Generate numbers 1 to 10 using select query
Answer.
SELECT ROWNUM
FROM DUAL
CONNECT BY LEVEL <= 10;
-----------------or------------------
select level
from dual
connect by level <= 10;Q10. Give output for the following.
| Table: T1 | Table: T2 | |||
| C1 | C2 | C1 | C2 | |
| 1 | a | 1 | x | |
| 1 | b | 1 | y | |
| 1 | c | 1 | z | |
Select *
From T1
inner join T2 on (T1.C1 = T2.C1);Answer. Output will be like Cartesian Product as follows
| T1.C1 | T1.C2 | T2.C1 | T2.C2 |
| 1 | a | 1 | x |
| 1 | a | 1 | y |
| 1 | a | 1 | z |
| 1 | b | 1 | x |
| 1 | b | 1 | y |
| 1 | b | 1 | z |
| 1 | c | 1 | x |
| 1 | c | 1 | y |
| 1 | c | 1 | z |
Q11. Give output of the following.
select 1+2+3+null
from dual;Answer. Null
Q12. Evaluate the following
- Create Table T with 3 columns.
- Create View V on Table T using Select * from T;
- Give output of query Select * from V;
- Alter table T to add 4th column in it.
- Give output of query Select * from V;
Answer. 3 columns when view V is queried first time and 4 columns in the next time.
Q13. Count the occurrence of substring ‘IR’ in string ‘GIRISH GIRISH’.
Answer.
select (length('GIRISH GIRISH') –
length(replace('GIRISH GIRISH', 'IR', ''))) /
length('IR') as occur
from dual;
-----------------or------------------
declare
v_str varchar2(20) := 'GIRISH GIRISH';
v_substr varchar2(20) := 'I';
l number := 0;
begin
select (length(v_str)-
length(replace(v_str, v_substr, ''))) /
length(v_substr)
into l
from dual;
dbms_output.put_line(l);
end;Q14. Evaluate the following.
Create or replace Function Func1(Value1 VarChar2,
Value2 Number) return number is
kount number;
begin
Update Table1
set Field1 = Value1
where Field2 = Value2;
commit;
select count(1)
into kount
from Table1
where Field2 = Value2;
return kount;
end Func1;
Select Func1('Partition', 101)
from dual;
Will above SQL Query work with above mentioned function?
If no, then suggest the method to execute the same. Answer. No, the above query will not execute due to DML statement in the function. To make it work either remove DML statement (update) from the function, or add Pragma Autonomous_Transaction to the function.
Q15. Modify the below SQL statement using following characters ( ) * + / – to get the output as 30;
Select 5 5 5 5 from dual;
Answer. select ((5/5) + 5) * 5
from dual; Q16. Evaluate the following.
Create or replace Function Func1(Value1 VarChar2(40),
Value2 Number(3)) return number is
kount number;
begin
select count(1)
into kount
from Table1
where Field2 = Value2;
return kount;
end Func1;
Select Func1('Partition', 101)
from dual;
Will the above query execute?
If not, then suggest the fix so that the above query can work. Answer. No, the above function will not work. To make it work remove the data length given with function parameters.
Q17. Analyze the following.
Create or replace Function Func1(Value1 VarChar2,
Value2 Number) return number is
kount number;
Pragma Autonomous_Transaction;
begin
Update Table1
set Field1 = Value1
where Field2 = Value2;
commit;
select count(1)
into kount
from Table1
where Field2 = Value2;
return kount;
end Func1;
Select Func1('Partition', 101)
from dual;
Will above SQL Query work with above mentioned function?
If no, then suggest the method to execute the same. Answer. Yes, the above query will execute.
Q18. Resolve the following.
Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue rollback and again issue a nextval. What will the output be?
Answer. 3
Q19. Evaluate the following.
Create table abc(xyz char(1));
insert into abc values ('A');
insert into abc values ('B');
commit;
Create or replace Function AddFunc(Value1 Number,
Value2 Number) return number is
begin
Return (Value1 * Value1)+(Value2 * Value2);
end Func1;
select AddFunc(2, 3) a, sysdate from dual;
select AddFunc(2, 3) as a, sysdate from abc;
Give the output for the above 2 queriesAnswer. Output of 1st query with Dual Table
| A | Sysdate |
| 13 | <System Date & Time> |
Output of 2nd query with ABC Table
| A | Sysdate |
| 13 | <System Date & Time> |
| 13 | <System Date & Time> |
Q20. Write a Function using PL/SQL to return the length of a string without using length Function.
Answer.
create or replace function StrLength(str VarChar2) return number is
ctr Number := 1;
ch VarChar2(1);
begin
loop
ch := substr(str, ctr, 1);
if ch is null then
ctr := ctr - 1;
exit;
else
ctr := ctr + 1;
end if;
end loop;
return ctr;
end;Q21. Write the output of the following query.
SELECT decode(null, null, 1, 0) from dual;Answer. Following is the output.
| decode(null, null, 1, 0) |
| 1 |
Although two NULL values are not equal, the output is 1, as decode checks for the existence of NULL and does not compare the two values.
Q22. Write the query for the following
Query to update all Nulls with 1 and all 0 with 10.
| Table: T | ||
| A | B | C |
| 1 | 2 | |
| 0 | 2 | 1 |
| 2 | 3 | |
| 4 | 5 | |
| 10 | 0 | |
| 10 | 30 | |
| 0 | 1 | |
Answer. update t
set a = decode(nvl(a, -1), -1, 1, 0, 10, a),
b = decode(nvl(b, -1), -1, 1, 0, 10, b),
c = decode(nvl(c, -1), -1, 1, 0, 10, c);Q23. Is there any error in the following query? If not, then give the output of the following query.
Create table abc(xyz char(1),
pqr number);
insert into abc values ('A', 1010);
commit;
select length(sysdate) as a,
length('sysdate') as b,
sysdate as c,
to_date('04-Aug-17') as d,
xyz, pqr
from abc;Answer. No error. Following is the Output.
| A | B | C | D | XYZ | PQR |
| 9 | 7 | 27/11/2017 04:22:05 PM | 04/08/2017 | A | 1010 |
Q24. Write SQL to display negative and positive values in separate columns.
| Table: T1 |
| C1 |
| 1 |
| -1 |
| 2 |
| -2 |
| 3 |
| -3 |
| Desired Output | |
| C1 | C2 |
| 1 | -1 |
| 2 | -2 |
| 3 | -3 |
Answer. select c1, c2
from (select c1, c1 as f1
from (select case when C1 < 0 then null else C1 end C1
from t1) a
where c1 is not null) x,
(select c2, abs(c2) as f1
from (select case when C1 > 0 then null else C1 end C2
from t1) b
where c2 is not null) y
where x.f1 = y.f1
order by c1 asc;Q25. Write SQL Statement for the following.
Mask the debit card numbers (14 or 16 digit number) by displaying 1st 4 digits and last 4 digits and rest as X.
Answer. Select substr(DrCardNo, 1, 4) ||
lpad('X', length(DrCardNo) – 4 - 4, 'X') ||
substr(DrCardNo, length(DrCardNo) - 3, 4) as
Masked_Card_No
from DrCardMaster;Q26. Give the output of the following query.
select trim(to_char(99,'99,99,999.00')) Sal1,
trim(to_char(99,'00,00,000.00')) Sal2
from dual;Answer.
| SAL1 | SAL2 |
| 99.00 | 00,00,099.00 |
Q27. Write a query using below table to get the desired output as shown below.
| Table: T1 | |
| C1 | C2 |
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| e | 5 |
| f | 6 |
| g | 7 |
| h | 8 |
| Desired Output | |
| C1 | C2 |
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
| 8 | h |
Answer. select chr(Greatest(ascii(c1), ascii(c2))) as c1,
chr(Least(ascii(c1), ascii(c2))) as c2
from T1; Q28. Give the output of the following statements.
Create table abc(xyz char(1));
insert into abc values ('A');
insert into abc values ('B');
commit;
--Q1
Select 'A' c1 from abc union
Select 'C' c2 from dual
order by 1 desc;
--Q2
Select 'A' c1 from abc union all
Select 'C' c2 from dual
order by 1 desc;
--Q3
Select xyz from abc minus
Select 'A' c2 from dual
order by 1 desc;
--Q4
Select xyz from abc minus
Select 'A' c2 from dual
order by 1 desc; Answer. Following is the output.
| –Q1 output |
| C1 |
| C |
| A |
| –Q2 output |
| C1 |
| C |
| A |
| A |
| –Q3 output |
| XYZ |
| B |
| –Q4 output |
| XYZ |
| B |
Q29. Display the number value in Words
Answer.
select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
Note: Number in column sal should be less than or
equal to 5373484 else it will result in error.
Following is the sample output.| SAL | (TO_CHAR(TO_DATE(SAL,’J’),’JSP’)) |
| 800 | eight hundred |
| 1600 | one thousand six hundred |
| 1250 | one thousand two hundred fifty |
Q30. Evaluate the following PL/SQL block & give your observations.
Declare
V_empid Employee.EmpId%Type;
begin
Insert into Employee (EmpId, EmpName, Gender, Desig, DOJ, DOB)
Values (SeqEmpNo.NextValue, 'Vinay Argekar', 'M', 'E10', Sysdate,
'01-Jan-1980')
returning EmpId into V_Empid;
Insert into EmpAdd (EmpId, Add1, Add2, City, State, Pin)
Values (SeqEmpNo.NextValue, '26/A/10 ShreeRam CHSL',
'Ahmed Nagar DSR Road', 'Thane', 'Maharashtra','100101')
returning EmpId into V_Empid;
commit;
end;Answer. The EmpId generated for EmpAdd table will not be same as EmpId generated for Employee table. To resolve this issue use SeqEmpNo.CurValue or V_Empid instead of SeqEmpNo.NextValue in insert statement for EmpAdd table.
Q31. Evaluate the following SQL statements.
CREATE SEQUENCE SEQID
MINVALUE 1
MAXVALUE 9999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
Create Table T1(col1 varchar2(100),
col2 number(10));
Insert Into T1('Oracle', SEQID.Nextval);
Create Table T100(col100 varchar2(10),
col200 number(10));
Rollback;
Insert Into T1('Oracle PL/SQL', SEQID.Nextval);
Rollback;
Insert Into T1('Oracle SQL Plus', SEQID.Nextval);
Commit;
Select * from T1;
Give the output for the select statment. List the tables which will be
created in the database using above statement. Also provide the value
for SEQID.CurVal.Answer. T1 & T100 are the tables that will be created.
The value of SEQID.CurVal will be 3.
Output of last select statement is as follows.
| Col1 | Col2 |
| Oracle | 1 |
| Oracle SQL Plus | 3 |
Q32. Hierarchical Queries
Answer. SELECT employee_id, lpad('-', level-1, '-') || last_name,
manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id
start with employee_id = 100;
Note: Execute the query on demo HR schema provided by Oracle &
check the result.Q33. Write SQL query to change the orientation of the following text from vertical to horizontal.
O
R
A ==> ORACLE
C
L
EAnswer. select listagg(n, '') within group (order by i) a
from (select 'O' n, 1 i from dual union all
select 'r', 2 from dual union all
select 'a', 3 from dual union all
select 'c', 4 from dual union all
select 'l', 5 from dual union all
select 'e', 6 from dual);Q34. You need to describe the table used in the below query & you don’t have the access to the schema where the table is existing. Describe the way to achieve this.
Select *
from EmployeeDtls@HRMS_Live;Answer.
Create Table TempTable as
Select *
from EmployeeDtls@HRMS_Live
where 1 = 2;
Desc TempTable;Q35. How to search for strings containing ‘%’ in Oracle? Search for columns containing ‘%’ in Oracle.
Answer. In ORACLE, ESCAPE keyword is to used to search for strings
containing ‘%’. Otherwise it would be considered as a META CHARACTER.
Using the escape character to search for strings containing like
‘ABC %%TRF’, ‘TR%FF’ or ‘%GH’)
SELECT col_name
FROM tbl_name
WHERE col_name LIKE '%?%%' ESCAPE '?';
Here ‘?’ can be replaced with any other character.
Another solution:
SELECT col_name
FROM tbl_name
WHERE instr(col_name,'%') > 0; Q36. Give the output of the following statements.
Create table abc(xyz char(1));
insert into abc values ('B');
commit;
Select 'A' c1 from abc Union Select 1 c1 from dual;Answer. Result is error string data type mismatch. To resolve the error, add single quote before and after 1.
Q37. How do I eliminate the duplicate rows?
Answer.
delete from table_name
where rowid not in
(select max(rowid)
from table
group by duplicate_values_field_name);
-----------------or------------------
delete from table_name
where rowid not in
(select min(rowid)
from table
group by duplicate_values_field_name);Q38. Write SQL query to display running totals for the data given below
| Table: Emp | |
| Name | Salary |
| PRITI | 20000 |
| RAHUL | 5000 |
| NIKITA | 1000 |
| SONA | 5000 |
Answer.
select b.name, b.salary,
(select sum(a.salary)
from emp a
where a.rowid <= b.rowid)
from emp b;
-----------------or------------------
select name, salary,
sum(salary) over(order by rowid)
from emp
order by rowid;
-----------------or------------------
select a.name, a.salary, sum(b.salary)
from emp a, emp b
where b.rowid <= a.rowid
group by a.name, a.salary, a.rowid
order by a.rowid;
Output:| Name | Salary | Total |
| PRITI | 20000 | 20000 |
| RAHUL | 5000 | 25000 |
| NIKITA | 1000 | 26000 |
| SONA | 5000 | 31000 |
Q39. To fetch ALTERNATE records from a table. (EVEN NUMBERED)
Answer. select *
from emp
where rowid in
(select decode(mod(rownum, 2), 0, rowid, null)
from emp);Q40. To fetch ALTERNATE records from a table. (Odd NUMBERED)
Answer. select *
from emp
where rowid in
(select decode(mod(rownum, 2), 0, null, rowid)
from emp);Q41. How do you find the database object (Table) used in which all procedures? What is query for that?
Answer. Using User_Source / All_Source
Select * from User_Source where upper(text) like ‘%EMPLOYEES%’;
Q42. What will happen after commit statement?
Declare
Cursor C1 is
Select empno, ename
from emp;
Begin
open C1;
loop
Fetch C1 into eno.ename;
Exit When C1 %notfound;
commit;
end loop;
end;Answer.
• The cursor having query as SELECT…. FOR UPDATE gets closed after COMMIT/ROLLBACK.
• The cursor having query as SELECT…. does not get closed even after COMMIT/ROLLBACK.
Q43. Select Top N records
Answer.
Select Employee_Id, Full_Name, Salary, Rank
from (SELECT Employee_Id,
First_Name || ' ' || Last_Name AS Full_Name, Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM employees)
where Rank <= &n;
--------------or--------------
Select Employee_Id, First_Name || ' ' || Last_Name AS Full_Name,
Salary
From employees
WHERE n >= (SELECT count(distinct Salary)
from Employees b
WHERE a.Salary >= b.Salary)
order by a.Salary desc;Q44. Select bottom N records
Answer.
Select Employee_Id, Full_Name, Salary, Rank
from (SELECT Employee_Id,
First_Name || ' ' || Last_Name AS Full_Name, Salary,
DENSE_RANK() OVER (ORDER BY Salary ASC) AS Rank
FROM employees)
where Rank <= &n;
--------------or--------------
Select Employee_Id, First_Name || ' ' || Last_Name AS Full_Name, Salary
From employees
WHERE n >= (SELECT count(distinct Salary)
from Employees b
WHERE a.Salary <= b.Salary)
order by a.Salary desc;
Note: By removing less than & greater than sign in the above queries
from outer query’s where clause we can get Nth top or bottom record
respectivelyQ45. How you will avoid your query from using indexes?
Answer.
SELECT ename, emp_no
FROM emp
Where emp_no+' ' = 12345;
You must concatenate the column name with space within codes
in the where condition or use hint as show below.
SELECT /*+ FULL(a) */ ename, emp_no
from emp
where emp_no = 1234;Q46. Give output for the following
Declare
fvar number := null;
svar number := 5
Begin
goto << fproc>>
if fvar is null then
<< fproc>>
svar := svar + 5
end if;
End;What will be the value of svar after the execution?
A] Error B] 10 C] 5 D] None of the above
Answer. A] Error
Q47. Convert the string “AaBb” to Ascii values?
Answer.
SELECT SUBSTR(DUMP('AaBb'), 15) aa
FROM DUAL;
----------or----------------
SELECT WM_CONCAT(A) aa
FROM (SELECT ASCII(SUBSTR('AaBb', LEVEL, 1)) A
FROM DUAL
CONNECT BY LEVEL <= LENGTH('AaBb'));| AA |
| 65,97,66,98 |
Dump is a function that returns the ASCII code of the characters of the string passed to it.
Q48. Show the start and end date for each month in the period that contains the fiscal year from 01-Jul-2010 to 30-Jun-2011. Following should be the output of the query.
| START_DATE | END_DATE |
| 01-Jul-2010 | 31-Jul-2010 |
| 01-Aug-2010 | 31-Aug-2010 |
| 01-Sep-2010 | 30-Sep-2010 |
| 01-Oct-2010 | 31-Oct-2010 |
| 01-Nov-2010 | 30-Nov-2010 |
| 01-Dec-2010 | 31-Dec-2010 |
| 01-Jan-2011 | 31-Jan-2011 |
| 01-Feb-2011 | 28-Feb-2011 |
| 01-Mar-2011 | 31-Mar-2011 |
| 01-Apr-2011 | 30-Apr-2011 |
| 01-May-2011 | 31-May-2011 |
| 01-Jun-2011 | 30-Jun-2011 |
Answer.
select trunc(add_months('01-JUL-10', level-1), 'MM') start_date,
last_day(add_months('01-JUL-10', level-1)) end_date
from dual
connect by level <= ceil(months_between('30-JUN-11', '01-JUL-10'));Q49. Write a select statement to find the sum of all the numbers from 1 to n.
Answer. select (n * (n + 1)) / 2 as SumTotal from dual;
Q50. Which one is better count(*), count(1), count(pk)?
Answer. All are same.
Q51. Is following SQL statement correct?
UPDATE dest_tab tt
SET (tt.code, tt.description) = (SELECT st.code, st.description
FROM source_tab st
WHERE st.id = tt.id)
WHERE EXISTS (SELECT 1
FROM source_tab
WHERE id = tt.id);Answer. Yes, it is perfectly correct.
Q52. Separate the numbers, alphabets & special characters from ‘1234bsdfs3@23##P258U@!$’?
Answer.
select regexp_replace('1234bsdfs3@23##P258U@!$',
'[^0-9]', '') as Numbers,
regexp_replace('1234bsdfs3@23##P258U@!$',
'[^a-z and ^A-Z]', '') as Alphabets,
regexp_replace('1234bsdfs3@23##P258U@!$',
'[^0-9 and ^a-z and ^A-Z]', '') as AlphNum,
regexp_replace('1234bsdfs3@23##P258U@!$',
'[0-9 and a-z and A-Z]', '') as SpecialChars
from dual; | Numbers | Alphabets | AlphNum | SpecialChars |
| 1234323258 | bsdfsPU | 1234bsdfs323P258U | @##@!$ |
In case of any discrepancies or doubts do contact me.
Best of Luck Friends.
TechOraFAQ 
Very helpful information for interview.
LikeLike
Thanks. Keep up the good work.
LikeLike
Good series of questions for interview. Helped me to revisit the entire course
LikeLiked by 1 person
Thanks. You can try out the queries on
https://livesql.oracle.com/apex/f?p=590:1:5934566212440::NO:RP
LikeLike
Good work. Very helpful. Appreciate the efforts of the author.
LikeLike
The content is amazing. Very Informative!!!
LikeLike