Question

Modify the following SQL command so that the Rep_ID column is the PRIMARY KEY for the table and the default value of Y is assigned to the Comm column.

(The Comm column indicates whether the sales representative earns commission.) CREATE TABLE STORE_REPS ( Rep_ID INT(5), Last VARCHAR(15), First VARCHAR(10), Comm CHAR(1));

Answer 1

In this SQL command, the Rep_ID column is set as the primary key, ensuring unique identifiers for each record. The Comm column is assigned a default value of 'Y', streamlining data entry and indicating default commission status.

Step-by-step explanation:

In the modified SQL command, the Rep_ID column is designated as the primary key for the STORE_REPS table by adding the PRIMARY KEY constraint after the column definition. This ensures that each Rep_ID value is unique and serves as the identifier for each record in the table.

Additionally, the default value for the Comm column is set to 'Y' using the DEFAULT keyword. This means that if no value is explicitly provided for the Comm column during the insertion of a new record, it will automatically be assigned the default value of 'Y'. This is specified to indicate that the sales representative earns commission by default.

The modified command enhances data integrity by enforcing uniqueness in the Rep_ID column, and it streamlines the data entry process by providing a default value for the Comm column. This is particularly useful in scenarios where the majority of sales representatives earn commission, and specifying 'Y' each time is redundant.