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.

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

transactions

set transaction isolation level repeatable read

begin tran

select * from Relationship where link_id=64099

commit tran

set transaction isolation level repeatable read

begin tran

update Relationship set start_date='2012-12-06' from Relationship where link_id=64099

commit tran

set transaction isolation level read committed

begin tran

select * from Relationship where link_id=64099

commit tran

set transaction isolation level read committed

begin tran

update Relationship set start_date='2012-12-06' from Relationship where link_id=64099

commit tran

NOLOCK VS ReadPast

select * from Relationship(nolock) where link_id=64099

select * from Relationship(readpast) where link_id=64099