Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 41 of 49

CREATE TABLE: building a table and choosing data types

Learn to create tables with CREATE TABLE, pick sensible data types for text, numbers, money, dates and flags, and see how SQLite differs from other databases.

15 minDesigning databasesUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonWrite a CREATE TABLE statement with sensible column types, insert a row into it, and explain how typing differs between databases.

Where tables come from

Until now you have read tables that already existed. Somebody had to create them. After this lesson you will be able to write the statement that does it, choose a type for every column, and know where your database might let you get away with mistakes that another database would reject.

The shape of the statement is simple. You say CREATE TABLE, give the table a name, and in parentheses list the columns. Each column has a name and a data type, and the columns are separated by commas.

The examples in this lesson use brand new tables, run in an empty SQLite 3.54 database, so nothing here touches the company data (except one example that copies from it). The next few lessons build on tables like these to show constraints, keys and changes.

Your first table

Here is a table for the members of a small club:

sql
CREATE TABLE members (
  id         INTEGER PRIMARY KEY,
  full_name  VARCHAR(80) NOT NULL,
  email      VARCHAR(100) NOT NULL,
  birth_date DATE,
  balance    DECIMAL(10,2) NOT NULL,
  is_active  BOOLEAN NOT NULL,
  joined_at  TIMESTAMP NOT NULL
);

The statement itself prints nothing. It changes the database by adding an empty table. PRIMARY KEY and NOT NULL are constraints, and the next two lessons explain them. For now, read them as "this column identifies the row" and "this column must always have a value".

Now add a row and read it back:

sql
INSERT INTO members (id, full_name, email, birth_date, balance, is_active, joined_at)
VALUES (1, 'Nisha Rao', 'nisha@example.com', '1995-08-21', 250.50, 1, '2026-01-05 09:30:00');

SELECT * FROM members;
result
+----+-----------+-------------------+------------+---------+-----------+---------------------+
| id | full_name | email             | birth_date | balance | is_active | joined_at           |
+----+-----------+-------------------+------------+---------+-----------+---------------------+
|  1 | Nisha Rao | nisha@example.com | 1995-08-21 |   250.5 |         1 | 2026-01-05 09:30:00 |
+----+-----------+-------------------+------------+---------+-----------+---------------------+

Notice that the balance came back as 250.5, not 250.50. SQLite does not keep the trailing zero. A database with a true decimal type would normally show 250.50, because DECIMAL(10,2) means "up to ten digits in total, two of them after the decimal point".

You can ask SQLite to describe a table with PRAGMA table_info. That command is specific to SQLite. MySQL uses DESCRIBE members;, the PostgreSQL command line uses \d members, and SQL Server has the sp_help procedure. Most of them can also be answered by querying the INFORMATION_SCHEMA views, a standard that SQLite does not provide.

sql
PRAGMA table_info(members);
result
+-----+------------+---------------+---------+------------+----+
| cid | name       | type          | notnull | dflt_value | pk |
+-----+------------+---------------+---------+------------+----+
|   0 | id         | INTEGER       |       0 |            |  1 |
|   1 | full_name  | VARCHAR(80)   |       1 |            |  0 |
|   2 | email      | VARCHAR(100)  |       1 |            |  0 |
|   3 | birth_date | DATE          |       0 |            |  0 |
|   4 | balance    | DECIMAL(10,2) |       1 |            |  0 |
|   5 | is_active  | BOOLEAN       |       1 |            |  0 |
|   6 | joined_at  | TIMESTAMP     |       1 |            |  0 |
+-----+------------+---------------+---------+------------+----+

Choosing data types

A type tells the database what a column may hold, how much space to reserve, and how to compare and calculate with it. The common standard types fall into a few families:

Kind of data Typical type Notes
Whole numbers INTEGER, SMALLINT, BIGINT Choose the smallest that is safe. Counts and ids are usually INTEGER.
Exact decimals DECIMAL(p,s) or NUMERIC(p,s) p is the total digits and s the digits after the point. Use it for money.
Approximate decimals REAL, DOUBLE PRECISION, FLOAT Fast, good for measurements, wrong for money.
Short text VARCHAR(n) Up to n characters.
Long text TEXT Free-form text with no fixed limit in most databases.
Dates and times DATE, TIME, TIMESTAMP A day, a time of day, or both.
Yes or no BOOLEAN See the differences note below.

Two choices deserve a closer look, because beginners get them wrong most often.

Money is not a float. Computers store floating-point numbers in binary, and many decimal fractions have no exact binary form. You can see the effect in one query:

sql
SELECT 0.1 + 0.2 AS total, 0.1 + 0.2 = 0.3 AS is_equal;
result
+---------------------+----------+
| total               | is_equal |
+---------------------+----------+
| 0.30000000000000004 |        0 |
+---------------------+----------+

The comparison is false, which is an awful surprise in an accounting report. A DECIMAL column in a database that implements it exactly avoids this. SQLite is the exception: it has no true decimal type, so a DECIMAL(10,2) column is stored as a floating-point number or an integer. In SQLite, a common workaround is to store money as whole cents in an INTEGER column. The company database sidesteps the problem in a similar way by holding salaries as whole numbers.

Dates are not free text. Use DATE and TIMESTAMP so the database can sort and calculate with them. Writing dates as '2026-01-05', year first, keeps them in the right order even when stored as text, which is what SQLite does underneath because it has no date type of its own.

Differences between databases

  • Booleans. PostgreSQL has a real BOOLEAN type that accepts TRUE and FALSE. MySQL accepts the word BOOLEAN but treats it as a small integer. SQL Server has no BOOLEAN and uses BIT. SQLite stores 1 and 0, as the output above shows.
  • Timestamps. In SQL Server the type named TIMESTAMP is not a date and time at all. Use DATETIME2 there.
  • Long text. PostgreSQL and MySQL have TEXT. SQL Server prefers VARCHAR(MAX).
  • Auto-numbering ids. Each database has its own spelling, which the keys lesson covers.

