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

From Script | Spoken-Tutorial
Jump to: navigation, search
Line 4: Line 4:
 
|-
 
|-
 
||00:02
 
||00:02
−
||Welcome to the Spoken tutorial on LibreOffice Base.
+
||Welcome to the '''Spoken tutorial''' on '''LibreOffice Base'''.
  
 
|-
 
|-
 
||00:06
 
||00:06
−
||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
+
||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.
  
 
|-
 
|-
 
||00:24
 
||00:24
−
||Let us first learn what a query is.  
+
||Let us first learn what a '''query''' is.  
  
 
|-
 
|-
 
||00:29
 
||00:29
−
||A Query can be used to get specific information from a database.
+
||A query can be used to get specific information from a database.
  
 
|-
 
|-
Line 24: Line 28:
 
|-
 
|-
 
||00:48
 
||00:48
−
||For example, let us consider our familiar Library database example.  
+
||For example, let us consider our familiar 'Library' database example.  
  
 
|-
 
|-
Line 40: Line 44:
 
|-
 
|-
 
||01:21
 
||01:21
−
||Let us see how we can create a simple query using Base.  
+
||Let us see how we can create a simple query using '''Base'''.  
  
 
|-
 
|-
 
||01:30
 
||01:30
−
||our example is to list all the members of the Library along with their phone numbers.
+
||Our example is to list all the members of the 'Library' along with their phone numbers.
  
 
|-
 
|-
 
||01:44
 
||01:44
−
||We are in the Library database. You probably know how to open this by now.
+
||We are in the 'Library' database. You probably know how to open this by now.
  
 
|-
 
|-
 
||01:51
 
||01:51
−
||Let us click on the Queries icon on the left panel.  
+
||Let us click on the '''Queries''' icon on the left panel.  
  
 
|-
 
|-
Line 64: Line 68:
 
|-
 
|-
 
||02:11
 
||02:11
−
||And that is by using a Query Wizard.  
+
||And that is by using a '''Query Wizard'''.  
  
 
|-
 
|-
 
||02:17
 
||02:17
−
||For creating complex queries, Base provides us with very handy options such as 'Create Query in Design View'  
+
||For creating complex queries, '''Base''' provides us with very handy options such as 'Create Query in Design View'  
  
 
|-
 
|-
 
||02:28
 
||02:28
−
||and 'Create Query in SQL view', which we will learn about later.
+
||and 'Create Query in SQL view' which we will learn about later.
  
 
|-
 
|-
Line 80: Line 84:
 
|-
 
|-
 
||02:43
 
||02:43
−
||Now, we see a popup window that says Query Wizard on the top.  
+
||Now, we see a pop-up window that says '''Query Wizard''' on the top.  
  
 
|-
 
|-
Line 88: Line 92:
 
|-
 
|-
 
||02:57
 
||02:57
−
||We are in Step 1 - 'Field Selection'.
+
||We are in '''Step 1 - 'Field Selection''''.
  
 
|-
 
|-
 
||03:03
 
||03:03
−
||On the right side, we will see a drop down box underneath the label Tables.  
+
||On the right side, we will see a drop-down box underneath the label '''Tables'''.  
  
 
|-
 
|-
Line 100: Line 104:
 
|-
 
|-
 
||03:21
 
||03:21
−
||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.
+
||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.
  
 
|-
 
|-
 
||03:35
 
||03:35
−
||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.  
+
||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.  
  
 
|-
 
|-
 
||03:50
 
||03:50
−
||Next, let us click on the 'Phone' field on the left and move it to the right.
+
||Next, let us click on the '''Phone''' field on the left and move it to the right.
  
 
|-
 
|-
 
||04:00
 
||04:00
−
||Note that to move all the fields to the right we can use the double arrow button that points 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.
  
 
|-
 
|-
 
||04:09
 
||04:09
−
||Now let us click on the Next button at the bottom.
+
||Now, let us click on the '''Next''' button at the bottom.
  
 
|-
 
