Data and Database Fundamentals practice questions

SELECT Name, CourseID FROM Students RIGHT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

Why it fails: RIGHT JOIN returns all rows from the right table (Enrollments) and matching rows from Students. Students without enrollments are excluded, but enrollments without a matching student (if any) would appear with NULL for Name. This does not match the requirement to list each student's enrolled courses.

SELECT Name, CourseID FROM Students, Enrollments WHERE Students.StudentID = Enrollments.StudentID;

Why it fails: The implicit comma join without a WHERE clause produces a Cartesian product (each row of Students paired with each row of Enrollments). Although the WHERE clause here is present, the syntax is outdated and less clear; it functions as an INNER JOIN, but the query is correct. However, it is not the best practice; optionally, it is acceptable but not the only correct syntax.

SELECT Name, CourseID FROM Students LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID;

Why it fails: LEFT JOIN returns all students, even those without enrollments, who would show NULL for CourseID. This does not meet the requirement to list only students with enrolled courses.

DROP TABLE

Why it fails: DROP TABLE removes the entire table structure.

ALTER TABLE

Why it fails: ALTER TABLE modifies table structure, does not remove rows.

REMOVE FROM table_name;

Why it fails: REMOVE is not a SQL command.

Encrypting sensitive data in the database

Why it fails: Encryption is security, not normalization.

Increasing the number of tables to improve performance

Why it fails: Normalization may increase tables but the goal is redundancy reduction, not performance.

Combining all data into a single table

Why it fails: That would increase redundancy, opposite of normalization.

SELECT * FROM Customers

Why it fails: SELECT * FROM Customers returns row data, not the database structure, so it does not reveal columns, keys or relationships. It is tempting because it is a valid query against the Customers table, and would be correct when retrieving customer records rather than examining schema.

INSERT INTO Customers VALUES (...)

Why it fails: INSERT adds rows to a table; it does not query database structure. It is tempting because INSERT is a core SQL data-manipulation statement, and it would be the correct choice when populating the Customers, Orders or Products tables with new records rather than examining their schema.

Simplify the database schema

Why it fails: Normalisation eliminates data redundancy and update anomalies by decomposing tables into smaller, related structures, which actually increases schema complexity rather than reducing it. The goal is to remove repeating groups through atomicity and dependency enforcement, not to simplify the schema. This option is tempting because simplifying a schema is a common database design objective, and it would be correct in a scenario where denormalisation is intentionally applied to improve read performance by reducing join operations.

Improve query performance

Why it fails: Improve query performance is not a normalisation goal; normalisation often adds joins, which can slow reads. It is tempting because denormalisation deliberately introduces redundancy to speed up reporting queries, so it would be the right aim when optimising a read-heavy data warehouse, not when eliminating repeating groups.

Denormalization for performance

Why it fails: Denormalization increases redundancy, opposite of normalization.

Using a single table to store all data

Why it fails: A single table leads to redundancy, not normalization.

CHECK

Why it fails: A CHECK constraint validates that column values satisfy a logical condition, such as price being greater than zero, but it does not by itself guarantee uniqueness across rows. While a CHECK could reject null or blank strings, it cannot prevent two different rows from storing the same non-blank email address, so it fails the uniqueness requirement.

FOREIGN KEY

Why it fails: A foreign key creates a link between a column in one table and the primary key of another table, enforcing referential integrity between related tables. It does not enforce uniqueness within the Email column itself, nor does it prevent blank values; applying it here would be inappropriate because there is no parent table being referenced by the email addresses.

PRIMARY KEY

Why it fails: A primary key uniquely identifies each row and cannot contain nulls, but it is typically a dedicated identifier such as a ClientID. Using the Email column as the primary key would prevent multiple clients from sharing an email address if they belong to the same company, and it would make the value difficult to update without breaking foreign key references elsewhere.

Check constraint on the DateOfBirth column to ensure it is in the past

Why it fails: A check constraint on DateOfBirth could validate that the date is not in the future, but this is not part of the stated requirements. The hospital needs uniqueness of medical record numbers and referential integrity for prescriptions. While data validation is good practice, it does not address the specific needs described in the scenario.

Unique constraint on the combination of FirstName, LastName, and DateOfBirth

Why it fails: A unique constraint on these three columns would prevent duplicate combinations, but the scenario does not require this. In fact, it could be problematic because two patients might share the same name and birthdate. The requirements only mention a unique medical record number and valid patient references for prescriptions, so this constraint is unnecessary.

Default constraint on the PrescriptionDate column

Why it fails: A default constraint sets a value automatically when none is provided, such as the current date for PrescriptionDate. This is unrelated to ensuring unique patient identifiers or linking prescriptions to patients. It does not enforce uniqueness or referential integrity, so it fails to meet the requirements of the scenario.

Simpler to set up and manage

