Related Topics
Database Management System
SELECT *
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');
In this example, the subquery (SELECT department_id FROM departments WHERE location = 'New York') retrieves the department IDs of departments located in New York. The outer query then selects all employees whose department IDs match the result of the subquery.
Subqueries provide a powerful way to perform complex queries, combining and manipulating data from multiple tables or result sets. They allow you to break down complex problems into smaller parts, enabling you to write more efficient and readable SQL queries.
SELECT employee_id, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM employees;
In this example, the window function AVG(salary) OVER (PARTITION BY department_id) calculates the average salary within each department. The result is displayed for each employee in the result set, showing the average salary of their respective department.
Window functions provide a powerful way to analyze and compare data within a specified window or group. They can simplify complex calculations and provide valuable insights into the data. The flexibility of window functions makes them a useful tool for data analysis, reporting, and decision-making in SQL.
Popular Category
Topics for You
Go through our study material. Your Job is awaiting.