SQL puzzle (for entertainment only)

SQL puzzle (for entertainment only)

Post by Jason Harr » Sun, 31 Dec 1899 09:00:00



Why not create a table with one record for each letter and extra
fields for the attributes?

Would that make things more simple?

Create the table with fields char_attr1-10 and int_attr1-10 and just
use them as needed. Then create a view that selects the attr fields
but alias's the attributes you are using and does not select the ones
you are not using. Then when you begin to use an attribute just modify
the view. Then the names would make sense and you just give your
manager a query tool and he can select them all out himself.

This will allow much better performance if you were to put millions of
rows in the table also, but would sacrifice a small amount of disk
space. Disk space is cheaper that delveopment resources to write all
those SQLs you have at the bottom.

Just my quick ideas on this.

Jason



Quote:>{   I'm doing the casting for Sesame Street (tm)....

>I have a table that lists all the nice letters, another table that
>lists all the bold letters, & another table that lists all the tall
>letters:-
>              TALL           NICE           BOLD

>              name           name           name
>              ____           ____           ____

>               A              B              K
>               B              C              L
>               C              K              M
>               K              L              N
>               G              M              P
>               H              D              Q
>               I              E              G
>               J              F              H
>                                             I
>                                             J

>My producer demands a quick summary of how many letters are tall and
>nice and bold; how many are tall and nice but not bold, how many are
>nice but neither tall nor bold, etc.

>So how to calculate these numbers? One way would be a series of seven
>SQL statements:  

>(1) select count(*)
>      from bold, tall, nice
>     where bold.name = nice.name
>       and bold.name = tall.name ;                  - number who are tall and
>                                                      bold and nice.
>(2) select count(*)
>      from bold
>     where name not in ( select name from nice )
>       and name not in ( select name from tall ) ;  - number who are bold but
>                                                      neither tall nor nice.
>etc.

>But I am secretly convinced that he is going to spring more binary
>attributes on me - which letters are fuzzy, which are rude, which
>are cheap, etc - and then ask for the number of letters in each of
>resulting subsets. Four attributes will mean 15 subsets, and thus
>15 SQL statements. Five attributes will mean 31 statements.  How
>can I produce the counts without doing an exponential amount of
>work as the number of binary attributes increases?

>Put another way, given the following create/insert statements...  }

>       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" ) ;

>{ ...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. }

 
 
 

SQL puzzle (for entertainment only)

Post by Dmitry V. Belyae » Sun, 31 Dec 1899 09:00:00



> { ...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


 
 
 

1. Date puzzle has me puzzled

I have a problem in which I need to know a person's ZIP code at the time a
service was given.  Here's a very simplified version of the table and data.

CREATE TABLE Address
(  [Id] int,
   [Start] [smalldatetime],
   [Stop]  [smalldatetime] NULL,
   [ZIP] [char] (5) NULL
)

INSERT Address VALUES (1, '1/1/2002', '5/19/2002', '90028')
INSERT Address VALUES (1, '5/20/2002', '7/4/2002', '90035')
INSERT Address VALUES (1, '7/5/2002', '9/30/2002', '90010')
INSERT Address VALUES (1, '10/1/2002', NULL, '90210')
INSERT Address VALUES (2, '1/1/2002', '3/7/2002', '90028')
INSERT Address VALUES (2, '3/8/2002', '9/10/2002', NULL)
INSERT Address VALUES (2, '9/11/2002', NULL, '90010')

A NULL Stop date means that person is still living at that location.  It's
also possible that a ZIP code will sometimes be unknown (NULL) which should
return NULL.

The problem is to write a query that will give me each person's ZIP code at
a certain point in time, say 7/8/2002.  That should give a result set of (1,
'90010') and (2, NULL).  Any idea how to write this?   Thanks.

2. DAO 3.5 / 3.6 and VC6 and Win2K

3. Solve this Paradox puzzle...! (a real puzzle)

4. Atlanta C/S Architect

5. on-line entertainment

6. A OPINION ?THE BEST RAD SOFTWARE DEVELOPMENT TOOL

7. Oracle DBAs needed for exciting entertainment company!!!

8. pgsql/src/interfaces/libpq libpq-fe.h libpq-int.h

9. ENTERTAINMENT TECHNOLOGY CENTER SEEKS PROGRAMMER

10. Oracle DBAs needed for exciting entertainment company!!!

11. FRANCE - ORACLE DBA - Leisure and Entertainment

12. ENTERTAINMENT TECHNOLOGY CENTER SEEKS PROGRAMMER