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

From Script | Spoken-Tutorial
Jump to: navigation, search
 
(2 intermediate revisions by 2 users not shown)
Line 13: Line 13:
 
|-
 
|-
 
||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 '''field'''s
+
||Select '''fields''', Set up '''aliases'''
* Set up '''aliase'''s
+
 
* Set up sorting order and
+
Set up sorting order and provide search criteria for a '''query'''.
* provide search criteria for a '''query'''.
+
  
 
|-
 
|-
Line 228: Line 227:
 
|-
 
|-
 
||07:06
 
||07:06
||That's it, let us '''run''' this query now.
+
||That's it. Let us '''run''' this query now.
  
 
|-
 
|-
 
||07:10
 
||07:10
||We can use the keyboard shortcut F5, or click on the Edit menu at the top of the window,
+
||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.
+
and then click on '''Run Query''' at the bottom.
  
 
|-
 
|-
Line 245: Line 244:
 
|-
 
|-
 
||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 252:
 
|-
 
|-
 
||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 264:
 
|-
 
|-
 
||08:23
 
||08:23
||Next, let us save this query, by pressing Control S. This opens a small popup window.
+
||Next, let us '''save''' this query by pressing '''Control, S'''. This opens a small pop-up window.
  
 
|-
 
|-
Line 278: Line 276:
 
|-
 
|-
 
||08:46
 
||08:46
||and then click on Ok button. and close this window.
+
||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 there, we have successfully created a query using the Design View
+
||So, there we have successfully created a query using the '''Design View'''.
  
 
|-
 
|-
Line 294: Line 292:
 
|-
 
|-
 
||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 Queries in Design View in LibreOffice Base
+
||This brings us to the end of this tutorial on '''Creating queries in Design View''', in '''LibreOffice Base'''.
  
 
|-
 
|-
Line 307: Line 304:
 
|-
 
|-
 
||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, Select fields
Add tables to the Query Design window
+
Select fields
+
  
 
|-
 
|-
 
||09:41
 
||09:41
||Set up aliases,
+
||Set up aliases, Set up sorting order And provide search criteria for a query.
Set up sorting order
+
And provide search criteria for a query.
+
  
 
|-
 
|-
 
||09:49
 
||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
+
||'''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, and this is Soundharya DesiCrew solutions, signing off. Thanks for joining.
+
||This script has been contributed by Priya Suresh, DesiCrew Solutions and this is Soundharya, DesiCrew solutions, signing off. Thanks for joining.
 
+
 
+
 
|-
 
|-
 
   
 
   
 
|}
 
|}

Latest revision as of 12:57, 23 March 2017

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