SQL View Method:
A query may be directly specified in Select Query Pane by a right click at table pane % SQL view. The upper and lower panes of selected query window are substituted by a pane to specify the SQL Statement that is written by using keyboard.
The desired SQL statement is directly okeyed in on this pane and saved in the same manner as described for design method. While forming the SQL statement, the following clauses are normally used for generating information (or Select) queries.
1. SELECT:
This clause is used to specify the fields to display data or information. Consider the following SQL statement segment.
SELECT code, Name, Amount
The fields Code, Name and Amount after SELECT clause indicate the data items to be displayed by the query statement.
2. FROM:
This clause is meant to indicate the source of data in terms of tables or queries or a combination of both. Two tables are joined by specifying a JOIN Clauuse based on a condition of Join. There can be three types of Join, Inner, Left and Right.
3. INNER:
This join clause is meant to display only exactly matching records between two data sources. Consider the following SQL statement segment.
FROM Accounts INNER JOIN Account Type
ON (Catld = Type)
In the above statement, only those records of Accounts and Account Type table constitute the source of query data, which match exactly on Catld = Type.
4. LEFT:
With this Join, all the records in the primary table in the relationship are displayed irrespective whether there are matching records in the related table or not. Consider the following SQL statement segment.
FROM Accounts LEFT JOIN Account Type
ON (Catld = Type)
In the above statement, all records of Accounts along with matching records of Account Type table constitute the source of query data, the matching condition is Catld = Type.
5. RIGHT:
With this join, all the records of related table in the relationship are displayed irrespective whether there are matching records in the primary table or not. Consider the following SQL statement.
FROM Accounts RIGHT JOIN Account Type
ON (Catld = Type)
In the above statement, all records of Account Type along with matching records of Accounts table constitute the source of query data. The matching condition is Catld = Type.
6. WHERE:
This clause in SQL statement is used to provide the condition to restrict the records to be returned by query. The resultant records of query must satisfy the condition which is specified after WHERE clause. This is meant to filter records returned by the query.
7. ORDER BY:
This clause is meant to specify the order in which the resultant records of query are required to appear. The basis of ordering is determined by the list of fields specified after the order by clause. Consider the following SQL statement segment.
ORDER BY Type, Code
The above statement in the context of Accounts table implies that the resultant record set is ordered by the Type field of Accounts and within Type, by Code field of Accounts.
8. GROUP BY:
The group by clause is used in the SQL statement to enable grouping of records for creating summary query. The fields after GROUP BY clause constitute the basis of grouping for which summary results are obtained. Consider the following SQL statement.
SELECT |
Debit sum (Amount) |
FROM |
Vouchers |
Group By |
Debit |
In the above SQL statement, the GROUP BY clause uses Debit account codes as the basis for computing the sum of amount of voucher. The total amount, by which every transacted account has been debited, is given by this SQL statement. In this statement, sum of amount is found for each group of records formed using GROUP BY clause.