Comprehensive Notes on Retrieve data using Query in base Class 10

In this article, you will find the Comprehensive Notes on Retrieve data using Query in base Class 10. This is the learning outcome 4 of Unit 3 Database Management System for IT 402 class 10. So let’s start now!

Retrieve data using Query in base

As you are aware now how to create database and tables in OO base. The next and very important object of the database is the query. So let me introduce what is a query?

Database Query

A query is used to fetch data from records and display them in desired format.

Query help you to perform searching in the database and returning the matching record from it. It can be run on multiple tables as well. You can filter the results using criteria and display specific results. Base provides an option to choose criteria for your search. So now let’s start how to use the query in Base for Retrieve data using Query Class 10.

Defining Query

A query is a command of DBMS system that retrieves data from the one or more database tables by specifying some criteria.

Base provides three ways to perform a query:

1. Query using wizard
2. Query in design view
3. Query using SQL view

So let’s start with query creation using wizard for Retrieve data using Query in base Class 10. The first way to Retrieve data using Query in base is query creation using wizard.

Query creation using the wizard

As you know wizard is a step by step process to complete a specific task. You can perform query using wizard in OO Base. The steps are as following:

1. Open your database and select Queries from object pane.
2. Click on Use Wizard to Create Query option from the Tasks window.
3. Now Query Wizard will open. This query wizard has 8 simple steps.
   1. Field Selection – This step allows to select the required fields from the table. Select the table and click on the required fields then click on the > button. Click on Next to go ahead.
   2. Sorting Order – You can sort the records using this step. There are four options to sort the records with different fields. Select the column name and choose the order Ascending or Descending order. Click on next.
   3. Search Conditions – In this step you are allowed to select specific conditions or criteria like Match all fields for all records or match any of the following then Select the field and conditions such as is equal to, is not equal to, is smaller than, is greater than, is equal or less than, is equal or greater than, like, not like, is null, is not null and enter the value at the last. Click on Next.
   4. Detail or summary – It will show the detail or summary for your query results. Click on Next.
   5. Grouping – If you want to group the records according to certain values this option is used. By default this option is disabled.
   6. Grouping Conditions – If you have applied grouping in step 5, then only this option will appear.
   7. Aliases – This option allows you to choose the alias names from your selected fields. Type the name in the box and click on Next.
   8. Overview – This step displays the overview of your query. You can display the query result or modify the query. If everything is fine, click on Display query button. Finally, click on the finish button to show the result.

Here I have taken emp table for queries. Let’s start!

Steps:

[1] Select Query from Database objects window.

[2] Select Use wizard to Create Query option. The Query Wizard will appear.

[3] Choose the table – EMP and desired fields (EMPNO, ENAME, DEPTNO and Salay) as follows:

[3] Click on Next button. Now this step prompt to sort the record. Select the sort order (Ascending or Descending). Click on Next button.

[4] Specify the condition on desired field. Click on next field. I have given condition here as salary >1500.

[5] Now it will ask for aggregate function which is skipped in my query. Click on Next button.

[6] Specify the alias names for the selected fields and click on next.

[7] Type the desired Query name and Click on Display Query radio button. Click on Finish button.

[8] The result will be displayed immediately after saving query.

The another way to perform query in Retrieve data using Query Class 10 is Query using design.

Query in design view

Follow the given steps to perform query in design view:

1. Open your database and select Queries from the object pane window. A new window appears to add tables for your query.
2. Select the table and click on Add Tables option.
3. Now come to fields row at the bottom given table and choose whatever field you need to display in the query results.
4. If you want to use any alias for your fields type name exact below the field name.
5. The next option is table, this is not mandatory when you are working with a single table.
6. After then move down to the next row to sort your records. Just select go to the row where you want to sort the records and select it.
7. Next is function, whenever you want to perform calculation or want to use some functions you can select this option.
8. Next row is Criterion, which is very important to display the records according to the criteria. Here you can use the comparison operators like >,<,>=,<=,= or != etc.
9. Then number of criteria you can add as per the requirements.
10. Now finally save your query and view the results.

Let us see the example:

Displaying all records from EMP table

[1] Select Query from Database window.

[2] Click on Create Query in Design View… from Tasks pane.

[3] A new Query Design window will appear with Add Table or Query dialog box. Click on Table radio button, Select table from the list, here EMP is selected then click on Add button.

