Finding the N th entry of a column in SQL

Let us take a simple table fruit and look how the query works

SQL> select * from fruit order by name asc;

NAME
----------
apple
banana
grape
orange

#If we need to find the second Maximum entry, The query would be:
#Sort the query in ascending order, then pick the second Maximum entry from the resultant set
SQL> select max(name) from fruit where rowid in ( select * from (select rowid from fruit order by name asc) where rownum <= &n );
Enter value for n: 2
old   1: select max(name) from fruit where rowid in ( select * from (select rowid from fruit order by name) where rownum <= &n )
new   1: select max(name) from fruit where rowid in ( select * from (select rowid from fruit order by name) where rownum <= 2 )

MAX(NAME)
----------
banana


#If we need to find the second Minimum entry, The query would be:

#Sort the query in the descending order, then pick the second Minimum entry from the resultant set

SQL> select min(name) from fruit where rowid in ( select * from (select rowid from fruit order by name desc ) where rownum <= &n);
Enter value for n: 2
old   1: select min(name) from fruit where rowid in ( select * from (select rowid from fruit order by name desc ) where rownum <= &n)
new   1: select min(name) from fruit where rowid in ( select * from (select rowid from fruit order by name desc ) where rownum <= 2)

MIN(NAME)
----------
grape

In order to get proper results, use the right combination of order by desc/asc and max/min feature of Query 

Some common ORA- Errors

ORA-19502 and ORA-27072

For egs :I recently faced an error like this

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/14/2013 06:23:29

ORA-19502: write error on file "/oracle/admin/flash_recovery_area/ENV/backupset/2013_11_14/o1_mf_nnndf_TAG20131114T062322_989j5tgv_.bkp", block number 26880 (block size=8192)

ORA-27072: File I/O error

Additional information: 4

Additional information: 26880

Additional information: 970752

I was wondering what might have gone wrong. It was surprising to figure out the solution for this scenario. This error will be thrown out if you have lack of disk space. So this affect RMAN Backup. So either add additional disk space to the disk or delete some unwanted files. And this worked for me, as RMAN executed the backup without any further errors

-----------------------------------------------------------------

ORA-19870 and ORA-19573

While restoring database this is one of the common ORA errors. 

For Egs:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 10/25/2013 00:27:48

ORA-19870: error while restoring backup piece /oracle/product/11.2.0.3/PROD/dbs/7con65m9_1_1

ORA-19573: cannot obtain exclusive enqueue for datafile 1

The culprit here is that, your DB is in open state. So mount it after a shutdown option. So solution is like

SQL>shutdown immediate;

SQL>startup mount;

and then go for restore database

RMAN>restore database;

ORA-04043

For egs:

SQL> desc dba_data_files;

ERROR:

ORA-04043: object dba_data_files does not exist

This has been one of the most common error produced if you haven't ran catalog.sql or catproc.sql. In some cases if your instance is down also, Oracle throws out this error.

So check the status of DB first. In case if its down, bounce it.

Still if the error persists, go to $ORACLE_HOME/rdbms/admin and run catalog.sql and catproc.sql

How to display a score(variable value) in opengl ?

It was a big task when I started using opengl to design a 3 D game to display a score board based on a variable. Most of my friends thought it would be a tough task to display a scoreboard which will be ticking each and every second of the game. Then I thought of generalizing the task and wanted to utilize my knowledge in basic C to code for the scenario.

The problem with displaying characters in opengl was that, One can only print just single character at a time on to the screen using the command

--> -->

GLvoid *font_style1 = GLUT_BITMAP_TIMES_ROMAN_24;

glRasterPos3f (0.0, 0.0, 0.0);

glutBitmapCharacter(font_style1, ch);

Here, the first line sets the Font type and its size. The second line adjusts the position of the character. Third line plots the character on to the screen, where 'ch' is the ascii value of required character. 

So here arises the biggest question:

How can one find the ascii value of the required score and then print ?

Solution is simple:

Split the digits of the score into single digits. Then take the value and add it to 48, which gives the ascii value of that digit.Print the digit, adjust the next character position, find the next digit , repeat the same procedure.

So code wise we can say as,

void scoredisplay (int posx, int posy, int posz, int space_char, int scorevar)
{
        int j=0,p,k;
        GLvoid *font_style1 = GLUT_BITMAP_TIMES_ROMAN_24;
       
        p = scorevar;
        j = 0;
        k = 0;
        while(p > 9)
        {
            k = p % 10;
            glRasterPos3f ((posx-(j*space_char)),posy, posz);   
            glutBitmapCharacter(font_style1,48+k);
            j++;
            p /= 10;
        }
            glRasterPos3f ((posx-(j*space_char)), posy, posz);   
            glutBitmapCharacter(font_style1,48+p);
      
}

