|
|
It's also possible to execute SELECT queries that return calculated columns. Retrieving calculated columns means getting data that doesn't necessarily exist in the database but can be constructed or calculated from data that's there. You might, for example, combine the FirstName and LastName columns in a database to a single Name column, built from the content of the two original columns.
To do this, you simply specify the new column name in the usual way but equate it to the result of some processing or other:
SELECT Details AS CustomColumn FROM Table;
Here you create a column called CustomColumn based on the information specified in Details, which can include all sorts of mathematical and string operations, as well as calling on certain built-in functions for data processing.
| Note |
You'll be looking at performing calculations and using built-in SQL functions in more detail in Chapter 5, "Performing Calculations and Using Functions." |
For example, you could perform an automatic calculation as follows:
SELECT ItemName, Amount, Amount * ItemCost AS TotalCost FROM Items;
Here you create a new column called TotalCost, which is the product of the values from two other columns, Amount and ItemCost, taken from the Items table.
As another example, let's return to the InstantUniversity database. Consider the following SQL query that combines data from the Mark and IfPassed columns to give either the actual mark if the exam is passed or zero if not. You place this data in a custom MarkIfPassed calculated column:
SELECT StudentID, ExamID, Mark * IfPassed AS MarkIfPassed FROM StudentExam;
The results from this query are as follows:
StudentID ExamID MarkIfPassed ----------- ----------- ------------ 1 1 55 1 2 73 2 3 44 2 5 0 2 6 63 ... ... ...
As you can see, the data returned in the result set looks different from that in the original table. Through your result set, you have access to a column of data, MarkIfPassed, that doesn't actually exist in the database.
For example, you could perform an automatic calculation as follows:
SELECT ItemName, Amount, Amount * ItemCost AS TotalCost FROM Items;
Here you create a new column called TotalCost, which is the product of the values from two other columns, Amount and ItemCost, taken from the Items table.
As another example, let's return to the InstantUniversity database. Consider the following SQL query that combines data from the Mark and IfPassed columns to give either the actual mark if the exam is passed or zero if not. You place this data in a custom MarkIfPassed calculated column:
SELECT StudentID, ExamID, Mark * IfPassed AS MarkIfPassed FROM StudentExam;
The results from this query are as follows:
StudentID ExamID MarkIfPassed ----------- ----------- ------------ 1 1 55 1 2 73 2 3 44 2 5 0 2 6 63 ... ... ...
As you can see, the data returned in the result set looks different from that in the original table. Through your result set, you have access to a column of data, MarkIfPassed, that doesn't actually exist in the database.
SQL Server uses the plus sign (+) to perform string concatenation:
SELECT FirstName + ' ' + LastName AS FullName FROM People;
In this example, you create a full name string in a column called FULLNAME by concatenating the strings in the FirstName and LastName columns of People using the + operator. For neatness, you also put a space in the middle, which is achieved by including a literal string value enclosed in ' or " characters.
However, because the + sign is also used for numeric addition, you can't use it for concatenation if any numeric values are involved. For example, this query:
SELECT 'Room ' + RoomID AS RoomName FROM Room;
causes the following error:
Syntax error converting the varchar value 'Room ' to a column of data type int.
The easiest way around this is to convert the numeric value to a string explicitly using SQL Server's STR() function:
SELECT 'Room ' + STR(RoomID) AS RoomName FROM Room;
 |
