SQL and MySQL course Β· Module 4: Creating tables (DDL)
Data types - choosing the right drawer
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_wydaniaorid. - DECIMAL(p,s) - decimal numbers with exact precision.
pis the total number of digits,sis the digits after the decimal point.DECIMAL(8,2)will store amounts like29.99- perfect forcena.
Text
- VARCHAR(n) - text of variable length, up to
ncharacters. Fortytulwe will use e.g.VARCHAR(255), forimie-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. Fitsdata_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. Which type will you use for a column storing a book's year of publication (a whole number)?
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):