LibreOffice-Suite-Base/C3/Create-simple-queries-in-SQL-View/English
| Visual Cue | Narration |
|---|---|
| Show Slide Number 1
Spoken tutorial on LibreOffice Base: Queries in SQL View Learning Objectives
|
Welcome to the Spoken tutorial on LibreOffice Base.
In this tutorial, we will learn how to Create Simple Queries in SQL View Write simple SQL Use SELECT, FROM , and WHERE clauses And choose upper lower or mixed cases for naming fields and tables |
| Show Slide Number 2
HSQLDB.org Base runs on HSQL database engine. Stands for Hyper Structured Query Language Database Engine HSQLDB is open source, written in Java Visit http://hsqldb.org for more information |
Before using Base to create queries in SQL View, let us talk about LibreOffice Base.
Base runs on the HSQL database engine This is an open source database engine software written in Java Go to http://hsqldb.org for more information on HSQLDB. |
| Show Slide Number 3
What is SQL?
LibreOffice Base, MySQL, Microsoft SQL Server, Microsoft Access, Oracle, DB2. Some examples of using SQL are
|
Okay, now let us learn about SQL.
SQL stands for Structured Query Language It is a standard language for accessing and manipulating databases It is an internationally accepted ANSI standard. And so it is used in a variety of Database Management Systems or DBMS. Some examples are our own LibreOffice Base, MySQL, Microsoft SQL Server, Microsoft Access, Oracle, and DB2. The most common use of SQL is to retrieve data from a database which is also known as querying a database. SQL can also be used for inserting data into a database, updating data or deleting data from a database. And we have done all of these operations using Base in our previous tutorials by using the very user friendly wizards and designing windows. But knowing the underlying query language gives us more flexibility and power to query the database. And SQL can also be used not only to modify data, but also to modify database and table structures |
| Show Slide Number 4
SQL Resources To learn more about SQL, visit
Chunked HTML: http://hsqldb.org/doc/2.0/guide/ All-in-one HTML: http://hsqldb.org/doc/2.0/guide/guide.html PDF: http://hsqldb.org/doc/2.0/guide/guide.pdf |
Since our tutorial does not cover all about SQL, here are some useful tutorials and their websites.<pause>
HSQLDB has its own user guides. They can be viewed online. Or they can be downloaded and saved into your computer as a PDF file. Okay, let us learn some SQL now. We will open our familiar Library database example. |
| Open Library database in base, if not already open
In the base main window, click on the Queries list on the left panel And then click on the ‘Create Query in SQL View’ |
Let’s open our Library database.
Now, let us click on the Queries list on the left panel and then click on ‘Create Query in SQL View’. |
| Show cursor movement appropriately.
(zooming or larger fonts preferred, Use the same upper or lower cases. Typing these with mistakes in Cases, will return errors.) SELECT * FROM Books Show cursor movement appropriately. Click on edit menu, click on Run Query. Point cursor over the top area. Click on Save icon, click ok. |
Now we see a blank window titled Query Design
and this is where we will type in our queries in SQL. Let us write our first simple query. And that is: Get information about all books in the library. Simple. Any retrieval needs to use a SELECT keyword. So we will write our query as SELECT * FROM Books Here Books is the table name. Notice the capital B in books. We will faithfully follow the table or column names we used earlier. And * is a wild card. Here it means, get all the fields or columns from the Books table. Now let us execute or run it. Click on Edit menu and then click on Run Query. Now we see a top panel with a list of records on books. We can save this query or any query we write and give them descriptive names. So there is our first simple query! |
| Show Slide Number 5
HSQL Tips and Notes 1. HSQLDB is case sensitive with its Database object names, such as tables, column names. 2. Table name “Books” is not the same as “books”. 3. For convenience, use all upper cases or all lower cases. Example: BOOKS, members, etc.
5. Keywords like SELECT, FROM can be of any case, but be uniform for better readability |
Here are some tips:
HSQLDB is case sensitive with its Database object names, such as tables and column names. Meaning, Table name “Books” with a Capital B is not the same as “books” with a small b. But for convenience we can use all upper cases or all lower cases. For example: BOOKS in capital letters, or members in small letters, etc. But using mixed cases, can be easy to read and understand. For example: BooksIssued, with a capital B and I. Or ReturnDate with a capital R and D. So, we have to use the table names and column names just the way they are created. For SQL keywords like SELECT, we can use any case or mix cases. But let us be uniform in our usage for better readability. In our examples, we will use all upper cases for keywords. |
| In the Base query window,
Highlight all the text in the bottom panel, and over write by typing: SELECT Title, Author FROM Books Click on Run Query icon. Show cursor movement. |
Now, onto our next query.
We can type this query in a new window, or we can overwrite it on the previous query. For now, let us overwrite it on the previous query. Let us retrieve specific columns from the Books table. SELECT Title, Author FROM Books And run the query. We can also use the Run Query icon below the file menu bar or use the keyboard shortcut F5. And there are our records with only those columns that we needed. |
| Highlight all the text in the bottom panel, and over write by typing:
SELECT * FROM Books WHERE Publisher = 'Cambridge' Show cursor movement appropriately Click query icon. Show cursor movement appropriately |
Okay. Let us move on.
Let us introduce conditions or criteria for our query. We will retrieve only those books published by Cambridge. And so, our query is SELECT * FROM Books WHERE Publisher = 'Cambridge' Notice that we have introduced a new keyword WHERE. Followed by a condition, where we say Publisher equals Cambridge Let us run our query now and we see only those books for which the publisher is Cambridge. |
| Highlight all the text in the bottom panel, and over write by typing:
SELECT * FROM Books WHERE Publisher = 'Cambridge' AND PublishedYear > 1975 Show cursor movement appropriately Click Run Query icon Show cursor movement appropriately |
And so we can have any number of conditions or criteria in a query.
Let us write a query with two conditions. Let us retrieve only those books published by Cambridge and only those that were published after the year 1975. And our query is: SELECT * FROM Books WHERE Publisher = 'Cambridge' AND PublishedYear > 1975 And we see two conditions after the WHERE keyword or clause. Notice that they are put together using ‘AND’. Here ‘AND’ is called a logical operator, and here it serves to combine conditions. ‘OR’ is another logical operator. Explore these by using them in the above query later. Let us now run the query and see the results at the top. There, these are the books that met our conditions. |
| Highlight all the text in the bottom panel, and over write by typing:
SELECT * FROM Books WHERE Publisher IN ( 'Cambridge', 'Oxford') Click Run Query icon. |
Okay, let us also learn another way to include multiple conditions.
How can we get a list of only those books for which the publisher is Cambridge or Oxford or both? And here is our query: SELECT * FROM Books WHERE Publisher IN ( 'Cambridge', 'Oxford') Notice the new keyword ‘IN’. It helps to combine conditions based on a single column, in this case, the Publisher. And notice the results now. |
| Show Slide Number 5
assignment: Write and test SQL queries for the following: 1. Get information about all the members in the Library. 2. Get a list of all book titles which are priced more than Rs 150 3. Get a list of books which were authored by William Shakespeare or John Milton? |
Here is an assignment:
Write and test your SQL queries for the following: 1. Get information about all the members in the Library. 2. Get a list of all book titles which are priced more than Rs 150 3. Get a list of books which were written by William Shakespeare or John Milton Let us learn more about SQL in the next tutorial. |
| Show Slide Number 6
Summary Create Simple Queries in SQL View Write simple SQL Use SELECT, FROM , WHERE clauses Choose upper, lower, or mixed cases for naming fields and tables |
This brings us to the end of this tutorial on Queries in SQL View in LibreOffice Base
To summarize, we learned how to: Create Simple Queries in SQL View Write simple SQL Use SELECT, FROM , and WHERE clauses And choose upper, lower, or mixed cases for naming fields and tables. |
| 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 Pvt Ltd, signing off. Thanks for joining. |
