The grouping functions are used by Management Systems Database so that operate on rowsets to give one result per group. There are two types of functions: single row functions and aggregate functions. The fundamental difference is that while the former perform the action on a single row at a time, the grouping ones obtain a result from a set of elements. It is about selecting a set of elements, filtering them by the conditions that we believe appropriate and obtaining a result from it.
Summary
[ hide ]
- 1 Primitive aggregation functions
- 2 Multiple Queries
- 1 Example
- 3 Other syntax
- 1 Description of possibilities
- 4 Sources
Primitive aggregation functions
- avg– Computes the arithmetic mean of an attribute or numeric expression .
- min– Returns the minimum of an attribute or numeric expression.
- max: calculates the maximum value of an attribute or numerical expression.
- sum: returns the total sum of attributes or numeric expressions.
- count(*): tuple counter .
- count (distinct): is a partial tuple counter, does not account for null or duplicate values.
Multiple queries
It is more than usual to need in a query data that is distributed in several tables. The relational databases are based on data that are distributed in tables that WILL be linked through a field . This field is the one that allows integrating the data from the tables.
For example, if we have an employee table whose key is the ID and another task table that refers to tasks performed by employees, it is safe (if the design is well done) that the employee ID will appear in the task table to find out who the employee was, he performed the task. If you want to get a list of job and employee data, you could do it this way:
SELECT task_code, task_description, employee_id, employee_name FROM tasks, employees;
The syntax is correct since, in fact, several tasks can be indicated in the FROM section separated by commas. But that produces a cross product, all task records related to all employee records will appear. The Cartesian product is sometimes useful for making complex queries, but in the normal case it is not, we need to discriminate that product so that only the records of the tasks related to their corresponding employees appear. This is called associating (JOIN) tables.
Example
The way to correctly perform the previous query (associated tasks with the employees who made it would be:
SELECT task_code, task_description, employee_id, employee_name FROM tasks, employees WHERE tasks.id_employee = employees.id;
Note that the column table notation is used to avoid ambiguity , since the same field name can be repeated in both tables. To avoid continuously repeating the table name, a table alias can be used:
SELECT a.cod_tarea, a.descripcion_tarea, b.id_empleado, b.nombre_empleado FROM tasks a, employees b WHERE a.id_empleado = b.id;
Conditions can be added to the WHERE section by chaining them with the AND operator: SELECT a.cod_tarea, a.descripcion_tarea FROM tasks a, employees b WHERE a.id_empleado = b.id AND b.nombre_empleado = ‘Javier’;
Finally, indicate that more than two tables can be linked through their related fields: SELECT a.cod_tarea, a.descripcion_tarea, b.name_employee, c.name_username FROM tasks a, employees b, utensils_used c WHERE a.dni_empleado = b.dni AND a.cod_tarea = c.cod_tarea;
The relationships described above are called equal relationships (equijoins), since tables are related through fields that contain equal values in two tables.
In this example we could find out the category to which each employee belongs, but these tables have a relationship that is no longer equal. The form would be:
SELECT a.employee, a.payment, b.category FROM employees a, categories b WHERE a.payment between b.minimum_payment and b.maximum_payment;
In the previously seen example of tasks and employees. It could happen that an employee had not yet performed a task, so there would be employees who would not appear in the query because they did not have a related task. The way to get all the records of a table to come out even if they are not related to another, is to make a side association or external join (also called outer join). In those associations, the sign (+) indicates that all the records in the table are desired whether or not they are related.
SELECT table1.column1, table1.column2,… table2.column1, table2.column2, … FROM table1, table2 WHERE table1 .Relatedcolumn (+) = table2.Relatedcolumn
That gets the related records between the tables and also the unrelated records from table2. You could use this other way:
SELECT table1.column1, table1.column2,… table2.column1, table2.column2, … FROMtable1, table2 WHEREtab la1 .columnRelated = table2.columnRelated (+)
In this case, those related and those in the first table that are not related to any of the first are displayed.
Other syntax
In the 1999 SQL version, a new syntax was devised to query multiple tables. The reason was to separate the association conditions from the registry selection conditions. The complete syntax is:
SELECT table1.column1, tabl1.column2,… table2.column1, table2.column2,… FROM table1 [ CROSS JOIN table2] | [ NATURAL JOIN tabla2] | [ JOIN table2 USING (column)] | [JOIN table2 ON (table1.colum = table2.column)] | [LEFT | RIGHT | FULL OUTER JOIN table2 ON (table1.colum = table2.column)]
Description of possibilities
- CROSS JOIN: Using the CROSS JOIN option, a cross product is performed between the indicated tables.
- NATURAL JOIN: It establishes an equality relationship between the tables through the fields that have the same name in both tables:
SELECT * FROM pieces NATURAL JOIN stock; In the previous example, the records of related parts in stock are obtained through the fields that have the same name in both tables.
- JOIN USING: It allows establishing relationships indicating which field (or fields) common to the two tables to use: SELECT * FROM pieces JOIN stocks USING (type, model);
- JOIN ON: Allows you to establish relationships whose condition is set manually, allowing you to make more complex associations or associations whose fields in the tables do not have the same name: SELECT * FROM pieces JOIN stocks ON (pieces.type = stocks.type AND pieces. model = stock.model);
The last possibility is to obtain lateral or external relations (outer join). For this, the syntax is used: SELECT * FROM pieces LEFT OUTER JOIN stocks ON (pieces.type = stocks.type AND pieces.model = stocks.model);
In this query, in addition to the related ones, the unrelated parts appear in stock. If we change the LEFT to a RIGHT, the stocks not present in pieces will appear. The FULL OUTER JOIN condition would produce a result that shows unrelated records from both tables.
