Useful SQL queries

Retrive the primary key/Identity column value after the insertion of records.
INSERT INTO tbl_adminuser (name,pass) VALUES ('Amit1', 'pass1');SELECT id FROM tbl_adminuser WHERE id = @@IDENTITY


Select rows where all the characters in a column are uppercase/lowercase
select * from tbl_adminuser where binary_checksum(pass)=binary_checksum('Pal')

How to select Duplicate Rows from a table
select email,count(*) from finaldatabase_Amit group by email having count(*)>1

Insert rows/records from one table to another table
insert into tbl_logo_Info_event (ImageURL_link,ImageURL_Show, Image_Name,Image_Title, Image_Profile) select ImageURL_link,ImageURL_Show,Image_Name,Image_Title,Image_Profile from tbl_logo_Info where ImageID='16'

Select two random rows from SQL server database table
Select top 2 * from tbl_news order by newid()

Select desired rows from table by IN Keyword
select * from tbl_logo_Info where ImageID IN(4,6,10) order by image_Title asc

Searching in a column of any table by a substring
Example : Searching 'Independence day' text in column name leave_desc for 'epen' substring.
SELECT leave_desc FROM tbl_calendar WHERE substring(leave_desc, 4, 4) = 'epen'

Retrieve Second highest salary from EMP table
SELECT MIN(Salary) FROM EMP WHERE
Salary IN (SELECT distinct TOP 2 Salary FROM EMP ORDER BY Salary DESC)

OR

SELECT Salary from EMP e
where 2 =(select count(Salary) Salary from EMP
where e.Salary <= Salary) as well as.... Third highest salary
SELECT MIN(Salary) FROM EMP WHERE
Salary IN (SELECT distinct TOP 3 Salary FROM EMP ORDER BY Salary DESC)

OR

SELECT TOP 1 ID FROM (SELECT DISTINCT TOP 2 ID FROM EMPNAME ORDER BY ID desc) a ORDER BY ID asc


Fourth Highest salary
SELECT MIN(Salary) FROM EMP WHERE
Salary IN (SELECT distinct TOP 4 Salary FROM EMP ORDER BY Salary DESC)

Creating new table from other tables
Creating new table(without records) from other tables
SELECT tbl_adminuser.pass,tbl_test.id,tbl_test.address INTO third_tab FROM tbl_adminuser,tbl_test WHERE 1 = 2

Creating new table(with records) from other tables
SELECT tbl_adminuser.pass,tbl_test.id,tbl_test.address INTO third_tab FROM tbl_adminuser,tbl_test WHERE tbl_test.id > 20

Insert rows/records to new table from two or more table's query result set
Select * into approved_nominations from (select * from Approved_DL_bestSchool union all select * from Approved_DL_bestuniversity union all select * from Approved_DL_bstEnggCollege) as alias_tbl_name