Daily Archives: April 30, 2018

Data Interview Question | What is a Data hub ?

30 Monday Apr 2018

Posted by in Data, Data warehousing, Download SQL Interview Q's, SQL SERVER, SQL Server Interview Questions

≈ Leave a comment


Data Interview Question | What is a Data hub ?

Hello Friends, Last month I recieved an email from one of my reader saying why you are not write on data related questions. I replied saying that I shall start soon. Today is a very good day to start the data related question. I shall also cover the BIG Data as well as data warehousing question. Now the category I am using these kind of questions as data interview questions. So today lets start with the data hub.

What is a data hub?-

A data hub basically is a collection of data (consider this as Big Data Management) from multiple sources. It uses a Hadoop platform as the central data repository. The idea of an data hub is to provide an organization with a centralized data source that can quickly provide users with the information they need.

The data distribution normally happens in the form of a hub and spoke architecture. The spoke-hub distribution paradigm is a form of transport topology optimization in which traffic routes are organized as a series of ‘spokes’ that connect outlying points to a central ‘hub.’

Enterprise data hubs are designed to address the challenge of data that is ballooning in volume, variety and velocity (3Vs). This term is largely associated with Cloudera and MapR.

Data Hub notes-
1. Data from all the sources data is moved to one place.
2. Data is (at least partially) harmonized as it is moved.
3. Data is indexed in the harmonized form for efficient access and analysis.

Data hubs products avaible are-

1. Apache Hadoop
2. Avoiding Mass Extinctions Engine
3. CKAN
4. DataHub
5. DataMarket
6. Dataverse
7. DSpace
8. MarkLogic
9. OpenDataSoft
10. Quandl
11. QuickCode
12. Socrata

References-

https://en.wikipedia.org/wiki/Data_hub
https://en.wikipedia.org/wiki/Spoke%E2%80%93hub_distribution_paradigm
https://searchcio.techtarget.com/definition/enterprise-data-hub
https://www.marklogic.com/blog/data-lakes-data-hubs-federation-one-best/

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 PUZZLE ( SQL 2016 ) | Remove all JSON elements BUT first | Advanced SQL

30 Monday Apr 2018

Posted by 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 | Remove all JSON elements BUT first | Advanced SQL

In this puzzle you have to delete all values from the JSON array but first. Please see the sample input and expected output.

Sample Input

(No column name)
{“a”:[1,2,3,4,5,6]}

Expected Output

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
{“a”:[1]}

Script – DDL and INSERT Sample Data

--

DECLARE @jsonstr NVARCHAR(MAX) = '{"a":[1,2,3,4,5,6]}'

--

SOLUTION – 1

--

DECLARE @jsonstr NVARCHAR(MAX) = '{"a":[1,2,3,4,5,6]}'

;WITH CTE AS 
(
  SELECT * FROM OPENJSON(@jsonstr, '$.a')
)
SELECT JSON_QUERY( CONCAT ( '[',STRING_AGG([Value],',') WITHIN GROUP (ORDER BY [key]) ,']') ,'$') a
FROM CTE
WHERE [key] = 0
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
GO

--

Output – 1

--

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
------------------------------------------------------
{"a":[1]}

