SQL – Data Definition - 2

CREATE TABLE…

CREATE TABLE Modules
(
moduleName CHARACTER(15)
,level SMALLINT
,courseCode CHARACTER(3)
,staffNo INTEGER
)

DATA TYPES…

• Act in part as a definition for domains.
• Every data value within the column must be of the same type.
• Define certain properties concerning the allowable values for a domain.
• Imagine an Excel sheet for an example.
• STRING Types

                          CHARACTER(n)

                          CHARACTER VARYING(n)

                          BIT(n)

                          BIT VARYING(n)

• NUMERIC Types

                            NUMERIC(m,n) A synonym for decima

                            DECIMAL(m,n)

                            INTEGER or INT

                            SMALLINT 

                            FLOAT (A number stored in floating point representation)

                            REAL (A synonym for FLOAT)

                            DOUBLE PRECISION (A synonym for FLOAT)

• DATETIME Types

                           DATE

                           TIME

                           TIMESTAMP (Date and times)

                           INTERVAL (Intervals between dates)

Other Domain Properties…

• NOT NULL – user must enter value for that column
• UNIQUE – prohibits the user from entering duplicates
• DEFAULT VALUES – Value a column should take in case user didn’t enter the value

Example…

CREATE TABLE Modules

(

moduleName CHARACTER(15) NOT NULL UNIQUE

,level SMALLINT DEFAULT 1

,courseCode CHARACTER(3)

,staffNo INTEGER

)