• Home
  • SQL Server
    • Articles
    • T-SQL Puzzles
    • Output Puzzles
    • Interview Questions
    • Performance Tuning
    • SQL SERVER On Linux
    • Resources
  • SSRS
    • SSRS Articles
    • Interview Questions
  • SSAS
    • SSAS Articles
    • DAX
  • SQL Puzzles
  • Interview Questions
    • SQL Interview Questions
    • Data Interview Questions
  • New Features(SQL SERVER)
    • SQL SERVER 2017
    • SQL SERVER 2016
    • SQL SERVER On Linux
  • Social
    • Expert Exchange
      • Top Expert in SQL
      • Yearly Award
      • Certifications
      • Achievement List
      • Top Expert of the Week
    • HackerRank (SQL)
    • StackOverflow
    • About Me
      • Contact Me
      • Blog Rules

Improving my SQL BI Skills

Improving my SQL BI Skills

Tag Archives: SQL Puzzle | 2nd Highest Salary in Each Department using OffSet & Fetch Method

SQL Puzzle | Number to call Puzzle

25 Thursday Jan 2018

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, any recommended websites for sql puzzles, Best SQL Puzzles, Buy SQL Server Interview Questions Book Online at Low Price, Calculating nth Max salary, Check Prime Number puzzle, Circular prime number puzzle, Circular Prime Number Puzzlre, COmples tSQL puzzles, Complex SQL Challenges, complex sql statement(puzzle), Complex tsql, Complex TSQL Challenge, convert string to proper case in sql server, Divide rows into two columns, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Even Odd Count Puzzle, Excellent t-SQL puzzles, Find nth highest salary, Find Nth maximum value in SQL Server, find records not ending with s, find records not ending with s puzzle, Finding prime numbers with SQL server, First letter upper case for each word in sql server, Free Download SQL SERVER Interview questions, Generate possible combinations puzzle, Get Next Value Puzzle, Get nth highest and lowest salary of an employee, Getting group by sum and total sum in a single query, Group by numbers that are in sequence, group sum total in one query, Huge blank areas in database field, implementation and administration, Improve SQL Skills, Interesting Interview Questions, Interview Puzzles in SQL Server, Interview Qs.SQL SERVER Questions, Interview questions and Answers for MS SQL Server designing, Interview Questions and Answers For SQL, Interview questions on Joins, Interview Questions on SQL, Interview SQL Puzzles, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Microsoft SQL Server interview questions for DBA, MS SQL Server interview questions, MSBI Interview Questions, Multiple Column Pivoting Puzzle, Multiplication of Digits Puzzle, Multiply digits of a number, multiply digits of a number puzzle, nth maximum & minimum salary in a single sql query, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, Prime numbers after given number puzzle, Prime numbers using SQL, Program to Print prime numbers, Puzzle SQL, puzzle sql developer, Puzzle SQl Server, Puzzles, PUzzles in SQL SERVER, Queries for SQL Interview, Records not ending with a character Puzzle, Records not ending with s Puzzle, Remove Huge Multiple Spaces from a DB Fields, Remove Multiple Spaces from a DB Fields, Replace string puzzle, SELECT Puzzle, Separate Int and char from a string column, Single Query multiple conditions puzzle, Single Quote update SQL, Smart Replace puzzle, Split & Count Puzzle, Split & count puzzle in SQL, Split and Count puzzle SQL Server, SQL, sql - How to find third or nth maximum salary from salary table?, SQL - The Pattern Puzzles, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Extreme Difficult Puzzle, SQL FAQ, SQL FAQs, sql group by only rows which are in sequence, SQL Interview Q & A, SQL Interview Questions, SQL Interview Questions - Part 2, SQL Interview Questions for SQL Professionals‎, SQL Joins, SQL Pattern Puzzles, SQL pl/sql puzzles, SQL prime number puzzle, SQL puzzle, SQL Puzzle | 2nd Highest Salary in Each Department using OffSet & Fetch Method, SQL Puzzle | Find Different Values Puzzle, SQL Puzzle | Get Weeks (Start Date - End Date) for a Month & Year, SQL Puzzle | How to get Date Part & Time Part only from DateTime in Sql Server [Multiple Methods], SQL Puzzle | Multiplication of Digits Puzzle, SQL Puzzle | Star Pattern Puzzle ( Fun with SQL ), SQL Puzzle | The Fiscal Puzzle - II, SQL Puzzle | The Middle 4 Puzzle, SQL Puzzle | The Missing Gaps Puzzle [Multiple Solutions], SQL Puzzle | The Time Difference Puzzle, SQL Puzzles, sql puzzles & answers, sql puzzles and answers free download, sql puzzles and answers pdf, sql puzzles for interview, sql puzzles oracle, SQL Queries, SQL Queries asked in interviews, SQL QUERY PUZZLES, SQL Query to Find Nth Highest Salary of Employee, SQL Questions, SQL Quiz, SQL Replace Puzzle, SQL Server - Common Interview Questions and Answers, SQL SERVER - Find Nth Highest Salary of Employee, SQL Server - General Interview Questions and Answers, SQL SERVER - Group sum, sql server - How to find the nth highest salary in SQL?, sql server 2008 interview questions, SQL Server 2008 Interview Questions and Answers, SQL Server Database, SQL Server database developer interview questions and answers, sql server dba interview questions, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Developer T-SQL Interview Questions, SQL server filter index with LIKE and RIGHT function?, SQL SERVER Indexes, SQL Server Interview Puzzle, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview Questions - Part 1, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL Server Interview Questions And Answers.pdf, sql server interview questions by Pawan Khowal sql interview questions, SQL SERVER Interview questions pdf, SQL Server Interview Questions | MSBISKILLS.Com, SQL Server Interview Questions | MSBISKILLS.com Top 50 SQL Server Questions & Answers, SQL Server Interview Questions/Answers Part-1, SQL Server Puzzle, SQL SERVER Puzzles, SQL server Questions and Answers, SQL SERVER Tips, SQL Skills, SQL Sudoku, SQL Tips & Tricks, SQL Tips and Tricks, SQL Top clause, SQL Tough Puzzle, SQL Tricks, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLBI Interview Questions, SQLSERVER, Star Pattern Puzzle, Sum of digits puzzle, Sum of digits SQL puzzle, SUM of grouped COUNT in SQL Query, T SQL Puzzles, T-SQL Challenge, T-SQL Interview Questions | SQL Server Interviews and Jobs, T-SQL Puzzle, T-SQL Query | [ Replace 6 Consecutive Digits with x from a string Puzzle ], T-SQL Query | [ The Complex Week Puzzle ], T-SQL Server Interview Questions, T-SQL Tricky Puzzles, The % percentage puzzle, The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The In Puzzle, The InNotIn Puzzle, The Middle 4 Puzzle, The Middle 4 SQL Puzzle, The Multiple Sums Puzzle, The new rows count puzzle, The Prime Number Puzzle, The Replace Puzzle, The Status Puzzle, The Time Difference Puzzle, The Tree Puzzle, The Update Puzzle, Top 10 Frequently Asked SQL Query Interview Questions, TOP 100 SQL SERVER INTERVIEW QUESTIONS QUERIES, Top 50 SQL Server Interview Question for Testers, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzle, TSQL Puzzles, TSQL Queries, Update one table and Insert in another table using single T-SQL statement at same time, Week puzzle, [Solved] sql query for nth highest & lowest salary


