LibreOffice-Suite-Base/C4/Indexes-Table-Filter-SQL-Command-window/English
| Visual Cue | Narration |
|---|---|
| Show Slide Number 1
Spoken tutorial on LibreOffice Base : Indexes, Table Filter, SQL Command window Learning Objectives
|
Welcome to the Spoken tutorial on LibreOffice Base.
In this tutorial, we will learn the following topics:
|
| Show Slide Number 2
What is an Index? An Index is a way to find and sort records within a database table faster. We can choose one field or multiple fields in a table on which the records need to be indexed. The Index stores the location of records based on the chosen field or fields To retrieve data, Base can move to the location of the data directly using the index. It is considerably faster than scanning through all of the records to find the data. The primary key of a table is automatically indexed. Example: Create an index on the Title column in the Books table within the example Library database. |
Let us first learn about Indexes.
What is an Index? An Index is a way to find and sort records within a database table faster. We can choose one field or multiple fields in a table on which the records need to be indexed. The Index stores the location of records based on the chosen field or fields. So to retrieve data, Base can move to the location of the data directly by using the index. And so it is considerably faster than scanning through all of the records to find the data. The primary key of a table is automatically indexed. Now let us create an index in our example Library database. We will create an index on the Title column in the Books table that will speed up searching on book titles. |
| Open Library database in Base, if not already open.
Click on the Tables list on the left panel And then right click and click on Edit on the ‘Books’ on the right panel. |
Let us first open our Library database, if not already opened.
And open the Books table in Edit mode. |
| Click the Tools menu and click on Index Design. | In the table design window, let us go to the Tools menu and choose Index Design. |
| In the popup window
Click on the left most icon ‘New Index’, to create index. Choose Title in the drop down under the Index field on the right. Choose Ascending in the dropdown right of this. Click on the third icon on the left, and click on save icon on the right of rename icon. |
In the Indexes window, notice that Base already has included the Primary Key as a unique Index.
Let us click on the left most icon, ‘New Index’, to create our index. And choose Title in the drop down list under the Index field on the right. We can also choose the Ascending or Descending order here and rename this index to ‘IDX_Title’ by clicking on the third icon on the left, and save it using the Save icon next to it. So there is our index on the title field. In this way we can create, edit, rename or delete indexes for tables using Base. |
| Show Slide Number 3
Assignment: Create an index on names in the Members table and call it ‘IDX_MemberName’ |
Here is an assignment for you:
Create an index on names in the Members table and call it ‘IDX_MemberName’ |
| Go to Base main window.
Click on Tools > Table Filter Check the ‘All Views’ and check the Books table. Click on the Ok button. Click on the View menu and then the Refresh Tables. Point to Books Again, when viewing this database from LibreOffice |
Next, let us see what a Table Filter is.
Table Filter feature allows us to hide tables in a Base database from other applications. As an example, let us hide all tables except the Books table in the Library database. Now, Table Filter is available under the Tools menu. Here, let us check ‘All Views’ and check the Books table. Meaning, we are marking only the Books table to be visible to other applications. Now let us click on the Ok button. Next click on the View menu and then on Refresh Tables. Notice that Books is the only table visible here. Also, when accessing this database from LibreOffice Writer or Calc, we will only see the Books table there. |
| Show Slide Number 4
Assignment: 1. Open LibreOffice Writer, access the Library database and check the tables available. 2. Bring back all the tables to visibility in Base. 3. Reopen LibreOffice Writer to check the tables. |
Here is another assignment:
1. Open LibreOffice Writer, access the Library database and check the tables available there. 2. Bring back all the tables to visibility in Base. 3. And reopen LibreOffice Writer to check the tables’ availability again. |
| Show Slide Number 5
SQL command window Accessed from Tools>SQL Use this window, to issue SQL statements to the database. Use Queries to execute SELECT statements. Can’t execute SQL statements which modify data and table structures or to create new tables inside Queries SQL command window helps to use such data manipulation and data definition statements or language. Examples of data manipulation language (DML) are: INSERT, UPDATE and DELETE data. And some examples of data definition language, (DDL) are: CREATE TABLE, DROP TABLE and ALTER statements. |
Finally, let us learn about the SQL command window.
The SQL command window is accessed by choosing SQL from the Tools menu. We can use this window, to issue SQL statements to the database. Now, we can also use Queries to execute SQL queries, but there we are limited to only asking for data from the database. Meaning, we can issue only SELECT statements there. But we cannot execute SQL statements which modify data and table structures or to create new tables there. And the SQL command window helps us to use such data manipulation and data definition statements or language. Examples of Data Manipulation Language, or simply DML are: INSERT, UPDATE and DELETE data. And some examples of Data Definition Language, or simply DDL, are: CREATE TABLE, DROP TABLE and ALTER statements. |
| In Base main window.
Click Tools > SQL Typing in the “Command to execute” text area: INSERT INTO "Books" ( "Title", "Author", "PublishYear", "Publisher", "Price") VALUES ('The Hobbit', 'J.R.R Tolkien', 2002, 'Oxford', 500); |
We will first see a DML example.
In the Base window, let us open the SQL Command Window from the Tools menu. Let us insert a new record into the Books table by typing, in the “Command to execute” text area: INSERT INTO "Books" ( "Title", "Author", "PublishYear", "Publisher", "Price") VALUES ('The Hobbit', 'J.R.R Tolkien', 2002, 'Oxford', 500); Before clicking on the Execute button, let us look at the command closely now. |
| Show Slide Number 6
INSERT INTO "Books" ( "Title", "Author", "PublishYear", "Publisher", "Price") VALUES ('The Hobbit', 'J.R.R Tolkien', 2002, 'Oxford', 500); Table name and field names need to be in double quotes. Base is case sensitive Double quotes ensure that Base will accept the table and field names as we created. If double quotes not used, Base will automatically convert all names into upper cases. Use single quotes to encase the values that are of data type TEXT. NUMERIC fields need not be encased with any quotes. Base will automatically generate data in AutoNumber fields |
The INSERT statement lists the table name and the field names and then the Values that need to go into the new record.
Notice that the table name and field names are enclosed in double quotes. We know that Base is case sensitive and the double quotes ensure that Base will accept the names as we created. If we don’t use the quotes, Base will automatically convert all names into upper cases. We have to use single quotes to encase the values that are of data type TEXT. NUMERIC fields need not be encased with any quotes. Also, we need not include the BookId field which is an AutoNumber field. Base will take care of auto-generating the number. |
| Go back to Base main window.
Click on Execute button. Close window. Double click on Books table. Show cursor movement over the last record. Click on Tools>SQL Type CREATE TABLE "Authors" ("AuthorId" INTEGER PRIMARY KEY, "Author" VARCHAR_IGNORECASE(50), "Country" VARCHAR_IGNORECASE(50)); Click execute button. Click Close button. Click on View>Refresh Tables Point mouse over Authors table. And double click it. |
So, let us execute the SQL. Notice the message ‘Command successfully executed’.
If there are any errors with the SQL we wrote, Base will point them out. Let us double click on the Books table and look for the new record we just inserted. There it is, appended in the last row. Next let us see a DDL example. We will create a new table called Authors with fields AuthorId, Author and Country. In the SQL command window, let us type as shown in the screen: And execute it. Let us go back to the Tables list and Refresh the tables from the Tools menu. There is the new Authors table we just created. |
| Show Slide Number 7
To know more about DML, visit: http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#N12934 To know about more DDL visit: |
To know more about DML, visit the http://hsqldb.org/ website shown on the screen.
To know about more DDL visit the Wikipedia website shown on the screen. |
| Show Slide Number 8
Assignment: 1. Use the UPDATE statement to set the price of the book with BookId as 3, to Rs. 300 2. Delete the book that has the title ‘'The Hobbit' 3. Insert a new record into the Authors table author name as ‘J.R.R. Tolkien’, and country as‘England’ 4. Drop the Authors table from the database, by using the DROP statement. |
Here is another assignment for you:
1. Use the UPDATE statement to set the price of the book with BookId as 3, to Rs. 300 2. Delete the book that has the title ‘'The Hobbit' 3. Insert a new record into the Authors table author name as ‘J.R.R. Tolkien’, and country as‘England’ 4. Drop the Authors table from the database, by using the DROP statement. |
| Show Slide Number 9
Summary
|
This brings us to the end of this tutorial on LibreOffice Base
To summarize, we learned the following topics:
|
| Acknowledgement Slide | 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 http://spoken-tutorial.org/NMEICT-Intro. This script has been contributed by Priya Suresh, DesiCrew Solutions,signing off. Thanks for joining. |
