Discuss the SQL view method of creating a query?
Discuss the SQL view method of creating a query?
The following clauses are used for generating information queries using SQL statements.
1. SELECT: This clause specifies the fields to display data or information. For example SELECT Code, Name, Amount This statement indicates that the fields Code, Name and Amount are to be displayed by the query statement.
2. FROM: This clause specifies the source of data in terms of tables or queries or a combination of both. By specifying the JOIN clause, two tables are joined. Join clause can be of following three types.
a) INNER- This Join clause displays only those records that are exactly matching between two data sources. For example, FROM Accounts INNER JOIN AccountType ON (CatId = Type) Here, only those records of Accounts and AccountType table are a part of source of query data, which match exactly on CatId = Type.
b) LEFT- This JOIN clause displays all the records in the primary table in a relationship irrespective of whether there are matching records in the related table or not. For example, FROM Accounts LEFT JOIN AccountType ON (CatId = Type) Here, all the records of Accounts along with the matching records form a part of the source of the query data.
c) RIGHT- This JOIN clause displays all the records of related table in a relationship irrespective of whether there are matching records in the primary table or not. For example, FROM Accounts RIGHT JOIN AccountType ON (CatId = Type) Here, all records of the AccountType along with the matching records of the Accounts table form a part of the source of query data.
3. WHERE: This clause provides the condition to restrict the records to be returned by the query. The result records of the query must satisfy the condition which is specified after the WHERE clause.
4. ORDER BY- This clause specifies the order in which the resultant records of the query are required to appear. The basis on which this ordering is done is determined by the list of the fields specified after the ORDER BY clause. For example, ORDER BY Type, Code This statement implies that the resultant record set is ordered by the 'Type' field of Accounts and within Type, by 'Code' field of Accounts.
5. GROUP BY- This clause enables the grouping of records for creating summary query. The fields specified after the GROUP BY clause form the basis of grouping for which the summary results are obtained.
For example,
SELECT Debit, Sum (Amount)
FROM Vouchers
GROUP BY Debit
Here, the GROUP BY clause uses debit account codes for calculating the sum total of the amount of the vouchers.
The following clauses are used for generating information queries using SQL statements.
1. SELECT: This clause specifies the fields to display data or information. For example SELECT Code, Name, Amount This statement indicates that the fields Code, Name and Amount are to be displayed by the query statement.
2. FROM: This clause specifies the source of data in terms of tables or queries or a combination of both. By specifying the JOIN clause, two tables are joined. Join clause can be of following three types.
a) INNER- This Join clause displays only those records that are exactly matching between two data sources. For example, FROM Accounts INNER JOIN AccountType ON (CatId = Type) Here, only those records of Accounts and AccountType table are a part of source of query data, which match exactly on CatId = Type.
b) LEFT- This JOIN clause displays all the records in the primary table in a relationship irrespective of whether there are matching records in the related table or not. For example, FROM Accounts LEFT JOIN AccountType ON (CatId = Type) Here, all the records of Accounts along with the matching records form a part of the source of the query data.
c) RIGHT- This JOIN clause displays all the records of related table in a relationship irrespective of whether there are matching records in the primary table or not. For example, FROM Accounts RIGHT JOIN AccountType ON (CatId = Type) Here, all records of the AccountType along with the matching records of the Accounts table form a part of the source of query data.
3. WHERE: This clause provides the condition to restrict the records to be returned by the query. The result records of the query must satisfy the condition which is specified after the WHERE clause.
4. ORDER BY- This clause specifies the order in which the resultant records of the query are required to appear. The basis on which this ordering is done is determined by the list of the fields specified after the ORDER BY clause. For example, ORDER BY Type, Code This statement implies that the resultant record set is ordered by the 'Type' field of Accounts and within Type, by 'Code' field of Accounts.
5. GROUP BY- This clause enables the grouping of records for creating summary query. The fields specified after the GROUP BY clause form the basis of grouping for which the summary results are obtained.
For example,
SELECT Debit, Sum (Amount)
FROM Vouchers
GROUP BY Debit
Here, the GROUP BY clause uses debit account codes for calculating the sum total of the amount of the vouchers.
