Difference between revisions of "LibreOffice-Suite-Base/C2/Create-queries-using-Design-View/English-timed"
From Script | Spoken-Tutorial
Sandhya.np14 (Talk | contribs) |
Sandhya.np14 (Talk | contribs) |
||
| Line 228: | Line 228: | ||
|- | |- | ||
||07:06 | ||07:06 | ||
| − | ||That's it | + | ||That's it. Let us '''run''' this query now. |
|- | |- | ||
||07:10 | ||07:10 | ||
| − | ||We can use the keyboard shortcut F5 | + | ||We can use the keyboard shortcut '''F5''' or click on the '''Edit''' menu at the top of the window |
| − | and then click on | + | and then click on '''Run Query''' at the bottom. |
|- | |- | ||
| Line 245: | Line 245: | ||
|- | |- | ||
||07:36 | ||07:36 | ||
| − | ||Notice that, we see a history of books issued to members and ordered by Issue Date. | + | ||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. |
| − | Also notice that none of the books are checked in. | + | |
|- | |- | ||
| Line 254: | Line 253: | ||
|- | |- | ||
||08:00 | ||08:00 | ||
| − | ||For example, let us remove the Checked In criterion. <pause> | + | ||For example, let us remove the '''Checked In''' criterion. <pause> |
|- | |- | ||
||08:07 | ||08:07 | ||
| − | ||Now let us run the query by pressing F5 again. | + | ||Now, let us '''run''' the query by pressing '''F5''' again. |
|- | |- | ||
| Line 266: | Line 265: | ||
|- | |- | ||
||08:23 | ||08:23 | ||
| − | ||Next, let us save this query | + | ||Next, let us '''save''' this query by pressing '''Control, S'''. This opens a small pop-up window. |
|- | |- | ||
| Line 278: | Line 277: | ||
|- | |- | ||
||08:46 | ||08:46 | ||
| − | ||and then click on | + | ||and then click on '''OK''' button. and close this window. |
|- | |- | ||
||08:52 | ||08:52 | ||
| − | ||We can open this saved query by double clicking on the query name in the main Base window. | + | ||We can open this saved query by double-clicking on the query name in the main '''Base''' window. |
|- | |- | ||
||09:01 | ||09:01 | ||
| − | ||So | + | ||So, there we have successfully created a query using the '''Design View'''. |
|- | |- | ||
| Line 294: | Line 293: | ||
|- | |- | ||
||09:12 | ||09:12 | ||
| − | ||Generate a list of the books issued to the member Nisha Sharma. | + | ||Generate a list of the books issued to the member Nisha Sharma. The list should be chronological by '''Issue date'''. |
| − | The list should be chronological by Issue date. | + | |
|- | |- | ||
||09:24 | ||09:24 | ||
| − | ||This brings us to the end of this tutorial on Creating | + | ||This brings us to the end of this tutorial on '''Creating queries in Design View''', in '''LibreOffice Base'''. |
|- | |- | ||
| Line 307: | Line 305: | ||
|- | |- | ||
||09:33 | ||09:33 | ||
| − | ||Create a query by using the Design View | + | ||* Create a query by using the Design View |
| − | Add tables to the Query Design window | + | * Add tables to the Query Design window |
| − | Select fields | + | * Select fields |
|- | |- | ||
||09:41 | ||09:41 | ||
| − | ||Set up aliases, | + | ||* Set up aliases, |
| − | Set up sorting order | + | * Set up sorting order And |
| − | And provide search criteria for a query. | + | * provide search criteria for a query. |
|- | |- | ||
||09:49 | ||09:49 | ||
| − | ||Spoken Tutorial | + | ||'''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 | ||10:10 | ||
| − | ||This script has been contributed by Priya Suresh, DesiCrew Solutions | + | ||This script has been contributed by Priya Suresh, DesiCrew Solutions and this is Soundharya, DesiCrew solutions, signing off. Thanks for joining. |
| − | + | ||
| − | + | ||
|- | |- | ||
|} | |} | ||
Revision as of 10:46, 29 September 2015
| 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
|
| 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
|
| 09:41 | * Set up aliases,
|
| 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. |
