Relational Database Concepts and SQL Fundamentals
Relational Concepts and SQL Fundamentals
This domain tests the vocabulary and rules behind Oracle’s relational model and the categories of SQL used throughout the exam. Questions focus on keys, NULL semantics, schema ownership, data types, and statement types rather than on one query syntax.
Core rules
- A table is a relation: rows are tuples and columns are attributes. Each column has a data type and optional constraints.
- A primary key uniquely identifies each row and cannot contain NULL. A foreign key references a primary or unique key and enforces referential integrity; it may be NULL unless declared NOT NULL.
- NULL means unknown or absent, not zero or a blank string. Comparisons with NULL use IS NULL or IS NOT NULL; = NULL is never true.
- SQL categories: DQL (SELECT), DML (INSERT, UPDATE, DELETE, MERGE), DDL (CREATE, ALTER, DROP, TRUNCATE), DCL (GRANT, REVOKE), and TCL (COMMIT, ROLLBACK, SAVEPOINT).
- In Oracle, a user owns a schema, and the schema name usually matches the user name. Objects are referenced as schema.object.
- Common types include VARCHAR2, CHAR, NUMBER, DATE, TIMESTAMP, and CLOB. CHAR pads values with blanks; VARCHAR2 does not.
Worked example
An employees table has emp_id NUMBER PRIMARY KEY, dept_id NUMBER REFERENCES departments(dept_id), and salary NUMBER. A row with dept_id NULL is valid because the foreign key is nullable. To find it, use SELECT emp_id FROM employees WHERE dept_id IS NULL. Writing dept_id = NULL returns no rows because the result is UNKNOWN, even if such rows exist.
Traps
- Treating NULL as equal to NULL or as zero.
- Assuming a primary key is always a single column; composite primary keys are common.
- Forgetting that DDL implicitly commits.
- Confusing CHAR blank padding with VARCHAR2 storage.