A table tracking student course enrollments needs a primary key. A student can enroll in many courses, and a course can have many students. Which of the following would be the best choice for a primary key for the Enrollments table?
A composite key (combining StudentID and CourseID). Because this is a many-to-many relationship, neither ID is unique on its own. The combination of the two uniquely identifies a specific enrollment record.
A developer needs to clear all data from a very large LogEvents table containing millions of rows. The table structure must remain, but all records should be removed. Which command is typically the most efficient for this specific task because it is a minimally logged operation?
TRUNCATE TABLE. Unlike the DELETE command, TRUNCATE is a DDL operation that is minimally logged and deallocates pages rather than logging individual row deletions, making it significantly faster for clearing large tables.
How would a developer remove a named constraint called PriceCheck from a Products table?
ALTER TABLE Products DROP CONSTRAINT PriceCheck; constraints are structural elements, so they must be modified using the ALTER TABLE statement followed by DROP CONSTRAINT.
A Students table has a GPA column. The business rule states that the GPA must be between 0.0 and 4.0. Which constraint should be used to enforce this rule on the column?
The CHECK constraint. This constraint allows you to specify a Boolean condition (e.g., CHECK (GPA >= 0.0 AND GPA <= 4.0)) that every row must satisfy.
To store a 5-digit US ZIP code like ‘90210’, which data type provides the most efficient and appropriate storage?
CHAR(5). ZIP codes are fixed-length strings, not mathematical numbers (you don’t subtract or multiply them). CHAR is more efficient than VARCHAR for fixed lengths, and unlike numeric types, it preserves leading zeros (e.g., ‘02110’).
To ensure that every product has a unique SKU (Stock Keeping Unit), a developer needs to add a constraint to the existing Products table. Which syntax correctly adds this table-level constraint?
ALTER TABLE Products ADD CONSTRAINT uk_sku UNIQUE (SKU); The UNIQUE constraint ensures all values in the column are distinct from one another.
A developer wants to give a name to a new constraint to make error messages more understandable. Which keyword allows this?
CONSTRAINT. By using the syntax ‘CONSTRAINT [Name] [Type]’, such as ‘CONSTRAINT pk_Employees PRIMARY KEY’, you explicitly name the rule rather than letting the database generate a random system name.
A table Courses has a primary key CourseID. A table Prerequisites has a foreign key CourseID that references the Courses table. What must happen before the Courses table can be dropped?
The foreign key constraint (or the Prerequisites table) must be dropped first. Referential integrity prevents you from dropping a parent table while a child table still references it.