(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 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 PUZZLE | Round the Vals value to Next whole Number | Advanced SQL

30 Monday Apr 2018

Posted by in SQL Concepts, SQL Performance Tuning, SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 3 Comments

Tags

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


SQL PUZZLE | Round the Vals value to Closest 10 value | Advanced SQL

In this puzzle you have to read the column – vals and round the integer to the closest next whole number. Please see the sample input and expected output.

Sample Input

Id Vals
1 40
2 41
3 42
4 43
5 44
6 45
7 46
8 47
9 48
10 49
11 50

Expected Output

Id Vals CloseToNextTen
1 40 40
2 41 50
3 42 50
4 43 50
5 44 50
6 45 50
7 46 50
8 47 50
9 48 50
10 49 50
11 50 50

Script – DDL and INSERT Sample Data

--

CREATE TABLE CloseTen
(
	 Id INT
	,Vals INT
)
GO

INSERT INTO CloseTen VALUES
(1,40),
(2,41),
(3,42),
(4,43),
(5,44),
(6,45),
(7,46),
(8,47),
(9,48),
(10,49),
(11,50)
GO

SELECT * FROM CloseTen
GO

--

SOLUTION – 1

--

SELECT * , ((Vals + 9)/ 10) * 10 CloseToNextTen
FROM CloseTen

--

Output – 1

--

Id          Vals        CloseToNextTen
----------- ----------- --------------
1           40          40
2           41          50
3           42          50
4           43          50
5           44          50
6           45          50
7           46          50
8           47          50
9           48          50
10          49          50
11          50          50

(11 rows affected)

--

SOLUTION – 2

--

SELECT * , (((Vals - 1) / 10) + 1) * 10  CloseToNextTen
FROM CloseTen

--

Output – 2

--

Id          Vals        CloseToNextTen
----------- ----------- --------------
1           40          40
2           41          50
3           42          50
4           43          50
5           44          50
6           45          50
7           46          50
8           47          50
9           48          50
10          49          50
11          50          50

(11 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 PUZZLE | The ONLY type PUZZLE | One of my BEST SQL Interview Question

30 Monday Apr 2018

Posted by 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

≈ 3 Comments

Tags

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


SQL PUZZLE | The ONLY type PUZZLE – One of my BEST SQL Interview Question

This is one of my best interview question for 5 years plus experience people. In this you have to find out rows where for each Id you have only one type of Type that is 21. In the sample you should get 2 and 4 rows as output. Please see the sample input and expected output.

Sample Input

Id Type Vals
1 3 900.00
1 3 200.00
1 3 800.00
1 21 200.00
2 21 100.00
2 21 100.00
3 3 100.00
3 3 100.00
4 21 100.00

Expected Output

Id Type Vals
2 21 100.00
2 21 100.00
4 21 100.00

Script – DDL and INSERT Sample Data

--

CREATE TABLE OnlyOneType
(
	 Id INT
	,[Type] INT
	,Vals DECIMAL(10,2)
)
GO

INSERT INTO OnlyOneType VALUES
(1,3 ,900.00 ),
(1,3 ,200.00 ),
(1,3 ,800.00 ),
(1,21,200.00 ),
(2,21,100.00  ),
(2,21,100.00  ),
(3,3 ,100.00 ),
(3,3 ,100.00 ),
(4,21,100.00)
GO

SELECT * FROM OnlyOneType T
GO

--

SOLUTION – 1

--

SELECT * FROM OnlyOneType T
WHERE [Type] = 21
AND NOT EXISTS ( SELECT NULL FROM OnlyOneType T1 WHERE T1.Id = T.Id and T1.Type <> 21 )


--

Output – 1

--

Id          Type        Vals
----------- ----------- ---------------------------------------
2           21          100.00
2           21          100.00
4           21          100.00

(3 rows affected)

--

SOLUTION – 2

--

SELECT Id,Type,Vals
FROM 
(
	SELECT *
	, COUNT(*) OVER(PARTITION BY Id) a 
	, SUM(CASE WHEN [Type]=21 THEN 1 ELSE 0 END) OVER(PARTITION BY Id) b 
	FROM OnlyOneType T
)x WHERE a = b

--

Output – 2

--

Id          Type        Vals
----------- ----------- ---------------------------------------
2           21          100.00
2           21          100.00
4           21          100.00

(3 rows affected)

--

SOLUTION – 3

--

SELECT T.* FROM OnlyOneType T
JOIN
(
	SELECT ID from OnlyOneType 
	GROUP BY ID having COUNT(DIstinct type) = 1 AND MAX([type]) = 21 
)x ON x.Id = T.Id

--

Output – 3

--

Id          Type        Vals
----------- ----------- ---------------------------------------
2           21          100.00
2           21          100.00
4           21          100.00

(3 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