Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


intersting sql puzzle


intersting sql puzzle

Author
Message
Goce Smilevski
Goce Smilevski
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 46
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
Vasc
SSC-Addicted
SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)

Group: General Forum Members
Points: 469 Visits: 376

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
Amol M Vaidya
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 1

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
Amol M Vaidya
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 1

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

Thanks


Jaiprakash M Bankolli
Jaiprakash M Bankolli
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 13
Post some more information !!


Kindest Regards,
Jaiprakash M Bankolli
My Blog
Suggestions for me
Jerome.J.N
Jerome.J.N
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 106
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. Tongue
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9436 Visits: 9517
neat.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
terryj30
terryj30
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1120 Visits: 261
does not seem to hard, start with create database create table and so on. hehe
good luck
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3280 Visits: 5158
Ok... here it is...


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_id
WHERE 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
   Wink C1
   WHERE xcnt >= (SELECT Count(*) FROM ALIEN_CAPABILITY INNER JOIN SPECIES ON SPECIES.spcid = ALIEN_CAPABILITY.spcid WHERE SPECIES.spcname = @SPname)
)





Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here


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