SQL CREATE TABLE, ALTER TABLE and DROP TABLE

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

CREATE TABLE names columns and gives them types and constraints. A primary key identifies records, NOT NULL requires presence, and additional constraints express valid relationships or values. CREATE TABLE does not add business records unless the dialect uses a special create-as-query form. Explicit column names and a deliberate key make later INSERT and SELECT statements easier to maintain.

ALTER TABLE changes a definition without necessarily replacing every row manually. Common tasks include adding a column, renaming a column or adjusting a constraint. Syntax and supported operations vary widely. Adding a nullable notes column is a simpler migration than adding a required field to an already populated table, because existing rows need a valid value for any new NOT NULL requirement. Plan a backfill and validation step when the new rule needs data that older rows do not have.

DROP TABLE removes both the definition and its rows. DELETE FROM table removes rows but leaves the definition available for new records. Dropping a table can be blocked by dependencies, such as foreign keys or views, or can remove dependent objects when a dialect-specific cascade option is used. Read the dependency impact rather than assuming a similarly named command has the same effect in every engine.

DDL transaction and locking behaviour matter in real migrations. PostgreSQL can roll back many table DDL changes; MySQL commonly commits around DDL and can rebuild a table for some alterations. SQL Server and SQLite have their own locking and transactional rules. A schema migration can block concurrent readers or writers even when its SQL is short. Test the migration on representative data, verify the application can read both old and new states during deployment if necessary, and keep a recovery plan suited to the actual engine.

The displayed simple CREATE and ADD forms work across the four engines. Column type changes, constraint alterations and renames differ. SQLite supports a narrower direct ALTER TABLE feature set than server engines and sometimes requires a carefully planned table rebuild. Do not apply MySQL MODIFY or PostgreSQL ALTER COLUMN TYPE syntax in another engine.

Check the engine-specific syntax and version requirements in SQLite ALTER TABLE, PostgreSQL ALTER TABLE.

Create a separate practice table with a required label, insert one record and read it back. Use a new table name so the earlier example remains inspectable.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

CREATE TABLE lesson_labels (id INTEGER PRIMARY KEY,label VARCHAR(40) NOT NULL);
INSERT INTO lesson_labels (id,label) VALUES (1,'Beginner');
SELECT id,label FROM lesson_labels;

id | label
1 | Beginner

The primary key gives the row a stable identity and NOT NULL requires a label to be present. The INSERT supplies both columns. The read verifies the creation and insert without requiring a database-specific metadata view or relying on client confirmation messages.

Inspect lesson_notes after ALTER to confirm the added field exists, then insert a second record while omitting extra_info and predict NULL in that optional column. On a separate disposable table, delete its only row and verify the object remains queryable. Drop that disposable object afterwards and confirm the subsequent SELECT fails because its definition is gone.