CCNA Database Fundamentals Questions

Why this answer

How to eliminate wrong answers

Option A is wrong because partitioning the table by product category would add complexity and overhead without directly speeding up lookups by product ID, and it requires significant schema changes and downtime. Option C is wrong because increasing server memory may improve caching of frequently accessed data, but it does not eliminate the need for a full table scan on a cold cache or first access, and it incurs hardware cost without addressing the root cause. Option D is wrong because migrating to a NoSQL system would require a complete architectural overhaul, significant downtime, and application rewrites, which contradicts the requirement for minimal downtime and cost.

Why this answer

How to eliminate wrong answers

Option A is wrong because 2 is only the quantity of one of Alice's orders, not the total sum of all her order quantities. Option B is wrong because NULL would only appear if there were no matching Orders rows for Alice, but she has two orders (IDs 101 and 102) with quantities 2 and 5. Option C is wrong because 5 is the quantity of only the second order, not the sum of both orders.

Why this answer

How to eliminate wrong answers

Option A is wrong because Second Normal Form (2NF) addresses partial dependencies on a composite key, not repeating groups; it assumes 1NF is already satisfied. Option B is wrong because Third Normal Form (3NF) deals with transitive dependencies (non-key attributes depending on other non-key attributes), which is a higher-level concern after 1NF and 2NF are met. Option D is wrong because the presence of repeating groups is a clear violation of First Normal Form, so the table is not in 1NF.

Why this answer

How to eliminate wrong answers

Option A is wrong because it violates database normalization principles by combining all data into a single table, leading to massive data redundancy and update anomalies. Option C is wrong because storing a JSON list of product IDs in a single column breaks first normal form (1NF) by storing multiple values in one column, making it impossible to enforce referential integrity or efficiently query individual products. Option D is wrong because adding fixed columns (Product1, Product2, Product3) cannot handle a variable number of products per order and violates the principle of atomicity, as it imposes an arbitrary limit and wastes space.

Why this answer

How to eliminate wrong answers

Option A is wrong because partitioning by row count does not inherently speed up queries filtered by a specific column; it only splits data into smaller physical segments, which can even increase overhead if the partition key does not align with the filter column. Option B is wrong because increasing RAM may help caching but does not change the fundamental need for a full table scan when no index exists; it is a hardware band-aid that does not optimize query execution paths. Option D is wrong because denormalization reduces joins but does not address the core issue of slow filtering on last_name; it introduces data redundancy and maintenance complexity without improving lookup speed on that column.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because increasing CPU and memory resources (vertical scaling) only provides a temporary performance boost and does not address the root cause of excessive joins from normalization; it is a costly band-aid that may not scale with growing data volumes. Option C is wrong because replacing the relational warehouse with a NoSQL document store would require a complete architectural overhaul, likely breaking existing BI tools and SQL-based reporting, and NoSQL systems typically lack the ACID guarantees and join capabilities needed for consistent aggregated reporting across dimensions. Option D is wrong because adding more indexes on all foreign key columns can speed up individual join operations but increases write overhead and storage costs, and in a highly normalized snowflake schema with many joins, the cumulative index overhead can actually degrade query performance due to index maintenance and lookup costs.

Why this answer

How to eliminate wrong answers

Option A is wrong because the table clearly has a transitive dependency, so a normal form violation exists. Option B is wrong because Second Normal Form (2NF) is satisfied: there is no partial dependency since the primary key is a single column (EmployeeID), and all non-key columns are fully functionally dependent on it. Option C is wrong because First Normal Form (1NF) is satisfied: all columns contain atomic values, and there are no repeating groups or arrays.

Why this answer

How to eliminate wrong answers

Option A is wrong because if the column name were misspelled, the query would typically return a syntax or invalid column name error, not an empty result set. Option B is wrong because if the database were offline, the query would fail with a connection error, not silently return zero rows. Option D is wrong because an incorrect table name would produce a 'table not found' error, not an empty result set.

Why this answer

How to eliminate wrong answers

