Ch 11 Queries in Libre Office Base NOTES Important Points

Share with others

Ch 11 Queries in Libre Office Base NOTES Important Points

Ch 11 Queries in Libre Office Base NOTES Important Points

Introduction

A database is used to store data in an organized manner. Queries are used to retrieve the desired data easily and accurately from database. In other words we can say that a query is a sort of question asked from a database.

A query is one of the most important feature of any DBMS. Using a query, we can retrieve and display data from one or more tables in a database.

LibreOffice Base allows us to create a query and even save it as an object in a database. This helps us to run the query multiple times as and when required.

Using a query, we can specify the fields that we want to display and also the criterion based on which the records to be filtered. For example In a Student database, if we want to display the names and marks of the students in a particular class from the Result table, who have scored less than 320 aggregate marks.

Queries

A query is to collect specific information from the pool of data. A query helps us to join information from different tables and filter that information

Queries are commands that are used to define the data structure and also to manipulate the data in the database.

Creating a Query

A query can be created in three ways. In this chapter we will learn the first two methods to create a query.

Using a Wizard
In Design View
In SQL view

Creating a Query Using a Wizard

Consider the following table: Table1 (created in database: School)

Rno	Name	Class	Fees
1	Anil	X	2500
2	Anuj	XI	3000
3	Ashish	XII	3200

Let us create a query that will display Roll number, Name, Class from table “Table1” whose Roll number is 3.

To create a query using a wizard, follow the following steps.

1. Open the School database. Click on Queries button present in the Database Pane.

2. In the Tasks Area, click on Use Wizard to Create Query… option

3. The Query Wizard will open.

4. Select the required table and then select the required fields(Name, Class and Rno) from the “Available fields” window to “Field in the Query” window and click on Next Button.

NOTE: Clicking on >> button moves all fields to “Fields in the Query” area.

6. Select the ascending or descending order of any particular field of the table. Since we do not want to set in a particular order, so we click on Next button.

7. The next step is to set the search conditions or the criteria on the basis of which records will be filtered from the table.

NOTE: Since we want to display the detail of student having roll number 3 so we set roll number as 3 in above step

9. Steps 4, 5 and 6 given in the Steps Pane are not required if there is no numeric field involved in the query. So skip these steps.

10. The next step to give alias name i.e. the column header name will be displayed when we run the query. Click on Next after writing alias name.

NOTE: By default the field names will be displayed as column headers. Some times field names are not user friendly, so an alias name which is more readable is required. For example change the default field name Rno to Roll Number.

11. The last step of the Query wizard displays the entire overview of the query.

12. Click on Finish button.

NOTE:

Once the query is created, it can be edited in Design view.

NOTE: Last Step of Query Wizard includes the following (Query Wizard Overview).

Name of the Query – By default, the name of the query is Query_Table1.

The action to be performed after the wizard finishes – By default Display Query option will be selected. Click and select the Modify Query radio button if the query has to be edited in the Design view.

Complete detail of the query – This section contains a summary about the query that has been created.

Creating a Query in Design View

Another way to create a query is using the Design view. This is a more flexible method.

Consider the following table: Table1 (created in database: School)

Rno	Name	Class	Fees
1	Anil	X	2500
2	Anuj	XI	3000
3	Ashish	XII	3200

Let us create a query that will display Roll number, Name, Class from table “Table1” whose Roll number is 3

1. Click Queries icon on the Objects Pane in the Database Window.

2. Click Create Query in Design View… icon in the Tasks Pane. The Query Design Window appears. In the middle of the window the Add Table or Query dialog box is displayed as shown below

3. Click on the “Table1” table to be used in the query and then click on Add Button. Alternatively double click on the “Table1” table.

4. Click Close button in the Add Table or Query dialog box to close it.

5. Next step is to select the fields. For our query we want to display Roll number, Name and Class. So in the list box of “Table1” table, double click on the required field.

NOTE: Observe that the Visible Check Box is by default selected. This means that all these three fields will be visible when you run the query. In the grid, there is a row titled Alias. It can be used to display meaningful names in the output. For example, instead of Rno, we would just like to display Roll Number.

To sort the records in either ascending or descending order of a particular field, the Sort row is given in
the grid. Select ascending or descending from the drop down of Sort row.

6. Write 3 in Criteria row below the Roll number Column.

NOTE: Once the query is designed, click Run Query ( ) button on the toolbar or press F5 key. The query result will be displayed in the Tables Pane area.

7. Click on Save button to save the query.

NOTE: By default, the name of the Query is Query1. To run the query again, double click on the query name. To close the Query window, click on on close button on the top right corner of the window.

Editing a Query

1. Right click on the Query Name Which you want to edit in the Objects Area of the Database window.

2. Select Edit option from the drop down menu. The Query Design window will be displayed.

3. Make the required changes like change the Alias, apply criteria or edit the existing criteria etc.

4. Click on Save and close the Query window.

Working with Numerical Data

Consider the following table: Table1 (created in database: School)

Rno	Name	Class	Fees
1	Anil	X	2500
2	Anuj	XI	3000
3	Ashish	XII	3200

Let us create a query that will display maximum fees from the table “Table1”.

1. In the Database Window, click Create Query in Design View… button to open the Query Design Window.

2. Select the required field. For our query we want to display Fees. So in the list box of “Table1” table, double click on the required field.

3. Select the function “Maximum” from the drop down list of functions under “Fees” Column as shown below.

4. Press F5 to run the query.

SUMMARY

• A query is used to retrieve and display data from one or more tables in a database.

• A specific search criteria is given to the DBMS to view the desired information.

• The result of the query is displayed in tabular form with field names in columns and the records in rows.

• A query can be created in three ways.
■ Using a Wizard
■ In Design View
■ In SQL view

Important links of Class X (IT – 402)

Disclaimer : I tried to give you the simple Notes of “Ch 11 Queries in Libre Office Base NOTES Important Points“, but if you feel that there is/are mistakes in the Notes of “Ch 11 Queries in Libre Office Base NOTES Important Points“ given above, you can directly contact me at csiplearninghub@gmail.com. NCERT Book and Study material available on CBSE Website are used to create above article “Ch 11 Queries in Libre Office Base NOTES Important Points“ All the screenshots used in above article “Ch 11 Queries in Libre Office Base NOTES Important Points“ are taken from NCERT book.