Home » SQL & PL/SQL » SQL & PL/SQL » What problem can you see?
What problem can you see? [message #236919] Fri, 11 May 2007 05:55 Go to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
DELETE Deposit
WHERE balance < (SELECT AVG(balance) FROM Deposit)

What problem can you see?
How to fix it?
Thx!
Re: What problem can you see? [message #236923 is a reply to message #236919] Fri, 11 May 2007 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
None.

Regards
Michel
Re: What problem can you see? [message #236924 is a reply to message #236919] Fri, 11 May 2007 06:08 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
SQL> drop table t1;

Table dropped.

SQL> create table t1 as select rownum rn, object_name from all_objects;

Table created.

SQL> delete from t1 where rn < (select avg(rn) from t1);

18233 rows deleted.

SQL> drop table t1;

Table dropped.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
PL/SQL Release 8.1.7.3.0 - Production
CORE    8.1.7.0.0       Production
TNS for IBM/AIX RISC System/6000: Version 8.1.7.3.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL>


So.... unless I misunderstood you ... no problems
Re: What problem can you see? [message #236927 is a reply to message #236919] Fri, 11 May 2007 06:16 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Actually it is an exercise question from a book. I have no idea. It is supposed to have some problem. Thank you though.
Re: What problem can you see? [message #236941 is a reply to message #236927] Fri, 11 May 2007 06:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, it has no TECHNICAL problem.

Of course, if you think about the LOGIC behing the query, there is a problem. But that's not really an Oracle problem, but a common sense one. Wink
Re: What problem can you see? [message #236950 is a reply to message #236941] Fri, 11 May 2007 07:04 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
I am so confused. What is the logic behind the query? How to correct it so that it is logically correct? Thx!

[Updated on: Fri, 11 May 2007 07:04]

Report message to a moderator

Re: What problem can you see? [message #236956 is a reply to message #236950] Fri, 11 May 2007 07:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You have not given us enough information to really answer that.

But the first thing that came to my mind :

1. It seems it's some kind of financial data
2. The basic of chartered accountancy is that everything booked on the different elements has to aggregate to 0 in the end.

So there is no logical reason at all for that query, since the data in the "deposit" table will be unusable when about half of the data is deleted because of a criteria that is not logical.

There there basically is no way to "fix" this query.
Re: What problem can you see? [message #236957 is a reply to message #236956] Fri, 11 May 2007 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ThomasG wrote on Fri, 11 May 2007 14:19
1. It seems it's some kind of financial data

It seems to me this is just an exercise. Cool

Regards
Michel

Re: What problem can you see? [message #236959 is a reply to message #236927] Fri, 11 May 2007 07:32 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
May I ask whether this is a generic database question or a Oracle question?

If this is an generic database question a problem migth exist for very "simple" (don't know a good english word) rdbms.
In that case I can imagine that the rdbms is updating a "moving target" since the average keeps chaning.

But..... that is NOT how oracle works.
Re: What problem can you see? [message #236960 is a reply to message #236919] Fri, 11 May 2007 07:41 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
What does it mean?
delete a row if balance< average ?
delete all the rows if balance< average at a time?
delete a row continuously if balance< average?
Re: What problem can you see? [message #236961 is a reply to message #236959] Fri, 11 May 2007 07:41 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

CMSC424: Database Design Lecture 7 Rolling Eyes
Re: What problem can you see? [message #236974 is a reply to message #236961] Fri, 11 May 2007 08:32 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
I think it is partly related to Oracle.
I don't know what exactly the SQL statements mean.

delete a row if balance< average ?
delete all the rows if balance< average at a time?
delete a row continuously if balance< average?

Anyone can answer me? Thx!
Re: What problem can you see? [message #236983 is a reply to message #236974] Fri, 11 May 2007 08:45 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
No, we can't answer that here, because it would take weeks until you get the basics.

If you want a basic understanding how databases and sql work you will have to take some course. ( Either in real live or read an online one )

[Updated on: Fri, 11 May 2007 08:47]

Report message to a moderator

Re: What problem can you see? [message #236984 is a reply to message #236974] Fri, 11 May 2007 08:46 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
oracle will decide for every row whether is satisfies the condition. In this case oracle checks whether the field balance in the row is smaller then de calculated average. If it indeed is lower then the row will be deleted.

(Very simplified)
Re: What problem can you see? [message #236987 is a reply to message #236919] Fri, 11 May 2007 09:02 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Thank you both of you and sorry for bothering all of you.
I have been taking a course teaching that already. It just focuses on teaching the syntax of SQL but almost doesn't cover how the statement are processed and they give me this question as an exercise. Mad
Re: What problem can you see? [message #236988 is a reply to message #236987] Fri, 11 May 2007 09:08 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
@dumdum did you able to solve the question.
Re: What problem can you see? [message #236989 is a reply to message #236988] Fri, 11 May 2007 09:12 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Not yet. I am trying.
Re: What problem can you see? [message #236999 is a reply to message #236919] Fri, 11 May 2007 09:54 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member

Okay, your query says...

DELETE Deposit
WHERE balance < (SELECT AVG(balance) FROM Deposit)


Now, the first thing is, "What error message are you getting when you try this"?

The second question is "What table are you deleting the data from"? You don't have a FROM clause in your statement. It would probably work if you wrote...

DELETE FROM Deposit
WHERE balance < (SELECT AVG(balance) FROM Deposit)


Which, in english, says delete all records from the table "Deposit" where the value in the field "balance" is greater than the average balance in the table.

The biggest problem with your request for help is that you don't provide an error message. Without a specific error message, it's sometimes hard to determine what's wrong. Usually if you read the error message, it provides a pretty good idea on what is wrong.

HTH,
Ron
Re: What problem can you see? [message #237001 is a reply to message #236999] Fri, 11 May 2007 10:10 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
He problably didn't get any error messages, the query is syntacticaly correct, since you can ommit the "from" in the delete statement.

SQL> DELETE test
  2   WHERE nr < (SELECT Avg(nr) FROM test);

0 rows deleted.

SQL> DELETE from test
  2   WHERE nr < (SELECT Avg(nr) FROM test);

0 rows deleted.

SQL>
Re: What problem can you see? [message #237002 is a reply to message #236999] Fri, 11 May 2007 10:12 Go to previous messageGo to next message
dumdum
Messages: 25
Registered: April 2007
Junior Member
Very detailed answer. Thank you!
Actually this is all of the question. It doesn't even have any table for reference. It is testing understanding of concept of SQL.

I attempt this question to do revision for exam which is coming soon. I have no answer key for this question. Sad That's why I am asking for help here. Btw, hope the paper will not have this kind of difficult question.

[Updated on: Fri, 11 May 2007 10:14]

Report message to a moderator

Re: What problem can you see? [message #237003 is a reply to message #237002] Fri, 11 May 2007 10:20 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, the answer I would give would be :

The delete is based on a criteria that is changed by the delete itself. If the query is part of an application and is for instance executed multiple times by mistake it would delete about half the rows every time it is executed until at some point there is only one row left.
Re: What problem can you see? [message #237009 is a reply to message #237003] Fri, 11 May 2007 10:33 Go to previous message
dumdum
Messages: 25
Registered: April 2007
Junior Member
ThomasG wrote on Fri, 11 May 2007 23:20
Well, the answer I would give would be :

The delete is based on a criteria that is changed by the delete itself. If the query is part of an application and is for instance executed multiple times by mistake it would delete about half the rows every time it is executed until at some point there is only one row left.



Thank you so much! It really helps.Nod Nod

[Updated on: Fri, 11 May 2007 10:33]

Report message to a moderator

Previous Topic: SQL
Next Topic: Partition error
Goto Forum:
  


Current Time: Thu Dec 08 16:32:04 CST 2016

Total time taken to generate the page: 0.16842 seconds