SQL Quiz: Create Table
10 questions · exam conditions
0:00
Create TableQuestion 1 of 10

Assume standard SQL constraint behavior. A table is created with CREATE TABLE Offer (offer_id INTEGER PRIMARY KEY, price DECIMAL(8,2) CHECK (price > 0), discount DECIMAL(8,2), CHECK (discount BETWEEN 0 AND price));. Neither price nor discount has a NOT NULL constraint.

Which row satisfies the declared constraints?

offer_id = 1, price = NULL, discount = 5.00
offer_id = 2, price = -1.00, discount = 0.00
offer_id = 3, price = 10.00, discount = 12.00
offer_id = 4, price = 10.00, discount = -2.00
← Back to quizzes

SQL Quiz

SQL Quiz: Create Table

Practice Create Table in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Create Table, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

Assume standard SQL constraint behavior. A table is created with CREATE TABLE Offer (offer_id INTEGER PRIMARY KEY, price DECIMAL(8,2) CHECK (price > 0), discount DECIMAL(8,2), CHECK (discount BETWEEN 0 AND price));. Neither price nor discount has a NOT NULL constraint.

Which row satisfies the declared constraints?

  1. offer_id = 1, price = NULL, discount = 5.00 (correct answer)
  2. offer_id = 2, price = -1.00, discount = 0.00
  3. offer_id = 3, price = 10.00, discount = 12.00
  4. offer_id = 4, price = 10.00, discount = -2.00
Explanation: When working with SQL CHECK constraints, you need to remember a critical rule: a CHECK constraint evaluates to true if the condition is true or if the condition evaluates to NULL. This "unknown = pass" behavior trips up many students but is standard SQL semantics. With that in mind, look at option A: price = NULL, discount = 5.00. The column-level check price > 0 evaluates as NULL > 0, which yields NULL — and since the result isn't explicitly FALSE, the constraint passes. Similarly, the table-level check discount BETWEEN 0 AND price becomes 5.00 BETWEEN 0 AND NULL, which also yields NULL — again, not false, so it passes too. The PRIMARY KEY is satisfied by a valid integer. Option A is the correct answer. Option B fails because price = -1.00 makes price > 0 evaluate to FALSE — an explicit violation that SQL rejects. Option C inserts price = 10.00 and discount = 12.00, which makes discount BETWEEN 0 AND price become 12.00 BETWEEN 0 AND 10.00 — clearly FALSE, so this row is rejected. Option D uses discount = -2.00, making discount BETWEEN 0 AND 10.00 evaluate as -2.00 BETWEEN 0 AND 10.00, which is FALSE and also rejected. A useful rule to memorize: NULL is the escape hatch for CHECK constraints. If any operand in a CHECK expression is NULL, the whole condition yields NULL, which SQL treats as "constraint not violated." Watch for questions that combine NULL columns with CHECK constraints — this is a classic exam trap.

Question 2

A scheduling table must require both dates and permit an activity to begin and end on the same date. Otherwise, the end date must be later than the start date.

Which definition enforces the requirements directly when the table is created?

  1. CREATE TABLE Activity (start_date DATE NOT NULL, end_date DATE NOT NULL, CHECK (end_date >= start_date)); (correct answer)
  2. CREATE TABLE Activity (start_date DATE CHECK (start_date IS NOT NULL), end_date DATE CHECK (end_date IS NOT NULL));
  3. CREATE TABLE Activity (start_date DATE NOT NULL, end_date DATE NOT NULL, CHECK (end_date >= end_date));
  4. CREATE TABLE Activity (start_date DATE NOT NULL, end_date DATE NOT NULL, CHECK (end_date > start_date));
