Tables, Rows & Schemas - SQL

1. How should the resulting object be described?
   Answer: It remains a table with four columns, its constraints, and zero data rows. When working with SQL, it's important to distinguish between a table's structure and its data. A table is defined by its schema — its columns, data types, and constraints — not by how many rows it currently holds. Keeping this separation in mind makes questions like this straightforward. Deleting every row with a DELETE statement removes all data from the table but leaves the table itself completely intact. The Queue table still exists in the database with its four columns and all associated constraints (primary keys, foreign keys, NOT NULL constraints, etc.). It simply has zero rows — which is a perfectly valid, queryable state. You can immediately run SELECT, INSERT, or any other DML statement against it. That makes B the correct answer. A is wrong because SQL places no minimum row requirement on a table. An empty table is entirely valid and commonly used — think of tables that start empty and get populated over time. C introduces a fictional "schema-only" status that doesn't exist in SQL; an empty table is fully operational and can be queried at any time (a SELECT simply returns zero rows). D is equally fictional — SQL does not insert a phantom NULL row as a placeholder when a table is emptied. That concept has no basis in standard SQL behavior. A useful study tip: always distinguish between DDL operations (DROP TABLE, ALTER TABLE), which change structure, and DML operations (DELETE, TRUNCATE), which affect data. A DELETE without a WHERE clause clears all rows but never touches the table's definition.
2. What has changed from the perspective of table organization?
   Answer: The table belongs to a different namespace, so its schema-qualified name has changed. When working with SQL schemas, think of them as namespaces — organizational containers that group database objects. A table's full identity includes both its schema and its name, written as schema.table_name. Moving a table between schemas doesn't alter its structure or data; it changes where the table lives within the database's namespace hierarchy. That's exactly what happens here. The Customer table moves from crm to archive, so its schema-qualified name changes from crm.Customer to archive.Customer. The columns, data types, constraints, and rows remain completely intact — only the namespace has shifted. This makes D the correct answer: the table now belongs to a different namespace, and any code referencing crm.Customer would break unless updated. The other choices reflect common misconceptions worth unpacking. A is nonsensical — rows and columns are fundamentally different things (rows are data instances, columns are structural definitions), and moving a table never swaps them. B confuses a schema with the concept of "archiving." A schema is always just a namespace container; tables inside it still hold ordinary rows regardless of whether the schema is named "archive." C invents a false split where columns and rows somehow belong to different schemas — SQL has no such mechanism. A table is a unified object; its components don't get distributed across schemas. A helpful study tip: whenever you see a question about schemas, ask yourself "what does a schema actually do?" It groups objects into a namespace and affects qualified naming — nothing more. It doesn't transform data, split table components, or change row structure.
3. Which statement most accurately distinguishes the definitions from the stored data?
   Answer: The table structures are equivalent, but their current row contents are different table states. When working with databases, you need to distinguish between two separate concepts: a table's definition (its structure or schema) and its state (the actual data stored at a given moment). The definition includes column names, data types, constraints, and relationships — essentially the blueprint. The state is the current snapshot of rows living inside that structure. Since both tables were created from the same deployment script, they share identical blueprints: same columns, same data types, same constraints. What differs is the data those blueprints hold. Production has more customer rows with different values, but that's a difference in state, not structure. Answer A captures this precisely — equivalent definitions, different current row contents — making it the correct choice. Answer B is wrong because it conflates definition with data. Matching definitions absolutely do not imply shared rows. A blueprint tells you what a house can hold, not what's inside it right now. Two houses built from the same plan can have completely different furniture. Answer C introduces a false claim: row count is never part of a table's structural definition. Structure describes shape and rules, not how many records happen to exist at a point in time. Answer D misuses the term "schema." Schema refers to the structural design of database objects — it does not grow or shrink as rows are inserted or deleted. Adding rows changes table state, never schema size. A handy study tip: whenever a question mixes "schema," "structure," or "definition" with "rows," "data," or "records," pause and ask yourself which concept each option is actually describing. These two layers — definition vs. state — are a favorite trap on SQL exams.
