SQL and MySQL course Β· Module 4: Creating tables (DDL)
Special drawers - DATE, BOOLEAN and ENUM
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 fordata_wypozyczeniaordata_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 TRUEIn 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. 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. 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').