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.
DUAL is a special one-row, one-column table in Oracle data dictionary.
DUAL comes in handy when you want to select just one row through a query. Oracle SQL structure requires you to have a FROM <table> clause, but some queries don’t need a table — if you want to know the current system date, for example, or the answer for (3+1)*5. DUAL is useful for queries you’d write for such cases:
The “ROWNUM greater than” query never fails to have an eye-popping effect the first time anyone sees it. If you haven’t worked with ROWNUM in Oracle much before, be prepared!
ROWNUM is a pseudocolumn, assigning a number to every row returned by a query. The numbers follow the sequence 1, 2, 3…N, where N is the total number of rows in the selected set. This is useful when you want to do some filtering based on the number of rows, such as:
A usual situation we come across in development: a table missed a unique key and allowed duplicate rows to be entered unchecked. Now we want to find and delete those duplicates.
In this article, we’ll see how to achieve this.
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.
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.
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:
In Oracle 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.
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!
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 were 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:
