May 10, 2005 at 7:57 am
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.
May 11, 2005 at 4:13 am
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.
May 11, 2005 at 4:24 am
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.
May 11, 2005 at 10:52 am
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
May 11, 2005 at 11:46 pm
Its just a puzzle....i stuck up ....
just curious to know the answer........any help ?
May 12, 2005 at 12:18 am
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 |
+----------+------------+
May 12, 2005 at 1:23 am
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.
May 12, 2005 at 1:29 am
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.....
May 12, 2005 at 1:43 am
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.
May 18, 2005 at 1:39 pm
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.
June 16, 2005 at 4:28 am
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
June 16, 2005 at 4:47 am
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.
July 29, 2005 at 7:47 am
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)
Vasc
October 26, 2005 at 11:14 pm
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...
October 26, 2005 at 11:16 pm
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