Option A is wrong because UNION combines rows from two or more SELECT statements into a single result set by stacking them vertically, not by matching related columns horizontally. Option B is wrong because a SUBQUERY is a nested query used to return a value or set of values for use in an outer query's WHERE, FROM, or SELECT clause, not to combine entire rows from two tables based on a related column. Option D is wrong because INTERSECT returns only the rows that appear in the result sets of both SELECT statements, which is used for set operations on identical row structures, not for joining distinct tables on a related column.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because a stored procedure that updates all products in one statement without error handling does not provide atomicity or rollback capability; if the single statement fails partway (e.g., due to a constraint violation), some rows may still be updated depending on the statement type, and without error handling, partial updates can occur. Option B is wrong because increasing the frequency of the script to run every hour with smaller batches does not solve the atomicity problem; each batch would still be vulnerable to partial failure, and more frequent runs increase contention with daytime operations. Option C is wrong because removing the transaction and using individual UPDATE statements with error logging does not provide atomicity; even with error logging, if the script fails after some updates, those updates persist, leaving inconsistent data, and error logging alone cannot roll back already committed changes.

Why this answer

How to eliminate wrong answers

Option B is wrong because SQL does not limit UPDATE to only the first row; without a WHERE clause, the operation targets all rows, not a single row. Option C is wrong because omitting the WHERE clause does not produce a syntax error; the UPDATE statement is syntactically complete and valid. Option D is wrong because the statement is not invalid; it executes successfully and updates every row in the table.

Why this answer

How to eliminate wrong answers

Option A is wrong because durability guarantees that committed transactions persist permanently, even after a system failure; it does not handle undoing uncommitted partial changes. Option B is wrong because isolation controls how concurrent transactions interact to prevent dirty reads or lost updates, but it does not address rollback of a single failed transaction. Option C is wrong because consistency ensures that a transaction transforms the database from one valid state to another, but it relies on atomicity to undo partial changes when a transaction fails; consistency itself does not perform the rollback.

Why this answer

How to eliminate wrong answers

Option A is wrong because adding more columns increases the row width, which can degrade I/O performance and does not speed up queries. Option B is wrong because denormalization introduces data redundancy and can improve read performance only in specific warehousing scenarios, but it is not the most direct or standard fix for slow queries on a large table; it often complicates writes and maintenance. Option C is wrong because further normalization typically increases the number of joins required, which can slow down queries rather than improve performance on a large table.

Why this answer

Why this answer

How to eliminate wrong answers

Why this answer

How to eliminate wrong answers

Option A is wrong because a clustered index physically reorders the table data based on the index key, which is beneficial for range queries but does not inherently improve exact match performance beyond what a B-tree index provides, and it can cause overhead during inserts and updates. Option C is wrong because a bitmap index is optimized for columns with low cardinality (few distinct values) and is typically used in data warehousing for complex queries involving multiple conditions, not for exact match searches on a single column in a transactional database. Option D is wrong because a hash index is designed for exact match lookups using a hash function, but it does not support ordered retrieval and is not the default or most common index type in relational databases like MySQL or SQL Server; B-tree indexes are the standard for general-purpose exact match queries.

Why this answer

Why this answer

How to eliminate wrong answers

Option B (50000) is wrong because it represents the minimum salary, not the average. Option C (165000) is wrong because it is the sum of all salaries (50000 + 60000 + 55000), not the average. Option D (60000) is wrong because it represents the maximum salary, not the average.

Why this answer

How to eliminate wrong answers

Option B is wrong because a transaction log backup alone cannot restore a database without a prior full backup to provide the base data structure and pages. Option C is wrong because a full backup alone only restores the database to the state at the time of that backup (10:00 PM previous day), not to the later point-in-time of 11:00 AM. Option D is wrong because a differential backup captures changes since the last full backup but does not provide point-in-time granularity to a specific hour; transaction log backups are required for that precision.

Why this answer

How to eliminate wrong answers

Option A is wrong because three tables with no direct link fail to establish any relationship between orders, customers, and items, making it impossible to associate a customer with their orders or items with specific orders. Option C is wrong because storing all data in a single JSON column violates first normal form (1NF), prevents efficient indexing and querying of individual fields, and makes it difficult to enforce data integrity or perform relational operations like joins. Option D is wrong because a single table with repeated item information (e.g., multiple columns for item1, item2) violates atomicity and leads to a fixed maximum number of items per order, causing data redundancy and update anomalies when item details change.

