human
Schema:
accounts (custid, name)
savings (custid, balance)
checking (custid, balance)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the names of accounts whose checking balance is above the average checking balance, but savings balance is below the average savings balance.
assistant
SELECT T1.name FROM accounts AS T1 JOIN checking AS T2 ON T1.custid = T2.custid WHERE T2.balance > (SELECT avg(balance) FROM checking) INTERSECT SELECT T1.name FROM accounts AS T1 JOIN savings AS T2 ON T1.custid = T2.custid WHERE T2.balance < (SELECT avg(balance) FROM savings)
human
Find the name and checking balance of the account with the lowest savings balance.
assistant
SELECT T1.name , T2.balance FROM accounts AS T1 JOIN checking AS T2 ON T1.custid = T2.custid JOIN savings AS T3 ON T1.custid = T3.custid ORDER BY T3.balance LIMIT 1
human
Find the saving balance of the account with the highest checking balance.
assistant
SELECT T3.balance FROM accounts AS T1 JOIN checking AS T2 ON T1.custid = T2.custid JOIN savings AS T3 ON T1.custid = T3.custid ORDER BY T2.balance DESC LIMIT 1
human
Find the total checking and saving balance of all accounts sorted by the total balance in ascending order.
assistant
SELECT T1.balance + T2.balance FROM checking AS T1 JOIN savings AS T2 ON T1.custid = T2.custid ORDER BY T1.balance + T2.balance
human
Find the name, checking balance and savings balance of all accounts in the bank sorted by their total checking and savings balance in descending order.
assistant
SELECT T2.balance , T3.balance , T1.name FROM accounts AS T1 JOIN checking AS T2 ON T1.custid = T2.custid JOIN savings AS T3 ON T1.custid = T3.custid ORDER BY T2.balance + T3.balance DESC
human
Find the name and savings balance of the top 3 accounts with the highest saving balance sorted by savings balance in descending order.
assistant
SELECT T1.name , T2.balance FROM accounts AS T1 JOIN savings AS T2 ON T1.custid = T2.custid ORDER BY T2.balance DESC LIMIT 3 ;