82 Effective SQL Joins, Grouping, and Aggregate Functions скачать в хорошем качестве

82 Effective SQL Joins, Grouping, and Aggregate Functions

*Effective SQL Joins, Grouping, and Aggregate Functions* SQL provides powerful tools for retrieving and summarizing data across multiple tables using **joins**, **grouping**, and **aggregate functions**. These operations allow you to combine, organize, and analyze data in more complex ways. #### *What You’ll Learn:* 1. **SQL Joins**: *Joins* allow you to combine data from two or more tables based on related columns. This is crucial when data is spread across multiple tables, and you need to extract meaningful information. **Types of Joins**: **INNER JOIN**: Returns only the rows that have matching values in both tables. ```sql SELECT employees.first_name, departments.name FROM employees INNER JOIN departments ON employees.department_id = departments.id; ``` **LEFT JOIN (or LEFT OUTER JOIN)**: Returns all rows from the left table, and matching rows from the right table. If there’s no match, NULL values are returned for columns from the right table. ```sql SELECT employees.first_name, departments.name FROM employees LEFT JOIN departments ON employees.department_id = departments.id; ``` **RIGHT JOIN (or RIGHT OUTER JOIN)**: Similar to the left join, but returns all rows from the right table and matching rows from the left table. **FULL JOIN**: Returns rows when there’s a match in one of the tables. If no match exists, the result will contain NULL values for the non-matching table. 2. **Grouping Data with `GROUP BY`**: *`GROUP BY`* is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions to calculate summaries like totals, averages, or counts for each group. Example: Grouping employees by their department to count how many employees are in each department. ```sql SELECT department, COUNT(*) AS num_employees FROM employees GROUP BY department; ``` 3. **Aggregate Functions**: *Aggregate functions* are used to perform calculations on multiple rows of data and return a single result. Common aggregate functions include: **COUNT()**: Counts the number of rows or values. ```sql SELECT COUNT(*) FROM employees WHERE department = 'Sales'; ``` **SUM()**: Adds up the values in a column. ```sql SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department; ``` **AVG()**: Calculates the average of the values in a column. ```sql SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department; ``` **MAX()**: Returns the maximum value in a column. ```sql SELECT department, MAX(salary) AS highest_salary FROM employees GROUP BY department; ``` **MIN()**: Returns the minimum value in a column. ```sql SELECT department, MIN(salary) AS lowest_salary FROM employees GROUP BY department; ``` 4. **HAVING Clause**: The *`HAVING`* clause is used to filter groups created by the `GROUP BY` clause. Unlike the `WHERE` clause, which filters individual rows, `HAVING` filters the aggregated results. Example: Finding departments with an average salary greater than 60,000: ``` #### **Examples of Combined Queries**: 1. **Join with Aggregation**: Retrieve the total salary expense for each department: ```sql SELECT departments.name, SUM(employees.salary) AS total_salary FROM employees INNER JOIN departments ON employees.department_id = departments.id GROUP BY departments.name; ``` 2. **Filter Groups with `HAVING`**: Find departments with more than 10 employees and an average salary greater than 50,000: ```sql SELECT department, COUNT(*) AS num_employees, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING COUNT(*) 10 AND AVG(salary) 50000; ``` 3. **Multiple Aggregate Functions**: Get the total number of employees, the highest salary, and the average salary in each department: ```sql SELECT department, COUNT(*) AS num_employees, MAX(salary) AS max_salary, AVG(salary) AS avg_salary FROM employees GROUP BY department; --- **Next Steps**: Once you're comfortable with joins, grouping, and aggregate functions, you can explore: **Subqueries**: Using queries inside other queries to retrieve data more efficiently. **Window Functions**: Performing calculations across a set of table rows related to the current row (like running totals or moving averages). **Indexing and Optimization**: Improving the performance of queries, especially for large datasets. By mastering joins, grouping, and aggregation, you’ll be able to handle more complex database queries and perform powerful data analysis.