Re: SQL puzzle (for entertainment only)
Paul Roberts wrote:
> { ...how can I, in SQL, get the following information:
>
> Tall? Nice? Bold? Count
>
> Y Y Y 1
> Y Y N 2
> Y N Y 4
> Y N N 1
> N Y Y 2
> N Y N 3
> N N Y 3
>
> ...although not necessarily in this exact format?
>
> - Paul (not a spokesman)
>
> Well, I thought it an interesting problem although many will
> doubtless disagree. It did come up in a business context, w/
> product IDs rather than letters. }
Here is a decision for this puzzle.It has linear complexity O (n) where n -
number of the tables. (The resulting table is built in a required format.)
create temp table tall ( name char(1) ) with no log;
insert into tall values ( 'A' );
insert into tall values ( 'B' );
insert into tall values ( 'C' );
insert into tall values ( 'K' );
insert into tall values ( 'G' );
insert into tall values ( 'H' );
insert into tall values ( 'I' );
insert into tall values ( 'J' );
create temp table nice ( name char(1) ) with no log;
insert into nice values ( 'B' );
insert into nice values ( 'C' );
insert into nice values ( 'K' );
insert into nice values ( 'L' );
insert into nice values ( 'M' );
insert into nice values ( 'D' );
insert into nice values ( 'E' );
insert into nice values ( 'F' );
create temp table bold ( name char(1) ) with no log;
insert into bold values ( 'K' );
insert into bold values ( 'L' );
insert into bold values ( 'M' );
insert into bold values ( 'N' );
insert into bold values ( 'P' );
insert into bold values ( 'Q' );
insert into bold values ( 'G' );
insert into bold values ( 'H' );
insert into bold values ( 'I' );
insert into bold values ( 'J' );
create temp table c_tall ( name char(1) ) with no log;
insert into c_tall values ( 'Y' );
insert into c_tall values ( 'N' );
create temp table c_nice ( name char(1) ) with no log;
insert into c_nice values ( 'Y' );
insert into c_nice values ( 'N' );
create temp table c_bold ( name char(1) ) with no log;
insert into c_bold values ( 'Y' );
insert into c_bold values ( 'N' );select t.name tall, n.name nice, b.name bold, 0 rate
from c_tall t, c_nice n, c_bold b
where not ((t.name='N') and (n.name='N') and (b.name='N'))
into temp stat with no log;
update stat set rate=1 where tall='Y';
update stat set rate=rate+2 where nice='Y';
update stat set rate=rate+4 where bold='Y';
select name from tall into temp comm1 with no log;
insert into comm1 select name from nice;
insert into comm1 select name from bold;
select distinct name, 0 rate from comm1 into temp comm with no log;
update comm set rate=1 where name in (select name from tall);
update comm set rate=rate+2 where name in (select name from nice);
update comm set rate=rate+4 where name in (select name from bold);
select s.tall, s.nice, s.bold, count(*)
from stat s, comm c
where s.rate=c.rate
group by s.tall, s.nice, s.bold
order by s.tall desc, s.nice desc, s.bold desc;
--
____________________________
Dmitry V. Belyaev
mailto:bdw@unibest.ru