Using the structure below, write the SQL that will provide the requested results
ID: 3701285 • 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.Generate a mailing list of all customers that have Money Market account. Sort by last then first name. (7 points)
2.Generate a mailing list of customers that have made loan payments in the last week. Sort by city, last then first name. (10 points)
3.Generate a list of customers and all transactions, including the amount, date, transaction type along with the account type, that were made in March 2018. Sort by last name, first name and account type. (12 points)
4.What is the total amount of deposits made in March 2018? (7 points)
5.By customers, what is the total amount each of deposits, withdrawals and payments for 2018? Sort by last then first name. (15 points)
6.Generate a mailing list of customers that made deposits to their Money Market account of more than $1000 this year. Sort by city, last name then first name. (12 points)
7.Generate a list of customers that opened any accounts this year that have a total of $500 deposited to them. Sort by total deposit, last then first name. (12 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
Solution:
The first four subparts have been answered as per Chegg guidelines, please repost others.
SELECT x.fName, x.lName, Cemail FROM tblCustomers x INNER JOIN tblAccount y ON a.custID= y. CustomerID WHERE y.AccType= 'Money Market' ORDER BY x.lName, x.fName
2)
SELECT x.fName, x.lName, Cemail FROM tblCustomers x INNER JOIN tblAccount y ON a.custID= y. CustomerID WHERE AEDate BETWEEN date_sub(now()), INTERVAL (week) and now() AND y.AcctType= "Loan" ORDER BY x, Ccityx.lName, x.fName
3)
SELECT x.CustomerID, z.TrnsID, z.TransAmt, z.TEDate, z.TransType, y.AcctType, FROM tblAccounts y INEER JOIN tblTransactions z ON y.AcctID= z.AccountID WHERE year(z.TEDate) = 2018 AND
month(z.TEDate) = 3
ORDER BY lName, fName, AcctType
4)
SELECT SUM(TransAmt) FROM tblTransactions WHERE TransType= 'Deposit' AND (year(CEDate)) = 2018 NAD month (TEDate) = 3
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.