SQL Puzzle | Number to call Puzzle

In this puzzle you have to Generate a new column called Nums(Number to call).

The conditions to generate the new column (Nums) are-

1. If the EmpMobileNumber is not null then take the value from the EmpMobileNumber and append ‘EmpMobileNumber’ to the data.
2. If the EmpResidenceNumber is not null then take the value from the EmpResidenceNumber and append ‘EmpResidenceNumber’ to the data.
3. If both EmpMobileNumber and EmpResidenceNumber are NOT NULL then take the value from the EmpMobileNumber and append ‘EmpMobileNumber’ to the data.
4. If both EmpMobileNumber and EmpResidenceNumber are NULL then we should get NULL as output.
5. The challenge is to do this without USING CASE Statement.

For more details please check the sample input and expected output.

Sample Input

EmpId EmpName EmpMobileNumber EmpResidenceNumber
1 Avtaar 9873686533 NULL
2 Kishan NULL 9873686513
3 Chander 9873682233 9873680233
4 Sharlee 9873111133 7873682233
5 Ishu NULL 9873686598
6 Ram 9873761211 NULL
7 Vaibhav NULL NULL
8 Mayank NULL 8761231231

Expected Output

EmpId EmpName Nums
1 Avtaar 9873686533 MobileNumber
2 Kishan 9873686513 ResidenceNumber
3 Chander 9873682233 MobileNumber
4 Sharlee 9873111133 MobileNumber
5 Ishu 9873686598 ResidenceNumber
6 Ram 9873761211 MobileNumber
7 Vaibhav NULL
8 Mayank 8761231231 ResidenceNumber

Script

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

--

CREATE TABLE EmployeeNumber
(
	 EmpId INT 
	,EmpName VARCHAR(10)
	,EmpMobileNumber VARCHAR(100)
	,EmpResidenceNumber VARCHAR(100)
)
GO

INSERT INTO EmployeeNumber VALUES
(1,'Avtaar','9873686533',NULL),
(2,'Kishan',NULL,'9873686513'),
(3,'Chander','9873682233','9873680233'),
(4,'Sharlee','9873111133','7873682233'),
(5,'Ishu',NULL,'9873686598'),
(6,'Ram','9873761211',NULL),
(7,'Vaibhav',NULL,NULL),
(8,'Mayank',NULL,'8761231231')
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 | WITHOUT CASE Statement

--

SELECT EmpId , EmpName
	, ISNULL ( EmpMobileNumber + ' MobileNumber' , EmpResidenceNumber + ' ResidenceNumber' ) Nums
FROM EmployeeNumber


--

Output-1 | WITHOUT CASE Statement

--                            

EmpId       EmpName    Nums                                
----------- ---------- ----------------------------------- 
1           Avtaar     9873686533 MobileNumber             
2           Kishan     9873686513 ResidenceNumber          
3           Chander    9873682233 MobileNumber             
4           Sharlee    9873111133 MobileNumber             
5           Ishu       9873686598 ResidenceNumber          
6           Ram        9873761211 MobileNumber             
7           Vaibhav    NULL                                
8           Mayank     8761231231 ResidenceNumber          
                                                           
(8 rows affected)                                          
                                                           
                                  
                                
--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MsbiSkills.com

Share this

  • Facebook
  • Twitter
  • WhatsApp
  • Email
  • LinkedIn
  • Skype
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram

SQL Puzzle | The String Puzzle [Split Puzzle]

25 Friday Nov 2016

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 2 Comments

