2.1 SQL SELECT Fundamentals & Projection

SQL SELECT Fundamentals & Projection

The SELECT statement is the fundamental Data Query Language (DQL) command in Oracle SQL. It empowers database developers and analysts to query data stored in relational database tables, views, and materialized views. Understanding how Oracle processes query syntax, projects specific attributes, evaluates mathematical operations, and formats column headers provides the bedrock for mastering the Oracle Database SQL Certified Associate (1Z0-071) examination.

---

1. Relational Query Capabilities: Projection, Selection, and Joining

Dr. E.F. Codd defined three core relational operations that a relational query language must support. The Oracle SQL SELECT statement implements all three:

+-----------------------------------------------------------------------------+
|                   RELATIONAL QUERY CAPABILITIES IN SQL                     |
|                                                                             |
|   1. PROJECTION (Vertical Subset)                                           |
|      SELECT employee_id, last_name, salary FROM employees;                  |
|      +-------------+-------------+----------+                               |
|      | EMPLOYEE_ID | LAST_NAME   | SALARY   |  <-- Only specified columns   |
|      +-------------+-------------+----------+                               |
|                                                                             |
|   2. SELECTION / RESTRICTION (Horizontal Subset)                            |
|      SELECT * FROM employees WHERE department_id = 60;                      |
|      +-------------+-------------+----------+                               |
|      | 103         | Hunold      | 9000     |  <-- Only matching rows       |
|      | 104         | Ernst       | 6000     |                               |
|      +-------------+-------------+----------+                               |
|                                                                             |
|   3. JOINING (Combining Tables)                                             |
|      SELECT e.last_name, d.department_name                                  |
|      FROM employees e JOIN departments d ON e.department_id = d.department_id;|
|      +-------------+-----------------------+                                |
|      | King        | Executive             |  <-- Data from linked tables   |
|      +-------------+-----------------------+                                |
+-----------------------------------------------------------------------------+

---

2. Basic SELECT Syntax and Clause Requirements

In Oracle SQL, the simplest valid query requires at least two mandatory clauses: the SELECT clause and the FROM clause.

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

• SELECT Clause: Identifies the column list or calculated expressions to be projected into the output.
• FROM Clause: Identifies the database table, view, inline view, or synonym containing the projected columns.

Querying All Columns (*)

To retrieve every column in the exact sequence in which the columns were defined when the table was created (or altered), use the asterisk wildcard character (*):

SELECT * 
FROM employees;

[!NOTE] While SELECT * is convenient for ad-hoc querying, writing production queries with explicitly named columns is an industry best practice. Explicit projection reduces network bandwidth overhead and prevents application failure if table schema structures change.

Querying Specific Columns (Projection)

To project specific attributes, separate each column name with a comma:

SELECT employee_id, first_name, last_name, email, hire_date
FROM employees;

Simulated Result Set:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     HIRE_DATE
----------- -------------------- ------------------------- ------------------------- ---------
        100 Steven               King                      SKING                     17-JUN-03
        101 Neena                Kochhar                   NKOCHHAR                  21-SEP-05
        102 Lex                  De Haan                   LDEHAAN                   13-JAN-01

---

3. Arithmetic Expressions and Operator Precedence

You can perform mathematical operations on projected numeric and date columns using standard arithmetic operators.

Operator Precedence Hierarchy

Oracle evaluates arithmetic operators based on strict precedence rules:

1. Parentheses (): Expressions inside parentheses are evaluated first, from the innermost level to the outermost level.
2. Multiplication and Division (*, /): Evaluated second. If multiple * and / operators occur together, Oracle evaluates them from left to right.
3. Addition and Subtraction (+, -): Evaluated last. If multiple + and - operators occur together, Oracle evaluates them from left to right.

-- Expression 1: Multiplication takes precedence over addition
SELECT last_name, salary, 12 * salary + 100 
FROM employees;

-- Expression 2: Parentheses override default precedence
SELECT last_name, salary, 12 * (salary + 100) 
FROM employees;

Execution Breakdown:

Suppose salary is 5000:

• In Expression 1: 12 * 5000 = 60000, then 60000 + 100 = 60100.
• In Expression 2: 5000 + 100 = 5100, then 12 * 5100 = 61200.

