SQL and MySQL course Β· Module 4: Creating tables (DDL)

Data types - choosing the right drawer

2 min read Β·
In this lesson4

Every field of a shelf must know what kind of data it accepts. It is like drawers for scrolls: one for numbers, another for dates, another for long stories. In MySQL we call these drawers data types.

Numbers

  • INT - whole numbers, e.g. a year of publication, an identifier. Ideal for rok_wydania or id.
  • DECIMAL(p,s) - decimal numbers with exact precision. p is the total number of digits, s is the digits after the decimal point. DECIMAL(8,2) will store amounts like 29.99 - perfect for cena.

Text

  • VARCHAR(n) - text of variable length, up to n characters. For tytul we will use e.g. VARCHAR(255), for imie - VARCHAR(50).
  • TEXT - long texts without a strict limit, e.g. a book opis (description) or the content of a review.

Dates

  • DATE - a date alone, e.g. 2024-03-15. Fits data_wypozyczenia, data_zwrotu.
  • DATETIME - a date together with a time, e.g. 2024-03-15 14:30:00.

The golden rule

Match the type to the data! Numbers to INT, amounts to DECIMAL, short strings to VARCHAR, long descriptions to TEXT, dates to DATE. The wrong drawer means chaos in the Library - we do not keep numbers in a drawer meant for text, nor do we cram text into a drawer meant for numbers.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. Which type will you use for a column storing a book's year of publication (a whole number)?

  2. 2. Which type will you use for the cena column to store amounts with cents precisely, e.g. 29.99?

These are 2 of 4 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Horizontal ordering

    Arrange the elements to form the start of a table-creation statement (left to right):

  • Code editor

    Write a CREATE TABLE statement that creates the autorzy table with columns: id of type INT, imie of type VARCHAR(50), nazwisko of type VARCHAR(50) and rok_urodzenia of type INT.

  • Click in order

    Click the elements in the order in which we write a single text column definition (one line):

  1. QuestionType for whole numbers
  2. QuestionType for price
  3. QuestionShort text versus long text
  4. Put in orderThe start of a CREATE TABLE statement
  5. Coding taskA table with correct types
  6. QuestionMeaning of DECIMAL parameters
  7. Click in orderThe tytul text column

Useful articles