modul:m290:learningunits:lu04:theorie:d_complexselects

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290:learningunits:lu04:theorie:d_complexselects [2024/09/04 13:33] – vdemirmodul:m290:learningunits:lu04:theorie:d_complexselects [2024/10/17 12:34] (aktuell) – vdemir
Zeile 1: Zeile 1:
-====== LU04d - SQL-DQL: Selects with Aggregate Functions ======+====== LU06d - SQL-DQL: Selects with Aggregate Functions ======
 SQL (Structured Query Language) provides powerful tools to perform calculations and data analysis on database records. Among these tools, aggregate functions play a crucial role in summarizing data by performing operations on multiple rows and returning a single result. This document focuses on five commonly used SQL aggregate functions: **MIN, MAX, COUNT, SUM, and AVG**, along with practical examples to illustrate their usage. SQL (Structured Query Language) provides powerful tools to perform calculations and data analysis on database records. Among these tools, aggregate functions play a crucial role in summarizing data by performing operations on multiple rows and returning a single result. This document focuses on five commonly used SQL aggregate functions: **MIN, MAX, COUNT, SUM, and AVG**, along with practical examples to illustrate their usage.
  
-===== 1. MIN Function ====+===== 1. MIN Function====
 The **MIN** function returns the smallest value in a specified column. This is particularly useful when you need to find the lowest value in a dataset, such as the minimum price of a product or the earliest date in a list of events. The **MIN** function returns the smallest value in a specified column. This is particularly useful when you need to find the lowest value in a dataset, such as the minimum price of a product or the earliest date in a list of events.
  
Zeile 13: Zeile 13:
  
 ==== 2. MAX Function ==== ==== 2. MAX Function ====
-The MAX function works oppositely to MIN, returning the largest value in a specified column. It’s commonly used to find the highest values, such as the maximum salary in a company or the latest date in a schedule.+The **MAX** function works oppositely to MIN, returning the largest value in a specified column. It’s commonly used to find the highest values, such as the maximum salary in a company or the latest date in a schedule.
  
 **Example:** **Example:**
Zeile 22: Zeile 22:
 Here, the query retrieves the highest salary from the "employees" table. Here, the query retrieves the highest salary from the "employees" table.
  
-==== 3. COUNT Function ==== +==== 3. AVG (Average) Function ==== 
-The COUNT function counts the number of rows that match a specified condition or simply counts all rows in a column. This is useful for determining the size of datasets, such as counting the number of orders placed by a customer or the total number of employees in a department.+The **AVG** function calculates the average value of a numeric column. This is useful for finding the mean value in a dataset, such as the average test score of students or the average price of products in a store. 
 + 
 +**Example:** 
 + 
 +  SELECT AVG(price) AS AveragePrice 
 +  FROM products; 
 + 
 +Here, the query returns the average price of all products in the "products" table. 
 + 
 +==== 4. COUNT Function ==== 
 +The **COUNT** function counts the number of rows that match a specified condition or simply counts all rows in a column. This is useful for determining the size of datasets, such as counting the number of orders placed by a customer or the total number of employees in a department.
  
 **Example:** **Example:**
Zeile 33: Zeile 43:
 This query returns the total number of orders placed by a customer with customer_id 123. This query returns the total number of orders placed by a customer with customer_id 123.
  
-==== 4. SUM Function ==== +==== 5. SUM Function ==== 
-The SUM function adds up all the values in a specified numeric column. It’s often used for calculating totals, such as the total sales revenue or the sum of all expenses in a financial report.+The **SUM** function adds up all the values in a specified numeric column. It’s often used for calculating totals, such as the total sales revenue or the sum of all expenses in a financial report.
  
 **Example:** **Example:**
Zeile 43: Zeile 53:
 In this example, the query calculates the total revenue by multiplying the quantity of items sold by their price and summing the results. In this example, the query calculates the total revenue by multiplying the quantity of items sold by their price and summing the results.
  
-==== 5. AVG (Average) Function ==== +==== 6. Combining Aggregate Functions with GROUP BY ====
-The AVG function calculates the average value of a numeric column. This is useful for finding the mean value in a dataset, such as the average test score of students or the average price of products in a store. +
- +
-**Example:** +
- +
-  SELECT AVG(price) AS AveragePrice +
-  FROM products; +
- +
-Here, the query returns the average price of all products in the "products" table. +
- +
-==== Combining Aggregate Functions with GROUP BY ====+
 While each of these functions is powerful on its own, their true potential is unlocked when combined with the GROUP BY clause. This clause allows you to apply aggregate functions to specific groups of data, such as calculating the total sales per region or the average salary per department. While each of these functions is powerful on its own, their true potential is unlocked when combined with the GROUP BY clause. This clause allows you to apply aggregate functions to specific groups of data, such as calculating the total sales per region or the average salary per department.
  
Zeile 67: Zeile 67:
 ===== Vocabulary ===== ===== Vocabulary =====
 ^ English ^ German ^ ^ English ^ German ^
-| oppositely | ... | +| oppositely | gegenüberliegend |  
 +| to perform | ausführen, durchführen | 
 +| to aggregate | vermengen, auswerten |  
 +| commonly | allgemein | 
 +| dataset | Datensatz | 
 +| expense | Kosten, Ausgaben | 
 +| revenue | Ertrag, Einnahmen | 
 +| mean value | Durschnittswert |  
 +| to apply | anwenden |
  
  
  • modul/m290/learningunits/lu04/theorie/d_complexselects.1725449634.txt.gz
  • Zuletzt geändert: 2024/09/04 13:33
  • von vdemir