here, the Scoredisplay() have folowing arguments

   initial X position(Extreme right)

   initial Y position

   initial Z position

   space in between each digits

   variable which need to be displayed

Now what ever the value on the variable, the score board will be shown at the required X,Y,Z co-ordinates. I searched net a lot to find this sort of code during my project,to get a hint of how to display my scoreboard, but was disappointed. So when I was successful in implementing this on my project and many of my friend's project, I thought of sharing with you all.

 

 

A simpler way to custom your files in ubuntu

I usually work with lot of files related to my project,  majority of them being .c or .cpp files. I always have to right click,open new document file and then type all usual headers like

#include<stdio.h> 

#include<string.h> 

#include<iostream> 

using namespace std;

. . . 

This used to make things boring. Then one of my friends shared a new way to custom your templates. Just thought of shring with you all. Its a kind of common pattern used my professionals to make their files look elegant and customized.    There has been a folder called templates in ubuntu(/home/user/Templates). There you can create your custom templates.   

Step 1   Right click there to create a new file and type what all you need for your frequent usage.    

Step 2   Now save the file  as required name and extension.     

Now see the magic. Just right click to create a new document(You can right click where ever you wish,not only the document folder as shown). You will be surprised to see your custom made file too, there in the list of Create New Document.     

So simply select it and you can have all your frequent code part typed in it already.  

So similarly, if you want many different varieties of customized files, repeat the same and save them as new names. This can really help one in easy coding as well as in customized manner. Then you needn't want to paste your name and the frequent codes into project files again and again. Hope you all will like this way of customization.

A temple of its beauty

Long back in books and epics, i have read about worshiping the tree is equivalent to worshiping god. It was only in words until I visited Oachira parabrahma temple, a few km away from Kayamkulam,Kollam District.It was a temple known for its simplicity and ofcourse very famous for the festivals and celebrations. Usually I watch this temple from outside on my way home but never could get down in my first three years of Btech career. When I awaits my bus to home, I finds people who stops their cars near the temple, and offers their money to the hundial near by. Not only cars, many lorry drivers, tourist vehicle drivers and all offer their earning in this hundial. I had always wondered why this much belief had been there for people passing by. But no one would believe that the interior of so called temple is simple and so natural.

It was recently when I went with my friends to this temple, which makes me pen down a few words about its specialty. The temple idols are placed at the bottom of trees, and it seems as if the tree is worshiped as god. All the offerings to god was natural, the garlands, milk, coconut water and all. The temple have a cool and devotional atmosphere all around its premises.  

The deities were all the usual one, but a temple with a unique feature of open worship and natural offerings brings thousands everyday to this temple. As many other temple, this temple too provides food for the needy ones and is been a shelter for many a people. The betel garland was the one which attracted me the most. It was looking grand and simple in its beauty. The temple can claim that their form of offerings are eco-friendly as well as 'greeny'. The temple offerings are not done by priests but there doesnt seems any question about its sincerity in their offerings.
History says that, One fine morning onwards Lord Shiva and Parvathy's face were resembled by the leaves of one of such big trees. Believe it or not, I would like to consider it as Tree been given the resemblance of god literally or mentally. With strong base on its roots and wide enough leaves and branches to give the shades, any one would like to enjoy the beauty of these trees and the power of deities resting on its shades.

It was a nice experience for anyone who visits this temple, if you haven't been to any such 'natural' temples.Thus my visit to this temple have been a fantastic experience and I would recommend everyone to atleast feel what I share as a "Temple of its beauty"

Saga from Amazon to Cisco

My experience with the placements 2012

o   Amazon

This was my first experience of placement season 2012.  The preliminary round consists of an online exam, which contains technical questions, aptitude questions and coding questions. Each section has its weightages. Answering all sections equally will only give you the green card to next round. Aptitude can be maintained by career launcher preparation at college level, technical is quite tough, coding is moderate but needs to be implemented in efficient way. Me was selected to next round, ie, Interview

My first Interview was really good. I was satisfied with my performance but they weren’t. Things I find from interview were

·         Technically they concentrate on Data Structures, Algorithms

·         Project is what they concentrate on and prepare it to the best

·         Try to impress your interviewers by logic of a code rather than going for the code explanation.

I was not up to the mark of data structures that interview people ask and this was my lesson from my failure #1.

o   MuSigma

With all my aptitude and code knowledge, I sat for my second company. The preliminary round consists of 2 sections Quantitative and Technical, but had negative marks. Questions were easy if management of time was taken into consideration. But when result was shortlisted, my name was not there.