4. Which response best evaluates the analyst's claims?
   Answer: Neither claim is correct because names alone establish neither schema membership nor a formal relationship. When working through database schema and relational integrity questions, ask yourself two things separately: What determines where a table lives? and What formally connects rows across tables? Conflating these two concepts is exactly the trap this question sets. A table's schema is defined by how it was created — specifically the schema name prefixed before the table name (like sales.Order vs. finance.Refund). Those prefixes tell you everything: these tables belong to different schemas. A shared column name like account_id has absolutely no bearing on schema membership. The schema is a namespace, and column names are just labels within each table's own structure. Equally important, equal values in two columns do not automatically create a formal relationship. A formal relationship requires an explicit constraint — specifically a foreign key — that the database engine enforces. Without that constraint, matching values between sales.Order.account_id and finance.Refund.account_id are just coincidental (or intentional) data similarities, not a structural bond the database recognizes or protects. That makes D the correct answer: neither claim holds up. Answer A fails on both counts, treating column names as authoritative for two things they simply don't control. Answer B incorrectly assumes a shared column name collapses two tables into one namespace — the schema prefix in the table name already disproves this directly. Answer C makes the classic mistake of confusing data equality with relational integrity; without a foreign key constraint, the database won't prevent orphaned or mismatched rows. A useful rule of thumb: in SQL, structure is defined by DDL constraints, not by naming conventions or value coincidences.
5. What are the table's final degree and cardinality?
   Answer: Degree $10$ and cardinality $117$ When working with relational database tables, you need to track two distinct properties separately: degree (the number of columns/attributes) and cardinality (the number of rows/tuples). Keeping these independent in your mind is the key to solving this type of question. For degree, start with $8$ columns. Adding two columns gives $8 + 2 = 10$. Row insertions and deletions never affect degree — columns are structural, not transactional. So the final degree is $10$. For cardinality, start with $120$ rows. Inserting $15$ rows brings the total to $120 + 15 = 135$. Then the deletion removes $18$ rows — and notice that the question tells you which rows were deleted (12 original + 6 new), but that detail is a distractor. All that matters is the total count removed: $135 - 18 = 117$. Final cardinality is $117$. This confirms answer B. Answer A gets the cardinality right but uses the original degree of $8$, ignoring the two added columns entirely. Answer C correctly updates the degree to $10$ but forgets to apply the deletion — stopping at $135$ after the insertions. Answer D uses the original degree of $8$ and also miscalculates cardinality, likely by subtracting the 18 deleted rows directly from the original 120, ignoring the inserted rows. A useful strategy: always resolve degree changes and cardinality changes on separate tracks. Column additions/drops only touch degree; row insertions/deletions only touch cardinality. The breakdown of which rows were deleted is often included to tempt you into overcounting — don't fall for it.
6. Which conclusion is best supported by this design?
   Answer: The tables can coexist because each schema provides a separate namespace for its tables. When working with database objects like tables, understanding how schemas organize and isolate names is essential. Think of a schema as a namespace — a container that gives every object inside it its own fully qualified identity. In SQL, a table's full name includes both its schema and its unqualified name, written as schema.TableName. So sales.Product and inventory.Product are two completely distinct objects despite sharing the name Product. The schema prefix is what makes them unique within the database. This is exactly why B is correct: schemas provide separate namespaces, allowing two tables with the same unqualified name to coexist without any conflict. A describes a rule that simply doesn't exist. Databases do not require unqualified names to be unique across the entire system — they only require that the fully qualified name (schema + table name) be unique. A confuses unqualified names with fully qualified identifiers. C is a logical leap that doesn't hold up: sharing a name and a couple of column names doesn't make two tables the same logical entity. Tables are defined by their data, constraints, and purpose — not just their structure headers. D introduces a fabricated rule suggesting identical schemas must hold identical data, which has no basis in how relational databases work. Schema names are purely organizational; they say nothing about the content of the tables inside them. As a study tip, remember that in SQL, identity is always fully qualified. When you see a question about naming conflicts or table coexistence, ask yourself whether the full schema-qualified names are actually identical — if not, there's no conflict.
7. Which statement is necessarily true immediately after the structural change?
   Answer: The table has six columns and the same number of rows as before the change. When working with SQL schema changes, it helps to understand what an ALTER TABLE statement actually does to existing data. Adding a column is a structural modification — it changes the table's definition, not its row count. When a new column is added to an existing table, the database engine updates the schema so that every row in the table now includes that column as part of its structure. Existing rows will typically show NULL (or a specified default value) in the new column, but they are still the same rows — none are added or removed. This is why C is correct: the table ends up with six columns and the exact same number of rows as before. A reflects a common misconception about how databases store metadata. Column definitions live in the system catalog (or information schema), not as rows inside the table itself. Adding a column does not create a new row in your data table. B is subtly wrong because it implies existing rows are somehow excluded from the new column's structure. In reality, all rows — old and new — share the same column structure after the ALTER TABLE completes. Existing rows simply receive NULL or the default value for the new column. D describes behavior closer to creating a view or a new table, not an ALTER TABLE operation. When you alter a table, the original table is the modified table — no separate schema object is created. A good rule of thumb: ALTER TABLE ... ADD COLUMN changes the shape of the table for all rows, past and future, while leaving the row count untouched.
