1
00:00:02 --> 00:00:05
Welcome to the Spoken tutorial on LibreOffice Base.

2
00:00:06 --> 00:00:09
In this tutorial, we will learn how to

3
00:00:10 --> 00:00:12
Write Queries in SQL View

4
00:00:13 --> 00:00:14
Use ORDER BY clause

5
00:00:15 --> 00:00:16
Use JOINS

6
00:00:17 --> 00:00:18
Use Aggregate functions

7
00:00:19 --> 00:00:20
Use the GROUP BY clause

8
00:00:21 --> 00:00:25
And to use built in Functions

9
00:00:26 --> 00:00:30
Let us learn more about writing SQL queries

10
00:00:31 --> 00:00:35
For this, let us open our familiar Library database.

11
00:00:36 --> 00:00:41
Now, let us click on the Queries list on the left panel

12
00:00:42 --> 00:00:48
and then click on ‘Create Query in SQL View’.

13
00:00:49 --> 00:00:54
First let us see how we can sort the results of a query.

14
00:00:55 --> 00:00:58
And let us consider the following example:

15
00:00:59 --> 00:01:08
Retrieve Book title and author information for all those books published by Cambridge or Oxford

16
00:01:09 --> 00:01:18
and sort them by the publisher in ascending order, followed by book titles in ascending order.

17
00:01:19 --> 00:01:21
And here is the query:

18
00:01:22 --> 00:01:27
SELECT Publisher, Title, Author

19
00:01:28 --> 00:01:30
FROM Books

20
00:01:31 --> 00:01:41
WHERE Publisher IN ( 'Cambridge', 'Oxford')

21
00:01:42 --> 00:01:49
ORDER BY Publisher ASC, Title ASC

22
00:01:50 --> 00:01:57
So notice we have used the ORDER BY clause to specify Sorting on column names.

23
00:01:58 --> 00:02:04
Also notice the comma characters which help to add more columns for sorting.

24
00:02:05 --> 00:02:18
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.

25
00:02:19 --> 00:02:25
Now, let us click on the Run Query icon below the file menu bar.

26
00:02:26 --> 00:02:37
Here are the books, first sorted by Publisher and then by book title.

27
00:02:38 --> 00:02:47
Let us now get a list of book titles, which have not yet been returned by the members, along with the book issue dates.

28
00:02:48 --> 00:02:54
Since titles are in the Books table and the Book Issue date is in the BooksIssued table,

29
00:02:55 --> 00:02:59
we will need to combine these two somehow.

30
00:03:00 --> 00:03:06
So we will use the JOIN keyword, to join these two tables

31
00:03:07 --> 00:03:13
and we will use the common column, BookId, to link these two tables.

32
00:03:14 --> 00:03:16
So the query is:

33
00:03:17 --> 00:03:34
SELECT B.title, I.IssueDate, I.Memberid
FROM Books B JOIN  BooksIssued I

34
00:03:35 --> 00:03:47
ON B.bookid = I.BookId
WHERE CheckedIn = FALSE

35
00:03:48 --> 00:03:54
Notice the letters B and I in the FROM clause.

36
00:03:55 --> 00:04:05
These are called Aliases, which can be either descriptive or just single alphabets for better readability.

37
00:04:06 --> 00:04:10
Notice that the BookId column is in both the tables.

38
00:04:11 --> 00:04:20
So we will use aliases to specify or qualify column names  to avoid any confusion

39
00:04:21 --> 00:04:30
Okay, now notice that we have joined the two tables using the JOIN keyword in the FROM clause.

40
00:04:31 --> 00:04:45
and we have specified the BookId column for joining by writing:
ON B.bookid = I.BookId

41
00:04:46 --> 00:04:48
So let us run our query now,

42
00:04:49 --> 00:04:58
and we see a list of books and their issue date and notice that the CheckedIn status; not checked in.

43
00:04:59 --> 00:05:07
Okay, also notice that we see only the memberId in the results. Not very useful, is it?

44
00:05:08 --> 00:05:14
So how do we display member names, which are in the members table?

45
00:05:15 --> 00:05:20
Simple; we JOIN the members table to our query as follows:

46
00:05:21 --> 00:05:36
SELECT B.Title, I.IssueDate, I.MemberId, M.Name
FROM Books B

47
00:05:37 --> 00:05:57
JOIN  BooksIssued I ON B.BookId = I.BookId
JOIN Members M ON I.MemberId = M.MemberId

48
00:05:58 --> 00:06:01
WHERE CheckedIn = FALSE

49
00:06:02 --> 00:06:11
So notice the second join with the Members table and the MemberId column used for joining.

