SQL - Expressions

SQL Expressions are the pieces of a SQL query that compare values against other values or perform arithmetic calculations. Expressions can be found inside of any SQL command usually in the form of a conditional statement. In the SQL world, conditional statements and expressions test or compare values against other values.

SQL - Boolean Expressions

Boolean expressions return rows (results) when a single value is matched.

SQL Boolean Expression:

USE mydatabase;

SELECT * FROM orders WHERE id = '1';

SQL Results:

id	customer	day_of_order	product	quantity
1	Tizag	2008-08-01 00:00:00.000	Pen	4

SQL - Numeric Expression

Numeric Expressions return a single numeric value instead of an entire row and usually perform calculations.

SQL Code:

USE mydatabase;

SELECT 15 + 4;

SQL Code:

USE mydatabase;

SELECT (15 / 5) * 10;

SQL Code:

USE mydatabase;

SELECT ((5+5) * (5+5));

Each of the examples above returns a numeric value which is displayed inside the results pane of the SQL application. SQL also offers several built-in functions to perform what is known as aggregate data calculations against a table or a specific table column.

• AVG() -- Returns the average value of a stated column.
• COUNT(*) -- Returns a count of the number of rows of table.
• SUM() -- Returns the sum of a given column.

Using one of the following functions also returns a numeric value:

SQL Code:

USE mydatabase;

SELECT COUNT(*) AS "Number of Orders"
FROM orders;

SQL Code:

USE mydatabase;

SELECT SUM(quantity)AS "Total Number of Items Purchased"
FROM orders;

SQL Code:

USE mydatabase;

SELECT AVG(quantity) AS "Average Number of Items Purchased"
FROM orders;

We can also combine these queries into a single query so that the results are viewable all at once.

SQL Code:

USE mydatabase;

SELECT COUNT(*) AS "Number of Orders",
SUM(quantity)AS "Total Number of Items Purchased",
AVG(quantity)AS "Average Number of Items Purchased"
FROM orders;

SQL - Date Expressions

As the name suggests, Date Expressions return date/time values.

• GetDate() -- Returns the current date/time.
• Current_Timestamp -- Returns the current timestamp.

Date expressions as you may have guessed, return date values. We will be taking a closer look at date expressions later on in this tutorial. Stay tuned.

SQL Code:

USE mydatabase;

SELECT Current_Timestamp;
SELECT  GETDATE();

Posted in: SQL