Introduction to Oracle Database
What the domain covers
This domain checks whether you understand the Oracle Database environment before writing queries: the difference between a database and an instance, users and schemas, tables and rows, and the SQL statement families. It also tests basic metadata knowledge, including DUAL and DESCRIBE.
Core rules
- A database is the physical set of files; an instance is the memory and background processes that open it. A user is an account; a schema is the set of objects owned by that user. They often share a name but are not the same concept.
- Tables store data in rows and columns. Each column has a data type; each row is one occurrence.
- SQL statements are grouped as DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE, MERGE), DCL (GRANT, REVOKE), and TCL (COMMIT, ROLLBACK, SAVEPOINT).
- DUAL is a one-row, one-column table owned by SYS and available to all users. Use it for expressions that need no table.
- DESCRIBE displays a table's column names, data types, and nullability.
Worked example
SELECT SYSDATE FROM dual;
Reasoning: SYSDATE returns the database server date and time. It needs a FROM clause in Oracle SQL, so DUAL supplies exactly one row. The result is one row with one column, not a list of all tables. If you wrote SELECT SYSDATE; it would fail because Oracle requires FROM.
Traps
Do not call COMMIT a DDL statement; it is TCL. Do not say a schema is a user: a schema is owned by a user. DUAL is not a user table you create. TRUNCATE is DDL and cannot be rolled back, unlike DELETE.