intersting sql puzzle

  • Independence Day - II

    The All Nations United Force (ANUF), formed to protect the human race

    against alien attacks, maintains a database of known extra terrestrial

    species and the types of missiles that these species are capable of

    using.

    The ANUF has developed several anti-missiles each of which is capable of

    protecting against one or more types of alien missiles. On one dark day

    the

    ANUF radars signal that several missiles that are known to be originated

    from species X are heading towards the Earth. The ANUF Chief of Staff

    calls

    you and asks to find out if there is a single ANUF anti-missile that can

    defuse ALL types of missiles that species X can use. As the application

    ANUF

    uses does not allow such a query to be answered you are required to

    issue a

    direct query against the ANUF database and find out the list of

    anti-missiles each of which can defuse ALL the missiles that can be used

    by

    species X.

     

    For example, assume species X can use missiles M-1, M-2 and M-3. Further

    assume ANUF anti-missile AM-1 can defuse {M-1, M-2, M-3, M-4}, ANUF

    anti-missile AM-2 can defuse {M-1, M-2} and ANUF anti-missile AM-3 can

    defuse {M-1, M-2, M-3}. Now your query should list AM-1 and AM-3 as the

    candidate anti-missiles since each of these have potential to defuse ALL

    the

    missiles that can be used by species X.

     

    The relational schema of the ANUF missile database is as follows:

    SPECIES(spcid INTEGER primary key,

    spcname VARCHAR(100));

    ALIEN_MISSILE(missile_id INTEGER primary key,

    missile_name VARCHAR(100),

    descr VARCHAR(400));

    ALIEN_CAPABILITY(spcid INTEGER references SPECIES(spcid),

    missile_id INTEGER,

    references ALIEN_MISSILE(missile_id));

    ANTI_MISSILE(antim_id INTEGER primary key,

    anitm_name VARCHAR(100),

    anitm_descr VARCHAR(400));

    DEFUSE_CAPABILITY(antim_id INTEGER references ANTI_MISSILE(antim_id),

    missile_id INTEGER references

    ALIEN_MISSILE(missile_id));

     

    Instructions

    1. Your answer should be a single SQL query

    2. The query should be database neutral (should conform to the SQL92

    standard). You should not use any vendor specific SQL extensions,

    user-defined functions or stored procedures.

    3. The species name should NOT be hard coded in the query and should

    instead

    be the parameter marker - "?"

    4. The query result should have exactly two columns antim_id and

    antim_name.

    5. The query should be submitted in a file and should be written in a

    single

    line.

  • Hi all,

    2 questions:

    1. why the first puzzle message isn't showing ? (I am new to contests, so this may be normal).

    2. why the puzzle is not listed at the official contest home ?

    http://www.sqlservercentral.com/forums/messages.aspx?forumid=160

    BTW, I solved it, but I am not sure whether and where to post the file.

    Regards,

    Goce.

  • Here is my solution:

    SELECT
      am.antim_id AS antim_id, am.anitm_name AS antim_name
    FROM
      (SELECT
         antim_id, COUNT(dc.missile_id) AS num_missiles
       FROM
         (SELECT
            missile_id
          FROM
            (SELECT spcid FROM SPECIES WHERE (spcname = ?)) AS s
            INNER JOIN ALIEN_CAPABILITY AS ac ON ac.spcid = s.spcid
    
         )  AS sm1
         INNER JOIN DEFUSE_CAPABILITY AS dc ON dc.missile_id = sm1.missile_id
       GROUP BY
         antim_id
      ) as candidates
      INNER JOIN ANTI_MISSILE AS am ON am.antim_id = candidates.antim_id
    WHERE
      (candidates.num_missiles = 
        (SELECT COUNT(*) AS possible_missiles
         FROM
           (SELECT missile_id
            FROM
              (SELECT spcid FROM SPECIES WHERE (spcname = ?)) AS s
              INNER JOIN ALIEN_CAPABILITY AS ac ON ac.spcid = s.spcid
            ) AS sm2
        )
      )
    

    Regards,

    Goce.

  • Why do I think that this is a homework problem? 

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

  • Its just a puzzle....i stuck up ....

    just curious to know the answer........any help ?

  • This is the actual bottle neck in the above puzzle

    If i want to select  antim_id from following table using query Q1...which has all the missile_id values returned by another query Q2 .

    For eg. if Q2 returns 100,300,400

    my Q1 should return 3000...How to do it.? Will using ALL will help?

    DEFUSE_CAPABILITY

    +----------+------------+

    | antim_id | missile_id |

    +----------+------------+

    |     1000 |        100 |

    |     1000 |        200 |

    |     2000 |        100 |

    |     2000 |        200 |

    |     2000 |        300 |

    |     3000 |        100 |

    |     3000 |        200 |

    |     3000 |        300 |

    |     3000 |        400 |

    |     3000 |        500 |

    +----------+------------+

     

  • Hi Thomas,

    Why did you post the puzzle as a contest ? It was not listed in the official contests list on SSC. I don't like situations like this, when it is possible that some SSC members abuse the enthusiasm of others. This may destroy the community spirit of the site.

    Why don't you post the puzzle URL here ? I suppose that many of us are interested in SQL puzzles, so we can test our knowledge, too.

    According to this part of your post:

    "my Q1 should return 3000...How to do it.? Will using ALL will help?"

    I can conclude that you are a newbie to SQL. Why don't you try yourself with easier puzzles ?

    Regards,

    Goce.

  • sorry guys i cannot give a link , i got it as a fwd email.

    yes i have little knowledge on sql, but just wanted to get it solved.....

  • Hi Thomas,

    so somebody tried to puzzle you, too :-).

    I suppose that mail originated from somewhere ... so we can still obtain some more info about the puzzle origin.

    Regards,

    Goce.

  • Here's a much smaller teaser.

     

    READ this code.

     

    print 'hello, David'

    /*

    go

    print 'hello, Allen'

    go

    */

    print 'Goodbye, All'

     

    Before running it, see if you can anticipate what the results will be. You'll be surprised.

  • hey can u post the create script

    Hold on hold on soldier
    When you add it all up
    The tears and marrowbone
    There's an ounce of gold
    And an ounce of pride in each ledger
    And the Germans killed the Jews
    And the Jews killed the Arabs
    And Arabs killed the hostages
    And that is the news
    And is it any wonder
    That the monkey's confused

  • Hi all,

    here is the script I wrote for myself, according to the puzzle specification. It also contains some sample data.

    CREATE TABLE SPECIES(
      spcid INTEGER primary key,
      spcname VARCHAR(100)
    )
    GO
    
    CREATE TABLE ALIEN_MISSILE(
      missile_id INTEGER primary key,
      missile_name VARCHAR(100),
      descr VARCHAR(400)
    )
    GO
    
    CREATE TABLE ALIEN_CAPABILITY(
      spcid INTEGER,
      missile_id INTEGER,
      constraint fk_alien_capability_species foreign key (spcid) references SPECIES(spcid),
      constraint fk_alien_capability_alien_missile foreign key (missile_id) references ALIEN_MISSILE(missile_id)
    )
    GO
    
    CREATE TABLE ANTI_MISSILE(
      antim_id INTEGER primary key,
      anitm_name VARCHAR(100),
      anitm_descr VARCHAR(400)
    )
    GO
    
    CREATE TABLE DEFUSE_CAPABILITY(
      antim_id INTEGER,
      missile_id INTEGER,
      constraint fk_defuse_capability_anti_missile foreign key (antim_id) references ANTI_MISSILE(antim_id),
      constraint fk_defuse_capability_alien_missile foreign key (missile_id)  references ALIEN_MISSILE(missile_id)
    )
    GO
    
    SET NOCOUNT ON
    INSERT INTO SPECIES (spcid, spcname) VALUES (1, 'X')
    INSERT INTO SPECIES (spcid, spcname) VALUES (2, 'Y')
    
    
    INSERT INTO ALIEN_MISSILE(missile_id, missile_name, descr) VALUES (1, 'M-1', 'Missile 1')
    INSERT INTO ALIEN_MISSILE(missile_id, missile_name, descr) VALUES (2, 'M-2', 'Missile 2')
    INSERT INTO ALIEN_MISSILE(missile_id, missile_name, descr) VALUES (3, 'M-3', 'Missile 3')
    INSERT INTO ALIEN_MISSILE(missile_id, missile_name, descr) VALUES (4, 'M-4', 'Missile 4')
    
    
    INSERT INTO ALIEN_CAPABILITY(spcid, missile_id) VALUES (1, 1)
    INSERT INTO ALIEN_CAPABILITY(spcid, missile_id) VALUES (1, 2)
    INSERT INTO ALIEN_CAPABILITY(spcid, missile_id) VALUES (1, 3)
    
    INSERT INTO ALIEN_CAPABILITY(spcid, missile_id) VALUES (2, 1)
    INSERT INTO ALIEN_CAPABILITY(spcid, missile_id) VALUES (2, 4)
    
    
    INSERT INTO ANTI_MISSILE(antim_id, anitm_name, anitm_descr) VALUES (1, 'AM-1', 'Anti-missile 1')
    INSERT INTO ANTI_MISSILE(antim_id, anitm_name, anitm_descr) VALUES (2, 'AM-2', 'Anti-missile 2')
    INSERT INTO ANTI_MISSILE(antim_id, anitm_name, anitm_descr) VALUES (3, 'AM-3', 'Anti-missile 3')
    INSERT INTO ANTI_MISSILE(antim_id, anitm_name, anitm_descr) VALUES (4, 'AM-4', 'Anti-missile 4')
    
    
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (1, 1)
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (1, 2)
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (1, 3)
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (1, 4)
    
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (2, 1)
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (2, 2)
    
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (3, 1)
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (3, 2)
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (3, 3)
    
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (4, 1)
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (4, 2)
    INSERT INTO DEFUSE_CAPABILITY(antim_id, missile_id) VALUES (4, 4)
    
    GO
    

    Regards,

    Goce.

  • DECLARE @spcname varchar(50)

    SET @spcname='Y'

    SELECT b.antim_id,anitm_name

    FROM

    (SELECT a.spcid,a.missile_id,b.spcname

    FROM Alien_CAPABILITY a INNER JOIN SPECIES b

    ON a.spcid=b.spcid

    WHERE spcname=@spcname) a

    INNER JOIN DEFUSE_CAPABILITY b

    ON a.missile_id=b.missile_id INNER JOIN ANTI_MISSILE c

    ON b.antim_id=c.antim_id

    GROUP BY b.antim_id ,anitm_name

    HAVING count(*)=(SELECT count(*) FROM Alien_CAPABILITY a INNER JOIN SPECIES b ON a.spcid=b.spcid WHERE spcname=@spcname)


    Kindest Regards,

    Vasc

  • well ,goce you just seem to enjoy criticism....you might as well answer the question for bishu.....i guess that will help more ...

    I agree that he should have posted this puzzle somewhere else but after pointing that you might as well help him solve that...

  • Okay...goce that was so nice of you to give the whole script...that save much time

    Thanks

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply