DBMS: Set Operations, Nulls, Aggregates, Group By, and Having Clause

Understanding Set Operations:

1. UNION: Combines rows from two queries.
2. INTERSECT: Returns only rows common to both queries.
3. EXCEPT: Returns rows from the first query that are not in the second.

Intersect Example:

1. By default, UNION, INTERSECT, and EXCEPT eliminate duplicates.
   
2. To keep duplicates, use UNION ALL, INTERSECT ALL, or EXCEPT ALL.
   
3. UNION ALL will not remove duplicate rows.

NULL Values in Database:

Key Points About Null Values:

1. NULL means "no value" or "unknown."
2. Arithmetic with NULL results in NULL. For example, 5 + NULL = NULL.
3. Use IS NULL to check for nulls, and IS NOT NULL to check for non-null values.

Checking for Nulls Values Example:

" This query lists instructors whose salary is unknown (NULL).

Aggregate Functions:

Understanding Aggregate Functions: Aggregate functions perform calculations on multiple rows. Key functions include:

• AVG: Calculates the average value.
• MIN: Returns the minimum value.
• MAX: Returns the maximum value.
• SUM: Adds up values.
• COUNT: Counts the number of rows.
  

Average Salary Example:

This query calculates the average salary of instructors in the Computer Science department.

Examples of Aggregate Functions:

1. Find the average salary for instructors in a specific department.
2. Count how many unique instructors taught in Spring 2010.
   

Counting Distinct Instructors Example:

Understanding GROUP BY

The GROUP BY clause groups rows that have the same values into summary rows. For example, calculating the average salary for each department.

" This query is incorrect because ID is not in the GROUP BY clause.

Using the Having Clause

HAVING is used to filter groups after GROUP BY. It works similarly to WHERE but applies to groups.

"This query returns departments where the average salary exceeds 42,000.

Summary:

• We covered set operations (UNION, INTERSECT, EXCEPT). 
• We learned how to handle NULL values. 
• We explored aggregate functions (AVG, MIN, MAX, SUM, COUNT). 
• We understood the importance of GROUP BY and HAVING clauses.