The Power of Oracle SQL

Having read today's topic “SQL. Entertaining tasks, ” I recalled that I had long wanted to recommend an excellent book for the advanced level of Oracle SQL from our excellent Oracle specialist, Alex Reprintsev -“ The Power of Oracle SQL ”. Not only is it extremely useful in itself for those who want to know Oracle SQL at a high level, it is also free ! In addition, there are versions in both Russian and English.

In general, links to the book itself .

And to discuss the book itself with the author .

And to seed a couple of examples of tasks from it:

Connected components

there is an undirected (non-directional) graph defined by a list of edges and

required to get connected components.

For the data in the table below:

create table edge(x1, x2) as select 10,20 from dual union all select 50,40 from dual union all select 20,30 from dual union all select 20,40 from dual union all select 60,70 from dual union all select 80,60 from dual union all select 20,90 from dual;

The following result is expected (the numbering order of the components is not critical):

  X GRP --- ----- 10 1 20 1 30 1 40 1 50 1 60 2 70 2 80 2 90 1

Ordering dependencies

Now we consider the problem on a directed (directed) graph.

There is a table with dependencies between objects that does not contain cyclic

dependencies. However, more than one path may exist between pairs of vertices, therefore

Such a structure cannot be called a tree.
```
 create table d(name, referenced_name) as (select null, 'a' from dual union all select null, 'd' from dual union all select 'a', 'b' from dual union all select 'd', 'b' from dual union all select 'b', 'e' from dual union all select 'b', 'c' from dual union all select 'e', 'c' from dual);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     
```
It is necessary to go around all the objects in the minimum number of steps, while at each step

You can bypass only those objects for which all dependent objects are bypassed. That is, on

the first step bypasses objects that do not have dependencies; in the second step, those that depend

from the objects of the first step and so on. In other words, depth dependencies are numbered.

Covering ranges

Suppose there is a table of the following form:

 create table t_range(a, b) as (select 1, 15 from dual union all select 3, 17 from dual union all select 6, 19 from dual union all select 10, 21 from dual union all select 17, 26 from dual union all select 18, 29 from dual union all select 20, 32 from dual union all select 24, 35 from dual union all select 28, 45 from dual union all select 30, 49 from dual);

b> a for each pair a, b; a unique

It is necessary to get the segments (1:15), (17:26), (28:45), that is, we start from the line with

minimum a , and take the next line such that for it a is greater than b from the current line and so

Further.

Top paths

For a table with a list of directories in the file system, output only those that do not have

subdirectory.

 create table t_path(path) as select '/tmp/cat/' from dual union all select '/tmp/cata/' from dual union all select '/tmp/catb/' from dual union all select '/tmp/catb/catx/' from dual union all select '/usr/local/' from dual union all select '/usr/local/lib/liba/' from dual union all select '/usr/local/lib/libx/' from dual union all select '/var/cache/' from dual union all select '/var/cache/'||'xyz'||rownum||'/' from dual connect by level <= 1e6;

For the indicated data, the result will be:

 PATH ------------------------------------------------------- /tmp/cat/ /tmp/cata/ /tmp/catb/ /usr/local/ /var/cache/

All Articles

The Power of Oracle SQL

More articles: