Advertisements

SQL Puzzle | The Non Space Puzzle

Tags

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


SQL Puzzle | The Non Space Puzzle

In this puzzle you have to get rows where there is no space in the column a. Please check out the sample input values and sample expected output below.

Sample Input

(No column name)
|s11hp|
|s1 1hp|
||
| |
| |

Expected Output

a
|s11hp|
||

Script

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

--

SELECT '|' + a + '|' FROM 
(
      SELECT 's11hp' a
      UNION ALL
      SELECT 's1 1hp'
      UNION ALL
      SELECT ''
      UNION ALL
      SELECT ' '
      UNION ALL
      SELECT '      '
)u

--

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


--

SELECT '|' + a + '|' FROM 
(
      SELECT 's11hp' a
      UNION ALL
      SELECT 's1 1hp'
      UNION ALL
      SELECT ''
      UNION ALL
      SELECT ' '
      UNION ALL
      SELECT '      '
)u
WHERE a NOT LIKE '% %'

--

Solution – 2


--

SELECT '|'+ a + '|' FROM 
(
      SELECT 's11hp' a
      UNION ALL
      SELECT 's1 1hp'
      UNION ALL
      SELECT ''
      UNION ALL
      SELECT ' '
      UNION ALL
      SELECT '      '
)u
WHERE CHARINDEX(' ',a,0) = 0

--

Output

--

/*------------------------
OUTPUT
------------------------*/

--------
|s11hp|
||

