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:
SQL
SQL (Structured Query Language) is a standard language used to operate relational databases. It includes operations such as database creation, retrieving and modifying data, granting privileges to data.
Understanding Subqueries and Their Types
A subquery is – to put it simply – a query within a query.
What purpose does a subquery serve?
A subquery may be needed when it takes more than a single step to reach the answer.
Suppose we need to find all employees who work in the same department as KING. We need to:
Select Rows With Max Value
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.
Why the SQL WITH clause is not exactly like a function definition
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.
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 simple CASE and searched CASE in structure and functionality.
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 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.
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 query to retrieve odd rows or even rows can be written in this form:
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:
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 below: