CCNA Database Structures And SQL Questions

A FOREIGN KEY enforces referential integrity by requiring every Customer_ID value in Orders to match an existing primary key in Customers, rejecting inserts or updates that reference a non-existent customer. This directly satisfies the stem's requirement that the field always reference a valid ID.

A CHECK constraint is a declarative database object that validates data on every INSERT and UPDATE, regardless of the client. Defining Balance >= 0 ensures the rule is enforced at the storage engine level, cannot be bypassed by application code, and applies uniformly to all statements. This is the standard, most efficient way to enforce a simple domain rule like non-negative balances.

DROP TABLE deletes the table's definition and all its data, removing the object from the schema entirely. This satisfies the stem's requirement to remove the table itself, unlike DELETE, which removes rows while preserving the table structure, or TRUNCATE, which empties data but keeps the table.

A PRIMARY KEY constraint implicitly includes NOT NULL, so a NULL SKU is rejected. Separately, the CHECK constraint on quantity_on_hand rejects the negative value. Both violations occur, and the database will report an error for at least one of them, preventing the insert. This is the correct behavior because both constraints are enforced at insert time in standard SQL, ensuring data integrity for unique and non-negative inventory values.

A normalized model reduces data redundancy and maintains consistency, which is critical for transactional systems. Complex queries with joins can be optimized with proper indexing. This approach aligns with the need for strict consistency and supports complex queries without sacrificing integrity.

A composite UNIQUE constraint enforces that no two rows can have the same combination of TradeDate and TradeID, while still allowing individual TradeID values to repeat across different dates. It also permits NULLs (depending on the DBMS) and can coexist with a primary key on another column, making it the precise fit for this requirement.

A composite primary key on sensor_id and reading_time enforces uniqueness for each sensor reading. Adding a nonclustered index on sensor_id and reading_time descending allows the query optimizer to efficiently retrieve the most recent reading per sensor. This design balances data integrity with query performance for the stated access pattern.

This statement uses the DISTINCT keyword to return only unique country values and then sorts them alphabetically with ORDER BY. It precisely meets the requirement of retrieving a list of unique countries sorted alphabetically. This is the correct and efficient solution.

A trigger executes automatically on each insert, setting the timestamp column without application intervention. This satisfies the requirement for an automatic timestamp on every row, unlike defaults that only apply when no value is supplied, or computed columns that cannot capture insertion time reliably.

DROP is a DDL command because it alters the database schema by removing an existing table, view or index definition entirely. Unlike DML statements such as DELETE, which remove rows while preserving the object, DROP satisfies the stem's requirement for commands that define or restructure schema objects.

AVG ignores NULL values in its calculation. If all orders for a particular customer have NULL OrderTotal, the AVG function has no non-NULL values to average, and it returns NULL for that customer. This is a common cause of NULL averages in aggregate queries. The analyst should handle NULLs explicitly if a different behavior is desired.

SQL is the standard query language for relational databases, operating over tables with rows and columns and enforcing schema through primary and foreign keys. This directly satisfies the stem's requirement for a true statement about SQL's role, since relational systems such as SQL Server, PostgreSQL and MySQL all expose SQL as their primary interface.

A table scan reads every row because no ordered access path exists for the predicate or join columns. Creating an index on the filtered or joined column lets the optimiser perform a seek or range scan, drastically reducing pages read.

An INNER JOIN returns only customers who have matching order rows, which satisfies the requirement to list customers who placed at least one order. Grouping by customer and applying SUM to the order amount produces the total spent per customer, directly matching the requested output without extra filtering.

Quantity is a numeric measure that varies per transaction and is aggregated across dimensions such as product, store, and time. Measures like this belong in the fact table because they are the values being summed, averaged, or compared during analysis, and they are meaningless without the dimension context that describes them.

ALTER TABLE modifies the structure of an existing table, so it adds the new column to Employees without dropping data. CREATE TABLE builds a new table and UPDATE changes row values, neither of which alters the schema.

Imposing a uniform access order means every transaction acquires locks in the same sequence, eliminating the circular-wait condition that produces deadlocks. This directly addresses the scenario's deadlock frequency by removing one of the four Coffman conditions rather than merely retrying after failure.

TRUNCATE removes all rows by deallocating the table's data pages, a minimally logged operation that resets the table to empty while preserving its schema, columns, and indexes. This satisfies the stem's requirement to delete every row permanently yet keep the table definition intact, unlike DROP, which eliminates the structure itself.

AVG is a standard SQL aggregate function that returns the arithmetic mean of a numeric column across rows, satisfying the stem's requirement. It collapses multiple rows into one value, distinguishing aggregates from scalar functions that operate per row.

