SQL Logical Questions and answers

1) Query to combine two columns but second columns called Name and Price. 

Name max length will be 60. Then after 60 length price column will start. 

SELECT Name
	,Price
	,CONCAT (
		LEFT(CONCAT (
				Name
				,Replicate(' ', 60)
				), 45)
		,Price
		) AS ConcatedString
FROM [portfolio];


Here name coulmn values current lenght is different however when we combine using price it should show padding.
This can be achieved using the LEFT and Replicate function.




2)If there is multiple fields we need to follow the same

DECLARE @Table AS TABLE (Val VARCHAR(4000))

INSERT INTO @Table
SELECT CONCAT (
		'Name'
		,Replicate(' ', 36)
		,'Price'
		,Replicate(' ', 10)
		,'Price Change'
		)

INSERT INTO @Table
SELECT CONCAT (
		LEFT(CONCAT (
				Name
				,Replicate(' ', 60)
				), 40)
		,LEFT(CONCAT (
				Price
				,Replicate(' ', 15)
				), 15)
		,[Price Change]
		)
FROM [portfolio]

SELECT *
FROM @Table


The above query information can be achieved by using LPAD and RPAD functions.
unfortunately SQL server do not have LPAD and RPAD functionality. 
It be achieved by creating function by using LPAD and RPAD functions.

2) RPAD and LPAD Function in SQL Server

SQL Server by default do not have RPAD and LPAD functions.

CREATE FUNCTION dbo.LPAD (
	@Column VARCHAR(100)
	,@PadString VARCHAR(10)
	,@length INT
	)
RETURNS VARCHAR(100)
AS
BEGIN
	DECLARE @Resultvar VARCHAR(100);

	SELECT @Resultvar = LEFT(CONCAT (
				REPLICATE(@PadString, @length - LEN(@Column))
				,@Column
				), @length);

	RETURN @Resultvar
END



CREATE FUNCTION dbo.RPAD (
	@Column VARCHAR(100)
	,@PadString VARCHAR(10)
	,@length INT
	)
RETURNS VARCHAR(100)
AS
BEGIN
	DECLARE @Resultvar VARCHAR(100);

	SELECT @Resultvar = RIGHT(CONCAT (
				@Column
				,REPLICATE(@PadString, @length - LEN(@Column))
				), @length);

	RETURN @Resultvar
END


The query can be re written like the below

SELECT CONCAT (
		dbo.LPAD(Name, ' ', 60)
		,dbo.LPAD(Price, ' ', 60)
		,[Price Change]
		)
FROM [portfolio]

2) Query to Create a table from 1 to 9 contain each number filled with one cell and total sum should be same. 
That means the sum should be equal for each row (horizontally when we sum row) and 
each column (when we sum all column values)
to achieve this we need to create a function that will return a table
for a row that sums to 15 by combining col1 ,col2 and col3 in row1
Then list of the 3*3 table.
-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		   M.R M
-- Create date:    22-Dec-2024
-- Description:	  Create a possible matches
-- =============================================
CREATE FUNCTION dbo.SudokuTable (
	-- Add the parameters for the function here
	@minval INT
	,@maxval INT
	)
RETURNS @SudokuTable TABLE (
	-- Add the column definitions for the TABLE variable here
	FirstColumn INT
	,SecondColumn INT
	,ThirdColumn INT
	)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	DECLARE @rc1 INT
		,@rc2 INT
		,@rc3 INT
		,@row1 INT
		,@inc INT
		,@sumval INT
	
	SET @sumval = 15
	SET @inc = 1
	SET @rc1 = 1
	
	WHILE @rc1 <= @maxval
	BEGIN
		SET @rc2 = @inc
		SET @rc3 = @inc
		
		WHILE (@rc2 <= @maxval)
		BEGIN
			SET @rc3 = @inc
			
			IF @rc1 = @rc2
				SET @rc2 = @rc2 + @inc
			
			IF @rc1 = @rc3
				SET @rc3 = @rc3 + @inc
			
			WHILE (@rc3 <= @maxval)
			BEGIN
				SET @row1 = sum(@rc1 + @rc2 + @rc3)
				
				IF (@row1 = @sumval)
				BEGIN
					INSERT INTO @SudokuTable
					VALUES (
						@rc1
						,@rc2
						,@rc3
						)		
				END
				
				SET @rc3 = @rc3 + @inc
				
				IF (@rc3 = @rc2)
					SET @rc3 = @rc3 + @inc
				
				IF (@rc1 = @rc3)
					SET @rc3 = @rc3 + @inc
			END
			
			SET @rc2 = @rc2 + @inc
			SET @rc3 = @rc1 + @inc
		END
		
		SET @rc1 = @rc1 + @inc
	END
	
	RETURN
