Retrieve Data using the Query | Information Technology for Class 10 PDF Download

Database Query

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. OO base provides an option to choose criteria for your search. So now let’s start how to use the query in OO Base for Retrieve data using Query.

Defining Query

A query is a command of DBMS system that retrieves data from the database table.

OO Base provides three ways to perform a query:

• Query using wizard
• Query in deisgn view
• Query using SQL view

So let’s start with query creation using wizard for Retrieve data using Query. The first way to Retrieve data using Query 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.
   • 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.
   • 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.
   • 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.
   • Detail or summary – It will show the detail or summary for your query results. Click on Next.
   • Grouping – If you want to group the records according to certain values this option is used. By default this option is disabled.
   • Grouping Conditions – If you have applied grouping in step 5, then only this option will appear.
   • Aliases – This option allows you to choose the alias names from your selected fields. Type the name in the box and click on Next.
   • 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.

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

Query in design view

Follow the given steps to perform query in design view:

• Open your database and select Queries from the object pane window. A new window appears to add tables for your query.
• Select the table and click on Add Tables option.
• Now come to fields row at the bottom given table and choose whatever field you need to display in the query results.
• If you want to use any alias for your fields type name exact below the field name.
• The next option is table, this is not mandatory when you are working with a single table.
• 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.
• Next is function, whenever you want to perform calculation or want to use some functions you can select this option.
• 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.
• Then number of criteria you can add as per the requirements.
• Now finally save your query and view the results.

Now in the next section of Retrieve data using Query 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. 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 consider the following.

There are four ways you can use select query:

• All records
• All records with limited columns
• Limited records with all columns
• Limited records with limited columns

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

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'

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.

Grouping Records

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

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