Why this answer

How to eliminate wrong answers

Option A is wrong because a UNIQUE constraint ensures all values in a column are distinct but allows a single NULL value, which would break the requirement for a guaranteed unique identifier for every employee. Option B is wrong because a FOREIGN KEY constraint enforces referential integrity between two tables and does not provide unique identification within the table itself. Option D is wrong because a NOT NULL constraint only prevents NULL values in a column but does not enforce uniqueness, so duplicate employee identifiers would still be allowed.

Why this answer

How to eliminate wrong answers

Option A is wrong because an index consumes additional storage space, not less; it stores a separate data structure (e.g., a B-tree) that maps index keys to row locations. Option B is wrong because an index only improves query performance for queries that use the indexed column in WHERE, JOIN, or ORDER BY clauses; queries that do not reference the 'Email' column or that perform full-table scans may see no benefit or even degraded performance. Option D is wrong because inserts become slower, not faster, due to the need to maintain the index by inserting a new entry into the index structure for each new row.

Why this answer

How to eliminate wrong answers

Option B is wrong because the Orders columns do not replicate the customer ID; they show NULL to indicate the absence of a matching order. Option C is wrong because zero is a numeric value that would imply an order exists with a zero value, not the absence of an order; SQL uses NULL for missing data. Option D is wrong because an empty string is a valid string value that would indicate an order with an empty field, not the lack of an order; NULL is the correct indicator for no matching row.

Why this answer

Why this answer

How to eliminate wrong answers

Option B (PRIMARY KEY) is wrong because while it also enforces uniqueness, it additionally requires the column to be non-null and uniquely identify each row, which is not necessary for just ensuring unique emails; the Email column may not be the natural primary key. Option C (CHECK) is wrong because CHECK constraints validate data against a Boolean expression (e.g., ensuring email format contains '@'), but they cannot enforce uniqueness across rows. Option D (FOREIGN KEY) is wrong because it enforces referential integrity between tables by requiring values in the Email column to match values in another table's primary key or unique column, which does not prevent duplicate emails within the same table.

Why this answer

Why these pairings

Why this answer

How to eliminate wrong answers

Option A is wrong because a full-text index is designed for linguistic searches on large text fields (e.g., searching for words or phrases within documents), not for exact equality matches on a single value like an ID. Option B is wrong because a nonclustered index on the column would improve performance but requires an additional lookup step (key lookup or bookmark lookup) to retrieve the actual data row, making it slower than a clustered index for direct equality searches. Option D is wrong because a unique index enforces uniqueness of values but does not inherently improve performance more than a non-unique index; its primary purpose is constraint enforcement, not query speed optimization.

Why this answer

Why this answer

How to eliminate wrong answers

Option B (ON DELETE SET NULL) is wrong because it would set the 'StudentID' foreign key column in 'Enrollments' to NULL when the parent student record is deleted, which does not remove the enrollment records and may violate NOT NULL constraints. Option C (ON DELETE NO ACTION) is wrong because it prevents the deletion of a student record if any referencing enrollment rows exist, requiring manual deletion of enrollments first, which contradicts the automatic removal policy. Option D (ON DELETE RESTRICT) is wrong because it also blocks the deletion of a student record if referencing enrollments exist, similar to NO ACTION, and does not automatically remove associated records.

Why this answer

How to eliminate wrong answers

Option A is wrong because many-to-one is the inverse of one-to-many and implies that many books could be associated with a single author, but it fails to capture the scenario where a book has multiple authors. Option B is wrong because one-to-many would mean one book can have many authors, but it does not allow an author to write many books, which violates the bidirectional requirement. Option D is wrong because one-to-one would mean each book has exactly one author and each author writes exactly one book, which directly contradicts the given requirement that both sides can have multiple instances.

Why this answer

How to eliminate wrong answers

Option A is wrong because CREATE TABLE is used to create a new table, not to modify an existing one, and it would attempt to create a duplicate table or fail if the table already exists. Option C is wrong because UPDATE is a Data Manipulation Language (DML) statement used to modify existing row data, not to change the column's schema or data type; VARCHAR(15) is not a valid value for an UPDATE statement. Option D is wrong because INSERT INTO adds a new row of data to the table, not to alter the column definition, and it would simply insert a specific phone number without changing the column's maximum length.

