Expressions in SQL

In the previous lesson, you learned about data types and operators in SQL as well as how to use them. In this article we will learn about expressions in SQL.

What are expressions?

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. These SQL expressions are like formulas and they are written in query language. You can use them to query the database for a specific set of data.

Syntax of an SQL expression

Consider the basic syntax of the SELECT statement as follows:

SELECT cot1, cot2, cotN FROM ten_bang WHERE [DIEU_KIEN|BIEU_THUC];

There are different types of SQL expressions, which are mentioned below:

1. Boolean
2. Number
3. Day

Now we will learn about each of these types of expressions in detail.

Boolean expression

Boolean expressions in SQL fetch data based on a combination of a value. The syntax of this expression is as follows:

SELECT cot1, cot2, cotN FROM ten_bang WHERE BIEU_THUC_KET_HOP_GIA_TRI_DON;

See the NHANVIEN table with the following records:

SQL> SELECT * FROM NHANVIEN; +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Huong | 32 | Da Nang | 2000.00 | | 2 | Khuong | 25 | Da Lat | 1500.00 | | 3 | Quyen | 23 | Ha Noi | 2000.00 | | 4 | Chi | 25 | Hue | 6500.00 | | 5 | Hanh | 27 | Phu Tho | 8500.00 | | 6 | Phuong | 22 | My Tho | 4500.00 | | 7 | Duyen | 24 | Ha Noi | 10000.00 | +----+----------+-----+-----------+----------+ 7 rows in set (0.00 sec)

The table below is an example of using Boolean expressions in SQL:

SQL> SELECT * FROM NHANVIEN WHERE LUONG = 10000; +----+-------+-----+---------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+-------+-----+---------+----------+ | 7 | Duyen | 24 | Ha Noi | 10000.00 | +----+-------+-----+---------+----------+ 1 row in set (0.00 sec)

Arithmetic expressions in SQL

These are expressions used to perform any mathematical operations in queries. The syntax of arithmetic expressions in SQL is as follows:

SELECT BIEU_THUC_SO_HOC as TEN_HOAT_DONG [FROM ten_bang WHERE DIEU_KIEN] ;

Here, BIEU_THUC_SO_HOC is used for a mathematical expression or any formula. The following simple example will show you how to use arithmetic expressions in SQL:

SQL> SELECT (3 + 7) AS ADDITION +----------+ | ADDITION | +----------+ | 10 | +----------+ 1 row in set (0.00 sec)

There are a number of built-in functions in SQL such as avg(), sum(), count(), etc. to perform calculation operations and summarize data for a specific table or column in the SQL database. board.

SQL> SELECT COUNT(*) AS "RECORDS" FROM NHANVIEN; +---------+ | RECORDS | +---------+ | 7 | +---------+ 1 row in set (0.00 sec)

Date expression in SQL

Date expression returns the current system's time and date values.

SQL> SELECT CURRENT_TIMESTAMP; +---------------------+ | Current_Timestamp | +---------------------+ | 2017-11-11 06:40:23 | +---------------------+ 1 row in set (0.00 sec)

Another date expression is written as follows:

SQL> SELECT GETDATE();; +-------------------------+ | GETDATE | +-------------------------+ | 2017-11-12 12:07:18.140 | +-------------------------+ 1 row in set (0.00 sec)

So you have grasped the basic expressions in SQL. From the next lessons we start with SQL commands to work with databases and tables.