Home » SQL & PL/SQL » SQL & PL/SQL » Interview questions
Interview questions [message #637787] Mon, 25 May 2015 01:22 Go to next message
purnima1
Messages: 79
Registered: June 2014
Member
Hi All,
Please help in providing the query for deleting duplicate rows,from the below mentioned, if it is possible.
in the ouput we can see distance between delhi to mumbai and numbai to delhi is same ,so there is no point of storing two rows for same thing.
Please confirm if possible.
Thanks in advance



create table tab (city1 varchar2(100),city2 varchar2(100),distance number );


begin 
insert into tab values ('DELHI','MUMBAI',2000); 

insert into tab values ('MUMBAI','DELHII',2000);

insert into tab values ('MUMBAI','PUNE',2000);

insert into tab values ('PUNE','MUMBAI',2000);

commit;
end ;

select * from tab ;
city1   city2   distance
DELHI	MUMBAI	2000
MUMBAI	DELHII	2000
MUMBAI	PUNE	2000
PUNE	MUMBAI	2000

[Updated on: Mon, 25 May 2015 01:24]

Report message to a moderator

Re: Interview questions [message #637788 is a reply to message #637787] Mon, 25 May 2015 01:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Are all attending or have attended the same interview?
Re: Interview questions [message #637790 is a reply to message #637788] Mon, 25 May 2015 01:28 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Might be.. I gt to know this from my friend..well thanks for link
Re: Interview questions [message #637791 is a reply to message #637790] Mon, 25 May 2015 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And try to find the answer to the added questions and not take the provided solutions without a thought.
Note the last questions are exactly about your "there is no point of storing two rows for same thing.".

Re: Interview questions [message #637797 is a reply to message #637791] Mon, 25 May 2015 05:33 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
I know Michel there is no point of storing two rows for same thing,but if somebody has asked this question and we are unable to solve this then I think this is right platform where we can discuss our problems
Re: Interview questions [message #637803 is a reply to message #637797] Mon, 25 May 2015 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is EXACTLY my point but instead of giving some solutions I encourage people to find them.
Did you try to find the solution in the other topic? Or are you just waiting we put it?

Re: Interview questions [message #637848 is a reply to message #637787] Wed, 27 May 2015 10:07 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Besides DELHI - MUMBAI 2000 is NOT the same as MUMBAI - DELHII 2000.
Razz
Re: Interview questions [message #637863 is a reply to message #637848] Thu, 28 May 2015 01:16 Go to previous messageGo to next message
anniepeteroracle
Messages: 47
Registered: March 2012
Location: bangalore
Member
delete from tab d1
where  exists(Select 1 from tab d2
              where d1.CITY1 = d2.CITY2
              and   d1.CITY2 = d2.CITY1
              and   d2.rowid      > d1.rowid );

[Updated on: Thu, 28 May 2015 01:18]

Report message to a moderator

Re: Interview questions [message #637867 is a reply to message #637863] Thu, 28 May 2015 01:25 Go to previous message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I encourage you to read the other topic pointed by Lalit and the link in it: How does one eliminate duplicates rows from a table?.

Previous Topic: Procedure is taking more time to execute
Next Topic: Increasing Initrans for Partitioned table without re-creating it
Goto Forum:
  


Current Time: Tue Mar 19 02:57:38 CDT 2024