Oracle Database/SELECT Statement

Listing the capabilities of SQL SELECT statements
A SELECT statement retrieves data from database. With a SELECT statement, you can use the following capabilities:
 * Projection: select the columns in a table that are returned by a query.
 * Selection: select the rows in a table that are returned by a query using certain criteria to restrict the result.
 * Joining: bring together data that is stored in different tables by specifying the link between them.

Executing a basic SELECT statement

 * 1) SQL statements are not case-sensitive.
 * 2) SQL statements can be entered on one or more lines.
 * 3) Keywords like SELECT, FROM cannot be abbreviated or split across lines.
 * 4) In SQL Developer, SQL statements can optionally be terminated by a semicolon . Semicolons are required when you execute multiple SQL statements.
 * 5) In SQL*Plus, you are required to end each SQL statement with a semicolon.


 * Select All Columns


 * Select Specific Columns


 * Exclude duplicate rows


 * Use Arithmetic Operators
 * The operator precedence is the same as normal mathematics, (ie. / * + -)
 * Arithmetic expressions containing a null value evaluate to null


 * Use Column Heading Defaults
 * SQL Developer:
 * Default heading display: Uppercase
 * Default heading alignment: Left-aligned
 * SQL*Plus:
 * Default heading display: Uppercase
 * Character and Date column headings: Left-aligned
 * Number column headings: Right-aligned


 * Use Column Alias
 * Renames a column heading
 * AS keyword between the column name and alias is optional
 * Requires double quotation marks if it contains spaces, special characters, or case-sensitive


 * Literal Character Strings
 * Date and character literal values must be enclosed within single quotation marks
 * Each character string is output once for each row returned


 * Escape the single quote character use two single quotes


 * Escape the single quote character use alternative quote (q) operator


 * Learn the DESCRIBE command to display the table structure

Describing various types of conversion functions that are available in SQL
Implicit data type conversion

Implicit conversion occurs when Oracle attempts to convert the values, that do not match the defined parameters of functions, into the required data types.

Explicit data type conversion Explicit conversion occurs when a function like TO_CHAR is invoked to change the data type of a value.

Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

 * Nest multiple functions
 * Apply the NVL, NULLIF, and COALESCE functions to data

Applying conditional expressions in a SELECT statement

 * Use conditional IF THEN ELSE logic in a SELECT statement

Describing various types of functions available in SQL
Single row functions return one result per row.
 * Describe the differences between single row and multiple row functions

Single row functions: Manipulate data items Accept arguments and return one value Act on each row that is returned Return one result per row May modify the data type Can be nested Accept arguments that can be a column or an expression

Character functions Case manipulation functions LOWER UPPER INITCAP

Using character, number, and date functions in SELECT statements

 * Manipulate strings with character function in the SELECT and WHERE clauses
 * Manipulate numbers with the ROUND, TRUNC and MOD functions
 * Perform arithmetic with date data
 * Manipulate dates with the date functions