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

From Script | Spoken-Tutorial
Revision as of 14:19, 23 March 2017 by PoojaMoolya (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
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:09 Create simple queries in SQL View, write simple SQL.
00:16 Use SELECT, FROM and WHERE clauses
00:20 And choose upper lower or mixed cases for naming fields and tables .
00:27 Before using Base to create queries in SQL View, let us talk about LibreOffice Base.
00:35 Base runs on the HSQL database engine.
00:41 This is an open source database engine software written in Java. Go to: http://hsqldb.org for more information on HSQLDB.
01:02 Okay, now let us learn about SQL.
01:06 SQL stands for 'Structured Query Language'. It is a standard language for accessing and manipulating databases.
01:17 It is an internationally accepted ANSI standard.
01:23 And so, it is used in a variety of 'Database Management Systems' or 'DBMS'.
01:31 Some examples are our own LibreOffice Base, MySQL, Microsoft SQL Server, Microsoft Access, Oracle and DB2.
01:47 The most common use of SQL is to retrieve data from a database which is also known as querying a database.
01:58 SQL can also be used for inserting data into a database, updating data or deleting data from a database.
02:09 And we have done all of these operations using Base, in our previous tutorials,
02:16 by using the very user friendly wizards and designing windows.
02:22 But knowing the underlying query language gives us more flexibility and power to query the database And SQL can also be used not only to modify data but also to modify database and table structures.
02:43 Since our tutorial does not cover all about SQL, here are some useful tutorials and their websites. <pause>.
02:59 HSQLDB has its own user guides. They can be viewed online Or they can be downloaded and saved into your computer as a PDF file.
03:14 Okay, let us learn some SQL now. We will open our familiar Library database example.
03:23 Let’s open our 'Library' database. Now, let us click on the Queries list on the left panel
03:34 and then click on ‘Create Query in SQL View’. Now we see a blank window titled Query Design
03:46 and this is where we will type in our queries in SQL.
03:51 Let us write our first simple query. And that is: Get information about all books in the library. Simple..
04:02 Any retrieval needs to use a SELECT keyword. And so, we will write our query as-
04:10 SELECT * FROM Books.
04:15 Here, Books is the table name. Notice the capital 'B' in books.
04:23 We will faithfully follow the table or column names we used earlier.
04:29 And '*' is a wild card. Here, it means- get all the fields or columns from the Books table.
04:39 Now, let us execute or run it. Click on Edit menu and then click on Run Query.
04:48 Now we see a top panel with a list of records on books.
04:53 We can save this query or any query we write and give them descriptive names.
05:00 So, there is our first simple query! Here are some tips:
05:06 HSQLDB is case sensitive with its Database object names such as tables and column names.
05:17 Meaning- Table name “Books” with a Capital 'B' is not the same as “books” with a small 'b'.
05:27 But, for convenience we can use all upper cases or all lower cases.
05:34 For example: 'BOOKS' in capital letters, or 'members' in small letters etc.
05:44 But, using mixed cases can be easy to read and understand. For example: 'BooksIssued', with a capital 'B' and 'I'.
05:57 Or 'ReturnDate' with a capital 'R' and 'D'.
06:03 So, we have to use the table names and column names just the way they are created.
06:11 For SQL keywords like SELECT, we can use any case or mix cases. But, let us be uniform in our usage for better readability.
06:25 In our examples, we will use all upper cases for keywords.
06:31 Now, onto our next query. We can type this query in a new window or we can overwrite it on the previous query.
06:42 For now, let us overwrite it on the previous query.
06:47 Let us retrieve specific columns from the Books table.

SELECT Title, Author FROM Books.

06:58 And run the query. We can also use the Run Query icon below the file menu bar or use the keyboard shortcut F5.
07:13 And there are our records with only those columns that we needed.
07:19 Okay. Let us move on.
07:22 Let us introduce conditions or criteria for our query.
07:27 We will retrieve only those books published by Cambridge.
07:31 And so, our query is: SELECT * FROM Books WHERE Publisher = 'Cambridge'.
07:46 Notice that we have introduced a new keyword WHERE
07:52 followed by a condition where we say 'Publisher' equals 'Cambridge'.
07:59 Let us run our query now and we see only those books for which the publisher is 'Cambridge'.
08:08 And so, we can have any number of conditions or criteria in a query.
08:14 Let us write a query with two conditions.
08:18 Let us retrieve only those books published by Cambridge and only those that were published after the year 1975.
08:29 And our query is: SELECT * FROM Books WHERE Publisher = 'Cambridge' AND PublishedYear > 1975.
08:49 And we see two conditions after the WHERE keyword or clause.
08:55 Notice that they are put together using ‘AND’. Here ‘AND’ is called a logical operator
09:04 and here it serves to combine conditions. ‘OR’ is another logical operator.
09:13 Explore these by using them in the above query later.
09:18 Let us now run the query and see the results at the top.
09:23 There, these are the books that met our conditions.
09:29 Okay, let us also learn another way to include multiple conditions.
09:36 How can we get a list of only those books for which the publisher is Cambridge or Oxford or both?
09:46 And here is our query: SELECT * FROM Books WHERE Publisher IN ( 'Cambridge', 'Oxford').
10:09 Notice the new keyword ‘IN’.
10:13 It helps to combine conditions based on a single column- in this case, the Publisher.
10:21 And notice the results now.
10:25 Here is an assignment:
10:27 Write and test your SQL queries for the following:
10:33 1. Get information about all the members in the Library.

2. Get a list of all book titles which are priced more than Rs 150. 3. Get a list of books which were written by William Shakespeare or John Milton.

10:56 Let us learn more about SQL in the next tutorial.
11:01 This brings us to the end of this tutorial on Queries in SQL View in LibreOffice Base.
11:09 To summarize, we learned how to:
11:12 Create simple queries in SQL View.
11:17 Write simple SQL
11:20 Use SELECT, FROM , and WHERE clauses
11:25 And choose upper, lower, or mixed cases for naming fields and tables.
11:35 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.
11:47 This project is coordinated by http://spoken-tutorial.org. More information on the same is available at the following link.
11:55 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