Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Rich.

Asked: April 21, 2002 - 2:22 pm UTC

Last updated: December 07, 2006 - 5:51 pm UTC

Version: 8.05

Viewed 10K+ times! This question is

You Asked

Tom,

I have an interview coming up and I read from one of your prvious responses that you ask three questions to those you interview . I was wondering if you had given the answers and I had missed them somewhere. Here are your questions and how I interpreted them.

I ask this question in interviews. You have a table:

create table t ( ....., month number, ..... );

______________________________________________________________
My table is:

CREATE TABLE T (TEST_DATE DATE, TEST_MONTH NUMBER(2), TEST_DAY VARCHAR2(20))

Table created.
_____________________________________________________________

Month is always a number between 1 and 12.

I ask three questions:

1) how many rows are in the table

_____________________________________________________________
Answer) Well, none yet :)
Now I insert with

1 INSERT INTO T
2 (SELECT TO_DATE('01/01/2002','MM/DD/YYYY') + (ROWNUM - 1),
3 TO_NUMBER(TO_CHAR(TO_DATE('01/01/2002','MM/DD/YYYY') + (ROWNUM - 1), 'MM')),
4 TO_CHAR(TO_DATE('12/31/2002','MM/DD/YYYY') + (ROWNUM - 1), 'Day')
5 FROM ALL_OBJECTS
6 WHERE TO_DATE('01/01/2002','MM/DD/YYYY') + (ROWNUM - 1) <=
7* TO_DATE('12/31/2002','MM/DD/YYYY'))
SQL> /

365 rows created.

__________________________________________________________________

2) how many rows BY MONTH are in the table (i want to know how many rows for
month one, month two and so on)

__________________________________________________________________

Answer)

1 select test_month,
2 count(test_day)
3 from t
4* group by test_month
SQL> /

TEST_MONTH COUNT(TEST_DAY)
---------- ---------------
1 31
2 28
3 31
4 30
5 31
6 30
7 31
8 31
9 30
10 31
11 30
12 31

_____________________________________________________________________
3) what MONTH has the most rows (and for a special bonus, tell me why this
question is ambigous)

Answer)

1 select i.test_month,
2 count(i.test_day)
3 from t i
4 group by i.test_month
5 having count(i.test_day) = (select max(count(o.test_day))
6* from t o group by o.test_month)
SQL> /

TEST_MONTH COUNT(I.TEST_DAY)
---------- -----------------
1 31
3 31
5 31
7 31
8 31
10 31
12 31

7 rows selected.

More than one month has 31 days in it?

________________________________________________________________


You would be surprised how many people struggle with #1 -- let alone #2. I've
had people YELL at me that #3 cannot possibly be done in a single query, no
doubt about it. They get really dejected when I put up at least three different
answers before asking if they would like more. I don't expect people to get #3
-- it's a tricky one, mostly want to see how they handle the pressure. It's #2
and #1 that gets me -- most people who rated themselves 8 or 9's (i have them
rate their sql ability on a scale of 1..10 -- higher is better) *cannot do it*.
I have them re-rate themselves after the three questions. It's interesting.

(my favorite answer to question #3 involves using a HAVING clause with ">= ALL"
in it -- not any, not some, but ALL...)

_____________________________________________________________________
How close was I to what you were asking and can you show the ">= ALL" solution.
Also, any other questions you throw out to the people you interview? As always, it's appreciated

and Tom said...

you never answered #1. I said "i have a table", I have data in my table.

#2 is technically inaccurate. There is nothing to say that test_day (that column doesn't even exist in my example) is NOT NULL -- if there is a null in there, the count(test_day) is wrong.


#3 is correct -- except for your improper use of test_day (should be count(*), not count(test_day(.


The answer I like for #3 is

select month
from t
group by month
having count(*) >= all( select count(*)
from t
group by month );


And yes -- that there may be more then one frequently occuring month is the bonus part of the answer.

I ask people to "draw a picture of Oracle". Show me the processes/threads -- the main ones. Explain what they are. Tell me about the SGA, what are the main areas in there, what are their sizes. Now, tell me about the files -- draw the entire picture on the whiteboard and explain to me about this "Oracle database" thing, how is it architected....



Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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?

Tom Kyte
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




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




Tom Kyte
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




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



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




Tom Kyte
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

Tom Kyte
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?



Tom Kyte
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?

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


Tom Kyte
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