A FOREIGN KEY constraint establishes a link between data in two tables, ensuring that the value in one table matches a value in another. It enforces referential integrity, preventing orphaned records and maintaining consistent relationships. This is a key mechanism for data integrity across related tables.

UPDATE modifies existing rows in a table, setting new column values for records matched by the WHERE clause. This satisfies the stem's constraint of changing existing records, unlike INSERT, which adds new rows, or DELETE, which removes them.

LIKE performs pattern matching on string columns using wildcards such as % and _, satisfying the requirement to search for a pattern rather than an exact value. Equality operators match whole values only, so LIKE is the operator that filters rows by substring or pattern.

An index maintains a sorted structure on the queried column, letting the database locate matching rows without scanning the entire table. This directly satisfies the stem's read-performance constraint, trading additional storage and slower writes for faster lookups on that column.

UNION concatenates the row sets from two SELECT statements into one result set, eliminating duplicate rows by default. It satisfies the stem's requirement to combine results from two different queries, provided both have matching column counts and compatible data types in corresponding positions.

A hash join reads the smaller currency table once, builds an in-memory hash table on the currency code, then probes it for each transaction row. This converts the nested loop's repeated scans of the large table into a single pass, which is far more efficient when the join column is not indexed and the large table dominates the cost.

DISTINCT filters the result set so each unique combination of selected column values appears only once, directly satisfying the requirement to eliminate duplicate rows. It operates on the full SELECT list rather than a single column, unlike GROUP BY, which aggregates rows and is intended for summary calculations.

A nested loop join is often chosen when one input is small and can be efficiently probed, especially if an index exists on the join column of the inner table. Here, the Regions table is small, and if it has an index on the join column, the optimizer can quickly find matching rows for each row of the larger Sales table. This is a classic scenario for nested loop joins.

This design places a foreign key in the Orders table pointing to the Customers table. Each order references exactly one customer, and a customer can be referenced by many orders. This precisely implements the one-to-many relationship required by the scenario, with no unnecessary constraints.

Key-value stores are a core NoSQL category, pairing each key with an opaque value and offering simple lookups without a fixed relational schema. This satisfies the question's demand for a common NoSQL database type, alongside document, column-family and graph stores.

A read-only replica receiving log-shipped changes provides a separate copy of the data that can be queried without interfering with the primary. If the replica is consistent at a point in time, the report sees a stable dataset even while the primary continues to accept inserts, which supports consistent reporting without blocking writers.

A unique index enforces uniqueness but does not necessarily prohibit NULLs. In many database systems, such as SQL Server and PostgreSQL, multiple NULLs are allowed in a unique index because NULL is considered distinct from other NULLs. This allows flexibility while still preventing duplicate non-NULL values. However, behavior varies, so it's important to check the specific DBMS documentation.

This query joins Sales and Products, groups by product_name, and sums the quantity. The HAVING clause filters groups where the sum exceeds 100. It correctly returns only products meeting the sales threshold, with the required columns. This is the proper use of GROUP BY and HAVING.

Window functions compute aggregates across a set of rows while retaining each individual row, so `SUM(sales) OVER (ORDER BY date)` returns a running total without collapsing detail. This directly satisfies the developer's requirement for cumulative sales, unlike GROUP BY, which reduces rows and cannot produce per-row running values.

Separate schemas give each tenant its own tables and namespace within a shared database instance, so queries and permissions are scoped per tenant. This provides stronger logical isolation than shared tables with tenant-ID columns, satisfying the requirement for effective data separation in a multi-tenant architecture.

A LEFT JOIN returns every row from the left table plus matched rows from the right, filling unmatched right columns with NULL. That precisely satisfies the requirement to preserve all Left records while including only matching Right records.

A view stores a predefined SELECT statement, so the join logic is written once and reused. This directly satisfies the scenario's need to simplify complex joins, hiding multi-table syntax behind a single named object that users query as if it were one table.

A view exposes only selected columns and rows from the Salary table, letting the reporting team query permitted data while salary columns remain hidden. Granting access to the view rather than the base table enforces column-level restriction, satisfying the requirement to prevent unauthorised access to specific columns.

HAVING filters grouped rows after aggregation, so it can restrict results to regions whose COUNT of transactions exceeds 100. WHERE cannot reference aggregate functions, making HAVING the only clause that satisfies this post-aggregation filter requirement.

Graph databases store nodes and edges as first-class entities, so relationships are persisted directly rather than inferred through joins. Traversal follows pointers between adjacent nodes, making multi-hop queries across complex, frequently changing relationships far faster than relational or document alternatives.

Statistics feed the query optimiser's cost estimates, so stale or missing statistics cause poor execution plans and slow queries. Regularly refreshing them is a routine maintenance activity, satisfying the stem's requirement for a common database maintenance task.

