intersting sql puzzle

Question

Post reply

intersting sql puzzle

bishu thomas

SSC-Addicted

Points: 490
More actions
May 10, 2005 at 7:57 am

#164429

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.

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

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

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 1

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.

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 2

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.

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 3

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

bishu thomas SSC-Addicted Points: 490 More actions · Answer 4

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

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

bishu thomas SSC-Addicted Points: 490 More actions · Answer 5

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 |

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

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 6

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.

bishu thomas SSC-Addicted Points: 490 More actions · Answer 7

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

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 8

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.

sara karasik SSCommitted Points: 1573 More actions · Answer 9

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.

Rani Shoura SSC Veteran Points: 219 More actions · Answer 10

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

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 11

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.

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 12

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

Amol M Vaidya Valued Member Points: 61 More actions · Answer 13

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

Amol M Vaidya Valued Member Points: 61 More actions · Answer 14

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

Thanks