SQL CREATE TABLE and Data Types

SQL CREATE TABLE creates a new table, defining its columns and their data types. Good table design is the foundation of a fast, reliable database.

CREATE TABLE Syntax

Give the table a name, then list each column with its type. Our example creates a Suppliers table and selects from it to prove it exists.

CREATE TABLE Suppliers (
  SupplierID   INTEGER PRIMARY KEY,
  SupplierName TEXT NOT NULL,
  Country      TEXT,
  Rating       REAL
);

INSERT INTO Suppliers VALUES (1, 'Indus Farms', 'Pakistan', 4.6);
SELECT * FROM Suppliers;
SupplierID | SupplierName | Country  | Rating
-----------+--------------+----------+-------
1          | Indus Farms  | Pakistan | 4.6
(1 row)

Common SQL Data Types

  • INT / INTEGER: whole numbers
  • DECIMAL(p,s) / NUMERIC: exact numbers such as money, e.g. DECIMAL(10,2)
  • FLOAT / REAL: approximate decimals
  • VARCHAR(n): variable-length text up to n characters; TEXT for long text
  • DATE, TIME, DATETIME / TIMESTAMP: dates and times
  • BOOLEAN: true/false (stored as 0/1 in MySQL and SQLite)
  • SQLite uses flexible "type affinity", so it accepts these names but stores data in a few storage classes

Create a Table from a Query

CREATE TABLE ... AS SELECT copies the result of a query into a new table. SQL Server uses SELECT ... INTO NewTable instead.

CREATE TABLE UKCustomers AS
SELECT CustomerID, CustomerName, City
FROM Customers
WHERE Country = 'UK';

SELECT * FROM UKCustomers;
CustomerID | CustomerName | City
-----------+--------------+-----------
3          | Cedar Market | London
6          | Fresh Mart   | Manchester
(2 rows)

ALTER TABLE

ALTER TABLE changes an existing table. Adding a column is supported everywhere; new columns are NULL for existing rows unless you give a DEFAULT.

ALTER TABLE Products ADD COLUMN Discount REAL DEFAULT 0;

SELECT ProductName, Price, Discount
FROM Products
LIMIT 3;
ProductName  | Price | Discount
-------------+-------+---------
Basmati Rice | 12.5  | 0
Green Tea    | 6     | 0
Olive Oil    | 18.75 | 0
(3 rows)

DROP TABLE

DROP TABLE deletes a table and all its data permanently. Use DROP TABLE IF EXISTS to avoid an error when the table might not exist. TRUNCATE TABLE (MySQL, SQL Server, PostgreSQL) removes all rows but keeps the table.

DROP TABLE IF EXISTS UKCustomers;

Frequently asked questions

What is the difference between VARCHAR and TEXT?

VARCHAR(n) has a maximum length and can usually be indexed fully; TEXT is for long content. In PostgreSQL and SQLite they perform almost the same.

Which data type should I use for money?

Use DECIMAL(10,2) or similar exact types. FLOAT and REAL are approximate and can cause rounding errors in financial sums.

What is the difference between DROP, DELETE and TRUNCATE?

DELETE removes chosen rows, TRUNCATE quickly removes all rows but keeps the table, and DROP removes the table itself including its structure.