Relational Databases Today we will look at Different

Relational Databases Today we will look at: • Different ways of searching a database • Creating queries • Aggregate Queries • More complex queries involving different types of relationships

Query By Example • Access and Base allow us to use filters like those in spreadsheet applications

Query By Example • We can also create queries to help us search • We first select the table we want to use • And then which fields we want to see in the results table • We can also sort results • Finally, we enter the criteria used to select the results

Complex Queries • With a relational database we can also do queries across multiple tables • Usually they need to be linked, but you can do this in the query itself • This query shows all of the students in alphabetical order, together with the name of their tutor.

Aggregate Queries • Aggregate queries are ones that perform calculations across groups of records – e. g. counting them, or working out totals or averages • This query shows how many students there are in each year • The first step is to group the records, e. g. into years, and then the function works on a group at a time

Relationship Types • By default, queries only show rows where there are records in each table • In this example, only students that have a transport type entered will appear in the results • Can we show the students who haven’t had a transport type entered?

Relationship Types • We can change the type of the relationship to show those missing records • We can choose the table from which we want to see all records • We can also use the criteria Is Null (or Is Empty in Libre. Office) to find blank entries