Modifying data, views, and SQL in applications
SQL also creates structures and changes data. Views package queries for reuse, and applications must run SQL safely and efficiently.
Data definition
Data definition language (DDL) statements create and change structure: CREATE TABLE products (id INTEGER PRIMARY KEY, name VARCHAR(100) NOT NULL, price NUMERIC(10, 2) CHECK (price >= 0), category_id INTEGER REFERENCES categories(id)). ALTER TABLE adds or changes columns and constraints; DROP TABLE removes a table and its data.
Inserting, updating, and deleting
Data manipulation language (DML) changes rows. INSERT INTO products (name, price) VALUES ('Lamp', 24.99) adds a row; INSERT ... SELECT copies query results. UPDATE products SET price = price * 1.05 WHERE category_id = 3 changes matching rows. DELETE FROM products WHERE discontinued = TRUE removes rows. Omitting WHERE affects every row, a costly and common mistake.
Upserts and returning
An upsert inserts a row or updates it if a key already exists: INSERT ... ON CONFLICT (id) DO UPDATE in PostgreSQL and SQLite, or MERGE in standard SQL and SQL Server. PostgreSQL's RETURNING clause returns values from changed rows, such as a newly generated ID, avoiding a second query.
Views
A view is a named, stored query that behaves like a virtual table: CREATE VIEW active_customers AS SELECT id, name, email FROM customers WHERE status = 'active'. Views simplify complex queries, hide columns users should not see, and provide logical data independence, since the underlying tables can change while the view's definition is adjusted.
Materialised views
A materialised view stores its query's results physically, so reads are fast, but it must be refreshed when the underlying data changes. They suit expensive reports and dashboards, such as daily sales summaries, where slightly stale data is acceptable. Simple views can often be updated through, but views with joins or aggregates usually cannot.
Access control
SQL's data control language manages permissions: GRANT SELECT ON orders TO analyst lets a role read a table, and REVOKE removes the privilege. Following least privilege, applications should connect with accounts that can do only what they need; a reporting tool needs no DELETE permission. Views combined with grants can expose only certain rows or columns.
SQL from application code
Applications send SQL through drivers and libraries. Never build queries by pasting user input into strings, which enables SQL injection; use parameterised queries, such as cursor.execute('SELECT * FROM users WHERE email = %s', (email,)), which send data separately from the command. Object-relational mappers (ORMs), such as SQLAlchemy, Django's ORM, and Hibernate, map tables to classes, but developers must still watch the SQL they generate.
The N+1 problem
A common ORM performance trap: loading 100 orders with one query, then lazily loading each order's customer with a separate query, makes 101 queries. Each round trip adds latency. Fetching related data together with a join, or batching with WHERE id IN (...), reduces this to one or two queries, often making pages many times faster.
Worked example
Raising prices by 10% for one category and seeing the result: BEGIN; UPDATE products SET price = ROUND(price * 1.10, 2) WHERE category_id = 7; SELECT name, price FROM products WHERE category_id = 7; COMMIT. Running the UPDATE inside a transaction lets you inspect the change and ROLLBACK if something looks wrong, such as accidentally matching every row.
Key takeaways
DDL statements such as CREATE TABLE, ALTER TABLE, and DROP TABLE define structure and constraints. DML statements INSERT, UPDATE, and DELETE change data; always check WHERE clauses. Upserts insert or update in one statement; RETURNING fetches changed values. Views are stored queries acting as virtual tables; materialised views store results and need refreshing. GRANT and REVOKE manage privileges under least privilege. Applications must use parameterised queries to prevent SQL injection; ORMs help but can cause the N+1 query problem, fixed with joins or batching.
Flashcard
Data definition language
Check your understanding
An ORM loads 50 blog posts, then runs one extra query per post to fetch its author. What is this called, and how is it fixed?