SQL Fundamentals II

Advanced SQL Data Retrieval Techniques

Once you have mastered the fundamentals of SQL, the next step is learning how to manipulate and retrieve data using advanced techniques. This guide covers the essential advanced SQL features used by Oracle developers to build efficient, high-performance database applications (Elmasri & Navathe, 2017).

1. Datetime Functions

Oracle provides powerful functions for manipulating dates and times. Modern Oracle databases support both DATE and TIMESTAMP data types (Oracle, 2023a).

  • SYSDATE and SYSTIMESTAMP: Returns the current database server date and time.
  • ADD_MONTHS(date, n): Adds or subtracts a specific number of months from a date.
  • MONTHS_BETWEEN(date1, date2): Returns the number of months between two dates.
  • NEXT_DAY(date, 'day'): Returns the date of the first specified weekday after a given date.
  • LAST_DAY(date): Returns the last day of the month for a given date.
  • EXTRACT(field FROM date): Extracts a specific component from a date or timestamp (Oracle, 2023a).

2. ROLLUP, CUBE, and GROUPING SETS

These extensions to the GROUP BY clause allow you to generate multiple grouping sets in a single query, producing subtotals and grand totals (Elmasri & Navathe, 2017).

  • ROLLUP: Creates a hierarchical grouping. It produces subtotals moving from the most detailed level to the grand total.
  • CUBE: Produces subtotals for all possible combinations of the specified columns.
  • GROUPING SETS: Allows you to explicitly specify exactly which groups you want to calculate (Oracle, 2023b).
-- Example using ROLLUP (Oracle, 2023b)
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id);

3. Hierarchical Queries

Hierarchical queries are used to retrieve data that has a parent-child relationship (e.g., an organizational chart). Oracle processes these queries by first evaluating any joins, then the CONNECT BY condition, and finally any remaining WHERE clauses (Oracle, 2026a).

  • CONNECT BY PRIOR: Defines the relationship between the parent and child rows.
  • START WITH: Specifies the root row(s) of the hierarchy.
  • LEVEL: A pseudo-column that returns the depth of the row in the hierarchy (root is 1).
  • SYS_CONNECT_BY_PATH: Returns the full path from the root to the current row (Oracle, 2026a).
-- Example from Oracle documentation (Oracle, 2026a)
SELECT employee_id, last_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

4. Correlated Subqueries

A correlated subquery is a subquery that depends on the outer query for its values. It is executed once for each row processed by the outer query (Elmasri & Navathe, 2017).

  • Unlike regular subqueries, they cannot be executed independently.
  • They are often used with the EXISTS or NOT EXISTS operators.
-- Find employees who earn more than the average salary of their department
SELECT last_name, salary, department_id
FROM employees e
WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department_id = e.department_id);

5. Multitable Inserts

Oracle allows you to insert data into multiple tables in a single statement, which is highly efficient for data warehousing and ETL processes (Oracle, 2023b).

  • Unconditional INSERT ALL: Inserts the same data into all specified tables.
  • Conditional INSERT ALL: Uses WHEN clauses to insert into different tables based on conditions.
  • INSERT FIRST: Evaluates WHEN conditions; once a condition is true, subsequent conditions are skipped.
-- Example of Conditional INSERT ALL
INSERT ALL
  WHEN salary > 10000 THEN INTO high_paid_emp (emp_id, name)
  WHEN salary <= 10000 THEN INTO low_paid_emp (emp_id, name)
SELECT employee_id, last_name, salary
FROM employees;

6. MERGE Operation

The MERGE statement (also known as "Upsert") allows you to update and insert data into a table in a single statement based on a condition. It is a deterministic statement, meaning you cannot update the same row of the target table multiple times in the same MERGE statement (Oracle, 2025b).

  • If the row exists (match found on the ON condition), it executes the UPDATE clause.
  • If the row does not exist (no match), it executes the INSERT clause.
MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name, t.salary = s.salary
WHEN NOT MATCHED THEN
  INSERT (id, name, salary) VALUES (s.id, s.name, s.salary);

7. External Tables

External tables allow you to query data stored outside the database (such as flat files) as if it were a regular table. If you omit the TYPE specification, ORACLE_LOADER is the default access driver (Oracle, 2026b).

  • Uses the ORACLE_LOADER or ORACLE_DATAPUMP access drivers.
  • Great for loading data from CSV or text files without using SQL*Loader.
  • External tables are read-only.
CREATE TABLE ext_employees (
  emp_id NUMBER,
  emp_name VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS (FIELDS TERMINATED BY ',')
  LOCATION ('employees.csv')
);

8. Regular Expression Usage

Oracle SQL supports regular expressions for complex pattern matching. The implementation complies with the IEEE POSIX standard for ASCII data and extends it to support multilingual data (Oracle, 2026c).

  • REGEXP_LIKE: Performs a regular expression match (similar to LIKE).
  • REGEXP_INSTR: Returns the position of a regular expression match.
  • REGEXP_SUBSTR: Extracts a substring that matches the pattern.
  • REGEXP_REPLACE: Replaces a substring matching the pattern.
-- Find phone numbers that match a specific pattern
SELECT last_name FROM employees
WHERE REGEXP_LIKE(phone_number, '^[0-9]{3}-[0-9]{3}-[0-9]{4}$');

-- Extract only the digits from a string
SELECT REGEXP_REPLACE('Phone: 123-ABC-456', '[^0-9]', '') FROM dual; 
-- Output: 123456

Conclusion: Mastering these advanced SQL techniques will significantly improve your ability to write efficient, complex queries. From generating analytical reports with ROLLUP to cleaning data with Regular Expressions, these tools are essential for any serious Oracle developer (Elmasri & Navathe, 2017).

References

Elmasri, R., & Navathe, S. B. (2017). Fundamentals of database systems (7th ed.). Pearson.

Oracle. (2023a). Oracle Database SQL language reference, 19c. Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/

Oracle. (2023b). Oracle Database SQL language reference, 19c: GROUP BY clause. Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html

Oracle. (2025b). Oracle Database SQL language reference, 19c: MERGE. Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html

Oracle. (2026a). Oracle Database SQL language reference: Hierarchical queries. Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Hierarchical-Queries.html

Oracle. (2026b). Oracle Database administrator's guide: Managing external tables. Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/26/admin/

Oracle. (2026c). Oracle Database development guide: Using regular expressions in database applications. Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/26/adfns/

No comments:

Post a Comment

Subscribe to: Posts (Atom)