Oracle Tips and Tricks — David Fitzjarrell

October 27, 2011

That’s Your Problem

Filed under: General — dfitzjarrell @ 17:13
Tags: ,

Many times a problem can appear to be more complicated than it actually is. This is due, I think, to being ‘locked into’ a thought process not conducive to solving the problem. Knowing how to read the problem and discover the information provided can help tremendously in working toward a solution. Let’s look at a few problems and their solutions to see how to change the way you think about, and look at, a problem.

Jonathan Lewis provides the first problem we consider, although it actually appeared in the comp.databases.oracle.server newsgroup several years ago. It’s gone through several iterations since its original offering and we’ll consider the most recent of those here. The problem:

Two mathematicians met at their college reunion. Wanting to keep current they started discussing their lives as only mathematicians can:

Mathematician #1: So, do you have any children?
Mathematician #2: Yes, three girls.
Mathematician #1: Wonderful! What are their ages?
Mathematician #2: I’ll give you a clue: the product of their ages is 36.
Mathematician #1: Hmmm, good clue but not nearly enough information.
Mathematician #2: Well, the sum of their ages is the number of people in this room.
Mathematician #1: (After looking about the room) That’s still not enough information.
Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.
Mathematician #1: I have it now — say, are the twins identical?

Given that all of the information needed to solve the problem is in the problem, what are the ages of the three girls?

The problem seems unsolvable at first glance but there is more information available than is originally seen. Let’s state what we know from the problem:

1 — There are three girls
2 — Their ages, multiplied together, have a product of 36
3 — The sum of their ages is (to us, anyway) an undisclosed number
4 — The oldest daughter has a hamster with a wooden leg

A strange collection of facts, some might say. But, looking deeper into the problem we can find some logic and answers not obvious from casual inspection. Let’s start with the product of the ages:

  
SQL> --
SQL> -- Generate an age list for the girls
SQL> --
SQL> -- Maximum age is 36
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum  
SQL> --
SQL> -- Return only the age groupings whose product
SQL> -- is 36
SQL> --
SQL> -- Return, also, the sum of the ages
SQL> --
SQL> -- This restricts the set of values needed to
SQL> -- solve the problem
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum = age1.age
 15        and age3.age >= age2.age
 16        and age1.age*age2.age*age3.age = 36
 17  )
 18  select *
 19  from product_check
 20  order by 1,2,3;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         1          1         36         38         36                          
         1          2         18         21         36                          
         1          3         12         16         36                          
         1          4          9         14         36                          
         1          6          6         13         36                          
         2          2          9         13         36                          
         2          3          6         11         36                          
         3          3          4         10         36                          

8 rows selected.

SQL> 

Notice we return not only the product of the ages but also the sums of the various combinations, as we’ll need this information later on in the problem. Now another ‘fact’ emerges:

5 — Knowing the sum of the ages doesn’t help matters much

This reveals that there is more than one combination of ages which produce the same sum:

  
QL> --
SQL> -- Find, amongst the acceptable values,
SQL> -- those sets where the summed value is
SQL> -- the same
SQL> --
SQL> -- This is necessary as providing the sum
SQL> -- was of little direct help in solving the
SQL> -- problem
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum = age1.age
 15        and age3.age >= age2.age
 16        and age1.age*age2.age*age3.age = 36
 17  ),
 18  summed_check as (
 19        select youngest, middle, oldest, sum, product
 20        from (
 21         select youngest, middle, oldest, sum, product,
 22         count(*) over (partition by sum) ct
 23         from product_check
 24        )
 25        where ct > 1
 26  )
 27  select *
 28  from summed_check;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         2          2          9         13         36                          
         1          6          6         13         36                          

SQL> 

Now we know the number of people in the room and why the sum wasn’t enough information to solve the problem. The final ‘nail in the coffin’ (so to speak) is the owner of the hamster with the wooden leg; the problem states:

Mathematician #2: One more clue: my oldest daughter has a pet hamster with a wooden leg.

It’s not the hamster, it is the fact that the oldest daughter (there’s only one) exists. Knowing that last piece of information provides the final answer:

  
SQL> 
SQL> --
SQL> -- Return the one set of values meeting all of
SQL> -- the criteria:
SQL> --
SQL> -- Product of 36
SQL> -- Sum of some unknown number
SQL> -- Oldest child exists
SQL> --
SQL> with age_list as (
  2        select rownum age
  3        from all_objects
  4        where rownum = age1.age
 15        and age3.age >= age2.age
 16        and age1.age*age2.age*age3.age = 36
 17  ),
 18  summed_check as (
 19        select youngest, middle, oldest, sum, product
 20        from (
 21         select youngest, middle, oldest, sum, product,
 22         count(*) over (partition by sum) ct
 23         from product_check
 24        )
 25        where ct > 1
 26  )
 27  select *
 28  from summed_check
 29  where oldest > middle;

  YOUNGEST     MIDDLE     OLDEST        SUM    PRODUCT                          
