T-SQL Query | [ The Training Puzzle ]

02 Thursday Apr 2015

Posted by Pawan Kumar Khowal in SQL Concepts, SQL SERVER, T SQL Puzzles

Tags

Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

T-SQL Query | [ The Training Puzzle ]

The puzzle is very simple. Here you have to present the week’s (wk) column comma separated and group by training and classroom columns. Please post all the possible solutions. Please check out the sample input and expected output for details.

Sample Input

TrainingID	Training	ClassRoom	StartTime	Duration	Wk
1	SQL Server	Silver-Room	10:00	02:00	M
2	SQL Server	Silver-Room	10:00	02:00	W
3	SQL Server	Silver-Room	10:00	02:00	T
4	SQL Server	Silver-Room	10:00	02:00	F
5	ASP.NET	Cloud-Room	11:00	01:45	F
6	ASP.NET	Cloud-Room	11:00	01:45	M
7	ASP.NET	Cloud-Room	11:00	01:45	TH

Expected output

Training	ClassRoom	StartTime	Duration	Weeks
SQL Server	Silver-Room	10:00	02:00	M ,W ,T ,F
ASP.NET	Cloud-Room	11:00	01:45	F ,M ,TH

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

Script

Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE TC26_TrainingInfo 
(
TrainingID INT IDENTITY PRIMARY KEY,
Training VARCHAR(20),
ClassRoom VARCHAR(20),
StartTime CHAR(5),
Duration CHAR(5),
Wk CHAR(2)
)

GO

INSERT INTO TC26_TrainingInfo (
Training, ClassRoom, StartTime, Duration, Wk )
SELECT 'SQL Server','Silver-Room','10:00','02:00','M' UNION ALL
SELECT 'SQL Server','Silver-Room','10:00','02:00','W' UNION ALL
SELECT 'SQL Server','Silver-Room','10:00','02:00','T' UNION ALL
SELECT 'SQL Server','Silver-Room','10:00','02:00','F' UNION ALL
SELECT 'ASP.NET','Cloud-Room','11:00','01:45','F' UNION ALL
SELECT 'ASP.NET','Cloud-Room','11:00','01:45','M' UNION ALL
SELECT 'ASP.NET','Cloud-Room','11:00','01:45','TH'

--

UPDATE – 11-Apr-2015 – Solution 1


--

SELECT Training,ClassRoom, StartTime,Duration,
STUFF(( SELECT ', ' + t1.wk 
FROM TC26_TrainingInfo t1 
WHERE 
	    t.Training = t1.Training 
        AND t.ClassRoom = t1.ClassRoom 
        AND t.StartTime = t1.StartTime 
        AND t.Duration = t1.Duration 
	    FOR XML PATH('')), 1, 1, '') Weeks
FROM TC26_TrainingInfo t
GROUP BY Training,ClassRoom, StartTime,Duration
ORDER BY Training DESC

--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ Count Spaces in a string Puzzle ]

02 Thursday Apr 2015

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

≈ 3 Comments

Tags

Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries

T-SQL Query | [ Count Spaces in a string Puzzle ]

The puzzle is very simple. Here you have to count the spaces present in the string; there are multiple ways to achieve this, Please post all the possible solutions. Please check out the sample input and expected output for details.

Sample Input

texts

P Q R S

L M N O 0 0

I am here only

Welcome to the new world

Hello world program

Are u nuts

Expected output

Data	SpacesPresent
P Q R S	4
L M N O 0 0	11
I am here only	5
Welcome to the new world	6
Hello world program	3
Are u nuts	4

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

Script

Use the below script to generate the source table and fill them up with the sample data.


CREATE TABLE CountSpaces
(
texts VARCHAR(100)
)
GO

INSERT INTO CountSpaces(texts) VALUES
('P Q R S '),
(' L M N O 0 0     '),
('I  am here only '),
(' Welcome to the new world '),
(' Hello world program'),
(' Are u nuts ')

UPDATE – 11-Apr-2015 – Solution 1


--

--NOTE In case of VARCHAR

--Solution 1

SELECT texts Data, DATALENGTH(texts) - DATALENGTH(REPLACE(texts,' ', '')) SpacesPresent FROM CountSpaces

--Solution 2

SELECT texts Data, LEN(texts+'a') - LEN(REPLACE(texts,' ', '') + 'a') SpacesPresent FROM CountSpaces


--NOTE In case of NVARCHAR

--Solution 1

SELECT texts Data, LEN(texts+'a') - LEN(REPLACE(texts,' ', '') + 'a') SpacesPresent FROM CountSpaces

--Solution 2

SELECT texts Data , DATALENGTH(CAST(texts AS VARCHAR(MAX))) - LEN(REPLACE(CAST(texts AS VARCHAR(MAX)),' ', '')) SpacesPresent FROM CountSpaces


--

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

Keep Learning

http://MSBISkills.com

T-SQL Query | [ Count NULL Values Puzzle ]

02 Thursday Apr 2015

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

≈ 2 Comments

Tags

T-SQL Query | [ Count NULL Values Puzzle ]

The puzzle is very simple. Here you have to count the null values from a single column; there are multiple ways to achieve this, Please post all the possible solutions. Please check out the sample input and expected output for details.

Sample Input

NULL

Expected output

NULLCount

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

Script

Use the below script to generate the source table and fill them up with the sample data.

CREATE TABLE CountNULL
(
 ID INT NULL
)
GO

INSERT INTO CountNULL(ID)
VALUES
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL),
(2),
(3),
(8),
(9),
(NULL)

UPDATE – 10-Apr-2015 – Solution 1 & 2


-- Solution 1 --

SELECT COUNT(1) NULLCount FROM CountNULL WHERE ID IS NULL


-- Solution 2 

SELECT SUM( CASE WHEN ID IS NULL THEN 1 ELSE 0 END ) NULLCount FROM CountNULL

--

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

Keep Learning

http://MSBISkills.com