Hi everyone, In this article series we will see SQL lessons. You can download and use the virtual machine ready for SQL Practices.
Arithmetic Operations in SQL
If we want to do mathematical operations using SQL, we need to pay attention to operator priority.
- Multiplication and division operators take precedence over addition and subtraction operators.
- Operators with the same priority are processed from left to right.
Let’s make a few examples using these operators.
SELECT prod_min_price, prod_list_price FROM SH.PRODUCTS;
SELECT prod_min_price+100, prod_list_price+100 FROM SH.PRODUCTS;
As shown in the picture, we’ve added 100 to the data in both columns. This process only adds during the query.
To add permanently, different action must be applied (UPDATE)
Let’s make an example with multiplication,
SELECT prod_min_price*0.5, prod_list_price*0.25 FROM SH.PRODUCTS;
Alias
Alias is primarily used to make the column headings in your SQL statements meaningful.
- Provides ease of calculation
- Comes after the column name, optionally the word “AS” can be used after the column name.
Let’s continue with an example,
SELECT CUS_FIRS_NAME AS "NAME", CUST_LAST_NAME AS "SURNAME" FROM SH.CUSTOMERS;
As shown in the picture, we changed the column names as desired.
Merge Operator
To merge table columns in SQL language, use the “||” The coupling operator, expressed with the symbol, is required.
Let’s continue with the example we have just made, and combine the name and surname columns.
SELECT CUST_FIRST_NAME||CUST_LAST_NAME AS "NAME-SURNAME" FROM SH.CUSTOMERS;
As you can see, the name and surname columns are merged.
Repeated Lines
You have a high chance of seeing identical data in the returned query result after the SQL command executed. The “DISTINCT” command will do the trick if you don’t want to see repeated rows in the query result.
Let’s look at the first version of the table,
SELECT PROD_LIST_PRICE FROM SH.PRODUCTS;
Multiple repetitive lines, Write a new query using the word “DISTICNT”
SELECT DISTINCT PROD_LIST_PRICE FROM SH.PRODUCTS;
Table Structures
In SQL, we use the “DESCRIBE” or “DESC” command to see the table structure.
Example “DESC” usage:
DESC SH.PRODUCTS;
See you next article..