sql

Thumbnail image for INNER JOIN and OUTER JOIN Explained

INNER JOIN and OUTER JOIN Explained

For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge. Here’s a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.

Read the Full Article →
Thumbnail image for Grant SELECT on V$ Views

Grant SELECT on V$ Views

When querying v$instance or v$session[1] as a non-admin user, you might come across this error: The error suggests that the non-admin user does not have the SELECT privilege on the sys-owned v$ view. On the face of it, the fix appears as simple as logging in as sys and granting select on the v$ view […]

Read the Full Article →
Thumbnail image for How to Find out Your Oracle Database Version

How to Find out Your Oracle Database Version

Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your database version. Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.

Read the Full Article →
Thumbnail image for UNPIVOT in Oracle 11G to Select Columns As Rows

UNPIVOT in Oracle 11G to Select Columns As Rows

Sometimes we need to transpose columns as rows in SQL. Oracle 11G onwards, this requirement can be easily implemented using the UNPIVOT clause. A typical scenario:

Read the Full Article →
Thumbnail image for SQL to Select Rows Conditionally Based On Column Value

SQL to Select Rows Conditionally Based On Column Value

Let’s say a table contains multiple rows for an id. The requirement is to select only one of those rows, based on the value in a "type" column which determines the row’s priority. A typical example is selecting one contact number for a customer, based on contact types.

Read the Full Article →
Thumbnail image for SQL to Find Master Records with Identical Detail Records

SQL to Find Master Records with Identical Detail Records

The query to identify duplicate records in Oracle in a single table is fairly straightforward. Things get tricky in a two-table (master-detail) scenario, in which the master table holds the header information (id, name, etc) and the detail table has sets of values associated with the master records. What if we need to find those […]

Read the Full Article →
Replace Duplicate Ids with Unique Values

Replace Duplicates in ID Column with Unique Values

A common scenario: in the development environment, a table’s ID column is missing its unique key constraint, and developers have been creating their own data for testing. Suddenly someone realizes that the ID column has duplicates. The DBA is summoned. "Enable the unique key", the DBA is told, "but do not delete our data." Can […]

Read the Full Article →
Thumbnail image for The Curious Case of The Missing ORA-00904

The Curious Case of The Missing ORA-00904

Here’s a puzzle for you to solve. You’re given these two tables – empl and dept – in which column emp_dept_id of table empl references dept_id of table dept. Note that the foreign key column names in the two tables is not identical. It’s called dept_id in table dept, emp_dept_id in table empl.

Read the Full Article →
Thumbnail image for Flatten Hierarchical Data using a Single SQL

Flatten Hierarchical Data using a Single SQL

Oracle has some neat ways of handling hierarchical data. A while back I wrote about how Oracle SQL can find the entire tree of data above or below a node in a hierarchy using the CONNECT BY clause. What if the requirement is to flatten hierarchical data? For example, given a table containing the employee […]

Read the Full Article →
Thumbnail image for LISTAGG for Converting Rows to Comma-Separated String

LISTAGG for Converting Rows to Comma-Separated String

In an earlier post, we saw how Oracle SQL can be used to transpose data from rows to columns. It is a similar, though simpler, exercise to transpose data from rows to a comma-seperated list.

Read the Full Article →