Introduction to Database Programming with SQL
SQL (Structured Query Language) is the standard language for interacting with relational databases. Beyond simply retrieving data with SELECT, SQL provides powerful commands for modifying data, performing calculations, creating views, and managing transactions.
This subtopic covers four key areas of database programming:
- Updating data , inserting, modifying, and deleting records
- Aggregate functions , performing calculations across sets of rows
- Database views , creating virtual or stored representations of query results
- Transactions , ensuring data integrity through ACID properties
Items 2, 3, and 4 above are HL only content. SL students are responsible for SQL data modification (INSERT, UPDATE, DELETE) and the performance implications of indexing.
Inserting New Records with INSERT INTO
INSERT INTO: An SQL statement used to add one or more new records to a table.
The basic syntax is:
INSERT INTO TableName (Column1, Column2, ...) VALUES (Value1, Value2, ...);
You must list the columns you are providing values for, and the values must match the listed columns in order.
Adding a new customer to the Customer table:
INSERT INTO Customer (CustomerID, CustomerEmail, CustomerAddress)
VALUES (1, 'john.doe@example.com', '123 Main St');
This inserts a single new row into the Customer table with the three specified field values.
When inserting data, ensure that all required fields (those marked as NOT NULL in the table schema) are provided. Leaving out a required field will cause an error and the insert will fail.
A common mistake is inserting a value for a primary key that already exists in the table. This violates the primary key constraint and will produce an error. Always verify that the ID you are inserting is unique.