Exam Topic: Retrieving Data Using the SQL SELECT Statement – Execute a basic SELECT statement

In The Capabilities of a SELECT Statement, we talked about returning columns from a table using the SELECT list and a few of the alternatives. In fact, any expression you can think of can be in the SELECT list. An expression is “a combination of one or more values, operators, and SQL functions that evaluates to a value. An expression generally assumes the datatype of its components.”

You can also work with expressions using literals and the DUAL table to try them out. Remember this one?

SQL> select sysdate from dual;

DUAL is a dummy table and fill in the need for a SQL statement to have a FROM clause even when one isn’t strictly needed.

Math

Mathematical expressions can be run in SQL as a SELECT FROM DUAL. Just remember your orders of operations!

  • Parentheses
  • Exponents
  • Multiplication
  • Division
  • Addition
  • Subtraction
SQL> select 7 * 7 from dual;

 7*7
----------
 49

SQL> select (3+4) * 7 from dual;

 (3+4)*7
----------
 49

SQL> select power(5,2) from dual;

POWER(5,2)
----------
 25

SQL> select power(5,2)/5 from dual;

POWER(5,2)/5
------------
 5

Strings

String expressions can be selected from DUAL, as well. This can be a good way to work out how you want a field to be returned in a report, sort of like working it out on a scratch piece of paper before moving it to the main query. Some common string expressions include:

  • Concatenation using || (two pipes) will combine two strings
  • Substring returns a portion of a string: substr(string, start-position, end-position)
  • In-string returns the position of a string within another string: instr(string, string-to-find, start-position)

Concatenation

Concatenation can work with any string, anything converted to characters or any function that returns characters.

SQL> select 'A' || 'B' from dual;

'A
--
AB

SQL> select 'A' || chr(86) from dual;

'A
--
AV

Some functions return character data, such as SYSDATE. Some datatypes get implicitly converted to characters when concatenated with a character.

SQL> select sysdate || 'A' from dual;

SYSDATE||'A'
-------------------
21-JUN-15A

SQL> select 'A' || 1 from dual;

'A
--
A1

Substring

Returning a portion of a string has many great uses in reports and queries. Returning the last four of a social security number, initial letters from a name, or limiting the number of characters in a string to a certain number are just a few common examples.

Substring works as follows:

substr(whole-string, beginning-position, end-position)

SQL> select substr('123-45-6789',-4,4) from dual;

SUBS
----
6789

SQL> select substr('YNGIWULF',1,1) from dual;

S
-
Y

SQL> select substr('THIS LINE IS FAR TOO LONG!',1,20) from dual;

SUBSTR('THISLINEISFA
--------------------
THIS LINE IS FAR TOO

In-String

INSTR searches a string for a substring and returns the position of the first character of the substring. This can be useful not only in itself but also when combined with SUBSTR. Sometimes a string needs to be detected in a substring and just knowing it is there gives you the information you need.

instr(whole-string, searched-substring, optional-beginning-position)

SQL> select instr('123-45-6789','45') from dual;

INSTR('123-45-6789','45')
-------------------------
 5

The return value is greater than 0, so that means that the substring was found in the string. Let’s say we want a certain value returned when it finds the value in the string. In a SELECT query on a table, this expression will be evaluated for every row, providing a ‘Yes’ or ‘No’ if ’45’ is found anywhere in the string.

SQL> select CASE WHEN instr('123-45-6789','45') > 0 THEN 'Yes' ELSE 'No' END 
2  from dual;

CAS
---
Yes

Use the INSTR function to help guide a SUBSTR function.

  • Line 2: the string ‘123-45-6789’ is having SUBSTR ran on it
  • Line 3: the first position of the substring is determined by the INSTR ran on ‘123-45-6789′ where the character ’45’ is found
  • Line 4: the length of the SUBSTR is set at 2

So, in a SELECT from a table, for each row, each field is searched for ’45’ and ’45’ is returned. The field name would be put in the place of ‘123-45-6789’ in the example below.

SQL> select 
2  substr('123-45-6789',
3  instr('123-45-6789','45')
4  ,2) 
5  from dual;

SU
--
45

Selecting expressions from dual is a great way to learn how to use functions and learn what to expect from expressions you plan on using. Use it as a try-out before implementing them in your main queries.

Tagged: ,