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

Retrieved from "https://script.spoken-tutorial.org/index.php?title=LibreOffice-Suite-Base/C2/Create-queries-using-Design-View/English-timed&oldid=23786"