Part B — Database Management System using LibreOffice Base

What is a Query?

A query is a sort of question asked from a database. It is used to search for desired records and retrieve the required data from one or more tables according to specified conditions.

Why are Queries Required?

As the number of records in a database increases, finding specific information by manually going through each record becomes difficult. A query allows us to retrieve the required information without individually checking every record.

What can be specified in a Query?

• The fields that we want to display.
• The criteria based on which records are to be filtered.
• The table or tables from which the information is required.

How is Query Result Displayed?

The result of a query is displayed in tabular form. The field names are displayed in columns and the records are displayed in rows.

Example — Student Database

Suppose a Student database contains a Result table. We want to display the names and marks of students who have scored less than 320 aggregate marks.

Three Ways

A query in LibreOffice Base can be created in three ways:

Example Used in the Textbook

The textbook uses the Sports Day database containing two tables:

• Events
• EventCategory

The example query displays Event Name and Winner for events whose CategoryID is C001.

Step 1 — Open the Database

Open the Sports Day database. In the Database Design window, click the Queries button present in the Database Pane on the left.

Step 2 — Start Query Wizard

In the Tasks Area, click Use Wizard to Create Query…. The Query Wizard will start.

Step 3 — Select the Table

The first step of the wizard is to select fields from the respective table. Select the Events table from the Tables list box. The corresponding fields are displayed in the Available Fields list box.

Step 4 — Select Fields

Select the required fields and click the right arrow ( > ) button to move them into Fields in the Query. For the textbook example, select:

• Event Name
• Winner
• CategoryID

Step 5 — Set Sorting Order

The next step is to select the sorting order. The result of a query can be displayed in ascending or descending order of a particular field. If no particular sorting order is required, click Next.

Step 6 — Set Search Criteria

The next step is to set the search condition or criterion on the basis of which records will be filtered. For the textbook example:

Field: Events.CategoryID
Condition: is equal to
Value: C001

Match All / Match Any

By default, Match all of the following is selected. If any one of the given conditions should be matched, select Match any of the following.

Step 7 — Numerical/Summarizing Steps

The wizard contains steps for tasks such as summarizing and performing numerical calculations. If there is no numeric field involved in the query, these steps are not required and can be skipped.

Step 8 — Give Alias

An Alias is an alternative, more readable name used as the column heading in the query output. For example, instead of displaying the field name Winner, we can give the alias Winner Name.

Step 9 — Query Overview

The last step of the Query Wizard displays the entire overview of the query. It shows the work performed during the previous steps in summarised form.

Information Shown in Overview

• Name of the Query
• Action after Wizard finishes
• Complete detail/summary of the query

By default, the query name may be displayed as Query_Events. The default action is Display Query. The Modify Query option can be selected if the query has to be edited in Design View.

Step 10 — Finish

Click the Finish button. The records satisfying CategoryID = C001 will be displayed.

Why Design View?

Design View is a more flexible method for creating a query. It can be used to create a query using a single table or multiple tables.

Example from the Textbook

The textbook creates a query to display records of the Athletics category using:

• Events table
• EventCategory table

Step 1 — Open Queries

Click the Queries icon in the Objects Pane of the Database Window.

Step 2 — Create Query in Design View

Click Create Query in Design View… in the Tasks Pane. The Query Design Window appears. The Add Table or Query dialog box is displayed.

Step 3 — Add Events Table

Select the Events table and click Add. Alternatively, double-click the Events table. The table is added to the Tables Pane.

Step 4 — Add EventCategory Table

Similarly, add the EventCategory table to the Tables Pane.

Step 5 — Close Dialog Box

Click the Close button in the Add Table or Query dialog box. Both tables are now visible in the Table Pane.

Step 6 — Select Fields

For the textbook example, select:

• EventName from Events
• Winner from Events
• CategoryName from EventCategory

The selected fields appear in the Design Grid in the lower part of the Query Design Window.

Visible Check Box

