Problem statement:
Given a bank database schema, (primary keys are mentioned). Write the SQL queries for this schema (i.e. the CREATE TABLE statements).
Things to consider while writing the queries:
a. The tables should be created chronologically as specified in the schema.
b. Make sure that the table and attributes names are the same. Do not alter the schema of the table.
c. Make sure that the tables are following the assumptions mentioned in task description.
d. Attributes with Varchar data types should have a length of 40.
e. Make sure that you implement each of the following:
i. check constraint
ii. default value statement.
iii. not null constraint
iv. on delete cascade clause
v. on update cascade clause
Database Schema:
branch ( branch_name, branch_city, assets )
customer ( cust_ID, customer_name, customer_street, customer_city )
loan ( loan_number, branch_name, amount )
borrower ( cust_ID, loan_number )
account ( account_number, branch_name, balance )
depositor ( cust_ID, account_number )
Things to consider:
● Branch: All branches must have assets that are monetary (they have a dollar value). There are four cities with branches: Brooklyn, Bronx, Manhattan, and Yonkers.
● Customer: Name columns can't be left blank.
● Loan: Loan numbers can be alphanumeric and the loans amount can't be left empty. The default loan amount is 0.0.
● Borrower: changes in cust_id and loan_number should be reflected in borrower table.
● Account: Accounts should be numeric and should not be empty.
● Depositor: A depositor is a type of customer, so if the cust_ID is deleted or changed, the same should be reflected in depositor table.
Question 1:
Write a query to find the cust_ID and customer name of each customer at the bank whose name starts with the letter 'B'.
Question 2:
Write a query to find the number of customers who live in the same city and only have a loan account in the bank.
Question 3:
Write a query to retrieve the total no. of customers per branch in descending order.
Question 4:
Write a query to find each customer who has an account at every branch located in “Brooklyn”.
Question 5:
Write a query to list the top 10 borrowers in each city in Descending order. Mention the loan amount and display the city as well.
If you need implementation for the above problem or any of its variants, feel free to contact us.
תגובות