Explanation: When designing a table in SQL, you can enforce data integrity rules at creation time using constraints — specifically NOT NULL for required fields and CHECK for conditional logic between columns. This question tests whether you can identify which combination of constraints correctly captures all three requirements: both dates are mandatory, same-day activities are allowed, and end must not precede start. Option A does exactly this. NOT NULL on both columns ensures neither date can be omitted, and CHECK (end_date >= start_date) uses the "greater than or equal to" operator, which permits same-day activities while rejecting any row where end comes before start. This is the precise, complete solution. Option B is flawed in two ways: it tries to enforce NOT NULL inside individual CHECK constraints rather than using the NOT NULL keyword, and more critically, it never compares the two dates at all — there's no cross-column validation, so a start date after an end date would still be accepted. Option C applies NOT NULL correctly, but the CHECK clause reads end_date >= end_date, which compares a column to itself. This is always true and enforces absolutely nothing about the relationship between the two dates. Option D is close but uses strict greater-than (>), which means a same-day activity — where start and end are equal — would be rejected. This directly contradicts the requirement that an activity may begin and end on the same date. As a study tip, pay close attention to > versus >= in CHECK constraints — exam questions frequently hinge on this single character when the rule explicitly includes or excludes boundary equality.

Question 3

The Department table has primary key department_id. An Employee row may initially belong to a department. If that department is deleted, the employee row must be retained and its department value must automatically become null.

Which definition of the relationship in CREATE TABLE Employee satisfies the requirement?

  1. department_id INTEGER, FOREIGN KEY (department_id) REFERENCES Department(department_id) ON DELETE NO ACTION
  2. department_id INTEGER NOT NULL, FOREIGN KEY (department_id) REFERENCES Department(department_id) ON DELETE SET NULL
  3. department_id INTEGER, FOREIGN KEY (department_id) REFERENCES Department(department_id) ON DELETE CASCADE
  4. department_id INTEGER, FOREIGN KEY (department_id) REFERENCES Department(department_id) ON DELETE SET NULL (correct answer)
