SQL PUZZLE | The NO Infant Puzzle | SQL Interview Question

03 Tuesday Apr 2018

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

≈ 1 Comment

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

SQL SERVER | Advantages of Truncate table command over Delete? | SQL Interview Question

03 Tuesday Apr 2018

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Concepts, SQL Performance Tuning, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment

Tags

SQL SERVER | Advantages of Truncate table command over delete? | SQL Interview Question

In this post we shall understand the advantages of Truncate table command over delete command with the new changes happened in SQL Server 2016. We shall also discuss the permission to needed to perform the Truncate table command.

A Truncate command removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

Syntax for SQL – Truncate a Table

--

TRUNCATE TABLE DatabaseName.Schema_Name.TableName;
GO

--

Syntax for SQL – B. Truncate Table Partitions | SQL Server 2016 and further versions

--

TRUNCATE TABLE PartitionedTableName WITH (PARTITIONS (1,2,3));  
GO  

--

Note – The above command can be used with SQL Server 2016 and further versions.

Read more from-

https://msbiskills.com/2018/01/11/new-t-sql-features-in-sql-server-2016-viii-truncate-table-with-partitions/

TRUNCATE TABLE advantages-(Microsoft)

1. Less transaction log space is used.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

2. Fewer locks are typically used.
When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

3. Without exception, zero pages are left in the table.
After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

What Permission you need to perform the Truncate Table command

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

Reference

https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql

Reference Reading-

https://msbiskills.com/2010/08/08/delete-and-truncate-differences/
https://msbiskills.com/tag/is-truncate-ddl-or-dml-command/
https://msbiskills.com/tag/truncate-temporal-table/

Thank You.