SQL and MySQL course · Module 11: Capstone project
The architect's workshop - CREATE TABLE and INSERT
In this lesson3
A master does not only read scrolls - they build new shelves. Let us return to the art of creating tables and filling them with data.
CREATE TABLE - a new shelf
We create a table with the CREATE TABLE statement. Each column has a name and a data type:
CREATE TABLE wydawnictwa (
id INT PRIMARY KEY AUTO_INCREMENT,
nazwa VARCHAR(100),
miasto VARCHAR(100)
);The most common data types:
INT- a whole number (e.g. a year, a count).VARCHAR(n)- short variable-length text up toncharacters (e.g. a title).TEXT- long text (e.g. a review).DECIMAL(8,2)- a number with decimal places (e.g. a price).DATE- a date.
PRIMARY KEY and AUTO_INCREMENT
PRIMARY KEY is the unique signature of every row. AUTO_INCREMENT makes the database assign the next number to each new entry by itself - we do not have to remember it.
INSERT - filling the shelf
We add data with the INSERT statement. We can add one row or many at once:
INSERT INTO czytelnicy (imie, nazwisko, miasto)
VALUES ('Anna', 'Nowak', 'Kraków'),
('Piotr', 'Lis', 'Gdańsk');Separate consecutive rows with a comma. This is how the Library grows - shelf by shelf, scroll by scroll!
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 data type will you choose for short text, e.g. a book title?
2. What does AUTO_INCREMENT on an id column do?
These are 2 of 3 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 wydawnictwa table with columns: id (INT, primary key, AUTO_INCREMENT), nazwa (VARCHAR(100)) and miasto (VARCHAR(100)).
- Code editor
Write an INSERT statement that adds one row to the ksiazki table (columns tytul, autor_id, cena): ('Nowy Świat', 3, 49.99).
- Vertical ordering
Arrange the lines of the statement that creates the wydawnictwa table (top to bottom):
- Code editor
Write an INSERT statement that adds TWO rows to the czytelnicy table (columns imie, nazwisko, miasto): ('Anna', 'Nowak', 'Kraków') and ('Piotr', 'Lis', 'Gdańsk').