How to use SQL EXISTS operator

EXISTS operator is use for check record exists or not in table and EXISTS operator return true if table contains more than one record

EXISTS Syntax

SELECT ColName FROM TableName WHERE EXISTS (SELECT ColName FROM TableName)

Example

1. IF EXISTS (SELECT TOP 1 CityName FROM CityMaster WHERE CityName = 'TEST')

How to assign grade student mark wise in SQL

Description : In this post how to apply a grade in student table using case when condition

Step 1 : Write below query from update rank in student table

UPDATE tblStudent SET tblStudent.Grade = CASE
    WHEN Mark BETWEEN 0 AND 100 THEN 'C'
    WHEN Mark BETWEEN 101 AND 120 THEN 'B'
    WHEN Mark BETWEEN 121 AND 150 THEN 'A'
    ELSE 'A+' END
FROM tblStudent AS St

Step 2 : Select student table for check result

SELECT * FROM tblstudent

Result is

Student1        100    C
Student2        120    B
Student3        100    C
Student4    150    A
Student5    150    A
Student6    160    A+
Student7    135    A
Student8    129    A
Student9    155    A+

How to use SQL HAVING

HAVING is use with GROUP BY because WHERE condition not use with query

HAVING Syntax

SELECT ColName
FROM TableName
WHERE ColName
GROUP BY ColName
HAVING Condition
ORDER BY ColName

Example

SELECT COUNT(EmpID) FROM EmployeeMaster
GROUP BY Country
HAVING COUNT(EmpID) > 1

above query return number of employee in each country (only add countries with more than 1 employee)

How to use SQL GROUP BY

GROUP BY statement is use with SQL Aggregate function like COUNT, MIN, MAX, AVG, SUM for get result in one or more column or row

GROUP BY Syntax

SELECT ColName
FROM TableName
WHERE Condition
GROUP BY ColName
ORDER BY ColName

Example

1. SELECT COUNT(CountryName) FROM City GROUP BY CountryName

2. SELECT COUNT(CountryName) FROM City GROUP BY CountryName ORDER BY COUNT(CountryName)

How to use SQL UNION

UNION is use for join multiple SELECT query in one result

Note :

1. Each column match with Each select query column

2. Each column datatype match with Each select query column

3. Each column order match with each select query

UNION Syntax

SELECT YourColumnName FROM YourTableName
UNION
SELECT YourColumnName FROM YourTableName

Example

SELECT CityName FROM Employee
UNION
SELECT CityName FROM Manager
ORDER BY CityName

How to use SELF JOIN in SQL Query

SELF JOIN Syntax

SELECT YourColumnName FROM YourTableName T1, SelfJoinTableName T2 WHERE Condition

Example

SELECT t1.CityName, t2.CountryName FROM City t1, Country t2
WHERE t1.CountryName = t2.CountryName
ORDER BY t2.CountryName

How to use RIGHT OUTER JOIN in SQL Query

RIGHT OUTER JOIN Syntax

SELECT YourColumnName FROM YourTableName
RIGHT OUTER JOIN InnerTableName ON InnerTableName.Column = YourTableName.Column

Example

SELECT * FROM City
RIGHT JOIN Country ON Country.CountryID = City.CountryIDF

OR

SELECT * FROM City
RIGHT OUTER JOIN Country ON Country.CountryID = City.CountryIDF