Chapter 6: Advanced Queries Using SQL
1. Aggregate functions
2. Set operations
3. Order by, Group by, Having clauses
4. SQL mechanisms for joining relations
(inner joins, outer joins and their types)
5. Nested queries (Case studies)
SQL Aggregate Functions
1. SQL aggregation function is used to perform the
calculations on multiple rows of a single column of a
table.
2. It returns a single value.
3. It is also used to summarize the data.
Types of SQL Aggregation Function
1. COUNT FUNCTION: -
1. COUNT function is used to Count the number of rows in a database
table. It can work on both numeric and non-numeric data types.
2. COUNT function uses the COUNT(*) that returns the count of all the
rows in a specified table. COUNT(*) considers duplicate and Null.
Syntax
[Link](*)
OR
[Link]( [ALL|DISTINCT] expression )
2. SUM Function: -
1. Sum function is used to calculate the sum of all selected columns.
It works on numeric fields only.
Syntax
1. SUM(Column Name)
OR
2. SUM( [ALL|DISTINCT] expression )
3. AVG function
1. The AVG function is used to calculate the average value of the numeric
type. AVG function returns the average of all non-Null values.
Syntax
1. AVG()
OR
2. AVG( [ALL|DISTINCT] expression )
4. MAX function
1. MAX function is used to find the maximum value of a certain column.
This function determines the largest value of all selected values of a column.
Syntax
1. MAX()
OR
2. MAX( [ALL|DISTINCT] expression )
4. MIN function
1. MIN function is used to find the minimum value of a certain column.
This function determines the smallest value of all selected values of a column.
Syntax
1. MIN()
OR
2. MIN( [ALL|DISTINCT] expression )
SQL Set Operation
The SQL Set operation is used to combine the two or more SQL SELECT
statements.
Types of Set Operation: -
[Link]
[Link]
[Link]
[Link]
1. UNION: -
1. The SQL Union operation is used to combine the result of two or more
SQL SELECT queries.
2. In the union operation, all the number of datatype and columns must
be same in both the tables on which UNION operation is being applied.
3. The union operation eliminates the duplicate rows from its result set.
Syntax: -
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
2. UNION ALL: -
1. Union All operation is equal to the Union operation. It returns the set
without removing duplication and sorting the data.
Syntax: -
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;
3. INTERSECT: -
1. It is used to combine two SELECT statements. The Intersect operation returns
the common rows from the SELECT statements.
2. In the Intersect operation, the number of datatype and columns must be the
same.
3. It has no duplicates, and it arranges the data in ascending order by default.
Syntax: -
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;
4. MINUS: -
1. It combines the result of two SELECT statements.
2. Minus operator is used to display the rows which are present in the first query
but absent in the second query.
3. It has no duplicates and data arranged in ascending order by default.
Syntax: -
SELECT column_name FROM table1
MINUS
SELECT column_name FROM table2;