[4] Select EMP.* in the row Field.

[5] Click File > Save option to save the query. A Save As dialog box appear. Type desired name in the Query Name box. Click on OK button.

Specifying criterion in design view

Display empno, ename and salary of employee whose salary is more than 1500.

[1] Follow the steps from step 1 to step 3 as same as above.

[2] Select fields in the row field as EMPNO, ENAME and SALARY and type >1500 in Criterion.

[3] Save the query.

Query Output

Sorting Data in LibreOffice Base using Query in Design View

Sort the employees data in ascending order of names.

[1] Add the table as above

[2] Select columns to be displayed from fields

[3] Select Sort option in the respective column. I have done it with Ename field.

[4] Save the query

Query Output:

Using functions in LibreOffice base Query Design

Display maximum salary

[1] Add the table

[2] Specify the desired columns

[3] Go to function the Function field in Salary column and select Maximum

[4] Save the query

Query Output:

Now in the next section of Retrieve data using Query Class 10 we will talk about Create Query in SQL View.

Create Query in SQL View

This option allows using SQL command to display the results. As you are familiar with DDL and DML commands in OO base, Select is one type of DML command that allows displaying records from a table. Follow some OO base typing etiquettes while using SQL view for Retrieve data using Query Class 10. So let’s see the complete process of how to use select in SQL view:

The syntax of select is something like this:

select */columns_list from table_name where conditions

In the above SQL statement,

• select is the command used for data retrieval
• */column list is used to specify all columns or specified columns from the table
• from is, keyword specifies the name of the table to the next
• table_name is a table from where you want to fetch the records
• where is again keyword that restricts your records from the table according to conditions
• condition use column name and specifies the column name and criteria

To understand the SQL view for Retrieve data using Query Class 10 consider the following.

There are four ways you can use select query:

1. All records
2. All records with limited columns
3. Limited records with all columns
4. Limited records with limited columns

Now let’s see all of these one by one to understand Retrieve data using Query Class 10.

All records

Whenever you want to fetch all records from table you can use this option. The syntax and example are as following:

select * from "emp"

All records with limited columns

Whenever you want to fetch all records with limited columns from table use specific column names with select clause. Consider the following example:

select "empcode","ename","job" from "emp"

Limited records with all columns

Whenever you want to access all columns * is used after select clause and to limit records, where condition is used. With where condition some relational operators are used as per the requirement. These operators are:

1. < : Display the records less than the specified column value.
2. > : Display the records more than the specified column value.
3. <=: Display the records less than or equal to the specified column value.
4. >=: Display the records greater than or equal to a specified column value.
5. = : Display the records equal to a specified column value
6. <>: Display the records no equal to the specified column value.
7. like: This operator is used to pattern matching criteria like the character is starting with, ends with or have a specific position in the value. The ‘%’ is used to specified any number of character whereas ‘_’ is used to specify the position of characters. Suppose you want to search a record whose fourth letter of name is ‘i’ and last character it ‘T’, then the query will be written as – select * from “emp” where “ENAME” like ‘___I%T’.
8. not like: Display all the records except the specified pattern matching
9. is null: Display records with null values
10. is not null: Display records except null

select * from "emp" where "empno"<1020

select * from "emp" where "empno">1015

select * from "emp" where "job"<>'MANAGER'

select * from "emp" where "ename" like 'M%T'

select * from "emp" where "ename" is null

select * from "emp" where "ename" is not null

Limited records with limited columns

Specify the column names after select and use where condition to restrict them.

selected "empno","ename" from "emp" where "ename"='MOHIT'

The next section of Retrieve data using Query Class 10 is sorting records.

Sorting

To sort records using SQL order by clause is added to your query. Observe following query:

select * from "emp" order by "ename"

Sometimes you need to group records which is our next topic for Retrieve data using Query Class 10.

Grouping Records

To group records group by clause is used. Observe the following query:

select "job",count("job") from "emp" group by "job"

That’s all from Retrieve data using Query Class 10. I hope you enjoyed this article Retrieve data using Query Class 10. If you have any doubt or query regarding Retrieve data using Query Class 10 you can post them in the comment section.

Share your feedback/ View/ Suggestions in the comment section to provide more better content.

Follow this link to refer NCERT Solutions Chapter 11 Queries in base.

Follow this link for more important questions on Queries in base.

Thank you for visiting our blog.