I’m having some trouble with writing this query. This is what I have so far:
Find the name of the customer that deposited the highest amount with one transaction (include the transaction amount).
select name, amount from customer, transaction, account where account.owner_ssn = customer.ssn and account.type = transaction.type order by transaction.amount desc limit by one;
**This is the bank database: **
CREATE TABLE customer ( name VARCHAR(20), sex CHAR(1), ssn CHAR(9) NOT NULL, phone CHAR(15), dob DATE, address VARCHAR(50), PRIMARY KEY(ssn) ); CREATE TABLE account ( number CHAR(16) UNIQUE NOT NULL, open_date DATE, type CHAR(20), owner_ssn CHAR(9) NOT NULL, PRIMARY KEY(number) ); CREATE TABLE transaction ( id INT(20) UNIQUE NOT NULL, amount DECIMAL(9,2), tdate DATE, type CHAR(10), account_num CHAR(16), PRIMARY KEY(id) );
Below query will return the customer name with amount who deposited highest amount in a single transaction. If there is more than one customer with same highest amount then the first one to deposited that will be selected.
select name, amount from customer c inner join account a on c.ssn=a.owner_ssn inner join transaction t on a.type = t.type order by t.amount desc, t.tdate limit 1;