LibreOffice-Suite-Base/C3/Create-simple-queries-in-SQL-View-II/English-timed

From Script | Spoken-Tutorial
Revision as of 16:48, 30 September 2015 by Sandhya.np14 (Talk | contribs)

Jump to: navigation, search
Time Narration
00:02 Welcome to the Spoken tutorial on LibreOffice Base.
00:06 In this tutorial, we will learn how to:
00:10 * Write queries in SQL View
00:13 * Use ORDER BY clause
00:15 * Use JOINS
00:17 * Use Aggregate functions
00:19 * Use the GROUP BY clause
00:21 * And to use built in Functions.
00:26 Let us learn more about writing SQL queries.
00:31 For this, let us open our familiar Library database.
00:36 Now, let us click on the Queries list on the left panel
00:42 and then click on ‘Create Query in SQL View’.
00:49 First, let us see how we can sort the results of a query.
00:55 And let us consider the following example:
00:59 Retrieve Book title and author information for all those books published by Cambridge or Oxford
01:09 and sort them by the publisher in ascending order, followed by book titles in ascending order.
01:19 And here is the query:
01:22 SELECT Publisher, Title, Author
01:28 FROM Books
01:31 WHERE Publisher IN ( 'Cambridge', 'Oxford')
01:42 ORDER BY Publisher ASC, Title ASC
01:50 So, notice we have used the ORDER BY clause to specify sorting on column names.
01:58 Also notice the comma characters which help to add more columns for sorting.
02:05 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.
02:19 Now, let us click on the Run Query icon below the file menu bar.
02:26 Here are the books, first sorted by Publisher and then by book title.
02;34 Now, onto our next query.
02:38 Let us now get a list of book titles which have not yet been returned by the members, along with the book issue dates.
02:48 Since titles are in the Books table and the Book Issue date is in the BooksIssued table,
02:55 we will need to combine these two somehow.
03:00 So, we will use the JOIN keyword, to join these two tables
03:07 and we will use the common column, BookId, to link these two tables.
03:14 So the query is:
03:17 SELECT B.title, I.IssueDate, I.Memberid

FROM Books B JOIN BooksIssued I

03:35 ON B.bookid = I.BookId

WHERE CheckedIn = FALSE

03:48 Notice the letters 'B' and 'I' in the FROM clause.
03:55 These are called Aliases which can be either descriptive or just single alphabets for better readability.
04:06 Notice that the BookId column is in both the tables.
04:11 So, we will use aliases to specify or qualify column names to avoid any confusion.
04:21 Okay, now notice that we have joined the two tables using the JOIN keyword in the FROM clause
04:31 and we have specified the BookId column for joining by writing:

ON B.bookid = I.BookId

04:46 So, let us run our query now
04:49 and we see a list of books and their issue date and notice that the CheckedIn status- not checked in.
04:59 Okay, also notice that we see only the MemberId in the results. Not very useful, is it?
05:08 So, how do we display member names which are in the members table?
05:15 Simple. We JOIN the members table to our query as follows:
05:21 SELECT B.Title, I.IssueDate, I.MemberId, M.Name

FROM Books B

05:37 JOIN BooksIssued I ON B.BookId = I.BookId

JOIN Members M ON I.MemberId = M.MemberId

05:58 WHERE CheckedIn = FALSE
06:02 So, notice the second join with the Members table and the MemberId column used for joining.
06:12 Let us run the query.
06:14 Here are the member names along with the books that were issued to them.
06:20 Next, let us learn about aggregates and grouping.
06:26 How can we get a count of all the members in the library?
06:31 Here is a query:
06:34 SELECT COUNT(*) AS "Total Members"

FROM Members

06:47 So, here notice the COUNT.
06:51 This is called an aggregate function as it returns just one value by evaluating a set of records.
07:02 Also we have added an Alias- ‘Total Members’.
07:07 Now, let us run the query.
07:10 So here, Base has evaluated all the 4 members records and returned the number 4 which is the total count of members.
07:22 Some more examples of aggregate functions are SUM, MAX and MIN.
07:30 Now, let us learn about grouping information.
07:36 How do we get a count of books for each publisher?
07:40 Here is the query:
07:43 SELECT Publisher, COUNT(*) AS "Number of Books" FROM Books

GROUP BY Publisher ORDER BY Publisher

08:03 Notice the new GROUP BY clause.
08:06 So, we are selecting the Publisher and the number of books and the GROUP BY clause to group the records for each Publisher.
08:18 Let us run the query now.
08:21 Notice the publisher names and the number of books by each publisher beside them.
08:33 Next, let us learn about using Functions in SQL.
08:38 Functions are statements that return a single value.
08:43 For example, CURRENT_DATE returns today’s date.
08:49 So, let us list all the book titles that are due to be returned by the members.
08:56 And the query is:
08:58 SELECT B.Title, I.IssueDate, I.ReturnDate
09:08 FROM Books B JOIN BooksIssued I ON B.bookid = I.BookId
09:21 WHERE CheckedIn = FALSE and ReturnDate < CURRENT_DATE
09:31 So, notice the use of the CURRENT_DATE function.
09:36 We are retrieving books for which the Return Date is past today’s date.
09:43 Let us run the query.
09:45 And, here are the books that are due to be returned.
09:51 For a list of functions that HSQLdb offers: go to http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html
10:23 The entire user guide can be accessed by visiting the website:
10:29 http://www.hsqldb.org/doc/2.0/guide/
10:48 Here is an assignment:
10:50 Write and test your SQL queries for the following:
10:55 1. Get a count of all the books in the Library.
10;58 2. Get a count of books written by each author.
11:03 3. Get a list of member names and their phone numbers who need to return books today.
11:11 4. Explain what this query does?

SELECT (price) AS "Total Cost of Cambridge Books"

11:24 FROM Books

WHERE publisher = 'Cambridge'

11:32 This brings us to the end of this tutorial on more queries in SQL View LibreOffice Base.
11:40 To summarize, we learned how to:
11:43 * Create Queries in SQL View
11:47 * Use ORDER BY clause
11:49 * Use JOINS
11:51 * Use Aggregate functions
11:54 * Use the GROUP BY clause
11:57 * And use built in Functions.
12:00 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.
12:21 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