Relational Databases for Archaeology Main Menu

Structured Query Language (SQL)

4
Data is only as useful as the questions you can ask of it.  One of the major strengths of relational databases is the Structured Query Language (SQL).  SQL enables the user to ask questions, ie to query, the database, using a set of standard terms.  Here is an example of what an SQL query looks like:

SELECT area_easting, area_northing, context_number FROM public.contexts WHERE area_easting > 100;

The keywords that guide the query are capitalized by convention and you will also recognize the fields and table names.  This query will return (will 'select') all of the rows 'from' the contexts table 'where' the context was from any area with area_easting identifier greater than 100.

The great strength for queries comes from joining multiple tables together using their existing relationships and running queries across the tables.  For example, you might want to know the average rim diameter of ceramics from all contexts that had dirt of a certain color.  You would write a query that joined your contexts and ceramics measurements tables together (using the 3 part key) and then you would filter it on the color of the contexts.  This would give you a list of all of the diameters.  You can even use SQL to do the math of averaging these diameters.

Although you can just start writing SQL code like the line seen above, it is easier to start using a graphical tool.  Libreoffice Base has such a tool built in, called Queries.  This option is between Tables and Forms in the left column of the main Base screen.  If you click in there, you will get the option to Create a Query in Design View.  In the following screencast, we setup and run a simple query that includes joins between the three tables in our many-to-many example.  You can run the query without any 'WHERE' criteria, but it is more interesting to add a check.  In this case, we add that we are looking for any sample with a relatively high number of objects.

Exercise: Write Queries

For the first step of this exercise, duplicate the query we setup above for samples and containers.

For the second step, design any query that includes at least two related tables.  Select some sort of criteria to search for and run the query.  Here are a few potential queries to get you thinking, you need not implement one of these, you can think of your own:  a)  a query that lists all lithic stone tools from contexts excavated by a particular excavator.  b) ceramic sherds of a certain surface color that are all contained in the same storage container

Note that you may need to increase the amount of dummy data in your tables to get interesting results.

This page has paths:

  1. Relational Databases for Archaeology Peter Cobb

This page references: