Oracle SQL Introduction & Single-Row Functions – Detailed Study Notes

Introduction to Oracle, Database Versions & Architecture

• Oracle positioned as a cloud/data-service provider analogous to Amazon AWS, Microsoft Azure, Google Cloud.
• "11g Express Edition" (XE) mentioned as the version used.
  • Version information available through dynamic performance view $\text{V\$\$VERSION}$ (queried as SELECT * FROM v$version;).
• Oracle uses a client–server architecture:
  • Database server stores/manages data.
  • Client applications (SQL Developer, etc.) issue SQL commands over sessions/connections.

SQL Worksheet, Connections & Schemas

• A SQL worksheet is where we write queries.
• Multiple connections/users (e.g., HR schema) selectable; active connection shown in upper-left of SQL Developer.
• Tables in the HR schema include EMPLOYEES, DEPARTMENTS, etc.
• Double-click a table or DESC table_name; to view structure (column names & data types).

Core SELECT Syntax & Column Handling

• Prototype: SELECT column_list FROM table_name;
  • * means “all columns.”
  • Comma-separate specific columns, e.g. SELECT employee_id, first_name FROM employees;
• Commenting in SQL Developer:
  • Single-line comment: -- comment text

Aliases & Calculated Columns

• AS keyword optional: SELECT first_name AS name FROM employees;
• Aliases affect display labels only; they are not valid in WHERE clauses.
  • Example: SELECT first_name AS f_name ... WHERE first_name = 'Allen'; (not WHERE f_name = 'Allen').
• Concatenation operator || builds virtual/derived columns:
  SELECT first_name || '_' || last_name AS full_name FROM employees;

Filtering Rows – WHERE Clause

• Equality & comparison: WHERE employee_id = 100.
• String literals must be in single quotes and are case-sensitive.
• Wildcards with LIKE:
  • % – any length string, _ – exactly one character.
  • LIKE 'A%' ⟹ starts with A.
  • LIKE '__e%' ⟹ third letter is e.
  • Use NOT LIKE to negate.
• Logical operators:
  • AND (all conditions true), OR (any), NOT (negation).
• IN / NOT IN for multi-value matches:
  WHERE employee_id IN (100,101,102,103);
• BETWEEN a AND b inclusive:
  WHERE salary BETWEEN 4000 AND 6000;
• IS NULL / IS NOT NULL – never use = NULL.

Sorting – ORDER BY

• Default order is ascending (ASC).
• DESC for descending:
  ORDER BY salary DESC, employee_id ASC;
• Columns can be referenced by position: ORDER BY 1, 2 DESC.
• Calculated columns may be referenced in ORDER BY (because ordering happens after SELECT list evaluation).

Special One-Row Table – DUAL

• Built-in dummy table with one column DUMMY CHAR(1) and one row.
• Often used for expression evaluation:
  SELECT 2+3 FROM dual; → 5.

Arithmetic Operators

• + - * / MOD (remainder):
  SELECT MOD(9,2) FROM dual; → 1.

Set Operators (rows must be union-compatible: same number & data-types)

• UNION – combines & removes duplicates.
• UNION ALL – combines without duplicate elimination (better performance).
• MINUS – rows in first query not found in second.
• Notes:
  • Column names in the final set come from the first SELECT.
  • Data-type mismatch or column-count mismatch raises error.

Aggregate Functions (Multi-row → Single value)

• $\text{COUNT}()$ – row count.
  • COUNT(*), COUNT(1) or COUNT(column).
• $\text{SUM}()$ – total of numeric column.
• $\text{AVG}()$ – average value.
• $\text{MIN}()$ / $\text{MAX}()$ – smallest / largest value.
• Often paired with GROUP BY, HAVING (to be covered later).

Conditional Logic

CASE Expression

SELECT salary,
       CASE
         WHEN salary < 10000 THEN 'LOW'
         WHEN salary BETWEEN 10000 AND 15000 THEN 'MEDIUM'
         ELSE 'HIGH'
       END AS salary_class
FROM employees;

• CASE can appear in SELECT list or ORDER BY but not in WHERE unless wrapped (because it’s calculated after WHERE).

DECODE

SELECT first_name,
       DECODE(first_name, last_name, first_name, last_name) AS chosen
FROM employees;

• Oracle-specific; essentially IF value1 = value2 THEN result1 ELSE result2 (supports multiple pairs).
• Limited to equality tests unlike CASE which supports ranges,

Pseudo-Columns

• ROWNUM – sequential numbers assigned after WHERE but before ORDER BY.
• ROWID – unique address of a row in database file; hexadecimal string useful for deletes/updates on duplicates.

Single-Row Character Functions

Single-Row Numeric Functions

• ROUND(num[,dec]) – standard rounding.
• TRUNC(num[,dec]) – drop decimals (no rounding).
• CEIL(num) – smallest integer $\ge$ num.
• FLOOR(num) – largest integer $\le$ num.
• MOD(m,n) – remainder of $m/n$.

Date & Time Functions

Formatting masks examples (TO_CHAR / TO_DATE):

• DD day of month, MON 3-letter month, YYYY 4-digit year.

NULL Handling & Substitution

• NVL(expr, replacement) – if expr NULL, return replacement.
• NVL2(expr, val_if_not_null, val_if_null).
• NULLIF(expr1, expr2) – returns NULL if expr1 = expr2 else expr1.
• COALESCE(e1,e2,…,en) – first non-NULL expression (up to 15 args).

String Pattern & Whitespace Utilities

• % and _ wildcards in LIKE.
• TRIM([LEADING|TRAILING|BOTH] chars FROM str) – advanced trimming; default spaces.
• LPAD/RPAD (not demoed but related) – pad to fixed length.

Performance Note

• UNION ALL faster than UNION because it skips duplicate-removal sort step.

DISTINCT Keyword

• Eliminates duplicates in selected columns.

SELECT DISTINCT department_id FROM employees;

Example: Parsing Email-Like Values

Goal – split string vinila_surya into first & last names.

SELECT SUBSTR(emp_name,                       1,
              INSTR(emp_name,'_')-1) AS first_name,
       SUBSTR(emp_name,INSTR(emp_name,'_')+1) AS last_name
FROM   demo_table;

• Uses INSTR to locate _, then SUBSTR before/after.

Creation & Manipulation (Preview)

• Creating table:
  CREATE TABLE employees_demo (emp_id NUMBER, f_name VARCHAR2(20), l_name VARCHAR2(20));
• Inserting rows:
  INSERT INTO employees_demo VALUES (10,'Alan','Smith');
• Will be explored fully under DDL/DML modules.

Practical Tips & Good Habits

• Always qualify string literals with quotes; case matters for data, not for keywords/table/column names.
• Pin result grids in SQL Developer to compare multiple query outputs.
• Use dynamic views (v$version, dual) for quick checks.
• Practice extensively: instructor will share worksheets containing table-creation scripts and exercises.
• Attendance & session recording housekeeping discussed (minor admin note).