Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword

Concatenation Operator

A concatenation operator:

• Links columns or character strings to other columns

• Is represented by two vertical bars (||)

• Creates a resultant column that is a character expression

You can link columns to other columns, arithmetic expressions, or constant values to create a

character expression by using the concatenation operator (||). Columns on either side of the operator

are combined to make a single output column.

                   SELECT FROM last_name||job_id AS "Employees" employees;

In the example, LAST_NAME and JOB_ID are concatenated, and given the alias Employees. Note

that the last name of the employee and the job code are combined to make a single output column.

The AS keyword before the alias name makes the SELECT clause easier to read.

Null Values with the concatenation operator

If you concatenate null value with a character string,the result is a character string. 

LAST_NAME || NULL results in LAST_NAME.

Note: You can also concatenate Date expression with other expressions or columns.

Literal Character Strings

• A literal is a character, a number, or a date that is included in the SELECT statement.
• Date and character literal values must be enclosed within single quotation marks.
• Each character string is output once for each row returned.

Date and character literals must be enclosed within single quotation marks (' '); number literals

need not be enclosed in a similar manner.

     SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

The example in the slide displays the last names and job codes of all employees. The column has the

heading Employee Details. Note the spaces between the single quotation marks in the SELECT

statement. The spaces improve the readability of the output.

Alternative Quote (q) Operator

• Specify your own quotation mark delimiter.
• Select any delimiter.
• Increase readability and usability.

             SELECT department_name || q'[ Department's Manager Id: ]'

                          || manager_id AS "Department and Manager" FROM departments;

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a

single quotation mark, you can use the quote (q) operator and select your own quotation mark

delimiter.

You can choose any convenient delimiter, single-byte or multibyte, or any of the following character

pairs: [ ], { }, ( ), or < >.

In the example shown, the string contains a single quotation mark, which is normally interpreted as a

delimiter of a character string. By using the q operator, however, brackets [] are used as the quotation

mark delimiters.The string between the brackets delimiters is interpreted as a literal character string.

Duplicate Rows

Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate

rows. The first example in the slide displays all the department numbers from the EMPLOYEES table.

Note that the department numbers are repeated.

To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause

immediately after the SELECT keyword. In the second example in the slide, the EMPLOYEES table

actually contains 20 rows, but there are only seven unique department numbers in the table.

                       SELECT department_id FROM employees;

               

                        SELECT DISTINCT department_id FROM employees;

You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects

all the selected columns, and the result is every distinct combination of the columns.

               SELECT  DISTINCT department_id, job_id FROM employees;

DESCRIBE command

Displaying the Table Structure

Use the DESCRIBE command to display the structure of a table.

Or, select the table in the Connections tree and use the Columns tab to view the table structure.

In SQL Developer, you can display the structure of a table by using the DESCRIBE command. The

command displays the column names and the data types, and it shows you whether a column must

contain data (that is, whether the column has a NOT NULL constraint).

In the syntax, table name is the name of any existing table, view, or synonym that is accessible to

the user.

                                                 DESCRIBE departments

Using the SQL Developer GUI interface, you can select the table in the Connections tree and use the

Columns tab to view the table structure.

Note: The DESCRIBE command is supported by both SQL*Plus and SQL Developer.