Oracle Programming/Retrieving Data Using the SQL SELECT Statement

< Oracle Programming

Listing the capabilities of SQL SELECT statementsEdit

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 statementEdit

    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

See alsoEdit