Why it fails: While flat file systems can be set up with minimal overhead, they lack the relational integrity and concurrent-access controls that a database management system provides. This option is tempting because for a small, single-user list of records, a flat file is indeed quick to configure. However, in a scenario requiring simultaneous multi-user updates and enforced data consistency across customer records, the absence of transaction management and referential constraints makes a flat file system unsuitable.

Stores data in a non-structured format

Why it fails: Storing records without a fixed schema prevents the enforcement of column types, primary keys and referential integrity across customer tables, so duplicate and orphaned records cannot be blocked. Non-structured storage suits media files, logs or documents where fields vary per item — not relational customer records requiring consistent querying.

No need for a query language

Why it fails: Databases still require SQL or another query language, so this is not an advantage they hold over flat files. Avoiding a query language describes fixed-format interchange such as CSV consumed directly by scripts, not relational storage of customer records.

Referential integrity

Why it fails: Referential integrity governs relationships between tables, ensuring a foreign key value matches an existing primary key in the parent table. It says nothing about uniqueness or nullability within the Orders table itself. Referential integrity would be the correct concept if the requirement concerned linking Orders to a Customers table.

Input validation

Why it fails: Input validation checks data at the application layer before it reaches the database, so it cannot guarantee the constraint for every record — direct inserts, imports and other applications bypass it. Entity integrity, enforced by a primary key or unique NOT NULL constraint, guarantees it at the database. Input validation suits form-level format checking.

Domain integrity

Why it fails: Domain integrity constrains the values a column may hold — data type, format and permissible range — not uniqueness or nullability of a key. The requirement describes an entity integrity constraint, enforced by a primary key or unique NOT NULL constraint. Domain integrity would be the correct concept for validating that, say, an OrderDate column holds valid dates.

Entity integrity

Why it fails: Entity integrity guarantees each row of a table is uniquely identified by a non-null primary key; it governs the Students table itself, not the cross-table reference. It would be the right answer if the question asked what the Students primary key enforces within that table.

Normalization

Why it fails: Normalization is a schema-design process that removes redundancy and update anomalies by decomposing tables into normal forms; it does not enforce any constraint at insert time. It would be correct if the stem asked how to restructure tables to eliminate duplicated student data.

Domain integrity

Why it fails: Domain integrity constrains a column's values to a permitted set or data type; a foreign key instead restricts the Enrollments rows to student IDs that exist in Students, which is referential integrity. Domain integrity would be correct when validating that, say, a grade column only accepts A–F.

Wide-column store

Why it fails: MongoDB stores documents as BSON with nested fields and arrays, queried through a document API, whereas wide-column stores organise data into rows and dynamic column families addressed by row key. Wide-column suits massive sparse datasets needing high-throughput key-range reads, such as time-series or analytics workloads, which is why it tempts here.

Graph database

Why it fails: MongoDB stores documents in BSON format and queries fields and arrays directly; it is not a graph database, which models entities as nodes and relationships as edges traversed via pointers. Graph databases suit connected-data problems like social networks or recommendation engines, where relationship traversal, not document retrieval by field, drives the workload.

Key-value store

Why it fails: MongoDB stores documents as BSON objects with named fields and typed values, so it is a document store, not a key-value store. Key-value stores map an opaque value to a single key with no queryable internal structure. Key-value is correct for caching sessions or simple lookups, not querying nested fields like 'tags'.

RETRIEVE * FROM Customers WHERE city = 'London';

Why it fails: RETRIEVE is not a SQL keyword; the statement fails to parse. It is tempting because it reads as a natural synonym for fetching rows; the correct verb is SELECT, which is the standard clause for projecting columns, so RETRIEVE would only be right in a non-SQL query language that defines it.

SELECT * FROM Customers HAVING city = 'London';

Why it fails: HAVING filters aggregated groups produced by GROUP BY, not individual rows, so it cannot restrict city values here. It is tempting because it looks like WHERE, and it would be correct after aggregating, for example HAVING count(*) > 5.

GET * FROM Customers WHERE city = 'London';

Why it fails: GET is not a SQL statement; no such keyword exists in the language. It is tempting because GET resembles HTTP or PowerShell retrieval verbs, and in those contexts it does fetch data, but SQL requires SELECT to project columns from Customers.

Denormalization

Why it fails: Denormalization deliberately reintroduces redundancy by merging tables, the opposite of the required change. It is tempting because it improves read performance in reporting and analytics workloads, where joining many normalized tables is costly, but it worsens the update anomalies described.

Indexing

Why it fails: Indexing builds lookup structures over existing columns; it leaves the table's schema and duplicate values untouched, so update anomalies persist. It is tempting because indexes genuinely speed up query retrieval on large tables, and would be the right answer if the scenario asked how to accelerate searches rather than reorganise data.

Partitioning

