Home » SQL & PL/SQL » SQL & PL/SQL » Output from group function (merged)
Output from group function (merged) [message #206972] Sat, 02 December 2006 22:19 Go to next message
BigLearner
Messages: 10
Registered: December 2006
Junior Member
Hai everyone,

I am totally new to Oracle and this forum too.

I was trying some questions in my book and got the answers wrong and not convinced with the given answer. Can someone tell me how this works.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ----- --------- ---- --------- ---- ---- ----
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-82 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 FILLER CLERK 7782 23-JAN-82 1300 10

Which of the following choices identifies the value tha would be
returned from the following query: select sum(sal) + sum(comm) from emp
where job = 'ANALYST' or ename like 'J%'?

A. 6000
B. 9925
C. 9975
D. NULL

Which of the following choices identify the value returned by Oracle
whn you issue the following query : select * from emp where hiredate >
'23-JAN-82'? (Choose two.)
A. ADAMS
B. MILLER
C. SCOTT
D. SMITH

Really expecting a speedy feedback.

Thank you.

BigLearner
Group functions [message #206977 is a reply to message #206972] Sat, 02 December 2006 22:33 Go to previous messageGo to next message
BigLearner
Messages: 10
Registered: December 2006
Junior Member
Dear Budiess,

Another doubt from group functions.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------ --------- ---- --------- ---- --------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-82 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 FILLER CLERK 7782 23-JAN-82 1300 10

Which of the following choices identifies the value tha would be
returned from the following query: select sum(sal) + sum(comm) from emp
where job = 'ANALYST' or ename like 'J%'?

A. 6000
B. 9925
C. 9975
D. NULL

Which of the following choices identify the value returned by Oracle
whn you issue the following query : select * from emp where hiredate >
'23-JAN-82'? (Choose two.)
A. ADAMS
B. MILLER
C. SCOTT
D. SMITH

I got these answers wrong so thought of asking you guys if you know the
right answer.

Really expecting a speedy feedbak.

Thank you.

BigLearner
Re: doubts on outputs [message #206980 is a reply to message #206972] Sat, 02 December 2006 23:37 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

Are you attending any interview? Just kidding!

It is D & B.

D ( as analysts will be hit with 2 records, but no one with j%)

B ( as 09-DEC-82 > 23-JAN-82 ) only one.

No offense, I would really suggest you to try running the sqls in test databases which will give you more idea & knowledge.


Good Luck,

Jay
Fun Programming with Oracle & Visual C++, Author of http://www.lightsql.com.
Re: Group functions [message #207179 is a reply to message #206977] Mon, 04 December 2006 07:57 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
BigLearner wrote on Sat, 02 December 2006 23:33

Which of the following choices identify the value returned by Oracle
whn you issue the following query : select * from emp where hiredate >
'23-JAN-82'? (Choose two.)
A. ADAMS
B. MILLER
C. SCOTT
D. SMITH



Your book actually had a question that compared a date column to a character string? What a shame.
I am going to have to disagree with Jay's answer.
'23-JAN-82' is a string, therefore '28-SEP-81' is the only value greater than it, so my answer would be MARTIN, which is not even listed.

And how do you expect anyone to give you an answer when all the columns are a mishmosh instead of lines up properly when code tags are used?

[Updated on: Mon, 04 December 2006 12:11]

Report message to a moderator

Re: Group functions [message #207274 is a reply to message #207179] Mon, 04 December 2006 22:33 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

Joy,

We use implicit conversion lot in our day to day simple query, though we do not encourge it in plsql code.

I partly agree with you, but not fully. In fact, I do not know the setup (especially NLS_DATE_FORMAT). When I answered, I assumed NLS_DATE_FORMAT setup is 'DD-MON-YY' which may be true or false!

Oracle has implicit date conversion. If set properly, it may hit the results. Ref metalink Note:69029.1.

An IMPLICIT conversion occurs when Oracle converts a DATE to another datatype OR converts another datatype to a DATE without an explicit TO_DATE or TO_CHAR function call.The most common implicit conversion is between DATE and CHARACTER (VARCHAR2 or similar) datatypes.

Thanks for the pointer.

Jay
Fun Programming with Oracle & Visual C++, Author of http://www.lightsql.com.
Re: Group functions [message #207318 is a reply to message #207274] Tue, 05 December 2006 00:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Jay,

I think joy_division was ranting about the book; not your reply.

You say
Quote:
We use implicit conversion lot in our day to day simple query, though we do not encourge it in plsql code.

I don't agree with you. I think you should never use implicit conversion, not even for a quick ad-hoc query.
Teach everyone to always do an explicit conversion, then it will come just naturally. They don't have to think about it when writing 'real' code.

just my 2 cents..
Re: Group functions [message #207340 is a reply to message #207318] Tue, 05 December 2006 03:21 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

Frank,

"I think you should never use implicit conversion. Teach everyone to always do an explicit conversion, then it will come just naturally."

Perfectly fine. I appreciate it.

Jay
Fun Programming with Oracle & Visual C++, Author of http://www.lightsql.com.
Re: doubts on outputs [message #207375 is a reply to message #206980] Tue, 05 December 2006 05:04 Go to previous messageGo to next message
BigLearner
Messages: 10
Registered: December 2006
Junior Member
The answer given for the 1st question by the book is D and I understand completely why its so. When you add something with NULL the result is NULL. I just realised it as I was doing some samples.

The 2nd question's answer is A & C. Thats given by Oracle Press and I couldn't figure out why?

Any idea how this happened?

Thanks a bunch for all the replies.

BigLearner
Re: doubts on outputs [message #207386 is a reply to message #207375] Tue, 05 December 2006 05:39 Go to previous messageGo to next message
kkinapps
Messages: 29
Registered: November 2006
Location: Hyderabad
Junior Member

Hi

what Oracle press gave is correct b'coz actually the standard data in emp table will have following values for scott and adams

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

So with the above data the output given by the query

select * from emp where hiredate >'23-JAN-82'

will be definitely the above two rows


Bye
KK
Re: doubts on outputs [message #207400 is a reply to message #207386] Tue, 05 December 2006 06:53 Go to previous messageGo to next message
BigLearner
Messages: 10
Registered: December 2006
Junior Member
Hai,

But Adams hiredate is 12 jan 82.

select * from emp where hiredate >'23-JAN-82'


Thats what confuses me if there could be a valid explanation for this or its really a printing mistake

BigLearner
Re: doubts on outputs [message #207402 is a reply to message #207400] Tue, 05 December 2006 07:14 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
thats why you are supposed to use conversion functions like to_char and to_date to avoid confusing oracle
Re: Group functions [message #207414 is a reply to message #207318] Tue, 05 December 2006 07:47 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Frank wrote on Tue, 05 December 2006 01:56
Jay,

I think joy_division was ranting about the book; not your reply.




Correct Frank, and no offense to Jay of course.
Previous Topic: Attachment trough UTL_SMTP
Next Topic: sum function on two colums but same table
Goto Forum:
  


Current Time: Wed Dec 07 12:53:59 CST 2016

Total time taken to generate the page: 0.06453 seconds