Difference between revisions of "LibreOffice-Suite-Base/C2/Create-queries-using-Design-View/English-timed"

From Script | Spoken-Tutorial
Jump to: navigation, search
Line 5: Line 5:
 
|-
 
|-
 
||00:00
 
||00:00
||Welcome to the Spoken tutorial on LibreOffice Base.
+
||Welcome to the '''Spoken tutorial''' on '''LibreOffice Base'''.
  
 
|-
 
|-
 
||00:04
 
||00:04
||In this tutorial, we will learn how to
+
||In this tutorial, we will learn how to:
  
 
|-
 
|-
 
||00:06
 
||00:06
||Create a query by using the Design View.
+
||* Create a '''query''' by using the '''Design View'''.
  
 
|-
 
|-
 
||00:10
 
||00:10
||Add tables to the Query Design window
+
||* Add tables to the '''Query Design''' window
  
 
|-
 
|-
 
||00:13
 
||00:13
||Select fields.
+
||* Select '''field'''s
Set up aliases,
+
* Set up '''aliase'''s
Set up sorting order
+
* Set up sorting order and
And provide search criteria for a query
+
* provide search criteria for a '''query'''.
  
 
|-
 
|-
 
||00:23
 
||00:23
||For this, let us consider our familiar Library database example.
+
||For this, let us consider our familiar 'Library' database example.
  
 
|-
 
|-
 
||00:29
 
||00:29
||In this Library database, we have stored information about books and members.
+
||In this 'Library' database, we have stored information about books and members.
  
 
|-
 
|-
 
||00:37
 
||00:37
||And, we also have a table to track the books issued to the members.
+
||And, we also have a '''table''' to track the books issued to the members.
  
 
|-
 
|-
 
||00:45
 
||00:45
||Now we will create a new query, to list all the books that have been issued to the members,
+
||Now we will create a new '''query''' to list all the books that have been issued to the members.
  
 
|-
 
|-
Line 48: Line 48:
 
|-
 
|-
 
||01:03
 
