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
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
 
 
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
 
 
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
 
 
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
 
 
Question
What is the diffrence between IN and EXISTS.which one is 
faster.
Rank Answer Posted By  
 Question Submitted By :: Shah.sonal2988
This Interview Question Asked @   Infogain
I also faced this Question!!   ALL Interview .com
Answer
> we use IN gives better performance when there is less number of record in a table (up to 999 records), But for large number records EXIST give better performance.
>In may or may not depends a sub query all time, But EXIST always depend on a sub query.
 
0
Rashmi
 
 
Answer
In the in clause, the inner query is executed first, hence if the inner query return less data than the outer query, then use IN clause, in the other scenario, user exists clause.

Also, in the exists clause the inner query always reference outer query's table, which may or may not be the case with in IN clause.
 
0
Neha
 
 
 
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