Beyond reading data, SQL defines ways to create, modify, and remove both rows and the structures that hold them. The CRUD operations—Create, Read, Update, Delete—correspond to INSERT, SELECT, UPDATE, and DELETE. INSERT INTO ... SELECT copies results from a query into a target table. UPDATE modifies existing rows matched by a condition, while DELETE removes them. The RETURNING clause available in PostgreSQL gives back the affected rows so an application can see what changed without an extra round trip.
Sometimes a write needs to insert or update depending on whether a row already exists—this is the role of UPSERT. PostgreSQL implements it via ON CONFLICT DO UPDATE, MySQL via ON DUPLICATE KEY UPDATE, and the SQL standard via MERGE, which can also perform conditional deletes based on a join condition. Schema changes are managed with CREATE TABLE (defining columns and constraints), ALTER TABLE (modifying structure), TRUNCATE TABLE (emptying all rows quickly without logging individual deletions), and DROP TABLE (removing the table entirely). DELETE removes specific rows slowly and transactionally, TRUNCATE empties the table fast and may reset identity counters, and DROP destroys the table and its data completely.
Constraints enforce business rules at the database level. NOT NULL disallows missing values, UNIQUE enforces distinctness (allowing NULLs unlike a primary key), CHECK evaluates a custom boolean expression such as price > 0, and DEFAULT supplies a fallback when no value is provided. SQL offers a rich type system: variable-length text types like TEXT or VARCHAR contrast with the fixed-length CHAR; integer sizes range from SMALLINT through BIGINT; DECIMAL and NUMERIC store exact fixed-precision numbers while FLOAT and DOUBLE are approximate; DATE, TIME, and TIMESTAMP handle temporal values, with TIMESTAMP WITH TIME ZONE storing moments converted to UTC. Many modern RDBMS also support JSON columns, UUID identifiers for distributed systems, ENUM types for restricted value sets, and specialized features like PostgreSQL's JSONB and range types.