Chandradev's Blog

Hi

In interview, so many time they will ask some simple sql query. Here is some simple  and usefull sql query

–Max Salary

Select Max(EmpSal) as EmpSal from tblEmp1

–Minimum Salary

Select Min(EmpSal) as EmpSal from tblEmp1

–2nd highest salary

SELECT MAX(EmpSal) FROM tblEmp1 WHERE EmpSal NOT IN (SELECT MAX(EmpSal) FROM tblEmp1)

–Find Nth Highest Salary of Employee order by Desc

SELECT TOP 3 EmpSal
FROM (
SELECT DISTINCT TOP 3 EmpSal
FROM tblEmp1
ORDER BY EmpSal DESC) a
ORDER BY EmpSal

–2nd highest Salary from buttom of table

Select max(EmpSal) as Salary from tblEmp1
where EmpSal in(Select distinct top 2 EmpSal
from tblEmp1 order by EmpSal ASC)

–2nd highest Salary from top of table

Select min(EmpSal) as Salary from tblEmp1
where EmpSal in(Select distinct top 2 EmpSal
from tblEmp1 order by EmpSal DESC)

–How to copy structure from one table to other table

Select * into emp3 from tblEmp where 1=2

–Sql Query between two values

SELECT * FROM tblEmp1
WHERE EmpSal
BETWEEN ‘20000’ AND ‘25000’

–Sql Query Date Difference

SELECT DATEDIFF(‘2008-11-30′,’2008-11-29’) AS DiffDate

–Sql query for Round Function

SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products

–Last() function use to return the last value from table

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

–Query for Having

–The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

–In Operator(The IN operator allows you to specify multiple  values in a WHERE clause.)

SELECT * FROM Persons
WHERE LastName IN (‘Prasad’,’Sharma’)

–Selecting Data of  Today

SELECT *FROM  tblpostjobs WHERE postedDate>= DATEADD(day, DATEDIFF(day,0,GETDATE()),0)  AND  postedDate<  DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0) order by JobId Desc

Substracting in sqlserver

Select top 5 *from tblBuySellUpdate1 except Select top 2 *from tblBuySellUpdate1

Here performance will be faster.

OR

Select top 20 * from tbl_News where id not in(select top 3 id from tbl_news order by id Desc)order by id DESC

Performance will slower

Synatx for Creating Composite Primary Key

CREATE TABLE track(col1 numeric(10) , col2 numeric(10) ,col3 numeric(10), col4 numeric(10),
PRIMARY KEY (col1,col2,col3))

Syntax for fetching Null values

Select *from tblBuySellUpdate where RefId is null

Syntax for Deleting duplicate record from tables

select distinct * into #temp from [tblEmp] truncate table [tblEmp] insert [tblEmp] select * from #temp drop table #temp