Written Test Questions

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);

Options:
A] declare
     dt Date;
   begin
     select sysdate
       into dt
       from dual;
   end;
B] declare
     dt Date;
   begin
     dt := sysdate;
   end;

Select Count(F1), Count(1), Count(*), Sum(f3), Count(f3),     
       Count(nvl(f4,0))
  from dual;

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. 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;

select trunc(9.347), ceil(9.347), floor(9.347), round(9.347,2)
  from dual;

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;

Answer.
 SELECT ROWNUM
   FROM DUAL
CONNECT BY LEVEL <= 10;
-----------------or------------------
 select level
   from dual
connect by level <= 10;

Select *
  From T1
 inner join T2 on (T1.C1 = T2.C1);

select 1 + 2 + 3 + null
  from dual;

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) := 'IR';
  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;

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. select ((5/5) + 5) * 5 
          from dual; 

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. 

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. 

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 queries

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;

SELECT decode(null, null, 1, 0) from dual;

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);

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. 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;

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;

select trim(to_char(99,'99,99,999.00')) Sal1, 
       trim(to_char(99,'00,00,000.00')) Sal2
  from dual;

Answer. select chr(Greatest(ascii(c1), ascii(c2))) as c1,
               chr(   Least(ascii(c1), ascii(c2))) as c2
          from T1; 

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.
 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.

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;

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. 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.

Answer. 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);

Select * 
  from EmployeeDtls@HRMS_Live;

Answer.
Create Table TempTable as 
  Select * 
    from EmployeeDtls@HRMS_Live
   where 1 = 2;
Desc TempTable;

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; 

Create table abc(xyz char(1));
insert into abc values ('B');
commit;
Select 'A' c1 from abc Union 
Select 1   c1 from dual;

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);

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:

Answer. select * 
          from emp
         where rowid in 
               (select decode(mod(rownum, 2), 0, rowid, null)
                  from emp);

Answer. select * 
          from emp
         where rowid in 
               (select decode(mod(rownum, 2), 0, null, rowid)
                  from emp);

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. 
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;

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
respectively

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;

Declare
  fvar number := null; 
  svar number := 5
Begin
  goto <<fproc>>
  if fvar is null then 
    <<fproc>>
    svar := svar + 5
  end if;
End;

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'));

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'));

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. 
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;