|-
 
||04:15
 
||04:15
−
||Now we are in Step 2 - Sorting Order.  
+
||Now we are in '''Step 2 - Sorting Order'''.  
  
 
|-
 
|-
 
||04:20
 
||04:20
−
||Since the result of our query is a list of members and their phone numbers, we can leave this as is,
+
||Since the result of our query is a list of members and their phone numbers, we can leave this as it is.
  
 
|-
 
|-
Line 132: Line 136:
 
|-
 
|-
 
||04:36
 
||04:36
−
||The Base Wizard, allows us to sort upto 4 fields in the result list at a time.
+
||The '''Base Wizard''' allows us to sort up to 4 '''fields''' in the result list at a time.
  
 
|-
 
|-
 
||04:45
 
||04:45
−
||For now, let us click on the top most drop down box,  
+
||For now, let us click on the top most drop-down box,  
  
 
|-
 
|-
 
||04:51
 
||04:51
−
||And click on Members.Name.  
+
||And click on '''Members.Name'''.  
  
 
|-
 
|-
Line 148: Line 152:
 
|-
 
|-
 
||05:03
 
||05:03
−
||Let us click on the Ascending option.
+
||Let us click on the '''Ascending''' option  
  
 
|-
 
|-
 
||05:07
 
||05:07
−
||And proceed to the next step.
+
||and proceed to the next step.
  
 
|-
 
|-
 
||05:11
 
||05:11
−
||Step 3 - Search Conditions.  
+
||'''Step 3 - Search Conditions'''.  
  
 
|-
 
|-
Line 164: Line 168:
 
|-
 
|-
 
||05:22
 
||05:22
−
||For example, we can limit the result set to only those members, whose name starts with the alphabet R.  
+
||For example, we can limit the result set to only those members whose name starts with the alphabet R.  
  
 
|-
 
|-
 
||05:34
 
||05:34
−
||For this, we will click on the Fields drop down box and then click on Members.Name.  
+
||For this, we will click on the '''Fields''' drop-down box and then click on '''Members.Name'''.  
  
 
|-
 
|-
 
||05:45
 
||05:45
−
||Now, click on the Condition drop down box.  
+
||Now, click on the '''Condition''' drop-down box.  
  
 
|-
 
|-
 
||05:51
 
||05:51
−
||Notice the various conditions here;
+
||Notice the various conditions here.
  
 
|-
 
|-
 
||05:58
 
||05:58
−
||Let us click on 'Like'.  
+
||Let us click on '''like'''.  
  
 
|-
 
|-
 
||06:02
 
||06:02
−
||In the Value text box, let us type in ‘capital R’ and a ‘percentage symbol’.  
+
||In the '''Value''' text box, let us type in ‘capital R’ and a ‘percentage symbol’.  
  
 
|-
 
|-
Line 192: Line 196:
 
|-
 
|-
 
||06:22
 
||06:22
−
||Let us now delete the 'R%' from the value text box to list all the members and click on the Next button.
+
||Let us now delete the 'R%' from the '''Value''' text-box to list all the members and click on the '''Next''' button.
  
 
|-
 
|-
Line 200: Line 204:
 
|-
 
|-
 
||06:43
 
||06:43
−
||This is because we are creating a simple query from a single table.
+
||This is because we are creating a simple query from a single '''table'''.
  
 
|-
 
|-
Line 212: Line 216:
 
|-
 
|-
 
||07:05
 
||07:05
−
||Some examples are count of all the members, or sum of the prices of all the books.  
+
||Some examples are count of all the members or sum of the prices of all the books.  
  
 
|-
 
|-
Line 220: Line 224:
 
|-
 
|-
 
||07:17
 
||07:17
−
||Okay, now here, let us set aliases;
+
||Okay, now here let us set aliases,
  
 
|-
 
|-
 
||07:23
 
||07:23
−
||Meaning, let us provide friendly and descriptive labels or headers in the resulting list.  
+
||meaning- let us provide friendly and descriptive labels or headers in the resulting list.  
  
 
|-
 
|-
Line 232: Line 236:
 
|-
 
|-
 
||07:46
 
||07:46
−
||So let us type in these new aliases in the two text boxes and click on the Next button.
+
||So, let us type in these new aliases in the two text-boxes and click on the '''Next''' button.
  
 
|-
 
|-
 
||07:57
 
||07:57
−
||Now we are in Step 8 - the final step.  
+
||Now, we are in '''Step 8 - the final step'''.  
  
 
|-
 
|-
Line 248: Line 252:
 
|-
 
|-
 
||08:20
 
||08:20
−
||Now notice that we see an overview of our choices in the wizard.  
+
||Now, notice that we see an overview of our choices in the wizard.  
  
 
|-
 
|-
Line 256: Line 260:
 
|-
 
|-
 
||08:31
 
||08:31
−
||Let us click on the 'Display Query' option on the top right side and click on the Finish button.
+
||Let us click on the 'Display Query' option on the top right side and click on the '''Finish''' button.
  
 
|-
 
|-
 
||08:41
 
||08:41
−
||The wizard window has closed and there is a new window whose title says, 'List of all members and their phone numbers'.
+
||The '''wizard window''' has closed and there is a new window whose title says- 'List of all members and their phone numbers'.
  
 
|-
 
|-
 
||08:52
 
||08:52
−
||Notice that we see all the four members that we originally entered in the Members table along with their phone numbers.
+
||Notice that we see all the four members that we originally entered in the '''Members''' table along with their phone numbers.
  
 
|-
 
|-
 
||09:04
 
||09:04
−
||Also, we see that, this list is arranged alphabetically in ascending order.  
+
||Also, we see that this list is arranged alphabetically in ascending order.  
  
 
|-
 
|-
 
||09:13
 
||09:13
−
||So this is our first simple query.
+
||So, this is our first simple query.
  
 
|-
 
|-
Line 292: Line 296:
 
|-
 
|-
 
||09:38
 
||09:38
−
||This brings us to the end of this tutorial on Creating Queries using Wizard in LibreOffice Base
+
||This brings us to the end of this tutorial on '''Creating Queries using Wizard in LibreOffice Base'''.
  
 
|-
 
|-
 
||09:45
 
||09:45
−
||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.
+
||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.
  
 
|-
 
|-
 
||10:00
 
||10:00
−
||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.  
+
||'''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.  
  
 
|-
 
|-
 
||10:12
 
||10:12
−
||This project is co-ordinated by http://spoken-tutorial.org.  
+
||This project is coordinated by http://spoken-tutorial.org.  
  
 
|-
 
|-
Line 312: Line 320:
 
|-
 
|-
 
||10:22
 
||10:22
−
||This script has been contributed by Priya Suresh, DesiCrew Solutiions.
+
||This script has been contributed by Priya Suresh, DesiCrew Solutiions. And this is soundharya, DesiCrew Solutions, signing off.  
−
 
+
Thanks for joining.
−
And this is soundharya, DesiCrew Solutions, signing off.  
+
−
 
+
−
Thanks for joining
+
  
 
|-
 
|-

Revision as of 21:42, 28 September 2015

Time Narration
00:02 Welcome to the Spoken tutorial on LibreOffice Base.
00:06 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.
00:24 Let us first learn what a query is.
00:29 A query can be used to get specific information from a database.
00:36 In other words, we can "query" the database for some data that matches a given criteria.
00:48 For example, let us consider our familiar 'Library' database example.
00:56 We have stored information about books and members in our Library database.
01:04 Now we can query the Library database for all the members of the Library.
01:12 Or we can query the database for all the books that are not in the Library.
01:21 Let us see how we can create a simple query using Base.
01:30 Our example is to list all the members of the 'Library' along with their phone numbers.
01:44 We are in the 'Library' database. You probably know how to open this by now.
01:51 Let us click on the Queries icon on the left panel.
01:57 On the right panel, we see three options.
02:03 Since we are creating a simple query first, we will choose an easy and fast method.
02:11 And that is by using a Query Wizard.
02:17 For creating complex queries, Base provides us with very handy options such as 'Create Query in Design View'
02:28 and 'Create Query in SQL view' which we will learn about later.
02:36 For now, let us click on 'Use Wizard to Create Query'.
02:43 Now, we see a pop-up window that says Query Wizard on the top.
02:50 On the left, we see 8 steps that we will go through.
02:57 We are in Step 1 - 'Field Selection'.
03:03 On the right side, we will see a drop-down box underneath the label Tables.
03:11 This is where we will select the source of the data that we need from this query.
03:21 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.
03:35 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.
03:50 Next, let us click on the Phone field on the left and move it to the right.
04:00 Note that to move all the fields to the right, we can use the double-arrow button that points to the right.
04:09 Now, let us click on the Next button at the bottom.
04:15 Now we are in Step 2 - Sorting Order.
04:20 Since the result of our query is a list of members and their phone numbers, we can leave this as it is.
04:30 Or we can order the list by member names.
04:36 The Base Wizard allows us to sort up to 4 fields in the result list at a time.
04:45 For now, let us click on the top most drop-down box,
04:51 And click on Members.Name.
04:55 We can also choose if we want to sort the names in ascending or descending order.
05:03 Let us click on the Ascending option
05:07 and proceed to the next step.
05:11 Step 3 - Search Conditions.
05:16 This step will help if we want to limit our result set to some conditions.
05:22 For example, we can limit the result set to only those members whose name starts with the alphabet R.
05:34 For this, we will click on the Fields drop-down box and then click on Members.Name.
05:45 Now, click on the Condition drop-down box.
05:51 Notice the various conditions here.
05:58 Let us click on like.
06:02 In the Value text box, let us type in ‘capital R’ and a ‘percentage symbol’.
06:13 This is how we can introduce simple and complex conditions into our query.
06:22 Let us now delete the 'R%' from the Value text-box to list all the members and click on the Next button.
06:37 Please note that we have skipped to Step 7.
06:43 This is because we are creating a simple query from a single table.
06:51 And our query will return details and not summaries.
06:57 Summary queries show data from aggregate functions and by grouping.
07:05 Some examples are count of all the members or sum of the prices of all the books.
07:13 We will learn about these later.
07:17 Okay, now here let us set aliases,
07:23 meaning- let us provide friendly and descriptive labels or headers in the resulting list.
07:32 So the 'Name' field can have an alias as 'Member Name' and the 'Phone' field can have an alias as 'Phone Number'.
07:46 So, let us type in these new aliases in the two text-boxes and click on the Next button.
07:57 Now, we are in Step 8 - the final step.
08:03 Here let us give a nice descriptive name to our simple query.
08:09 Let us type in 'List of all members and their phone numbers' against the 'Name of the Query’ label.
08:20 Now, notice that we see an overview of our choices in the wizard.
08:27 And how do we want to proceed from here?
08:31 Let us click on the 'Display Query' option on the top right side and click on the Finish button.
08:41 The wizard window has closed and there is a new window whose title says- 'List of all members and their phone numbers'.
08:52 Notice that we see all the four members that we originally entered in the Members table along with their phone numbers.
09:04 Also, we see that this list is arranged alphabetically in ascending order.
09:13 So, this is our first simple query.
09:18 Here is an assignment.
09:21 Create a query that will list all the Books in ascending order.
09:28 Include all fields.
09:31 Name it as ‘List of all books in the Library’.
09:38 This brings us to the end of this tutorial on Creating Queries using Wizard in LibreOffice Base.
09:45 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.
10:00 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.
10:12 This project is coordinated by http://spoken-tutorial.org.
10:17 More information on the same is available at the following link.
10:22 This script has been contributed by Priya Suresh, DesiCrew Solutiions. 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-Query-Wizard/English-timed&oldid=23783"