Fundamentals of databases: INSERT

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)