SQL Command Families
SQL statements are often grouped conceptually into data definition, data manipulation, querying, and transaction control. CREATE, ALTER, and DROP define database objects; INSERT, UPDATE, and DELETE change rows; SELECT retrieves data; BEGIN, COMMIT, and ROLLBACK control transactions.
These categories help organize learning, but the most important skill is understanding what each statement does to data and schema. Be especially careful with destructive statements and run them against the intended database and environment.
Common SQL operations
-- Define structure
CREATE TABLE notes (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, body TEXT NOT NULL);
-- Insert and read
INSERT INTO notes (body) VALUES ('Learn PostgreSQL');
SELECT id, body FROM notes;
-- Change a row
UPDATE notes SET body = 'Practice PostgreSQL' WHERE id = 1;💡 The example creates a table, inserts a row, retrieves it, and updates it. In real applications, parameterized queries should be used to pass dynamic values safely.
Key Takeaways
- DDL defines schema; DML changes rows; SELECT retrieves data.
- Transaction statements control whether changes are committed.
- Review WHERE clauses carefully before UPDATE or DELETE.
Knowledge Check
Which statement retrieves rows without changing table data?