8. What is the most accurate conceptual result?
   Answer: Both rows are deleted because each stored row independently satisfies the condition. When working with SQL deletion, the key concept to internalize is that rows are independent physical records — not logical groupings. The database engine doesn't "see" two identical rows as one thing; it sees two separate entries that happen to share the same values. This is exactly why C is correct. When you execute a DELETE WHERE statement, the database scans every stored row and evaluates the condition against each one individually. If both rows satisfy the condition, both rows get deleted — full stop. The fact that their values are identical is irrelevant to how the engine processes them. Each row is its own independent unit of storage. A is wrong because SQL has no concept of "logical deduplication" during deletion. The database doesn't merge identical rows into a single entity before applying operations — that's not how row storage works. B is wrong because SQL evaluates conditions row-by-row without any confusion about duplicates; there's no mechanism that causes the engine to skip or error on matching rows. D is wrong because SQL assigns no inherent priority based on insertion order during a standard DELETE. There's no "earlier duplicate wins" rule — that misconception conflates SQL behavior with application-level retry logic. The practical takeaway here is important: if you want to delete only one of several duplicate rows, a standard DELETE WHERE won't help you — you'd need a more advanced technique like using ROWID (in Oracle), ctid (in PostgreSQL), or a CTE with ROW_NUMBER(). Knowing this distinction will help you on both exam questions and real-world schema design problems.
9. Which assessment of the developer's expectation is most accurate?
   Answer: It is not guaranteed because a table's rows have no inherent retrieval order. Whenever you see a SQL question about row retrieval order, anchor your thinking to one foundational rule: a relational database table is an unordered set of rows. Unless you explicitly include an ORDER BY clause in your SELECT statement, the database engine is free to return rows in any order it chooses — based on storage layout, indexes, query plan optimizations, or internal caching. No standard guarantees physical insertion order will match retrieval order. This makes A the correct answer. The developer's expectation is not guaranteed precisely because SQL tables have no inherent retrieval order. Even if rows happened to be stored physically in insertion sequence, the query optimizer might scan an index, perform a parallel read, or reorganize pages during compaction — all of which can scramble the apparent order. The only safe way to guarantee chronological retrieval is ORDER BY on a timestamp or sequence column. B reflects a common misconception — that rows "remember" their insertion position like array indices. Relational tables are sets, not lists; insertion sequence is not a stored property of row position. C is pure distraction; column data types have absolutely nothing to do with retrieval order. D introduces a plausible-sounding but entirely fabricated threshold — no such page-size rule exists in SQL standards or any major database system. As a study tip, remember this mantra: **"No ORDER BY, no guaranteed order."** On SQL exams, any answer claiming deterministic ordering without an explicit ORDER BY should be treated with immediate suspicion, regardless of how the table was populated.
10. If the designer adds emergency_contact as a nullable column, which description is accurate?
    Answer: The column becomes part of every row, with other rows permitted to contain NULL there. When working with SQL schema changes, it's essential to understand that a table's structure — its columns — applies uniformly to every row. This question tests whether you understand how ALTER TABLE ... ADD COLUMN actually works. Adding a nullable column like emergency_contact modifies the table's schema for all rows simultaneously. Rows that have no value for this new attribute simply store NULL there automatically. This is exactly what option C describes: the column exists in every row, but non-specified rows are permitted to hold NULL, effectively leaving them "blank" for that field. This is precisely why nullable columns are so useful — they let you capture optional data without breaking existing records. Option A reflects a fundamental misunderstanding. In a relational table, every row must conform to the same column structure. Rows cannot have their own independent sets of columns — that would violate the relational model entirely. Option B incorrectly suggests the selected customer moves to a separate schema. SQL doesn't work this way; schemas define structure for entire tables, not individual rows. A single customer cannot be isolated into a different schema by adding a column. Option D is also wrong because it implies rows cannot gain new columns, suggesting you'd need a second row instead. In reality, ALTER TABLE adds the column to all existing rows at once — no duplicate rows are needed or appropriate. A good rule of thumb: column definitions belong to the table, not to individual rows. Whenever you see a question implying rows can have different structures, that's almost certainly a distractor exploiting a common misconception about the relational model.

Start this quiz