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

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 under lying query language gives us more flexiblity 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 co-ordinated 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