SQL Fundamentals: A Complete Guide for Oracle Developers

1. Introduction to SQL

SQL (Structured Query Language) is the standard language for interacting with relational databases. It allows you to store, retrieve, manipulate, and manage data (Elmasri & Navathe, 2017). Oracle Database uses SQL as its primary interface, supplemented by PL/SQL for procedural extensions (Oracle, 2025b).

DDL (Data Definition Language): Defines database structures (CREATE, ALTER, DROP).
DML (Data Manipulation Language): Manages data within objects (SELECT, INSERT, UPDATE, DELETE).
DCL (Data Control Language): Manages access and permissions (GRANT, REVOKE).
TCL (Transaction Control Language): Manages transactions (COMMIT, ROLLBACK, SAVEPOINT).

2. Using SQL*Plus

SQL*Plus is the classic command-line interface for Oracle Database. It allows developers to execute SQL statements, run PL/SQL blocks, and format query results (Oracle, 2025a).

Common Commands:

CONNECT username/password - Logs into the database.
DESCRIBE table_name - Shows the structure of a table.
SET LINESIZE 200 - Adjusts the width of the output.
SPOOL output.txt - Saves query results to a file.
/ - Executes the previous SQL statement or PL/SQL block again.

3. Restricting and Sorting Data

Use the WHERE clause to filter rows and the ORDER BY clause to sort the output (Elmasri & Navathe, 2017).

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 5000
ORDER BY salary DESC;

Comparison Operators: =, >, <, >=, <=, <> (not equal).
Logical Operators: AND, OR, NOT.
Special Operators: BETWEEN, IN, LIKE (with wildcards % and _), IS NULL.

4. Using Single-Row Functions to Customize Output

Single-row functions operate on a single row and return one result per row. They can be used in the SELECT, WHERE, and ORDER BY clauses (Oracle, 2025a).

Character Functions: UPPER(), LOWER(), INITCAP(), SUBSTR(), LENGTH(), TRIM().
Number Functions: ROUND(), TRUNC(), MOD().
Date Functions: SYSDATE, MONTHS_BETWEEN(), ADD_MONTHS(), NEXT_DAY().
Conversion Functions: TO_CHAR(), TO_DATE(), TO_NUMBER().
General Functions: NVL() (replaces null), COALESCE() (returns first non-null), NULLIF().

5. Reporting Aggregated Data Using Group Functions

Group functions operate on sets of rows to give one result per group. They are often used with the GROUP BY clause (Elmasri & Navathe, 2017).

Common Group Functions: AVG(), COUNT(), MAX(), MIN(), SUM().
Rules: You must include all columns in the SELECT list that are not inside a group function in the GROUP BY clause.
Filtering Groups: Use the HAVING clause to restrict groups (unlike WHERE, which restricts individual rows).

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

6. Displaying Data from Multiple Tables

Use joins to combine rows from two or more tables based on a related column (Elmasri & Navathe, 2017).

INNER JOIN: Returns only rows that have matching values in both tables.
LEFT OUTER JOIN: Returns all rows from the left table, and matching rows from the right.
RIGHT OUTER JOIN: Returns all rows from the right table, and matching rows from the left.
FULL OUTER JOIN: Returns all rows when there is a match in either table.
CROSS JOIN: Returns the Cartesian product of both tables.

SELECT e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

7. Using Subqueries to Solve Queries

A subquery is a query inside another query. It is used to return data that will be used by the main query (outer query) (Oracle, 2025a).

Single-row Subquery: Returns one row. Uses single-row operators (=, >, <).
Multiple-row Subquery: Returns multiple rows. Uses multi-row operators (IN, ANY, ALL).
Correlated Subquery: References a column from the outer query.

SELECT last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

8. Using the Set Operators

Set operators combine the results of two or more queries into a single result set (Elmasri & Navathe, 2017).

UNION: Returns all distinct rows from both queries.
UNION ALL: Returns all rows (including duplicates) from both queries. Faster than UNION because no sorting is done.
INTERSECT: Returns rows that appear in both queries.
MINUS: Returns rows from the first query that do not appear in the second query.

9. Manipulating Data

DML statements are used to manage data inside tables (Elmasri & Navathe, 2017).

INSERT: Adds new rows to a table.
UPDATE: Modifies existing rows.
DELETE: Removes rows from a table.
Transactions: Changes are not permanent until you execute a COMMIT. You can undo changes with ROLLBACK or set a partial rollback point with SAVEPOINT.

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 50;
COMMIT;

10. Using DDL Statements to Create and Manage Tables

DDL statements define, change, and remove database structures (Oracle, 2025a).

CREATE TABLE: Creates a new table with columns and data types.
ALTER TABLE: Adds, modifies, or drops columns, or adds constraints.
DROP TABLE: Deletes the table and its data permanently.
TRUNCATE TABLE: Removes all rows from a table but keeps the structure. It is a DDL command and cannot be rolled back.
Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.

CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    last_name VARCHAR2(25) NOT NULL,
    salary NUMBER(8,2),
    department_id NUMBER(4)
);

11. Creating Other Schema Objects

Beyond tables, Oracle supports several other schema objects to improve performance and manage data logic (Oracle, 2025b).

Views: A virtual table based on the result-set of a SQL statement. CREATE VIEW emp_view AS SELECT ...
Sequences: Generates unique numbers automatically (useful for primary keys). CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;
Indexes: Speeds up the retrieval of rows. CREATE INDEX idx_emp_name ON employees(last_name);
Synonyms: An alias for a table, view, sequence, or other schema object. CREATE SYNONYM emp FOR employees;

12. Managing Objects with Data Dictionary Views

The data dictionary is a set of read-only tables and views that provide information about the database. It is owned by the SYS user (Oracle, 2025b).

USER_ Views: Shows objects owned by the current user (e.g., USER_TABLES, USER_VIEWS).
ALL_ Views: Shows objects the current user can access (e.g., ALL_TABLES).
DBA_ Views: Shows all objects in the database (requires DBA privileges).

-- Find all tables you own
SELECT table_name FROM user_tables;

-- Find all constraints on your tables
SELECT constraint_name, constraint_type, table_name 
FROM user_constraints;

Conclusion: Mastering these SQL fundamentals is the foundation for becoming a proficient Oracle developer. From simple queries to managing complex schema objects, these concepts are used daily in application development and database administration (Elmasri & Navathe, 2017).

References

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

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

Oracle. (2025b). Database concepts (19c). Oracle Help Center. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/

Pages