Tutorial

SQL Expressions

Published on August 3, 2022
author

Pankaj

SQL Expressions

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

SQL expression is a combination of one or more values, operators and SQL functions that results in to a value. These SQL EXPRESSIONs are similar to a formula and they are written in query language. You can also use them to query the database for a specific set of data.

SQL Expressions

SQL Expressions SQL expression can be classified into following categories.

  1. Boolean
  2. Numeric
  3. Date

Let us try to understand the types of expression in detail with sample programs.

SQL Boolean Expression

SQL Boolean Expression fetches data based on the condition that is mentioned as part of the SQL query. It should fetch just single value when the query is executed. Its syntax is given below.

SELECT column 
FROM table_name 
WHERE SINGLE_VALUE_MATCHING_EXPRESSION;

Let us try to understand Boolean expression using a sample table with some data.

EmpId EmpName EmpAge EmpSalary
1 John 32 2000
2 Smith 25 2500
3 Henry 29 3000

We will consider the Employee table mentioned above as an example. Example Query:

SELECT EmpName FROM Employee WHERE EmpId = 1;

The query above will result in single value which is “John” for our query. SQL Boolean expressions should be used with primary keys to make suer you always get only one results. This is required incase of nested SQL select queries.

select posts.title, posts.author_id from posts, authors 
where posts.author_id = authors.id and 
posts.author_id = (select id from authors where name = 'Pankaj');

Above query will return all the posts name and id where author name is Pankaj. Here nested SQL query should always return only one row otherwise you will get error message as Subquery returns more than 1 row.

SQL Numeric Expression

SQL Numeric Expression is used for performing mathematical operation in SQL query. Its syntax is as follows:

SELECT NUMERICAL_EXPRESSION as OPERATION_NAME
FROM table_name

NUMERICAL_EXPRESSION is the mathematical formula for function which will be used in the SQL query. Let’s try to understand Numeric expression using an example.

SELECT count(*) FROM Employee; -- 3

The query above will result as 3 because the COUNT(*) function will provide the total count of the rows based on the condition in WHERE clause. For example select count(*) from employee where empsalary > 2400; will return 2. There are other functions also like sum(), avg(), min(), max() etc. These functions are used for mathematical operations. Let’s see some more example code snippets for SQL numeric expressions.

SELECT sum(EmpSalary) as "Salary" FROM Employee; -- 7500
select min(EmpSalary) from Employee; -- 2000
select max(EmpSalary) from Employee; -- 3000
select sum(EmpSalary) from Employee; -- 7500
select avg(EmpSalary) from Employee; -- 2500.0000

SQL Date Expression

SQL Date Expression results in datetime value. Let’s try to understand Date expression with some sql statements.

SELECT CURRENT_TIMESTAMP; -- 2018-01-20 10:32:37

-- For MYQSL
SELECT now(); -- 2018-01-20 10:32:57

-- For SQL Server
SELECT GetDate();

-- For Oracle DB
select sysdate from Dual; -- 20-JAN-18

The above queries will result in current time of the system. Note that every relational database vendor has specific methods to get current date and timestamp values, so there is a slight chance that one function in a database doesn’t work in another database. That’s all for SQL expressions, we will use these a lot in later sql tutorials and programs. Further Reading: SQL Data Types. Reference: Oracle Documentation

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products


About the authors
Default avatar
Pankaj

author

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
JournalDev
DigitalOcean Employee
DigitalOcean Employee badge
February 26, 2020

Great article. So SQL has certain keywords for certain tasks, is there a way to create your own keyword for a particular type of task?

- Patrick

    JournalDev
    DigitalOcean Employee
    DigitalOcean Employee badge
    February 3, 2018

    Hi Pankaj. When you talk about boolean expressions, you say “It should fetch just single value when the query is executed”. But what do you mean? If in the above table I have 2 rows with empsalary=2000, the query will result in 2 values. Also, in all your tutorials, when you say “it’s syntax is …”, you are using a possessive form, so you should write “its syntax is”. Cheers

    - Valentino

      Try DigitalOcean for free

      Click below to sign up and get $200 of credit to try our products over 60 days!

      Sign up

      Join the Tech Talk
      Success! Thank you! Please check your email for further details.

      Please complete your information!

      Featured on Community

      Get our biweekly newsletter

      Sign up for Infrastructure as a Newsletter.

      Hollie's Hub for Good

      Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

      Become a contributor

      Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

      Welcome to the developer cloud

      DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

      Learn more
      DigitalOcean Cloud Control Panel