Enter this query into Query Analyzer and execute it against the InstantUniversity database:
SELECT ClassID,
Time + ', Room ' + STR(RoomID) AS ClassDetails
FROM Class;
Here you retrieve the ID for each row in the Class table, together with a new column called ClassDetails, which you create by concatenating the Time field with the string literal ', Room ', followed by the ID for the room where that class is held. Because RoomID is an integer field, you need to convert the value to a string before you can use it in a concatenation. The output from this query is as follows:
ClassID ClassDetails -------- ----------------------------------------- 1 Mon 09:00-11:00, Room 6 2 Mon 11:00-12:00, Thu 09:00-11:00, Room 5 3 Mon 14:00-16:00, Room 3 4 Tue 10:00-12:00, Thu 14:00-15:00, Room 2 ... ...
|
When run in the default (that is, not ANSI) mode, MySQL also uses + as the concatenation operator:
SELECT FirstName + ' ' + LastName AS FullName FROM People;
Naturally, using + has the same drawback as in SQL Server—you'll get unpredictable results if any numeric values are involved. MySQL automatically converts numeric values to strings (and vice versa) as necessary, so it doesn't have any direct equivalent to SQL Server's STR() function. You therefore need a different solution to this problem.
| Note |
When run in ANSI mode, MySQL uses the same string concatenation operator as Oracle (||). |
The answer comes in the form of the CONCAT() function. This function takes a variable number of arguments and joins them to form a single string. The arguments will automatically be converted to strings, so you can pass in arguments of any data type.
| Note |
This feature allows you to use this function to simulate the SQL Server STR() function—you can concatenate an empty string to the value you want to convert (say, a number), and that value will be returned as a string. |
So, to join the string literal 'Room ' and the RoomID into a single value, you'd use this statement:
SELECT CONCAT('Room ', RoomID) AS RoomName
FROM Room;
You'll look at using SQL functions in more detail in Chapter 5, "Performing Calculations and Using Functions."
|
Execute this query against the InstantUniversity database:
SELECT ClassID,
CONCAT(Time, ', Room ', RoomID) AS ClassDetails
FROM Class;
Again, you retrieve the ID for each class, together with the calculated ClassDetails that represents the details of where and when the class is held. Again, because RoomID is an integer field, you need to use the CONCAT() function rather than the + operator to perform the concatenation. The output from this query is as follows:
ClassID ClassDetails -------- ----------------------------------------- 1 Mon 09:00-11:00, Room 6 2 Mon 11:00-12:00, Thu 09:00-11:00, Room 5 3 Mon 14:00-16:00, Room 3 4 Tue 10:00-12:00, Thu 14:00-15:00, Room 2 ... ...
|
Oracle uses the ANSI standard concatenation operator, ||. Because this operator is reserved for string concatenation, the operands don't need to be cast to a string type—they'll be converted automatically:
SELECT FirstName || ' ' || LastName AS FullName FROM People;
This operator is also used for MySQL when it's running in ANSI mode.
|
Execute this query against the InstantUniversity database:
SELECT ClassID,
Time || ', Room ' || RoomID AS ClassDetails
FROM Class;
This query returns the ClassID for each class, together with a calculated column containing the time and room number details for each class:
ClassID ClassDetails -------- ----------------------------------------- 1 Mon 09:00-11:00, Room 6 2 Mon 11:00-12:00, Thu 09:00-11:00, Room 5 3 Mon 14:00-16:00, Room 3 4 Tue 10:00-12:00, Thu 14:00-15:00, Room 2 ... ...
|
DB2 can use either the ANSI standard || operator or the CONCAT keyword to perform string concatenation:
or:
SELECT FirstName CONCAT ' ' CONCAT LastName AS FullName FROM People;
Whichever of these you use, you'll need to convert any numeric data types to strings before performing the concatenation. You can achieve this using the CHAR() function:
SELECT 'Room ' CONCAT CHAR(RoomID) AS RoomName FROM Room;
You can also use CONCAT() as a standard function. It takes two parameters, which both must be string types:
SELECT CONCAT('Room ', CHAR(RoomID)) AS RoomName FROM Room;
|
Execute this query against the InstantUniversity database:
SELECT ClassID,
Time CONCAT ', Room ' CONCAT CHAR(RoomID)
AS ClassDetails
FROM Class;
Here you use the CONCAT keyword like an operator to join three values into a single string. If you had used CONCAT() as a function, you would have to nest one call to CONCAT() within another because this function takes two arguments:
SELECT ClassID,
CONCAT(
CONCAT(Time, ', Room '), CHAR(RoomID)
) AS ClassDetails
FROM Class;
The output from this query is as follows:
ClassID ClassDetails -------- ----------------------------------------- 1 Mon 09:00-11:00, Room 6 2 Mon 11:00-12:00, Thu 09:00-11:00, Room 5 3 Mon 14:00-16:00, Room 3 4 Tue 10:00-12:00, Thu 14:00-15:00, Room 2 ... ...
|
Finally, Access uses the ampersand (&) to perform concatenation. As with || in Oracle, this operator is used only to join strings, so you don't need to convert the operands to a string type before using it.
|
Execute this query against the InstantUniversity database:
SELECT ClassID, Time & ', Room ' & RoomID AS ClassDetails FROM Class;
Again, you join the Time and RoomID columns, separated by the literal string ', Room ', into a single column named ClassDetails:
ClassID ClassDetails -------- ----------------------------------------- 1 Mon 09:00-11:00, Room 6 2 Mon 11:00-12:00, Thu 09:00-11:00, Room 5 3 Mon 14:00-16:00, Room 3 4 Tue 10:00-12:00, Thu 14:00-15:00, Room 2 ... ...
|
|
|