Calculated fields are fields that do not exist in a table, but are created in the
SELECT statement. For example, you might want to create
Concatenation is a fancy word for stringing together different words or characters. SQL Server, Oracle and MySQL each has its own way of handling concatenation. All three of the code samples below will return the following results:
In SQL Server, the plus sign (
+) is used as the concatenation operator.
-- Select the full name of all employees. SQL SERVER. SELECT FirstName + ' ' + LastName FROM Employees;
In Oracle, the double pipe (||) is used as the concatenation operator.
-- Select the full name of all employees. Oracle. SELECT FirstName || ' ' || LastName FROM Employees;
MySQL does this in yet another way. There is no concatenation operator. Instead, MySQL uses the CONCAT() function .
-- Select the full name of all employees. MySQL. SELECT CONCAT(FirstName, ' ', LastName) FROM Employees;
Note that concatenation only works with strings. To concatenate other data types, you must first convert them to strings.
Mathematical calculations in SQL are similar to those in other languages.
/* If the cost of freight is greater than or equal to $500.00, it will now be taxed by 10%. Create a report that shows the order id, freight cost, freight cost with this tax for all orders of $500 or more. */ SELECT OrderID, Freight, Freight * 1.1 FROM Orders WHERE Freight >= 500;
The above SELECT statement will return the following results:
You will notice in the examples above that the calculated columns have the header "
(No column name)". The keyword
AS is used to provide a named header for the column.
Note: you cannot use aliases in a WHERE clause!
SELECT OrderID, Freight, Freight * 1.1 AS FreightTotal FROM Orders WHERE Freight >= 500;
As you can see, the third column now has the title "