+-----------------------------------------------------------------------------+
|                     ARITHMETIC PRECEDENCE COMPARISON                        |
|                                                                             |
|   Query 1: 12 * salary + 100                                                |
|   Step 1: [12 * 5000] = 60000   (Multiplication First)                      |
|   Step 2: [60000 + 100] = 60100 (Addition Second)                           |
|                                                                             |
|   Query 2: 12 * (salary + 100)                                              |
|   Step 1: (5000 + 100) = 5100   (Parentheses First)                         |
|   Step 2: [12 * 5100] = 61200   (Multiplication Second)                     |
+-----------------------------------------------------------------------------+

---

4. Default Column Headings & Formatting Behavior

When Oracle SQL outputs the results of a query, default formatting rules apply to the column headings:

• Default Text Case: Column headings are displayed in ALL UPPERCASE letters.
• Default Alignment:
  • Character and Date column values/headings are left-aligned.
  • Numeric column values/headings are right-aligned.
• Calculated Headers: If an expression is projected without an alias, Oracle displays the raw expression string in uppercase (e.g., SALARY*12).

---

5. Column Aliases: Rules, Syntax, and Options

A column alias renames a column heading in the query result set. Aliases are essential for making calculated expressions readable and giving business-friendly titles to database fields.

Basic Column Alias Syntax:

-- Method A: Space separator (standard SQL)
SELECT last_name name, salary * 12 annual_salary
FROM employees;

-- Method B: Using optional 'AS' keyword (recommended for readability)
SELECT last_name AS name, salary * 12 AS annual_salary
FROM employees;

[!TIP] The AS keyword is optional between the column name/expression and its alias. However, using AS makes the code significantly easier to read and maintain.

Double-Quoted Aliases: Preserving Case, Spaces, and Special Symbols

By default, Oracle converts all unquoted column aliases to uppercase in the result set. To override this default, you must enclose the alias in double quotation marks ("...").

Double quotation marks are mandatory when an alias:

1. Contains spaces (e.g., "Annual Salary").
2. Requires exact mixed-case or lowercase formatting (e.g., "Monthly Pay").
3. Contains special characters or punctuation (e.g., "Total ($)", "Employee #").
4. Matches a reserved SQL keyword (e.g., "SELECT", "FROM", "GROUP").

SELECT last_name AS "Employee Surname",
       salary AS "Monthly",
       salary * 12 AS "Annual Salary ($)",
       department_id AS "Dept #"
FROM employees;

Result Set Display:

Employee Surname            Monthly Annual Salary ($)     Dept #
------------------------- --------- ----------------- ----------
King                          24000            288000         90
Kochhar                       17000            204000         90
De Haan                       17000            204000         90

---

6. Critical 1Z0-071 Exam Traps: Quotes and Keywords

Trap 1: Single Quotes vs. Double Quotes

This is one of the most frequently tested syntax traps on the 1Z0-071 exam:

• Single quotes ('...'): Used exclusively for character string and date literals (data values).
• Double quotes ("..."): Used exclusively for database object identifiers, column aliases, and case-sensitive schema names.

-- INVALID: Using single quotes for an alias triggers ORA-00923
SELECT last_name AS 'Employee Name' FROM employees;
-- ORA-00923: FROM keyword not found where expected

-- VALID: Double quotes must be used for aliases
SELECT last_name AS "Employee Name" FROM employees;

Trap 2: The AS Keyword in Table Aliases vs. Column Aliases

• Column Aliases: The AS keyword is optional and valid (SELECT salary AS sal FROM employees).
• Table Aliases: The AS keyword is STRICTLY INVALID in Oracle SQL (FROM employees AS e fails with ORA-00933: SQL command not properly ended).

Trap 3: Missing Commas and Accidental Aliasing

If you omit a comma between two column names, Oracle does not report a syntax error; instead, it treats the second column name as an alias for the first column!

-- Intent: Retrieve first_name AND last_name
-- Actual execution: Retrieves first_name, renaming the column header to LAST_NAME
SELECT first_name last_name 
FROM employees;

Trap 4: Trailing Commas in the SELECT List

Placing a comma after the final expression before the FROM keyword is invalid:

-- INVALID: Trailing comma before FROM
SELECT employee_id, last_name, salary, 
FROM employees;
-- ORA-00936: missing expression