A correlated subquery is a type of nested subquery that uses columns from the outer query in its WHERE clause.
For example, a query to list employees whose salary is more than their department’s average:
{ 3 comments }
SQL
In Oracle SQL, we often need to select rows with max value of a specific column, such as date. Let’s say we have a table that stores customers’ orders, and we want to list the last order dates and amounts for each customer.
Here is a demo data set and query to achieve this. The same query can be extended to select rows based on the maximum value of any other column.
{ 3 comments }
The SQL WITH clause is similar in concept to a function definition in procedural code. In a function, we factor the common code, put it all together at one place and call it as many times as needed in the main program. That’s precisely how we use the WITH clause in SQL – factor out the common subquery, put it all together at one place and call it as many times as needed in the main query.
BUT there is a difference.
{ 2 comments }
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 simple CASE and searched CASE in structure and functionality.
{ 0 comments }
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 can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.
{ 71 comments }
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 query to retrieve odd rows or even rows can be written in this form:
{ 8 comments }
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:
{ 0 comments }
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 below:
{ 0 comments }
