Scribd
Upload
19 views4 pages

SQL Complete Notes

The document covers SQL concepts including various types of joins (INNER, LEFT, RIGHT, FULL OUTER) with examples, aggregate functions (COUNT, SUM, AVG, MAX, MIN) and their usage in queries. It also explains the GROUP BY and HAVING clauses for grouping data and filtering results, as well as the ORDER BY clause for sorting. Additionally, it introduces the COALESCE function for handling NULL values in SQL queries.

Uploaded by

gsitharthan283
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
19 views4 pages

SQL Complete Notes

The document covers SQL concepts including various types of joins (INNER, LEFT, RIGHT, FULL OUTER) with examples, aggregate functions (COUNT, SUM, AVG, MAX, MIN) and their usage in queries. It also explains the GROUP BY and HAVING clauses for grouping data and filtering results, as well as the ORDER BY clause for sorting. Additionally, it introduces the COALESCE function for handling NULL values in SQL queries.

Uploaded by

gsitharthan283
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

SQL Joins, Aggregation, NULL Handling, Grouping, and COALESCE

1. SQL JOINS - Syntax and Examples

INNER JOIN:

SELECT Students.Name, Departments.DeptName

FROM Students

INNER JOIN Departments ON Students.DeptID = Departments.DeptID;

LEFT JOIN:

SELECT Students.Name, Departments.DeptName

FROM Students

LEFT JOIN Departments ON Students.DeptID = Departments.DeptID;

RIGHT JOIN:

SELECT Students.Name, Departments.DeptName

FROM Students

RIGHT JOIN Departments ON Students.DeptID = Departments.DeptID;

FULL OUTER JOIN:

SELECT Students.Name, Departments.DeptName

FROM Students

FULL OUTER JOIN Departments ON Students.DeptID = Departments.DeptID;

Output Example:

| Name | DeptName |

|--------|--------------|

| Asha | Computer Sci |

| Ravi | Electronics |

| Meena | NULL |

| NULL | Mechanical |

2. Aggregate Functions - Definition, Syntax, and Output


SQL Joins, Aggregation, NULL Handling, Grouping, and COALESCE

Functions: COUNT(), SUM(), AVG(), MAX(), MIN()

Query:

SELECT COUNT(Age) AS Total_Age_Entries, COUNT(*) AS Total_Students,

SUM(Age) AS Total_Age, AVG(Age) AS Average_Age,

MAX(Age) AS Oldest, MIN(Age) AS Youngest

FROM Students;

Sample Output:

| Total_Age_Entries | Total_Students | Total_Age | Average_Age | Oldest | Youngest |

|-------------------|----------------|-----------|--------------|--------|----------|

|4 |5 | 82 | 20.5 | 22 | 19 |

3. Aggregation over Two Tables - JOIN + GROUP BY

Query:

SELECT D.DeptName, COUNT(S.StudentID) AS Num_Students

FROM Students S

JOIN Departments D ON S.DeptID = D.DeptID

GROUP BY D.DeptName;

Output:

| DeptName | Num_Students |

|---------------|--------------|

| Computer Sci | 3 |

| Electronics | 1 |

| Mechanical |1 |

4. GROUP BY and HAVING - Definition, Syntax, and Example


SQL Joins, Aggregation, NULL Handling, Grouping, and COALESCE

Definition: GROUP BY groups rows to apply aggregate functions. HAVING filters the result groups.

Syntax:

SELECT column, AGG_FUNC(column2)

FROM table

GROUP BY column

HAVING condition;

Example:

SELECT DeptID, AVG(Marks) AS AvgMarks

FROM Students

GROUP BY DeptID

HAVING AVG(Marks) > 80;

Output:

| DeptID | AvgMarks |

|--------|----------|

| 101 | 87.67 |

5. ORDER BY - Definition and Syntax

Definition: ORDER BY sorts result rows in ascending or descending order.

Syntax:

SELECT column1 FROM table ORDER BY column1 [ASC|DESC];

Example:

SELECT Name, Marks FROM Students ORDER BY Marks DESC;

6. COALESCE() - Definition, Syntax, and Example


SQL Joins, Aggregation, NULL Handling, Grouping, and COALESCE

Definition: Returns the first non-null value from a list.

Syntax:

COALESCE(expr1, expr2, ..., exprN)

Example:

SELECT Name, COALESCE(Phone, 'No Phone') AS Contact

FROM Students;

Output:

| Name | Contact |

|--------|--------------|

| Asha | 9876543210 |

| Ravi | No Phone |

| Meena | 8899001122 |

You might also like