SQL important Queries

Check sql server version in Sql Server

Ans-select @@version

check how many tables in Sql Server

Ans-select * from sys.tables

Show complete structure of table in Sql Server

Ans-EXEC sp_help TableName;

How to show all temp tables in sql server

Ans-select * from tempdb.sys.objects

List all indexes in Sql Server

SELECT T.Name, I.name FROM sys.indexes I INNER JOIN Sys.tables t ON t.object_id =

I.Object_ID WHERE index_id >= 1 -- skip heep

List all indexes with all details in Sql Server

select * from sys.indexes

Get first and last date of month in Sql Server

DECLARE @Month int

DECLARE @Year int

set @Month = 2

set @Year = 2005

select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First*/

select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last*/

Display every Nth row in Sql Server

SELECT id FROM employees GROUP BY id  HAVING MOD(id, N) = 0;

Age in years in Sql Server

DECLARE @dob datetime

SET @dob='1992-01-09 00:00:00'

SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal

,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound

,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc

Date diffrence in Sql Server

DATEDIFF(datepart,startdate,enddate)

Update Table using Join in Sql Server

UPDATE employee e LEFT JOIN employee_salary s ON e.id=s.employee_id

SET s.salary='50000'

where e.first_name='Admin'

.

DELETE Table using Join in Sql Server

Delete employee_salary FROM employee_salary LEFT JOIN employee ON employee.id=employee_salary.employee_id

WHERE employee.first_name='Admin'

Select first 3 characters of FIRST_NAME from EMPLOYEE

 select substring(FIRST_NAME,0,3) from employee

Get position of 'L' in name 'Gulshan' from employee table

Select CHARINDEX('L',FIRST_NAME,0) from employee where first_name = 'Gulshan'

 Replacing 'L' with '$'

select REPLACE(FIRST_NAME,'L','$') from employee

Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table

select SUBSTRING (convert(varchar,joining_date,103),7,4) , SUBSTRING (convert(varchar,joining_date,100),1,3) , SUBSTRING (convert(varchar,joining_date,100),5,2) from EMPLOYEE

Get employee details from employee table whose joining year is “2013”

Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,103),7,4) = '2013'

Get employee details from employee table whose joining month is “January”

Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,100),1,3) = 'Jan'

Get employee details from employee table who joined before January 1st 2013

Select * from EMPLOYEE where joining_date < '01/01/2013'

Get employee details from employee table who joined between January 1st and January 31st

Select * from EMPLOYEE where joining_date between '01/01/2013' and '01/31/2013'

Get Joining Date and Time from employee table

Select convert(varchar(19),joining_date,121) from EMPLOYEE

Get Joining Date,Time including milliseconds from employee table

select convert(varchar,joining_date,121) from EMPLOYEE

Get difference between JOINING_DATE and INCENTIVE_DATE from employee and incentives table

Select FIRST_NAME,INCENTIVE_DATE - JOINING_DATE from employee a inner join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

Get names of employees from employee table who has '%' in Last_Name.

Select FIRST_NAME from employee where Last_Name like '%[%]%'

Get Last Name from employee table after replacing special character with white space

Select REPLACE(LAST_NAME,'%',' ') from employee

Get department,no of employees in a department,total salary with respect to a department from employee table order by total salary descending

Select DEPARTMENT,count(FIRST_NAME),sum(SALARY) Total_Salary from employee group by DEPARTMENT order by Total_Salary descending

 Select Last Name from employee table which contain only numbers

Select * from EMPLOYEE where lower(LAST_NAME) = upper(LAST_NAME)

Insert into employee table Last Name with ''' (Single Quote - Special Character, Use single quote before special character )

Insert into employee (LAST_NAME) values ('Test''')

Select Nth Highest salary from employee table

select min(SALARY) from (select top N * from employee) a

select min(SALARY) from (select top 2 * from employee) a

Select max incentive with respect to employee from employee and incentives table using sub query

select DEPARTMENT,(select ISNULL(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID = EMPLOYEE_ID) Max_incentive from EMPLOYEE

Select first_name, incentive amount from employee and incentives table for all employees who got incentives using left join

Select FIRST_NAME, isnull(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID

 Select first_name, incentive amount from employee and incentives table for all employees even if they didn't get incentives and set incentive amount as 0 for those employees who didn't get incentives.

Select FIRST_NAME, ISNULL(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID = B.EMPLOYEE_REF_ID