Insert Statements

We have created our tables but they have no data. So we need to insert some records. We do this with an insert statement, of course.

Adding The First Record

INSERT INTO person (person_id, person_first, person_last)
VALUES ('X00001', 'Alex', 'Alexander');

This code inserts into the person table a record with the ID number 'X00001', a first name 'Alex', and a last name 'Alexander'. You can probably guess from the capitalization that INSERT INTO are keywords telling the database that we are going to insert some data and "person" is the name of the table we created. As mentioned before, SQL code is case insensitive but we should capitalize keywords to make the code easier to read.

After the table name is a list of the columns we are providing data for. SQL was written this way to allow us to leave some columns null. If we did not want to include a first name we could just leave it off the list. Also, if we are providing Be aware that some columns can be marked as required, but that is a topic for a later time. We are just setting up some friendly elementary tables to play with.

After the column listing is the keyword VALUES. Following that keyword is a list of values to be inserted. It is also enclosed in parenthesis. The values are all enclosed in single quotes. This tells the database they are string literals. You must use single quotes and not double quotes. Double quotes are used for column names, not values.

The entire INSERT statement is terminated with a semicolon just like all previouse statements.

Add More Records

Now we are going to add a whole buncha records and it is going to be easy.

INSERT INTO person (person_id, person_first, person_last)
VALUES
('X00002', 'Bob', 'Baker'),
('X00003', 'Charlene', 'Crystal'),
('X00004', 'Dolly', 'Donaldson'),
('X00005', 'Edgar', 'East'),
('X00006', 'Freddy', 'Freaky'),
('X00007', 'Grant', 'Gradenow'),
('X00008', 'Helen', 'Helene'),
('X00009', 'Isabella', 'Inanova'),
('X00010', 'John', 'Jacobson');

This code is basically the same as the one record version. The only difference is that we include multiple records. Each record is enclosed in parenthesis, separated by commas, and (as usual) terminated with a semicolon.

Creating Awards Data

Let's dive right in to creating some award data. We will have one row per student per award. We need to ensure each award has a student ID or we won't know who the award is for. Also we need to make sure the award's ID is the same as one of the IDs from the student table or, again, we won't know who the award is for. We can make rules that fix these problems, but not right now. We are going to just be careful.

INSERT INTO award
	(award_id,
	award_fund,
	award_offered,
	award_accepted,
	award_disbursed)
VALUES
('X00001', 'PELL', 100, 100, 100),
('X00001', 'SEOG', 101, 101, 101),
('X00001', 'SUB', 102, 202, 0),
('X00001', 'UNSUB', 103, 2002, 0),

('X00002', 'PELL', 200, 0, 0),
('X00002', 'SUB', 202, 2202, 0),
('X00002', 'UNSUB', 203, 2333, 0),

('X00003', 'PELL', 3000, 300, 300),

('X00004', 'SUB', 402, 4202, 0),
('X00004', 'UNSUB', 400, 4, 0),

('X00005', 'PELL', 555, 500, 500),

('X00008', 'PELL', 88, 0, 0),
('X00008', 'SEOG', 80, 80, 80);

Note that I have changed to formatting a little. After the table name is the list of columns to be given data. Instead of putting all of them on one line, I put them each on a separate line. I also put some blank lines between awards for each student. This doesn't matter to the database. It will evaluate the code without caring about extra whitespace characters. If you trace the lines character by character you will see the code is fundamentally the same as the single record example (except with multiple records).

Inserting Null Values

Now we are going to add a little more data. This time we are only supplying the ID, fund, and offered amount.

INSERT INTO award (award_id, award_fund, award_offered, award_accepted) VALUES ('X00002', 'SEOG', 21, 20),
INSERT INTO award (award_id, award_fund, award_offered) VALUES ('X00009', 'SEOG', 90);
INSERT INTO award (award_id, award_fund, award_offered) VALUES ('X00010', 'PELL', 100);
INSERT INTO award (award_id, award_fund, award_offered) VALUES ('X00010', 'SEOG', 110);

For these three records we did not supply the accepted nor disbursed amounts. When the records are created they will be null values. They will not be zero. They just will not exist.

Next...

We finally have some data to pull back out of our database. We are going to learn about SELECT statements (or queries)