Arithmetics

It is possible to use arithmetic expressions not only in the select, clause, to obtain a new column in the result-table, but also in the creation of the rows selection conditions.

Supposing the course is divided in 2 semeters and 3 credits correspond with 1 weekly class-hour, name of the subjects and number of weekly class-hours of each one in a single semester.

select descripcion, (creditos/3)*2 horas

from asignaturas;

Subject's credit are yearly: divided in 3, we obtain the hours to teach during a school year every week; if we reduce this to a single semester, every week we will have double class-hours.

Description of the subjects and number of weekly hours for the subjects with less than 4 weekly class-hours

select descripcion, creditos

from asignaturas

where (creditos/3)*2 < 4

Functions

The whole list of availabe functions in MariaDB can be consulted in his web . Note that every DBMS can provide its own functions that can be the same as the ones managed by MariaDB or not.

Round 15.1297 to two decimals

select round(15.1297,2) redondeo;

select round(15.1297,2) redondeo from dual;

Obviously, to perform and show a calculus like this, no table is required.

Notice the two versions of the query. Dual is a dummy table for queries like this, and it is not necessary in MariaDB. Actually, it is maintained for compatibility with others DBMS (Oracle).

Aggregate functions

There are a set of aggregate functions that return calculated values over one or more columns.

The difference from the ones in the previous section is that these functions work over a set of values: they return a single value after processing a set of tuples selected through the condition specified in where; clause; in case this is not specified, the calculus is done over the whole column.

Stadistical functions need that the expression being evaluated is built from numeric columns.

The expression expr can contain the name of a column or a calculus over one or more columns.

If the keyword distinct is specified, the expression must to be a column name, and it is assumed that the function is calculated only over distinct values of the expression.

COUNT( * ) number of rows
COUNT( [DISTINCT] expr ) distinct number of values in expr
SUM( [DISTINCT] expr ) sum of all the values of expr
AVG( [DISTINCT] expr ) average of all the values in expr
MIN( expr ) the minimum value in expr
MAX( expr ) the maximum value in expr

How many teachers are there in our DB?

select count(*) profes from profesores;

How many subjects of more that 4 credits we have?

select count(*) from asignaturas

where creditos > 4;

How many distinct values for credit we have?

select count(distinct creditos) quecreditos

from asignaturas;

You can check that there are three different credit values: 6.0, 9.0 y 4.5 with the proper query:

About DISTINCT modifier

All the aggregating functions (except count(*)) ignore nulls (NULL). Therefore, select count(creditos) from asignaturas should return the number of rows in asignaturas table with no null value for credits.

select count(*) filas,

count(creditosp) valores,

count(distinct creditosp) distintos from asignaturas;

The use of the distinct modifier implies, apart from this, that duplicates are not considered. Suppose a table with 5 rows and a column colx and we want to get the average of the stored values in it (NULL,1,1,1,3): AVG(colx) will return 1.5, whereas AVG(distinct colx) will return 2.

select avg(creditosp) sinDis,

avg(distinct creditosp) conDis from asignaturas

In nested queries

Sometimes the conditions for filtering tuples or aggregation calculations are more complex than a simple comparison with a constant or a value stored in a table. This intermediate data, not a final result, has to be obtained by an auxiliary query, a subquery.

In the filter condition of the command select ... where we can also:

compare an expression with the result of another select command
determine whether the value of an expression is included in the results of another select command

Description and credits of subjects with fewer credits.

select descripcion, creditos

from asignaturas

where creditos = ( select min(creditos) from asignaturas )

First, the nested select is calculated (in brackets) and the minimum value for the credits column of the subject table is obtained. With this value we compare tuple by tuple and obtain the subject (or subjects) whose number of credits is equal to the minimum.

It is important to realise that the comparison creditos = ? expects a single value, not a set of rows. Hence, we must make sure that the result of the nested query contains a row and a column. Also, the data type must be consistent.

Page updated

Google Sites

Report abuse