DATABASE MANAGEMENT SYSTEM

In SQL, relations refer to how data from multiple tables is combined or derived to create a meaningful result set. There are primarily two types of relations: joined relations and derived relations.

1. Joined Relations:

Joined relations involve combining data from two or more tables based on a related column. SQL provides several types of joins to accomplish this:

INNER JOIN:

The INNER JOIN returns only the rows where there is a match in both tables based on the specified condition.

SELECT *
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

LEFT JOIN (or LEFT OUTER JOIN):

The LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

SELECT *
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

RIGHT JOIN (or RIGHT OUTER JOIN):

The RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for columns from the left table.

SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

FULL JOIN (or FULL OUTER JOIN):

The FULL JOIN returns all rows when there is a match in either the left or right table. If there is no match, NULL values are returned for columns from the table without a match.

SELECT *
FROM table1
FULL JOIN table2 ON table1.column_name = table2.column_name;

2. Derived Relations:

Derived relations involve creating a new set of data based on the result of a query or operation. This can include using sub-queries, aggregations, or transformations to derive meaningful information.

Sub-Queries:

Sub-queries are queries embedded within another query. They can be used to derive data dynamically.

Example:

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'IT');

Aggregations:

Aggregations involve deriving summary information from a set of rows, often using functions like COUNT, SUM, AVG, etc.

Example:

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Transformations:

Derived relations can involve transforming or manipulating data in various ways, such as using functions or calculations.

Example:

SELECT employee_name, salary * 1.1 AS increased_salary
FROM employees;