Tags

Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, any recommended websites for sql puzzles, Best SQL Puzzles, Buy SQL Server Interview Questions Book Online at Low Price, Calculating nth Max salary, Check Prime Number puzzle, Circular prime number puzzle, Circular Prime Number Puzzlre, COmples tSQL puzzles, Complex SQL Challenges, complex sql statement(puzzle), Complex tsql, Complex TSQL Challenge, convert string to proper case in sql server, Divide rows into two columns, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Even Odd Count Puzzle, Excellent t-SQL puzzles, Find nth highest salary, Find Nth maximum value in SQL Server, find records not ending with s, find records not ending with s puzzle, Finding prime numbers with SQL server, First letter upper case for each word in sql server, Free Download SQL SERVER Interview questions, Generate possible combinations puzzle, Get Duplicate Rows from Multiple Columns, Get nth highest and lowest salary of an employee, Getting group by sum and total sum in a single query, Group by numbers that are in sequence, group sum total in one query, implementation and administration, Improve SQL Skills, Interesting Interview Questions, Interview Puzzles in SQL Server, Interview Qs.SQL SERVER Questions, Interview questions and Answers for MS SQL Server designing, Interview Questions and Answers For SQL, Interview questions on Joins, Interview Questions on SQL, Interview SQL Puzzles, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Microsoft SQL Server interview questions for DBA, MS SQL Server interview questions, MSBI Interview Questions, Multi Column Pivot Puzzle, Multiple Column Pivoting Puzzle, Multiplication of Digits Puzzle, Multiply digits of a number, multiply digits of a number puzzle, nth maximum & minimum salary in a single sql query, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, Prime numbers after given number puzzle, Prime numbers using SQL, Program to Print prime numbers, Puzzle SQL, puzzle sql developer, Puzzle SQl Server, Puzzles, PUzzles in SQL SERVER, Queries for SQL Interview, Records not ending with a character Puzzle, Records not ending with s Puzzle, Replace string puzzle, SELECT Puzzle, Single Query multiple conditions puzzle, Smart Replace puzzle, Split & Count Puzzle, Split & count puzzle in SQL, Split and Count puzzle SQL Server, Split Full name into multiple name, SQL, sql - How to find third or nth maximum salary from salary table?, SQL - The Pattern Puzzles, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Extreme Difficult Puzzle, SQL FAQ, SQL FAQs, sql group by only rows which are in sequence, SQL Interview Q & A, SQL Interview Questions, SQL Interview Questions - Part 2, SQL Interview Questions for SQL Professionals‎, SQL Joins, SQL Pattern Puzzles, SQL pl/sql puzzles, SQL prime number puzzle, SQL puzzle, SQL Puzzle | 2nd Highest Salary in Each Department using OffSet & Fetch Method, SQL Puzzle | Find Different Values Puzzle, SQL Puzzle | Get Weeks (Start Date - End Date) for a Month & Year, SQL Puzzle | How to get Date Part & Time Part only from DateTime in Sql Server [Multiple Methods], SQL Puzzle | Multiplication of Digits Puzzle, SQL Puzzle | Star Pattern Puzzle ( Fun with SQL ), SQL Puzzle | The Fiscal Puzzle - II, SQL Puzzle | The Middle 4 Puzzle, SQL Puzzle | The Missing Gaps Puzzle [Multiple Solutions], SQL Puzzle | The Time Difference Puzzle, SQL Puzzle | The Updated By Puzzle - I, SQL Puzzles, sql puzzles & answers, sql puzzles and answers free download, sql puzzles and answers pdf, sql puzzles for interview, sql puzzles oracle, SQL Queries, SQL Queries asked in interviews, SQL QUERY PUZZLES, SQL Query to Find Nth Highest Salary of Employee, SQL Questions, SQL Quiz, SQL Replace Puzzle, SQL Server - Common Interview Questions and Answers, SQL SERVER - Find Nth Highest Salary of Employee, SQL Server - General Interview Questions and Answers, SQL SERVER - Group sum, sql server - How to find the nth highest salary in SQL?, sql server 2008 interview questions, SQL Server 2008 Interview Questions and Answers, SQL Server Database, SQL Server database developer interview questions and answers, sql server dba interview questions, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Developer T-SQL Interview Questions, SQL server filter index with LIKE and RIGHT function?, SQL SERVER Indexes, SQL Server Interview Puzzle, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview Questions - Part 1, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL Server Interview Questions And Answers.pdf, sql server interview questions by Pawan Khowal sql interview questions, SQL SERVER Interview questions pdf, SQL Server Interview Questions | MSBISKILLS.Com, SQL Server Interview Questions | MSBISKILLS.com Top 50 SQL Server Questions & Answers, SQL Server Interview Questions/Answers Part-1, SQL Server Puzzle, SQL SERVER Puzzles, SQL server Questions and Answers, SQL SERVER Tips, SQL Skills, SQL Sudoku, SQL Tips & Tricks, SQL Tips and Tricks, SQL Top clause, SQL Tough Puzzle, SQL Tricks, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLBI Interview Questions, SQLSERVER, Star Pattern Puzzle, Sum of digits puzzle, Sum of digits SQL puzzle, SUM of grouped COUNT in SQL Query, T SQL Puzzles, T-SQL Challenge, T-SQL Interview Questions | SQL Server Interviews and Jobs, T-SQL Puzzle, T-SQL Query | [ Replace 6 Consecutive Digits with x from a string Puzzle ], T-SQL Query | [ The Complex Week Puzzle ], T-SQL Server Interview Questions, T-SQL Tricky Puzzles, The % percentage puzzle, The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The In Puzzle, The InNotIn Puzzle, The Middle 4 Puzzle, The Middle 4 SQL Puzzle, The Multiple Sums Puzzle, The new rows count puzzle, The Phone Call Puzzle, The Prime Number Puzzle, The Replace Puzzle, The String Puzzle [Split Puzzle], The Time Difference Puzzle, The Tree Puzzle, The Update Puzzle, Top 10 Frequently Asked SQL Query Interview Questions, TOP 100 SQL SERVER INTERVIEW QUESTIONS QUERIES, Top 50 SQL Server Interview Question for Testers, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzle, TSQL Puzzles, TSQL Queries, Update one table and Insert in another table using single T-SQL statement at same time, Week puzzle, Write a SQL Query to Calculate the longest period ( in days ) that the company has gone without hiring or firing any one, [Solved] sql query for nth highest & lowest salary


SQL Puzzle | The String Puzzle [Split Puzzle]

Write a T-SQL to fetch first Name, Initial, Last Name and Suffix from Full Name Column.
Please check out the sample input and the expected output.

Sample Inputs

EmployeeID FullName
1 Nancy Davolio
2 Andrew W Fuller Jr
3 Janet Leverling
4 Margaret Peacock
5 Steven Buchanan
6 Michael J Suyama Jr
7 Robert T King III
8 Laura Callahan
9 Anne Dodsworth

Expected Output

EmployeeID FullName FirstName Initial LastName Suffix
1 Nancy Davolio Nancy   Davolio  
2 Andrew W Fuller Jr Andrew W Fuller Jr
3 Janet Leverling Janet   Leverling  
4 Margaret Peacock Margaret   Peacock  
5 Steven Buchanan Steven   Buchanan  
6 Michael J Suyama Jr Michael J Suyama r
7 Robert T King III Robert T King III
8 Laura Callahan Laura   Callahan  
9 Anne Dodsworth Anne   Dodsworth

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE [dbo].[EmployeesTest](
	[EmployeeID] [int] NOT NULL,
	[FullName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (1, N'Nancy Davolio')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (2, N'Andrew W Fuller Jr')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (3, N'Janet Leverling')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (4, N'Margaret Peacock')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (5, N'Steven Buchanan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (6, N'Michael J Suyama Jr')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (7, N'Robert T King III')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (8, N'Laura Callahan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (9, N'Anne Dodsworth')
GO

--

—

SOLUTION – 1


--

SELECT EmployeeID,FullName, FirstName,Initial , REPLACE(LName,CASE WHEN rt > 0 
THEN SUBSTRING(LName,rt,LEN(LName)) ELSE '' END,'') LastName, 
CASE WHEN rt > 0 THEN SUBSTRING(LName,rt,LEN(LName)) ELSE '' END Suffix FROM 
(
	SELECT *, RTRIM(LTRIM(REPLACE(LastName,Initial,''))) LName , 
                  CHARINDEX(N' ', RTRIM(LTRIM(REPLACE(LastName,Initial,''))),0) rt
	FROM 
	(
		SELECT *, SUBSTRING(RTRIM(LTRIM(LastName)) , 0, CHARINDEX(N' ',RTRIM(LTRIM(LastName)),1) ) Initial 
		FROM 
		(
			SELECT *, SUBSTRING(FullName , 0, CHARINDEX(N' ',FullName,1) ) FirstName				
					, SUBSTRING(FullName , CHARINDEX(N' ',FullName,1) , DATALENGTH(FullName) ) LastName
			FROM [EmployeesTest]
		)l
	)n
)b

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Share this

  • Facebook
  • Twitter
  • WhatsApp
  • Email
  • LinkedIn
  • Skype
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram

SQL Puzzle | The Updated By Puzzle – I

24 Thursday Nov 2016

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, any recommended websites for sql puzzles, Best SQL Puzzles, Buy SQL Server Interview Questions Book Online at Low Price, Calculating nth Max salary, Check Prime Number puzzle, Circular prime number puzzle, Circular Prime Number Puzzlre, COmples tSQL puzzles, Complex SQL Challenges, complex sql statement(puzzle), Complex tsql, Complex TSQL Challenge, convert string to proper case in sql server, Divide rows into two columns, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Even Odd Count Puzzle, Excellent t-SQL puzzles, Find nth highest salary, Find Nth maximum value in SQL Server, find records not ending with s, find records not ending with s puzzle, Finding prime numbers with SQL server, First letter upper case for each word in sql server, Free Download SQL SERVER Interview questions, Generate possible combinations puzzle, Get Duplicate Rows from Multiple Columns, Get nth highest and lowest salary of an employee, Getting group by sum and total sum in a single query, Group by numbers that are in sequence, group sum total in one query, implementation and administration, Improve SQL Skills, Interesting Interview Questions, Interview Puzzles in SQL Server, Interview Qs.SQL SERVER Questions, Interview questions and Answers for MS SQL Server designing, Interview Questions and Answers For SQL, Interview questions on Joins, Interview Questions on SQL, Interview SQL Puzzles, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Microsoft SQL Server interview questions for DBA, MS SQL Server interview questions, MSBI Interview Questions, Multi Column Pivot Puzzle, Multiple Column Pivoting Puzzle, Multiplication of Digits Puzzle, Multiply digits of a number, multiply digits of a number puzzle, nth maximum & minimum salary in a single sql query, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, Prime numbers after given number puzzle, Prime numbers using SQL, Program to Print prime numbers, Puzzle SQL, puzzle sql developer, Puzzle SQl Server, Puzzles, PUzzles in SQL SERVER, Queries for SQL Interview, Records not ending with a character Puzzle, Records not ending with s Puzzle, Replace string puzzle, SELECT Puzzle, Single Query multiple conditions puzzle, Smart Replace puzzle, Split & Count Puzzle, Split & count puzzle in SQL, Split and Count puzzle SQL Server, SQL, sql - How to find third or nth maximum salary from salary table?, SQL - The Pattern Puzzles, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Extreme Difficult Puzzle, SQL FAQ, SQL FAQs, sql group by only rows which are in sequence, SQL Interview Q & A, SQL Interview Questions, SQL Interview Questions - Part 2, SQL Interview Questions for SQL Professionals‎, SQL Joins, SQL Pattern Puzzles, SQL pl/sql puzzles, SQL prime number puzzle, SQL puzzle, SQL Puzzle | 2nd Highest Salary in Each Department using OffSet & Fetch Method, SQL Puzzle | Find Different Values Puzzle, SQL Puzzle | Get Weeks (Start Date - End Date) for a Month & Year, SQL Puzzle | How to get Date Part & Time Part only from DateTime in Sql Server [Multiple Methods], SQL Puzzle | Multiplication of Digits Puzzle, SQL Puzzle | Star Pattern Puzzle ( Fun with SQL ), SQL Puzzle | The Fiscal Puzzle - II, SQL Puzzle | The Middle 4 Puzzle, SQL Puzzle | The Missing Gaps Puzzle [Multiple Solutions], SQL Puzzle | The Time Difference Puzzle, SQL Puzzle | The Updated By Puzzle - I, SQL Puzzles, sql puzzles & answers, sql puzzles and answers free download, sql puzzles and answers pdf, sql puzzles for interview, sql puzzles oracle, SQL Queries, SQL Queries asked in interviews, SQL QUERY PUZZLES, SQL Query to Find Nth Highest Salary of Employee, SQL Questions, SQL Quiz, SQL Replace Puzzle, SQL Server - Common Interview Questions and Answers, SQL SERVER - Find Nth Highest Salary of Employee, SQL Server - General Interview Questions and Answers, SQL SERVER - Group sum, sql server - How to find the nth highest salary in SQL?, sql server 2008 interview questions, SQL Server 2008 Interview Questions and Answers, SQL Server Database, SQL Server database developer interview questions and answers, sql server dba interview questions, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Developer T-SQL Interview Questions, SQL server filter index with LIKE and RIGHT function?, SQL SERVER Indexes, SQL Server Interview Puzzle, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview Questions - Part 1, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL Server Interview Questions And Answers.pdf, sql server interview questions by Pawan Khowal sql interview questions, SQL SERVER Interview questions pdf, SQL Server Interview Questions | MSBISKILLS.Com, SQL Server Interview Questions | MSBISKILLS.com Top 50 SQL Server Questions & Answers, SQL Server Interview Questions/Answers Part-1, SQL Server Puzzle, SQL SERVER Puzzles, SQL server Questions and Answers, SQL SERVER Tips, SQL Skills, SQL Sudoku, SQL Tips & Tricks, SQL Tips and Tricks, SQL Top clause, SQL Tough Puzzle, SQL Tricks, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLBI Interview Questions, SQLSERVER, Star Pattern Puzzle, Sum of digits puzzle, Sum of digits SQL puzzle, SUM of grouped COUNT in SQL Query, T SQL Puzzles, T-SQL Challenge, T-SQL Interview Questions | SQL Server Interviews and Jobs, T-SQL Puzzle, T-SQL Query | [ Replace 6 Consecutive Digits with x from a string Puzzle ], T-SQL Query | [ The Complex Week Puzzle ], T-SQL Server Interview Questions, T-SQL Tricky Puzzles, The % percentage puzzle, The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The In Puzzle, The InNotIn Puzzle, The Middle 4 Puzzle, The Middle 4 SQL Puzzle, The Multiple Sums Puzzle, The new rows count puzzle, The Phone Call Puzzle, The Prime Number Puzzle, The Replace Puzzle, The Time Difference Puzzle, The Tree Puzzle, The Update Puzzle, Top 10 Frequently Asked SQL Query Interview Questions, TOP 100 SQL SERVER INTERVIEW QUESTIONS QUERIES, Top 50 SQL Server Interview Question for Testers, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzle, TSQL Puzzles, TSQL Queries, Update one table and Insert in another table using single T-SQL statement at same time, Week puzzle, Write a SQL Query to Calculate the longest period ( in days ) that the company has gone without hiring or firing any one, [Solved] sql query for nth highest & lowest salary


SQL Puzzle | The Updated By Puzzle – I

This puzzle is a variation in puzzle – https://msbiskills.com/2016/11/24/sql-puzzle-the-updated-by-puzzle/

Write a T-SQL query that produces the expected output. We have to show who has updated the name and when it was updated
Please check out the sample input and the expected output.

Sample Inputs

ID NAME DOB UPDATED_BY UPDATE_DT
1 SAM 01/01/1981 MIKE 10/01/2006
1 SAMUEL 01/01/1982 JAMES 02/13/2007
1 SAMIEL 01/01/1982 JACOB 05/21/2010
2 DAVE 10/20/1978 SHARON 08/15/2015

Expected Output

Id NAME DOB UpdatedBy UpdatedDt UpdatedBy UpdatedDt UpdatedBy UpdatedDt
1 SAM 01/01/1981 MIKE 10/01/2006 JACOB 05/21/2010 JAMES 02/13/2007
2 DAVE 10/20/1978 SHARON 08/15/2015      

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--


;WITH CTE
AS
(SELECT *
FROM (
VALUES 
   (1,'SAM','01/01/1981','MIKE','10/01/2006')
   ,(1,'SAMUEL','01/01/1982','JAMES','02/13/2007')
   ,(1,'SAMIEL','01/01/1982','JACOB','05/21/2010')
   ,(2,'DAVE','10/20/1978','SHARON','08/15/2015')
 ) AS A(ID,NAME,DOB,UPDATED_BY,UPDATE_DT)
)
SELECT * FROM CTE

--

—

SOLUTION – 1


--

;WITH CTE
AS
(SELECT *
FROM (
VALUES 
   (1,'SAM','01/01/1981','MIKE','10/01/2006')
   ,(1,'SAMUEL','01/01/1982','JAMES','02/13/2007')
   ,(1,'SAMIEL','01/01/1982','JACOB','05/21/2010')
   ,(2,'DAVE','10/20/1978','SHARON','08/15/2015')
 ) AS A(ID,NAME,DOB,UPDATED_BY,UPDATE_DT)
)
,CTE2 AS
(	
	SELECT * , CONCAT('By',rnk) c1 , CONCAT('Dt',rnk) c2 FROM 
	(
		SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID,DOB) rnk FROM CTE
	)p 
)
SELECT Id, MIN(NAME) NAME, MIN(DOB) DOB , ISNULL(MAX([By1]),'') UpdatedBy 
,ISNULL(MAX([Dt1]),'') UpdatedDt,ISNULL(MAX([By2]),'') UpdatedBy ,ISNULL(MAX([Dt2]),'')UpdatedDt,ISNULL(MAX([By3]),'') UpdatedBy
,ISNULL(MAX([Dt3]),'') UpdatedDt FROM CTE2
PIVOT ( MAX(UPDATED_BY) FOR c1 IN ([By1],[By2],[By3]) )y1
PIVOT ( MAX(UPDATE_DT) FOR c2 IN ([Dt1],[Dt2],[Dt3]) )y2
GROUP BY Id

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Share this

  • Facebook
  • Twitter
  • WhatsApp
  • Email
  • LinkedIn
  • Skype
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram

SQL Puzzle | The Updated By Puzzle

24 Thursday Nov 2016

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 1 Comment

Tags

Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, any recommended websites for sql puzzles, Best SQL Puzzles, Buy SQL Server Interview Questions Book Online at Low Price, Calculating nth Max salary, Check Prime Number puzzle, Circular prime number puzzle, Circular Prime Number Puzzlre, COmples tSQL puzzles, Complex SQL Challenges, complex sql statement(puzzle), Complex tsql, Complex TSQL Challenge, convert string to proper case in sql server, Divide rows into two columns, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Even Odd Count Puzzle, Excellent t-SQL puzzles, Find nth highest salary, Find Nth maximum value in SQL Server, find records not ending with s, find records not ending with s puzzle, Finding prime numbers with SQL server, First letter upper case for each word in sql server, Free Download SQL SERVER Interview questions, Generate possible combinations puzzle, Get Duplicate Rows from Multiple Columns, Get nth highest and lowest salary of an employee, Getting group by sum and total sum in a single query, Group by numbers that are in sequence, group sum total in one query, implementation and administration, Improve SQL Skills, Interesting Interview Questions, Interview Puzzles in SQL Server, Interview Qs.SQL SERVER Questions, Interview questions and Answers for MS SQL Server designing, Interview Questions and Answers For SQL, Interview questions on Joins, Interview Questions on SQL, Interview SQL Puzzles, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Microsoft SQL Server interview questions for DBA, MS SQL Server interview questions, MSBI Interview Questions, Multiple Column Pivoting Puzzle, Multiplication of Digits Puzzle, Multiply digits of a number, multiply digits of a number puzzle, nth maximum & minimum salary in a single sql query, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, Prime numbers after given number puzzle, Prime numbers using SQL, Program to Print prime numbers, Puzzle SQL, puzzle sql developer, Puzzle SQl Server, Puzzles, PUzzles in SQL SERVER, Queries for SQL Interview, Records not ending with a character Puzzle, Records not ending with s Puzzle, Replace string puzzle, SELECT Puzzle, Single Query multiple conditions puzzle, Smart Replace puzzle, Split & Count Puzzle, Split & count puzzle in SQL, Split and Count puzzle SQL Server, SQL, sql - How to find third or nth maximum salary from salary table?, SQL - The Pattern Puzzles, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Extreme Difficult Puzzle, SQL FAQ, SQL FAQs, sql group by only rows which are in sequence, SQL Interview Q & A, SQL Interview Questions, SQL Interview Questions - Part 2, SQL Interview Questions for SQL Professionals‎, SQL Joins, SQL Pattern Puzzles, SQL pl/sql puzzles, SQL prime number puzzle, SQL puzzle, SQL Puzzle | 2nd Highest Salary in Each Department using OffSet & Fetch Method, SQL Puzzle | Find Different Values Puzzle, SQL Puzzle | Get Weeks (Start Date - End Date) for a Month & Year, SQL Puzzle | How to get Date Part & Time Part only from DateTime in Sql Server [Multiple Methods], SQL Puzzle | Multiplication of Digits Puzzle, SQL Puzzle | Star Pattern Puzzle ( Fun with SQL ), SQL Puzzle | The Fiscal Puzzle - II, SQL Puzzle | The Middle 4 Puzzle, SQL Puzzle | The Missing Gaps Puzzle [Multiple Solutions], SQL Puzzle | The Time Difference Puzzle, SQL Puzzles, sql puzzles & answers, sql puzzles and answers free download, sql puzzles and answers pdf, sql puzzles for interview, sql puzzles oracle, SQL Queries, SQL Queries asked in interviews, SQL QUERY PUZZLES, SQL Query to Find Nth Highest Salary of Employee, SQL Questions, SQL Quiz, SQL Replace Puzzle, SQL Server - Common Interview Questions and Answers, SQL SERVER - Find Nth Highest Salary of Employee, SQL Server - General Interview Questions and Answers, SQL SERVER - Group sum, sql server - How to find the nth highest salary in SQL?, sql server 2008 interview questions, SQL Server 2008 Interview Questions and Answers, SQL Server Database, SQL Server database developer interview questions and answers, sql server dba interview questions, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Developer T-SQL Interview Questions, SQL server filter index with LIKE and RIGHT function?, SQL SERVER Indexes, SQL Server Interview Puzzle, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview Questions - Part 1, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL Server Interview Questions And Answers.pdf, sql server interview questions by Pawan Khowal sql interview questions, SQL SERVER Interview questions pdf, SQL Server Interview Questions | MSBISKILLS.Com, SQL Server Interview Questions | MSBISKILLS.com Top 50 SQL Server Questions & Answers, SQL Server Interview Questions/Answers Part-1, SQL Server Puzzle, SQL SERVER Puzzles, SQL server Questions and Answers, SQL SERVER Tips, SQL Skills, SQL Sudoku, SQL Tips & Tricks, SQL Tips and Tricks, SQL Top clause, SQL Tough Puzzle, SQL Tricks, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLBI Interview Questions, SQLSERVER, Star Pattern Puzzle, Sum of digits puzzle, Sum of digits SQL puzzle, SUM of grouped COUNT in SQL Query, T SQL Puzzles, T-SQL Challenge, T-SQL Interview Questions | SQL Server Interviews and Jobs, T-SQL Puzzle, T-SQL Query | [ Replace 6 Consecutive Digits with x from a string Puzzle ], T-SQL Query | [ The Complex Week Puzzle ], T-SQL Server Interview Questions, T-SQL Tricky Puzzles, The % percentage puzzle, The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The In Puzzle, The InNotIn Puzzle, The Middle 4 Puzzle, The Middle 4 SQL Puzzle, The Multiple Sums Puzzle, The new rows count puzzle, The Phone Call Puzzle, The Prime Number Puzzle, The Replace Puzzle, The Time Difference Puzzle, The Tree Puzzle, The Update Puzzle, Top 10 Frequently Asked SQL Query Interview Questions, TOP 100 SQL SERVER INTERVIEW QUESTIONS QUERIES, Top 50 SQL Server Interview Question for Testers, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzle, TSQL Puzzles, TSQL Queries, Update one table and Insert in another table using single T-SQL statement at same time, Week puzzle, Write a SQL Query to Calculate the longest period ( in days ) that the company has gone without hiring or firing any one, [Solved] sql query for nth highest & lowest salary


SQL Puzzle | The Updated By Puzzle

Write a T-SQL query that produces the expected output. We have to show who has updated the name and when it was updated.

Please check out the sample input and the expected output.

Sample Inputs

ID NAME DOB UPDATED_BY UPDATE_DT
1 SAM 01/01/1981 MIKE 10/01/2006
1 SAMUEL 01/01/1982 JAMES 02/13/2007
1 SAMIEL 01/01/1982 JACOB 05/21/2010
2 DAVE 10/20/1978 SHARON 08/15/2015

Expected Output

ID NAME DOB UPDATED_BY UPDATE_DT NAME DOB
1 SAM 01/01/1981 JACOB 05/21/2010 SAMIEL 01/01/1982
1 SAMIEL 01/01/1982 JAMES 02/13/2007 SAMUEL 01/01/1982
1 SAMUEL 01/01/1982        
2 DAVE 10/20/1978      

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--


;WITH CTE
AS
(SELECT *
FROM (
VALUES 
   (1,'SAM','01/01/1981','MIKE','10/01/2006')
   ,(1,'SAMUEL','01/01/1982','JAMES','02/13/2007')
   ,(1,'SAMIEL','01/01/1982','JACOB','05/21/2010')
   ,(2,'DAVE','10/20/1978','SHARON','08/15/2015')
 ) AS A(ID,NAME,DOB,UPDATED_BY,UPDATE_DT)
)
SELECT * FROM CTE

--

—

SOLUTION – 1


--

;WITH CTE
AS
(SELECT * , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID,DOB) rnk1
FROM (
VALUES 
   (1,'SAM','01/01/1981','MIKE','10/01/2006')
   ,(1,'SAMUEL','01/01/1982','JAMES','02/13/2007')
   ,(1,'SAMIEL','01/01/1982','JACOB','05/21/2010')
   ,(2,'DAVE','10/20/1978','SHARON','08/15/2015')
 ) AS A(ID,NAME,DOB,UPDATED_BY,UPDATE_DT)
)
,CTE2 AS
(	
	SELECT ID,NAME,DOB,UPDATE_DT,rnk FROM 
	(
		SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID,DOB) rnk FROM CTE
	)p 
)
SELECT a.ID, a.NAME , a.DOB , ISNULL(y.UPDATED_BY,'') UPDATED_BY , ISNULL(y.UPDATE_DT,'') UPDATE_DT , ISNULL(y.NAME,'') NAME, 
ISNULL(y.DOB,'') DOB FROM CTE2 a
OUTER APPLY
(
	SELECT TOP 1 * FROM CTE b
	WHERE a.Id = b.Id AND b.rnk1 > a.rnk
	ORDER BY b.rnk1
)y


--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Share this

  • Facebook
  • Twitter
  • WhatsApp
  • Email
  • LinkedIn
  • Skype
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram

SQL Puzzle | Get Duplicate Rows from Multiple Columns

23 Wednesday Nov 2016

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 1 Comment

Tags

Advanced SQL Interview Questions and Answers, Advanced SQL tutorial pdf, any recommended websites for sql puzzles, Best SQL Puzzles, Buy SQL Server Interview Questions Book Online at Low Price, Calculating nth Max salary, Check Prime Number puzzle, Circular prime number puzzle, Circular Prime Number Puzzlre, COmples tSQL puzzles, Complex SQL Challenges, complex sql statement(puzzle), Complex tsql, Complex TSQL Challenge, convert string to proper case in sql server, Divide rows into two columns, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Even Odd Count Puzzle, Excellent t-SQL puzzles, Find nth highest salary, Find Nth maximum value in SQL Server, find records not ending with s, find records not ending with s puzzle, Finding prime numbers with SQL server, First letter upper case for each word in sql server, Free Download SQL SERVER Interview questions, Generate possible combinations puzzle, Get Duplicate Rows from Multiple Columns, Get nth highest and lowest salary of an employee, Getting group by sum and total sum in a single query, Group by numbers that are in sequence, group sum total in one query, implementation and administration, Improve SQL Skills, Interesting Interview Questions, Interview Puzzles in SQL Server, Interview Qs.SQL SERVER Questions, Interview questions and Answers for MS SQL Server designing, Interview Questions and Answers For SQL, Interview questions on Joins, Interview Questions on SQL, Interview SQL Puzzles, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Microsoft SQL Server interview questions for DBA, MS SQL Server interview questions, MSBI Interview Questions, Multiple Column Pivoting Puzzle, Multiplication of Digits Puzzle, Multiply digits of a number, multiply digits of a number puzzle, nth maximum & minimum salary in a single sql query, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, Prime numbers after given number puzzle, Prime numbers using SQL, Program to Print prime numbers, Puzzle SQL, puzzle sql developer, Puzzle SQl Server, Puzzles, PUzzles in SQL SERVER, Queries for SQL Interview, Records not ending with a character Puzzle, Records not ending with s Puzzle, Replace string puzzle, SELECT Puzzle, Single Query multiple conditions puzzle, Smart Replace puzzle, Split & Count Puzzle, Split & count puzzle in SQL, Split and Count puzzle SQL Server, SQL, sql - How to find third or nth maximum salary from salary table?, SQL - The Pattern Puzzles, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Extreme Difficult Puzzle, SQL FAQ, SQL FAQs, sql group by only rows which are in sequence, SQL Interview Q & A, SQL Interview Questions, SQL Interview Questions - Part 2, SQL Interview Questions for SQL Professionals‎, SQL Joins, SQL Pattern Puzzles, SQL pl/sql puzzles, SQL prime number puzzle, SQL puzzle, SQL Puzzle | 2nd Highest Salary in Each Department using OffSet & Fetch Method, SQL Puzzle | Find Different Values Puzzle, SQL Puzzle | Get Weeks (Start Date - End Date) for a Month & Year, SQL Puzzle | How to get Date Part & Time Part only from DateTime in Sql Server [Multiple Methods], SQL Puzzle | Multiplication of Digits Puzzle, SQL Puzzle | Star Pattern Puzzle ( Fun with SQL ), SQL Puzzle | The Fiscal Puzzle - II, SQL Puzzle | The Middle 4 Puzzle, SQL Puzzle | The Missing Gaps Puzzle [Multiple Solutions], SQL Puzzle | The Time Difference Puzzle, SQL Puzzles, sql puzzles & answers, sql puzzles and answers free download, sql puzzles and answers pdf, sql puzzles for interview, sql puzzles oracle, SQL Queries, SQL Queries asked in interviews, SQL QUERY PUZZLES, SQL Query to Find Nth Highest Salary of Employee, SQL Questions, SQL Quiz, SQL Replace Puzzle, SQL Server - Common Interview Questions and Answers, SQL SERVER - Find Nth Highest Salary of Employee, SQL Server - General Interview Questions and Answers, SQL SERVER - Group sum, sql server - How to find the nth highest salary in SQL?, sql server 2008 interview questions, SQL Server 2008 Interview Questions and Answers, SQL Server Database, SQL Server database developer interview questions and answers, sql server dba interview questions, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL Server Developer T-SQL Interview Questions, SQL server filter index with LIKE and RIGHT function?, SQL SERVER Indexes, SQL Server Interview Puzzle, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview Questions - Part 1, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL Server Interview Questions And Answers.pdf, sql server interview questions by Pawan Khowal sql interview questions, SQL SERVER Interview questions pdf, SQL Server Interview Questions | MSBISKILLS.Com, SQL Server Interview Questions | MSBISKILLS.com Top 50 SQL Server Questions & Answers, SQL Server Interview Questions/Answers Part-1, SQL Server Puzzle, SQL SERVER Puzzles, SQL server Questions and Answers, SQL SERVER Tips, SQL Skills, SQL Sudoku, SQL Tips & Tricks, SQL Tips and Tricks, SQL Top clause, SQL Tough Puzzle, SQL Tricks, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLBI Interview Questions, SQLSERVER, Star Pattern Puzzle, Sum of digits puzzle, Sum of digits SQL puzzle, SUM of grouped COUNT in SQL Query, T SQL Puzzles, T-SQL Challenge, T-SQL Interview Questions | SQL Server Interviews and Jobs, T-SQL Puzzle, T-SQL Query | [ Replace 6 Consecutive Digits with x from a string Puzzle ], T-SQL Query | [ The Complex Week Puzzle ], T-SQL Server Interview Questions, T-SQL Tricky Puzzles, The % percentage puzzle, The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The In Puzzle, The InNotIn Puzzle, The Middle 4 Puzzle, The Middle 4 SQL Puzzle, The Multiple Sums Puzzle, The new rows count puzzle, The Phone Call Puzzle, The Prime Number Puzzle, The Replace Puzzle, The Time Difference Puzzle, The Tree Puzzle, The Update Puzzle, Top 10 Frequently Asked SQL Query Interview Questions, TOP 100 SQL SERVER INTERVIEW QUESTIONS QUERIES, Top 50 SQL Server Interview Question for Testers, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Puzzle, TSQL Puzzles, TSQL Queries, Update one table and Insert in another table using single T-SQL statement at same time, Week puzzle, Write a SQL Query to Calculate the longest period ( in days ) that the company has gone without hiring or firing any one, [Solved] sql query for nth highest & lowest salary


SQL Puzzle | Get Duplicate Rows from Multiple Columns

Write a T-SQL query that fetches duplicate Rows from Multiple Columns like col1,col2,col3,col4

Please check out the sample input and the expected output.

Sample Inputs

ID col1 col2 col3 col4
1 a b c d
2 a b c d
3 a b c d
4 g h i j

Expected Output

Id col1 col2 col3 col4
1 a b c d
2 a b c d
3 a b c d

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--


CREATE TABLE testdupli
(
	ID Int
	,col1 varchar(1)
	,col2 varchar(1)
	,col3 varchar(1)
	,col4 varchar(1)
)
GO

INSERT INTO testdupli VALUES
(1 ,'a', 'b' ,'c', 'd'),
(2 ,'a', 'b' ,'c', 'd'),
(3 ,'a', 'b' ,'c', 'd'),
(4 ,'g', 'h' ,'i', 'j')


--

—

SOLUTION – 1


--

SELECT Id,col1,col2,col3,col4
FROM
(
	SELECT *, COUNT(*) OVER (PARTITION BY col1,col2,col3,col4) rnk
	FROM testdupli
)b
WHERE b.rnk > 1

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Share this

  • Facebook
  • Twitter
  • WhatsApp
  • Email
  • LinkedIn
  • Skype
  • Reddit
  • Tumblr
  • Pinterest
  • Pocket
  • Telegram
← Older posts

Blog Stats

  • 737,261 hits

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,113 other followers

Pawan Khowal

502 SQL Puzzles with answers

Achievement - 500 PuzzlesJuly 18th, 2018
The big day is here. Finally presented 500+ puzzles for SQL community.

200 SQL Server Puzzle with Answers

The Big DayAugust 19th, 2016
The big day is here. Completed 200 SQL Puzzles today

Archives

December 2019
M T W T F S S
« Sep    
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Top Articles

  • pawankkmr.files.wordpress…
  • pawankkmr.files.wordpress…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.files.wordpress…
  • pawankkmr.wordpress.com/2…

Archives

  • September 2018 (2)
  • August 2018 (6)
  • July 2018 (25)
  • June 2018 (22)
  • May 2018 (24)
  • April 2018 (33)
  • March 2018 (35)
  • February 2018 (53)
  • January 2018 (48)
  • December 2017 (32)
  • November 2017 (2)
  • October 2017 (20)
  • August 2017 (8)
  • June 2017 (2)
  • March 2017 (1)
  • February 2017 (18)
  • January 2017 (2)
  • December 2016 (5)
  • November 2016 (23)
  • October 2016 (2)
  • September 2016 (14)
  • August 2016 (6)
  • July 2016 (22)
  • June 2016 (27)
  • May 2016 (15)
  • April 2016 (7)
  • March 2016 (5)
  • February 2016 (7)
  • December 2015 (4)
  • October 2015 (23)
  • September 2015 (31)
  • August 2015 (14)
  • July 2015 (16)
  • June 2015 (29)
  • May 2015 (25)
  • April 2015 (44)
  • March 2015 (47)
  • November 2012 (1)
  • July 2012 (8)
  • September 2010 (26)
  • August 2010 (125)
  • July 2010 (2)

