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

How to use LEFT OUTER JOIN in SQL Query

LEFT OUTER JOIN Syntax

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

Example

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

OR

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

How to use SUM() function in SQL Query

SUM() function return total sum of numeric, int, float, bigint value

SUM() Syntax

SELECT SUM(YourNumericValueColumn) WHERE YourTableName

Example

SELECT SUM(Salary) FROM Employee

How to user AVG() function in SQL Query

AVG() function return average value from table

AVG() Syntax

SELECT AVG(YourColumnName) FROM YourTableName

Example

SELECT AVG(Salary) FROM Employee

SQL DELETE Query

DELETE query is delete all existing record from table with condition or without condition

DELETE Syntax

DELETE FROM YourTableName WHERE Condition

Example

1. Delete specific record form table
DELETE FROM City WHERE CityID = 1

1. Delete all record from table
DELETE FROM City

SQL UPDATE Query

UPDATE Statement is use for change values in existing record

UPDATE Syntax

UPDATE YourTableName SET Column1 = Value1 WHERE Condition for update record

Example

1. Single Record Update
- UPDATE City SET CityName = 'Test 2' WHERE ID = 1

2. Multiple Record Update
- UPDATE Student SET Standard = 2 WHERE Standard = 1

3. Update all record in table
- UPDATE Student SET Standard = 1