SQL - Expressions
In the programming world an expression is a special statement that returns a value. SQL is no exception to this standard rule.
SQL - Expression Types
Expressions in SQL generally fall into one of four categories including: Boolean, Numeric, Character, and/or Date Expressions.
SQL Code:
SELECT column_one FROM table_name;
The simplest form of an expression appears as column_one of our table. Select is our clause telling our database what we want to do, and column_one acts as the defined expression returning each row of that particular column.
Expressions after the where clause might appear more familiar to programmers.
SQL Code:
SELECT * FROM table_name WHERE column_one = 'some value';
The latter example returns rows of the specified column containing 'some value'. Using expressions like the one above gives you precise control over what results will be returned. More information on the where clause is available at SQL Where.
SQL - Boolean Expressions
A boolean expression in any programming language returns a true/false result. Returning to the previous example containing the where clause.
SQL Code:
SELECT * FROM table_name WHERE column_one = 'some value';
The logic behind this query is that each row is being tested for 'some value' to appear in our column_one. Each time a match is found (testing true), that row is selected and returned for our viewing pleasure.
SQL - Numeric Expression
A numeric expression simply returns a numeric value. There are some built in functions that we will be examining in greater detail later on. Using one of the following functions is perhaps the easiest way to demonstrate the return of a number:
- AVG() -- Returns the average of a stated column.
- COUNT() -- Returns a count of the number of rows of a given column.
- SUM() -- Returns the sum of a given column.
SQL Code:
SELECT COUNT(*) FROM table_name;
Our expression above returns a numeric value representing the number of rows that have been inserted into your table thus far. Please be aware that the AVG(), COUNT(), and SUM() only return results for integer table columns. Using one of these functions with a varchar column will result in an error message.
SQL - Character Expressions
Character expressions are used to test for values of a string. Often these expressions will appear in a where clause as follows.
SQL Code:
SELECT * FROM table_name WHERE
column_one LIKE '%string';
Here we have used the percent(%) symbol to signify the start of our string. SQL tests our expression against column_one and returns all the rows and columns where column_one contains our string.
This might come across easier if we use a live example. Say we have created a table with employee information. In this table we have set up a column a last_name column. The query above will come in handy if we were wanting to pull all the employees with a last_name that begins with a "T". Now if we plug in our hypothetical situation into our code, we should have something like the following.
SQL Code:
SELECT * FROM employees WHERE
last_name LIKE '%T';
Keep in mind that SQL is case sensitive, using a lowercase t would not yield results for a last_name that has been capitalized.
SQL - Date Expressions
Date expressions come in three flavors. These expressions are very straight forward, simply type in any of those listed below and SQL will return exactly what you have requested.
- Current_Date -- Returns the current date.
- Current_Time -- Returns the current time.
- Current_Timestamp -- Returns the current timestamp.
These expressions can also be placed into your tables as column values for any given row with an insert statement. We will be looking more indepth at the insert clause, however here is a glimpse of what is to come.
SQL Code:
INSERT INTO table_name(column_one,column_two,)
Values(Current_Date,Current_Timestamp);
This statement inserts a new row into our imaginary table with the current date value for column one and the current timestamp value for columne_two.
| 
