Writing SQL Queries Workbench

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) 
);

Answer

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;

Advertisement

Answer