Create one query that uses the commands: COMPUTE, OR, IN, CAST, SUBSTR. It should also contain one mathematical function\formula.
Discuss the logic that explains what this query does and why you created it. The following command was executed:
SELECT ACCOUNT,SUBSTRING(SHORT_DESCRIPTION,1,10) AS SHORT_DESC,BALANCE FROM ACCOUNTS WHERE CAST(ACCOUNT AS INT) Like '%6%' OR ACCOUNT IN(777777) COMPUTE AVG(BALANCE)
The command produced the following results:
Description of the command:
The SUBSTRING was used to shorten the length of the Short_Description Field for viewing purposes. The CAST command was used to restrict any account that did not have the number 6 in it. The OR at the end of that statement was used to include an account named 777777. The COMPUTE command computed the average balance of the accounts meeting all the above requirements.
Grand Total of the Balance Field
Test the database by means of a query by account number and a report totaling the balance field. This query needs to display all fields (description, short description, and balance) using the account number as the key to the query. The report will display all fields with a break based on the first two digits of the account number and subtotal of the balance field at each break. A grand total of the balance field will be provided at the end of the report.
ORDER by left(Account, 2)
COMPUTE sum(Balance) by left(Account, 2)
Kudler Fine Foods
November 08, 2010
Less: Reserve for Bad Debts
Total Current Assets$1,971,000
Less: Accumulated Depreciation
Furniture and Fixtures