1
Question
Describe the series of SQL statements to produce trial balance data base design for Model-II.
Open in App
Solution
Model-II The following series of SQL statements retrieve the record set for producing trial balance when database design for Model-II is used. In addition to this, the accounts have been categorised within the trial balance according to the Account Type: Expenses, Revenues, Assets and Liabilities.
(i) To Find Total Amount by Which the Accounts have been Debited The transacted accounts in design of Model-II have been stored in AccCode of Vouchers Main and Code of Vouchers Detail.
The following SQL statement is formed to generate the relevant information from Vouchers Details.
SELECT Code, Sum (amount) AS Total
FROM vouchersMain INNER JOIN vouchers Details ON vouchesMain. Vno= VouchersDetails. Vno
WHERE Type=0
GROUP BY Code;
Similarly, the following SQL statement is formed to generate the required information from Vouchers Main table.
SELECT AccCode AS Code, sum (amount) AS Total
FROM vouchersMain INNER JOIN vouchersDetails ON vouchersMain.Vno = VouchersDetails. Vno
WHERE Type=1
GROUP BY AccCode;
Both the SQL statements are meant to extract similar sets of records, but from two different sources. Therefore, the resultant record set of these SQL statements have been horizontally merged using UNION clause as shown below
SELECT Code, sum(amount) AS Total
FROM vouchersMain INNER JOIN vouchersDetails ON vouchersMain.Vno= VouchersDetails.Vno
WHERE Type=0
GROUP BY Code
UNION ALL
SELECT AccCode AS Code, sum(amount) AS Total
FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain.Vno = VouchersDetails. Vno
WHERE Type=1
GROUP BY AccCode;
The above SQL statement is saved as Query 101 for its subsequent use. The total of debit amount in this query represents the Total with positive amounts.
(ii) To Find the Total Amount by Which the Accounts have been Credited In order to ascertain the total amount by which every transacted account has been credited, a query similar to that in (a) need to be formed. This is achieved by substituting Debit field in SELECT and GROUP BY clause by Credit field and the sum of amount generated by sum(Amount) is multiplied by −1 so that the final amount assigned to Total field is always negative. Accordingly, the following SQL statement is formed
SELECT Code, sum(amount)∗−1 AS Total
FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain. Vno= VouchersDetails. Vno
WHERE type=1 GROUP BY Code, Amount UNION
SELECT AccCode AS Code, sum(amount)∗−1 AS Total
FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain. Vno = VouchersDetails.Vno
WHERE Type=0
GROUP BY AccCode, Amount;
In the above SQL statement, the sum of amount has been multiplied by −1 to ensure that the amount of credit is always negative just as amount of debit is taken as positive. This query is saved as Query 102 for its subsequent use.
(iii) To Find a Collective Record Set of Accounts with their Debit and Credit totals
A collective record set is generated by forming a union query between Query 101 and Query 102 to ensure that the debit and credit amount with respect to each account becomes available for generating the net amount.
Accordingly, the following SQL statement is formed.
SELECT
FROM Query 101
UNION Select∗
FROM Query 102;
The above SQL statement causes horizontal merger of record sets returned by Query 101 and Query 102. This SQL Statement is saved as Query 103 for its subsequent use in next query.
(iv) To Find the Net Amount with which an Account has been Debited or Credited
To generate the net amount, an SQL statement similar to Query 04 (designed for query (d) of Model-I) above, is formed as shown below, except that its source of data is Query 103 instead of Query 03.
SELECT Code, Sum (Total) AS Net
FROM Query 103
GROUP BY Code;
This query is saved as Query 104 for its subsequent use in generating a record set, giving details of information for trial balance.
(v) To Find the Record Set Which Consists of Account Code, Name of Account Debit Amount and Credit Amount This query, which is meant to provide relevant information to the trial balance report, is similar to Query 05 (designed and discussed in (e) of Model-I). Accordingly, the following SQL statement is formed by changing the source of data from Query 05 to Query 105 as shown below
SELECT a. code, b. name AS [Name of Account], IIF (a. Net>0, a. Net, null) AS
Debit, IIF (a. Net<0,abs(a.Net),null) AS Credit FROM Query 104 AS a, Account ASb/
WHERE a. code = b. code;
In above SQL statement, the results of Query 104 and data stored in accounts table has been used. This SQL statement is saved as Query 105 for providing source of information to Trial Balance Report.Trial Balance with Sorting and Grouping Levels In order to prepare a trial balance with all the account duly grouped by and sorted within category of accounts, two additional queries (vi) and (vii) are required.
(vi) To Find the Record Set of Accounts with their Category and Category ID
Accounts table is related to Account Type table vide Type field. The following SQL statement, using INNER JOIN clause, is formed to retrieve the relevant fields for various accounts.
SELECT Accounts.Code, Accounts Name, Category, Catld FROM Accounts
INNER JOIN AccountType ON
Accounts.Type = Account type CatId;
This SQL statement is saved as Query 106 for its subsequent use in next query.
(vii) To Find the Record Set Consisting of Account Code, Name of Account, Debit Amount and Credit Amount along with Category Details This query, when compared with (v) above, reveals that two additional fields Category and Catld are required. Accordingly, the SQL statement stored as Query 105 is modified by substituting Accounts table with Query 106 to form the following statement.
SELECT a. Code, b. name AS [Name of Account],
IIF (a.Net>0,a.Net,null) AS Debit, IIF (a.Net>0,abs(a.Net),null) AS Credit, Category, Catld
FROM Query 104 AS a, Query 106 AS b
WHERE a. code = b. code;
This SQL statement is saved as Query 107 to provide information details for designing trial balance with grouping and sorting of the accounts.
(i) To Find Total Amount by Which the Accounts have been Debited The transacted accounts in design of Model-II have been stored in AccCode of Vouchers Main and Code of Vouchers Detail.
The following SQL statement is formed to generate the relevant information from Vouchers Details.
SELECT Code, Sum (amount) AS Total
FROM vouchersMain INNER JOIN vouchers Details ON vouchesMain. Vno= VouchersDetails. Vno
WHERE Type=0
GROUP BY Code;
Similarly, the following SQL statement is formed to generate the required information from Vouchers Main table.
SELECT AccCode AS Code, sum (amount) AS Total
FROM vouchersMain INNER JOIN vouchersDetails ON vouchersMain.Vno = VouchersDetails. Vno
WHERE Type=1
GROUP BY AccCode;
Both the SQL statements are meant to extract similar sets of records, but from two different sources. Therefore, the resultant record set of these SQL statements have been horizontally merged using UNION clause as shown below
SELECT Code, sum(amount) AS Total
FROM vouchersMain INNER JOIN vouchersDetails ON vouchersMain.Vno= VouchersDetails.Vno
WHERE Type=0
GROUP BY Code
UNION ALL
SELECT AccCode AS Code, sum(amount) AS Total
FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain.Vno = VouchersDetails. Vno
WHERE Type=1
GROUP BY AccCode;
The above SQL statement is saved as Query 101 for its subsequent use. The total of debit amount in this query represents the Total with positive amounts.
(ii) To Find the Total Amount by Which the Accounts have been Credited In order to ascertain the total amount by which every transacted account has been credited, a query similar to that in (a) need to be formed. This is achieved by substituting Debit field in SELECT and GROUP BY clause by Credit field and the sum of amount generated by sum(Amount) is multiplied by −1 so that the final amount assigned to Total field is always negative. Accordingly, the following SQL statement is formed
SELECT Code, sum(amount)∗−1 AS Total
FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain. Vno= VouchersDetails. Vno
WHERE type=1 GROUP BY Code, Amount UNION
SELECT AccCode AS Code, sum(amount)∗−1 AS Total
FROM vouchersMain INNER JOIN vouchersDetails ON VouchersMain. Vno = VouchersDetails.Vno
WHERE Type=0
GROUP BY AccCode, Amount;
In the above SQL statement, the sum of amount has been multiplied by −1 to ensure that the amount of credit is always negative just as amount of debit is taken as positive. This query is saved as Query 102 for its subsequent use.
(iii) To Find a Collective Record Set of Accounts with their Debit and Credit totals
A collective record set is generated by forming a union query between Query 101 and Query 102 to ensure that the debit and credit amount with respect to each account becomes available for generating the net amount.
Accordingly, the following SQL statement is formed.
SELECT
FROM Query 101
UNION Select∗
FROM Query 102;
The above SQL statement causes horizontal merger of record sets returned by Query 101 and Query 102. This SQL Statement is saved as Query 103 for its subsequent use in next query.
(iv) To Find the Net Amount with which an Account has been Debited or Credited
To generate the net amount, an SQL statement similar to Query 04 (designed for query (d) of Model-I) above, is formed as shown below, except that its source of data is Query 103 instead of Query 03.
SELECT Code, Sum (Total) AS Net
FROM Query 103
GROUP BY Code;
This query is saved as Query 104 for its subsequent use in generating a record set, giving details of information for trial balance.
(v) To Find the Record Set Which Consists of Account Code, Name of Account Debit Amount and Credit Amount This query, which is meant to provide relevant information to the trial balance report, is similar to Query 05 (designed and discussed in (e) of Model-I). Accordingly, the following SQL statement is formed by changing the source of data from Query 05 to Query 105 as shown below
SELECT a. code, b. name AS [Name of Account], IIF (a. Net>0, a. Net, null) AS
Debit, IIF (a. Net<0,abs(a.Net),null) AS Credit FROM Query 104 AS a, Account ASb/
WHERE a. code = b. code;
In above SQL statement, the results of Query 104 and data stored in accounts table has been used. This SQL statement is saved as Query 105 for providing source of information to Trial Balance Report.Trial Balance with Sorting and Grouping Levels In order to prepare a trial balance with all the account duly grouped by and sorted within category of accounts, two additional queries (vi) and (vii) are required.
(vi) To Find the Record Set of Accounts with their Category and Category ID
Accounts table is related to Account Type table vide Type field. The following SQL statement, using INNER JOIN clause, is formed to retrieve the relevant fields for various accounts.
SELECT Accounts.Code, Accounts Name, Category, Catld FROM Accounts
INNER JOIN AccountType ON
Accounts.Type = Account type CatId;
This SQL statement is saved as Query 106 for its subsequent use in next query.
(vii) To Find the Record Set Consisting of Account Code, Name of Account, Debit Amount and Credit Amount along with Category Details This query, when compared with (v) above, reveals that two additional fields Category and Catld are required. Accordingly, the SQL statement stored as Query 105 is modified by substituting Accounts table with Query 106 to form the following statement.
SELECT a. Code, b. name AS [Name of Account],
IIF (a.Net>0,a.Net,null) AS Debit, IIF (a.Net>0,abs(a.Net),null) AS Credit, Category, Catld
FROM Query 104 AS a, Query 106 AS b
WHERE a. code = b. code;
This SQL statement is saved as Query 107 to provide information details for designing trial balance with grouping and sorting of the accounts.
Suggest Corrections
0
Similar questions
View More
Join BYJU'S Learning Program
Explore more
Join BYJU'S Learning Program