||01:03
||Lets open the Library database.
+
||Let's open the '''Library''' database.
  
 
|-
 
|-
 
||01:07
 
||01:07
||Let us click on the Queries icon on the left panel.
+
||Let us click on the '''Queries''' icon on the left panel.
  
 
|-
 
|-
 
||01:13
 
||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,
+
||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
 
||01:28
||And we also see a small popup window at the top, that says Add Table or Query.
+
||And we also see a small pop-up window at the top that says '''Add Table or Query'''.
  
 
|-
 
|-
Line 68: Line 68:
 
|-
 
|-
 
||01:46
 
||01:46
||And we will need all the three tables for our query to generate the history of the books issued to members.
+
||And we will need all the three tables for our query to generate the '''history''' of the books issued to members.
  
 
|-
 
|-
 
||01:57
 
||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 popup window.
+
||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
 
||02:11
||We will similarly add the BooksIssued table and the Members table <pause>
+
||We will similarly add the '''BooksIssued''' table and the '''Members table'''. <pause>
  
 
|-
 
|-
 
||02:19
 
||02:19
||We now see that, the three tables have appeared in the background query design window.
+
||We now see that the three tables have appeared in the background query design window.
  
 
|-
 
|-
 
||02:26
 
||02:26
||Let us now ‘Close the popup window.
+
||Let us now close the pop-up window.
  
 
|-
 
|-
 
||02:31
 
||02:31
||This brings the Query design window to the foreground.
+
||This brings the '''Query design window''' to the foreground.
  
 
|-
 
|-
 
||02:39
 
||02:39
||Notice that the three tables are in the top half of the window
+
||Notice that the three tables are in the top half of the window.
  
 
|-
 
|-
 
||02:46
 
||02:46
||Here let us introduce some space among these tables.
+
||Here, let us introduce some space among these tables.
  
 
|-
 
|-
 
||02:53
 
||02:53
||Let us click, drag and drop the Members table to the far right.
+
||Let us click, drag and drop the '''Members''' table to the far right.
  
 
|-
 
|-
 
||03:01
 
||03:01
||And then, click, drag and drop the BooksIssued table to the centre.
+
||And then click, drag and drop the '''BooksIssued''' table to the centre.
  
 
|-
 
|-
 
||03:11
 
||03:11
||Now we see lines linking these tables and these are the relationships that we had established earlier.
+
||Now we see lines linking these tables and these are the '''relationships''' that we had established earlier.
  
 
|-
 
|-
 
||03:23
 
||03:23
||We can double-click on the lines to see the relationship details.
+
||We can double-click on the lines to see the '''relationship''' details.
  
 
|-
 
|-
 
||03:30
 
||03:30
||For now, let us see the bottom half of the Query design window.
+
||For now, let us see the bottom half of the 'Query design' window.
  
 
|-
 
|-
 
||03:37
 
||03:37
||This area has several rows of cells. As we design the query, we will fill these up.
+
||This area has several rows of '''cell'''s. As we design the query, we will fill these up.
  
 
|-
 
|-
 
||03:48
 
||03:48
||First, we will check the Field column.
+
||First, we will check the '''Field''' column.
  
 
|-
 
|-
Line 132: Line 132:
 
|-
 
|-
 
||04:01
 
||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.
+
||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
 
||04:12
||Next is the Name field in the Members table.
+
||Next is the '''Name''' field in the '''Members''' table.
  
 
|-
 
|-
 
||04:17
 
||04:17
||And then the Issue Date field in the BooksIssued table.
+
||And then, the '''Issue Date''' field in the '''BooksIssued''' table.
  
 
|-
 
|-
 
||04:24
 
||04:24
||Next, the Return date,the actual return date
+
||Next, the '''Return date,the actual return date'''. And finally the '''checked in''' field.
And finally the checked in field.
+
  
 
|-
 
|-
Line 153: Line 152:
 
|-
 
|-
 
||04:44
 
||04:44
||Also the corresponding table names in the third row.
+
||Also the corresponding '''table''' names in the third row.
  
 
|-
 
|-
 
||04:50
 
||04:50
||Next, let us look at the ‘Alias’ in the second row.
+
||Next, let us look at the '''Alias''' in the second row.
  
 
|-
 
|-
Line 165: Line 164:
 
|-
 
|-
 
||05:04
 
||05:04
||So let us type in aliases as shown in the image.<pause>
+
||So, let us type in aliases as shown in the image. <pause>
  
 
|-
 
|-
Line 173: Line 172:
 
|-
 
|-
 
||05:15
 
||05:15
||Next, let us look at the Sort row.
+
||Next, let us look at the '''Sort''' row.
  
 
|-
 
|-
 
||05:21
 
||05:21
||We can specify the ordering of the result set here
+
||We can specify the ordering of the '''result set''' here.
  
 
|-
 
|-
 
||05:26
 
||05:26
||Since we need a history of the books issued, we will order it chronologically.
+
||Since we need a '''history''' of the books issued, we will order it chronologically.
  
 
|-
 
|-
 
||05:34
 
||05:34
||Meaning we will sort the result set by the Issue Date in ascending order.
+
||Meaning- we will sort the '''result set''' by the '''Issue Date''' in ascending order.
  
 
|-
 
|-
 
||05:43
 
||05:43
||For this, we will click on the empty cell in the Sort row, under the Issuedate field.
+
||For this, we will click on the empty '''cell''' in the '''Sort''' row, under the '''Issuedate''' field. And let us click on '''Ascending'''.
And let us click on ‘Ascending’.
+
  
 
|-
 
|-
 
||05:56
 
||05:56
||Okay, we will go to the next row - ‘Visible’
+
||Okay, we will go to the next row - '''‘Visible’'''.
  
 
|-
 
|-
 
||06:02
 
||06:02
||Here we can set the visibility of the fields we selected by checking or un-checking them.
+
||Here, we can set the visibility of the fields we selected by checking or un-checking them.
  
 
|-
 
|-
Line 206: Line 204:
 
|-
 
|-
 
||06:17
 
||06:17
||Next, we will go to the ‘Function’ row.
+
||Next, we will go to the '''‘Function’''' row. This is used to create complex queries. We will skip this for now
This is used to create complex queries.
+
We will skip this for now.
+
  
 
|-
 
|-
 
||06:27
 
||06:27
||and we will go to the ‘Criterion’ row.
+
||and we will go to the '''‘Criterion’''' row.
  
 
|-
 
|-
 
||06:32
 
||06:32
||This is where we can limit the result set to a simple or complex set of criteria.
+
||This is where we can limit the '''result set''' to a simple or complex set of criteria.
  
 
|-
 
|-
 
||06:40
 
||06:40
||For example, we can query for those books only, which were issued but have not been returned by members,
+
||For example, we can '''query''' for those books only which were issued but have not been returned by members.
  
 
|-
 
|-
 
||06:49
 
||06:49
||meaning, only those that are not checked in.
+
||Meaning- only those that are not checked-in.
  
 
|-
 
|-
 
||06:54
 
||06:54
||So let us click on the empty cell in this row, under the CheckedIn field
+
||So, let us click on the empty '''cell''' in this row, under the '''CheckedIn''' field and type in ‘Equals Zero’.
and type in ‘Equals Zero’.
+
  
 
|-
 
|-
 
||07:06
 
||07:06
||Thats it, let us run this query now.
+
||That's it, let us '''run''' this query now.
  
 
|-
 
|-

Revision as of 09:30, 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
  • 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 popup 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 co-ordinated 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