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

Join query in detail for SQL Server

We have three types of Joins in sql.

1. Inner join
2. Outer join
3. Cross join

1. Inner join:
Inner join is the default type of join, it will produces the result set, which contains matched rows only.

We have three types of Joins in INNER JOIN

a. Self Join
b. Equi Join
c. Natural join

a. Self Join:
A join joins with itself is called self join, working with self joins we use alias tables.

b. Equi Join:
DISPLAYS ALL THE MATHCING ROWS FROM JOINED TABLE. AND ALSO DISPLAYS REDUNDANT VALUES. IN THIS WE USE * SIGN TO JOIN THE TABLE.

Example.
Create table EMPNAME (ID int, EMPNAME varchar(20))
CREATE table EMPADDRESS (ID int, ADDRESS varchar(20))

INSERT INTO EMPNAME VALUES(1, 'DEEPAN')
INSERT INTO EMPNAME VALUES(2, 'RAMESH')
INSERT INTO EMPNAME VALUES(3, 'PRADEEP')
INSERT INTO EMPADDRESS VALUES(1, 'BANGALORE')
INSERT INTO EMPADDRESS VALUES(2, 'DELHI')
INSERT INTO EMPADDRESS VALUES(4, 'DELHI')

SELECT * FROM EMPNAME
ID EMPNAME
----------- --------------------
1 DEEPAN
2 RAMESH
3 PRADEEP

SELECT * FROM EMPADDRESS
ID ADDRESS
----------- --------------------
1 BANGALORE
2 DELHI
4 DELHI

Equi Join example
SELECT * FROM EMPNAME
JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

c. Natural Join
DISPLAYS ALL THE MATHCING ROWS FROM
JOINED TABLE.IT RESTRICT
REDUNDANT VALUES.

Example:
SELECT A.*, E.ID, E.EMPNAME FROM EMPADDRESS A
INNER JOIN
EMPNAME E ON E.ID = A.ID

This natural join query will return all the columns of categories table and prodcutId and productName from products table. We can further modify this natural inner join query as per your requirements to visualize the data by specifying the column names of categories table also.

Inner Join Query Example by specifying column names:
SELECT A.ID, A.ADDRESS, E.ID, E.EMPNAME FROM EMPADDRESS A INNER JOIN
EMPNAME E ON E.ID = A.ID

This inner join query will display only the specified column names of both the tables.

Example : INNER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

OR

SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
INNER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID


Output of the above JOIN Query (i.e Inner Query)
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI

2. Outer join:
Outer join produces the results, which contains matched rows and unmatched rows.

Outer join is further classified as three types.

a. Left outer join
b. Right outer join
c. Full outer join.

a. Left outer join:
Left outer join produces the results, which contains all the rows from left table and matched rows from right table.

Example : LEFT OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
LEFT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above LEFT OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
3 PRADEEP NULL

b.Right outer join:
Right outer join produces the resultset, which contains all the rows from right table and matched rows from left table.

Example : RIGHT OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
RIGHT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above RIGHT OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
NULL NULL DELHI

c. Full outer join:
Full outer join produces the resultset, which contains all the rows from left table and all the rows from right table.

Example : FULL OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
FULL OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above FULL OUTER JOIN Query
-----------------------------------------------------
ID EMPNAME ADDRESS
----------- -------------------- --------------------
1 DEEPAN BANGALORE
2 RAMESH DELHI
3 PRADEEP NULL
NULL NULL DELHI

3. Cross join:
A join without having any condition is known as cross join, in cross join every row in first table is joins with every row in second table. Cross join is nothing but cartesian product.

Example : CROSS JOIN
===================================================================
SELECT * FROM EMPNAME CROSS JOIN EMPADDRESS