LibreOffice-Suite-Base/C2/Create-queries-using-Query-Wizard/English
| Visual Cue | Narration |
|---|---|
| Show Slide Number 1
Spoken tutorial on LibreOffice Base: Create Queries using Wizard Learning Objectives
|
Welcome to the Spoken tutorial on LibreOffice Base.
In this tutorial, we will learn how to create simple queries using the Query wizard Select fields Set the sorting order of the fields And provide search criteria or conditions for a query |
| Show Slide Number 2
What is a query? Retrieve specific information from a database. Data can be returned based on given criteria Our example Library database has information on books and members Create Simple Queries Example of a Simple Query: List all members of the Library along with their phone numbers |
Let us first learn what a query is.
A Query can be used to get specific information from a database. In other words, we can "query" the database for some data that matches a given criteria. For example, let us consider our familiar Library database example. We have stored information about books and members in our Library database. Now we can query the Library database for all the members of the Library. Or we can query the database for all the books that are not in the Library. Let us see how we can create a simple query using Base. We will list all the members of the Library along with their phone numbers. |
| In the Base main window,
Click on 'Queries' on the left panel, Point cursor over the three options and click on 'Use Wizard to Create Query' |
We are in the Library database. You probably know how to open this by now.
Let us click on the Queries icon on the left panel. On the right panel, we see three options. Since we are creating a simple query first, we will choose an easy and a fast method. And that is by using a Query Wizard. For creating complex queries, Base provides us with very handy options such as 'Create Query in Design View' and 'Create Query in SQL view', which we will learn about later. For now, let us click on 'Use Wizard to Create Query'. |
| In the popup window,
Point mouse over the 8 steps on the left. |
Now, we see a popup window that says Query Wizard on the top.
On the left, we see 8 steps that we will go through. |
| Show cursor movement as narration goes forward
Click on Tables: Members in the tables drop down box. Double Click on the 'Name' field in the list on the left Double Click on 'Phone' on the left Click on the Next button |
We are in Step 1 - 'Field Selection'.
On the right side, we see a drop down box underneath the label Tables. This is where we will select the source of the data that we need from this query. Since our example query is about getting a list of all the members of the Library, we will click on the Tables: Members from the drop down box. Now, let us double click on the 'Name' field in the Available fields list on the left and move it to the list box on the right. Next, let us click on the 'Phone' field on the left and move it to the right. Note that to move all the fields to the right we can use the double arrow button that points to the right. Now let us click on the Next button at the bottom. |
| Click on the top most drop down box,
And click on Members.Name. Click on the Ascending option. Click on Next button. |
Now we are in Step 2 - Sorting Order.
Since the result of our query is a list of members and their phone numbers, we can leave this as is, Or we can order the list by member names. The Base Wizard, allows us to sort upto 4 fields in the result list at a time. For now, let us click on the top most drop down box, And click on Members.Name. We can also choose if we want to sort the names in ascending or descending order. Let us click on the Ascending option. And proceed to the next step. |
| Click on the Fields drop down box and then click on Members.Name.
And then, click on the Condition drop down box Point at the various options here, then click on 'Like'. Now in the Value text box, let us type in 'R%' Delete R% from the value text box, we dont need it now. Click on Next button. |
Step 3 - Search Conditions.
This step will help if we want to limit our result set to some conditions. For example, we can limit the result set to only those members, whose name starts with the alphabet R. For this, we will click on the Fields drop down box and then click on the Members.Name. Now, click on the Condition drop down box. Notice the various conditions here; <pause> Let us click on 'Like'. In the Value text box, let us type in ‘capital R’ and a ‘percentage symbol’. This is how we can introduce simple and complex conditions into our query. Let us now delete the 'R%' from the value text box to list all the members and click on the Next button. |
| Type in Member Name against Members.Name
and type in Phone Number against Members.Phone. Click on Next button. |
Note that we have skipped to Step 7.
This is because we are creating a simple query from a single table. And our query will return details and not summaries. Summary queries show data from aggregate functions and by grouping. Some examples are count of all the members, or sum of the prices of all the books. We will learn about these later. Okay, now here, let us set aliases; Meaning, let us provide friendly and descriptive labels or headers in the resulting list. So the 'Name' field can have an alias as 'Member Name' and the 'Phone' field can have an alias as 'Phone Number'. So let us type in these new aliases in the two text boxes and click on the Next button. |
| Type in 'List of all members and their phone numbers' against the 'Name of the Query label.
Click on the 'Display Query' option on the top right side. Click on the Finish button. |
Now we are in Step 8 - the final step.
Here let us give a nice descriptive name to our simple query. Let us type in 'List of all members and their phone numbers' against the 'Name of the Query’ label. Now notice that we see an overview of our choices in the wizard. And how do we want to proceed from here? Let us click on the 'Display Query' option on the top right side and click on the Finish button. |
| Show cursor movement as narration goes forward | The wizard window has closed and there is a new window whose title says, 'List of all members and their phone numbers'.
Notice that we see all the four members that we originally entered in the Members table along with their phone numbers. Also, we see that, this list is arranged alphabetically in ascending order. So this is our first simple query. |
| Show Slide Number 3
Assignment: Retrieve an alphabetical list of all the Books in the library. Query needs to be named ‘List of all books in the Library’ |
Here is an assignment.
Create a query that will list all the Books in ascending order. Include all fields. Name it as ‘List of all books in the Library’. |
| Show Slide Number 4
Summary
|
This brings us to the end of this tutorial on Creating Queries using Wizard in LibreOffice Base
To summarize, we learned how to : Create simple queries using the Query wizard Select fields Set the sorting order of the fields And provide search criteria or conditions for a query. |
| Acknowledgement Slide | Spoken Tutorial Project is a part of the Talk to a Teacher project, supported by the National Mission on Education through ICT, MHRD, Government of India. This project is co-ordinated by http://spoken-tutorial.org. More information on the same is available at the following link http://spoken-tutorial.org/NMEICT-Intro. This script has been contributed by Priya Suresh, DesiCrew Solutions Pvt Ltd. Thanks for joining. |