(2 row(s) 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

Advertisements

SQL Puzzle | The XML Explicit Puzzle

Tags

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


SQL Puzzle | The XML Explicit Puzzle

In this puzzle you have to generate the XML using some method. Any method like XML Explicit or XML Auto or XML path.
Please check out the sample input values and sample expected output below.

Sample Input

ClientAccountId ClientName ContactCode InvoiceFlag
123456 Mickey Mouse MMouse 0
123456 Mickey Mouse MMouse 1

Expected Output


<StaticData>
  <Header FileDate="10/16/2017" />
  <Body DataType="Account">
    <Account ClientAccountId="123456" Name="Mickey Mouse">
      <AssignContact ClientContactId="MMouse" InvoiceFlag="0" />
    </Account>
    <Account ClientAccountId="123456" Name="Mickey Mouse">
      <AssignContact ClientContactId="MMouse" InvoiceFlag="1" />
    </Account>
  </Body>
  <Trailer DataType="Account" RecordCount="2" />
</StaticData>


Script

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

--

;WITH tempPort AS
(
     SELECT 123456 ClientAccountId , 'Mickey Mouse' ClientName , 'MMouse' ContactCode , 0 InvoiceFlag 
     UNION ALL
     SELECT 123456 ClientAccountId , 'Mickey Mouse' ClientName , 'MMouse' ContactCode , 1 InvoiceFlag      
)
SELECT * FROM tempPort

--

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


--

;WITH tempPort AS
(
     SELECT 123456 ClientAccountId , 'Mickey Mouse' ClientName , 'MMouse' ContactCode , 0 InvoiceFlag 
     UNION ALL
     SELECT 123456 ClientAccountId , 'Mickey Mouse' ClientName , 'MMouse' ContactCode , 1 InvoiceFlag      
)
SELECT Tag,Parent,[StaticData!1!StaticData],
                        [Header!2!FileDate],
                        [Header!2!DeleteContactsByOmission],
                        [Body!3!DataType],
                        [Account!4!ClientAccountId], 
                        [Account!4!Name],
                        [AssignContact!5!ClientContactId], 
                        [AssignContact!5!InvoiceFlag],        
                        [Trailer!6!DataType],
                        [Trailer!6!RecordCount]
FROM 
(
     SELECT 1 as Tag,  
              0 as Parent,
              0 child,             
              NULL as [StaticData!1!StaticData],
              NULL as [Header!2!FileDate],
              NULL as [Header!2!DeleteContactsByOmission],
              NULL as [Body!3!DataType],
              NULL as [Account!4!ClientAccountId], 
              NULL as [Account!4!Name],
              NULL as [AssignContact!5!ClientContactId], 
              NULL as [AssignContact!5!InvoiceFlag],          
              NULL as [Trailer!6!DataType],
              NULL as [Trailer!6!RecordCount]
            
     UNION ALL   
     Select 2,1,0, 'StaticData', convert(char(10),getdate(),101),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

     UNION ALL   
     Select 3,1,0, 'StaticData',  convert(char(10),getdate(),101),'True','Account',NULL,NULL,NULL,NULL,NULL,NULL

     UNION ALL  
           
           Select 4,3,0, 'StaticData', convert(char(10),getdate(),101),'True','Details',ClientAccountId,ClientName,NULL,NULL,NULL,NULL from ( SELECT DISTINCT ClientAccountId,ClientName FROM tempPort t ) t
           UNION ALL
           Select 5,4,1, 'StaticData', convert(char(10),getdate(),101),'True','Details',ClientAccountId,ClientName,v.ContactCode,v.InvoiceFlag,NULL,NULL from ( SELECT DISTINCT ClientAccountId,ClientName FROM tempPort t ) t
           CROSS APPLY
           (
                SELECT TOP 1 InvoiceFlag,ContactCode
                FROM tempPort t1
                WHERE t1.ClientAccountId = t.ClientAccountId
                AND t1.ClientName = t1.ClientName 
           )v

     UNION ALL   
           Select 4,3,2, 'StaticData', convert(char(10),getdate(),101),'True','Details',ClientAccountId,ClientName,NULL,NULL,NULL,NULL from ( SELECT DISTINCT ClientAccountId,ClientName FROM tempPort t ) t
           UNION ALL
           Select 5,4,3, 'StaticData', convert(char(10),getdate(),101),'True','Details',ClientAccountId,ClientName,v.ContactCode,v.InvoiceFlag,NULL,NULL from ( SELECT DISTINCT ClientAccountId,ClientName FROM tempPort t ) t
           CROSS APPLY
           (
                SELECT TOP 1 InvoiceFlag,ContactCode
                FROM tempPort t1
                WHERE t1.ClientAccountId = t.ClientAccountId
                AND t1.ClientName = t1.ClientName 
                ORDER BY InvoiceFlag DESC
           )v

     UNION All   
     Select 6,1,0 ,'StaticData', convert(char(10),getdate(),101),'True','Trailer',NULL,NULL,NULL,NULL,'Account',count(*) from tempPort
)r
ORDER BY 3,4,5,6,child
FOR XML Explicit


--

Output

--

<StaticData>
  <Header FileDate="10/16/2017" />
  <Body DataType="Account">
    <Account ClientAccountId="123456" Name="Mickey Mouse">
      <AssignContact ClientContactId="MMouse" InvoiceFlag="0" />
    </Account>
    <Account ClientAccountId="123456" Name="Mickey Mouse">
      <AssignContact ClientContactId="MMouse" InvoiceFlag="1" />
    </Account>
  </Body>
  <Trailer DataType="Account" RecordCount="2" />
</StaticData>


--

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 | The 3 Decimal Digits Puzzle

Tags

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


SQL Puzzle | The 3 Decimal Digits Puzzle

In this puzzle you have find out the rows where the value of column contains a digit and 3 decimal places after that. Please let me know if you know any easy method than i have mentioned. Please check out the sample input values and sample expected output below.

Sample Input

Val
1.0
1.234
0.34
34.000
34
23.456
1.1
12.

Expected Output

Val
1.234
34.000
23.456

Script

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

--

CREATE TABLE [3Decimal]
(
	Val VARCHAR(100)
)
GO

INSERT INTO [3Decimal] VALUES
('1.0'),('1.234'),('0.34'),('34.000'),('34'),('23.456'),('1.1'),('12.')


--

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


--


SELECT * FROM [3Decimal]
WHERE Val LIKE '%.___'
	
--

Solution – 2


--

SELECT * FROM [3Decimal]
WHERE RIGHT(Val,4) LIKE '.%'
	
--

Output

--

/*------------------------

SELECT * FROM [3Decimal]
WHERE Val LIKE '%.___'
------------------------*/
Val
----------------------------------------------------------------------------------------------------
1.234
34.000
23.456

(3 row(s) 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 | The Percentage Puzzle

Tags

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


SQL Puzzle | The Percentage Puzzle

In this puzzle you have find out the percentage of people passed in the exam and the percentage of the people failed the exam.

Please check out the sample input values and sample expected output below.

Sample Input

ID StudentName ActionableResult
1 Haverly 0
2 Due 1
3 Bourne 1
4 Pam 1

Expected Output

PercentPass PercentFail
25.00000000000 75.00000000000

Script

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

--

CREATE TABLE StudentPassFail
(
	 ID INT
	,StudentName VARCHAR(10)
	,ActionableResult SMALLINT /* 1 - Fail , 0 - Pass */
)
GO

INSERT INTO StudentPassFail VALUES
( 1 , 'Haverly' , 0 ),
( 2 , 'Due' , 1 ),
( 3 , 'Bourne' , 1 ),
( 4 , 'Pam' , 1 )
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


--

SELECT 
	       ( COUNT(*)  - SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END )  ) * 100. / COUNT(*)  as PercentPass 
	,100 - ( COUNT(*)  - SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END )  ) * 100. / COUNT(*)  as PercentFail
