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