The ALTER TABLE ... ADD COLUMN statement modifies the table's structure by appending a new column definition. Existing rows receive a NULL value for the new column unless a default is specified, so their data remains intact. This is the standard DDL statement for adding a column to an existing table in SQL.

NoSQL document stores shard data across nodes using partition keys, satisfying the horizontal scaling and high availability constraints for unstructured content. Unlike relational engines, they avoid rigid schemas and single-node write bottlenecks, letting each server hold independent document subsets with replication for failover.

Financial transactions demand ACID guarantees: atomicity, consistency, isolation and durability, enforced through strict schemas and multi-row transactions. A relational engine provides these natively, whereas most NoSQL stores trade full ACID compliance for horizontal scale and flexible schemas.

Dynamic Management Views, such as sys.dm_exec_sessions and sys.dm_exec_connections, expose live session and connection state on SQL Server. Querying them returns the current connection count directly, unlike static catalogs that describe schema objects rather than runtime activity.

LEFT preserves all rows from the left table, returning NULLs for unmatched right-side columns. It satisfies the stem's requirement for a standard SQL join type, sitting alongside INNER, RIGHT, FULL OUTER and CROSS in the ANSI specification. Unlike set operators such as UNION, joins combine columns horizontally across related tables.

Denormalisation duplicates data across tables, so a single logical fact is stored in multiple places. When one copy is updated but others are not, insert, update and delete anomalies arise, producing inconsistent query results. This directly satisfies the stem's requirement to identify a genuine drawback of denormalising, unlike performance gains.

GRANT SELECT ON SalesData TO analyst; provides the analyst with the privilege to run SELECT queries against the SalesData table. It does not grant INSERT, UPDATE, or DELETE privileges, so the analyst cannot modify data. This is the standard SQL statement for granting read-only access to a specific table in PostgreSQL and most other relational databases, directly fulfilling the requirement.

GRANT SELECT provides read-only access to the specified table. It allows the user to query data without granting INSERT, UPDATE, or DELETE privileges. This precisely meets the requirement of retrieving data while preventing modifications, following the principle of least privilege.

A document-oriented store allows each product document to contain its own set of attributes, so categories with different attributes are represented naturally without schema migrations. This flexibility supports frequent evolution and avoids downtime, matching the requirement for highly variable product attributes across thousands of categories.

Source and target engines define types differently, so columns such as dates, booleans or decimals may lack an exact equivalent. Unmapped or narrowed types cause DDL creation to fail or silently truncate, breaking the migration before data loads.

An append-only log-structured store writes sequentially to disk, avoiding random I/O and in-place index updates, which keeps write latency low at high ingest rates. Keying entries by timestamp and sensor ID preserves arrival order and enables efficient range scans for time-series analysis, directly satisfying both the throughput and ordering requirements described.

Normalisation structures tables so each fact is stored once, eliminating duplicate data and the update, insert and delete anomalies that duplication causes. This directly satisfies the stem's requirement to reduce redundancy, unlike indexing or denormalisation, which address query speed rather than data duplication.

A unique index physically enforces uniqueness by rejecting duplicate key values at insert or update, satisfying the constraint that no two rows share the same value. This differs from a plain non-unique index, which merely accelerates lookups without restricting data, so uniqueness is guaranteed by the index itself rather than application logic.

Serializable is the strictest isolation level, using range locks and predicate locking to make concurrent transactions behave as if run sequentially. This guarantees the highest consistency but forces blocking, giving the lowest concurrency under heavy writes.

PRIMARY KEY enforces entity integrity by uniquely identifying each row and implicitly forbidding NULLs, making it a standard SQL constraint alongside FOREIGN KEY, UNIQUE, CHECK and NOT NULL. It satisfies the stem's requirement for a common constraint used to guarantee row-level uniqueness.

ORDER BY sorts the result set by one or more specified columns, ascending by default or descending with DESC. It is the SQL clause that satisfies the requirement to sort query output, applied after WHERE and before any row-limiting clause.

SYSDATETIME() is a nondeterministic function, and within a single INSERT statement it is evaluated once, then the same value is applied to all rows. This is a documented behavior for nondeterministic functions used in DEFAULT constraints during multi-row inserts. To get per-row timestamps, you would need a different approach such as a trigger or application-supplied values.

A composite PRIMARY KEY enforces uniqueness across the combination of ProductCode and WarehouseID and implicitly disallows NULLs, which matches the requirement that each pair uniquely identifies a row. It also creates a unique index that the database engine uses to reject duplicate pairs, ensuring entity integrity for the inventory table.

The server address identifies the host — by name or IP, often with a port or instance — that the client must reach. It is one of the three essential components, since without it the provider cannot locate the database engine to connect to.