Trying luck with negative marks is not an good option was my lesson from this failure #2.

o   Tally

Time to test my detailed preparation of data structures, that was my view point for this company. The preliminary round consist of a written test with 2 parts – Technical and Analytical. The technical part consisted of mainly data structures, DBMS, and basics of C programming. It was easy and good platform to test my data structure knowledge. It took nearly one month to announce the result of this round, but unfortunately I was not there in 10 shortlisted students. But the experience was a booster for my further technical preparation, because questions proved that the way I was studying was correct.

The lesson I learnt from failure #3 was to extend the technical preparation to DBMS, programming and get in touch with Interview questions. 

o   Microsoft

Any CSE student would have at least dreamt of being an Microsoft guy one day, and that dreams had lead me for an intense preparation for this company. The first round was online test consisting of technical and quantitative questions. The questions were really standard and needed good skills to crack this round. Luckily I cleared this round. The next round was a coding round – My best round. Coding was always a passion for me and I was excited about the round. There were 4 coding question and one scenario describing question. All the 4 coding questions I wrote very good codes, but couldn’t manage much contents for the scenario question. When the result came after one week , I couldn’t find my name on shortlist.

Failure #4 : The lesson was to learn the placement rounds fully before attending any company and practice sample papers in advance.

o   Thought works

The best company to work for any CSE student, where there is no special cabins, pair programming and agile development is given priority. One thing, I should specially mention is the punctuality of the team Thought works. It was really awesome that they maintained their schedules according to a plan and with proper punctuality. From my part, I have done a descent home work for the company. I looked at experiences of people from net regarding recruitment etc.

The first round of thought works is a set of questions that they are asking for previous 4 years. It is mathematics, logic and diagram related some 15 questions, which one needs to be practiced twice before their original test. 
Hope this link can help in passing the first round

http://placement.freshersworld.com/placement-papers/ThoughtWorks/Placement-Paper-Whole-Testpaper-29732

I can assure the candidates that, If you are familiar with this pattern of questions, first round will be very easy to crack. I was selected to the next round. The next round was coding. There were a plenty been selected to this round and thus they wanted to conduct this coding round in 2 phases. In phase one, they asked all to produce an output pattern they shown and asked to code for that. It was pretty simple for any CSE student who is one touch with the basics of coding. I was sure of clearing this round and I knew clearly that next round would be a challenging one. With all my acm-icpc coding experience, I step into next coding round. This was also a good coding question and by God’s grace I could impress my interviewer who was verifying the code. Even though I hadn’t finished my code, Interviewer was satisfied with my explanation and logic of solving the problem. He asked me to finish it asap and I could do it within next 5 minutes and I knew it was my day. Results came and I was shortlisted to Interview rounds.

That night I prepared about company’s recent products and refreshed my technical preparation. I also had good preparation on my project part. All the failures till now were tallied and I was trying to make interview grand. First round of Interview was very good for me and my homework really helped me. I had read in internet regarding one of the experiences of a person who wrote ‘solving logical problems’ in resume and got out of interview since he couldn’t solve a logical problem. It was interesting for me, because it was my hobby in my resume. And with no surprise, I got such a logical question from my interviewer. I could answer that question with full confidence and first time in my life, I observed the smile on my interviewer. They were totally flat by my unexpected answering and I knew that holding technical now will give me my chance. They started from my areas of Interest, went through my hobbies and were relating these two aspects of my resume. The technical questions were really easy and I could do my best with my earlier preparations. At last when I was coming out of interview room, I looked at their faces. They were smiling and I knew well before the results that I can go for next round.

The next round was a HR cum Technical oriented round. They went straight to Achievement part of resume. First line they read ‘Acm- icpc coder’. They were impressed by that achievement and gave me one nice problem to solve. It was the first time I was hearing such a problem. I kept talking to them about the way I was analyzing the question. They kept on giving me hints and suggestions when I proceed with my plans. This went on for half an hour and I was still not upto a proper answer. They told me that, they are going for a walk outside for 5 minutes and by that time if I could, that’s great or else we will move on. By god’s grace, by those 5 minutes, I came up with answer. When they came back, I explained with my full confidence the logic to solve the problem. They were shocked and surprised. I knew that if I can cling on next one hour with this level, I’m in. They went into my project. They went so deep into the subject in which I did my project. I could manage that but still it was not an impressive one for sure. Then they asked me about C++/OOP concepts and their real world implementation. I could explain the concepts well but had some real troubles explaining real world cases. They helped in correcting this part. Then they introduced a very new concept to me and asked to solve that scenario. I tried my level best and nothing was going into my mind at that time. Still I managed the situation by explaining the logic to solve the situation. At this time, we both panel and me understood that my one hour interview have gone upto two and half hours. When I left the cabin, I was sure of its fate. The HR manager told me that unhappy news but told me that panel want to meet me to give a feedback. It was a very positive feedback I received from them. They mentioned that my logical skills were apt for company but needed to improve on my OOP concepts and C++. They openly told that they were impressed the way I traversed through the rounds so far and on my ability to solve the coding problems.

