Question

In using a database created from this model, which of the following SQL queries would give us a list of customers who have daily deposit transactions (deposit is a transaction type) totaling $10,000 or more across all of their accounts (i.e., this is a total across all accounts of a customer, not per account). Include in this list the Customer ID and name, and the dates and their respective daily total.

Consider the following physical entity-relationship model: Customers Addresses Merchants PK mer_id th THPK cust_id FK add id FK add_id int(8) int(10) char(50) char(10) char(255) cust_name cust_phone cust_email mer name PK add_id int(10) line 1 char(255) line 2 char(255) city char(50) state_province char(50) zip_postcode char(10) country char(50) int(8) int(10) char(50) char(10) char(255) mer_phone mer_email PK HPK Acc_Types acc_type_id smallint(4) th | type char(50) description text Tran_Types type_id smallint(4) type char(50) description text Accounts Transactions PK acc_number char(10) HT PK tran id bigint(12) SFK cust_id int(8) OFK acc_number char(10) KFK acc_type_id smallint(4) FK merch_id int(8) date_opened datetime FK tran_type_id smallint(4) date closed datetime tran_date datetime current_balance decimal(11,2) tran_amount decimal(8,2) acc_balance decimal(11,2) In using a database created from this model, which of the following SQL queries would give us a list of customers who have daily deposit transactions (deposit is a transaction type) totaling $10,000 or more across all of their accounts (i.e., this is a total across all accounts of a customer, not per account). Include in this list the Customer ID and name, and the dates and their respective daily total.

		SELECT c.cust_id, c.cust_name, DATE(t.tran_date), SUM(t.tran_amount) AS daily_total FROM Customers c JOIN Accounts a JOIN Transactions t JOIN Tran_Types tt WHERE tt.type = 'deposit' GROUP BY c.cust_id, c.cust_name, DATE(t.tran_date) HAVING daily_total >= 10,000;
		SELECT c.cust_id, c.cust_name, DATE(t.tran_date), t.tran_amount AS daily_total FROM Customers c, Accounts a, Transactions t, Tran_Types tt WHERE tt.type = 'deposit' AND c.cust_id = a.cust_id AND a.acc_number = t.acc_number AND tt.type_id = t.tran_type_id AND daily_total >= 10,000;
		SELECT c.cust_id, c.cust_name, DATE(t.tran_date), t.tran_amount AS daily_total FROM Customers c, Accounts a, Transactions t, Tran_Types tt WHERE tt.type = 'deposit' AND c.cust_id = a.cust_id AND a.acc_number = t.acc_number AND tt.type_id = t.tran_type_id GROUP BY c.cust_id, c.cust_name, DATE(t.tran_date) HAVING daily_total >= 10,000;
		None of the above.

Answer 1

SELECT c.cust_id, c.cust_name, DATE(t.tran_date), SUM(t.tran_amount) AS daily_total
FROM Customers c
   JOIN Accounts a 
   JOIN Transactions t 
   JOIN Tran_Types tt 
WHERE tt.type = 'deposit'
GROUP BY c.cust_id, c.cust_name, DATE(t.tran_date)
HAVING daily_total >= 10,000;

B is wrong as t.tran_amount is not the total sum of amount in all accounts of the customer

C is wrong as SUM() function is not used with Group by .

Do ask if any doubt. Please up-vote.