Oracle Interview Question for Applications Developers


Country: India
Interview Type: Phone Interview




Comment hidden because of low score. Click to expand.
20
of 20 vote

select SUM( CASE when tblColumn = 'a' THEN 1 ELSE 0 END) AS Acount,
	   SUM( CASE when tblColumn = 'b' THEN 1 ELSE 0 END) AS Bcount,
	   SUM( CASE when tblColumn = 'c' THEN 1 ELSE 0 END) AS Ccount
from tblTest

- HR July 26, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
-1
of 1 vote

not correct

- Anonymous January 09, 2013 | Flag
Comment hidden because of low score. Click to expand.
1
of 1 vote

SQL> select SUM( CASE when type='A' THEN 1 ELSE 0 END) AS Acount,
2 SUM( CASE when type='B' THEN 1 ELSE 0 END) AS Bcount,
3 SUM( CASE when type='C' THEN 1 ELSE 0 END) AS Ccount from Test;

ACOUNT BCOUNT CCOUNT
---------- ---------- ----------
3 4 2

SO IT IS CORRECT!!!

- raushan February 11, 2013 | Flag
Comment hidden because of low score. Click to expand.
1
of 3 vote

select a acount,b bcount, c ccount from (select count(name) a from countme where name='a'),(select count(name) b from countme where name='b'),(select count(name) c from countme where name='c')

- nikhil suri August 27, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

nyc woork

- emailns11 August 27, 2013 | Flag
Comment hidden because of low score. Click to expand.
1
of 1 vote

select * from ttest;

T
-
a
a
a
b
b
b
b
c
c

SELECT *
FROM
  ( SELECT tcol FROM ttest
  ) PIVOT ( COUNT(1) FOR tcol IN ('a' AS Acount,'b' AS bcount,'c' AS ccount) 
) ;

- letsnailit January 07, 2017 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT COUNT(DECODE(NAME,'K',1)) AS K_COUNT, COUNT(DECODE(NAME,'a',1)) AS A_COUNT, COUNT(DECODE(NAME,'J',1)) AS J_COUNT FROM HP.SAMPLE WHERE DECODE(NAME,'K',1)=1 OR DECODE(NAME,'a',1)=1 OR DECODE(NAME,'J',1)=1

Is this solution applicable ?

- JBond September 10, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SELECT COUNT(DECODE(NAME,'K',1)) AS K_COUNT, COUNT(DECODE(NAME,'a',1)) AS A_COUNT, COUNT(DECODE(NAME,'J',1)) AS J_COUNT FROM HP.SAMPLE WHERE DECODE(NAME,'K',1)=1 OR DECODE(NAME,'a',1)=1 OR DECODE(NAME,'J',1)=1

- JBond September 10, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

use partition statement, first get the count for a, b and c, then partition it

- ayangyang June 02, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select [a] as [Acount] , [b] as [Bcount] , [c] as [Ccount]
from ( select tblcolumn
from tbltest
)a
pivot (count(tblcolumn)
for [tblcolumn] in([a],[b],[c])
)pvt

- Anonymous July 25, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

SQL> select SUM( CASE when type='A' THEN 1 ELSE 0 END) AS Acount,
2 SUM( CASE when type='B' THEN 1 ELSE 0 END) AS Bcount,
3 SUM( CASE when type='C' THEN 1 ELSE 0 END) AS Ccount from Test;

ACOUNT BCOUNT CCOUNT
---------- ---------- ----------
3 4 2

This is correct :)

- maheshm.mca March 29, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

I have used a pivot here, instead of a case, which works better.

SELECT a as A_COUNT,b AS B_COUNT,c as C_COUNT
FROM Dummy
PIVOT
(
COUNT(Code)
FOR Code
IN ([a],[b],[c])
)
AS PivotTable

- Mayur723 October 28, 2014 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

select max(decode(alp,'a',count(alp))) acol,
max(decode(alp,'b',count(alp))) bcol,
max(decode(alp,'c',count(alp))) ccol
from one group by alp;

- Austin April 18, 2015 | Flag Reply
Comment hidden because of low score. Click to expand.
-1
of 1 vote

SELECT * ,
CASE WHEN tblColumn = 'a' THEN COUNT( * ) END AS Acount,
CASE WHEN tblColumn = 'b' THEN COUNT( * ) END AS BCount,
CASE WHEN tblColumn = 'c' THEN COUNT( * ) END AS CCount
FROM tblTest WHERE key = value GROUP BY size

- Anonymous September 08, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
-2
of 2 vote

Not sure if this is okay, since it's using nested queries.

SQL>select (select count(*) from test1 where tblColumn='a') as ACOUNT, (select count(*) from test1 where tblColumn='b') as BCOUNT, (select count(*) from tblTest where tblColumn='c') as CCOUNT from dual;

- Anonymous August 08, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

Nope u cant use nested query.

- Black stallion August 09, 2012 | Flag
Comment hidden because of low score. Click to expand.
-2
of 2 vote

select count(tblCollumn) as acount,bcount,ccount
from tbltest
group by tblTest;

- Get_RiGhT August 24, 2012 | Flag Reply
Comment hidden because of low score. Click to expand.
Comment hidden because of low score. Click to expand.
0
of 0 votes

This is the best way

- CPF July 27, 2012 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

No Kevin, Please look into the qstn, you need to provide the result in three different columns not in a single column.

- Black stallion July 27, 2012 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

If you pivot the result of this, it will be what you need.

- Anonymous July 28, 2012 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

The above answer is absolutely right. The group by clause will do the sorting out for you and the count will give the desired results.The practical demonstration shows the same too.

- GeekOBrain July 28, 2012 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

yeah..but it it will not give column name seperately in required format.

- Abhishek August 01, 2012 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes
- gaurav28mino August 04, 2012 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 votes

No, this query will give. The format is not matching with requirement.

count(*)
----------
3
4
2

- Anonymous August 08, 2012 | Flag


Add a Comment
Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Learn More

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.

Learn More

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Learn More

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.

Learn More