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

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