SQL Puzzle | Create constraint on multiple columns

14 Tuesday Feb 2017

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

≈ Leave a comment

Tags

SQL Puzzle | Create constraint on multiple columns

In this puzzle you have a table called Employees. In the table columns are – ID, EmployeeID, EmpName, IsValid. The requirement is that we should not have more than one IsValid = 1 per EmployeeID. What you do to handle this kind of situation while inserting data into the table ?

Script

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

--

CREATE TABLE OneIsValid
(
	  ID BIGINT
	, EmployeeID BIGINT
	, EmpName VARCHAR(100)
	, IsValid INT
)
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


--

/* For this kind of requirement we should always create function like below. */


CREATE FUNCTION dbo.CheckIsValid
( 
	@EmployeeID INT 
)
RETURNS INT
AS 
BEGIN
		RETURN 
		(
			SELECT COUNT(*) cnt FROM dbo.OneIsValid  
			WHERE EmployeeID = @EmployeeID AND IsValid = 1
		)
END

ALTER TABLE OneIsValid
    ADD CONSTRAINT Ix_Constraint CHECK (dbo.CheckIsValid(EmployeeID) = 1)
GO

--

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 | Delete Duplicate Records from a Table but one

14 Tuesday Feb 2017

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

≈ 3 Comments

Tags

SQL Puzzle | Delete Duplicate Records from a Table but one

In this puzzle you have two tables – a1 and a2. I want to remove duplicates from table a2 which have a entry in a1 table. Please check out the sample input values and sample expected output below.

Sample Input

Table – A1

ID
1
2

Table – A2

ID
1
2
1
3
3

Expected Output – Table A2

Id
1
2
3
3

Script

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

--

CREATE TABLE A1
(
	ID INT
)
GO

INSERT INTO A1 VALUES (1),(2)
GO

CREATE TABLE A2
(
	ID INT
)
GO

INSERT INTO A2 VALUES (1),(2),(1),(3),(3)
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


--

;WITH CTE AS 
(
	SELECT *, ROW_NUMBER () OVER (PARTITION BY Id ORDER BY Id) row_num from A2
)
DELETE x FROM a2 x 
INNER JOIN a1 on a1.Id = x.Id 
INNER JOIN CTE on x.Id = cte.Id 
WHERE CTE.row_num <> 1

--

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 Hotel Puzzle

14 Tuesday Feb 2017

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

≈ Leave a comment

Tags

SQL Puzzle | The Hotel Puzzle

Here is a table for recording guests’ stays at a hotel (arrDate denotes arrival date, depDate is departure date). A new entry (for a new guest) could be accidentally put in for a room number, even before the existing guest in that room has not checked out. How would you redesign the table to fix this issue. Note – This question was asked @ Microsoft.

Script

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

--

CREATE TABLE HotelStays
(roomNum INTEGER NOT NULL,
arrDate DATE NOT NULL,
depDate DATE NOT NULL,
guestName CHAR(30) NOT NULL,
PRIMARY KEY (roomNum, arrDate));

--

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


--

CREATE FUNCTION checkdata(@RoomNo int , @DepDate date)
RETURNS DATE
AS
BEGIN
	DECLARE @dep Date
		IF ((SELECT COUNT(1) FROM Rom WHERE RoomNo = @RoomNo AND DepDate = @DepDate) > 1)
		SELECT @dep = Depdate FROM Rom WHERE RoomNo = @RoomNo
		ELSE
		SELECT @dep = ISNULL(( SELECT MAX(CAST(DepDate AS DATE)) DepDate FROM Rom 
		WHERE RoomNo = @RoomNo AND DepDate <> @DepDate),CAST('1900-01-01' AS DATE))
    RETURN @dep         
END
GO

--

ALTER TABLE HotelStays
	ADD CONSTRAINT chk_date_1 CHECK  (DATEDIFF(day,arrdate,dbo.checkdata(RoomNo,DepDate)) < 0 )
GO
--

--

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The Pivot Puzzle – VII

14 Tuesday Feb 2017

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

≈ 3 Comments

Tags

SQL Puzzle | The Pivot Puzzle – VII

In this puzzle you have to write a T-SQL which will transform the data into column format. If any column is missed then we have to generate that column.

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

Sample Input

