This page contains a few examples of database operations. We'll use a truly important topic: roller coasters. Here's a table of of some arbitrarily-selected ones. The speed is the max in MPH; the length and height are in feet.

Coasters:

Coaster_Name	Park_Name	City	State	Speed	Length	Height	Loops	Construction
Screamin' Eagle	Six Flags	St. Louis	MO	62	3872	110	0	wood
Mr Freeze	Six Flags	St. Louis	MO	70	1300	218	1	steel
The Boss	Six Flags	St. Louis	MO	66	5051	122	0	wood
Timber Wolf	Worlds of Fun	Kansas City	MO	53	4230	100	0	wood
Boomerang	Worlds of Fun	Kansas City	MO	47	935	116	3	steel
Tempesto	Busch Gardens	Williamsburg	VA	62	863	150	1	steel
Rebel Yell	King's Dominion	Richmond	VA	56	3368	85	0	wood
Georgia Cyclone	Six Flags	Atlanta	GA	50	2970	95	0	wood
Goliath	Six Flags	Atlanta	GA	70	4480	200	0	steel
Great Am Scream Machine	Six Flags	Atlanta	GA	57	3450	105	0	wood

These data come from the Roller Coaster Database web site.

And, here are a couple more boring tables giving the state names, abbreviations, and populations (2010 census).

States:

Abbrev	Name	Capital	Population
MS	Mississippi	Jackson	2,967,297
MO	Missouri	Jefferson City	5,988,927
NY	New York	Albany	19,378,102
LA	Louisiana	Baton Rouge	4,533,372
VA	Virginia	Richmond	8,001,024
GA	Georgia	Atlanta	9,687,653
MD	Maryland	Annapolis	5,773,552

States2:

Abbrev	Name	Capital	Population
LA	Louisiana	Baton Rouge	4,533,372
MN	Minnesota	St. Paul	5,303,925
MD	Maryland	Annapolis	5,773,552
NM	New Mexico	Santa Fe	2,059,179

Select

Select is probably the most obvious operation. It chooses rows (records, tuples) from a table. For instance, we can select from Coasters where Construction = 'wood' and length < 3500. This results in the following table:

Coaster_Name	Park_Name	City	State	Speed	Length	Height	Loops	Construction
Rebel Yell	King's Dominion	Richmond	VA	56	3368	85	0	wood
Georgia Cyclone	Six Flags	Atlanta	GA	50	2970	95	0	wood
Great Am Scream Machine	Six Flags	Atlanta	GA	57	3450	105	0	wood

Alternatively, we could select from States where Name ends with the letter 'a':

Abbrev	Name	Capital	Population
LA	Louisiana	Baton Rouge	4,533,372
VA	Virginia	Richmond	8,001,024
GA	Georgia	Atlanta	9,687,653

Project

The project operation selects columns (attributes) from a table (relation) to make a new one. So we might project Coaster_Name, Speed, and Length from Coasters:

Coaster_Name	Speed	Length
Screamin' Eagle	62	3872
Mr Freeze	70	1300
The Boss	66	5051
Timber Wolf	53	4230
Boomerang	47	935
Tempesto	62	863
Rebel Yell	56	3368
Georgia Cyclone	50	2970
Goliath	70	4480
Great Am Scream Machine	57	3450

We can also combine these operations. For instance, we can select on Coasters where Speed ≥ 60, then project on Coaster_Name. That yields this table:

Coaster_Name
Screamin' Eagle
Mr Freeze
The Boss
Tempesto
Goliath

Which is just a list of the “fast” coasters in the table, where fast means 60 or over.

Union

The union operation just combines the contents of tables. It only makes sense when they have the same structure. Tables are sets of tuples, and this is just the set union operation. For instance, States + States2 is:

Abbrev	Name	Capital	Population
MS	Mississippi	Jackson	2,967,297
MO	Missouri	Jefferson City	5,988,927
NY	New York	Albany	19,378,102
LA	Louisiana	Baton Rouge	4,533,372
VA	Virginia	Richmond	8,001,024
GA	Georgia	Atlanta	9,687,653
MD	Maryland	Annapolis	5,773,552
MN	Minnesota	St. Paul	5,303,925
NM	New Mexico	Santa Fe	2,059,179

Notice that the Louisiana and Maryland entries only appear once in a union, as is correct for set union.

Difference

The difference operation removes from the first table the tuples which are also in the second. States - States2 is:

Abbrev	Name	Capital	Population
MS	Mississippi	Jackson	2,967,297
MO	Missouri	Jefferson City	5,988,927
NY	New York	Albany	19,378,102
VA	Virginia	Richmond	8,001,024
GA	Georgia	Atlanta	9,687,653

Join

The join operation is used to combine relations based on a common attribute. Generally, a join is performed on tables with different properties. For instance, we can do a join on Coasters.State = States.Abbrev. That would give us: This associates with each coaster the data for the state it is located in. In practice, a join is usually followed by a project. Perhaps we might project the Coaster_Name and Population fields to find out the population of the state where each one is located:

Coaster_Name	Population
Screamin' Eagle	5,988,927
Mr Freeze	5,988,927
The Boss	5,988,927
Timber Wolf	5,988,927
Boomerang	5,988,927
Tempesto	8,001,024
Rebel Yell	8,001,024
Georgia Cyclone	9,687,653
Goliath	9,687,653
Great Am Scream Machine	9,687,653

Perhaps a more interesting example would be to follow the same join with a project on Park_Name, State, and Population. That will give us:

Park_Name	State	Population
Six Flags	MO	5,988,927
Worlds of Fun	MO	5,988,927
Busch Gardens	VA	8,001,024
King's Dominion	VA	8,001,024
Six Flags	GA	9,687,653

This result has fewer tuples than the join because some duplicates are discarded. Again, we are treating database tables as sets.

If we're interested knowing which coasters are located in state capitals (there must be some deep sociological significance to this), we could join States and Coasters for Coasters.State = States.Abbrev, then select for Coasters.City = States.Capital. That gives:

You might then project the interesting fields.

Product

The product operation simply produces all combinations of records from each database. The product of Coasters and States2 contains 10 × 4 = 40 records. Here are some of them:

The join operation can be represented as a product followed by a select, but join is often listed as an operation in its own right. The SQL select operation can also perform join and project, and is frequently used to do all three at once.