Simple CASE vs. Searched CASE

Simple CASE vs. Searched CASE

The CASE construct in Oracle has two variants – the simple CASE and the searched CASE. We saw examples of both kinds in the topic The Difference Between DECODE and CASE. Let’s have a closer look to compare them in structure and functionality.

Read the Full Article →
Difference Between DECODE and CASE

The Difference Between DECODE and CASE

DECODE and CASE statements in Oracle both provide a conditional construct, of this form: if A = n1 then A1 else if A = n2 then A2 else X Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function. Everything DECODE […]

Read the Full Article →
Selecting ODD or EVEN rows from a table

Selecting ODD or EVEN rows from a table

Question: How can I select only the even/odd rows from an Oracle table? Answer: Talking of “even or odd rows” is meaningless in Oracle until you have ordered the rows. Oracle does not store rows in a specific order – the order has to come from the query. Once the order is specified, then the […]

Read the Full Article →
LEVEL Pseudocolumn in Hierarchical Queries

LEVEL Pseudocolumn in Hierarchical Queries

LEVEL is a pseudocolumn (i.e. not a real column in the database but available in a query), which has a special function in hierarchical queries – it returns the position of any row in the hierarchy. Consider the hierarchy of employees in SCOTT’s EMP table, shown in tree structure like below:

Read the Full Article →
Hierarchical Queries in Oracle

Hierarchical Queries: A QuickStart Guide

A hierarchical query is one that works on data with a "tree" relationship. An Example of Hierarchical Data The employee-manager relationship in SCOTT’s famous EMP table. Each employee record has a manager’s id associated with it. In effect, there is a "tree" of data relationships At each level, I can get the employee-manager relationship as […]

Read the Full Article →
Oracle Interview Tips

7 Surefire Tips to Ace your Interview

Presenting for you the third article in the series on interviews. Earlier in the series: tips to make your resume stand out, and the fallacy of memorizing interview questions. Your resume has made it to the shortlist and you’ve got that precious interview call. You’re excited and nervous and eager to sail through. How can […]

Read the Full Article →
How to Create an Awesome Résumé

How to Create an Awesome Résumé: Eight Golden Rules

We’ve seen the pointlessness of memorizing answers to interview questions, earlier in the series on interviews. In this article, we have for you 8 gleaming tips to give your résumé a facelift and get you that valuable interview call. Your résumé is your sales pitch, your chance to make an impact in the brief time […]

Read the Full Article →
Interview Questions

The Fallacy of Interview Questions

A series of posts about Oracle interviews starts with this article. “Interview Questions” is a hot topic on many Oracle forums. Job aspirants want these questions to be listed down, emailed to them, complete with model answers. Some imagine that by learning answers to such questions, they will land a job, though they have not […]

Read the Full Article →
delete-truncate-drop

The Difference between DELETE, TRUNCATE and DROP

DELETE, TRUNCATE and DROP – all three commands get rid of table data. How exactly are they different? When should you use which? A bird’s eye view of their differences, a table comparing the three.

Read the Full Article →
Top N Query in Oracle

Nth Highest Salary in Oracle

“How can I select the Nth highest salary of the EMP table?” This is a question that every Oracle newbie stumbles over. Ask it on a forum and you’re pointed to the archives. That gets you nowhere as when you search the archives, all you find is a host of other messages also asking you […]

Read the Full Article →