A subquery (also known as a nested query) is an SQL query that is embedded inside another SQL query. Explanation: Example: Find employees who earn more than the average salary in the company. sqlCopyEditSELECT name, salary FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees); Here, the subquery (SELECT AVG(salary) FROM Employees) calculates the average…

An OUTER JOIN retrieves all records from one or both tables, even if there are no matches, by filling missing values with NULLs. Explanation: There are three types of outer joins: Example: SQL Query for LEFT OUTER JOIN: sqlCopyEditSELECT Employees.name, Departments.department_name FROM Employees LEFT OUTER JOIN Departments ON Employees.dept_id = Departments.dept_id; Output: name department_name Alice…

An INNER JOIN in SQL returns only the rows where there is a match in both tables based on a specified condition. Explanation: Example: Consider two tables:Employees Table emp_id name dept_id 1 Alice 10 2 Bob 20 3 Charlie 30 Departments Table dept_id department_name 10 HR 20 IT SQL Query for INNER JOIN: sqlCopyEditSELECT Employees.name,…

A natural join is a type of join that automatically matches columns with the same name in both tables and returns rows with equal values. Explanation: Example SQL Natural Join: sqlCopyEditSELECT Employees.name, Departments.department_name FROM Employees NATURAL JOIN Departments; Here, Employees and Departments must have a common column (e.g., department_id). Summary Table Concept Definition Stored Procedure…

A cross join produces the Cartesian product of two tables, returning all possible combinations of rows from both tables. Explanation: Example SQL Cross Join: sqlCopyEditSELECT A.name, B.course_name FROM Students A CROSS JOIN Courses B; This returns every student paired with every course.

A join is an operation in SQL that combines rows from two or more tables based on a related column. Explanation: Joins allow fetching related data across tables. The most common types include: Example INNER JOIN: sqlCopyEditSELECT Employees.name, Departments.department_name FROM Employees INNER JOIN Departments ON Employees.department_id = Departments.department_id;

Relational algebra is a mathematical foundation for relational databases, defining operations to manipulate and retrieve data. Explanation: Relational algebra provides fundamental operations such as: Example in SQL: sqlCopyEditSELECT * FROM Employees WHERE department = ‘HR’; sqlCopyEditSELECT name, salary FROM Employees;

OLAP is a technology that allows users to analyze multidimensional data interactively from different perspectives for business intelligence and decision-making. Explanation: Example OLAP Query (Aggregate Sales by Region and Year): SELECT region, year, SUM(sales) FROM SalesData GROUP BY region, year;

OLTP is a database system that manages transaction-oriented applications, handling multiple short online transactions efficiently. Explanation: Example OLTP Query (Bank Transaction): sqlCopyEditSTART TRANSACTION; UPDATE Accounts SET balance = balance – 500 WHERE account_id = 1; UPDATE Accounts SET balance = balance + 500 WHERE account_id = 2; COMMIT;

Data mining is the process of discovering patterns, correlations, and useful insights from large datasets using statistical, machine learning, and database techniques. Explanation: Example of Association Rule Mining (Market Basket Analysis): If customers buy “bread” and “butter,” they are likely to buy “milk.”