The majority of the time, columns in the SELECT statement exist within a database table. However, sometimes it is more advantageous to calculate a value than to store it.
An example might be the salary. In the employee table, we store the annual salary. However, a request comes in asking to display the monthly salary. Does the table need to be changed to create a column for storing the monthly salary? Must we go through and update all of the rows (one per employee) and store the monthly salary into the new column just so we can select it for display?
The answer is no, we do not need to do any of this. Instead of storing the monthly salary, we can calculate it from the annual salary using division. If the annual salary is divided by 12 (months per year), we "derive" the monthly salary using mathematics.
These math functions have a priority associated with their order of execution when mixed in the same formula. The sequence is basically the same as their order in the chart. All exponentiation is performed first. Then, all multiplication and division is performed and lastly, all addition and subtraction is done. Whenever two different operands are at the same priority, like addition and subtraction, they are performed based on their appearance in the equation from left to right.
Although the above is the default priority, it can be over-ridden within the SQL. Normally an equation like 2+4*5 yields 22 as the answer. This is because the 4*5 = 20 is done first and then the 2 is added to it. However, if it is written as (2+4)*5, now the answer becomes 30 (2+4=6*5=30).
The following SELECT shows these and the results of an assortment of mathematics:SELECT 2+4*5, (2+4)*5
Note: starting with integer values, as in the above, the answer is an integer. If decimals are used, the result is a decimal answer. Otherwise, a conversion can be used to change the characteristics of the data before being used in any calculation. Adding the decimal makes a difference in the precision of the final answer. So, if the SQL is not providing the answer expected from the data, convert the data first.
The next SELECT shows how the SQL can be written to implement the earlier example with annual and monthly salaries:SELECT salary (format 'ZZZ,ZZ9.99')
Since the column name is the default column heading, the derived column is called salary/12, which is not probably what we wish to see there.
Derived data can be used in the WHERE clause as well as the SELECT. The following SQL will only return the columns when the monthly salary is greater than $1,000.00:SELECT salary (format 'ZZZ,ZZ9.99')
Some of these functions are demonstrated below and throughout this book. Here they are also using alias names for the columns. Their application will be specific to the type of application being written. It is not the intent of this book to teach the meaning and use in engineering and trigonomy, but more to educate regarding their existence.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.