FROM StudentPassFail
	
--

Output

--

/*------------------------
SELECT 
		   ( COUNT(*)  - SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END )  ) * 100. / COUNT(*)  as PercentPass 
	,100 - ( COUNT(*)  - SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END )  ) * 100. / COUNT(*)  as PercentFail
FROM StudentPassFail
------------------------*/
PercentPass                             PercentFail
--------------------------------------- ---------------------------------------
25.00000000000                          75.00000000000

(1 row(s) 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 | The OrdersAndState puzzle

Tags

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


SQL Puzzle | The OrdersAndState puzzle

We need to find out customers who have placed orders in 2017 and for those orders the amount should be equal or greater than $100.
Note that if it has multiple orders all must be at least $100 and the order must be going to IL. Also If there are multiple orders that meet 1 and 2 then all must be going to IL

Please check out the sample input values and sample expected output below.

Sample Input

Customerid OrderDate State Orderamount
1 2016-11-02 00:00:00.000 IL 100
2 2017-02-05 00:00:00.000 IL 200
3 2017-06-05 00:00:00.000 WI 200
3 2017-05-15 00:00:00.000 WI 200
1 2017-03-02 00:00:00.000 IL 100

Expected Output

Customerid OrderDate State Orderamount
2 2017-02-05 00:00:00.000 IL 200

>

Script

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

--

CREATE TABLE OrderAndStates
(
	 Customerid INT
	,OrderDate DATETIME 
	,[State] VARCHAR(2)    
	,Orderamount INT
)
GO

INSERT INTO OrderAndStates VALUES
(1,'11/02/2016','IL',100),
(2,'02/05/2017','IL',200),
(3,'06/05/2017','WI',200),
(3,'05/15/2017','WI',200),
(1,'03/02/2017','IL',100)
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


--
SELECT b.*
FROM OrderAndStates b
CROSS APPLY 
( 
	SELECT 1 r FROM OrderAndStates a 
	WHERE a.Customerid = b.Customerid 
	AND (Orderamount) >= 100
	AND State = 'IL' 				
	HAVING MIN(orderdate) >= '20170101'
)r	
--

Output


/*------------------------
SELECT b.*
FROM OrderAndStates b
CROSS APPLY 
( 
	SELECT 1 r FROM OrderAndStates a 
	WHERE a.Customerid = b.Customerid 
	AND (Orderamount) >= 100
	AND State = 'IL' 				
	HAVING MIN(orderdate) >= '20170101'
)r
------------------------*/
Customerid  OrderDate               State Orderamount
----------- ----------------------- ----- -----------
2           2017-02-05 00:00:00.000 IL    200

(1 row(s) 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