Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

intersting sql puzzle Expand / Collapse
Author
Message
Posted Tuesday, May 10, 2005 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 13, 2005 12:01 AM
Points: 4, 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.

Post #181068
Posted Wednesday, May 11, 2005 10:52 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Saturday, October 18, 2014 8:19 AM
Points: 761, Visits: 134
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

Post #181538
Posted Wednesday, May 11, 2005 11:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 13, 2005 12:01 AM
Points: 4, Visits: 1

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

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

Post #181696
Posted Thursday, May 12, 2005 12:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 13, 2005 12:01 AM
Points: 4, 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 |
+----------+------------+

 

Post #181701
Posted Thursday, May 12, 2005 1:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 5:30 PM
Points: 246, 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.




Post #181709
Posted Thursday, May 12, 2005 1:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 13, 2005 12:01 AM
Points: 4, 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.....

Post #181710
Posted Thursday, May 12, 2005 1:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 5:30 PM
Points: 246, 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.




Post #181716
Posted Thursday, May 12, 2005 5:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:21 AM
Points: 1,945, Visits: 3,062

Look up relational division.  Fix the DDL. 

 



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #182075
Posted Wednesday, May 18, 2005 1:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:57 AM
Points: 133, Visits: 89

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.

Post #183495
Posted Thursday, June 16, 2005 4:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 20, 2008 10:35 PM
Points: 35, 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
Post #191201
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse