Tags

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


SQL PUZZLE | The NO Infant Puzzle | SQL Interview Question

In this puzzle you have to check whether you have any child for each parent . If any of the child is of age = 0 then we need to show 0. If all the children for any parent are greater than 0 then show 1 as the new column called Ots Output.Please check out sample input and expected output.

Sample Input

Parent Table

Id Nm
1 Pawan
2 Avtaar

Children Table

Id PId Age Nm
1 1 5 Avika
2 1 0 Avika1
3 2 3

Expected output

Id Nm Ots
1 Pawan 0
2 Avtaar 1

Script – DDL and INSERT sample data

--

CREATE TABLE Parent
(
	 Id INT
	,Nm VARCHAR(10)
)
GO

INSERT INTO Parent
SELECT 1,'Pawan'
UNION ALL
SELECT 2,'Avtaar'
GO

CREATE TABLE Children
(
	 Id INT
    ,PId INT
	,Age INT
	,Nm VARCHAR(10)
)
GO

INSERT INTO Children
SELECT 1,1,5,'Avika' UNION ALL
SELECT 2,1,0,'Avika1' UNION ALL
SELECT 3,2,3,''
GO

SELECT * FROM Children
GO

SELECT * FROM Parent
GO

--

SOLUTION 1

--

SELECT p.Id,p.Nm,CASE WHEN MIN(n) = 0 THEN 0 ELSE 1 END Ots 
FROM
(
	SELECT * , Age*ISNULL(LAG(Age) OVER (PARTITION BY c.PId ORDER BY c.Id),1) n
	FROM children c  
)x INNER JOIN parent p on p.id = x.PId
GROUP BY p.Id,p.Nm
ORDER BY P.Id

--

OUTPUT – 1

--

Id          Nm         Ots
----------- ---------- -----------
1           Pawan      0
2           Avtaar     1

(2 rows affected)

--

SOLUTION – 2

--

SELECT 
p.ID , 
p.nm , 
CASE WHEN (
       CASE WHEN MIN(abs(c.age)) = 0 THEN 0 ELSE
       EXP(SUM(Log(abs(nullif(c.age,0))))) * 
		round(0.5-count(nullif(sign(sign(c.age)+0.5),1))%2,0) END ) = 0 THEN 0 
       ELSE 1  
END  
FROM parent p inner join children c on p.id = c.PId GROUP BY p.id,p.nm

--

OUTPUT – 2

--

Id          Nm         Ots
----------- ---------- -----------
1           Pawan      0
2           Avtaar     1

(2 rows affected)

--

SOLUTION -3

--

select p.Id,p.Nm, IIF(COUNT(*) = SUM(CASE WHEN c.Age > 0 THEN 1 ELSE 0 END) ,1,0) Ots
from Parent p 
INNER JOIN children c ON c.pid = p.id 
GROUP BY p.Id,p.Nm

--

OUTPUT – 3

--

Id          Nm         Ots
----------- ---------- -----------
1           Pawan      0
2           Avtaar     1

(2 rows affected)

--

SOLUTION -4

--

SELECT *
FROM Parent p
OUTER APPLY 
(
	SELECT IIF(COUNT(*) = SUM(CASE WHEN c.Age > 0 THEN 1 ELSE 0 END),1,0) Ots
	FROM Children c 
	WHERE p.id = c.PId
)z

--

OUTPUT – 4

--

Id          Nm         Ots
----------- ---------- -----------
1           Pawan      0
2           Avtaar     1

(2 rows affected)

--

SOLUTION -5

--

select p.*, CASE WHEN EXISTS 
				(SELECT NULL from children c where c.pid = p.id and c.age = 0)
             THEN 0 ELSE 1 END AS Ots
from Parent p

--

OUTPUT – 5

--

Id          Nm         Ots
----------- ---------- -----------
1           Pawan      0
2           Avtaar     1

(2 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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