Difference between revisions of "LibreOffice-Suite-Base/C3/Create-simple-queries-in-SQL-View-II/English-timed"

From Script | Spoken-Tutorial
Jump to: navigation, search
Line 150: Line 150:
 
|-
 
|-
 
|03:55
 
|03:55
−
|These are called Aliases which can be either descriptive or just single alphabets for better readability.
+
|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 tables.  
+
|Notice that the '''BookId''' column is in both the '''table'''s.  
  
 
|-
 
|-
 
|04:11
 
|04:11
−
|So we will use aliases to specify or qualify column names  to avoid any confusion
+
|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; not checked in.
+
|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 memberId in the results. Not very useful, is it?
+
|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, which are in the members table?
+
|So, how do we display member names which are in the members table?
  
 
|-
 
|-
 
|05:15
 
|05:15
−
|Simple; we JOIN the members table to our query as follows:
+
|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, along with the books that were issued to them.
+
|Here are the member names along with the books that were issued to them.
  
 
|-
 
|-
Line 229: Line 229:
 
|-
 
|-
 
|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, as it returns just one value by evaluating a set of records.  
+
|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 270:
 
|-
 
|-
 
|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 292:
 
|-
 
|-
 
|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 300:
 
|-
 
|-
 
|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 312:
 
|-
 
|-
 
|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 344:
 
|-
 
|-
 
|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 368: Line 368:
 
|-
 
|-
 
|11:03
 
|11:03
−
|3. Get a list of member names and their phone numbers, who need to return books today
+
|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 More Queries in SQL View LibreOffice Base  
+
|This brings us to the end of this tutorial on more queries in '''SQL View''' LibreOffice Base.
  
 
|-
 
|-
Line 390: Line 390:
 
|-
 
|-
 
|11:43
 
|11:43
−
|Create Queries in SQL View
+
|* Create Queries in SQL View
  
 
|-
 
|-
 
|11:47
 
|11:47
−
|Use ORDER BY clause
+
|* Use ORDER BY clause
  
 
|-
 
|-
 
|11:49
 
|11:49
−
|Use JOINS
+
|* Use JOINS
  
 
|-
 
|-
 
|11:51
 
|11:51
−
|Use Aggregate functions
+
|* Use Aggregate functions
  
 
|-
 
|-
 
|11:54
 
|11:54
−
|Use the GROUP BY clause
+
|* Use the GROUP BY clause
  
 
|-
 
|-
 
|11:57
 
|11:57
−
|And use built in Functions
+
|* And use built in Functions.
  
 
|-
 
|-
 
|12:00  
 
|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 co-ordinated by http://spoken-tutorial.org. More information on the same is available at the following link.
+
|'''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
+
  
 
|-
 
|-

Revision as of 16:48, 30 September 2015

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

Retrieved from "https://script.spoken-tutorial.org/index.php?title=LibreOffice-Suite-Base/C3/Create-simple-queries-in-SQL-View-II/English-timed&oldid=23835"