Why this answer

How to eliminate wrong answers

Option A is wrong because the query contains no ORDER BY clause, so results are not sorted by Department or any other column; sorting requires an explicit ORDER BY. Option C is wrong because SELECT * returns all columns, not just the Department column; to return only the Department column, the query would need to specify SELECT Department. Option D is wrong because the WHERE clause filters rows to only those where Department = 'Sales', so it does not return all rows from the Employees table.

Why this answer

How to eliminate wrong answers

Option A is wrong because consistency ensures that a transaction transforms the database from one valid state to another, respecting all defined rules (e.g., constraints, triggers); a partially recorded order could still be consistent if the missing line items are allowed by schema, but the core issue is the incomplete unit of work. Option B is wrong because isolation deals with concurrent transaction visibility (e.g., preventing dirty reads or lost updates via locking or MVCC), not with the all-or-nothing completion of a single transaction. Option D is wrong because durability guarantees that once a transaction is committed, its changes survive system failures (e.g., via write-ahead logging); here the header was saved but the line items were not, so the transaction was never fully committed, and durability is not violated.

Why this answer

How to eliminate wrong answers

Option B (Primary key) is wrong because a primary key uniquely identifies each row in a table and implicitly enforces NOT NULL and uniqueness, but the question specifically asks about a constraint that enforces a field must have a value—a primary key's primary role is uniqueness, not just requiring a value. Option C (Index) is wrong because an index is a data structure that improves query performance by allowing faster data retrieval; it does not enforce any requirement for a field to have a value. Option D (Foreign key) is wrong because a foreign key enforces referential integrity between two tables, ensuring that a value in one table matches a value in another table's primary key; it does not require that a field itself must have a value.

Why this answer

How to eliminate wrong answers

Option A is wrong because hierarchical databases organize data in a tree-like structure with parent-child relationships, which is inflexible for modeling many-to-many relationships between customers and orders without redundancy. Option B is wrong because flat-file databases store all data in a single table or file, leading to data duplication and difficulty managing related entities like order history without manual normalization. Option C is wrong because NoSQL databases are designed for unstructured or semi-structured data and horizontal scaling, but they lack the strict schema enforcement and relational integrity needed for consistent customer-order associations.

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B (3) is wrong because it would imply three employees in department 10, but the data shows only two. Option C (0) is wrong because it would mean no employees in department 10, but the data clearly contains two. Option D (1) is wrong because it would indicate a single employee, but the query counts two matching rows.

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing the transaction isolation level to Serializable increases locking and contention, which actually makes deadlocks more likely, not less. Option B is wrong because removing indexes increases the likelihood of table scans and lock escalation, which can worsen lock contention and deadlock frequency. Option C is wrong because manually killing one of the processes is unnecessary and disruptive; the database's deadlock detection mechanism automatically selects and rolls back a victim transaction without administrator intervention.

Why this answer

How to eliminate wrong answers

Option A is wrong because indexing is used to speed up data retrieval by creating data structures (e.g., B-trees) that improve query performance, not to eliminate duplicate data or enforce entity-level table design. Option B is wrong because transaction isolation is a property of database transactions (defined by SQL standards like ACID) that controls concurrency and visibility of changes, not a method for structuring tables to avoid redundancy. Option D is wrong because backup and recovery involves creating copies of data and restoring it after failure (e.g., using full, differential, or transaction log backups), which does not address data duplication or table design.

Why this answer

How to eliminate wrong answers

Option A is wrong because a one-to-one relationship would require each book to have exactly one author and each author to write exactly one book, which contradicts the given scenario. Option B is wrong because hierarchical is not a standard relational database relationship type; it typically refers to a data model (like in LDAP or XML) where records have parent-child relationships, not the associative many-to-many needed here. Option C is wrong because a one-to-many relationship would allow one book to have many authors but would force each author to write only one book (or vice versa), which does not satisfy the bidirectional multiplicity described.

Why this answer

How to eliminate wrong answers

