Interview Question for Sql query or sqlserver interview query.

Description:-

In this example we explain that most important sql query that is asked by all interviewers in interview. So this some query is more useful to prepare your sql server interview question.

Here we define or explain all most important sql query that is I was really faced in my interview in my life and I will solved all the sql query and here define all sql query are below.

Sorting Row data in gridview Gridview Sorting 

How to handle Concurrency in Linq to Sql Concurrency Example 

--Select Today Date in Sql query RETURN only Date

SELECT CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME)

--Select LastDay OR Last Date in Sql query

SELECT CAST( CONVERT( CHAR(8), GetDate()-1, 112) AS DATETIME)

--Select NextDay or Next Date in Sql query

SELECT CAST( CONVERT( CHAR(8), GetDate()+1, 112) AS DATETIME)

--Select Next Week OR Next week Date in Sql query

SELECT CAST( CONVERT( CHAR(8), GetDate()+7, 112) AS DATETIME)

--Select Last Week OR Last week Date in Sql query

SELECT CAST( CONVERT( CHAR(8), GetDate()-7, 112) AS DATETIME)

--Select first day of the month in Sql query

SELECT DATEADD(dd, -DAY((SELECT CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME) )) + 1,(SELECT CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME) ))

--Select Last day of month in Sql query

SELECT DATEADD(dd, -DAY(DATEADD(mm, 1,(SELECT CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME)))), DATEADD(mm, 1,(SELECT CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME))))

--Select First Day of Year in Sql query

SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,

            DATEADD(YEAR, 0 ,GETDATE())), 0),

            'First Day of Year'

--Select Last Day of Year in Sql query

SELECT      DATEADD(MILLISECOND, -3,

            DATEADD(YEAR, DATEDIFF(YEAR, 0,

            DATEADD(YEAR, 0, GETDATE())) + 1, 0)),

            'Last Day of Year'

--How to find second highest or maximum salary of Employee in SQL

-- (1)Second maximum salary using sub query and IN clause

SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);

--(2)Here is another SQL query to find second highest salary using subquery and < operator instead of IN clause:

SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee);

--(3)Second highest salary using TOP keyword

SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM employees ORDER BY salary DESC) AS emp ORDER BY salary ASC

--(4)Second maximum salary using LIMIT keyword

SELECT salary  FROM (SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary LIMIT 1;

--How to Display Half record or rows in a table or 50 percent of the rows in sql

SELECT TOP (select COUNT(*)/2 from dbo.reportinfo) name from reportinfo

                                --OR

SELECT top 50 percent name from reportinfo

--Remove Duplicate Rows From Table

--(1)Remove Duplicate Records by using ID()

DELETE

FROM MyTable

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM MyTable

GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

--(2)Remove Duplicate Records by using ROW_NUMBER()

WITH TempEmp (Name,duplicateRecCount)

AS

(

SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name)

AS duplicateRecCount

FROM dbo.Employee

)

--Now Delete Duplicate Records

DELETE FROM TempEmp

WHERE duplicateRecCount > 1