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

Special drawers - DATE, BOOLEAN and ENUM

2 min read Β·
In this lesson4

Beyond numbers and text, the Library needs a few drawers with a special purpose. Get to know them so your shelves describe the world precisely.

DATE and DATETIME - time in the Library

  • DATE - a date alone, e.g. 2024-03-15. Ideal for data_wypozyczenia or data_zwrotu.
  • DATETIME - a date together with a time, e.g. 2024-03-15 14:30:00. Use it when the exact moment matters, e.g. data_logowania.

Remember: if the day alone is enough - DATE. If you also need the clock - DATETIME.

BOOLEAN - true or false

  • BOOLEAN (in MySQL it is really TINYINT) - stores a true/false value. Great for fields like dostepna:
dostepna BOOLEAN DEFAULT TRUE

In MySQL TRUE is 1 and FALSE is 0 - but you can write it in human terms.

ENUM - a list of allowed values

  • ENUM('a','b','c') - the column may take only one of a predefined list of values. Great for states with a fixed set of options:
stan ENUM('nowy', 'uzywany', 'zniszczony')

A copy can only be new, used or destroyed - MySQL will accept nothing else. It is a guard that lets no random values in.

Example: the egzemplarze shelf

CREATE TABLE egzemplarze (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data_zakupu DATE,
  dostepna BOOLEAN DEFAULT TRUE,
  stan ENUM('nowy', 'uzywany', 'zniszczony')
);

The purchase date, availability and state - each drawer matched to its content.

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 to store the moment of an event together with the time, e.g. 2024-03-15 14:30:00?

  2. 2. Which type best stores a true/false value, e.g. whether a copy is available?

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

Hands-on tasks in the game

  • Code editor

    Write a CREATE TABLE statement that creates the egzemplarze table with columns: id of type INT with AUTO_INCREMENT and PRIMARY KEY, data_zakupu of type DATE, dostepna of type BOOLEAN with a default value of TRUE, and stan of type ENUM('nowy','uzywany','zniszczony').

  1. QuestionType for a date with time
  2. QuestionLogical type
  3. QuestionThe ENUM type
  4. Coding taskA shelf with special types
  5. QuestionData type for a boolean value

Useful articles