SQL interview questions

1. Write a query for below scenario
Customer
Address

customer having first name santhosh and do not have any address.

CREATE TABLE [dbo].[Customer](

[Id] [int] NOT NULL,

[FName] [varchar](50) NULL,

[MName] [varchar](50) NULL,

 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 

(

[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[Address](

[Id] [int] NOT NULL,

[CustId] [int] NOT NULL,

[Add1] [varchar](50) NOT NULL,

[Add2] [varchar](50) NOT NULL,

 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

Ans :
select * from Customer
left join Address on Customer.Id = Address.CustId
where Address.Add1 is NULL and Customer.FName = 'Santhosh'

2. Write a  query for employee and dept problem where employee salary between 5000 to 10000 and name start with San. ?

select * from Employee
left join Department on Customer.DeptId = Department.Id
Where Salary between 5000 and 10000 and FName like 'San%'

3. Write a query for deleting duplicate customer and keeping 1 customer record .

DELETE n1 FROM Customer n1, Customer n2
Where n1.id > n2.id AND n1.FName = n2.FName

4. What is difference between nvarchar and varchar ?
nvarchar stores uncode characters and takes varchar takes ascii characters
http://sqlhints.com/2011/12/23/difference-between-varchar-and-nvarchar/


5. What are clustered and non clustered indexes ?



6. What is transaction ?