SQLServerCentral / Contests! / SQLServerCentral.com / intersting sql puzzle / Latest Posts

RE: intersting sql puzzle

Atif-ullah Sheikh — Sat, 09 Aug 2008 03:43:05 GMT

Ok... here it is...[code]DECLARE @SpName varchar(10)SET @SpName = 'Y'SELECT DISTINCT ANTI_MISSILE.antim_id,ANTI_MISSILE.anitm_name FROM DEFUSE_CAPABILITY INNER JOIN ANTI_MISSILE ON DEFUSE_CAPABILITY.antim_id = ANTI_MISSILE.antim_idWHERE DEFUSE_CAPABILITY.antim_id IN ( SELECT antim_id FROM ( SELECT Count(*) AS xcnt ,antim_id FROM DEFUSE_CAPABILITY WHERE missile_id IN (SELECT missile_id FROM ALIEN_CAPABILITY INNER JOIN SPECIES ON SPECIES.spcid = ALIEN_CAPABILITY.spcid WHERE SPECIES.spcname = @SPname) Group BY antim_id ) C1 WHERE xcnt >= (SELECT Count(*) FROM ALIEN_CAPABILITY INNER JOIN SPECIES ON SPECIES.spcid = ALIEN_CAPABILITY.spcid WHERE SPECIES.spcname = @SPname)) [/code]Atif Sheikh

RE: intersting sql puzzle

terryj30 — Thu, 31 Jul 2008 03:22:34 GMT

does not seem to hard, start with create database create table and so on. hehegood luck

RE: intersting sql puzzle

RBarryYoung — Fri, 07 Mar 2008 17:17:22 GMT

neat.

RE: intersting sql puzzle

Jerome.J.N — Fri, 11 Jan 2008 02:21:50 GMT

Y dont u make this into a sci-fi hollywood movie and the money it generates can be used to out source this kinda problems and solve it. :P

RE: intersting sql puzzle

Jaiprakash M Bankolli — Fri, 04 May 2007 01:06:00 GMT

Post some more information !!

RE: intersting sql puzzle

Amol M Vaidya — Wed, 26 Oct 2005 23:16:00 GMT

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

Thanks

RE: intersting sql puzzle

Amol M Vaidya — Wed, 26 Oct 2005 23:14:00 GMT

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

RE: intersting sql puzzle

Vasc — Fri, 29 Jul 2005 07:47:00 GMT

DECLARE @spcname varchar(50)SET @spcname='Y'

SELECT b.antim_id,anitm_nameFROM(SELECT a.spcid,a.missile_id,b.spcnameFROM Alien_CAPABILITY a INNER JOIN SPECIES b ON a.spcid=b.spcidWHERE 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_idGROUP BY b.antim_id ,anitm_nameHAVING count(*)=(SELECT count(*) FROM Alien_CAPABILITY a INNER JOIN SPECIES b ON a.spcid=b.spcid WHERE spcname=@spcname)

RE: intersting sql puzzle

Goce Smilevski — Thu, 16 Jun 2005 04:47:00 GMT

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))GOCREATE TABLE ALIEN_MISSILE(  missile_id INTEGER primary key,  missile_name VARCHAR(100),  descr VARCHAR(400))GOCREATE 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))GOCREATE TABLE ANTI_MISSILE(  antim_id INTEGER primary key,  anitm_name VARCHAR(100),  anitm_descr VARCHAR(400))GOCREATE 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))GOSET NOCOUNT ONINSERT 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.

RE: intersting sql puzzle

Rani Shoura — Thu, 16 Jun 2005 04:28:00 GMT

hey can u post the create script

RE: intersting sql puzzle

sara karasik — Wed, 18 May 2005 13:39:00 GMT

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.

RE: intersting sql puzzle

CELKO — Thu, 12 May 2005 17:09:00 GMT

Look up relational division. Fix the DDL.

RE: intersting sql puzzle

Goce Smilevski — Thu, 12 May 2005 01:43:00 GMT

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.

RE: intersting sql puzzle

bishu thomas — Thu, 12 May 2005 01:29:00 GMT

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

RE: intersting sql puzzle

Goce Smilevski — Thu, 12 May 2005 01:23:00 GMT

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.

RE: intersting sql puzzle

bishu thomas — Thu, 12 May 2005 00:18:00 GMT

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

RE: intersting sql puzzle

bishu thomas — Wed, 11 May 2005 23:46:00 GMT

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

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

RE: intersting sql puzzle

Russell Shilling — Wed, 11 May 2005 10:52:00 GMT

Why do I think that this is a homework problem?

intersting sql puzzle

bishu thomas — Tue, 10 May 2005 07:57:00 GMT

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

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.