Answers to Interview Questions
Rich, April 21, 2002 - 4:52 pm UTC
Tom,
Thanks for the quick response but I'm still a little confused about the first question. How can I tell how many rows are in your table?
April 21, 2002 - 5:10 pm UTC
you would issue select count(*) from t;
Answers to Interview Questions
Rich, April 21, 2002 - 10:36 pm UTC
Thanks, I had it in my head this was a trick question and was overlooking the obvious.
Famy, April 22, 2002 - 4:54 pm UTC
From you above example for ALL , I ran the following queries.
why isnt the 2a not returning a row just like 1a
case 1:
Wrote file afiedt.buf
1 select TO_CHAR(HIREDATE,'MM')
2 from EMP
3 group by TO_CHAR(HIREDATE,'MM')
4 having count(*) >= all(select count(*)
5 from EMP
6* group by TO_CHAR(HIREDATE,'MM') )
/
TO
--
12
Case 1a:
select TO_CHAR(HIREDATE,'MM')
2 from EMP
3 group by TO_CHAR(HIREDATE,'MM')
4 having count(*) = (select max(count(*))
5 from EMP
6 group by TO_CHAR(HIREDATE,'MM') )
7 /
TO
--
12
case 2:
Wrote file afiedt.buf
1 select TO_CHAR(HIREDATE,'MM')
2 from EMP
3 group by TO_CHAR(HIREDATE,'MM')
4 having count(*) >= all( select count(*)
5 from EMP
6* group by TO_CHAR(HIREDATE,'MM') )
TO
--
12
case 2a:
Wrote file afiedt.buf
1 select TO_CHAR(HIREDATE,'MM')
2 from EMP
3 group by TO_CHAR(HIREDATE,'MM')
4 having count(*) = all( select count(*)
5 from EMP
6* group by TO_CHAR(HIREDATE,'MM') )
no rows selected
April 22, 2002 - 9:05 pm UTC
the count(*) is not equal to ALL of the count(*)'s -- makes sense doesn't it.
Tom, the original poster of the quesion is right in his answer to your question number 2
Famy, April 22, 2002 - 5:15 pm UTC
look at this
pmekala2@CPNRSKD1> select deptno, count(comm)
2 from emp
3 group by deptno ;
DEPTNO COUNT(COMM)
--------- -----------
10 0
20 0
30 4
pmekala2@CPNRSKD1> select deptno, count(*)
2 from emp
3 group by deptno
4 /
We get accurante count of comm for a dept when we do count(comm) ( and comm is a nullable column), and not when we do a count(*).
So the posters solution to question 2
select test_month,
2 count(test_day)
3 from t
4* group by test_month
is right , even if test_day is a nullable column
Your comments please.....
April 22, 2002 - 9:10 pm UTC
He is wrong, you are wrong. Look at your own example:
1* select deptno, count(comm), count(*) from emp group by deptno
scott@ORA817DEV.US.ORACLE.COM> /
DEPTNO COUNT(COMM) COUNT(*)
---------- ----------- ----------
10 0 3
20 0 5
30 4 6
scott@ORA817DEV.US.ORACLE.COM>
the count(comm) is WAY off.
You only get the right count with COUNT(*) or COUNT( non-nullable-column ).
Consider:
scott@ORA817DEV.US.ORACLE.COM> create table t ( month int, some_other_column int );
Table created.
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> insert into t values ( 12, NULL );
1 row created.
scott@ORA817DEV.US.ORACLE.COM> insert into t values ( 12, NULL );
1 row created.
scott@ORA817DEV.US.ORACLE.COM> insert into t values ( 12, NULL );
1 row created.
scott@ORA817DEV.US.ORACLE.COM> insert into t values ( 12, NULL );
1 row created.
scott@ORA817DEV.US.ORACLE.COM> insert into t values ( 12, NULL );
1 row created.
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> insert into t values ( 11, 1 );
1 row created.
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> select month, count(some_other_column)
2 from t
3 group by month
4 /
MONTH COUNT(SOME_OTHER_COLUMN)
---------- ------------------------
11 1
12 0
the only correct answer to the question:
how many rows BY MONTH are in the table (i want to know how many rows for
month one, month two and so on)
is represented by the following output:
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> select month, count(*)
2 from t
3 group by month
4 /
MONTH COUNT(*)
---------- ----------
11 1
12 5
which differs significantly from the nullable column one...
Thanks for the question regarding "Answers to Interview Questions ", version 8.05
Subhash S B, April 23, 2002 - 2:54 am UTC
Hi Tom,
Dont you think
select count(ROWID) from t1 group by Col1;
is a much better way to group on a particular col. without ignoring the nulls.
Thanks,
Subhash
April 23, 2002 - 7:27 am UTC
No -- this is exactly what count(*) is designed to do.
the ONLY time I use count(column_name) is when i expect NULLS in column_name and want a non-null count.
Otherwise count(*) is the only thing that you need to use.
Why do you believe count(rowid) would be better or superior to count(*).
Answers to Interview Questions
Rich, April 26, 2002 - 1:10 pm UTC
Again, thanks for this great website and pointing out the errors I made with regard to my use of NULLs. Can you explain the benefit of having a field populated with null. My application never purposely inserts null values and I only find them useful for searching for missing data. For example:
select rj.gap "Sequence",
decode(l.location, NULL, 'Gap',l.location) "Location Code"
from location_tbl l,
(select lpad(to_char(rownum),3,0) gap from all_objects where rownum < 1000) rj
where rj.gap = l.location (+)
and (l.effdt = (select max(l1.effdt) from location_tbl l1
where l.setid = l1.setid
and l.location = l1.location
and l1.effdt <= sysdate) or l.effdt is null)
order by rj.gap
We ran out of location codes that were set up and I ran a report to find out what other values could be used.
April 26, 2002 - 8:24 pm UTC
Thats exactly what NULLS are for "missing data". It is when the data is unknown, that is the very essence of NULL.
Excellent
Ik, March 01, 2003 - 12:45 pm UTC
Tom,
Just curious...Why is this your prefferred solution?
select month
from t
group by month
having count(*) >= all( select count(*)
from t
group by month );
Why not something like
select month,cnt FROM
(SELECT month, cnt, RANK(cnt) OVER () rn
FROM
(select month, count(*) cnt
from t
group by month))
WHERE
rn = 1
This hits the table only once. Please correct me if iam wrong.
March 01, 2003 - 1:03 pm UTC
because all databases can do that one. It should be answerable by anyone that knows SQL.
abt Package Version
P.S.Vinodh, January 06, 2004 - 5:28 am UTC
Hi Tom,
From couple of my frineds iam got same question which i could not able to get answers.
Is there any way to find Package specification and package Body version" OR is there any thing like that which maintain the version of these packages in Oracle or in Unix.
In couple of interview they asked these question seems.. i thought to know the answer but in asktom i could not able to get it..
can you tell us something about that.
Thanks in advance
January 06, 2004 - 8:45 am UTC
if you want "versions" and "source code control", you'll be using a source code control tool (just like you would for java, c, vb, etc)
there is no "source code control" builtin the database itself. there is no concept of a "version" on the package body/spec
interview
abc, December 22, 2004 - 11:09 am UTC
After creating controlfile with "create controlfile" command do we have to use alter database open resetlogs to open the database?
why?
optimizer_mode=choose and I have query which based on 3 tables and stats is generated for 1 table only then what optimizer mode will be used by oracle for other 2 tables?
December 22, 2004 - 11:16 am UTC
yes, no, maybe. (on the resetlogs)
depends on what you did. but these questions really don't relate to the original thread.
if one table has stats
and optimizer mode is choose
the CBO will be used, entirely.
oracle testing
sam, December 06, 2006 - 11:05 pm UTC
Tom:
If you are interviewing someone for a testing (acceptance testing and integration testing) position for web and client server oracle systems what questions would you be asking him to determine whether to hire him or not?
December 07, 2006 - 9:46 am UTC
same sorts of questions I would ask almost any candidate.
I can teach anyone a product, a technology - if they are teachable.
and you give insufficient detail to answer really - are you expecting this person to come in and RUN the test, participate in the test, learn how to test over time and so on.
Your specific requirements will drive you to a set of relevant questions at the appropriate level for the position you desire to fill (and if you do not know enough about the position to be filled - make sure to find someone that does and invite them to the interview so they can judge if the person is telling the truth)
interview
sam, December 07, 2006 - 4:34 pm UTC
Tom:
<same sorts of questions I would ask almost any candidate>
Can you list the basic questions you usually ask? Would you be checking for basic intelligence (ability to think and learn) instead of specific technology?
This is a software testing position for oracle web application and some Power builder application. There is not really time to learn. He should be able to look at business requirements and create test plan and use cases and test the code developers write.
December 07, 2006 - 5:51 pm UTC
have a conversation with them, see if they impress you. The questions come and go - whatever I'm feeling like. I don't believe I've ever asked the same set twice.
interview
sam, December 08, 2006 - 1:05 am UTC
Tom:
Have you listed any basic questions here on your website?
What do you look for:
chemistry
personality
technical ability
Looks
all of the above
to RIch
A reader, October 17, 2007 - 11:48 am UTC
you may come up with some crazy question that you know the answers, If the person doesn't know them it does not mean that he/she is dumb.!!
May be you have worked in that area more (date asthmatic) and
may be the interviewee has worked more on analytics !! So if he pauses some crazy windowing query in front of you and you have to answer it with in 5 mins may be you can not do it.
and I don't think , from the candidate prospective, that before you go to interview, you read all oracle and pl/sql book, may be you brush up your skills but you can't do everything...
So when you interview, i guess you should look for the adaptiveness, does he understands the question ?
how does he/she try to solve problem ? it is a different issue if he/she solves it or not given the time based on your priority.
how confidant he/she is?
business knowledge ?
And personally i don't care about the certification, from my point of view it not worth it (may be Tom would not like this comment, but that's true !! , you have to take a crash course from oracle !! ha ha ha and sometimes instructor does not know enough details... if you ask question!! )
A reader, May 06, 2011 - 7:05 am UTC
ra gqvia?
tom anwer
A reader, December 16, 2011 - 11:16 pm UTC
what am i wathing