FrançaisGet started

CREATE TABLE and data types

The CREATE TABLE statement and the usual column types (INTEGER, NUMERIC, VARCHAR, CHAR, DATE, TIMESTAMP, BOOLEAN), the DEFAULT clause: choosing a type and predicting what the database stores, transforms or rejects.

  • 22 cards
  • 4 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. Understand

A few cards from the deck

  • CREATE TABLE: what is it?

    Show the answer

    SQL statement that builds an empty relation by naming its columns, each with its type and any constraints.

    It only defines the structure: rows arrive later through INSERT. CREATE TABLE produit (ref INTEGER, libelle VARCHAR(40)) builds an empty two-column table.

  • What does PostgreSQL do with this INSERT?

    ```sql
    CREATE TABLE produit (
    ref INTEGER,
    libelle VARCHAR(10),
    prix NUMERIC(5,2)
    );
    INSERT INTO produit VALUES (1, 'Clavier', 1000.00);
    `

    • It rejects it with numeric field overflow: 4 digits before the point, only 3 allowed
    • It stores 1000.00 as is, because the precision only limits the number of decimals
    • It stores 999.99, the largest value the column allows, without warning anyone
    • It rejects it, because Clavier exceeds the 10 characters of the label
    Show the answer

    It rejects it with numeric field overflow: 4 digits before the point, only 3 allowed

    NUMERIC(5,2) leaves 5 - 2 = 3 digits for the integer part: the maximum is 999.99. The engine never caps silently, it raises ERROR: numeric field overflow. Clavier is 7 characters long, it fits in VARCHAR(10).

  • A NUMERIC(6,2) column accepts at most … digits before the point and … digits after it.

    Show the answer

    A NUMERIC(6,2) column accepts at most 4 digits before the point and 2 digits after it.

    Precision 6 counts all the digits, scale 2 counts those of the fractional part: 6 - 2 = 4 digits are left for the integer part, so 9999.99 at most.

  • Column type: what is it?

    Show the answer

    The domain of values a column may hold; it decides the space used and the operations allowed on those values.

    Two NUMERIC values can be added and two DATE values compared, but two VARCHAR values cannot be added: the database applies the rules of its type to each column.

  • Why is a postcode such as 01000 not stored in an INTEGER column, when it only contains digits?

    • INTEGER is limited to four digits, too few for a postcode
    • INTEGER refuses any value that starts with a zero and raises an error
    • The digits of an integer cannot be compared one by one with those of another
    • An integer has no leading zero: 01000 would become 1000
    Show the answer

    An integer has no leading zero: 01000 would become 1000

    The database reads '01000' as the integer 1000 and stores 1000: the zero is lost. A postcode, a phone number or an identifier is never used in arithmetic: store it as CHAR(5) or VARCHAR.

Study this deck