END
GO
The above will generates table have all possibilites to create 
15 for each row.
Now we got all list, from that we need to make 3 rows .

SET NOCOUNT ON
DECLARE @SudokuTable AS TABLE (
	ID INT identity
	,FirstColumn INT
	,SecondColumn INT
	,Thirdcolumn INT
	)
CREATE TABLE #temp1 (
	ID INT identity(1, 1)
	,FirstColumn INT
	,SecondColumn INT
	,Thirdcolumn INT
	)
CREATE TABLE #temp2 (
	ID INT identity(1, 1)
	,FirstColumn INT
	,SecondColumn INT
	,Thirdcolumn INT
	)
DECLARE @Finaltable AS TABLE (List VARCHAR(100))
DECLARE @minvalue INT
	,@inc INT = 1
	,@maxvalue INT
	,@r1c1 INT
	,@r1c2 INT
	,@r1c3 INT
	,@r2c1 INT
	,@r2c2 INT
	,@r2c3 INT
	,@r3c1 INT
	,@r3c2 INT
	,@r3c3 INT
DECLARE @secondminval INT
	,@secondmaxval INT
	,@thirdminval INT
	,@thirdmaxval INT
	,@setval INT = 1
INSERT INTO @SudokuTable
SELECT *
FROM [dbo].[SudokuTable](1, 9)
SELECT @maxvalue = max(ID)
	,@minvalue = min(ID)
FROM @SudokuTable
WHILE (@minvalue <= @maxvalue)
BEGIN
	SELECT @r1c1 = FirstColumn
		,@r1c2 = SecondColumn
		,@r1c3 = Thirdcolumn
	FROM @SudokuTable
	WHERE ID = @minvalue
	
	INSERT INTO #temp1
	SELECT FirstColumn
		,SecondColumn
		,Thirdcolumn
	FROM @SudokuTable
	WHERE FirstColumn NOT IN (
			@r1c1
			,@r1c2
			,@r1c3
			)
		AND SecondColumn NOT IN (
			@r1c1
			,@r1c2
			,@r1c3
			)
		AND Thirdcolumn NOT IN (
			@r1c1
			,@r1c2
			,@r1c3
			)
	
	SELECT @secondmaxval = max(ID)
		,@secondminval = min(ID)
	FROM #temp1
	
	WHILE (@secondminval <= @secondmaxval)
	BEGIN
		SELECT @r2c1 = FirstColumn
			,@r2c2 = SecondColumn
			,@r2c3 = Thirdcolumn
		FROM #temp1
		WHERE ID = @secondminval
		
		INSERT INTO #temp2
		SELECT FirstColumn
			,SecondColumn
			,Thirdcolumn
		FROM #temp1
		WHERE FirstColumn NOT IN (
				@r2c1
				,@r2c2
				,@r2c3
				)
			AND SecondColumn NOT IN (
				@r2c1
				,@r2c2
				,@r2c3
				)
			AND Thirdcolumn NOT IN (
				@r2c1
				,@r2c2
				,@r2c3
				)
		
		SELECT @thirdmaxval = max(ID)
			,@thirdminval = min(ID)
		FROM #temp2
		
		WHILE (@thirdminval <= @thirdmaxval)
		BEGIN
			SELECT @r3c1 = FirstColumn
				,@r3c2 = SecondColumn
				,@r3c3 = Thirdcolumn
			FROM #temp2
			WHERE ID = @thirdminval
			
			IF (
					sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c2 + @r2c2 + @r3c2)
					AND sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c3 + @r2c3 + @r3c3)
					AND sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c1 + @r2c2 + @r3c3)
					AND sum(@r1c1 + @r2c1 + @r3c1) = sum(@r1c3 + @r2c2 + @r3c1)
					)
			BEGIN
				INSERT INTO @Finaltable
				VALUES (
					CONCAT (
						'##Set '
						,@setval
						,'  for Sudoko##'
						)
					)
				
				INSERT INTO @Finaltable
				VALUES (
					CONCAT (
						@r1c1
						,'  '
						,@r1c2
						,'  '
						,@r1c3
						)
					)
					,(
					CONCAT (
						@r2c1
						,'  '
						,@r2c2
						,'  '
						,@r2c3
						)
					)
					,(
					CONCAT (
						@r3c1
						,'  '
						,@r3c2
						,'  '
						,@r3c3
						)
					)
				
				SET @setval = @setval + @inc
			END
			
			SET @thirdminval = @thirdminval + @inc
		END
		
		TRUNCATE TABLE #temp2
		
		SET @secondminval = @secondminval + @inc
	END
	
	TRUNCATE TABLE #temp1
	
	SET @minvalue = @minvalue + @inc
