SQL and ORM 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 ?

7. What is Temporary Table ?

Temporarytables

8. Linq optimization tips ?

linq optimization tips

 8. Lazy loading ?

lazy loading vs eager loading

9. Inline query vs stored procedure ?

• Reduce network traffic -- you have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.
• Caching query plan -- the first time the sproc is executed, SQL Server creates an execution plan, which is cached for reuse. This is particularly performant for small queries run frequently.
• Ability to use output parameters -- if you send inline SQL that returns one row, you can only get back a recordset. With sprocs you can get them back as output parameters, which is considerably faster.
• Permissions -- when you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a sproc
• Separation of logic -- remove the SQL-generating code and segregate it in the database.
• Ability to edit without recompiling -- this can be controversial. You can edit the SQL in a sproc without having to recompile the application.
• Find where a table is used -- with sprocs, if you want to find all SQL statements referencing a particular table, you can export the sproc code and search it. This is much easier than trying to find it in code.
• Optimization -- It's easier for a DBA to optimize the SQL and tune the database when sprocs are used. It's easier to find missing indexes and such.
• SQL injection attacks -- properly written inline SQL can defend against attacks, but sprocs are better for this protection.

Reference : 1 . stackoverflow

10.  Dapper advantage over EF ?

mediumlink

11.