SQL Puzzle | Multiple ways to add Date and Time to a DATETIME | SQL Interview Question

10 Friday Aug 2018

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

≈ Leave a comment

Tags

SQL Puzzle | Multiple ways to add Date and Time to a DATETIME | SQL Interview Question

This was asked to one of my developer by a Client in a technical interview so I thought I must share it with you guys. In this puzzle you have 2 variables one is DATE and other is TIME and you have to add both to get a DATETIME. Please check out the sample input and the expected output.

Sample Input

Date	Time
2018-08-10	07:48:46.4566667

Expected Output

DateTime
2018-08-10 07:48:46.457

Script – DDL and INSERT Sample Data

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'

--

–SOLUTION 1

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT CAST(@Dt AS DATETIME) + CAST(@Tm AS DATETIME) [DateTime]
GO

--

OUTPUT – 1

--

DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

–SOLUTION 2

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT 
	DATEADD(MILLISECOND, DATEDIFF(MILLISECOND, '00:00:00', @Tm), CONVERT(DATETIME, @Dt))
	[DateTime]
GO

--

OUTPUT – 2

--

DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

–SOLUTION 3

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT DATEADD(DAY,DATEDIFF(DAY, 0, @Dt),CAST(@Tm AS DATETIME))
	 [DateTime]
GO

--

OUTPUT – 3

--

DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row affected)


--

–SOLUTION 4

--

DECLARE @Dt DATE = '2018-08-10' , @Tm Time = '07:48:46.4566667'
SELECT CAST(CONCAT(@Dt,' ',CAST(@Tm AS VARCHAR(12))) AS DATETIME)
	[DateTime]
GO

--

OUTPUT – 4

--


DateTime
-----------------------
2018-08-10 07:48:46.457

(1 row 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 Performance 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 Puzzle | The Grouping Puzzle – A Single SELECT | SQL Interview Question

07 Tuesday Aug 2018

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

≈ 2 Comments

Tags

SQL Puzzle | The Grouping Puzzle – A Single SELECT | SQL Interview Question

In this puzzle you have to have find the sum of sales for each employee. The catch here is that for inactive employees(IsActive=0) group the sum under Inactive name and rest under their respective names. Another catch is that you need to do this in a single select.Please check out the sample input and the expected output.

Sample Input

EmpName	SaleDate	SaleAmount	IsActive
Pawan	2018-08-07 10:18:28.663	2500.00	1
Pawan	2018-08-07 10:18:28.663	3000.00	1
Avtaar	2018-08-07 10:18:28.663	800.00	1
Avtaar	2018-08-07 10:18:28.663	1000.00	1
Kishan	2018-08-07 10:18:28.663	2800.00	1
Kishan	2018-08-07 10:18:28.663	3000.00	1
Nimit	2018-08-07 10:18:28.663	500.00	1
Nimit	2018-08-07 10:18:28.663	800.00	1
K	2018-08-07 10:18:28.663	1000.00	0
L	2018-08-07 10:18:28.663	1000.00	0
M	2018-08-07 10:18:28.663	500.00	0
J	2018-08-07 10:18:28.663	2500.00	0

Expected Output

EmpName	SaleAmount
Avtaar	1800.00
Inactive	5000.00
Kishan	5800.00
Nimit	1300.00
Pawan	5500.00

Script – DDL and INSERT Sample Data

--

CREATE TABLE dbo.tbl_sales
(  
    EmpName VARCHAR(256),
    SaleDate DATETIME,
    SaleAmount DECIMAL(18,2),
    IsActive BIT
)
GO

INSERT INTO tbl_sales VALUES 
('Pawan',GETDATE(),2500.00,1),
('Pawan',GETDATE(),3000.00,1),
('Avtaar',GETDATE(),800.00,1),
('Avtaar',GETDATE(),1000.00,1),
('Kishan',GETDATE(),2800.00,1),
('Kishan',GETDATE(),3000.00,1),
('Nimit',GETDATE(),500.00,1),
('Nimit',GETDATE(),800.00,1),
('K',GETDATE(),1000.00,0),
('L',GETDATE(),1000.00,0),
('M',GETDATE(),500.00,0),
('J',GETDATE(),2500.00,0)
GO

SELECT * FROM tbl_sales
GO

--

–SOLUTION 1

--

SELECT DISTINCT IIF(IsActive = 0, 'Inactive',EmpName) EmpName , 
				SUM(SaleAmount) OVER (PARTITION BY  IIF(IsActive = 0, 'Inactive',EmpName))
				[SaleAmount]
FROM tbl_sales
GO

--

OUTPUT – 1

--

EmpName         SaleAmount
--------------- ---------------
Avtaar          1800.00
Inactive        5000.00
Kishan          5800.00
Nimit           1300.00
Pawan           5500.00

(5 rows affected)

--

–SOLUTION 2

--

SELECT IIF(IsActive = 0, 'Inactive',EmpName) EmpName 
	  ,SUM(SaleAmount) [SaleAmount]
FROM tbl_sales
GROUP BY IIF(IsActive = 0, 'Inactive',EmpName)
GO
--

OUTPUT – 2

--

EmpName         SaleAmount
--------------- ---------------
Avtaar          1800.00
Inactive        5000.00
Kishan          5800.00
Nimit           1300.00
Pawan           5500.00

(5 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 Performance 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 Puzzle | The Compare * 100 Puzzle | SQL Interview Question

01 Wednesday Aug 2018

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

≈ 1 Comment

Tags

SQL Puzzle | The Compare * 100 Puzzle | SQL Interview Question

In this puzzle you have to generate a new column called FinalOutput. The logic for the new column is that you have to check if value of Length1 = Length1 * 100 for each value for col1 and col2, If yes then you need to concatenate the small value with the larger value separating by |. If not then show the value directly. Can you do that using a single select?. Please check out the sample input and the expected output.

Sample Input

col1	col2	Length1
A	APD	23
A	APD	2300
A	APD	45
A	APD	78
B	BPD	45
B	BPD	4500
B	BPD	450
NULL	CPD	98
NULL	CPD	9800
NULL	CPD	983

Expected Output

col1	col2	Length1	FinalOutput
A	APD	23	23 \| 2300
A	APD	2300	23 \| 2300
A	APD	45	45
A	APD	78	78
B	BPD	45	45 \| 4500
B	BPD	4500	45 \| 4500
B	BPD	450	450
NULL	CPD	98	98 \| 9800
NULL	CPD	9800	98 \| 9800
NULL	CPD	983	983

Script – DDL and INSERT Sample Data

--

CREATE Table Combine 
(
    col1 varchar(MAX),
    col2 varchar(MAX),
    Length1 float
)
GO

INSERT INTO Combine VALUES
('A','APD',23),
('A','APD',2300),
('A','APD',45),
('A','APD',78),
('B','BPD',45),
('B','BPD',4500),
('B','BPD',450),
('NULL','CPD',98),
('NULL','CPD',9800),
('NULL','CPD',983)
GO

SELECT * FROM Combine
GO

--

–SOLUTION 1 : THE SUM … OVER() WITH IIF

--

SELECT a.*, 
		IIF
		(
			b.Length1 IS NOT NULL,
			CONCAT(IIF(a.Length1>b.Length1,b.Length1,a.Length1),' | ',IIF(a.Length1>b.Length1,a.Length1,b.Length1)),
			CONCAT(a.Length1,'')
		)FinalOutput 
FROM Combine
a LEFT JOIN Combine b ON a.col1 = b.col1 AND a.col2 = b.col2
AND ( a.Length1 * 100 = b.Length1 OR a.Length1 = b.Length1 * 100 )

--

OUTPUT – 1

--

col1       col2         Length1                FinalOutput 
---------- ------------ ---------------------- ----------- 
A          APD          23                     23 | 2300   
A          APD          2300                   23 | 2300   
A          APD          45                     45          
A          APD          78                     78          
B          BPD          45                     45 | 4500   
B          BPD          4500                   45 | 4500   
B          BPD          450                    450         
NULL       CPD          98                     98 | 9800   
NULL       CPD          9800                   98 | 9800   
NULL       CPD          983                    983         
                                                           
(10 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 Performance 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 OUTPUT PUZZLE | The JOINs – How well do you know them ? | SQL Interview Question[Updated Answers]

28 Saturday Jul 2018

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

≈ Leave a comment

Tags

SQL OUTPUT PUZZLE | The Joins – How well do you know them ? | SQL Interview Question

In this puzzle you have to go through all the SQL queries and provide us the output with really executing them at the SSMS.

DATA.

Table1 – FirstTab

ID
1
2
NULL

Table2 – SecondTab

ID
1
3
NULL

Script – DDL and INSERT Sample Data

--

CREATE TABLE FirstTab
(
	 ID INT	
)
GO

INSERT INTO FirstTab VALUES
(1),
(2),
(NULL)
GO

SELECT * FROM FirstTab
GO

CREATE TABLE SecondTab
(
	ID INT	
)
GO

INSERT INTO SecondTab VALUES
(1),
(3),
(NULL)
GO

SELECT * FROM SecondTab
GO


--

Q1. What will be the OUTPUT of the following statement?

--

SELECT COUNT(1) Cnt FROM FirstTab a
INNER JOIN SecondTab b ON a.Id  b.ID
--Note  : INNER JOIN with not equal to join

--

SOLUTION

--


So here the output will be 3 as 1  2 , 32 and 31
Cnt
-----------
3

(1 row affected)

Note - NULLs will not participate here in JOIN.

--

Q2. What will be the OUTPUT of the following statement?

--

SELECT COUNT(1) Cnt FROM FirstTab a
LEFT JOIN SecondTab b ON a.Id  b.ID
--Note  : LEFT JOIN with not equal to join

--

SOLUTION

--

The output will be 4 here.

Cnt
-----------
4

(1 row affected)

Note - 3 rows for the Inner join and 1 NULL remaining from the left table.

--

Q3. What will be the OUTPUT of the following statement?

--

SELECT COUNT(1) Cnt FROM FirstTab a
RIGHT JOIN SecondTab b ON a.Id  b.ID
--Note  : RIGHT JOIN with not equal to join

--

SOLUTION

--


The output will be 4 here.

Cnt
-----------
4

(1 row affected)

Note - 3 rows for the Inner join and 1 NULL remaining from the right table.


--

Q4. What will be the OUTPUT of the following statement?

--

SELECT COUNT(1) Cnt FROM FirstTab a
FULL OUTER JOIN SecondTab b ON a.Id  b.ID
--Note  : FULL OUTER JOIN with not equal to join


--

SOLUTION

--


Cnt
-----------
5

The output will be 5. 3 for the inner join + 1 NULL for the left join and 1 for the right join.

--

Q5. What will be the OUTPUT of the following statement?

--

SELECT COUNT(1) Cnt FROM FirstTab a
CROSS JOIN SecondTab b 
WHERE a.Id  b.ID

--

SOLUTION

--

This is similar as INNER JOIN. So the output will be 3.

--

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 OUTPUT PUZZLE | The IN & NOT IN – How well do you know them ? | SQL Interview Question

28 Saturday Jul 2018

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

≈ Leave a comment

Tags

SQL OUTPUT PUZZLE | The IN & NOT IN Puzzle – How well do you know them ? | SQL Interview Question

In this puzzle you have to go through all the SQL queries and provide us the output with really executing them at the SSMS.

Script – DDL and INSERT Sample Data

--

CREATE TABLE FirstTab
(
	 ID INT
	,[Name] VARCHAR(10)
)
GO

INSERT INTO FirstTab VALUES
(5,'Pawan'),
(6,'Sharlee'),
(7,'Krish'),
(NULL,'Avtaar')
GO

SELECT * FROM FirstTab
GO

CREATE TABLE SecondTab
(
	ID INT	
)
GO

INSERT INTO SecondTab VALUES
(5),
(NULL)
GO

SELECT * FROM SecondTab
GO

--

DATA.

Table1 – FirstTab

ID	Name
5	Pawan
6	Sharlee
7	Krish
NULL	Avtaar

Table2 – SecondTab

ID
5
NULL

Q1. What will be the OUTPUT of the following statement?

--

SELECT COUNT(*) 
FROM FirstTab a WHERE a.Id NOT IN ( SELECT Id FROM SecondTab WHERE Id IS NULL )

--

SOLUTION

--


The output will be 0 here. 
If you have NULL inside the sub query and 
you are using NOT IN you will not get any rows.


--

Q2. What will be the OUTPUT of the following statement?

--

SELECT COUNT(*) 
FROM FirstTab a WHERE a.Id NOT IN ( SELECT Id FROM SecondTab WHERE Id = 5 )

--

SOLUTION

--

Here you will get 2 as the output. 
We are fetch records other than 2. 
So there are only 5 records - 6 and 7. 
NULL will not be considered here as it cannot be compared.

--

Q3. What will be the OUTPUT of the following statement?

--

SELECT COUNT(*) 
FROM FirstTab a WHERE a.Id NOT IN ( SELECT Id FROM SecondTab )

--

SOLUTION

--

The output will be 0 here. 
If you have NULL inside the sub query and 
you are using NOT IN you will not get any rows.

--

Q4. What will be the OUTPUT of the following statement?

--

SELECT COUNT(*) 
FROM FirstTab a WHERE a.Id NOT IN ( SELECT Id FROM SecondTab WHERE Id IS NOT NULL )


--

SOLUTION

--

Here you will get 2 as the output. 
We are fetch records other than 5 as 
NULL has been eliminated using IS NOT NULL condition. 
So there are only 2 records - 6 and 7. 
NULL will not be considered here as it cannot be compared.

--

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

Improving my SQL BI Skills

Tag Archives: Interview Questions on SQL

SQL Puzzle | Multiple ways to add Date and Time to a DATETIME | SQL Interview Question

SQL Puzzle | The Grouping Puzzle – A Single SELECT | SQL Interview Question

SQL Puzzle | The Compare * 100 Puzzle | SQL Interview Question

SQL OUTPUT PUZZLE | The JOINs – How well do you know them ? | SQL Interview Question[Updated Answers]

SQL OUTPUT PUZZLE | The IN & NOT IN – How well do you know them ? | SQL Interview Question

Share this

Share this

Share this

Share this

Share this