LibreOffice-Suite-Base/C2/Create-queries-using-Design-View/English-timed

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