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.
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.
When querying v$instance or v$session[1] as a non-admin user, you might come across this error:
SQL> select version
2 from v$instance;
from v$instance
*
ERROR at line 2:
ORA-00942: table or view does not exist
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: log in as sys and grant select on v$ views to the user.
Appearances are deceptive, they say. Try that and you will get an ORA-02030 error.
Just as you sometimes need to find out your database name when connected to Oracle, you may also need to find out your Oracle database version.
Here’s a rundown of ways to identify the Oracle database version using SQL and PL/SQL.
Easily transpose columns as rows in Oracle 11G+, with the UNPIVOT clause in SQL.
A typical scenario:
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.
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 master records that have identical sets of values in the detail table? Can a single SQL list master records with identical detail records?
A common scenario: in the development environment, a table’s ID column is missing its unique 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 the DBA escape this catch-22? For sure, says this post — with an approach of replacing *only* the duplicate IDs with unique values, and letting everything else stay untouched.
“ORA-00904: invalid identifier” is a familiar error to Oracle developers. It occurs, for example, when you refer to a table column that does not exist.
What’s worse than an error that shows up when you don’t expect it? An error that does NOT show up when you totally expect it.
Here’s a puzzle for you to solve. You’re given two tables – EMPL and DEPT – in which column EMP_DEPT_ID of table EMPL references DEPT_ID of table DEPT.
SQL> desc dept Name Null? Type ----------------- -------- ------------ DEPT_ID NUMBER(2) DEPT_NAME VARCHAR2(6) SQL> desc empl Name Null? Type ----------------- -------- ------------ EMP_ID NUMBER(2) EMP_NAME VARCHAR2(6) EMP_DEPT_ID NUMBER(2)
Note that the foreign key column names in the two tables are not identical. The column is called DEPT_ID in table DEPT, EMP_DEPT_ID in table EMPL.
Oracle has some neat ways of handling hierarchical data.
A single 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 reporting hierarchy of an organization (image alongside), get a single SQL to return the 4 types of employee roles as columns:
This is trickier than transposing rows as columns in a non-hierarchical table, and needs a little extra to get the result. But it is nowhere near as tough as it appears. No multiple self-joins, no recursive CTE.
This post will show you how to flatten hierarchical data using a single SQL in Oracle.
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 comma-separated list, using Oracle’s built-in function LISTAGG.