ALLInterview.com :: Home Page            
 Advertise your Business Here     
Browse  |   Placement Papers  |   Company  |   Code Snippets  |   Certifications  |   Visa Questions
Post Question  |   Post Answer  |   My Panel  |   Search  |   Articles  |   Topics  |   ERRORS new
   Refer this Site  Refer This Site to Your Friends  Site Map  Bookmark this Site  Set it as your HomePage  Contact Us     Login  |  Sign Up                      
Google
   
 
Categories >> Software >> Databases >> SQL-PLSQL
 
 


 

Back to Questions Page
 
Question
What are the Diff B/W Cursor and REF Cursor
Rank Answer Posted By  
 Question Submitted By :: Guest
This Interview Question Asked @   CTS
I also faced this Question!!   ALL Interview .com
Answer
Cursor is Static
REF Cursor is Dynamic
 
0
Guest
 
 
Question
Name    Salary
Abc      50000
Abc      50000
xyz      20000
find the max salary using aggregate function?
Rank Answer Posted By  
 Question Submitted By :: Guest
This Interview Question Asked @   CTS
I also faced this Question!!   ALL Interview .com
Answer
select max(sal) from tab_name;
or
select * from tab_name where sal=(select max(sal) from 
tab_name) and rownum<=1;
 
0
Guest
 
 
Question
How will you make performance better for application including 
front end and back
Rank Answer Posted By  
 Question Submitted By :: Guest
This Interview Question Asked @   Metric-Stream
I also faced this Question!!   ALL Interview .com
Answer
Describe Java and Database performance technique for UI and 
database and mid-tier
 
0
Guest
 
 
 
Question
	

I have a tablle like this.
cust    acc
---------------
a       1
b       2|3
c       4|5|6

I Want below o/p:
cust   acc
-----------
a      1
b      2
b      3
c      4
c      5
c      6
Please any one can you have any ideas share me.
I have urgent requirement.
Rank Answer Posted By  
 Question Submitted By :: Basha
This Interview Question Asked @   MTS , Capgemni
I also faced this Question!!   ALL Interview .com
Answer
select * from tab
group by acc
 
0
Monu Singh Shekhawat
 
 
Answer
SELECT a,
REGEXP_SUBSTR (b, '([^|]+)', 1, lvl) b


FROM dummy,
(SELECT LEVEL lvl
          FROM (SELECT MAX (LENGTH (REGEXP_REPLACE
(b, '[^|]'))) mx
                  FROM dummy)
        CONNECT BY LEVEL <= mx + 1)
WHERE lvl - 1 <= LENGTH (REGEXP_REPLACE (b, '[^|]'));
 
0
Punith
 
 
Answer
create table test001 (cuss varchar2(10), acc varchar2(30));

insert into test001 values ('a','1');

insert into test001 values ('b','2|3');

insert into test001 values ('c','4|5|6');

WITH CTE AS (SELECT  CUSS,ACC FROM TEST001)
select distinct trim(regexp_substr( acc, '[^|]+', 1, 
level)) acc ,cuss from cte 
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(ACC, '[^|]+')) + 
1;
 
0
Kavitha Nedigunta
 
 
Question
I have a tablle like this:

cust   acc
-----------
a      1
b      2
b      3
c      4
c      5
c      6


I Want below o/p:
cust    acc
---------------
a       1
b       2|3
c       4|5|6

Please any one can you have any ideas share me.
I have urgent requirement.
Rank Answer Posted By  
 Question Submitted By :: Basha
This Interview Question Asked @   MTS
I also faced this Question!!   ALL Interview .com
Answer
select cust, wm_conact(acc) as acc from table
 group by cust
 
0
Trainedforjob
 
 
Answer
select cust, REPLACE(wm_conact(acc), ',', '|') as acc from table
group by cust
 
0
Bubun4U
 
 
Answer
select customer_id,

MAX(CASE WHEN RNK MOD 8 = 1 THEN ACCOUNT_NO ELSE '' END) ||
MAX(CASE WHEN RNK MOD 8 = 2 THEN ',' || ACCOUNT_NO ELSE '' END) ||
MAX(CASE WHEN RNK MOD 8 = 3 THEN ',' || ACCOUNT_NO ELSE '' END)
AS ACCOUNT_NO
FROM 
(
select customer_id,account_no,  rank() over (partition by customer_id order by account_no) as rnk
from customer_account ) TEMP
GROUP BY 1


The above query is tested and it works.
 
0
Prathibha
 
 
Answer
select cust,wmsys.wm_concat(acc) from tbl group by cust;
 
0
Kpk
 
 
Answer
select cust,listagg(acc,'|') within group(order by acc) from
tbl group by cust;
 
0
Phanikumar
 
 
Question
column A      column b     |      output
  10             7         |       10
   5             8         |        8
   7            -9         |        7
   3             5         |        5
   0             6         |        6

Write a sql query to print such output.
Rank Answer Posted By  
 Question Submitted By :: Seema.panda07
I also faced this Question!!   ALL Interview .com
Answer
select greatest(nvl(cola,0),nvl(colb,0)) from tablename
 
0
Satish Tadepalli
 
 
Answer
select a,b,
CASE WHEN (a>b) THEN a
     ELSE b
