Generally in any programming language, expression is a combination of values, constants, variables, operators and functions that results in a value. In SQL, expressions are used to query database/table to get specific data.
There are different types of expressions in SQL:
DATE Expression
Date expressions give the current date and time values.
Example 1:
Use Date expression NOW to get the current date and timestamp.
mysql> SELECT NOW(); +------------------------------+ | NOW() | +------------------------------+ | 2014-12-09 20:19:16 | +------------------------------+ 1 row in set (0.06 sec)
Example 2:
Another way to get the current date and timestamp is “CURRENT_TIMESTAMP”
mysql> SELECT CURRENT_TIMESTAMP; +----------------------------------------------+ | CURRENT_TIMESTAMP | +----------------------------------------------+ | 2014-12-09 20:19:36 | +-----------------------------------------------+ 1 row in set (0.00 sec)
Boolean Expression
The SQL query fetches records based on the True or False of the condition specified in the query.
SELECT columns from table_name WHERE value matching expression (Either TRUE or FALSE) |
Consider Employee table with below records:
Emp_ID | Emp Name | Address | Email id | Salary | Dept |
1 | John | CA | 12000 | 1 | |
2 | Michel | CA | 13000 | 2 | |
3 | Linda | CA | 12500 | 2 | |
4 | Michael | CA | 20000 | 3 | |
5 | Max | CA | 30000 | 3 |
Then if we consider below query
SELECT * FROM EMP WHERE SALARY = 12000; +-----------------+------------------+-----------------+----------------------------+----------+ | emp_id | emp_name | address | emailid | salary | +-----------------+------------------+-----------------+----------------------------+----------+ | 1 | John | CA | john@abc.com | 12000 | +-----------------+------------------+-----------------+----------------------------+----------+ 1 row in set (0.00 sec)
Here the rows will be selected when the condition will be TRUE. The expression is built using Operators (which you learnt in previous chapter).
Numeric Expression:
In the Numeric expression, the numeric operators are used to provide the result. Can use the functions that give numerical results like COUNT, AVG, SUM, MIN and MAX. These are the aggregate calculations for a table column.
Basic syntax:
SELECT numerical_expression as Label_name [from table_name Where condition] Here the table name and condition are optional. |
Example 1:
Consider the display of addition of two numbers under label “Add_Num”
mysql> SELECT 10+20 as ADD_NUM; +-----------------+ | ADD_NUM | +-----------------+ | 30 | +-----------------+ 1 row in set (0.08 sec)
Example 2:
Get the count of records in employee table.
mysql> SELECT COUNT(EMP_ID) AS EMP_COUNT FROM EMPLOYEE; +---------------------------+ | EMP_COUNT | +---------------------------+ | 5 | +---------------------------+ 1 row in set (0.86 sec)
Example 3:
Get the average salary for department ‘3’.
mysql> SELECT AVG(SALARY) AS AVG_SAL FROM EMPLOYEE -> WHERE DEPT_ID = 3; +-----------------+ | AVG_SAL | +-----------------+ | 25000 | +-----------------+ 1 row in set (0.17 sec)
Example 4:
Get the count of employees and total salary paid to the employees.
mysql> SELECT COUNT(EMP_ID) AS COUNT_EMPLOYEES, -> SUM(SALARY) AS TOTAL_SALARY -> FROM EMPLOYEE; +-------------------------------------+---------------------------+ | COUNT_EMPLOYEES | TOTAL_SALARY | +-------------------------------------+---------------------------+ | 5 | 87500 | +-------------------------------------+---------------------------+ 1 row in set (0.00 sec)
Example 5:
Get the Maximum Salary for each department.
mysql> SELECT DEPT_ID, MAX(SALARY) AS SAL_PER_DEPT -> FROM EMPLOYEE -> GROUP BY DEPT_ID; +-----------------+----------------------------+ | DEPT_ID | SAL_PER_DEPT | +-----------------+----------------------------+ | 1 | 12000 | | 2 | 13000 | | 3 | 30000 | +-----------------+----------------------------+ 3 rows in set (0.05 sec)----------------------+