Database Maintenance

Using Data handling Software to maintain a Database

Database maintenance involves the following:

Using Data handling Software to interrogate a Database

Database interrogation involves using the database management software to query (search) the database for data. Remember the data stored in the database is not infomation until we apply meaning to it.

Examples reasons to query data:

The above examples shows that queries are a means of producing information from data. This information is used by the decision makers in organisations to plan strategies and tactics. databases usually allow users to create, save and then reuse queries.

Query design

Specifies which records to search for and the fields to display.

Simple query - looks for data in one filed only

e.g. all songs in a playlist by Led Zepplin

Complex query - looks for data in multiple fields

e.g. all songs in a playlist by Led Zepplin from the album Led Zepplin II

The example of a simple query below uses Microsoft Access database table and a query to find the names of all the songs that are classified as "Rock" ad have been played more than 10 times. This involves the follwoing steps:

The standard language for manipulating a database is called Structured Query Language (SQL). Each DSBMS supports a standard set of commands such as:

There are also SQL commands that can summarise data sets and to carry out mathematical operations on the relevant data types. In order to carry out a task on a database an instruction is written using one or more of the above statements. This instruction is called an SQL query. These can be fairly simple, or they can be extremely complicated.

Example of an SQL statement

SELECT * FROM MyDatabase.Names WHERE 'First_Name' = 'John';

The first keyword is 'SELECT' which means a set of records is going to be extracted from the database. The asterisk * means 'every field in the record'.

Then comes FROM which defines which parts of the database is to be used, in this case a single table called 'Names' from the database called 'MyDatabase'.

Then comes the very important WHERE clause. This defines a condition that causes only a sub-set of the table(s) to be extracted. In this case the field called 'First_Name' must contain the name 'John'.

In English, this says “Get me all the records from Names that have the first name John”.

Try out some of the activities at SQL fun & Games:  SQLZOO