SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


intersting sql puzzle


intersting sql puzzle

Author
Message
bishu thomas
bishu thomas
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 1

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.


Russell Shilling
Russell Shilling
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1478 Visits: 209
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
bishu thomas
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 1

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

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


bishu thomas
bishu thomas
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 1

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
Goce Smilevski
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 46
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
bishu thomas
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 1

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
Goce Smilevski
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 46
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
sara karasik
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 95

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
Rani Shoura
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 2
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search