END
SELECT *
FROM @Finaltable

Import CSV Files into SQL Server using SSMS (SQL Server Management Studio)

 SSMS tool is used for writing queries. Apart from this, it is very helpful tool to import the CSV data into SQL Server.

Suppose we have a CSV file like this which is having double quotes and comma separated and it has headers also.

To Load this file into SQL Server, We can import using the below.

Database --> Tasks --> Import Data

It opensup the wizard.

Click next. Select Flat File as Data Source and select the file path,

Format :  Delimited. In the above CSV the delimiter is comma. 

Header Row Delimeter :  It is comma

Header rows to skip :  We have headers in the csv file so it should be zero

Select check box column names in the first row data

Click on columns to preview

If you want to change anything, use the advanced tab 

To preview rows clik on preview. 

Click next and select SQL native client and SQL server instance name. We windows or SQL based on the usage. Select database. 

Click next and sleect target name. We can rename the table also if it new table. Since this table not exist in db ,SQL will create automatically.

If we want to change anything in destination mapping we can use edit mapping. If we are sure it is not exist we can use create destination table or use other options based on usage.

Run immediately to run the package.

Click Finish to start the package.

The package run completed successfully.

We can see verify the table data by running the select in the target database.

Sql server General T-SQL Interview questions

How to Delete duplicate Records:

here am showing the examples how to delete duplicate records in sqlserver.

First create the table with sample data

create table #test (id int identity,name varchar(100))

insert #test

select 'mals'

union all

select 'mals'

first method.using cte with row_number

;with cte as

(

select ID,name,ROW_NUMBER() over(PARTITION BY NAME ORDER BY ID) as row from #test

)

delete from CTE

where row>1

select * from #test

go

drop table #test

go

second method.using group by clause

create table #test (id int identity,name varchar(100))

insert #test

select 'mals'

union all

select 'mals'
go

delete from #test

where id in (select MAX(ID) from #test

group by name

having COUNT(*)>1)

select * from #test

go

drop table #test

Suppose there is no unique key  in table. using the below sample query you can delete the duplicate data.

create table test (name varchar(10))

insert Test

select 'malli'

union all

select 'malli'

union all

select 'ram'

union all

select 'ram'

;with cte as

(

  select row_number() over (partition by name order by name) as row,name from test

)

delete from cte where row>1

select * from Test

drop table Test

2) How to get top n records from a give table

using ranking function.

;with cte

as

(

select id,dense_rank() over ( order by id desc) as row

from systables

)

select id from cte where row between 6 and 10   ---top 6-10 records

go

using top 

declare @date datetime;

set @date=getdate()

select top 5 id

from sysobjects

where id in (select top 10 id

from sysobjects order by id desc

)

order by id asc    ---top 6-10 records

3) Suppose i have 3 tables with same data.First table have clustered index ,second table have non clustered
index and third table have no index. suppose i issue a command

select * from tablename

which will execute faster.

declare @time datetime2;

set @time=GETDATE()