Option A is wrong because it only shows one row (Widget, 2), omitting the Gadget row entirely, which would only happen if a WHERE clause filtered out Gadget or if the query used DISTINCT on Category without COUNT. Option C is wrong because it lists 'Widget, 2; Gadget, 1; Widget, 1', which incorrectly includes a third row with Widget count 1, likely confusing COUNT(*) with a row count per individual occurrence or misapplying a HAVING clause. Option D is wrong because it lists 'Widget, 2; Gadget, 1; Widget, 1' (same as C), which duplicates Widget with an incorrect count and adds an extra row not produced by the GROUP BY.

Why this answer

How to eliminate wrong answers

Option A is wrong because Consistency ensures that a transaction brings the database from one valid state to another, but it does not handle rollback on failure—that is the role of Atomicity. Option B is wrong because Durability guarantees that once a transaction is committed, its changes persist even after a system crash, but it does not undo a failed transaction. Option C is wrong because Isolation ensures that concurrent transactions do not interfere with each other, but it does not provide rollback mechanisms for a single failing transaction.

Why this answer

How to eliminate wrong answers

Option B (Indexing) is wrong because indexing improves query performance by creating data structures for faster data retrieval, but it does not address data redundancy or update anomalies. Option C (Denormalization) is wrong because it intentionally adds redundant data to improve read performance, which would worsen update anomalies rather than resolve them. Option D (Query optimization) is wrong because it focuses on improving the efficiency of SQL query execution plans, not on restructuring the database schema to eliminate redundancy.

Why this answer

How to eliminate wrong answers

Option A is wrong because replacing LEFT JOIN with INNER JOIN would exclude products with no sales entirely, which does not fix the incorrect TotalSold display and loses required data. Option C is wrong because SUM(COALESCE(s.QuantitySold,0)) would convert NULL to 0, but without a GROUP BY, the aggregate still operates on an ungrouped result set, so the incorrect display of p.Quantity for TotalSold persists. Option D is wrong because removing the ORDER BY clause only affects row ordering, not the aggregation logic; the fundamental issue of missing GROUP BY remains.

Why this answer

How to eliminate wrong answers

Option B (Isolation) is wrong because isolation concerns the visibility of intermediate transaction states to other concurrent transactions, not the completion of a single transaction. Option C (Durability) is wrong because durability guarantees that committed changes persist, but here the transaction was never committed due to the failure. Option D (Consistency) is wrong because consistency ensures that a transaction leaves the database in a valid state; while a partial update could lead to inconsistency, the primary violated property is atomicity, as the transaction failed to execute as an all-or-nothing unit.

Why this answer

How to eliminate wrong answers

Option A is wrong because relational databases require a fixed schema with predefined columns, making it difficult to store products with varying attributes without extensive null values or table redesigns. Option B is wrong because network databases use a graph-like structure with records and sets, which is rigid and not designed for schema-less, document-oriented data. Option C is wrong because hierarchical databases organize data in a strict parent-child tree structure, which cannot accommodate products with different attributes without forcing a uniform schema across all records.

Why this answer

How to eliminate wrong answers

Option B is wrong because the AND operator is syntactically and logically correct for combining two conditions; it is not the cause of the mismatch. Option C is wrong because the column name 'Department' is correctly spelled in the query and matches the table schema; a misspelling would cause an error, not an empty result. Option D is wrong because even if Salary were stored as text, the comparison 'Salary > 50000' would still work in most SQL databases via implicit conversion, and the issue is specifically with the Department string, not the salary comparison.

Why this answer

How to eliminate wrong answers

Why this answer

How to eliminate wrong answers

Option B is wrong because in most SQL databases (including MySQL, PostgreSQL, and SQL Server), GRANT statements are auto-committed as DDL commands and do not require an explicit COMMIT. Option C is wrong because SELECT privilege alone is sufficient to read data; UPDATE is a separate operation and is not required for SELECT to work. Option D is wrong because if the table name were misspelled, the GRANT command would typically return an error (e.g., 'Table or view not found'), not silently succeed while still blocking the SELECT.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because a table lock by another user would typically cause the DELETE to wait or fail, not report success while still showing the row; locks block conflicting operations, not hide committed changes. Option B is wrong because if the WHERE clause used the wrong value, the DELETE would still report success (0 rows affected) and the original row would remain, but the question states the DELETE 'reports success' and the row is still there—this could happen, but the most likely explanation in a database context is a missing commit, not a WHERE clause error, as the latter is a logic mistake not a transaction issue. Option D is wrong because lacking DELETE privilege would cause the statement to fail with an error (e.g., 'permission denied'), not report success.

