Fundamentals of databases: INSERT

From Wikibooks, open books for an open world
Jump to navigation Jump to search

PAPER 2 - ⇑ Fundamentals of databases ⇑

← UPDATE INSERT DELETE →


We might also want to add new things to our database, for example when we are adding new Criminal records or a new friendship link on Facebook. To add new records we use the INSERT command with values of the fields we want to insert:

INSERT INTO crooks
VALUES (1234,'Julie', 'female','12/12/1994','Little Maplestead',67)

Sometimes we might not want to insert all the fields, some of them might not be compulsory:

INSERT INTO crooks (ID, name, town) --specific fields to insert into
VALUES (999, 'Frederick', 'Shotley')
ID name gender DoB town numScars
1 Geoff male 12/05/1982 Hull 0
2 Jane female 05/08/1956 York 1
3 Keith male 07/02/1999 Snape 6
4 Oliver male 22/08/1976 Blaxhall 2
5 Kelly female 11/11/1911 East Ham 10
6 Marea female 14/07/1940 Wythenshawe 6
1234 Julie female 12/12/1994 Little Maplestead 67
999 Frederick Shotley


Exercise: INSERT statements

Using the above data set, write an INSERT statement to create the following person:

name: Gerry
town: Keele
gender: male
numScars: 9
ID: 12
DoB: 13/12/1987

Answer:

Be careful, you must insert the fields in the order they are specified in the table

INSERT INTO crooks
VALUES (12, 'Gerry', 'male', '13/12/1987', 'Keele', 9)

Alternatively you could use the field headings, but this is a little cumbersome:

INSERT INTO crooks (name, town, gender, numScars, ID, DoB)
VALUES ('Gerry', 'Keele', 'male', 9, 12, '13/12/1987')

Using the above data set, write an INSERT statement to create the following person:

name: Kelly-Ann
gender: female
numScars: 1

Answer:

INSERT INTO crooks (name, gender, numScars)
VALUES ('Kelly-Ann', 'female', 1)