Learning Team Pos/410
Multiple Command Query
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:
Use KFF_TEAM_A
Go
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.
SELECT Account,Description,Short_description,Balance FROM ACCOUNTS ORDER by left(Account, 2) COMPUTE sum(Balance) by left(Account, 2) COMPUTE sum(Balance);
Kudler Fine Foods
Balance Sheet
November 08, 2010
Assets
Current Assets: Cash
Accounts Receivable
Less: Reserve for Bad Debts
Merchandise Inventory
Prepaid Expenses
Notes Receivable
$86,000
$0 $1,430,000 $86,000
$429,000
$26,000 $0 Total Current Assets $1,971,000
Fixed Assets: Vehicles
Less: Accumulated Depreciation
Furniture and Fixtures
Less: Accumulated
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:
Use KFF_TEAM_A
Go
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.
SELECT Account,Description,Short_description,Balance FROM ACCOUNTS ORDER by left(Account, 2) COMPUTE sum(Balance) by left(Account, 2) COMPUTE sum(Balance);
Kudler Fine Foods
Balance Sheet
November 08, 2010
Assets
Current Assets: Cash
Accounts Receivable
Less: Reserve for Bad Debts
Merchandise Inventory
Prepaid Expenses
Notes Receivable
$86,000
$0 $1,430,000 $86,000
$429,000
$26,000 $0 Total Current Assets $1,971,000
Fixed Assets: Vehicles
Less: Accumulated Depreciation
Furniture and Fixtures
Less: Accumulated