Exercise

Exercise 1

Write the SQL script to define a new table to track customers buying wristbands from an on-line store. Decide upon an appropriate name, data type, and nullability option for each column. Due to strict storage requirements, use the most conservative data types possible. Guidelines for the columns in this table are as follows:

Define a single column for the customer's name. It should allow between 1 and 200 characters and is a required entry. Some customer's may have foreign names.

Four columns are needed to store the customer's address, city, state and zip code. All addresses and cities will contain only U.S. domestic names, and none of these columns require a value. The address column should allow up to 200 characters, the city should allow up to 100 characters, the state will always be a two-character abbreviation, and the postal code will always be five characters in length.

A column is needed to store the quantity of wristbands purchased. This column will store a whole number value up to 10,000 and should be automatically set to 1 for new records.

A column is needed to store the price paid. This is a required entry and should store values up to $100,000.

Answers

 CREATE TABLE WristBandSales (  CustomerName    nVarChar(200) NOT NULL , Address        VarChar(200)  NULL , City           VarChar(100)  NULL , State          Char(2)       NULL , ZipCode        VarChar(5)    NULL , Quantity       SmallInt      NOT NULL  DEFAULT 1 , Price          SmallMoney    NOT NULL ) 

Although it's considered good practice to define a primary key column, the requirements didn't specify this. The CustomerName column is defined as an nVarChar type so it can store Unicode characters using an international character set. This doubles the storage requirement but meets this need as effectively as possible.

The Address and City columns don't require Unicode capability so they use a VarChar data type. The State column uses a fixed-length type because of the overhead associated with the VarChar type uses 2 bytes even if the column is empty. The ZipCode column could be defined as either Char(5) or VarChar(5). There are slight performance improvements using the Char type for smaller values but an empty value in a Char will use five characters of storage. Although it's probably more practical to use the Char type in this example, the requirement was to reduce store space.

The SmallMoney type, although not common, reduces storage requirements and meets the maximum value requirement.

Exercise 2

Wristbands are sold on two separate web sites that use two copies of the database. Sales records will be merged together on occasion into one database.

Using the query you created in Exercise 1, add a column to serve as a primary key and to uniquely identify each sales record. Records should not be stored in physical order using this value.

We're currently only licensed to sell wristbands in three states. Constrain the State column so it only accepts customers in Washington, Oregon, and California (WA, OR, and CA).

Answers

 CREATE TABLE WristBandSales (  SalesID        UniqueIdentifier  NOT NULL  DEFAULT NEWID() CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED , CustomerName   nVarChar(200)        NOT NULL , Address        VarChar(200)         NULL , City           VarChar(100)         NULL , State          Char(2)              NULL , ZipCode        VarChar(5)           NULL , Quantity       SmallInt             NOT NULL  DEFAULT 1 , Price          SmallMoney           NOT NULL , CONSTRAINT CK_State CHECK (State IN(‘WA’, ) 

The SalesID column was defined as a UniqueIdentifier type to ensure uniqueness. Unlike using the Identity attribute with the Int type, this column will not automatically generate a value when a record is inserted. The NEWID() function is used to do this when referenced in a default attribute.