Querying the Database

Once the database is populated with data, an agency can begin using the database to analyze TIM performance. This analysis is done through database queries. A database query is a request – basically a question the user asks the database – used to retrieve information from the database. This request is written in a script language. Different query languages exist for different types of databases; however, queries are usually constructed using SQL, a standardized query language for requesting information from a database. Standard SQL allows a script to run on almost all relational database management systems available with little to no modifications.

An example database script written in SQL is shown here:

select count(*) from Incident;

This simple script returns the total number of incidents in the database.

Another example database script is:

select count(*) from Incidents where parent_incident_id is null;

This script returns the number of primary incidents in the database.

Finally, the following database script asks the database to provide the percentage of secondary crashes in the database (as a percentage of all incidents in the database).

select 100*(select count(*) from incident i 
where i.parent_incident_id is not null) / (select count(*) from incident i);

Later in the guidance, database queries are provided to illustrate a number of ways to use the model database to answer questions that might be of interest to agencies regarding their TIM performance.