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 <= 36
5 )
6 select *
7 from age_list;
AGE
----------
1
2
3
4
5
6
7
8
9
10
11
AGE
----------
12
13
14
15
16
17
18
19
20
21
22
AGE
----------
23
24
25
26
27
28
29
30
31
32
33
AGE
----------
34
35
36
36 rows selected.
SQL>
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 <= 36
5 ),
6 product_check as (
7 select
8 age1.age as youngest,
9 age2.age as middle,
10 age3.age as oldest,
11 age1.age+age2.age+age3.age as sum,
12 age1.age*age2.age*age3.age as product
13 from age_list age1, age_list age2, age_list age3
14 where age2.age >= 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:
SQL> --
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 <= 36
5 ),
6 product_check as (
7 select
8 age1.age as youngest,
9 age2.age as middle,
10 age3.age as oldest,
11 age1.age+age2.age+age3.age as sum,
12 age1.age*age2.age*age3.age as product
13 from age_list age1, age_list age2, age_list age3
14 where age2.age >= 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> -- 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 <= 36
5 ),
6 product_check as (
7 select
8 age1.age as youngest,
9 age2.age as middle,
10 age3.age as oldest,
11 age1.age+age2.age+age3.age as sum,
12 age1.age*age2.age*age3.age as product
13 from age_list age1, age_list age2, age_list age3
14 where age2.age >= 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. For those using Oracle 12.1.0.x, where wm_concat doesn’t appear to be available, or for those not wanting to use wm_concat here is the same solution using listagg:
SQL>
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 bitand(service_cds, 0) col1,
3 bitand(service_cds, 2) col2,
4 bitand(service_cds, 7) col3
5 from
6 (select subscr_type, invoice, listagg(service,',') within group (order by subscr_type, invoice) 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>
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, listagg(service,',') within group (order by subscr_type, invoice) services, sum(service_cd) service_cds
8 from subscription
9 group by subscr_type,invoice);
SUBSCR_TYPE INVOICE SERVICES PHOTOCOPY PRINTING SCAN_AND_STORE SCAN_AND_STORE_W_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>
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 …
Oracle Tips and Tricks -- David Fitzjarrell 