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:
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:
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;+----+-----------+-------------------+------------+---------+-----------+---------------------+
| 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.
PRAGMA table_info(members);+-----+------------+---------------+---------+------------+----+
| 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:
SELECT 0.1 + 0.2 AS total, 0.1 + 0.2 = 0.3 AS is_equal;+---------------------+----------+
| 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
BOOLEANtype that acceptsTRUEandFALSE. MySQL accepts the wordBOOLEANbut treats it as a small integer. SQL Server has noBOOLEANand usesBIT. SQLite stores1and0, as the output above shows. - Timestamps. In SQL Server the type named
TIMESTAMPis not a date and time at all. UseDATETIME2there. - Long text. PostgreSQL and MySQL have
TEXT. SQL Server prefersVARCHAR(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:
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;+-----+---------+---------+-----+
| 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:
CREATE TABLE strict_demo (n INTEGER, label TEXT) STRICT;
INSERT INTO strict_demo VALUES (1, 'ok');INSERT INTO strict_demo VALUES ('abc', 'x');Error: cannot store TEXT value in INTEGER column strict_demo.nThe 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:
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:
CREATE TABLE eng_staff AS
SELECT id, first_name, salary FROM employees WHERE department_id = 1;
SELECT * FROM eng_staff;+----+------------+--------+
| 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. UseSTRICTtables, 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
VARCHARjust 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
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;+----+-----------------------+-------+--------------+
| 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.