Faliure #5 : Improve on OOP concepts and utilize this experience of thought works for coming company.

  

o   KLA Tencor

It was one day before our journey to ettimadai I came about to know the strange fact of KLA Tencor recruitment @ amrita. That fact was that they will recruit only 1 person from Amrita. Still to try the best, I set out for the company. The preliminary round consisted of two papers – Technical (coding included) and Aptitude. The paper was easy because of the ‘homework’. Luckily I got selected to the next round of interview. It was a half an hour long interview process as they informed. It was basically to test that answers written in previous round. Initially my interviewer asked me some basic questions from my paper and I answered them clearly. Then he went into the coding part (where I was more confident). I had expected his question to improve the code I have written and I had a ready answer available in my mind. As I waited he asked me the same and I could impress him by my new code. He again asked for 2 more ways to solve the same problem (this was not expected), still I could manage this easily. Then he told that he has done with my interview part. I was shocked because it was hardly ten minutes I been there in cabin. I have heard that smaller the time an Interview takes riskier the chance. But my answers were confident enough to reserve me a seat for the next round. This was technical round. They tested on basis of C/C++ programming skills. The beauty of the round was that all the panels had asked same set of questions to interviewees. I had managed my part well but the questions began to increase the intensity and I could imagine my status after the interview.

My interviewer after interview went into a discussion room. I sat near that room and looked inside through the glass. The people out there discussed a lot on my paper. At last I saw one member taking a red marker and moving to the board there. I first thought, they may be writing and analyzing my skills and knowledge part, but it was really different. I saw that member drawing a horizontal line, and I was sure that there was my name on the board and they put an end to my status by that line. Then naturally the HR manager walks out, calls me and convey to me my failure and feed back to improve on my OOP skills.

Failure #6 : OOP is a big subject and my preparation for this subject is still not upto mark and I need to rectify it asap to go forward. This placement process increased my confidence level since I knew I have been one among the few students who reached till this round.

o   IBM

I am an IBMer was a dream for any CSE unplaced guy. The world’s most oldest and reputed firm was our ‘first mass recruiter’. With 6 failure experiences and good CGPA, anyone would dream to get at least a mass recruitment when a company like IBM comes. I had prepared myself for a good HR round interview with highlights of my acm-icpc (where IBM was the event sponsor). The preliminary online exam was tough with number series and technical questions. I have tried my level best and sweat a lot to complete my questions. Luckily I cleared this round and moved into next round of letter writing. This email writing round, which was easy for me, but was not so good looking for IBM’ers as they didn’t shortlisted me for the GD. I had heard many of my friends saying that they wrote something and still got selected. It was a painful wait till 10:00 pm in the night to know that I haven’t selected for the GD round. This was a night of sadness because to realize that all my experience and luck haven’t favored me this time. When some 30 of our batch went into for the next round, I even though having sent a thousand emails formally and non-formally getting no spot was a sad feeling. I was thinking of getting placed in at least a mass recruiting company  rather than a core and still slipping that spot seemed to be very serious.

Failure #7 : There was no failure from my part for this company. All I have to do now is to prepare more intensely for the next company, do my part and secure an offer asap.

[The very next day was the turning point in my life. Me and my friend Revathy were called to CIR ettimadai campus office and were asked to attend Cisco placements to happen very next day at Bangalore campus. It was a joyful moment to hear for the one who had already lost a mass recruitment company and is looking for a core company. CIR specially mentioned one thing that we are allowed to attend this recruitment not only for ourselves but also for our juniors. It was told to us that, this company wanted students from ettimadai and Bangalore campus only and it was with efforts they made it eligible for Amritapuri Campus with a cut off of 9 CGPA. If we could prove to cisco companies that, our campus too have the potential to crack, next year onwards, our juniors will also be allowed to take part in this placement process.  With one day to prepare, I collected as many resources as I can about Cisco, looked for their patterns, prepared on their interview questions and was getting myself ready for the “battle”. The arrangement for reaching Bangalore was made by the help of my brother  Ranjeeth Odunghat and his classmates of MTECH Cyber Security wing of Ettimadai campus.]

o   Cisco