select * from NOIXRELATIONSHIP

select DATEDIFF(ms,@time,getdate())

set @time=GETDATE()

select * from CLIXRELATIONSHIP

select DATEDIFF(ms,@time,getdate())

set @time=GETDATE()

select * from NONCLIXRELATIONSHIP

select DATEDIFF(ms,@time,getdate())

go

4)  Below is the Employee table which has the four feilds that contain the data. Assume ManagerID is Dept ID. You need to get second highest salary for each department.

Table

-----------

Expected output 

-----------------

Using the Ranking functions we can get the desired output. 

1) Row_number provides the 3 entries even if tiep-up with the with same salary

2) Dense_rank and rank will give the match and returns mutiple rows.

WITH CTE
AS (
	SELECT ID
		,name
		,AnnualSalary
		,ManagerID
		,row_number() OVER (
			PARTITION BY ManagerID ORDER BY AnnualSalary DESC
			) AS rnk
	FROM Table1
	)
SELECT ID
	,name
	,AnnualSalary
	,ManagerID
FROM CTE
WHERE rnk = 2;

WITH CTE
AS (
	SELECT ID
		,name
		,AnnualSalary
		,ManagerID
		,dense_rank() OVER (
			PARTITION BY ManagerID ORDER BY AnnualSalary DESC
			) AS rnk
	FROM Table1
	)
SELECT ID
	,name
	,AnnualSalary
	,ManagerID
FROM CTE
WHERE rnk = 2;

CREATE TABLE Table1 (
	ID INT
	,Name VARCHAR(50)
	,AnnualSalary BIGINT
	,ManagerID INT
	)

INSERT INTO Table1
VALUES (
	1
	,'Lisa'
	,150000
	,NULL
	)
	,(
	2
	,'Dan'
	,110000
	,1
	)
	,(
	3
	,'Oliver'
	,180000
	,1
	)
	,(
	4
	,'Dave'
	,100000
	,1
	);

INSERT INTO Table1
VALUES (
	5
	,'Steve'
	,200000
	,NULL
	)
	,(
	6
	,'Alice'
	,2050000
	,5
	)
	,(
	7
	,'Damian'
	,100000
	,5
	)
	,(
	8
	,'Avery'
	,210000
	,5
	)
	,(
	9
	,'Mark'
	,210000
	,5
	);

SELECT *
FROM Table1

5) Returns pages from a cook book alternatevly 

CREATE TABLE [dbo].[recipes] (
	[page_no] [int] NULL
	,[title] [varchar](50) NULL
	) ON [PRIMARY]
GO

DECLARE @table TABLE (
	pageno INT
	,title VARCHAR(30)
	)

INSERT INTO @table
VALUES (
	1
	,'pancake'
	)
	,(
	2
	,'pizza'
	)
	,(
	3
	,'pasta'
	)
	,(
	5
	,'cookie'
	);

WITH cte_pages
AS (
	-- generate page numbers
	SELECT 0 n
		,MAX(pageno) maxpgno
	FROM @table
	
	UNION ALL
	
	SELECT n + 1 n
		,maxpgno
	FROM cte_pages
	WHERE n <= maxpgno
	)
	,cte_left
AS (
	--- even
	SELECT n
		,ROW_NUMBER() OVER (
			ORDER BY n
			) rn
	FROM cte_pages
	WHERE n % 2 = 0
	)
	,cte_right
AS (
	--- odd
	SELECT n
		,ROW_NUMBER() OVER (
			ORDER BY n
			) rn
	FROM cte_pages
	WHERE n % 2 <> 0
	)
SELECT tl.title left_title
	,tr.title right_title
FROM cte_left l
INNER JOIN cte_right r ON l.rn = r.rn
LEFT OUTER JOIN @table tl ON tl.pageno = l.n
LEFT OUTER JOIN @table tr ON tr.pageno = r.n

6)  Last person to get into lift with max weight of 1000

SELECT TOP 1 name
FROM liftline t1
WHERE (
		SELECT SUM(t2.weight)
		FROM liftline t2
		WHERE t2.turn <= t1.turn
		) <= 1000
ORDER BY t1.turn DESC