Why this answer

How to eliminate wrong answers

Option A is wrong because UPDATE modifies existing records by setting the city to 'Chicago' for all rows, which does not retrieve any data and would incorrectly change all customers' cities. Option C is wrong because DELETE removes all rows where city is 'Chicago', which destroys data instead of retrieving it. Option D is wrong because INSERT adds new rows to the table, which does not retrieve existing data and would require specifying values, not a condition.

Why this answer

Why this order

Why this answer

How to eliminate wrong answers

Option A is wrong because DELETE FROM Log; is a DML operation that removes rows one by one, logging each deletion, and can be rolled back within a transaction, so it is not irreversible. Option B is wrong because ALTER TABLE Log REMOVE ROWS; is not a valid SQL statement; ALTER TABLE is used to modify table structure (e.g., add/drop columns), not to remove rows. Option D is wrong because DROP TABLE Log; removes the entire table structure along with all data, which does not preserve the table for future use.

Why this answer

How to eliminate wrong answers

Option A is wrong because using a single flat file for all data would perpetuate the same redundancy and integrity problems—it offers no mechanism to enforce unique entries or relationships between data. Option B is wrong because a NoSQL database, while flexible, typically lacks built-in support for ACID transactions and foreign key constraints, making it less suitable for enforcing strict referential integrity in a small bookstore scenario with structured relational data. Option D is wrong because adding data validation rules to a spreadsheet cannot prevent duplicate rows or enforce cross-table relationships; spreadsheets lack the relational integrity features needed to eliminate redundancy and ensure consistency across 10,000+ rows shared by multiple users.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because a denial of service attack aims to overwhelm resources or crash the service, not to manipulate database queries via input; SQL injection can sometimes lead to DoS, but it is not the primary risk. Option C is wrong because cross-site scripting involves injecting client-side scripts into web pages viewed by other users, not directly into SQL queries on the server. Option D is wrong because a buffer overflow exploits memory boundaries in compiled code (e.g., C/C++), not in SQL query construction within a web application.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because a relational database uses tables with foreign keys to represent relationships, which can model hierarchies but requires complex self-joins and is less efficient for recursive queries like traversing an org chart. Option B is wrong because a document database stores semi-structured data (e.g., JSON) and lacks native support for efficient traversal of parent-child relationships, often requiring application-level logic to reconstruct the hierarchy. Option C is wrong because a graph database excels at modeling complex many-to-many relationships (e.g., social networks) and is overkill for a simple tree structure, though it could technically work; the hierarchical model is more directly suited for strict parent-child reporting lines.

Why this answer

How to eliminate wrong answers

Option A is wrong because 'Mouse' would only be returned if its Price were greater than 100, but it is not (likely 50 or less). Option B is wrong because 'Laptop, Mouse' implies both rows satisfy the condition, but only 'Laptop' has Price > 100. Option D is wrong because the result set is not empty; at least one row (Laptop) meets the condition.

Why this answer

How to eliminate wrong answers

Option B (GROUP BY) is wrong because it is used to group rows that have the same values in specified columns into summary rows, often with aggregate functions (e.g., COUNT, SUM), but it does not inherently eliminate duplicates; it can actually produce duplicate-like rows if not combined with aggregation. Option C (WHERE) is wrong because it filters rows based on a condition but does not remove duplicates; it only restricts which rows are included in the result set. Option D (ORDER BY) is wrong because it sorts the result set by one or more columns but has no effect on duplicate removal; it only changes the order of rows.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because a foreign key violation does not produce a warning; it is a hard error that prevents the insert entirely, and the row is not inserted. Option B is wrong because the row cannot be inserted successfully due to the foreign key constraint; the database enforces referential integrity and blocks the operation. Option C is wrong because foreign key constraints do not trigger automatic creation of missing parent records; the Customers table remains unchanged, and the insert fails.