DISTINCT - Eliminating Duplicate Rows

The default display of queries is all rows, including duplicate rows.

Eliminating Duplicate Rows

Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.

Note:

You can specify multiple columns after the DISTINCT clause. The DISTINCT clause affects all the selected columns, and the result is every distinct combination of the columns.

EX: 

Without DISTINCT the above output:  (Notice the difference)

Literal Character String in SELECT statement

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

Using Literal Character String:

The Example above displays the names and job codes of all employees. The column has the heading Employee Details. Notice the spaces between the single quote marks in the select statement. The spaces improves the readability of the output. 

Concatenation Operator in SELECT statement

A concatenation operator:

• Concatenates columns or character strings to other columns.
• is represented by two vertical bar ( || )
• Creates a resultant column that is character expression.

Using the Concatenation Operator:

In the above example ename and job are concatenated, and they are given alias Employees. Notice that the ename and job code are combined to make a single output column.

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

ALIAS - Column Aliases in SELECT statement

A column alias:

• Renames a column heading.
• Is useful with calculations.
• AS keyword is optional between the column name and alias.
• Requires double quotation marks if it contains spaces or special characters or is case sensitive

Using Column Aliases (with example)