SQL puzzle

Question

Post reply

SQL puzzle

Sandeep-248595

SSC Enthusiast

Points: 166
More actions
April 14, 2007 at 3:51 am

#118178

Hi,
Can anyone solve this puzzle ?
Two tables Emp1 and Emp2

Emp1                     Emp2
101                       101
102                       102
103                       103
104                        105

The final result should be
104
105
Good LUCK!!!
--Sandeep

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110192 More actions · Answer 1

I can.

And have solved this kind of tasks many times.

Is it your homework?

So, you have to do it.

_____________
Code for TallyGenerator

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 2

There are at least three SET-BASED methods you can use

1) FULL JOIN

2) UNION ALL with GROUP BY

3) UNION ALL with LEFT JOIN

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 3

-- Prepare sample data

DECLARE @Emp1 TABLE (Emp INT)

INSERT @Emp1

SELECT 101 UNION ALL

SELECT 102 UNION ALL

SELECT 103 UNION ALL

SELECT 104

DECLARE @Emp2 TABLE (Emp INT)

INSERT @Emp2

SELECT 101 UNION ALL

SELECT 102 UNION ALL

SELECT 103 UNION ALL

SELECT 105

-- Show the expected output 1

SELECT COALESCE(e1.Emp, e2.Emp)

FROM @Emp1 AS e1

FULL JOIN @Emp2 AS e2 ON e2.Emp = e1.Emp

WHERE e1.Emp IS NULL

OR e2.Emp IS NULL

-- Show the expected output 2

SELECT Emp

FROM (

SELECT Emp

FROM @Emp1

UNION ALL

SELECT Emp

FROM @Emp2

) AS x

GROUP BY Emp

HAVING COUNT(*) = 1

-- Show the expected output 3

SELECT e1.Emp

FROM @Emp1 AS e1

LEFT JOIN @Emp2 AS e2 ON e2.Emp = e1.Emp

WHERE e2.Emp IS NULL

UNION ALL

SELECT e2.Emp

FROM @Emp2 AS e2

LEFT JOIN @Emp1 AS e1 ON e1.Emp = e2.Emp

WHERE e1.Emp IS NULL

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Did you just do someone's homework, Peter? Hope you get an "A"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 5

Hey, you know me...

At least I didn't write

sp_msforeachdb 'drop database ''?''' or similar.

N 56°04'39.16"
E 12°55'05.25"

Sergiy SSC Guru Points: 110192 More actions · Answer 6

He does not need your help on it.

Such a "brilliant" SQL developer will kill all projects without anyone's help.

_____________
Code for TallyGenerator

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 7

His professor will notice that the code is not written by him.

It is out of his style, if he needs to question for help.

I know some of my occasional students cheat, just by reading their suggestions to the problem I give them.

N 56°04'39.16"
E 12°55'05.25"

Sergiy SSC Guru Points: 110192 More actions · Answer 8

So, whom did you help?

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Oooohhh.... now that's just plain evil Hadn't thought about doing something that nasty

As for professors catching code "out of a student's style"?... According to the level of idiocy of I've experienced from people interviewing for SQL jobs, most instructors aren't catching things like this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 10

Since the only requirement posted was the output, this should do it.

select 104 union all select 105 order by 1

Good LUCK!!!

Sandeep-248595 SSC Enthusiast Points: 166 More actions · Answer 11

Hi Peter and Micheal,

Thank you very much for your extended help .

as for "SERIGY" and "JEFF MODEN" .....i am a SQL amateur and just thought of a possibility. This ain't a homework for me !!

Also i suppose that this forum is for helping others and not to challenge anyone's capabilty. You never know what "someone" can become.

If you people got those guts....u participate in SQL contests and compete with those above you and not those who are amateur.

At the same time sorry for being candid but i felt really bad looking at your replies !!!

Sergiy SSC Guru Points: 110192 More actions · Answer 12

Sandeep, if it was not your homework you could explain what do you need it for.

It does not look like real life task, it's more like exercise from a schoolbook.

_____________
Code for TallyGenerator

Muhammad Abbas-388404 Valued Member Points: 70 More actions · Answer 13

Select * from emp1 full outer join emp2 on emp1.empno=emp2.empno

where emp1.empno is null or emp2.empno is null

CodeDigger Mr or Mrs. 500 Points: 506 More actions · Answer 14

Use the ANSI standard EXCEPT operator, which gives you the difference between two sets. It also exists in SQL Server 2005, and in Oracle, it is called "MINUS".