ID	Cau	Times
939861018	E8889	0
939861018	E8889	1
939861954	E8889	0
939861988	E8788	5
939861772	E8889	0
939861772	E8889	4

Expected Output

Id	0	1	4	5
939861018	E8889	E8889
939861772	E8889		E8889
939861954	E8889
939861988				E8788

Script

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

--

CREATE TABLE Transform
(
	 ID VARCHAR(100)
	,Cau VARCHAR(100)
	,Times INT
)
GO

INSERT INTO Transform VALUES
('939861018','E8889',0),
('939861018','E8889',1),
('939861954','E8889',0),
('939861988','E8788',5),
('939861772','E8889',0),
('939861772','E8889',4)
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


--

;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
)
,CTE AS
(
	SELECT Number - 1 Number FROM Series
)
,CTE1 AS
(
	SELECT t.Id,t.Cau,ISNULL(t.Times,s.Number) Times FROM CTE s
	LEFT JOIN Transform t
	ON S.Number = t.Times
	WHERE Number <= ( (SELECT MAX(Times) FROM Transform t ) ) 
	AND Number >= 0 
)
SELECT Id,ISNULL([0],'') [0],ISNULL([1],'') [1],ISNULL([2],'') [2],
ISNULL([3],'') [3],
ISNULL([4],'') [4],
ISNULL([5],'') [5],
ISNULL([6],'') [6]
FROM CTE1
PIVOT 
(
    MIN(Cau)
    FOR [Times] IN ([0],[1],[2],[3],[4],[5],[6]) 
)t 
WHERE Id IS NOT NULL

--

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 | SELECT Columns which doesn’t have all zero value

14 Tuesday Feb 2017

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

≈ 1 Comment

Tags

SQL Puzzle | Select columns which doesn’t have all zero value

In this puzzle will be given the table name and you have to get the data from the columns where at least one value is NON Zero. In below example we have 5 columns – Col1,col2,col3,col4 and col5. The requirement is to get data from col1,col3,col4.

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

Sample Input

Col1	Col3	Col4
100	0	20
100	0	10
100	12	20
100	0	80

Expected Output

Col1	Col3	Col4
100	0	20
100	0	10
100	12	20
100	0	80

Script

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

--

CREATE TABLE IgnoreZero
(
      Col1  INT  
     ,Col2  INT    
     ,Col3  INT
     ,Col4  INT
     ,Col5  INT
)
GO
 
INSERT INTO IgnoreZero VALUES
 (100,0,0,20,0)
,(100,0,0,10,0)
,(100,0,12,20,0)
,(100,0,0,80,0)
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


--

DECLARE @sq AS NVARCHAR(MAX) = ''
DECLARE @Cols1 AS VARCHAR(MAX) = ''
 
DECLARE @TableName AS VARCHAR(MAX) = 'IgnoreZero'
 
SELECT @Cols1 = @Cols1 + COLUMN_NAME + ', ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
SET @Cols1 = SUBSTRING(@Cols1,0,LEN(@Cols1)) 
 
SET @sq = N'
DECLARE @Cols AS VARCHAR(MAX) = '''' ; 
SELECT @Cols = @Cols + cols + '', ''
FROM ' +  @TableName + ' s
UNPIVOT ( Vals for cols in ( ' + @Cols1 +  ')) u
GROUP BY cols 
HAVING SUM(Vals) > 0 ;
SET @Cols = SUBSTRING(@Cols,0,LEN(@Cols));
SELECT @Cols1 = @Cols ;'
 
EXEC SP_ExecuteSQL @Query = @sq ,@Params = N'@Cols1 VARCHAR(MAX) OUTPUT' ,@Cols1 =@Cols1 OUTPUT
EXEC( 'SELECT ' +  @Cols1 +  ' FROM ' + @TableName) 

--

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

M	T	W	T	F	S	S
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28

Improving my SQL BI Skills

Daily Archives: February 14, 2017

SQL Puzzle | Create constraint on multiple columns

SQL Puzzle | Delete Duplicate Records from a Table but one

SQL Puzzle | The Hotel Puzzle

SQL Puzzle | The Pivot Puzzle – VII

SQL Puzzle | SELECT Columns which doesn’t have all zero value

Share this

Share this

Share this

Share this

Share this