---------- ---------- ---------- ---------- ----------                          
         2          2          9         13         36                          

SQL> 

The ages of the girls are 9, 2 and 2 which also clarifies the question of identical twins.

The problem was solved in a systematic and (to me, at least) logical way by breaking the problem down into workable pieces.

So you don’t encounter such problems at college reunions or parties (what a dull life that must be); you may encounter them at work. This next problem was presented in the Oracle PL/SQL group:

Hi,

I have 3 columns of data

Column 1:subscription
Column 2: invoice number
Column 3: Service

I need to seperate the subscription types into new, renewals and additional
which is fine but the next bit i am having trouble

Each invoice number can have 1 or more service

e.g.
Invoice Number Service
123 Photocopying
123 Printing
123 Scan & Store
234 Photocopying
234 Scan & Store
345 Photocopying
345 Printing

I apply a rate for each service e.g.
photocopying = 1.5
printing = 1.7

but if Scan and store is in an invoice with photocopying we charge an extra
1.5

but if printing is a service with the scan and store a different rate
applies 1.7

so i can’t just count scan and store and apply a rate i have to figure out
if it is with photocopying or with printing and then apply the rate

What I want to be able to do is creat a table with columns that calculates
this
so i get a 4 columns:

Service usage rate total
photocopying 3 1.5 4.5
Printing 2 1.7 3.4
Scan & Store 1 1.5 1.5
Scan & Store w/Print 1 1.7 1.7

The problem comes in when i’m trying to count scan and store wit/without
printing. I can’t figure it out.

I import the report from an excel spreadsheet into acces and want to run a
query that does all this…

thanks in advance,
ainese

With this problem I decided to change the table a bit and add a numeric SERVICE_CD column:

  
SQL> Create table subscription(
  2          subscr_type varchar2(15),
  3          invoice number,
  4          service varchar2(40),
  5          service_cd number
  6  );

Table created.

SQL>
SQL> insert all
  2  into subscription
  3  values('RENEWAL',123,'Photocopying',0)
  4  into subscription
  5  values('RENEWAL',123,'Printing',2)
  6  into subscription
  7  values('RENEWAL',123,'Scan '||chr(38)||' Store',5)
  8  into subscription
  9  values('ADDITIONAL',234,'Photocopying',0)
 10  into subscription
 11  values('ADDITIONAL',234,'Scan '||chr(38)||' Store',5)
 12  into subscription
 13  values('NEW',345,'Photocopying',0)
 14  into subscription
 15  values('NEW',345,'Printing',2)
 16  select * From dual;

7 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Using the wm_concat() function and the BITAND operator produced results that will make the final solution easier to code; using BITAND allows Oracle to generate a result based upon the sum of the SERVICE_CD values and by properly choosing those SERVICE_CD entries make it easier to isolate the various combinations:

  
SQL> select subscr_type, invoice, services,
  2         bitand(service_cds, 0) col1,
  3         bitand(service_cds, 2) col2,
  4         bitand(service_cds, 7) col3
  5  from
  6  (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
  7  from subscription
  8  group by subscr_type,invoice);

SUBSCR_TYPE        INVOICE SERVICES                                       COL1       COL2       COL3
--------------- ---------- ---------------------------------------- ---------- ---------- ----------
NEW                    345 Photocopying,Printing                             0          2          2
RENEWAL                123 Photocopying,Printing,Scan & Store                0          2          7
ADDITIONAL             234 Photocopying,Scan & Store                         0          0          5

SQL>

Knowing which BITAND results indicate which chargeable combinations allows using DECODE to produce a version of the desired results:

  

SQL> column services format a40
SQL> break on report skip 1
SQL> compute sum  of photocopy printing scan_and_store scan_and_store_w_prt on report
SQL>
SQL> select subscr_type, invoice, services,
  2         decode(bitand(service_cds, 0), 0, 1.5, 0) photocopy,
  3         decode(bitand(service_cds, 2), 2, 1.7, 0) printing,
  4         decode(bitand(service_cds, 7), 5, 1.5, 0) scan_and_store,
  5         decode(bitand(service_cds, 7), 7, 1.7, 0) scan_and_store_w_prt
  6  from
  7  (select subscr_type, invoice, wm_concat(service) services, sum(service_cd) service_cds
  8  from subscription
  9  group by subscr_type,invoice);

