You are a database administrator on an instance of SQL Server 2008. You need to create an Event table that will store the following data:
• A fixed-length, three-character alphanumeric code identifying the type of event
• The event description, with a maximum length of 30 characters
• The number of seats available for the event, with a maximum value of 50
• A flag to indicate if the event is public or private
• A event rating between 0 and 10 with two decimal places to the right of the decimal
Your table will only be used by English-speaking users, and you want to minimize the storage requirements for the table.
Which CREATE TABLE statement should you use?
A. CREATE TABLE Event (
EventID int,Code char(3),Description varchar(30),SeatsAvail smallint,TypeFlag bit,Rating decimal(3,2));
B. CREATE TABLE Event (
EventID int,Code char(3),Description varchar(30),SeatsAvail int,TypeFlag bit,Rating decimal(4,2));
C. CREATE TABLE Event (
EventID int,Code char(3),Description varchar(30),SeatsAvail tinyint,TypeFlag bit,Rating decimal(3,2));
D. CREATE TABLE Event (
EventID int,Code char(3),Description varchar(30),SeatsAvail tinyint,TypeFlag bit,Rating decimal(4,2));
Correct Answer: D
Explanation/Reference:
You should use the following CREATE TABLE statement:
CREATE TABLE Event (EventID int,Code char(3),Description varchar(30),SeatsAvail tinyint,TypeFlag bit,Rating decimal (4,2));
The char and varchar data types both allow character data up to 8,000 characters. The char data type is fixed-length, and the varchar data type is variable-length.
Creating the Code column as char(3) will allow the Code column to store a fixed-length three-character alphanumeric code. Creating the Description column as varchar(30) allows for some storage savings for rows with descriptions shorter than 30 characters. You should use a char data type when values in a column will be a consistent length and a varchar data type when the length of values will vary.
For the SeatsAvail column, a tinyint data type would allow the required values but minimize storage. A tinyint data type can store an integer value between 0 and 255 with a single byte. The bit data type would be used to represent a Boolean value, such as whether the event is public or private. The Rating column is defined as decimal(4,2). This allows the column to store a decimal value with a maximum of four total digits, with two digits to the right of the decimal place. This will allow you to store Rating values between 0.00 and 99.99.
You should not use the following CREATE TABLE statement:
CREATE TABLE Event (EventID int,Code varchar(3),Description varchar(30),SeatsAvail int,TypeFlag int,Rating decimal (4,2));
A varchar data type stores variable-length data, rather than fixed-length data as required for the Code column in this scenario. The SeatsAvail column does not minimize storage because it is defined as an int data type. An int requires four bytes of storage. In this scenario, a tinyint would be sufficient. The TypeFlag does not minimize storage because it uses an int data type. Because only two possible values need to be stored, a bit could represent this with less storage.
You should not use the following CREATE TABLE statement:
CREATE TABLE Event (EventID int,Code char(3),Description varchar(30),SeatsAvail smallint,TypeFlag bit,Rating decimal (3,2));
The SeatsAvail column does not minimize storage because it is defined as a smallint data type. A smallint data type requires two bytes of storage and can hold values between -32,768 and 32,767. A tinyint uses one byte and can store values between 0 and 255, which would have been sufficient in this scenario. The Rating column will not be able to store the required value because only three total digits are allowed. With this column definition, the Rating column could only hold values between 0.00 and 9.99.
You should not use the following CREATE TABLE statement:
CREATE TABLE Event (EventID int,Code char(3),Description varchar(30),SeatsAvail tinyint,TypeFlag bit,Rating decimal (3,2));
With this statement, the Rating column will not be able to store the required value because only three total digits are allowed. With this column definition, the Rating column could only hold values between 0.00 and 9.99.