END output
 from a
 
5
Apoorva Garg
 
 
Answer
select greatest(cola,colb) from tblname;
 
0
Phani Kumar
 
 
Answer
select A,B,greatest(A,B) output from <tablename>
 
0
Suresh Ramsng
 
 
Question
I m giving Source, Destination and Age. Write a procedure or 
function, it will give to u this source to destination 
tickets are available or not, if available then check this 
person is senior citizen or not,if this person is senior 
citizen then give some discount.
PLZ give this answer......
Thanks advance.....
Rank Answer Posted By  
 Question Submitted By :: Ajit
I also faced this Question!!   ALL Interview .com
Answer
TRY THIS ONE ...

CREATE TABLE FARE_DETAILS
( SOURCE VARCHAR2(100),
  DESTINATION VARCHAR2(100),
  FARES NUMBER(10,2));


create or replace procedure PROC_TCKT_RESERVATION
(m_source IN VARCHAR2,
 m_destination IN VARCHAR2,
 m_age IN NUMBER
 ) 
is
v_source FARE_DETAILS.SOURCE%TYPE;
v_destination FARE_DETAILS.DESTINATION%TYPE;
v_fares FARE_DETAILS.FARES%TYPE;
v_fares_out FARE_DETAILS.FARES%TYPE;
begin
IF m_source= m_destination THEN
   RAISE_APPLICATION_ERROR(-20001,'SOURCE, DESTINATION
SHOULD NOT BE SAME');
END IF;
SELECT FARES
INTO  v_fares
FROM FARE_DETAILS
WHERE SOURCE= m_source
AND DESTINATION= m_destination ;
   IF m_age>=60 THEN
      v_fares_out:= (v_fares-v_fares*0.3);
      DBMS_OUTPUT.PUT_LINE('GIVEN AGE IS FOR SENIOR CITIZEN,
FARE IS '||v_fares_out);
   ELSE
    v_fares_out:= v_fares;
    DBMS_OUTPUT.PUT_LINE(' FARE IS '||v_fares_out);
   END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
     RAISE_APPLICATION_ERROR(-20001,'NO DATA FOUND ..ENTER
COREECT SOURCE/DESTINATION');

end PROC_TCKT_RESERVATION;
 
0
Aritra
 
 
Question
I am creating an index on Emp table Empno column,if u using 
this indexed column in ur SELECT stmt. where clause,then how 
do u know that yr index will be working or nor?
Thanks Advance...
Rank Answer Posted By  
 Question Submitted By :: Ajit
I also faced this Question!!   ALL Interview .com
Answer
Do the explain plan.if it is not using ur created  index 
then use hints to use ur created index
 
0
Satish
 
 
Answer
You execute your query like
explain plan for select sal from emp where empno='1224';
after that check this below one.
select * from table(dbms_xplan.display);
here u can find it'll be excuted by using index or TABLE ACCESS FULL
 
0
Suresh Ramsing
 
 
Question
Fetch an entire row from the employees table for a specific
employee ID:
Rank Answer Posted By  
 Question Submitted By :: Suresh Ku Patra
I also faced this Question!!   ALL Interview .com
Answer

DECLARE
   l_employee   employees%ROWTYPE;
BEGIN
   SELECT *
     INTO l_employee
     FROM employees
    WHERE employee_id = 138;

   DBMS_OUTPUT.put_line (
      l_employee.last_name);
END; 


 
0
Suresh Ku Patra
 
 
Answer
select * from employees where employee_id='1381';
 
0
Suresh Ramsing
 
 
Question
How to find last day of the month in sql query
Rank Answer Posted By  
 Question Submitted By :: Shah.sonal2988
I also faced this Question!!   ALL Interview .com
Answer
below query will get the last day of the month 
select last_day(sysdate) from dual
 
0
Vishwa
 
 
Answer
select TO_CHAR( last_day(sysdate), 'day')
from dual
 
5
Ajit
 
 
Question
if a string is there like s1,s2,s3,s4.How to find count of 
commas in this.
Rank Answer Posted By  
 Question Submitted By :: Shah.sonal2988
I also faced this Question!!   ALL Interview .com
Answer
14:41:23 SQL> SELECT regexp_count ('s1,s2,s3,s4', ',')
14:41:24   2    FROM DUAL;

REGEXP_COUNT('S1,S2,S3,S4',',')
-------------------------------
                              3

Elapsed: 00:00:00.00
14:41:26 SQL>
 
0
Prashant
 
 
Answer
select 's1,s2,s3,s4' str,
length('s1,s2,s3,s4') - length(replace('s1,s2,s3,s4', ',')) str2
from dual;
 
0
Ajit
 
 
Answer
select
length('s1,s2,s3,s4')-length(replace('s1,s2,s3,s4',',')) as
Count_comma from dual
 
0
Debbie
 
 
 
Back to Questions Page
 
 
 
 
 


   
Copyright Policy  |  Terms of Service  |  Articles  |  Site Map  |  RSS Site Map  |  Contact Us
   
Copyright 2013  ALLInterview.com.  All Rights Reserved.

ALLInterview.com   ::  KalAajKal.com