Rename Column in an Oracle Table

Rename Column in an Oracle Table

Can you change the name of a table column in Oracle? Oracle 9i and above, you can very easily – there is a direct RENAME column command. Pre-Oracle 9i, you have to do a little more. In this article, we’ll look at both the current and older methods of renaming a table column in Oracle.

Read the Full Article →
BOOLEAN Datatype in Oracle

The BOOLEAN Datatype

The Oracle database does not have the BOOLEAN datatype. No direct way to represent that a column like IS_ACTIVE on table ITEMS is TRUE or FALSE. The good news is that there are simple workarounds to the situation.

Read the Full Article →
Difference between CHAR, VARCHAR and VARCHAR2

The Difference between CHAR, VARCHAR and VARCHAR2

Oracle has three datatypes to store strings. – CHAR, VARCHAR and VARCHAR2. How do they differ from each other? When should you use which? Let’s find out the answers in this article.

Read the Full Article →
Scalar Subquery

Scalar Subquery

A subquery in the SELECT clause of the main query is called a scalar subquery. This is a single row, single column query, which looks just like a column or function in the SELECT clause. The structure is:

Read the Full Article →
Thumbnail image for IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

In our last article we said that in SQL queries, IN and EXISTS are interchangeable. Many of us assume therefore, that NOT IN and NOT EXISTS are also interchangeable. A big mistake. See how NOT IN and NOT EXISTS behave differently in this small example.

Read the Full Article →
Oracle SQL: IN vs EXISTS

Which is faster – IN or EXISTS?

A question asked multiple times over on Oracle forums: Which is faster – IN or EXISTS? The short answer, post-Oracle 9i is: Both are pretty much the same! Huh? What about that thumb rule – IN for small inner query, EXISTS for big inner query?

Read the Full Article →
Inline Views

What are Inline Views in Oracle?

An inline view is a subquery with an alias that you can use within a SQL statement. An inline view behaves just as if the subquery was a table name. A classic use of inline views is in queries for Top-N analysis. See the one used for finding Nth row from a table:

Read the Full Article →
Correlated Subquery in SQL

Correlated Subquery

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:

Read the Full Article →
Subqueries and Their Types

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:

Read the Full Article →
Select Rows With Max Value

Select Rows With Max Value

We often need to select rows based on the maximum 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 […]

Read the Full Article →