Article Categories

  • Analysis Services (6)
    • DAX (6)
  • Data (2)
    • Data warehousing (2)
  • Integration Services (2)
  • Magazines (3)
  • Reporting Services (4)
  • SQL SERVER (820)
    • Download SQL Interview Q's (212)
    • SQL Concepts (323)
    • SQL Performance Tuning (155)
    • SQL Puzzles (331)
    • SQL SERVER 2017 Linux (6)
    • SQL Server Interview Questions (308)
    • SQL SERVER Puzzles (332)
    • T SQL Puzzles (547)
    • Tricky SQL Queries (439)
  • UI (30)
    • ASP.NET (5)
    • C# (13)
    • CSS (9)
    • OOPS (3)
  • Uncategorized (5)

Recent Posts

  • Microsoft SQL Server 2019 | Features added to SQL Server on Linux September 26, 2018
  • SQL Puzzle | The Map Puzzle September 26, 2018
  • SQL OUTPUT PUZZLE | The Equal to puzzle August 17, 2018
  • SQL Puzzle | How to alter a column from null to not null August 14, 2018
  • Advanced SQL | Fill the Price for missing months August 13, 2018
  • SQL Puzzle | Multiple ways to add Date and Time to a DATETIME | SQL Interview Question August 10, 2018
  • SQL Puzzle | The Grouping Puzzle – A Single SELECT | SQL Interview Question August 7, 2018
  • SQL Puzzle | The Compare * 100 Puzzle | SQL Interview Question August 1, 2018
  • SQL Server Interview Questions & Answers – SET 22(10 Questions & Answers) July 30, 2018
  • SQL OUTPUT PUZZLE | The JOINs – How well do you know them ? | SQL Interview Question[Updated Answers] July 28, 2018
  • SQL OUTPUT PUZZLE | The IN & NOT IN – How well do you know them ? | SQL Interview Question July 28, 2018
  • SQL Puzzle | Count total occurrence of a character in all columns | SQL Interview Question July 28, 2018
  • SQL Puzzle | Ranking Puzzle – 1 | SQL Interview Question July 27, 2018
  • SQL Server on LINUX | Performance best practices and configuration guidelines July 26, 2018
  • SQL Server 2017 on LINUX FAQs – II – Microsoft July 25, 2018
  • SQL Puzzle | The Hierarchy Spacing Puzzle | SQL Interview Question July 22, 2018
  • SQL Server Question | Can we declare a variable/ define a column as INTEGER or DEC? July 21, 2018
  • Updated SQL Puzzle | The INNER JOIN – Two Rows PUZZLE | Solution without UNION/UNION All July 20, 2018
  • SQL Puzzle | The NOT in Range Puzzle – SINGLE SELECT | SQL Interview Question July 20, 2018
  • SQL Puzzle | The INNER JOIN – Two Rows PUZZLE | SQL Interview Question July 20, 2018
  • SQL Server | Have you ever gave length in the Float data type declaration? | SQL Interview Question July 19, 2018
  • SQL Puzzle | Remove trailing zeros puzzle – The DOUBLE PRECISION/FLOAT(53) data type | Advanced SQL July 19, 2018
  • SQL Puzzle | The SINGLE INSERT Puzzle / The Double INTO Puzzle | Advanced SQL July 18, 2018
  • SQL Puzzle | The JOIN Puzzle – A Single SELECT | SQL Interview Question July 18, 2018
  • SQL Server 2017 on LINUX | Install sqlcmd and bcp the SQL Server command-line tools on Ubuntu July 17, 2018
  • SQL Server 2017 on LINUX | Installing SQL Server on Ubuntu July 17, 2018
  • SQL Server 2017 on LINUX | How to install SQL Operations Studio(SOS) on LINUX(Ubuntu) July 17, 2018
  • SQL Puzzle | Multiple ways to convert datetime to date with 00:00:00.000 Time | SQL Interview Question July 16, 2018
  • SQL Puzzle | Have you ever used SELECT TOP 0 ? – Multiple ways to create a new table from existing | SQL Interview Question July 12, 2018
  • SQL Server 2017 on LINUX FAQs – Microsoft July 11, 2018

Create a website or blog at WordPress.com

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.