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 Thursday, June 16, 2005 4:47 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 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.



Post #191204
Posted Friday, July 29, 2005 7:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 469, Visits: 321

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
Post #205666
Posted Wednesday, October 26, 2005 11:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 3, 2007 7:55 AM
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...

Post #232777
Posted Wednesday, October 26, 2005 11:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 3, 2007 7:55 AM
Points: 39, Visits: 1

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

Thanks

Post #232778
Posted Friday, May 4, 2007 1:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 9:25 AM
Points: 29, Visits: 13
Post some more information !!


Kindest Regards,
Jaiprakash M Bankolli
My Blog
Suggestions for me
Post #363227
Posted Friday, January 11, 2008 2:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:29 AM
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. :P
Post #441627
Posted Friday, March 7, 2008 5:17 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
neat.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #466225
Posted Thursday, July 31, 2008 3:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 29, 2013 1:32 AM
Points: 1,118, Visits: 261
does not seem to hard, start with create database create table and so on. hehe
good luck
Post #544195
Posted Saturday, August 9, 2008 3:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 3,244, Visits: 5,008
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
) 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

Post #549736
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse