Re: DBA quiz

From: Vadim Tropashko <Vadim_member_at_newsranger.com>
Date: Sun, 04 Mar 2001 20:34:45 GMT
Message-ID: <FFxo6.1570$45.1949_at_www.newsranger.com>

That's SRAT from

Martin Henz. Don't be puzzled! In Proceedings of the Workshop on Constraint Programming Applications, in conjunction with the Second International Conference on Principles and Practice of Constraint Programming (CP96), Cambridge, Massachusetts, USA, August 1996.

There is a lot of web references as well.

If you don't like going procedural, here is SQL solution:

create table ANSWERS (
answer CHAR(1);
)

insert into ANSWERS values ('A');
insert into ANSWERS values ('B');
insert into ANSWERS values ('C');
insert into ANSWERS values ('D');
insert into ANSWERS values ('E');

Since SQL doesn't support implication "A->B" directly, it is represented as "not A or B", which makes the query somewhat less readable.

The query:
^^^^^^^^^^

select * from (select t1.answer a1, t2.answer a2, t3.answer a3, t4.answer a4, t5.answer a5, t6.answer a6, t7.answer a7, t8.answer a8, t9.answer a9, t10.answer a10
from answers t1, answers t2, answers t3, answers t4, answers t5 ,answers t6, answers t7, answers t8, answers t9, answers t10) where (
-- #1
(a1<>'A' or (a2='B' and a1<>'B') ) -- (A)

and (a1<>'B' or (a3='B' and a1<>'B' and a2<>'B') ) -- (B)
and (a1<>'C' or (a4='B' and a1<>'B' and a2<>'B' and a3<>'B') ) -- (C)
and (a1<>'D' or (a5='B' and a1<>'B' and a2<>'B' and a3<>'B' and a4<>'B') ) --

(D)

and (a1<>'E' or (a6='B' and a1<>'B' and a2<>'B' and a3<>'B' and a4<>'B' and a5<>'B') ) -- (E)
) and ( -- #2
(a2<>'A' or (a2=a3 and a1<>a2 and a3<>a4 and a4<>a5 and a5<>a6 and
a6<>a7 and a7<>a8 and a8<>a9 and a9<>a10) ) -- (A) and (a2<>'B' or (a3=a4 and a1<>a2 and a2<>a3 and a4<>a5 and a5<>a6 and a6<>a7 and a7<>a8 and a8<>a9 and a9<>a10) ) -- (B) and (a2<>'C' or (a4=a5 and a1<>a2 and a2<>a3 and a3<>a4 and a5<>a6 and a6<>a7 and a7<>a8 and a8<>a9 and a9<>a10) ) -- (C) and (a2<>'D' or (a5=a6 and a1<>a2 and a2<>a3 and a3<>a4 and a4<>a5 and a6<>a7 and a7<>a8 and a8<>a9 and a9<>a10) ) -- (D) and (a2<>'E' or (a6=a7 and a1<>a2 and a2<>a3 and a3<>a4 and a4<>a5 and a5<>a6 and a7<>a8 and a8<>a9 and a9<>a10) ) -- (E) ) and ( #3
(a3<>'A' or (a10=a3) ) -- (A)

and (a3<>'B' or (a9=a3 and a10!=a3) ) -- (B)
and (a3<>'C' or (a8=a3 and a10!=a3 and a9!=a3) ) -- (C)
and (a3<>'D' or (a7=a3 and a10!=a3 and a9!=a3 and a8!=a3) ) -- (D)
and (a3<>'E' or (a6=a3 and a10!=a3 and a9!=a3 and a8!=a3 and a7!=a3) ) 

-- (E)
) and (
(a4<>'A' or CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')=0 ) --
(A)

and (a4<>'B' or CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')=1 ) --
(B)

and (a4<>'C' or CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')=2 ) --
(C)

and (a4<>'D' or CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')=3 ) --
(D)

and (a4<>'E' or CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')=4 ) --
(E)

) and (
(a5<>'A' or a10=a5 ) -- (A)

and (a5<>'B' or a9=a5 ) -- (B)
and (a5<>'C' or a8=a5 ) -- (C)
and (a5<>'D' or a7=a5 ) -- (D)
and (a5<>'E' or a6=a5 ) -- (E)

) and (
(a6<>'A' or

CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')=CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'B') ) -- (A)
and (a6<>'B' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')=CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'C') ) -- (B)
and (a6<>'C' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')=CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'D') ) -- (C)
and (a6<>'D' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')=CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'E') ) -- (D)
--and (a6<>'E' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')=CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A') ) -- (E)
) and (
(a8<>'A' or

CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'E')=2 ) -- (A)
and (a8<>'B' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'E')=3 ) -- (B)
and (a8<>'C' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'E')=4 ) -- (C)
and (a8<>'D' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'E')=5 ) -- (D)
and (a8<>'E' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'A')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'E')=6 ) -- (E)
) and (
(a9<>'A' or

CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'B')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'C')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'D') in (2,3,5,7) ) -- (A)
and (a9<>'B' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'B')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'C')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'D') in (1,6) ) -- (B)
and (a9<>'C' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'B')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'C')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'D') in (0,1,4,9) ) -- (C)
and (a9<>'D' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'B')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'C')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'D') in (0,1,8) ) -- (D)
and (a9<>'E' or
CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'B')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'C')+CHARCOUNT(a1||a2||a3||a4||a5||a6||a7||a8||a9||a10,'D') in (0,5,10) ) -- (E)
)

It returns ten rows, but I skipped one question (as an applicant to DBA position you should be able to fugure out which one:-). The execution time is almost 5 min -- compare that to C. Received on Sun Mar 04 2001 - 21:34:45 CET