Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Twin Condition Puzzle | SQL Server Interview Question

In the puzzle you have to find records where records have same length what the length records A has. In below data record A has length 5. So we have to first find the length of record A and then we have to find records with the same length what A has. In this case we need record D as the output. Please check the sample input and the expected output.

Notes –
1. We do not need A in the output.
2. The limitation is that we can only use a single SELECT for the puzzle.

Sample Input

Id Name Length
1 A 5
2 B 3
3 C 4
4 D 5

Expected Output

Id Name Length
1 D 5

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE FindRecordsWithALength
(
	 Id INT
	,[Name] VARCHAR(10)
	,[Length] INT
)
GO

INSERT INTO FindRecordsWithALength VALUES
(1,'A',5),
(2,'B',3),
(3,'C',4),
(4,'D',5)
GO

SELECT * FROM FindRecordsWithALength
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1 | This solution was given by my friend ASHUTOSH

--


SELECT b.* FROM FindRecordsWithALength a
INNER JOIN FindRecordsWithALength b  ON a.Name = 'A'
AND a.Length = b.Length AND a.Id != b.Id
GO


--

Output-1

--                            

Id          Name       Length
----------- ---------- -----------
4           D          5

(1 row affected)

--

Add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements