Daily Archives: February 1, 2018

SQL Puzzle | OUTPUT Puzzle – The RIGHT Puzzle | Solution Added.

01 Thursday Feb 2018

Posted by in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 2 Comments


SQL Puzzle | OUTPUT Puzzle – The RIGHT Puzzle

In this puzzle you have to find out the output of the two queries without executing them. And You also have to provide reasoning for the output.

QUERY-1

--

DECLARE @Vals AS CHAR(6) = '87654'
SELECT RIGHT('000000' + @Vals , 6 ) Outp
GO

--

QUERY-2

--

DECLARE @Vals AS VARCHAR(6) = '87654'
SELECT RIGHT('000000' + @Vals , 6 ) Outp
GO

--

OUTPUT for Query 1

--

Outp
--------
|87654 |

(1 row affected)

--

In this case you will have a space at the end with the 5 characters on the left. So when we assign the 5 characters to a CHAR(6) [As it is fixed length data type] it is saving the data as |87654 | and the we are adding ‘000000’ to the value , so we will get |00000087654 |. So clearly we have a space at the end and we we take right 6 characters we will get |87654 |.

OUTPUT for Query 2

--

Outp
--------
|087654|

(1 row affected)

--

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | Add rows to the data as per the value present in a column

01 Thursday Feb 2018

Posted by in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 3 Comments

Tags

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


SQL Puzzle | Add rows to the data as per the value present in a column

In this puzzle you have to add extra rows to data as per the value present in in the column MultiplyData. If the value in the MultiplyData = 2 then that rows has be added twice including the original row. For more details please check the sample input and expected output.

Sample Input

Id Vals MultiplyData
1 a 1
2 b 2
3 c 3
4 d 4
5 e 2

Expected Output

Id Vals MultiplyData
1 a 1
2 b 2
2 b 2
3 c 3
3 c 3
3 c 3
4 d 4
4 d 4
4 d 4
4 d 4
5 e 2
5 e 2

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE MultiplyDataPuzzle
(
	 Id  INT
	,Vals VARCHAR(10)
	,MultiplyData INT
)
GO

INSERT INTO MultiplyDataPuzzle VALUES 
(1,'a',1),
(2,'b',2),
(3,'c',3),
(4,'d',4),
(5,'e',2)
GO

--

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

SOLUTION 1 | USING Generate Number Method

--

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2
)
SELECT p.* FROM MultiplyDataPuzzle p
INNER JOIN Series m  ON p.MultiplyData >= m.number 
ORDER BY p.Id

--

Output-1

--                            

Id          Vals       MultiplyData
----------- ---------- ------------
1           a          1
2           b          2
2           b          2
3           c          3
3           c          3
3           c          3
4           d          4
4           d          4
4           d          4
4           d          4
5           e          2
5           e          2

(12 rows affected)
                               
--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | Get MIN, MAX, AVG, Last and First Value | The SINGLE SELECT Puzzle

01 Thursday Feb 2018

Posted by in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

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


SQL Puzzle | The MIN, MAX, AVG, Last and First Value – SINGLE SELECT Puzzle

In this puzzle the requirement is you have to write a SINGLE T-SQL to get Minimum balance, Average balance, Maximum Balance and last and first value of balance per month and account. Please see expected output and input for more details. . For more details please check the sample input and expected output.

Sample Input

Id Acct Months Bal
1 10011 1 345
2 10011 1 122
3 10011 1 190
4 10011 2 111
5 10011 3 2300
6 10011 3 87820
7 10012 1 345
8 10012 1 190
9 10012 3 5000
10 10012 3 1500
11 10012 3 7000

Expected Output

Id Acct Months Bal MinimumBalance AverageBalance MaximumBalance FirstValue LastValue
1 10011 1 345 122 219.000000 345 345 190
2 10011 1 122 122 219.000000 345 345 190
3 10011 1 190 122 219.000000 345 345 190
4 10011 2 111 111 111.000000 111 111 111
5 10011 3 2300 2300 45060.000000 87820 2300 87820
6 10011 3 87820 2300 45060.000000 87820 2300 87820
7 10012 1 345 190 267.500000 345 345 190
8 10012 1 190 190 267.500000 345 345 190
9 10012 3 5000 1500 4500.000000 7000 5000 7000
10 10012 3 1500 1500 4500.000000 7000 5000 7000
11 10012 3 7000 1500 4500.000000 7000 5000 7000

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE GetMinMaxAvgVal
(
     Id INT
	,Acct INT
    ,Months INT   
    ,Bal INT
)
GO

INSERT INTO GetMinMaxAvgVal VALUES
(1, 10011, 1, 345),
(2, 10011, 1, 122),
(3, 10011, 1, 190),
(4, 10011, 2, 111),
(5, 10011, 3, 2300),
(6, 10011, 3, 87820),
(7, 10012, 1, 345),
(8, 10012, 1, 190),
(9, 10012, 3, 5000),
(10, 10012, 3, 1500),
(11, 10012, 3, 7000)
GO



--

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

SOLUTION 1 | USING MIN, MAX, AVG and First_Value with PARTITIONS

--

SELECT *
, MIN(Bal) OVER(PARTITION BY Acct,Months) MinimumBalance
, AVG(Bal*1.0) OVER(PARTITION BY Acct,Months) AverageBalance
, MAX(Bal) OVER(PARTITION BY Acct,Months) MaximumBalance
, FIRST_VALUE(Bal) OVER(PARTITION BY Acct,Months ORDER BY Id ASC) FirstValue
, FIRST_VALUE(Bal) OVER(PARTITION BY Acct,Months ORDER BY Id DESC) LastValue
FROM GetMinMaxAvgVal
ORDER By Id

--

Output-1

--                            

Id          Acct        Months      Bal         MinimumBalance AverageBalance       MaximumBalance FirstValue  LastValue
----------- ----------- ----------- ----------- -------------- -------------------- -------------- ----------- -----------
1           10011       1           345         122            219.000000           345            345         190
2           10011       1           122         122            219.000000           345            345         190
3           10011       1           190         122            219.000000           345            345         190
4           10011       2           111         111            111.000000           111            111         111
5           10011       3           2300        2300           45060.000000         87820          2300        87820
6           10011       3           87820       2300           45060.000000         87820          2300        87820
7           10012       1           345         190            267.500000           345            345         190
8           10012       1           190         190            267.500000           345            345         190
9           10012       3           5000        1500           4500.000000          7000           5000        7000
10          10012       3           1500        1500           4500.000000          7000           5000        7000
11          10012       3           7000        1500           4500.000000          7000           5000        7000

(11 rows affected)

                               
--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com