Table Creation Data Types

1
Table Creation / Data Types
2
A Data type is...

• Specifies what kind of information a column will
  hold so that the system will know how the data is
  to be physically stored and manipulated.
• Example
• an integer data type can only hold whole numbers
  and can be manipulated arithmetically.

3
Different Datatypes
4
Defining Data type Length

• Defining the length of a data type allocates
  storage space for that specified data.
• Using a fixed length data typed allocates the
  same amount of space regardless of the actual
  data length.
• Variable length datatypes can adjust to the
  length of the data as long as the maximum is not
  exceeded.

5
What does this mean?
If we define the following fixed
datatype char(10) 10 bytes of storage space is
allocated regardless of the actual data
length. If we redefine as a variable
datatype varchar(10) the data length can not
exceed 10 bytes but the actual storage space will
only be as long as the data stored.
6
Implications?

• Data type allocation

7
Conclusions

• Choose your datatypes and lengths carefully.
• Choosing a fixed length can yield higher
  performance since the system need not maintain
  allocation.
• However, if you know your data will not have a
  consistent length then a variable length is best
  as this will conserve space.

8
Nulls are...

• Nulls represent data that is absent and/or
  unavailable.
• Nulls are not blank or zero
• Assigning null status to a column tells the
  system to insert a null value if there is no data
  available
• Assigning a not null status means that the
  system will reject any entries that do not
  provide data for this column. An error is usually
  generated.

9
To Be Null or Not to Be Null?

• Is the data essential?
• Is the column a primary key?
• Would things make sense if the data was not
  present?
• Do other things rely on this data?

10
4 Steps to Table Creation

• Name itWhat is the entity?
• Name the columns it containsWhat are the
  properties of this entity and which am I
  interested in?
• Specify the datatype of each columnWhat kind of
  data do these properties represent?
• Specify the NULL status of each column

11
Table Creation Syntax

• SQL statement
• CREATE TABLE tablename
• (colname datatype(length) null/not null,
• .
• .)

12
Example

• create table titles
• (title_id char(6) not null,
• title varchar(80) not null,
• type char(12) null)

13
Table Creation Process

• Decide on datatype (length, precision, etc.) of
  each column
• Decide which columns should be null
• Decide which columns need to be unique
• Note foreign/primary key pairings
• Make sure permissions are properly assigned

14
Charting table structure
15
Query Construction

• Spelling is and always will be important
• Strings must be enclosed in single quotes ex.
  Jane Doe
• Numeric data should not
• All search conditions must be separated by AND or
  OR depending on the desired condition

16
Testing Equality

• For numeric datagt, lt, , !
• For string data, !
• Wildcard comparison for stringsLIKE or NOT
  LIKE - zero or more character_ - one
  character_at_ - use to escape a wildcard