Creating Tables and Changing Data

So far you have only read data. Analysts also create tables for their own work and occasionally correct data.

CREATE TABLE

A table definition lists each column, its type and its rules.

CREATE TABLE reviews (
    review_id   INTEGER PRIMARY KEY,
    product_id  INTEGER NOT NULL REFERENCES products(product_id),
    rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment     TEXT,
    review_date TEXT NOT NULL DEFAULT CURRENT_DATE
);

SELECT name FROM sqlite_master WHERE type = 'table';

Constraints

Constraints are rules the database enforces on every row.

Constraint	Rule
PRIMARY KEY	Identifies each row. Unique and not null
FOREIGN KEY / REFERENCES	The value must exist in another table
NOT NULL	A value is required
UNIQUE	No two rows may share the value
CHECK	The value must satisfy a condition
DEFAULT	The value used when none is given

Constraints stop bad data at the door, which is far cheaper than cleaning it later.

Data types

Common types are INTEGER, REAL or DECIMAL for numbers, TEXT or VARCHAR for text, DATE and TIMESTAMP for dates and times, and BOOLEAN. SQLite is flexible about types and stores dates as text; most other databases enforce types strictly.

INSERT

CREATE TABLE reviews (
    review_id  INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    rating     INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment    TEXT
);

INSERT INTO reviews (product_id, rating, comment)
VALUES (1, 5, 'Fast and light'),
       (1, 4, 'Good value'),
       (4, 2, 'Stopped working');

SELECT * FROM reviews;

Always list the columns. The statement then keeps working if someone adds a column to the table. Try changing a rating to 9 and run it again: the CHECK constraint rejects the row.

You can also insert the result of a query with INSERT INTO ... SELECT ....

UPDATE

UPDATE products
SET price = ROUND(price * 1.10, 2)
WHERE category = 'Accessories';

SELECT name, category, price FROM products;

An UPDATE without a WHERE clause changes every row in the table.

DELETE

DELETE FROM orders
WHERE status = 'cancelled';

SELECT order_id, status FROM orders;

A DELETE without WHERE removes every row. Before running an update or delete, run a SELECT with the same WHERE clause to see exactly which rows will be affected.

Transactions

A transaction groups statements so that they all succeed or all fail. COMMIT makes the changes permanent and ROLLBACK undoes them.

BEGIN;

UPDATE products SET price = 0;
SELECT name, price FROM products;

ROLLBACK;

SELECT name, price FROM products;

The first result shows every price at zero. After ROLLBACK, the prices are back. Wrapping risky changes in a transaction lets you check the result before you commit.

Views

A view is a saved query that you can use like a table. It stores the definition, not the data.

CREATE VIEW order_totals AS
SELECT oi.order_id,
       SUM(oi.quantity * p.price) AS order_total
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
GROUP BY oi.order_id;

SELECT * FROM order_totals ORDER BY order_total DESC LIMIT 3;

Views give a team one agreed definition of a calculation such as order total.

Changing and removing tables

ALTER TABLE reviews ADD COLUMN verified INTEGER; adds a column. DROP TABLE reviews; deletes the table and all its data.

Practice

Create a table suppliers with an id, a required name and a country that defaults to ‘Unknown’. Insert two rows and select them.