Why it fails: Partitioning divides one table's rows across storage segments by a key range or list, leaving the same columns intact, so it cannot remove redundancy or update anomalies. It is tempting because it also splits a table, and would be correct when the goal is query pruning or managing very large tables.

MySQL

Why it fails: MySQL Community Edition is open source under the GPL, so it is not proprietary. It is tempting because Oracle also sells commercial MySQL editions, but the DBMS itself is not classed as a proprietary relational system in this context.

PostgreSQL

Why it fails: PostgreSQL is open source under the PostgreSQL Licence, so it is not proprietary. It is tempting because it is a full-featured relational DBMS with SQL support, and would be the right answer if the question asked for an open-source relational database instead.

SQLite

Why it fails: SQLite is public domain open-source software, so it is not proprietary. It is tempting because it is a relational DBMS, but it is embedded and file-based, and would be correct if the question asked for a lightweight embedded relational database.

Platform as a Service (PaaS)

Why it fails: PaaS delivers a managed runtime where the provider handles patching, scaling and backups, but the customer still deploys and administers the application and its data. It is tempting because the stem describes managed platform duties, yet the question asks for the model where the provider also operates the application itself.

Software as a Service (SaaS)

Why it fails: SaaS delivers a complete provider-run application to end users, with no customer-visible database service to configure or consume programmatically. It is tempting because the provider manages everything, but SaaS is correct when users simply access finished software rather than building on a managed database platform.

Infrastructure as a Service (IaaS)

Why it fails: IaaS supplies raw compute, storage and networking that the customer still patches, backs up and scales themselves, so the provider's automatic database management contradicts the model. IaaS is tempting because the database runs on provider hardware, but it is correct when the customer needs control of the operating system and middleware.

ADD

Why it fails: ADD is not a standalone SQL statement; it appears only as a clause, such as ALTER TABLE ... ADD COLUMN, which changes table structure rather than inserting row data. It would be correct when extending a table's schema with a new column.

CREATE

Why it fails: CREATE defines new database objects such as tables, views or indexes; it does not populate a table with row data. It is tempting because it is the standard command for bringing a table into existence, which would be the right choice when the table itself does not yet exist.

UPDATE

Why it fails: UPDATE modifies values in existing rows via SET, so it cannot insert a new row. It is tempting because it is the everyday command for changing table data, and it would be the right choice when correcting a stored value or altering an existing record's columns.

Self-referencing

Why it fails: Self-referencing relationships link rows within one table using a foreign key to the same table's primary key, so no third table is created. It is tempting because it also uses foreign keys, but the scenario's requirement for a junction table containing keys from two related tables indicates many-to-many.

One-to-one

Why it fails: One-to-one is implemented by adding a unique foreign key to one of the two tables, or by sharing the primary key; no junction table is needed. It is tempting because both tables are directly linked, but many-to-many is the relationship requiring a third table with foreign keys from both.

One-to-many

Why it fails: One-to-many is implemented by placing a foreign key in the child table pointing to the parent, requiring no junction table. It is tempting because junction tables do contain foreign keys, but many-to-many is the relationship that actually demands a third table holding keys from both sides.

Requires normalized data

Why it fails: NoSQL databases deliberately store denormalised, duplicated data to avoid joins and scale horizontally, so requiring normalised data contradicts their design. Normalisation is a relational technique for eliminating redundancy. It is tempting because normalisation is a genuine database design goal, but it applies to relational schemas, not NoSQL stores.

Enforces strict schema definition

Why it fails: NoSQL databases typically use flexible or schema-on-read models, allowing documents with differing fields in one collection, so strict schema enforcement is the opposite of their characteristic. Strict schemas belong to relational tables. It is tempting because schema enforcement guarantees data consistency, which is valuable in relational designs but not a NoSQL trait.

Uses SQL for queries

Why it fails: NoSQL databases use non-SQL query languages or APIs, such as document queries or key-value lookups, so claiming SQL contradicts the defining characteristic. It is tempting because some NoSQL engines offer SQL-like interfaces, but that is an add-on, not the model's native query mechanism.

SQLite

Why it fails: SQLite is an embedded, file-based library, not a cloud service, so it cannot deliver the managed high availability or automatic scaling the e-commerce application demands. It is tempting because it is a genuine relational database, and it would be the right choice for a local, single-user application or mobile app needing zero configuration.

Microsoft SQL Server Express

Why it fails: SQL Server Express is an on-premises edition limited to 10 GB per database, with no automatic scaling or high availability. It is tempting because it is free and relational, and would be correct for a small local application with modest data and no resilience requirement.

MySQL Community Edition

Why it fails: MySQL Community Edition is self-hosted software; the administrator must provision servers, clustering and scaling themselves, so it provides no managed cloud high availability or automatic scaling. It is tempting because MySQL is a relational database, and it would be correct for an on-premises deployment where the team controls the infrastructure.