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();
Found Something Wrong in this Lesson?Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time! |