🎓 A1Learner
🔍

SQL Create Table

CREATE TABLE builds a new table from column names and types. The statement names the table first and then lists each column with its type.

Build the wishlists table

This block builds a table named wishlists. One insert and one select prove the table works.

CREATE TABLE wishlists (
  wishlist_id INTEGER,
  customer_id INTEGER,
  item TEXT
);
INSERT INTO wishlists VALUES (1, 2, 'Backpack');
SELECT wishlist_id, customer_id, item
FROM wishlists;

The three types in one table

This table holds one column of each type. INTEGER holds a whole number, REAL holds a decimal number, and TEXT holds text.

CREATE TABLE prices (
  entry_id INTEGER,
  amount REAL,
  label TEXT
);
INSERT INTO prices VALUES (1, 9.99, 'Mouse Pad');
SELECT entry_id, amount, label
FROM prices;

A new table starts empty

COUNT counts the rows in the new wishlists table. The count is zero because no insert has run yet.

CREATE TABLE wishlists (
  wishlist_id INTEGER,
  customer_id INTEGER,
  item TEXT
);
SELECT COUNT(*) AS row_count
FROM wishlists;

Example

CREATE TABLE wishlists (
  wishlist_id INTEGER,
  customer_id INTEGER,
  item TEXT
);
INSERT INTO wishlists VALUES (1, 2, 'Backpack');
INSERT INTO wishlists VALUES (2, 5, 'Desk Lamp');
SELECT wishlist_id, customer_id, item
FROM wishlists
ORDER BY wishlist_id;

Key points

  • CREATE TABLE: the statement that builds a new table
  • Each column gets a name and a type.
  • INTEGER: a type for a whole number
  • REAL: a type for a decimal number
  • TEXT: a type for text
  • A new table starts empty.

In this example, CREATE TABLE builds wishlists with three columns. The first two columns take whole numbers, and the item column takes text. Two inserts add two rows. The select reads both rows back, in wishlist_id order.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

Which statement builds a new table?