The company name Cisco had its privilege as they are coming to Amrita for the first time.  I had thought of applying for this company by off campus recruitment. With my one day preparation I had a fair idea of the rounds and pattern. I had called George Varghese, one of CSE senior who got placed in the company to get from him the experience and company environment. I had read his blog of experience and was preparing as per the details in that blog.

The first round consist of a written multiple choice paper consisting of 20 analytical questions and 30 technical questions and we get a total of 60 minutes to solve. The analytical questions were easy and my suggestion is to score the maximum from this section. The technical consisted of digital electronics, OS, COA, C programming, Data Structures and DBMS. This was the toughest technical questions I have ever seen. I tried my best at this round. I had no hopes of results but Luckily my name was called for next round. I was really excited watching my name on the shortlisted candidates. We were asked to submit our resume and photograph to HR manager. I was among first five students who submitted it. The next round was Interview round. My friend Revathy told that it was HR oriented from her experience.

As I stepped into Interview room, I had full confidence on my part and all they was looking was the resume, which according to me is my key for recruitment. He asked me to introduce myself and I knew well that interviewers want to catch on my area of interest. And I knew well that I wont utter a word networks, which was never my favorite subject but was theirs favorite one. Data Structures, DBMS and  programming , that was my favorites and we had some discussions on these. Then they asked me to tell about Cisco. With my homework knowledge I explained to him what cisco was to me. I added a beautiful line in between my speech that since childhood, I’ve been a user of Cisco product. I was sure he’ll be eager to ask me back what product it was and he asked. I answered, the set top box of my Television is a cisco product and from those days onwards I’ve loved to be a part of Cisco company.

Then they asked me to explain about a project and wanted to explain its implementation. Since it was my individual project I had my full confidence to explain its details. Then the interviewer asked me the trickiest question . There was actually 2 posts to which they were recruiting, IT Analyst and IT Engineer. Interviewers asked me my choice. I told that both posts suits me and to be of my choice I’ll select IT Analyst because my project experience gives me enough confidence to proceed with this post. The other interviewer once more looked at my resume and asked me that ‘See Rohith, you have done a lot of technical projects and also you sounds good at your programming part, then how good will you be in such a non-technical post?’. This was shocking to hear that IT Analyst was a non technical post. I answered that according to me, I considered IT Analyst as a technical posts were my technical knowledge can help and in this case I think my service will be more efficient if I could be an IT Engineer. They laughed. I was a bit down personally due to my contradicting statement. Then they went to my present semester subjects. They asked me specifically the role of Data Mining and its significance in Industry. I could well explain to him the relevance and I could make a good impression regarding my knowledge on these subjects. Later they asked me myself to rate out of 10 for my coding skills and I rated myself as 8/10. Then they asked me openly to judge my performance. I frankly told that switching the post from my part seems to be a bad move from my part. They smiled and told me that its ok, and convinced me that they wanted to give me the job that I suits myself and thus benefitting the company growth. They asked for my questions to them. I asked them about their working experience and what makes them stay with company so long. They answered about the work culture and experience of being a Cisco employee. The handshakes did my last part of my interview.

I personally had gone down with my performance. While my friends Revathy and Agosh (Mtech CyberSecurity, Ettimadai campus) were happy about their interviews. Fortunately when they called for the next round, they called my name too. This was like getting a re-birth or something for me. I decided in mind that whatever is the next round I’ll give my 200% to it. The round was a GD and topic was ‘corruption – can it be removed? ‘The energy I gained during this round was my best of my life. I could speak some 5 times in GD, could contribute many aspects to the discussion and I was for the first time in my life was so confident of my part. Then the HR manager asked all whether we are willing to work if given a posting at Bangalore, anyone trying for higher studies etc. All managed optimistic answers to these questions. Then he told us that, this round was the final round and they’ve done with the placement procedures and asked us to wait for the results. This was a smiling moment for me because to know that a round is final and you know that you’ve performed well on that round, and you know you’re so close to be called ‘professional’.

But when the shortlist came, shockingly I realized my name was not there. The further moments what I experienced were beyond words. It was a mixture of disappointment, sadness and overall ashamed of myself loosing at the final round. This was counted as my failure #8 and I was trying to learn many new lessons.

After one week, I was surprised to receive a mail from Cisco team informing that I’ve been given a full time offer. I was first not believing that was true. I called Ettimadai CIR Dept. and they confirmed my offer. This was a moment of celebration as first time I realized the strength of god and his blessings. Never in my dreams had I expected such a happy ending to my placement process. I utilize this opportunity to thank one and all especially CIR Dept. Amritapuri and Ettimadai campuses for the wonderful opportunity given for me.