MySQL's own types are covered in more depth in the MySQL data types lesson and the MySQL CREATE TABLE lesson.

SQLite is loosely typed

This is the biggest difference you will meet if you practise on SQLite, which is why this course says so plainly. In most databases a column type is a rule. In SQLite it is closer to a suggestion called an affinity. Watch what an ordinary table accepts:

sql
CREATE TABLE loose (n INTEGER, label VARCHAR(3));
INSERT INTO loose VALUES (42, 'ok'), ('17', 'toolong'), ('abc', 'x');
SELECT n, typeof(n) AS n_type, label, length(label) AS len FROM loose;
result
+-----+---------+---------+-----+
| n   | n_type  | label   | len |
+-----+---------+---------+-----+
|  42 | integer | ok      |   2 |
|  17 | integer | toolong |   7 |
| abc | text    | x       |   1 |
+-----+---------+---------+-----+

Three surprising things happened, and none raised an error. The text '17' was converted to the integer 17, because it looks like a number. The text 'abc' went into an INTEGER column untouched. And 'toolong', seven characters, fitted a VARCHAR(3) column, because SQLite ignores the length.

SQLite's remedy is the STRICT keyword after the closing parenthesis, which makes it enforce the declared types. In that mode it accepts only a small set of type names, such as INTEGER, REAL, TEXT and BLOB:

sql
CREATE TABLE strict_demo (n INTEGER, label TEXT) STRICT;
INSERT INTO strict_demo VALUES (1, 'ok');
sql
INSERT INTO strict_demo VALUES ('abc', 'x');
result
Error: cannot store TEXT value in INTEGER column strict_demo.n

The shell puts its own prefix in front of messages like this, for example Error near line 3:, and the wording after it is the part that matters. The wording of constraint errors is also specific to SQLite, and other databases word theirs differently.

MySQL, PostgreSQL and SQL Server would all refuse the loose inserts above in an ordinary table. So when you design a table, write the types as if they were enforced, because on the database you deploy to they will be.

Creating a table only if it is missing, or from a query

Scripts are often run twice. IF NOT EXISTS makes the second run harmless:

sql
CREATE TABLE IF NOT EXISTS members (id INTEGER);

Because the table exists, nothing happens and no error appears, and the original definition stays. Without those words, creating a table that already exists is an error, and SQLite words it as table members already exists.

You can also build a table from a query. This one, run against the sample company database, holds only the engineering staff:

sql
CREATE TABLE eng_staff AS
SELECT id, first_name, salary FROM employees WHERE department_id = 1;

SELECT * FROM eng_staff;
result
+----+------------+--------+
| id | first_name | salary |
+----+------------+--------+
|  1 | Meera      | 180000 |
|  2 | Arjun      | 125000 |
|  3 | Kavya      | 125000 |
|  4 | Rohan      |  82000 |
|  5 | Isha       |  78000 |
| 19 | Diya       |  60000 |
+----+------------+--------+

CREATE TABLE ... AS SELECT copies the data and the column names, but not the keys, constraints or indexes. SQL Server uses a different form for the same idea, SELECT ... INTO new_table.

Common mistakes

  • Storing money in a floating-point type. Use DECIMAL, or whole cents in an integer, and round only when you show the result.
  • Using text for everything. A phone number should stay text, because you never add phone numbers, but an age or a price should be numeric, or sorting will put '9' after '10'.
  • Trusting the declared type in SQLite. A typo such as '1O' with a letter O is stored without complaint. Use STRICT tables, or validate in your application.
  • A trailing comma after the last column. The final column definition must be followed directly by the closing parenthesis.
  • Choosing a huge VARCHAR just in case. Pick a limit that reflects the real data, because the limit documents the design.

Exercise

Create a table called books with an integer id primary key, a required title of up to 120 characters, a price that is an exact decimal with two decimal places, and a published_on date that may be empty. Insert one book without a publication date, then select all rows.

Show one possible answer
sql
CREATE TABLE books (
  id           INTEGER PRIMARY KEY,
  title        VARCHAR(120) NOT NULL,
  price        DECIMAL(8,2) NOT NULL,
  published_on DATE
);

INSERT INTO books (id, title, price) VALUES (1, 'Learning SQL by Doing', 450.00);

SELECT * FROM books;
result
+----+-----------------------+-------+--------------+
| id | title                 | price | published_on |
+----+-----------------------+-------+--------------+
|  1 | Learning SQL by Doing |   450 | NULL         |
+----+-----------------------+-------+--------------+

The column published_on is NULL because the insert did not provide it and the column allows empty values. SQLite shows the price as 450; a database with a true DECIMAL type shows 450.00.

Key points

  • CREATE TABLE names the table, then lists each column with a name and a data type, separated by commas.
  • Pick the type that matches the data: integers for counts, DECIMAL for money, VARCHAR or TEXT for words, DATE and TIMESTAMP for time, BOOLEAN (or the nearest equivalent) for yes or no.
  • Never store money in floating-point types. Binary fractions cannot hold values such as 0.1 exactly.
  • SQLite is loosely typed: a declared type is only a hint unless you create the table with STRICT. Most other databases enforce the type.

Check yourself

1. Which type is the safest choice for a price such as 199.50?
2. What does CREATE TABLE IF NOT EXISTS do when the table is already there?
3. In SQLite, what happens if you insert the text 'abc' into a column declared INTEGER in an ordinary (non-STRICT) table?
4. How many characters can a VARCHAR(30) column hold in a database that enforces the length?