The following are the necessary tables for a Bank Database customer table: | customer_id | customer_name | customer_street | customer_city | +-------------+---------------+-----------------+---------------+ | C-101 | Jones | Main | Harrison | | C-201 | Smith | North | Rye | | C-211 | Hayes | Main | Harrison | | C-212 | Curry | North | Rye | | C-215 | Lindsay | Park | Pittsfield | | C-220 | Turner | Putnam | Stamford | | C-222 | Williams | Nassau | Princeton | | C-225 | Adams | Spring | Pittsfield | | C-226 | Johnson | Alma | Palo Alto | | C-233 | Glenn | Sand Hill | Woodside | | C-234 | Brooks | Senator | Brooklyn | | C-255 | Green | Walnut | Stamford | +-------------+---------------+-----------------+---------------+ branch table: +-------------+-------------+---------+ | branch_name | branch_city | assets | +-------------+-------------+---------+ | Brighton | Brooklyn | 7100000 | | Downtown | Brooklyn | 9000000 | | Mianus | Horseneck | 400000 | | North Town | Rye | 3700000 | | Perryridge | Horseneck | 1700000 | | Pownal | Bennington | 300000 | | Redwood | Palo Alto | 2100000 | | Round Hill | Horseneck | 8000000 | +-------------+-------------+---------+ account table: +-------------+----------------+---------+ | branch_name | account_number | balance | +-------------+----------------+---------+ | Downtown | A-101 | 500 | | Perryridge | A-102 | 400 | | Brighton | A-201 | 900 | | Mianus | A-215 | 700 | | Brighton | A-217 | 750 | | Redwood | A-222 | 700 | | Round Hill | A-305 | 350 | +-------------+----------------+---------+ loan table: +-------------+-------------+--------+ | loan_number | branch_name | amount | +-------------+-------------+--------+ | L-11 | Round Hill | 900 | | L-14 | Downtown | 1500 | | L-15 | Perryridge | 1500 | | L-16 | Perryridge | 1300 | | L-17 | Downtown | 1000 | | L-23 | Redwood | 2000 | | L-93 | Mianus | 500 | +-------------+-------------+--------+ depositor table: +-------------+----------------+ | customer_id | account_number | +-------------+----------------+ | C-101 | A-217 | | C-201 | A-215 | | C-211 | A-102 | | C-215 | A-222 | | C-220 | A-305 | | C-226 | A-101 | | C-226 | A-201 | +-------------+----------------+ borrower table: +-------------+-------------+ | customer_id | loan_number | +-------------+-------------+ | C-101 | L-17 | | C-201 | L-11 | | C-201 | L-23 | | C-211 | L-15 | | C-212 | L-93 | | C-222 | L-17 | | C-225 | L-16 | | C-226 | L-14 | +-------------+-------------+ Write MySQL queries for the following tasks: 4. Find account numbers with the highest balances for each city in the database 5. Show the loan number, loan amount, and name of customers who have the top 5 highest loan amounts. The data should be sorted by increasing amounts, then decreasing loan numbers in case of the same loan amount. [Hint for top 5 checks the "limit" keyword in mysql] 6. Find the names of customers with an account and also a loan at the Perryridge branch.
The following are the necessary tables for a Bank
customer table:
| customer_id | customer_name | customer_street | customer_city |
+-------------+---------------+-----------------+---------------+
| C-101 | Jones | Main | Harrison |
| C-201 | Smith | North | Rye |
| C-211 | Hayes | Main | Harrison |
| C-212 | Curry | North | Rye |
| C-215 | Lindsay | Park | Pittsfield |
| C-220 | Turner | Putnam | Stamford |
| C-222 | Williams | Nassau | Princeton |
| C-225 | Adams | Spring | Pittsfield |
| C-226 | Johnson | Alma | Palo Alto |
| C-233 | Glenn | Sand Hill | Woodside |
| C-234 | Brooks | Senator | Brooklyn |
| C-255 | Green | Walnut | Stamford |
+-------------+---------------+-----------------+---------------+
branch table:
+-------------+-------------+---------+
| branch_name | branch_city | assets |
+-------------+-------------+---------+
| Brighton | Brooklyn | 7100000 |
| Downtown | Brooklyn | 9000000 |
| Mianus | Horseneck | 400000 |
| North Town | Rye | 3700000 |
| Perryridge | Horseneck | 1700000 |
| Pownal | Bennington | 300000 |
| Redwood | Palo Alto | 2100000 |
| Round Hill | Horseneck | 8000000 |
+-------------+-------------+---------+
account table:
+-------------+----------------+---------+
| branch_name | account_number | balance |
+-------------+----------------+---------+
| Downtown | A-101 | 500 |
| Perryridge | A-102 | 400 |
| Brighton | A-201 | 900 |
| Mianus | A-215 | 700 |
| Brighton | A-217 | 750 |
| Redwood | A-222 | 700 |
| Round Hill | A-305 | 350 |
+-------------+----------------+---------+
loan table:
+-------------+-------------+--------+
| loan_number | branch_name | amount |
+-------------+-------------+--------+
| L-11 | Round Hill | 900 |
| L-14 | Downtown | 1500 |
| L-15 | Perryridge | 1500 |
| L-16 | Perryridge | 1300 |
| L-17 | Downtown | 1000 |
| L-23 | Redwood | 2000 |
| L-93 | Mianus | 500 |
+-------------+-------------+--------+
depositor table:
+-------------+----------------+
| customer_id | account_number |
+-------------+----------------+
| C-101 | A-217 |
| C-201 | A-215 |
| C-211 | A-102 |
| C-215 | A-222 |
| C-220 | A-305 |
| C-226 | A-101 |
| C-226 | A-201 |
+-------------+----------------+
borrower table:
+-------------+-------------+
| customer_id | loan_number |
+-------------+-------------+
| C-101 | L-17 |
| C-201 | L-11 |
| C-201 | L-23 |
| C-211 | L-15 |
| C-212 | L-93 |
| C-222 | L-17 |
| C-225 | L-16 |
| C-226 | L-14 |
+-------------+-------------+
Write MySQL queries for the following tasks:
4. Find account numbers with the highest balances for each city in the database
5. Show the loan number, loan amount, and name of customers who have the top 5 highest loan amounts. The data should be sorted by increasing amounts, then decreasing loan numbers in case of the same loan amount. [Hint for top 5 checks the "limit" keyword in mysql]
6. Find the names of customers with an account and also a loan at the Perryridge branch.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