50
00:06:12 --> 00:06:13
Let us run the query.

51
00:06:14 --> 00:06:19
Here are the member names, along with the books that were issued to them.

52
00:06:20 --> 00:06:25
Next, let us learn about aggregates and grouping.

53
00:06:26 --> 00:06:30
How can we get a  count of all the members in the library?

54
00:06:31 --> 00:06:33
Here is a query:

55
00:06:34 --> 00:06:46
SELECT COUNT(*) AS "Total Members"
FROM Members

56
00:06:47 --> 00:06:50
So here notice the COUNT.

57
00:06:51 --> 00:07:01
This is called an aggregate function, as it returns just one value by evaluating a set of records.

58
00:07:02 --> 00:07:06
Also we have added an Alias ‘Total Members’.

59
00:07:07 --> 00:07:09
Now let us run the query.

60
00:07:10 --> 00:07:21
So here, Base has evaluated all the 4 members records and returned the number 4 which is the total count of members.

61
00:07:22 --> 00:07:29
Some more examples of aggregate functions are SUM, MAX and MIN.

62
00:07:30 --> 00:07:35
Now let us learn about grouping information.

63
00:07:36 --> 00:07:39
How do we get a count of books for each publisher?

64
00:07:40 --> 00:07:42
Here is the query:

65
00:07:43 --> 00:08:02
SELECT Publisher, COUNT(*) AS "Number of Books" FROM Books
GROUP BY Publisher 
ORDER BY Publisher

66
00:08:03 --> 00:08:05
Notice the new GROUP BY clause.

67
00:08:06 --> 00:08:17
So we are selecting the Publisher and the number of books and the GROUP BY clause to group the records for  each Publisher.

68
00:08:18 --> 00:08:20
Let us run the query now.

69
00:08:21 --> 00:08:32
Notice the publisher names and the number of books by each publisher beside them.

70
00:08:33 --> 00:08:37
Next, let us learn about using Functions in SQL.

71
00:08:38 --> 00:08:42
Functions are statements that return a single value.

72
00:08:43 --> 00:08:48
For example, CURRENT_DATE returns today’s date.

73
00:08:49 --> 00:08:55
So let us list all the book titles that are due to be returned by the members.

74
00:08:56 --> 00:08:57
And the query is:

75
00:08:58 --> 00:09:07
SELECT B.Title, I.IssueDate, I.ReturnDate

76
00:09:08 --> 00:09:20
FROM Books B JOIN BooksIssued I ON B.bookid = I.BookId

77
00:09:21 --> 00:09:30
WHERE CheckedIn = FALSE and ReturnDate < CURRENT_DATE

78
00:09:31 --> 00:09:35
So, notice the use of the CURRENT_DATE function.

79
00:09:36 --> 00:09:42
We are retrieving books for which the Return Date is past today’s date.

80
00:09:43 --> 00:09:44
Let us run the query

81
00:09:45 --> 00:09:50
And here are the books that are due to be returned.

82
00:09:51 --> 00:10:22
For a list of functions that HSQLdb offers: go to  http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html

83
00:10:23 --> 00:10:28
The entire user guide can be accessed by visiting the website;

84
00:10:29 --> 00:10:47
http://www.hsqldb.org/doc/2.0/guide/

85
00:10:48 --> 00:10:49
Here is an assignment:

86
00:10:50 --> 00:10:54
Write and test your SQL queries for the following:

87
00:10:55 --> 00:11:02
1. Get a count of all the books in the Library.

88
00:11:03 --> 00:11:10
3. Get a list of member names and their phone numbers, who need to return books today

89
00:11:11 --> 00:11:23
4. Explain what  this query does?
SELECT (price) AS "Total Cost of Cambridge Books"

90
00:11:24 --> 00:11:31
FROM Books
WHERE publisher = 'Cambridge'

91
00:11:32 --> 00:11:39
This brings us to the end of this tutorial on More Queries in SQL View LibreOffice Base

92
00:11:40 --> 00:11:42
To summarize, we learned how to:

93
00:11:43 --> 00:11:46
Create Queries in SQL View

94
00:11:47 --> 00:11:48
Use ORDER BY clause

95
00:11:49 --> 00:11:50
Use JOINS

96
00:11:51 --> 00:11:53
Use Aggregate functions

97
00:11:54 --> 00:11:56
Use the GROUP BY clause

98
00:11:57 --> 00:11:59
And use built in Functions

99
00:12:00 --> 00:12:20
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.

100
00:12:21 --> 00:12:26
This script has been contributed by Priya Suresh, DesiCrew Solutions.
And this is Soundharya, DesiCrew Solutions, signing off. 

Thanks for joining