The Visible check box is selected by default. This means that the field will be displayed when the query is run. If a field should not be displayed in the output, deselect its Visible check box.

Step 7 — Alias Row

The Design Grid contains an Alias row. It is used to display a meaningful column heading instead of the original field name. Example:
Winner → Winner Name

Step 8 — Sort Row

By default, query data is not sorted. The Sort row is used to arrange records in ascending or descending order. For example, to display Event Name alphabetically, select Ascending under the Event Name column.

Step 9 — Run the Query

After designing the query, click the Run Query button on the toolbar. The shortcut key to run a query is:

Step 10 — Save the Query

Click the Save button. The Save As dialog box appears. By default, the query name may be Query1. Enter a different name if required and click OK.

Running a Saved Query Again

The saved query appears in the Objects Area of the Database Window. To run the query again, simply double-click the query name. The result will be displayed in a separate window.

Two Main Sections

The Query Design Window is divided into two sections.

Why Edit a Query?

After creating a query, its design can be modified. For example, a criterion can be added or changed to display different records.

Steps to Edit a Query

1. Click the Queries icon in the Database Window.
2. The list of created queries will appear in the Objects Area.
3. Right-click the required query.
4. Select Edit from the drop-down menu.
5. The Query Design Window will appear.

Example — Athletics Category

To display only records belonging to the Athletics category, type:

Applying Numerical Criteria

Suppose we want to display records where the Points scored are more than 10. Add the Points field to the Design Grid and enter:

Relational Operators

The Criterion row can use relational operators such as:

What is a Criterion?

A criterion is a condition specified in a query to filter the records according to the required value.

Examples

Multiple Conditions

The Criterion row can also be used to apply multiple conditions. The required conditions are entered in the appropriate fields of the query design.

Numerical Functions in Queries

A query can do more than simply display records. When numerical data is involved, mathematical functions can be used to calculate values.

The textbook mentions functions to find:

• Count
• Sum
• Minimum
• Maximum
• Average

Example — Average Points for Each Category

The textbook designs a query to display the average points for each category of events. The required tables are:

• Events
• EventCategory

Step 1 — Add Fields

Add:
CategoryName from EventCategory
Points from Events

Step 2 — Group CategoryName

Under CategoryName, click the down arrow in the Function row. Select Group. The Group option forms groups for each different data value in the CategoryName column.

Step 3 — Apply Average to Points

Under the Points column, select the Average function.

Step 4 — Run Query

Press F5 to run the query. The query result will show the average points for the categories.

Applying a Condition on Groups

A criterion can also be applied to groups. For example, to display the average of only the Athletics category, type Athletics in the Criterion row under Category Name.

MCQ Topics Directly Asked

• Meaning of Query
• Ways of designing a Query
• Flexibility of Design View
• Number of sections in Query Design Window
• What is NOT true about queries
• Shortcut key F5
• Numerical functions
• Moving fields using the > button
• Relational operators
• Add Table or Query dialog box
• Numerical/summarizing step

True / False Concepts

• A query can be run more than once.
• A query can be created from multiple tables.
• F5 is used to run a query.
• A query can contain numerical data.
• By default, query results are not sorted.
• A query can display the average value of a numerical field.
• Criterion can be applied to fields.
• Alias is an alternative name for a field.
• Visible check box is selected by default.
• A query created using a wizard can be edited in Design View.

Fill in the Blanks — Key Answers

Remember These Actions

• Queries icon → opens query-related options.
• Use Wizard to Create Query → starts Query Wizard.
• Create Query in Design View → opens Query Design Window.
• Add Table or Query → appears when Design View is opened for the first time.
• > → moves selected field into Fields in the Query.
• ∧ / ∨ → changes field order.
• Visible → controls whether the field appears in output.
• Alias → changes displayed column heading.
• Sort → arranges records.
• Criterion → filters records.
• F5 → runs the query.
• Save → saves the query for future use.
• Double-click saved query → runs the saved query again.