CompTIA DataSys+: key terms

Flashcards for this exam CompTIA DataSys+ guide Class dates

• 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
• Always EncryptedA SQL Server and Azure SQL feature that encrypts sensitive columns on the client so the database engine and DBAs never see plaintext.Advanced
• Always On Availability Groups (AG)A SQL Server high-availability and disaster-recovery feature that replicates a group of databases to secondary replicas with automatic or manual failover.Advanced
• ANSI SQLThe vendor-neutral SQL standard (ISO/IEC 9075) that defines core syntax and behavior that relational databases implement.Intermediate
• Anti JoinA join pattern that returns rows from one table that have no match in another.Intermediate
• Apache CassandraAn open-source, masterless, distributed wide-column database designed for high write throughput and multi-datacenter availability.Intermediate
• Apache HadoopAn open-source framework for distributed storage (HDFS) and batch processing (MapReduce, YARN) on clusters of commodity hardware.Foundational
• Apache HiveA data warehouse system on Hadoop that lets users query files with a SQL-like language (HiveQL) using schema on read.Intermediate
• Apache KafkaA distributed event streaming platform that stores ordered, durable, replayable logs of records in partitioned topics.Intermediate
• Asynchronous ReplicationReplication in which the primary acknowledges writes immediately and ships changes to replicas afterward.Intermediate
• AtomicityThe ACID property that a transaction completes entirely or not at all.Foundational
• B-Tree IndexA balanced tree index that keeps keys sorted and supports equality, range and ORDER BY lookups in logarithmic time.Intermediate
• BASEA NoSQL consistency model: Basically Available, Soft state, Eventually consistent, the counterpoint to ACID.Intermediate
• Big DataDatasets too large, fast or varied for traditional single-server databases to store and process efficiently.Foundational
• Bitmap IndexAn index storing a bit array per distinct value, efficient for low-cardinality columns in read-heavy analytic workloads.Advanced
• BlockingA situation in which one session waits for a lock held by another session; it resolves when the holder finishes.Intermediate
• Boyce-Codd Normal Form (BCNF)A stricter version of 3NF in which every determinant (left side of a functional dependency) is a candidate key.Advanced
• BSONBinary JSON: MongoDB's binary-encoded document format, which adds types such as ObjectId, Date, Decimal128 and binary data.Intermediate
• Caching StrategyPatterns for keeping a cache consistent with a database, such as cache-aside (lazy loading), write-through and write-behind.Intermediate
• Candidate KeyAny minimal set of columns that could uniquely identify every row; one is chosen as the primary key.Intermediate
• CAP TheoremThe principle that a distributed data store can guarantee at most two of Consistency, Availability and Partition tolerance during a network partition.Intermediate
• Cardinality (Data Modeling)The number of instances of one entity that can relate to instances of another: one-to-one, one-to-many or many-to-many.Foundational
• 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
• Change Data Capture (Data Engineering) (CDC)Identifying and capturing row-level inserts, updates and deletes in a source so only changes are propagated downstream.Intermediate
• Check ConstraintA rule that limits the values a column can accept to those that satisfy a Boolean expression.Intermediate
• Clustered IndexAn index that determines the physical order in which table rows are stored, so the table itself becomes the index's leaf level.Intermediate
• COALESCE FunctionA function that returns the first non-NULL argument in its list.Foundational
• Column CardinalityThe number of distinct values in a column; high cardinality means many unique values.Intermediate
• Columnar StorageStoring each column's values together on disk rather than storing whole rows, which speeds analytic scans and improves compression.Intermediate
• Columnstore IndexA SQL Server and Azure SQL index that stores data column by column in compressed segments for fast analytic scans.Advanced
• 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 IndexAn index built on two or more columns, ordered by the first column, then the second, and so on.Intermediate
• Composite KeyA key made of two or more columns that together uniquely identify a row.Intermediate
• Conceptual Data ModelA high-level model that shows the main business entities and their relationships, without attributes or technical detail.Foundational
• Connection PoolingReusing a pool of open database connections across requests instead of opening a new connection each time.Intermediate
• Consistency (ACID)The ACID property that a transaction takes the database from one valid state to another, respecting all constraints and rules.Foundational
• Correlated SubqueryA subquery that references columns from the outer query and is logically evaluated once per outer row.Intermediate
• Covering IndexAn index that contains every column a query needs, so the query can be answered from the index alone.Advanced
• CREATE TABLE AS SELECT (CTAS)A statement that creates a new table and fills it with the results of a query in one step.Intermediate
• CREATE TABLE StatementThe DDL statement that defines a new table with its columns, data types and constraints.Foundational
• Crow's Foot NotationAn ERD notation that uses symbols at line ends to show cardinality: a three-pronged 'crow's foot' for many, a bar for one and a circle for zero.Intermediate
• CursorA database object that lets procedural code process a query result one row at a time.Advanced
• Cypher Query LanguageA declarative graph query language, created by Neo4j, that uses ASCII-art patterns such as (a)-[:REL]->(b) to match nodes and relationships.Advanced
• Data AnomaliesInsert, update and delete anomalies: integrity problems caused by redundant data in unnormalized tables.Intermediate
• Data ArchivingMoving data that is no longer actively used to lower-cost, long-term storage while keeping it retrievable for compliance or reference.Foundational
• 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 GovernanceThe framework of policies, roles, standards, processes and controls that ensures data is managed as an asset: secure, high quality, compliant and usable.Foundational
• Data LakeA central repository that stores large volumes of raw data in any format (structured, semi-structured or unstructured) cheaply, usually in object storage.Foundational
• Data LakehouseAn architecture that adds warehouse features such as ACID transactions, schema enforcement and fast SQL to low-cost data lake storage using open table formats.Intermediate
• Data Lifecycle Management (DLM)Managing data from creation and storage through use, sharing, archiving and secure deletion according to policy.Intermediate
• 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
• Data MartA subset of a data warehouse focused on one business area or department, such as sales or finance.Foundational
• Data PipelineAn automated series of steps that moves data from sources through transformations to destinations.Foundational
• Data Quality (DQ)The degree to which data is fit for its intended use, judged on dimensions such as accuracy, completeness, consistency, timeliness, validity and uniqueness.Foundational
• Data Query Language (DQL)The SQL sublanguage for retrieving data, consisting essentially of the SELECT statement.Foundational
• Data TransformationConverting data from one structure, format or value set to another, by filtering, joining, aggregating, pivoting, deriving or recoding.Foundational
• Data Warehouse (DW)A central repository of integrated, cleaned, historical data from multiple sources, structured for analytics and reporting.Foundational
• Database AuditingTracking and recording database events such as logins, queries, permission changes and data modifications for security and compliance.Intermediate
• 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 StatisticsMetadata about the distribution of values in columns and indexes, used by the optimizer to estimate row counts.Advanced
• 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
• DeadlockA cycle in which two or more transactions each hold a lock the other needs, so none can proceed.Intermediate
• 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
• DenormalizationIntentionally adding redundancy, for example by merging tables or duplicating columns, to speed reads and simplify queries.Intermediate
• Derived TableA subquery in the FROM clause that acts as a temporary inline table, which must have an alias.Intermediate
• Dirty ReadReading data written by another transaction that has not yet committed and may be rolled back.Intermediate
• Distributed TransactionA transaction that spans multiple databases or nodes and must still be atomic across all of them.Advanced
• Document DatabaseA NoSQL database that stores self-describing documents (JSON or BSON) whose structure can vary from document to document.Foundational
• Document EmbeddingStoring related data inside a parent document (embedding) rather than in a separate collection with references.Intermediate
• Domain IntegrityThe rule that every column value comes from the column's valid set of values, enforced by data types, CHECK, NOT NULL and DEFAULT.Intermediate
• DROP StatementThe DDL command that permanently removes a database object, such as a table, view, index or schema, together with its data.Foundational
• DurabilityThe ACID property that committed changes persist even after a crash or power loss.Foundational
• Dynamic Data Masking (DDM)Masking sensitive column values in query results for unauthorized users at run time without changing the stored data.Intermediate
• Dynamic SQLSQL statements built as strings at run time and then executed, for example with EXEC, sp_executesql or EXECUTE IMMEDIATE.Advanced
• Enterprise Data Warehouse (EDW)An organization-wide data warehouse that integrates data across all business areas to serve as a single source for analytics.Intermediate
• EntityA person, place, thing, event or concept about which data is stored; it usually becomes a table.Foundational
• Entity IntegrityThe relational rule that every table has a primary key and that no primary key value is NULL or duplicated.Intermediate
• Entity-Relationship Diagram (ERD)A diagram that shows entities, their attributes and the relationships between them, used to design databases.Foundational
• Eventual ConsistencyA model in which replicas converge to the same value if no new updates occur, but reads may temporarily return stale data.Intermediate
• EXISTS OperatorA predicate that returns true if a subquery returns at least one row.Intermediate
• Extract, Load, Transform (ELT)A pattern that loads raw data into the target platform first and transforms it there using the platform's own compute.Foundational
• Extract, Transform, Load (ETL)A data integration process that extracts data from sources, transforms it in a separate engine, then loads it into a target such as a warehouse.Foundational
• 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
• Fourth Normal Form (4NF)A table is in 4NF when it is in BCNF and has no non-trivial multivalued dependencies.Advanced
• Full LoadReloading an entire dataset each run, typically by truncating and repopulating the target.Foundational
• Full Table ScanReading every row of a table to answer a query, typically because no usable index exists or most rows are needed.Intermediate
• Full-Text IndexAn index that supports fast word- and phrase-based searches inside large text columns.Intermediate
• Functional DependencyA relationship where the value of one attribute (or set) uniquely determines the value of another: X -> Y.Intermediate
• GRANT StatementThe DCL statement that gives privileges on database objects to users or roles.Foundational
• Graph DatabaseA database that stores data as nodes and edges with properties, optimized for traversing relationships.Intermediate
• 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
• Hadoop Distributed File System (HDFS)Hadoop's distributed file system, which splits files into large blocks replicated across DataNodes, with a NameNode tracking metadata.Intermediate
• Half-Open Date RangeA date filter written as >= start AND < end, which includes the start and excludes the end so datetime values are captured correctly.Intermediate
• Hash IndexAn index that maps keys through a hash function to buckets, giving very fast equality lookups but no range support.Advanced
• HAVING ClauseThe clause that filters groups after GROUP BY, typically using aggregate conditions.Foundational
• High-Water Mark (Incremental Load)The last processed value of a monotonically increasing column, such as a modified timestamp or ID, stored to drive the next incremental extract.Intermediate
• Horizontal PartitioningSplitting a table by rows into several partitions or tables with the same columns.Intermediate
• Hot PartitionA partition or shard that receives a disproportionate share of traffic, creating a bottleneck while others sit idle.Advanced
• Identity ColumnA column whose values the database generates automatically, typically incrementing integers, often used as a surrogate key.Intermediate
• Implicit ConversionAutomatic conversion of one data type to another by the database engine when an expression mixes types.Intermediate
• In-Memory DatabaseA database that keeps its primary data in RAM for microsecond to millisecond access, often with optional persistence.Intermediate
• Incremental LoadLoading only data that is new or changed since the last run, instead of reloading everything.Intermediate
• Index FragmentationDisorder in index pages, either logical (pages out of order) or low page density, which reduces scan efficiency.Advanced
• Index SeekAn access method that navigates an index tree directly to the matching keys instead of reading the whole index or table.Advanced
• 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
• Inverted IndexA mapping from each term to the list of documents or rows that contain it, used by search engines and full-text indexes.Intermediate
• Isolation (ACID)The ACID property that concurrent transactions do not see each other's intermediate, uncommitted changes.Foundational
• Junction TableAn associative table that resolves a many-to-many relationship by holding foreign keys to both related tables.Intermediate
• Key LookupAn execution-plan operation in which the engine uses a nonclustered index and then fetches the remaining columns from the clustered index for each row.Advanced
• Key-Value StoreA NoSQL database that stores data as unique keys mapped to opaque values, optimized for fast lookups by key.Foundational
• LAG and LEAD FunctionsWindow functions that return a value from a previous row (LAG) or a following row (LEAD) within a partition.Intermediate
• Lambda ArchitectureA big data architecture with a batch layer for accurate historical views and a speed layer for real-time views, merged in a serving layer.Advanced
• LATERAL JoinA join in which the right-hand table expression can reference columns from preceding tables in the FROM clause.Advanced
• 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
• Leftmost Prefix RuleThe principle that a composite index can be used for seeks only when queries filter on its leading column(s).Advanced
• LockingThe mechanism that controls concurrent access by placing shared (read) or exclusive (write) locks on resources.Intermediate
• Log ShippingA disaster-recovery method that automatically backs up transaction logs on a primary and restores them on secondary servers.Advanced
• Logical Data ModelA detailed, technology-independent model that defines entities, attributes, keys, relationships and normalization.Intermediate
• Lost UpdateAn anomaly in which two transactions read the same value and both write back, so one overwrites the other's change.Advanced
• Many-to-Many Relationship (Database) (M:N)A relationship where many rows in one table relate to many rows in another; it is resolved with a junction table.Intermediate
• MapReduceA programming model that processes large datasets in parallel with a map phase producing key-value pairs and a reduce phase aggregating them.Intermediate
• Massively Parallel Processing (MPP)An architecture that splits data and query work across many nodes that process their portions in parallel.Intermediate
• Materialized ViewA database object that stores the precomputed result of a query and refreshes it as base data changes.Intermediate
• MERGE StatementA single DML statement that inserts, updates or deletes target rows based on whether they match rows in a source.Intermediate
• MongoDBA popular document database that stores BSON documents in collections and supports rich queries, indexes, aggregation and horizontal sharding.Foundational
• Multi-Model DatabaseA database that supports several data models, such as document, key-value, graph and column, behind one service.Intermediate
• Multivalued Dependency (MVD)A constraint where one attribute determines a set of values for another attribute, independent of the other attributes.Advanced
• Multiversion Concurrency Control (MVCC)A concurrency method that keeps multiple versions of rows so readers see a consistent snapshot without blocking writers.Advanced
• N+1 Query ProblemA performance anti-pattern that runs one query to fetch N parent rows and then N more queries to fetch each one's children.Advanced
• Natural KeyA key made from real-world business data that is already unique, such as an ISBN, VIN or email address; also called a business key.Intermediate
• Non-Repeatable ReadAn anomaly in which reading the same row twice in one transaction returns different values because another transaction committed a change in between.Intermediate
• Nonclustered IndexA separate index structure containing key values and pointers to the data rows; a table can have many.Intermediate
• NoSQL DatabaseA non-relational database that stores data as key-value pairs, documents, wide columns or graphs, usually with a flexible schema.Foundational
• 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
• Object-Relational Mapping (ORM)A technique and set of libraries (Hibernate, Entity Framework, SQLAlchemy) that map programming objects to relational tables.Intermediate
• OLAP CubeA multidimensional data structure that precomputes measures across dimensions for fast slicing and aggregation.Intermediate
• One-to-Many Relationship (1:N)A relationship where one row in a parent table can relate to many rows in a child table, but each child relates to one parent.Foundational
• One-to-One Relationship (1:1)A relationship where each row in one table matches at most one row in another.Intermediate
• Online Analytical Processing (OLAP)Systems optimized for complex, read-heavy queries that aggregate large volumes of historical data for analysis.Foundational
• Online Transaction Processing (OLTP)Systems that handle many short, concurrent read and write transactions, such as orders, payments and bookings.Foundational
• Optimistic Concurrency ControlA strategy that lets transactions proceed without locks and checks for conflicts at write time using version numbers or timestamps.Advanced
• OptionalityWhether participation in a relationship is mandatory (minimum one) or optional (minimum zero).Intermediate
• Orphan RecordA child row whose foreign key points to a parent row that does not exist.Foundational
• PACELC TheoremAn extension of CAP: if there is a Partition, choose Availability or Consistency; Else, choose Latency or Consistency.Advanced
• Parameterized QueryA query that uses placeholders for input values that are bound separately, so the input is never treated as SQL code.Intermediate
• Partial DependencyA dependency in which a non-key attribute depends on only part of a composite primary key.Intermediate
• Partition PruningSkipping partitions whose data cannot satisfy a query's filter, which reduces the data scanned.Intermediate
• Pessimistic Concurrency ControlA strategy that locks data when it is read for update, preventing conflicts by making others wait.Advanced
• Phantom ReadAn anomaly in which re-running a range query returns new or missing rows because another transaction inserted or deleted rows.Intermediate
• Physical Data ModelThe implementation-specific model with actual tables, columns, data types, indexes, partitions and constraints for a chosen DBMS.Intermediate
• 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
• Point-in-Time Recovery (PITR)Restoring a database to its exact state at a specific moment, for example just before an accidental delete.Intermediate
• Primary Key (PK)A column or set of columns whose values uniquely identify each row in a table; it cannot contain NULLs.Foundational
• Property GraphA graph model in which nodes and relationships both carry labels and key-value properties.Advanced
• Query Execution PlanThe sequence of physical operations (scans, seeks, joins, sorts) that the database chooses to run a query.Intermediate
• Query OptimizerThe database component that evaluates possible ways to run a query and picks the lowest-cost plan.Intermediate
• 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
• Read CommittedAn isolation level in which a transaction sees only committed data, though values may change between reads.Intermediate
• Read UncommittedThe lowest isolation level, which takes no shared locks on reads and allows dirty reads.Intermediate
• Recovery ModelA SQL Server database setting (Simple, Full or Bulk-logged) that controls how the transaction log is kept and which restores are possible.Advanced
• Recursive CTEA CTE that references itself to traverse hierarchical or graph data such as org charts or bills of materials.Advanced
• RedisAn in-memory key-value data store supporting strings, hashes, lists, sets, sorted sets and streams, widely used for caching.Intermediate
• 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
• Repeatable ReadAn isolation level that guarantees rows read once will return the same values if read again in the same transaction.Advanced
• Replica SetA group of MongoDB servers holding the same data, with one primary for writes and secondaries that replicate and can take over automatically.Intermediate
• REVOKE StatementThe DCL statement that removes previously granted privileges from a user or role.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
• ROW_NUMBER FunctionA window function that assigns a unique sequential integer to rows within each partition according to the ORDER BY.Intermediate
• Sargable QueryA query whose predicates can use an index seek or pruning (Search ARGument ABLE), typically because columns are not wrapped in functions.Advanced
• SAVEPOINT (SQL)A named marker inside a transaction that allows a partial rollback to that point without abandoning the whole transaction.Intermediate
• Schema-on-ReadApplying structure to data when it is queried, rather than enforcing a schema when it is written.Intermediate
• Schema-on-WriteValidating and structuring data to a predefined schema when it is loaded, rejecting non-conforming records.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
• Semi JoinA join pattern that returns rows from one table that have at least one match in another, without duplicating them.Advanced
• SequenceA standalone database object that generates unique numbers on request, independent of any one table.Intermediate
• SerializableThe strictest isolation level, in which concurrent transactions behave as if they had run one after another.Advanced
• Shard KeyThe field or fields used to decide which shard stores each document or row.Advanced
• ShardingHorizontal partitioning of data across multiple database servers (shards) to scale writes and storage.Intermediate
• Shared-Nothing ArchitectureA distributed design in which each node has its own CPU, memory and disk, sharing nothing with other nodes.Advanced
• Slowly Changing Dimension (SCD)Techniques for handling changes to dimension attributes over time, such as a customer moving city.Intermediate
• Snapshot IsolationAn isolation level in which each transaction reads a consistent snapshot of the data as of its start, using row versions.Advanced
• Snowflake SchemaA dimensional model whose dimension tables are normalized into multiple related tables, such as Product to Subcategory to Category.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
• Staging Area (Data Warehousing)An intermediate storage location where extracted data lands before being cleansed, transformed and loaded into target tables.Foundational
• Star SchemaA dimensional model with a central fact table joined directly to denormalized dimension tables, forming a star shape.Foundational
• Stored ProcedureA named, precompiled set of SQL (and procedural) statements stored in the database and executed with CALL or EXEC.Intermediate
• Strong ConsistencyA guarantee that every read returns the most recent committed write, as if there were a single copy of the data.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
• SuperkeyAny set of one or more columns whose combined values uniquely identify each row, whether or not it is minimal.Advanced
• Surrogate KeyA system-generated identifier with no business meaning, such as an auto-increment integer or GUID, used as a primary key.Intermediate
• Synchronous ReplicationReplication in which a write is acknowledged only after it is committed on both the primary and the replica.Intermediate
• Table PartitioningDividing a large table into smaller physical pieces by a key such as date, while queries still see a single table.Intermediate
• 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
• Time-Series Database (TSDB)A database optimized for timestamped measurements, offering fast ingestion, time-based compression, retention and windowed aggregation.Intermediate
• Transact-SQLMicrosoft's SQL dialect for SQL Server, Azure SQL, Synapse and Fabric Warehouse, with procedural extensions.Foundational
• Transaction Control Language (TCL)The SQL commands that manage transactions: BEGIN/START TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT.Foundational
• Transaction Isolation LevelA setting that defines which concurrency anomalies a transaction may see: Read Uncommitted, Read Committed, Repeatable Read or Serializable.Intermediate
• Transaction LogA sequential record of every change made by transactions, used for recovery, rollback and replication.Intermediate
• Transaction Log BackupA backup of the transaction log records since the last log backup, enabling point-in-time recovery and freeing log space.Advanced
• Transitive DependencyAn indirect dependency in which a non-key attribute depends on another non-key attribute rather than directly on the key.Intermediate
• 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
• Tunable ConsistencyThe ability to choose, per operation or per account, how many replicas must acknowledge a read or write.Advanced
• Two-Phase Commit (2PC)A protocol that coordinates a distributed transaction: participants first vote to prepare, then all commit or all abort.Advanced
• UNION ALLA set operator that stacks the results of two queries and keeps all rows, including duplicates.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
• Vertical PartitioningSplitting a table by columns into separate tables that share the primary key.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
• Wide-Column StoreA NoSQL database that organizes data into rows with dynamic columns grouped into column families, built for massive write throughput.Intermediate
• 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
• Write-Ahead Logging (WAL)A technique in which changes are recorded in a durable log before being applied to data files.Advanced