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

1CREATE TABLE employees (
2  employee_id INT PRIMARY KEY,
3  name VARCHAR(50),
4  age INT
5);

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.

1CREATE TABLE customers (
2  customer_id INT,
3  name VARCHAR(50),
4  email VARCHAR(100) NOT NULL
5);

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.

1CREATE TABLE products (
2  product_id INT,
3  product_code VARCHAR(10) UNIQUE,
4  name VARCHAR(50)
5);

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.

1CREATE TABLE students (
2  student_id INT,
3  name VARCHAR(50),
4  age INT CHECK (age >= 18)
5);

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.

1CREATE TABLE customers (
2  customer_id INT PRIMARY KEY,
3  name VARCHAR(50)
4);
5
6
7CREATE TABLE orders (
8  order_id INT,
9  order_date DATE,
10  customer_id INT,
11  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
12);

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.

1SELECT column1
2FROM table1
3UNION
4SELECT column1
5FROM table2;
6

1-- Create table for web developers
2CREATE TABLE Developers (
3    DeveloperID INT PRIMARY KEY,
4    Name VARCHAR(100) NOT NULL,
5    Email VARCHAR(100) UNIQUE,
6    SkillID INT
7);
8
9-- Insert sample data into Developers table
10INSERT INTO Developers (DeveloperID, Name, Email, SkillID) VALUES
11(1, 'Harjas Singh', 'harjas@example.com', 3),
12(2, 'Mahak Chugh', 'mahak@example.com', 4),
13(3, 'Jasraj Singh', 'jasraj@example.com', 1);
14
15-- Create another table for developers
16CREATE TABLE OtherDevelopers (
17    DeveloperID INT PRIMARY KEY,
18    Name VARCHAR(100) NOT NULL,
19    Email VARCHAR(100) UNIQUE,
20    SkillID INT
21);
22
23-- Insert sample data into OtherDevelopers table
24INSERT INTO OtherDevelopers (DeveloperID, Name, Email, SkillID) VALUES
25(4, 'Sehaj Bindra', 'sehaj@example.com', 2),
26(5, 'Japanjot Singh', 'japanjot@example.com', 3);
27
28-- UNION query to combine data from both tables
29SELECT * FROM Developers
30UNION
31SELECT * FROM OtherDevelopers;

• 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:

1SELECT column1
2FROM table1
3INTERSECT
4SELECT column1
5FROM table2;
6

1-- INTERSECT query to find common developers in both tables
2SELECT * FROM Developers
3INTERSECT
4SELECT * FROM OtherDevelopers;

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:

1SELECT column1
2FROM table1
3EXCEPT
4SELECT column1
5FROM table2;
6

1-- EXCEPT query to find developers only in the Developers table
2SELECT * FROM Developers
3EXCEPT
4SELECT * FROM OtherDevelopers;

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.

1SELECT customer_id, product, SUM(quantity) AS total_quantity
2FROM orders
3GROUP BY customer_id, product;

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.

1SELECT customer_id, SUM(quantity) AS total_quantity
2FROM orders
3GROUP BY customer_id
4HAVING SUM(quantity) >= 100;

Conclusion