Integrity constraints , Aggregate & Comparison operators in DBMS

What are Constraints?

Integrity Constraints in SQL

• Integrity constraints are rules defined on tables to ensure the accuracy, consistency, and validity of data.
• They help maintain the integrity and quality of the database.
• Here are some common integrity constraints:
• Primary Key
• Not NULL
• Unique
• Check
• Referential Key

a. Primary Key

b. Not NULL

• The not-null constraint ensures that null values cannot exist in a column.
• It enforces the requirement for data presence in that column.

c. Unique

• The unique constraint ensures that each value in a specified column or combination of columns is unique across the table.
• It prevents duplicate values from being inserted.

d. Check

• The check constraint lets users create specific rules that limit the values allowed to be added or changed in a column.
• It ensures that the data satisfies specific conditions.

e. Referential Key

• The referential key (foreign key) constraint establishes a relationship between two tables based on a column's values.
• It ensures data consistency and maintains referential integrity.

What are Nested Queries?

• Nested queries, also known as subqueries, are queries within other queries.
• They allow users to perform complex operations by nesting one query inside another.
• The inner query is executed first, and its result is used as input for the outer query.
• This powerful feature provides flexibility and enables users to retrieve data based on multiple conditions and criteria.

What are Correlated Nested Queries

• Correlated nested queries are a special type of nested query where the inner query depends on the values from the outer query.
• The inner query is executed for each row of the outer query, dynamically evaluating the condition based on the current row's values.
• Correlated nested queries are useful when data retrieval requires information from both the outer and inner queries.

Set-Comparison Operators in SQL

• Set-comparison operators in SQL are used to compare values within sets.
• They help users perform operations like finding common elements, determining differences, or validating data against a set of values.
• Here are some commonly used set-comparison operators:
• UNION
• INTERSECT
• EXCEPT

a. UNION

• A single result set is created by combining the result sets of two or more SELECT statements using the UNION operator.
• It removes duplicate rows by default.

• In this example, we have two tables, Developers and OtherDevelopers, containing data about web developers.
• The UNION query combines the data from both tables and retrieves distinct rows, removing duplicates.

b. INTERSECT

• The INTERSECT operator retrieves the common records from two or more SELECT statements.
• It returns only the rows that exist in all the SELECT statements.
• Syntax:

c. EXCEPT

• The EXCEPT operator retrieves the rows that exist in the first SELECT statement but not in subsequent SELECT statements.
• It gives back the variation or discrepancy between the sets of results.
• Syntax:

Aggregate Operators

• Aggregate operators in SQL allow users to perform calculations on groups of rows rather than individual rows.
• They are used in conjunction with the GROUP BY and HAVING clauses.

a. GROUP BY

• The GROUP BY clause groups the rows based on specified columns, creating subsets of data.
• Aggregate functions can then be applied to these groups to calculate summaries or perform calculations.
• Let's assume we have a table called "orders" with columns "customer_id", "product", and "quantity".
• We need to find out how many of each product each customer has ordered in total.
• We can use the GROUP BY clause to group the rows by "customer_id" and
• "product" and then apply the SUM aggregate function to calculate the total quantity.

b. HAVING

• In order to filter the grouped data based on specific conditions, the GROUP BY clause and the HAVING clause are used together.
• It allows users to specify conditions for the aggregated data.
• Using the previous example, let's say we want to retrieve only those customers who have ordered a total quantity greater than or equal to 100.
• We can use the HAVING clause to filter the grouped data based on the aggregated result.

Conclusion