Difference between revisions of "LibreOffice-Suite-Base/C3/Create-simple-queries-in-SQL-View-II/English-timed"
From Script | Spoken-Tutorial
PoojaMoolya (Talk | contribs) |
|||
(4 intermediate revisions by one other user not shown) | |||
Line 4: | Line 4: | ||
|- | |- | ||
|00:02 | |00:02 | ||
− | |Welcome to the Spoken tutorial on LibreOffice Base. | + | |Welcome to the '''Spoken tutorial''' on '''LibreOffice Base'''. |
|- | |- | ||
|00:06 | |00:06 | ||
− | |In this tutorial, we will learn how to | + | |In this tutorial, we will learn how to: |
|- | |- | ||
|00:10 | |00:10 | ||
− | |Write | + | | Write queries in '''SQL View''' |
|- | |- | ||
|00:13 | |00:13 | ||
− | |Use ORDER BY clause | + | | Use '''ORDER BY''' clause |
|- | |- | ||
|00:15 | |00:15 | ||
− | |Use JOINS | + | | Use '''JOINS''' |
|- | |- | ||
|00:17 | |00:17 | ||
− | |Use Aggregate functions | + | | Use '''Aggregate functions''' |
|- | |- | ||
|00:19 | |00:19 | ||
− | |Use the GROUP BY clause | + | | Use the '''GROUP BY''' clause |
|- | |- | ||
|00:21 | |00:21 | ||
− | |And to use built in | + | | And to use built in '''Function'''s. |
|- | |- | ||
|00:26 | |00:26 | ||
− | |Let us learn more about writing SQL queries | + | |Let us learn more about writing SQL queries. |
|- | |- | ||
|00:31 | |00:31 | ||
− | |For this, let us open our familiar Library database. | + | |For this, let us open our familiar '''Library''' database. |
|- | |- | ||
|00:36 | |00:36 | ||
− | |Now, let us click on the Queries list on the left panel | + | |Now, let us click on the '''Queries''' list on the left panel |
|- | |- | ||
|00:42 | |00:42 | ||
− | |and then click on ‘Create Query in SQL View’. | + | |and then click on '''‘Create Query in SQL View’'''. |
|- | |- | ||
|00:49 | |00:49 | ||
− | |First let us see how we can sort the results of a query. | + | |First, let us see how we can sort the results of a query. |
|- | |- | ||
Line 64: | Line 64: | ||
|- | |- | ||
|01:09 | |01:09 | ||
− | |and sort them by the publisher in ascending order, followed by book titles in ascending order. | + | |and sort them by the '''publisher''' in ascending order, followed by '''book titles''' in ascending order. |
|- | |- | ||
Line 72: | Line 72: | ||
|- | |- | ||
|01:22 | |01:22 | ||
− | |SELECT Publisher, Title, Author | + | |'''SELECT Publisher, Title, Author''' |
|- | |- | ||
|01:28 | |01:28 | ||
− | |FROM Books | + | |'''FROM Books''' |
|- | |- | ||
|01:31 | |01:31 | ||
− | |WHERE Publisher IN ( 'Cambridge', 'Oxford') | + | |'''WHERE Publisher IN ( 'Cambridge', 'Oxford')''' |
|- | |- | ||
|01:42 | |01:42 | ||
− | |ORDER BY Publisher ASC, Title ASC | + | |'''ORDER BY Publisher ASC, Title ASC''' |
|- | |- | ||
|01:50 | |01:50 | ||
− | |So notice we have used the ORDER BY clause to specify | + | |So, notice we have used the '''ORDER BY''' clause to specify sorting on column names. |
|- | |- | ||
Line 96: | Line 96: | ||
|- | |- | ||
|02:05 | |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. | + | |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 | |02:19 | ||
− | |Now, let us click on the Run Query icon below the file menu bar. | + | |Now, let us click on the '''Run Query''' icon below the file menu bar. |
|- | |- | ||
|02:26 | |02:26 | ||
− | |Here are the books, first sorted by Publisher and then by book title. | + | |Here are the books, first sorted by '''Publisher''' and then by '''book title'''. |
|- | |- | ||
− | |02 | + | |02:34 |
|Now, onto our next query. | |Now, onto our next query. | ||
|- | |- | ||
|02:38 | |02:38 | ||
− | |Let us now get a list of book titles | + | |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 | |02:48 | ||
− | |Since titles are in the Books table and the Book Issue date is in the BooksIssued table, | + | |Since titles are in the '''Books''' table and the Book Issue date is in the '''BooksIssued''' table, |
|- | |- | ||
Line 124: | Line 124: | ||
|- | |- | ||
|03:00 | |03:00 | ||
− | |So we will use the JOIN keyword, to join these two tables | + | |So, we will use the '''JOIN''' keyword, to join these two tables |
|- | |- | ||
|03:07 | |03:07 | ||
− | |and we will use the common column, BookId, to link these two tables. | + | |and we will use the common column, '''BookId''', to link these two tables. |
|- | |- | ||
Line 136: | Line 136: | ||
|- | |- | ||
|03:17 | |03:17 | ||
− | |SELECT B.title, I.IssueDate, I.Memberid | + | |'''SELECT B.title, I.IssueDate, I.Memberid''' '''FROM Books B JOIN BooksIssued I''' |
− | FROM Books B JOIN BooksIssued I | + | |
|- | |- | ||
|03:35 | |03:35 | ||
− | |ON B.bookid = I.BookId | + | |'''ON B.bookid = I.BookId''' '''WHERE CheckedIn = FALSE''' |
− | WHERE CheckedIn = FALSE | + | |
|- | |- | ||
|03:48 | |03:48 | ||
− | |Notice the letters B and I in the FROM clause. | + | |Notice the letters 'B' and 'I' in the '''FROM''' clause. |
|- | |- | ||
|03:55 | |03:55 | ||
− | |These are called Aliases | + | |These are called '''Aliases''' which can be either descriptive or just single alphabets for better readability. |
|- | |- | ||
|04:06 | |04:06 | ||
− | |Notice that the BookId column is in both the | + | |Notice that the '''BookId''' column is in both the '''table'''s. |
|- | |- | ||
|04:11 | |04:11 | ||
− | |So we will use | + | |So, we will use '''aliase'''s to specify or qualify column names to avoid any confusion. |
|- | |- | ||
|04:21 | |04:21 | ||
− | |Okay, now notice that we have joined the two tables using the JOIN keyword in the FROM clause | + | |Okay, now notice that we have joined the two tables using the '''JOIN''' keyword in the '''FROM''' clause |
|- | |- | ||
|04:31 | |04:31 | ||
− | |and we have specified the BookId column for joining by writing: | + | |and we have specified the '''BookId''' column for joining by writing: '''ON B.bookid = I.BookId''' |
− | ON B.bookid = I.BookId | + | |
|- | |- | ||
|04:46 | |04:46 | ||
− | |So let us run our query now | + | |So, let us '''run''' our query now |
|- | |- | ||
|04:49 | |04:49 | ||
− | |and we see a list of books and their issue date and notice that the CheckedIn status | + | |and we see a list of books and their issue date and notice that the '''CheckedIn''' status- not checked in. |
|- | |- | ||
|04:59 | |04:59 | ||
− | |Okay, also notice that we see only the | + | |Okay, also notice that we see only the '''MemberId''' in the results. Not very useful, is it? |
|- | |- | ||
|05:08 | |05:08 | ||
− | |So how do we display member names | + | |So, how do we display member names which are in the members table? |
|- | |- | ||
|05:15 | |05:15 | ||
− | |Simple | + | |Simple. We '''JOIN''' the '''members''' table to our query as follows: |
|- | |- | ||
|05:21 | |05:21 | ||
− | |SELECT B.Title, I.IssueDate, I.MemberId, M.Name | + | |'''SELECT B.Title, I.IssueDate, I.MemberId, M.Name''' '''FROM Books B''' |
− | FROM Books B | + | |
|- | |- | ||
|05:37 | |05:37 | ||
− | |JOIN BooksIssued I ON B.BookId = I.BookId | + | |'''JOIN BooksIssued I ON B.BookId = I.BookId''' '''JOIN Members M ON I.MemberId = M.MemberId''' |
− | JOIN Members M ON I.MemberId = M.MemberId | + | |
|- | |- | ||
|05:58 | |05:58 | ||
− | |WHERE CheckedIn = FALSE | + | |'''WHERE CheckedIn = FALSE''' |
|- | |- | ||
|06:02 | |06:02 | ||
− | |So notice the second join with the Members table and the MemberId column used for joining. | + | |So, notice the second join with the '''Members''' table and the '''MemberId''' column used for joining. |
|- | |- | ||
|06:12 | |06:12 | ||
− | |Let us run the query. | + | |Let us '''run''' the query. |
|- | |- | ||
|06:14 | |06:14 | ||
− | |Here are the member names | + | |Here are the member names along with the books that were issued to them. |
|- | |- | ||
Line 229: | Line 224: | ||
|- | |- | ||
|06:34 | |06:34 | ||
− | |SELECT COUNT(*) AS "Total Members" | + | |'''SELECT COUNT(*) AS "Total Members"''' '''FROM Members''' |
− | FROM Members | + | |
|- | |- | ||
|06:47 | |06:47 | ||
− | |So here notice the COUNT. | + | |So here, notice the '''COUNT'''. |
|- | |- | ||
|06:51 | |06:51 | ||
− | |This is called an aggregate function | + | |This is called an '''aggregate function''' as it returns just one value by evaluating a set of '''record'''s. |
|- | |- | ||
|07:02 | |07:02 | ||
− | |Also we have added an Alias ‘Total Members’. | + | |Also we have added an '''Alias'''- ‘Total Members’. |
|- | |- | ||
|07:07 | |07:07 | ||
− | |Now let us run the query. | + | |Now, let us '''run''' the query. |
|- | |- | ||
|07:10 | |07:10 | ||
− | |So here, Base has evaluated all the 4 members records and returned the number 4 which is the total count of members. | + | |So here, '''Base''' has evaluated all the 4 members records and returned the number 4 which is the total count of members. |
|- | |- | ||
|07:22 | |07:22 | ||
− | |Some more examples of aggregate functions are SUM, MAX and MIN. | + | |Some more examples of aggregate functions are '''SUM, MAX''' and '''MIN'''. |
|- | |- | ||
|07:30 | |07:30 | ||
− | |Now let us learn about grouping information. | + | |Now, let us learn about grouping information. |
|- | |- | ||
Line 270: | Line 264: | ||
|- | |- | ||
|07:43 | |07:43 | ||
− | |SELECT Publisher, COUNT(*) AS "Number of Books" FROM Books | + | |'''SELECT Publisher, COUNT(*) AS "Number of Books" FROM Books''' |
− | GROUP BY Publisher | + | '''GROUP BY Publisher''' |
− | ORDER BY Publisher | + | '''ORDER BY Publisher''' |
|- | |- | ||
|08:03 | |08:03 | ||
− | |Notice the new GROUP BY clause. | + | |Notice the new '''GROUP BY''' clause. |
|- | |- | ||
|08:06 | |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. | + | |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 | |08:18 | ||
− | |Let us run the query now. | + | |Let us '''run''' the query now. |
|- | |- | ||
Line 292: | Line 286: | ||
|- | |- | ||
|08:33 | |08:33 | ||
− | |Next, let us learn about using Functions in SQL. | + | |Next, let us learn about using '''Functions''' in '''SQL'''. |
|- | |- | ||
Line 300: | Line 294: | ||
|- | |- | ||
|08:43 | |08:43 | ||
− | |For example, CURRENT_DATE returns today’s date. | + | |For example, '''CURRENT_DATE''' returns today’s date. |
|- | |- | ||
|08:49 | |08:49 | ||
− | |So let us list all the book titles that are due to be returned by the members. | + | |So, let us list all the book titles that are due to be returned by the members. |
|- | |- | ||
Line 312: | Line 306: | ||
|- | |- | ||
|08:58 | |08:58 | ||
− | |SELECT B.Title, I.IssueDate, I.ReturnDate | + | |'''SELECT B.Title, I.IssueDate, I.ReturnDate''' |
|- | |- | ||
|09:08 | |09:08 | ||
− | |FROM Books B JOIN BooksIssued I ON B.bookid = I.BookId | + | |'''FROM Books B JOIN BooksIssued I ON B.bookid = I.BookId''' |
|- | |- | ||
|09:21 | |09:21 | ||
− | |WHERE CheckedIn = FALSE and ReturnDate < CURRENT_DATE | + | |'''WHERE CheckedIn = FALSE and ReturnDate < CURRENT_DATE''' |
|- | |- | ||
|09:31 | |09:31 | ||
− | |So, notice the use of the CURRENT_DATE function. | + | |So, notice the use of the '''CURRENT_DATE''' function. |
|- | |- | ||
|09:36 | |09:36 | ||
− | |We are retrieving books for which the Return Date is past today’s date. | + | |We are retrieving books for which the '''Return Date''' is past today’s date. |
|- | |- | ||
|09:43 | |09:43 | ||
− | |Let us run the query | + | |Let us '''run''' the query. |
|- | |- | ||
|09:45 | |09:45 | ||
− | |And here are the books that are due to be returned. | + | |And, here are the books that are due to be returned. |
|- | |- | ||
Line 344: | Line 338: | ||
|- | |- | ||
|10:23 | |10:23 | ||
− | |The entire user guide can be accessed by visiting the website | + | |The entire user guide can be accessed by visiting the website: |
|- | |- | ||
Line 363: | Line 357: | ||
|- | |- | ||
− | |10 | + | |10:58 |
|2. Get a count of books written by each author. | |2. Get a count of books written by each author. | ||
|- | |- | ||
|11:03 | |11:03 | ||
− | |3. Get a list of member names and their phone numbers | + | |3. Get a list of member names and their phone numbers who need to return books today. |
|- | |- | ||
|11:11 | |11:11 | ||
|4. Explain what this query does? | |4. Explain what this query does? | ||
− | SELECT (price) AS "Total Cost of Cambridge Books" | + | '''SELECT (price) AS "Total Cost of Cambridge Books"''' |
|- | |- | ||
|11:24 | |11:24 | ||
− | |FROM Books | + | |'''FROM Books''' '''WHERE publisher = 'Cambridge'''' |
− | WHERE publisher = 'Cambridge' | + | |
|- | |- | ||
|11:32 | |11:32 | ||
− | |This brings us to the end of this tutorial on | + | |This brings us to the end of this tutorial on more queries in '''SQL View''' LibreOffice Base. |
|- | |- | ||
Line 410: | Line 403: | ||
|- | |- | ||
|11:57 | |11:57 | ||
− | |And use built in Functions | + | |And use built in Functions. |
|- | |- | ||
|12:00 | |12:00 | ||
− | |Spoken Tutorial | + | |'''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 | |12:21 | ||
− | |This script has been contributed by Priya Suresh, DesiCrew Solutions. | + | |This script has been contributed by Priya Suresh, DesiCrew Solutions. And this is Soundharya, DesiCrew Solutions, signing off. |
− | + | Thanks for joining. | |
− | And this is Soundharya, DesiCrew Solutions, signing off. | + | |
− | + | ||
− | + | ||
− | Thanks for joining | + | |
|- | |- |
Latest revision as of 14:25, 23 March 2017
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. |