Using the structure below, write the SQL that will provide the requested results
ID: 3701365 • Letter: U
Question
Using the structure below, write the SQL that will provide the requested results.
tblCustomers
tblAccounts
tblTransactions
CustID
AutoNumber
AcctID
AutoNumber
TransID
AutoNumber
FName
Short Text
CustomerID
Number
AccountID
Number
LName
Short Text
AcctType
Short Text
TransAmt
Currency
CAdd
Short Text
AcctOpenDate
Date/Time
TransDate
Date/Time
CCity
Short Text
AcctCloseDate
Date/Time
TransType
Short Text
CSt
Short Text
AcctSigScan
Attachment
TransSig
Attachment
CZip
Short Text
AEBy
Short Text
TEBy
Short Text
CPhone
Short Text
AEDate
Date/Time
TEDate
Date/Time
CCell
Short Text
CEMail
Hyperlink
CSSN
Short Text
CEBy
Short Text
CEDate
Date/Time
(NOTE: AcctType includes: Checking, Money Market, Savings, Loan, CD, IRA, etc. TransType includes: Deposit, Withdraw, Payment)
1.By customers, what is the total amount each of deposits, withdrawals and payments for 2018? Sort by last then first name. (15 points)
tblCustomers
tblAccounts
tblTransactions
CustID
AutoNumber
AcctID
AutoNumber
TransID
AutoNumber
FName
Short Text
CustomerID
Number
AccountID
Number
LName
Short Text
AcctType
Short Text
TransAmt
Currency
CAdd
Short Text
AcctOpenDate
Date/Time
TransDate
Date/Time
CCity
Short Text
AcctCloseDate
Date/Time
TransType
Short Text
CSt
Short Text
AcctSigScan
Attachment
TransSig
Attachment
CZip
Short Text
AEBy
Short Text
TEBy
Short Text
CPhone
Short Text
AEDate
Date/Time
TEDate
Date/Time
CCell
Short Text
CEMail
Hyperlink
CSSN
Short Text
CEBy
Short Text
CEDate
Date/Time
Explanation / Answer
SELECT sum(TransAmt)
FROM tblTransactions
WHERE year(TransDate) = 2018
GROUP BY TransType
ORDER BY LName, FName
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.