SUBSCR_TYPE INVOICE SERVICES                           PHOTOCOPY PRINTING SCAN_STORE SCAN_STORE_PRT
----------- ------- ---------------------------------- --------- -------- ---------- --------------
NEW             345 Photocopying,Printing                    1.5      1.7          0              0
RENEWAL         123 Photocopying,Printing,Scan & Store       1.5      1.7          0            1.7
ADDITIONAL      234 Photocopying,Scan & Store                1.5        0        1.5              0
                                                       --------- -------- ---------- --------------
sum                                                          4.5      3.4        1.5            1.7

SQL>

All services in this example are charged the appropriate rates, including the adjustments made for certain combinations of service.

One last problem is one found often on the web:

Display the second highest salary in the employee table
Display the employee id, first name, last name and salary for employees earning the second highest salary

Depending on which question is asked several solutions present themselves. The first is the ‘obvious’ solution:

  
SQL> select salary
  2  from
  3  (select salary from employees order by 1 desc)
  4  where rownum = 2;

no rows selected

SQL>

which doesn’t work because ROWNUM is never set to 1 so it can’t get to 2. A modest rewrite produces:

  
SQL> select salary
  2  from
  3  (select rownum rn, salary from
  4  (select salary from employees order by 1 desc))
  5  where rn = 2;

    SALARY
----------
     17000

1 row selected.

SQL>

which still might not be the correct answer as more than one person may have the same salary, including the highest. Another rewrite, using DENSE_RANK() provides the solution:

  
SQL> select salary from
  2  (select salary, dense_rank() over (order by salary desc) rk
  3          from employees)
  4  where rk=2;

    SALARY
----------
     17000
     17000

2 rows selected.

SQL>

To produce more information the above query needs a small modification:

  
SQL> select employee_id, first_name, last_name, salary
  2  from
  3  (select employee_id, first_name, last_name, salary, rank() over (order by salary desc) rk
  4   from employees)
  5  where rk =2;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        101 Neena                Kochhar                        17000
        102 Lex                  De Haan                        17000

2 rows selected.

SQL>

RANK() and DENSE_RANK() do just what they’re named — rank the requested values — but only DENSE_RANK() will not skip ranking numbers when duplicate values exist:

  
SQL> select salary, rank() over (order by salary desc) rk
  2          from employees;

    SALARY         RK
---------- ----------
     24000          1
     17000          2
     17000          2
     14000          4
     13500          5
     13000          6
     12000          7
     12000          7
     12000          7
     11500         10
...

Notice that the third highest salary is ranked 4 with RANK(); not so with DENSE_RANK():

  
SQL> select salary, dense_rank() over (order by salary desc) rk
  2          from employees;    SALARY         RK
---------- ----------
     24000          1
     17000          2
     17000          2
     14000          3
     13500          4
     13000          5
     12000          6
     12000          6
     12000          6
     11500          7
     11000          8
     11000          8
     11000          8
     10500          9
     10500          9
     10000         10
     10000         10
     10000         10
     10000         10
...

which is why DENSE_RANK() was used to solve the problem.

Problem solving, when given a little thought, isn’t a terrible chore if you know how to read the problem and extract known information. From that you can eventually arrive at a solution (and, yes, multiple solutions can exist depending upon how you think about the problem). The above are examples to get you started thinking in the ‘right’ direction. As always, some practice at solving problems is recommended so take these problems, work them through, change data, work them through again (and you may find holes in my solutions that I didn’t consider). The more you practice, the more you learn.

A train leaves station A at 3:30 PM and travels west at 50 miles per hour …

About these ads

4 Comments »

  1. Hmmm…..Wouldn't the last age case in your example – 3,3,4 – equally satisfy the conditions as proposed? Nothing says that the sum must be 13, a sum of 10 is as valid. Granted: a single year of difference between siblings is a bit of a stretch for the mother! :-)

    Comment by Noons — October 27, 2011 @ 21:28 | Reply

  2. Not as the problem is stated — had there been 10 people in the room the sum would have been sufficient to determine the ages; since the sum did not provide a definitive answer we are 'stuck' with the two sets having the sum of 13, thus making 9,2,2 the solution.

    Comment by d_d_f — October 27, 2011 @ 21:40 | Reply

  3. Ah yes, indeed :dang, we're after duplicates! :-)

    Comment by Noons — October 27, 2011 @ 23:03 | Reply

  4. Good post and interesting advice "The more you practice, the more you learn"I will offer you a beer, David, next time.CiaoAlberto

    Comment by Alberto — October 28, 2011 @ 14:59 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 675 other followers

%d bloggers like this: