Oracle Database SQL Certified Associate: key terms

Flashcards for this exam Oracle Database SQL Certified Associate guide

• ACIDThe four properties of reliable transactions: Atomicity, Consistency, Isolation and Durability.Foundational
• Aggregate FunctionA function that computes one value from a set of rows, such as SUM, AVG, COUNT, MIN, MAX, STDDEV or LISTAGG.Foundational
• ALTER TABLE StatementThe DDL statement that changes an existing table's structure, such as adding, dropping or modifying columns and constraints.Foundational
• Alternate KeyA candidate key that was not chosen as the primary key, usually enforced with a UNIQUE constraint.Intermediate
• ANSI SQLThe vendor-neutral SQL standard (ISO/IEC 9075) that defines core syntax and behavior that relational databases implement.Intermediate
• BETWEEN OperatorA predicate that tests whether a value lies within an inclusive range: x BETWEEN a AND b means x >= a AND x <= b.Foundational
• Bitmap IndexAn index storing a bit array per distinct value, efficient for low-cardinality columns in read-heavy analytic workloads.Advanced
• Candidate KeyAny minimal set of columns that could uniquely identify every row; one is chosen as the primary key.Intermediate
• Cartesian ProductThe set of all possible row pairings between two tables, produced by a cross join or a missing join condition.Intermediate
• Cascading Referential ActionForeign key options (CASCADE, SET NULL, SET DEFAULT, NO ACTION, RESTRICT) that define what happens to child rows when a parent row changes.Intermediate
• CASE ExpressionSQL's conditional expression, which returns a value based on the first WHEN condition that is true.Foundational
• CAST FunctionThe ANSI SQL function that explicitly converts a value to another data type: CAST(expression AS type).Foundational
• Check ConstraintA rule that limits the values a column can accept to those that satisfy a Boolean expression.Intermediate
• COALESCE FunctionA function that returns the first non-NULL argument in its list.Foundational
• ColumnA named attribute of a table with a defined data type that every row stores a value (or NULL) for; also called a field.Foundational
• Column AliasA temporary name given to a column or expression in a query's result using AS.Foundational
• COMMIT StatementThe TCL command that makes all changes in the current transaction permanent and visible to other sessions.Foundational
• Common Table Expression (CTE)A named temporary result set defined with WITH that can be referenced in the statement that follows.Intermediate
• Composite KeyA key made of two or more columns that together uniquely identify a row.Intermediate
• Correlated SubqueryA subquery that references columns from the outer query and is logically evaluated once per outer row.Intermediate
• COUNT FunctionAn aggregate that counts rows: COUNT(*) counts every row, COUNT(col) counts non-null values, COUNT(DISTINCT col) counts unique non-null values.Foundational
• CREATE TABLE StatementThe DDL statement that defines a new table with its columns, data types and constraints.Foundational
• CROSS JOINA join with no condition that returns every combination of rows from both tables (the Cartesian product).Intermediate
• CursorA database object that lets procedural code process a query result one row at a time.Advanced
• Data Control Language (DCL)The SQL commands that manage permissions: GRANT, REVOKE and, in SQL Server, DENY.Foundational
• Data Definition Language (DDL)The SQL commands that define and change database structure: CREATE, ALTER, DROP, TRUNCATE and RENAME.Foundational
• Data DictionaryA repository of metadata describing each table, column, data type, constraint and meaning in a database.Foundational
• Data Manipulation Language (SQL) (DML)The SQL commands that add, change or remove rows: INSERT, UPDATE, DELETE and MERGE (and, in some classifications, SELECT).Foundational
• Database IndexA data structure that speeds up row lookups on one or more columns at the cost of extra storage and slower writes.Foundational
• Database NormalizationThe process of organizing tables to reduce redundancy and prevent update anomalies by applying normal forms.Foundational
• Database SchemaThe structure of a database (its tables, columns, types, keys and relationships), or a named namespace that groups database objects.Foundational
• Database TableThe core relational storage object: a named set of rows sharing the same columns; formally called a relation.Foundational
• Database TransactionA logical unit of work of one or more operations that must succeed or fail as a whole.Foundational
• Default ConstraintA constraint that supplies a value for a column when an INSERT does not specify one.Foundational
• DELETE StatementThe DML statement that removes rows that match a condition from a table, logging each row.Foundational
• Derived TableA subquery in the FROM clause that acts as a temporary inline table, which must have an alias.Intermediate
• DISTINCT KeywordA SELECT modifier that removes duplicate rows from the result.Foundational
• DROP StatementThe DDL command that permanently removes a database object, such as a table, view, index or schema, together with its data.Foundational
• Dynamic SQLSQL statements built as strings at run time and then executed, for example with EXEC, sp_executesql or EXECUTE IMMEDIATE.Advanced
• Entity IntegrityThe relational rule that every table has a primary key and that no primary key value is NULL or duplicated.Intermediate
• EXCEPT OperatorA set operator that returns distinct rows from the first query that are not in the second; Oracle calls it MINUS.Intermediate
• EXISTS OperatorA predicate that returns true if a subquery returns at least one row.Intermediate
• First Normal Form (1NF)A table is in 1NF when every column holds atomic (single) values, there are no repeating groups, and each row is unique.Foundational
• Foreign Key (FK)A column in one table that references the primary or unique key of another table, enforcing a relationship between them.Foundational
• FULL OUTER JOINA join that returns all rows from both tables, matching where possible and filling NULLs on whichever side has no match.Intermediate
• GRANT StatementThe DCL statement that gives privileges on database objects to users or roles.Foundational
• GROUP BY ClauseThe clause that groups rows sharing the same values so that aggregate functions return one row per group.Foundational
• GROUPING SETSA GROUP BY extension that lists exactly which groupings to compute in a single query.Advanced
• HAVING ClauseThe clause that filters groups after GROUP BY, typically using aggregate conditions.Foundational
• Implicit ConversionAutomatic conversion of one data type to another by the database engine when an expression mixes types.Intermediate
• IN OperatorA predicate that tests whether a value matches any value in a list or subquery.Foundational
• INNER JOINA join that returns only rows with matching values in both tables.Foundational
• INSERT StatementThe DML statement that adds new rows to a table, from literal values or from a SELECT.Foundational
• INTERSECT OperatorA set operator that returns only the distinct rows present in both query results.Intermediate
• LAG and LEAD FunctionsWindow functions that return a value from a previous row (LAG) or a following row (LEAD) within a partition.Intermediate
• LEFT OUTER JOINA join that returns every row from the left table plus matching rows from the right, with NULLs where no match exists.Foundational
• LIKE OperatorA predicate that matches strings against a pattern using wildcards: % for any sequence and _ for a single character.Foundational
• LIMIT ClauseA clause that restricts how many rows a query returns; the syntax varies (LIMIT, TOP, FETCH FIRST n ROWS ONLY).Foundational
• MERGE StatementA single DML statement that inserts, updates or deletes target rows based on whether they match rows in a source.Intermediate
• NOT EXISTSA predicate that is true when a correlated subquery returns no rows; the NULL-safe way to write an anti join.Intermediate
• NOT NULL ConstraintA constraint requiring that a column always contains a value.Foundational
• NTILE FunctionA window function that divides ordered rows into a specified number of roughly equal buckets and returns each row's bucket number.Advanced
• NULLA marker meaning a value is missing or unknown; it is not zero, an empty string or false.Foundational
• NULLIF FunctionA function that returns NULL if its two arguments are equal, otherwise the first argument.Intermediate
• Oracle Autonomous DatabaseOracle's self-driving, self-securing, self-repairing cloud database that automates tuning, patching, scaling and backups.Foundational
• ORDER BY ClauseThe clause that sorts the final result set by one or more expressions, ascending (default) or descending.Foundational
• PIVOT and UNPIVOTSQL operators that rotate data: PIVOT turns row values into columns, and UNPIVOT turns columns into rows.Intermediate
• PL/SQLOracle's procedural extension of SQL, used to write blocks, procedures, functions, packages and triggers.Intermediate
• Primary Key (PK)A column or set of columns whose values uniquely identify each row in a table; it cannot contain NULLs.Foundational
• RANK and DENSE_RANKRanking window functions: RANK gives tied rows the same rank and leaves gaps; DENSE_RANK gives ties the same rank without gaps.Intermediate
• Recursive CTEA CTE that references itself to traverse hierarchical or graph data such as org charts or bills of materials.Advanced
• Referential IntegrityThe guarantee that every foreign key value matches an existing key in the referenced table, so no orphaned rows exist.Foundational
• Relational DatabaseA database that stores data in tables of rows and columns, linked through keys, and queried with SQL.Foundational
• Relational Database Management System (RDBMS)Software that creates, manages and queries relational databases, enforcing schemas, constraints, security and transactions.Foundational
• REVOKE StatementThe DCL statement that removes previously granted privileges from a user or role.Foundational
• RIGHT OUTER JOINA join that returns every row from the right table plus matching rows from the left, with NULLs where no match exists.Foundational
• ROLLBACK StatementThe TCL command that undoes all uncommitted changes in the current transaction, or back to a savepoint.Foundational
• ROLLUP and CUBEGROUP BY extensions that add subtotal rows: ROLLUP for hierarchical subtotals and CUBE for every combination of the grouping columns.Advanced
• RowA single record in a table, holding one value (or NULL) for each column; called a tuple in relational theory.Foundational
• SAVEPOINT (SQL)A named marker inside a transaction that allows a partial rollback to that point without abandoning the whole transaction.Intermediate
• Second Normal Form (2NF)A table is in 2NF when it is in 1NF and every non-key column depends on the whole primary key, not part of it.Intermediate
• SELECT StatementThe SQL statement that retrieves rows and columns from one or more tables or views.Foundational
• Self JoinA join of a table to itself, using two aliases, to compare rows within the same table.Intermediate
• SequenceA standalone database object that generates unique numbers on request, independent of any one table.Intermediate
• SQL Data TypesThe categories of values a column can hold: numeric (INT, DECIMAL, FLOAT), character (CHAR, VARCHAR), date/time, Boolean, binary and semi-structured.Foundational
• SQL Logical Query Processing OrderThe order in which SQL logically evaluates clauses: FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT/TOP.Intermediate
• Stored ProcedureA named, precompiled set of SQL (and procedural) statements stored in the database and executed with CALL or EXEC.Intermediate
• Structured Query Language (SQL)The standard declarative language for defining, querying and manipulating data in relational databases.Foundational
• SubqueryA query nested inside another statement, used as a value, a list, or a derived table.Foundational
• Table AliasA short temporary name for a table in a query, used to qualify columns and required for self joins.Foundational
• Third Normal Form (3NF)A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column (no transitive dependencies).Intermediate
• Three-Valued Logic (3VL)SQL's logic system, in which conditions evaluate to TRUE, FALSE or UNKNOWN because of NULLs.Advanced
• Top-N QueryA query that returns the N highest or lowest rows overall or per group.Intermediate
• Transaction Control Language (TCL)The SQL commands that manage transactions: BEGIN/START TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT.Foundational
• TriggerProcedural code that runs automatically in response to INSERT, UPDATE or DELETE events (or DDL events) on a table.Intermediate
• TRUNCATE StatementA statement that quickly removes all rows from a table while keeping its structure, typically with minimal logging.Intermediate
• UNION ALLA set operator that stacks the results of two queries and keeps all rows, including duplicates.Foundational
• UNION OperatorA set operator that combines the results of two queries into one set and removes duplicate rows.Foundational
• Unique ConstraintA constraint that prevents duplicate values in a column or column combination.Foundational
• UPDATE StatementThe DML statement that modifies existing column values in rows that match a condition.Foundational
• User-Defined Function (UDF)A custom function created in the database that returns a scalar value or a table and can be called inside SQL statements.Intermediate
• ViewA named stored query that behaves like a virtual table; it stores no data and runs its query when referenced.Foundational
• WHERE ClauseThe clause that filters individual rows before grouping, keeping only those where the condition is true.Foundational
• Window FrameThe subset of rows within a partition that a window function considers, set with ROWS or RANGE BETWEEN.Advanced
• Window FunctionA function that computes a value across a set of rows related to the current row, defined with OVER(), without collapsing them.Intermediate