RETRIEVE DATA USING QUERY

Creation Of Query Using Design View

Some RDBMS provide a graphical means to create queries, but most RDBMS do not do so. That’s where you use SQL (pronounced as “sequel”) or Structured Query Language. Query languages are computer languages used to make queries into databases and information systems. Queries are commands that are used to define the data structure and also to manipulate the data in the database.

A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language(DML) command.

The SELECT statement has many optional clauses:

• WHERE specifies which rows to retrieve.

• ORDER BY specifies an order in which to return the rows.

To retrieve all the columns in a table the syntax is:

SELECT * FROM <TABLENAME>;

In order to execute queries click on the Queries option available on the left side under

database section, click Create Query in SQL View as shown below A window appears similar to the one displayed below. You can type the query in the above window and execute it by using the F5 function key or by clicking the icon in the window. 

For example, if you want to display all the data in the table that you created in the early session, then the select statement will be:

Select * from Details;

After executing the select query the output will be shown similar to the one displayed below.

Performing calculation

In Base, simple calculations can be done on the data using arithmetic operators.

Example: To display the salary of all the employees after incrementing by 1000 then the

following SQL command will be executed in Base SQL Design

Select “Employee ID”, “FirstName”, “Salary” +1000

from “Employee;

To display the salary of all the employees after decreasing by 10000 then the following SQL command will be executed in Base SQL Design Select “EmployeeID”, “FirstName”, “Salary” - 10000 from “Employee”

To display the salary of all the employees after incrementing it as twice the amount of present salary, then the following SQL command will be executed in Base SQL Design.

Select “EmployeeID”, “FirstName”, “Salary” * 2 from “Employee”

To display half of the salary amount paid to the employees, then the following SQL command will be executed in Base SQL Design.

Select “EmployeeID”, “FirstName”, “Salary”/2 from “Employee”

Grouping of Data

To display the records containing the same type of values “WHERE” clause can be used

with the Select SQL Command.

To get details about the list of students whose favorite color is blue, you can use:

select * from SDetails where Color=’Blue’;

After executing the select query the output will be shown similar to the one displayed

below. To view records in ascending order of RollNo, from the table the select statement will be:

select * from SDetails order by “Rollno” ASC; You can add, modify or delete records using the Insert, Update and Delete commands.

To type and execute SQL commands, click on Tools > SQL. A window similar to the one

below will be displayed. You can type the SQL Commands in the Command to execute space and click on Execute.

 UPDATE Statement

Update statement is used for modifying records in a database. The general syntax of the update statement is as follows:

UPDATE <table_name>

SET <column_name> = value [, column_name = value ...]

[WHERE <condition>];

To update a record using an update statement, type the following and click Execute. Update SDetails set Location = ‘Bhubaneswar’ where Rollno = 14; Execute select query to view the updated table. After execution you should see a window similar to the one displayed below.