Challenging problems to try against the banking database.

a) Generate a list of all cities that customers live in, where there is no bank branch in that city. Make sure that the results are distinct; no city should appear twice. Also, sort the cities in increasing alphabetical order.

b) Are there any customers who have neither an account nor a loan? Write a SQL query that reports the name of any customers that have neither an account nor a loan. Note that MySQL does not support the EXCEPT operator! But there is more than one way

c) The bank decides to promote its branches located in the city of Horseneck, so it wants to make a $50 gift-deposit into all accounts held at branches in the city of Horseneck. Write the SQL UPDATE command for performing this operation. Do not use MySQL-specific extensions for this UPDATE operation. You may only use the standard UPDATE tblname SET ... WHERE ... form, where you can only specify one table in the UPDATE clause, and all references to other tables must appear in the WHERE clause.

d) MySQL also supports a non-standard, multiple table version of UPDATE, of the form: UPDATE tbl1, tbl2, ... SET ... WHERE ... In this form, you can refer to columns from any of the specified tables in the SET and WHERE clauses, and MySQL will figure out what to update from what you write. Write another answer to part c, using this syntax. Note that you can also give the tables in the UPDATE clause aliases, as usual.

e) Retrieve all details (account_number, branch_name, balance) for the largest account at each branch. Implement this query as a join against a derived relation in the FROM clause. f) Implement the same query as in the previous problem, this time using an IN predicate with multiple columns, e.g., (branch_name, balance) IN ...