human
Schema:
accounts (custid, name)
savings (custid, balance)
checking (custid, balance)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the name and total checking and savings balance of the accounts whose savings balance is lower than corresponding checking balance.
assistant
SELECT T1.name , T3.balance + T2.balance FROM accounts AS T1 JOIN checking AS T2 ON T1.custid = T2.custid JOIN savings AS T3 ON T1.custid = T3.custid WHERE T3.balance < T2.balance
human
Find the name of account that has the lowest total checking and saving balance.
assistant
SELECT 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 LIMIT 1
human
Find the name and id of accounts whose checking balance is below the maximum checking balance.
assistant
SELECT T1.custid , T1.name FROM accounts AS T1 JOIN checking AS T2 ON T1.custid = T2.custid WHERE T2.balance < (SELECT max(balance) FROM checking)
human
Find the name of accounts whose checking balance is higher than corresponding saving balance.
assistant
SELECT T1.name FROM accounts AS T1 JOIN checking AS T2 ON T1.custid = T2.custid JOIN savings AS T3 ON T1.custid = T3.custid WHERE T2.balance > T3.balance
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 of accounts whose checking balance is below the average checking 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 ) ;