Explanation: When working with foreign keys, two things determine the correct behavior: the referential action (what happens to child rows when a parent is deleted) and whether the foreign key column allows NULL. The question requires that employee rows survive a department deletion and that their department_id automatically becomes null — so you need ON DELETE SET NULL paired with a nullable column. Option D gets both right: department_id INTEGER (nullable by default, since there's no NOT NULL constraint) combined with ON DELETE SET NULL. When a department is deleted, the database automatically sets the department_id to NULL on any affected employee rows, satisfying both requirements exactly. Option A uses ON DELETE NO ACTION, which prevents the deletion of any department that still has employees referencing it — the opposite of what's needed. Option B almost works, but the fatal flaw is NOT NULL: you can't set a column to NULL if it's declared NOT NULL. The database would raise a constraint violation the moment it tried to apply the SET NULL action. Option C uses ON DELETE CASCADE, which deletes the employee row entirely when its department is deleted — directly violating the requirement to retain the employee. A useful pattern to remember: whenever a question says "retain the child row but clear the reference," your answer needs both ON DELETE SET NULL and a nullable foreign key column. If either piece is missing, the behavior breaks. Scan every word of each option — exam questions frequently hide one small detail (like NOT NULL in option B) that makes an otherwise plausible answer wrong.

Question 4

The table Product has columns warehouse_id INTEGER and sku VARCHAR(20), with PRIMARY KEY (warehouse_id, sku). A new ShipmentLine table contains corresponding columns with the same data types. Neither parent column is individually unique.

Which clause should be included in CREATE TABLE ShipmentLine to require each line's warehouse and SKU pair to identify an existing product?

  1. FOREIGN KEY (warehouse_id) REFERENCES Product (warehouse_id), FOREIGN KEY (sku) REFERENCES Product (sku)
  2. FOREIGN KEY (sku, warehouse_id) REFERENCES Product (warehouse_id, sku)
  3. FOREIGN KEY (warehouse_id, sku) REFERENCES Product (warehouse_id, sku) (correct answer)
  4. FOREIGN KEY (warehouse_id, sku) REFERENCES Product (sku, warehouse_id)
Explanation: When you create a multi-column foreign key in SQL, two rules govern its correctness: the child columns must be listed in the same order as the referenced parent columns, and the referenced columns must form a unique or primary key constraint together as a group — not individually. In this scenario, Product has a composite primary key of (warehouse_id, sku). To enforce referential integrity in ShipmentLine, you need a single foreign key constraint that references both columns together, matching their order exactly. Option C — FOREIGN KEY (warehouse_id, sku) REFERENCES Product (warehouse_id, sku) — does exactly this: the child columns map positionally to the parent columns in the correct sequence, and the referenced pair is the valid primary key. Option A is the classic trap here. It tries to declare two separate single-column foreign keys, but neither warehouse_id nor sku alone is unique in Product. SQL requires that the referenced column(s) be a primary key or have a unique constraint. Splitting a composite key into individual foreign keys violates this rule and most databases will reject it entirely. Option B reverses the column order in the child list — (sku, warehouse_id) referencing (warehouse_id, sku) — meaning sku would be compared against warehouse_id and vice versa, causing a type mismatch or logical error. Wait — actually B lists (sku, warehouse_id) referencing (warehouse_id, sku), which misaligns the mapping. Option D makes the same mistake in the referenced side: (warehouse_id, sku) referencing (sku, warehouse_id) swaps the parent column order, breaking the positional mapping. The key study tip: with composite foreign keys, column order is not cosmetic — it determines which child value maps to which parent column. Always align them positionally.

Question 5

A table is created using CREATE TABLE EventLog (event_id INTEGER PRIMARY KEY, status VARCHAR(12) DEFAULT 'NEW' NOT NULL);.

Which statement correctly describes the effect of inserting a row into EventLog?

  1. INSERT INTO EventLog (event_id) VALUES (5); succeeds and stores 'NEW' as the status. (correct answer)
  2. INSERT INTO EventLog (event_id) VALUES (5); succeeds and stores NULL as the status.
  3. INSERT INTO EventLog VALUES (5, NULL); succeeds because the default replaces the explicit NULL.
  4. INSERT INTO EventLog VALUES (5, NULL); succeeds because NOT NULL applies only to omitted values.
Explanation: When working with DEFAULT and NOT NULL constraints in SQL, you need to understand exactly when each one activates — they serve different purposes and operate at different moments during an INSERT. A DEFAULT value kicks in only when a column is omitted entirely from the insert statement. If you omit status from the column list, the database engine substitutes 'NEW' automatically. A NOT NULL constraint, on the other hand, enforces that no NULL value ever gets stored — regardless of how it arrives. Answer A is correct. When you write INSERT INTO EventLog (event_id) VALUES (5);, the status column is not mentioned at all, so the engine applies the default value of 'NEW'. The row is stored with event_id = 5 and status = 'NEW'. Both constraints are satisfied. Answer B is wrong because it confuses "omitting a column" with "storing NULL." Omitting a column doesn't store NULL — it triggers the default. NULL would only appear if there were no default and no NOT NULL constraint. Answer C describes a behavior that doesn't exist in standard SQL. Defaults do not replace an explicit NULL. When you write VALUES (5, NULL), you are directly assigning NULL to status, which violates the NOT NULL constraint — causing the insert to fail, not succeed. Answer D is wrong for the same reason as C, but adds a false rule. NOT NULL is not scoped to "omitted values only" — it applies to any attempt to store NULL, including explicit ones. A good rule of thumb: defaults substitute for absence, while NOT NULL guards against explicit nulls. These two concepts are tested together precisely because students often conflate them.

Question 6

A table is created with CREATE TABLE Inventory (warehouse_id INTEGER, product_id INTEGER, quantity INTEGER, PRIMARY KEY (warehouse_id, product_id));. It currently contains key pairs (1, 10), (1, 11), and (2, 10).

Which pair of new key values can both be inserted without violating the primary key?

  1. (1, 10) and (3, 12)
  2. (1, 12) and (1, 12)
  3. (2, 11) and (3, 10) (correct answer)
  4. (NULL, 12) and (3, 13)
Explanation: When a table has a composite primary key, uniqueness is evaluated on the combination of columns together — not each column individually. A value like warehouse_id = 1 can repeat many times, as long as the full pair (warehouse_id, product_id) hasn't been used before. Keep this in mind as you evaluate each option against the existing pairs: (1, 10), (1, 11), and (2, 10). Option C gives you (2, 11) and (3, 10). Neither of these combinations exists in the table — (2, 11) is new even though warehouse 2 and product 11 each appear separately, and (3, 10) is new because warehouse 3 hasn't been used at all. Both inserts succeed without any violation, making C the correct answer. Option A fails immediately because (1, 10) is already in the table — inserting a duplicate primary key always throws a violation, regardless of the second value. Option B tries to insert (1, 12) twice in the same batch. Even if (1, 12) doesn't exist yet, you can't insert the same new key twice simultaneously — the second insert would collide with the first. Option D is a trap. Primary key columns cannot contain NULL values in SQL, because NULL represents an unknown — and you can't guarantee uniqueness of something unknown. So (NULL, 12) would be rejected outright. A quick mental checklist: verify each pair against existing data, watch for duplicates within the batch itself, and remember that NULL is never allowed in a primary key column.

Question 7

A Student table needs an internal integer primary key. Every student must also have an email address, and no two students may use the same email address.

Which CREATE TABLE statement enforces all three requirements?

  1. CREATE TABLE Student (student_id INTEGER PRIMARY KEY, email VARCHAR(200) NOT NULL PRIMARY KEY);
  2. CREATE TABLE Student (student_id INTEGER PRIMARY KEY, email VARCHAR(200) UNIQUE);
  3. CREATE TABLE Student (student_id INTEGER PRIMARY KEY, email VARCHAR(200) NOT NULL, UNIQUE (student_id, email));
  4. CREATE TABLE Student (student_id INTEGER PRIMARY KEY, email VARCHAR(200) NOT NULL UNIQUE); (correct answer)
Explanation: When designing a table, you need to match each business rule to the right SQL constraint: PRIMARY KEY enforces uniqueness plus NOT NULL on the key column, NOT NULL prevents missing values, and UNIQUE prevents duplicate values while still allowing the column to be defined separately. The question asks you to satisfy three rules simultaneously: an integer primary key, a mandatory email (NOT NULL), and a unique email (UNIQUE). Answer D — email VARCHAR(200) NOT NULL UNIQUE — chains both constraints directly onto the column declaration, cleanly enforcing all three requirements at once. This is valid SQL: you can stack inline constraints on a single column, and NOT NULL UNIQUE together means every row must have an email and no two rows can share one. Answer A fails immediately because a table can only have one PRIMARY KEY. Declaring email as a second PRIMARY KEY is a syntax error in standard SQL — you cannot have two primary keys on a single table. Answer B correctly sets student_id as the primary key, but it only adds UNIQUE to the email column, omitting NOT NULL. This means a student could be inserted with no email at all, violating the "every student must have an email" rule. Answer C applies UNIQUE (student_id, email) as a composite unique constraint, which only prevents the combination of both columns from repeating — not the email alone. Two students could share the same email as long as their IDs differ, which breaks the uniqueness requirement. As a study tip, remember: UNIQUE alone does not block NULLs, so whenever a column must be both present and non-repeating, you always need NOT NULL UNIQUE together.

Question 8

The Customers table already exists, and customer_id is its primary key. A new Orders table must give every order a non-null, unique identifier and require every order to reference an existing customer.

Which CREATE TABLE statement satisfies all requirements?

  1. CREATE TABLE Orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)); (correct answer)
  2. CREATE TABLE Orders (order_id INTEGER UNIQUE, customer_id INTEGER NOT NULL, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id));
  3. CREATE TABLE Orders (order_id INTEGER, customer_id INTEGER NOT NULL, UNIQUE (order_id, customer_id), FOREIGN KEY (customer_id) REFERENCES Customers(customer_id));
  4. CREATE TABLE Orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, FOREIGN KEY (order_id) REFERENCES Customers(customer_id));
Explanation: When designing a relational table, you need to satisfy three distinct requirements separately: a non-null unique identifier, a not-null foreign key column, and a properly targeted foreign key constraint. Keep all three in mind as you evaluate each option. Option A nails all three. PRIMARY KEY automatically enforces both NOT NULL and UNIQUE on order_id, giving every order a guaranteed unique identifier. The customer_id column is marked NOT NULL, and the FOREIGN KEY correctly references Customers(customer_id), ensuring every order ties to a real customer. This is your answer. Option B falls short because UNIQUE alone does not prevent null values. A column can have multiple nulls in many SQL engines even with a UNIQUE constraint (nulls are treated as distinct from each other), so the "non-null" requirement goes unmet. You'd need to add NOT NULL explicitly alongside UNIQUE — or just use PRIMARY KEY. Option C applies UNIQUE to the combination of (order_id, customer_id), which is a composite unique constraint. This doesn't guarantee that order_id alone is unique, and it still doesn't enforce NOT NULL on order_id. The requirement calls for each order to have its own unique, non-null identifier — not a pair. Option D has the foreign key pointing to the wrong column — FOREIGN KEY (order_id) REFERENCES Customers(customer_id). The order_id is the order's own identifier, not a reference to a customer. This would attempt (and likely fail) to match order IDs against customer IDs, which is logically meaningless. Study tip: Remember that PRIMARY KEY = NOT NULL + UNIQUE in one declaration. When a question requires both properties on an identifier column, PRIMARY KEY is almost always the cleanest and correct solution.

Question 9

An Employee table must store a unique employee identifier and an optional manager identifier. A manager must be another employee in the same table, and top-level employees must be allowed to have no manager.

Which statement creates the required self-referencing structure?

  1. CREATE TABLE Employee (employee_id INTEGER PRIMARY KEY, manager_id INTEGER NOT NULL, FOREIGN KEY (manager_id) REFERENCES Employee(employee_id));
  2. CREATE TABLE Employee (employee_id INTEGER PRIMARY KEY, manager_id INTEGER, FOREIGN KEY (manager_id) REFERENCES Employee(employee_id)); (correct answer)
  3. CREATE TABLE Employee (employee_id INTEGER PRIMARY KEY, manager_id INTEGER, FOREIGN KEY (manager_id) REFERENCES Employee(manager_id));
  4. CREATE TABLE Employee (employee_id INTEGER PRIMARY KEY, manager_id INTEGER, FOREIGN KEY (manager_id) REFERENCES Manager(employee_id));
Explanation: When you see a question about hierarchical data in SQL, focus on two key design requirements: how a table references itself, and whether a column allows NULL values. A self-referencing foreign key is a foreign key in a table that points back to the primary key of that same table. This is the standard way to model hierarchies like employees and managers, where a manager is also an employee. The foreign key column must reference the primary key of the parent table — in this case, employee_id. Option B is correct because manager_id is declared without NOT NULL, making it optional (allowing NULL for top-level employees with no manager), and the foreign key correctly references Employee(employee_id) — the primary key of the same table. This satisfies every requirement in the passage. Option A fails because manager_id INTEGER NOT NULL forces every employee to have a manager, which means top-level employees cannot exist. The self-reference itself is valid, but the constraint breaks the business rule. Option C has the correct nullable manager_id, but the foreign key references Employee(manager_id) instead of Employee(employee_id). Foreign keys must point to a primary key (or unique key) column — manager_id is neither, so this is structurally invalid. Option D references a completely different table, Manager, which doesn't exist in the schema. This would cause a creation error and doesn't implement a self-referencing structure at all. A good study tip: whenever you model a hierarchy in one table, remember the formula — the child column (e.g., manager_id) must be nullable for optional relationships and must reference the primary key of the same table.

Question 10

A table's amount column must store values with exactly five available digits to the left of the decimal point and two available digits to the right. The definition should use no more precision than necessary.

Which column definition has the required precision and scale?

  1. amount DECIMAL(5,2)
  2. amount DECIMAL(7,2) (correct answer)
  3. amount DECIMAL(7,5)
  4. amount DECIMAL(9,2)
Explanation: When working with DECIMAL in SQL, you need to understand two parameters: precision and scale. Precision is the total number of digits the column can store (both sides of the decimal), and scale is how many of those digits appear to the right of the decimal point. So DECIMAL(p, s) means p total digits, s of which are after the decimal — leaving p - s digits available to the left. The question asks for exactly 5 digits to the left and 2 digits to the right, with no excess precision. That means you need p - s = 5 and s = 2, giving you p = 7. The correct definition is B) amount DECIMAL(7,2), which allocates exactly 7 total digits: 5 to the left of the decimal and 2 to the right — nothing wasted, nothing missing. Choice A) DECIMAL(5,2) is a common trap. It looks like it might mean "5 digits and 2 decimal places," but remember: precision is total digits. This only allows 5 - 2 = 3 digits to the left, not 5. Choice C) DECIMAL(7,5) has the right total precision but the wrong scale — it reserves 5 digits to the right of the decimal, leaving only 2 to the left. Choice D) DECIMAL(9,2) gives 7 digits to the left, which exceeds the requirement and uses more precision than necessary. A handy tip: always calculate left-side digits as precision - scale. When a question says "no more precision than necessary," find the minimum p that satisfies both sides, then verify your arithmetic before choosing.