LibreOffice-Suite-Base/C2/Create-queries-using-Design-View/English-timed

From Script | Spoken-Tutorial
Jump to: navigation, search
Time Narration
00:00 Welcome to the Spoken tutorial on LibreOffice Base.
00:04 In this tutorial, we will learn how to:
00:06 Create a query by using the Design View.
00:10 Add tables to the Query Design window
00:13 Select fields, Set up aliases

Set up sorting order and provide search criteria for a query.

00:23 For this, let us consider our familiar 'Library' database example.
00:29 In this 'Library' database, we have stored information about books and members.
00:37 And, we also have a table to track the books issued to the members.
00:45 Now we will create a new query to list all the books that have been issued to the members.
00:54 In other words, let us generate a history of books that have been issued to the members.
01:03 Let's open the Library database.
01:07 Let us click on the Queries icon on the left panel.
01:13 On the right panel, we will click on the ‘Create Query in Design view’. We now see a new window which is also called the Query Design window.
01:28 And we also see a small pop-up window at the top that says Add Table or Query.
01:39 Here is where we will define the source of the data for the query.
01:46 And we will need all the three tables for our query to generate the history of the books issued to members.
01:57 We will do this by clicking on the Books table in the list and then clicking on the Add button on the right in the pop-up window.
02:11 We will similarly add the BooksIssued table and the Members table. <pause>
02:19 We now see that the three tables have appeared in the background query design window.
02:26 Let us now close the pop-up window.
02:31 This brings the Query design window to the foreground.
02:39 Notice that the three tables are in the top half of the window.
02:46 Here, let us introduce some space among these tables.
02:53 Let us click, drag and drop the Members table to the far right.
03:01 And then click, drag and drop the BooksIssued table to the centre.
03:11 Now we see lines linking these tables and these are the relationships that we had established earlier.
03:23 We can double-click on the lines to see the relationship details.
03:30 For now, let us see the bottom half of the 'Query design' window.
03:37 This area has several rows of cells. As we design the query, we will fill these up.
03:48 First, we will check the Field column.
03:53 This is for specifying the fields we need to display in the result set.
04:01 To do this, we will first double-click on the Title field in the Books table in the upper half of the window.
04:12 Next is the Name field in the Members table.
04:17 And then, the Issue Date field in the BooksIssued table.
04:24 Next, the Return date,the actual return date. And finally the checked in field.
04:34 Notice these fields in the bottom half of the window in the first row.
04:44 Also the corresponding table names in the third row.
04:50 Next, let us look at the Alias in the second row.
04:57 This is where we can enter descriptive names for the selected fields.
05:04 So, let us type in aliases as shown in the image. <pause>
05:11 And we are done with the aliases.
05:15 Next, let us look at the Sort row.
05:21 We can specify the ordering of the result set here.
05:26 Since we need a history of the books issued, we will order it chronologically.
05:34 Meaning- we will sort the result set by the Issue Date in ascending order.
05:43 For this, we will click on the empty cell in the Sort row, under the Issuedate field. And let us click on Ascending.
05:56 Okay, we will go to the next row - ‘Visible’.
06:02 Here, we can set the visibility of the fields we selected by checking or un-checking them.
06:11 Notice that, by default, all of them are checked.
06:17 Next, we will go to the ‘Function’ row. This is used to create complex queries. We will skip this for now
06:27 and we will go to the ‘Criterion’ row.
06:32 This is where we can limit the result set to a simple or complex set of criteria.
06:40 For example, we can query for those books only which were issued but have not been returned by members.
06:49 Meaning- only those that are not checked-in.
06:54 So, let us click on the empty cell in this row, under the CheckedIn field and type in ‘Equals Zero’.
07:06 That's it. Let us run this query now.
07:10 We can use the keyboard shortcut F5 or click on the Edit menu at the top of the window

and then click on Run Query at the bottom.

07:27 Can you see some data in the upper half of the window?
07:32 These are the results of your query.
07:36 Notice that, we see a history of books issued to members and ordered by Issue Date. Also notice that none of the books are checked-in.
07:51 Now we can go to the query design area below and change it any way we want .
08:00 For example, let us remove the Checked In criterion. <pause>
08:07 Now, let us run the query by pressing F5 again.
08:15 This time we see a longer list of data returned from the query.
08:23 Next, let us save this query by pressing Control, S. This opens a small pop-up window.
08:34 Let us give a descriptive name to our query here.
08:38 Let us type ‘History of Books Issued to Members’
08:46 and then click on OK button. and close this window.
08:52 We can open this saved query by double-clicking on the query name in the main Base window.
09:01 So, there we have successfully created a query using the Design View.
09:09 Here is an assignment:
09:12 Generate a list of the books issued to the member Nisha Sharma. The list should be chronological by Issue date.
09:24 This brings us to the end of this tutorial on Creating queries in Design View, in LibreOffice Base.
09:31 To summarize, we learned how to:
09:33 Create a query by using the Design View, Add tables to the Query Design window, Select fields
09:41 Set up aliases, Set up sorting order And provide search criteria for a query.
09:49 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 coordinated by http://spoken-tutorial.org. More information on the same is available at the following link.
10:10 This script has been contributed by Priya Suresh, DesiCrew Solutions and this is Soundharya, DesiCrew solutions, signing off. Thanks for joining.

Contributors and Content Editors

Gaurav, Minal, PoojaMoolya, Sandhya.np14