---

The CREATE TABLE command

---

• The CREATE TABLE command.
  • The CREAT TABLE command is used to define tables (= relations) within a database schema.

  • The relation created by the CREAT TABLE command is initially empty.

    You can then use an INSERT command to insert data into the relation

    Or better, use one of the vendor's provided data loading tools to "bulk insert" data into a relation

    (Such tools can even load data from a spreadsheet into a table)

    ---

  • Syntax of the CREAT TABLE command:

    CREATE TABLE relation_name ( attr_name1 type1 [attr_constraint1] , attr_name2 type2 [attr_constraint2] , .... attr_namen typen [attr_constraintn] [ , integrity constrains ] );

    Defines a relation named relation_name with attributes:

    • attr_name₁
    • attr_name₂
    • ...
    • attr_name_n

    ---

  • Each attribute has a data type

    The available data types is an more varied than the data types available in a programming language

  ---

  ---

• Data Types in SQL
  • Numeric Types:
    • INTEGER or INT:

      (ordinary) integer

    • SMALLINT

      short integer

    • DECIMAL(i,j) or DEC(i,j) or NUMERIC(i,j):

      fixed point numbers with i decimal digits precision and j digits after the decimal point fixed point numbers are accurate and do NOT have round off errors Example: DEC(8,3) = fixed point numbers of the format: xxxxx.yyy NOTE: You should use DEC(n,2) in financial application (to avoid roundoff errors !)

    • FLOAT or REAL:

      single precision floating point numbers (with roundoff errors)

    • DOUBLE PRECISION

      double precision floating point numbers (still with roundoff errors)

    ---

  • Character string
    • CHARACTER(n) or CHAR(n)

      fixed length character strings (number of characters in string is (always) n)

    • VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(n)

      variable length character strings (maximum number of characters in string is n) SQL includes the number of characters of the string when it stores a VARCHAR value In total, VARCHAR(n) stores n + 2 bytes, the 2 extra bytes is for the string length

      NOTE: To denote string, use single quotes: 'John'

    ---

  • Bit string
    • BIT(n)

      fixed length bit string (number of bits in string is (always) n)

    • BIT VARYING(n)

      variable length bit string (maximum number of bits in string is n)

      NOTE: To denote bit string 10101, use: B'10101'

    ---

  • Boolean
    • BOOLEAN

      boolean data

      NOTE:

      • Due to the NULL value, SQL uses a three value logic to evaluate boolean expressions:

        When comparing two values x and y, if either x or y is NULL , then some logical comparisons evaluate to an UNKNOWN value rather than true or false, e.g.: x > NULL --> unknown

      • Advice: avoid using NULL values if you can....

    ---

  • DATE
    • DATE

      Calendar date

      NOTE:

      • DATE values are specified as: DATE'YYYY-MM-DD'

      • Must be preceeded by the keyword DATE

    ---

  • TIME

    • TIME

      Time of day

      NOTE:

      • TIME values are specified as: TIME'HH:MM:SS'

      • Must be preceeded by the keyword TIME

    ---

  • TIMESTAMP

    • TIMESTAMP

      DATA + TIME

      NOTE:

      • TIMESTAMP values are specified as: TIMESTAMP'YYYY-MM-DD HH:MM:SS'

      • Must be preceeded by the keyword TIMESTAMP

      • Need a space between DATE and TIME

  ---

  Note:

  A database system has the liberty not to provide some SQL datatype Read the documentation of the specific DBMS that you are using to see which data types are available Data types in MySQL: click here

  ---

  ---

  ---

  ---

• Example creating a table (relation) and inserting data
  • Example: CREATE TABLE:

    CREATE TABLE test ( name char(10), addr varchar(10), id int, salary dec(9,2), bdate date );

    ---

  • Example: insert a tuple into the database

    INSERT INTO test values ( 'John', '123 H Lane', 12345, 45000.65, DATE'1970-4-1' );

    ---

  • Example: select tuples from a table (relation)

    SELECT * from test;

  ---

  ---

• Deleting Tables (Relations)
  • The DROP TABLE command will delete a relation (its defintion and all its content)

  • Example:

    DROP TABLE test;

  ---

  ---

  ---

  ---

• Finding out information about your database and tables in MySQL:
  • These commands are helpful to find out about the structure of the database:

    show databases list all databases in mySQL use DBName Set current DB to DBName show tables List the tables in the current DB SELECT DATABASE() Return the name of the current DB describe TableName Display the structure of table TableName