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

    SELECT *|{[DISTINCT] column|expression [[AS] alias],...}
    FROM    table;

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 *
    FROM   hr.employees;

• Select Specific Columns

    SELECT employee_id, last_name, hire_date
    FROM   hr.employees;

• Exclude duplicate rows

    SELECT DISTINCT last_name
    FROM   hr.employees;

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

    SELECT last_name, salary, (salary+100-20)*105/100
    FROM   hr.employees;

• 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

    SELECT last_name AS name, commission_pct comm, salary*12 "Annual Salary"
    FROM   hr.employees;

• 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

    SELECT last_name || ' annually earns ' || salary*12
    FROM   hr.employees;

• Escape the single quote character use two single quotes

    SELECT last_name || '''s employee no is ' || employee_id
    FROM   hr.employees;

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

    SELECT last_name || q'<'s employee no is >' || employee_id
    FROM   hr.employees;

• Learn the DESCRIBE command to display the table structure

    DESC[RIBE] table

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

• Describe the differences between single row and multiple row functions

Single row functions return one result per row.

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

Identifying the available group functions

Describing the use of group functions

Grouping data by using the GROUP BY clause

Including or excluding grouped rows by using the HAVING clause

Writing SELECT statements to access data from more than one table using equijoins and nonequijoins

Joining a table to itself by using a self-join

Viewing data that generally does not meet a join condition by using outer joins

Generating a Cartesian product of all rows from two or more tables