SQL DATA

SQL DATA
Using Column Aliases
MySQL

SELECT nome as nome_aluno
FROM aluno;
1
2
SELECT nome as nome_aluno
FROM aluno;

The example displays the name of all students. Note that the AS keyword was used before the column alias name.

Concatenation Operator
You can join columns of character type (CHAR or VARCHAR) to create a character expression using the concatenation operator. Columns on either side of the operator are combined to make a single column of output.

MySQL

SELECT concat ('O aluno ', nome, ' possui email ', email) as "Email"
FROM aluno;
1
2
SELECT concat ('O aluno ', nome, ' possui email ', email) as "Email"
FROM aluno;

In the example, the name and email columns are concatenated, and the result is given the alias "Email". Note that student name and email are combined by getting a single output column.

Duplicate Lines
MySQL

SELECT nome
FROM aluno;
1
2
SELECT nome
FROM aluno;

Unless you indicate otherwise, sql displays the results of a query without deleting duplicate rows. The above example displays all student names from the STUDENTS table. Note that student names are repeated.

Deleting Duplicate Rows
MySQL

SELECT DISTINCT nome
FROM aluno;
1
2
SELECT DISTINCT nome
FROM aluno;


To eliminate duplicate rows from the query result, include the DISTINCT keyword immediately after the SELECT word.

You can specify multiple columns after the word DISTINCT. The DISTINCT qualifier affects all selected columns, and the result represents a distinct combination of columns.

MySQL

SELECT DISTINCT id_aluno, nome
FROM aluno;
1
2
SELECT DISTINCT id_aluno, nome
FROM aluno;
Arithmetic Expressions

You may need to modify the way data is displayed, for example, by performing calculations. This is possible through the use of arithmetic expressions. An arithmetic expression can contain column names, constant numeric values, and arithmetic operators.

Using Arithmetic Operators
MySQL

SELECT nome_curso, preco, format(preco/2, 2)
FROM curso;
1
2
SELECT nome_curso, preco, format(preco/2, 2)
FROM curso;


The above example uses the division operator to calculate a 50% discount for all courses and show a new price / 2 column on the screen.

Note that the resulting column price / 2 is not a new column of the COURSE table, and is used only in the display.

Operator Precedence

If an arithmetic expression has more than one operator, multiplication and division are evaluated first. If the operators within an expression are of the same priority, then the evaluation is done from left to right.

You can use parentheses to force the expression placed inside them to be evaluated first.

MySQL

SELECT nome_curso, preco, 2*preco+100
FROM curso;
1
2
SELECT nome_curso, preco, 2*preco+100
FROM curso;

The above example shows the name of the course and the price of the course increased by 100% + $ 100.00. Note that multiplication is performed before addition.

Note: Use parentheses to reinforce the default order of precedence and improve the clarity of the command. For example, the above expression could be written this way, without change in the result: (2 * price) +100.

Using Parentheses
MySQL

SELECT nome_curso, preco, 12*(preco+100)
FROM curso
1
2
SELECT nome_curso, preco, 12*(preco+100)
FROM curso

You can change the precedence rules using parentheses to specify the order in which operators are to be executed. Due to the use of parentheses, addition takes precedence over multiplication.

Null values ​​in arithmetic expressions
If the value of any column in an arithmetic expression is null, the result of the expression is also null. For example, if you try to perform a division by zero, you get an error. However, if you divide a number by null, the result is null or unknown.