Conde Bond Post

Sólo existe un tipo de conocimiento, aquel que se transmite.

Entreteniments amb SQL


El James Murthag d’AllThingsOracle va proposar una endevinalla matemàtica que vaig considerar que era interessant per a la meva filla…

Tal i com vaig prometre, publico una de les múltiples solucions que té, cercant sobretot que la meva filla la pugués entendre.

Comencem creant un espai de treball, el nostre es diu ATOQ
(AllThingsOracleQuiz):

sqlplus / as sysdba
create user ATOQ identified by atoq account unlock;
grant connect, resource to ATOQ;
connect ATOQ/atoq

L’enigma consisteix en trobar la llista de números més llarga fins al 100, on cada número sigui múltiple de l’anterior.

Crearem tres taules:

  • PRIMENUMS on guardarem els nombres primers més petits (el mínim divisor de qualsevol número, és un nombre primer), i el nomm de la llista de números que busquem…
create table primeNums (primeNum NUMBER (2) not  null, listname VARCHAR2(10) not null);
INSERT INTO PRIMENUMS VALUES (2,'Primera');
INSERT INTO PRIMENUMS VALUES (3,'Segona');
INSERT INTO PRIMENUMS VALUES (5,'Tercera');
INSERT INTO PRIMENUMS VALUES (7,'Quarta');
INSERT INTO PRIMENUMS VALUES (11,'Cinquena');
INSERT INTO PRIMENUMS VALUES (13,'Sisena');
COMMIT;

No és una solució molt ortodoxa des d’un punt de vista de modelatge de dades, però ja ens servirà…

  • ALLTHINGSQUIZWORK On guardarem les llistes de números de l’enigma:
create table allThingsQuizWork (listname VARCHAR2(10) not null, seqNum NUMBER(2) not null, value NUMBER(2) );
COMMENT ON TABLE allThingsQuizWork IS 'Taula per treballar la solucio del problema';
COMMENT ON COLUMN allThingsQuizWork.listName IS 'Nom de la llista de numeros fins a 100';
COMMENT ON COLUMN allThingsQuizWork.seqNum   IS 'Posicio que ocupen en la sequencia';
COMMENT ON COLUMN allThingsQuizWork.value    IS 'Numero de la sequencia';
  • QUIZLOG On guardarem la solució de l’enigma…
create table quizlog (text VARCHAR2(2000) NOT NULL);

Hipòtesi
1. L’1 és el primer número de totes les llistes. No admet discussió!🙂

2. Començant per l’1 hem de incrementar d’un amb un el número, fins arribar al 100.

3. Si en la llista de números, el número següent ha de ser múltiple de l’anterior, es que el segon es divisible pel primer. Tampoc admet discussió!😉

NOTA: Si un número es divisible per un altre, és que la resta de la seva divisió és 0.
En SQL aquesta equació es pot resoldre amb la funció MODULUS que ens dona la resta de 
la divisió entre dos números. 
Per exemple, la divisibilitat de b en a s'escriu de la següent manera: MOD(b,a) = 0

Per tant per cada número que evaluem haurem de buscar si és divisble pel número primer que utilizem per fer la llista i també si és divisible per l’anterior (ho serà per força, però ho comprovem).

4. Si el número que evaluem no és divisible pel número primer que utilitzem per fer la llista, podria ser que fos divisible pel número anterior de la llista, per tant ho comprovem.

I ja està, això traduït a SQL es pot escriure de moltes maneres però he triat aquesta:

DECLARE
  b             allThingsQuizWork.value%TYPE:=0;
  c             quizlog.text%TYPE;
  P             allThingsQuizWork.value%TYPE:=0;
  seqNum        allThingsQuizWork.seqNum%TYPE:=0;
  listNum       allThingsQuizWork.value%TYPE:=0;
BEGIN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE QUIZLOG';
   EXECUTE IMMEDIATE 'TRUNCATE TABLE ALLTHINGSQUIZWORK';
   FOR i IN (SELECT primeNum,listName FROM primeNums ORDER BY primeNum ASC) LOOP
-- Agafem els nombres primers com a divisors i per crear la llista de números...
     p:=i.primeNum;
     listNum:=0;
     FOR a IN 1..100 LOOP
-- Recorrem els 100 primers números naturals en orde creixent
        IF MOD(a,p) = 0 THEN
-- Si el numero es divisible pel numero primer que estem mirant
-- comprovem si tenim valors a la nostra llista
          SELECT COUNT(*) INTO listNum FROM allThingsQuizWork WHERE listName=i.listName;
          IF listNum = 0 THEN
             seqNum:=seqNum+1;
-- Si no tenim valors a la llista, l'afegim juntament amb l 1
             INSERT INTO ALLTHINGSQUIZWORK VALUES(i.LISTNAME,1,1); COMMIT;
             seqNum:=seqNum+1;
             INSERT INTO ALLTHINGSQUIZWORK VALUES(i.LISTNAME,seqNum,a); COMMIT;
-- Ens guardem el numero per comparar despres amb el seguent
             b:=a;
          ELSE
-- Si tenim valors a la llista comprovem si divideix al que ens hem guardat abans
             IF MOD(a,b) = 0 THEN
                seqNum:=seqNum+1;
                INSERT INTO ALLTHINGSQUIZWORK VALUES(i.LISTNAME,seqNum,a); COMMIT;
-- Ens guardem el numero per comparar despres amb el seguent
                b:=a;
             ELSE
-- El numero que estem mirant no es divisible pel nombre primer, 
-- ni pel que teniem guardat, seguim endavant...
                NULL;
             END IF;
          END IF; 
        ELSE
-- El numero que estem mirant no es divisible pel primer
-- pero ens assegurem que no sigui divisible pel que tenim guardat... EP!!! ; )
          IF MOD(a,b) = 0 THEN
             seqNum:=seqNum+1;
             INSERT INTO ALLTHINGSQUIZWORK VALUES(i.LISTNAME,seqNum,a); COMMIT;
-- Ens guardem el numero per comparar despres amb el seguent
             b:=a;
           ELSE
-- El numero que estemm mirant es indivisible pel primer i pel que teniem guardat, 
-- seguim endavant...
             NULL;
          END IF;
        END IF;
     END LOOP;
   END LOOP;
-- Una vegada hem acabat diem quants numeros té cada llista...
   FOR k in (SELECT DISTINCT PRIMENUM,LISTNAME FROM PRIMENUMS ORDER BY PRIMENUM) LOOP
       SELECT COUNT(*) INTO listNum FROM allThingsQuizWork WHERE LISTNAME=k.LISTNAME;
       c:='La '||TRIM(k.LISTNAME)||' llista te '||listNum||' numeros : ';
       FOR l IN (SELECT DISTINCT SEQNUM, VALUE 
                   FROM allThingsQuizWork 
                  WHERE LISTNAME=k.LISTNAME 
                  ORDER BY SEQNUM) LOOP
          IF L.SEQNUM = 1 THEN
            c:=c|| l.VALUE;
          ELSE
            c:=c||','||l.value;
          END IF;
        END LOOP;
        INSERT INTO QUIZLOG VALUES (c); COMMIT;
    END LOOP;
EXCEPTION
  WHEN OTHERS THEN 
    DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLErrm,1,200));
END;
/

Comprovem el resultat consultant la taula QUIZLOG:
el resultat

I esperem que nos haguem equivocat…

Fins la propera.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: