LibreOffice-Suite-Base/C3/Create-simple-queries-in-SQL-View-II/English
| Visual Cue | Narration |
|---|---|
| Show Slide Number 1
Spoken tutorial on LibreOffice Base : More Queries in SQL View Learning Objectives Write Queries in SQL View Use ORDER BY clause Use JOINS Use Aggregate functions Use the GROUP BY clause And use built in Functions |
Welcome to the Spoken tutorial on LibreOffice Base.
In this tutorial, we will learn how to Write Queries in SQL View Use ORDER BY clause Use JOINS Use Aggregate functions Use the GROUP BY clause And use built in Functions |
| Open Library database in base, if not already open
Click on the Queries list on the left panel And then click on the ‘Create Query in SQL View’ |
Let us learn more about writing SQL queries
For this, lets open our familiar Library database. Now, let us click on the Queries list on the left panel and then click on ‘Create Query in SQL View’. |
| In the Query design window type exactly as follows:
SELECT Publisher, Title, Author FROM Books WHERE Publisher IN ( 'Cambridge', 'Oxford') ORDER BY Publisher ASC, Title ASC Show cursor movement appropriately as narration goes forward Click on Run Query icon Show cursor movement over the results. |
First let us see how we can sort the results of a query.
And let us consider the following example: Retrieve Book title and author information for all those books published by Cambridge or Oxford and sort them by the publisher in ascending order, followed by book titles in ascending order. And here is the query: SELECT Publisher, Title, Author FROM Books WHERE Publisher IN ( 'Cambridge', 'Oxford') ORDER BY Publisher ASC, Title ASC So notice we have used the ORDER BY clause to specify Sorting on column names. Also notice the comma characters which help to add more columns for sorting. And to specify the ascending or descending order, we can simply type A S C or D E S C next to each column name. Now, let us click on the Run Query icon below the file menu bar. Here are the books, first sorted by Publisher and then by book title. |
| Highlight all text in the bottom panel and overwrite by typing:
SELECT B.title, I.IssueDate, I.Memberid FROM Books B JOIN BooksIssued I ON B.bookid = I.BookId WHERE CheckedIn = FALSE Show cursor movement appropriately as narration goes forward Click on Run Query icon Show cursor movement over the results. |
Now, onto our next query.
Let us now get a list of book titles, which have not yet been returned by the members, along with the book issue dates. Since titles are in the Books table and the Book Issue date is in the BooksIssued table, we will need to combine these two somehow. So we will use the JOIN keyword, to join these two tables and we will use the common column, BookId, to link these two tables. So the query is: SELECT B.title, I.IssueDate, I.Memberid FROM Books B JOIN BooksIssued I ON B.bookid = I.BookId WHERE CheckedIn = FALSE Notice the letters B and I in the FROM clause. These are called Aliases, which can be either descriptive or just single alphabets for better readability. Notice that the BookId column is in both the tables. So we will use aliases to specify or qualify column names to avoid any confusion Okay, now notice that we have joined the two tables using the JOIN keyword in the FROM clause. and we have specified the BookId column for joining by writing: ON B.bookid = I.BookId So let us run our query now, and we see a list of books and their issue date and notice that the CheckedIn status; not checked in. |
| Highlight all text in the bottom panel and overwrite by typing:
SELECT B.Title, I.IssueDate, I.MemberId, M.Name FROM Books B JOIN BooksIssued I ON B.BookId = I.BookId JOIN Members M ON I.MemberId = M.MemberId WHERE CheckedIn = FALSE Show cursor movement appropriately as narration goes forward Click Run query icon. Show cursor movement appropriately |
Okay, also notice that we see only the memberId in the results. Not very useful, is it?
So how do we display member names, which are in the members table? Simple; we JOIN the members table to our query as follows: SELECT B.Title, I.IssueDate, I.MemberId, M.Name FROM Books B JOIN BooksIssued I ON B.BookId = I.BookId JOIN Members M ON I.MemberId = M.MemberId WHERE CheckedIn = FALSE So notice the second join with the Members table and the MemberId column used for the joining. Let us run the query. Here are the member names, along with the books that were issued to them. |
| Highlight all text in the bottom panel and overwrite by typing:
SELECT COUNT(*) AS "Total Members" FROM Members Show cursor movement appropriately as narration goes forward Click Run Query icon Show cursor movement appropriately |
Next, let us learn about aggregates and grouping.
How can we get a count of all the members in the library? Here is the query: SELECT COUNT(*) AS "Total Members" FROM Members So here notice the COUNT. This is called an aggregate function, as it returns just one value by evaluating a set of records. Also we have added an Alias ‘Total Members’. Now let us run the query. So here, Base has evaluated all the 4 members records and returned the number 4 which is the total count of members. Some more examples of aggregate functions are SUM, MAX and MIN. |
| Highlight all the text in the bottom panel, and overwrite by typing:
SELECT Publisher, COUNT(*) AS "Number of Books" FROM Books GROUP BY Publisher ORDER BY Publisher Show cursor movement appropriately as narration goes forward Click Run Query icon. Show cursor movement appropriately |
Now let us learn about grouping information.
How do we get a count of books for each publisher? Here is the query: SELECT Publisher, COUNT(*) AS "Number of Books" FROM Books GROUP BY Publisher ORDER BY Publisher Notice the new GROUP BY clause. So we are selecting the Publisher and the number of books and the GROUP BY clause to group the records for each Publisher. Let us run the query now. Notice the publisher names and the number of books by each publisher beside them. |
| Highlight all the text in the bottom panel, and overwrite by typing:
SELECT B.Title, I.IssueDate, I.ReturnDate FROM Books B JOIN BooksIssued I ON B.bookid = I.BookId WHERE CheckedIn = FALSE and ReturnDate < CURRENT_DATE Show cursor movement appropriately as narration goes forward Click Run Query icon. Show cursor movement appropriately |
Next, let us learn about using Functions in SQL.
Functions are statements that return a single value. For example, CURRENT_DATE returns today’s date. So let us list all the book titles that are due to be returned by the members. And the query is: SELECT B.Title, I.IssueDate, I.ReturnDate FROM Books B JOIN BooksIssued I ON B.bookid = I.BookId WHERE CheckedIn = FALSE and ReturnDate < CURRENT_DATE So, notice the use of the CURRENT_DATE function. We are retrieving books for which the Return Date is past today’s date. Let us run the query And here are the books that are due to be returned. |
| Show Slide Number 2
For a list of functions that HSQLdb offers: Visit http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html |
For a list of functions that HSQLdb offers: go to http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html
The entire user guide can be accessed by visiting the website; |
| Show Slide Number 3
Assignment: Write and test SQL queries for the following: 1. Get a count of all the books in the Library. 2. Get a count of books written by each author. 3. Get a list of member names and their phone numbers, who need to return books today 4. Explain what this query does? SELECT SUM(price) AS "Total Cost of Cambridge Books" FROM Books WHERE publisher = 'Cambridge' |
Here is an assignment:
Write and test your SQL queries for the following: 1. Get a count of all the books in the Library. 2. Get a count of books written by each author. 3. Get a list of member names and their phone numbers, who need to return books today 4. Explain what this query does? SELECT SUM(price) AS "Total Cost of Cambridge Books" FROM Books WHERE publisher = 'Cambridge' |
| Show Slide Number 4
Summary Create Queries in SQL View Use ORDER BY clause Use JOINS Use Aggregate functions Use the GROUP BY clause And use built in Functions |
This brings us to the end of this tutorial on More Queries in SQL View LibreOffice Base
To summarize, we learned how to: Create Queries in SQL View Use ORDER BY clause Use JOINS Use Aggregate functions Use the GROUP BY clause And use built in Functions |
| 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. |
