build apps by user

SQL – Select, Insert, Update, Delete

SQL means Structured Query Language. It is similar to English and is therefore very easy to use for interacting with the structured data in database. You can use SQL to read, add, update and delete of data. There are different kinds of SQL. The SQL used in BAU DB is from Microsoft Access.

Select Formula :
Retrieving Rows of Data from Database Tables

Select formula is used when you want to retrieve some data from the database. It lets you retrieve rows of values from database tables based on certain criteria. Apart from getting a value directly from a table field, you can also perform calculation in a Select formula.

Format (Syntax)

Select table.field(s) From table(s) Where criteria Order By table.field(s)
Select ClauseSelect table.field(s)
It defines the fields, or columns, in the tables that you want to retrieve. Calculations can be performed on the table fields.
From ClauseFrom table(s)
It defines the database tables from which to get the data. All table.fields in Select clause must be present in the tables defined in From clause.
Where ClauseWhere criteria
It defines some comparison expressions as the conditions to select the rows of data, or records, from the tables. There can be no Where clause in a Select formula.
Order By ClauseOrder By table.field(s)
It defines the displaying order of the rows of data, when the Select clause retrieves more than one row of data. It defines a list of table fields so that the rows of data are sorted column by column.
The keywords Asc and Desc are added beside each field to be sorted in ascending and descending order respectively.

Example 1

Select CustName, Address, Person, Email From Customer
It retrieves some fields from the Customer table. Since no condition is added in the formula, all rows of Customer records from the table are returned in the result set.

Example 2

Select CustName, Address From Customer Where Business="Automobile"
A comparison expression is added in the Where clause. It only retrieves Customer records in the Automobile Business.

Example 3

Select Business, CustName, Person, Email From Customer Order By Business, CustName Desc
The Order By clause controls the displaying order of the retrieved data. The column Business is sorted in ascending order and the column CustName is sorted in descending order. Since the default sorting order is ascending, you can freely choose to add the keyword Asc or not.

Example 4

Select Customer.CustName, Person, ProjName From Customer, Project Where Customer.CustName=Project.CustName And Customer.Business="Automobile"
It retrieves data from both Customer and Project tables. The 2 tables are joined by the common field CustName.
Since the field CustName exists in both tables, table name must be specified together with field name – Customer.CustName and Project.CustName, in the formula.
This example also illustrates the use of AND/OR logical operators when the Where clause has more than one logical condition.

Example 5

Select CustName, Address + ", " + Email From Customer
This example illustrates you can perform some operations on the retrieved data in a Select formula. You want to display the Address field and the Email field in a single column, and they are concatenated together with a comma.

Example 6

Select CustName, Left(Address, 10) + "..." + Right(Address, 12) From Customer
This example demonstrates the use of SQL functions to manipulate data in the formula. Left() and Right() are the SQL functions. Left(Address, 10) returns the 10 left most characters of Address, and Right(Address, 12) returns the 12 right most characters of Address. They are combined together into a single column in the output result.
The above examples show that SQL formula is simple and easy to use, and yet powerful in manipulation of data.