DTSC660: Data and Database Management with SQL

Module 9

Assignment 7

Purpose

For this assignment, you will utilize the banking database that you created in Assignment 4. You will be performing some advanced queries on the same dataset. To complete this assignment, ensure that you have access to the data and banking database from Assignment 4 before continuing.




Each question is all or nothing. Graders will not attempt to correct or interpret malformed SQL queries.




You will be responsible for testing your code on the provided data set before submission. The question will be graded based on whether or not it generates the correct output. Extraneous columns will not count against you.

Submission

You will submit a total of




1




sql files. Each file must use the postgres standards taught in the course. Use of other flavors of SQL such as T-SQL will result in an automatic 0 for the assignment. Do not submit files as archives (ZIP) files.

• 
  
  
  

  
  
  
  File 1
  
  
  : You must submit a SQL document called _Assignment7. This document must include ALL queries requested in the instructions below.
  
  
  
  
  
  
  
  
  
  
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  You will submit the file to the Assignment 7 folder.
  
  
  

  
  
  
  

Questions

• 
  
  
  

  
  
  
  
  
  
  Question 1 (Must include a subquery):
  
  
  
  
  Write a query to find the cust_ID and customer name of each customer at the bank who only has at least one loan at the bank, and no account.
  
  
  
  
  
  
  
  
  
  
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Question 2 (Must include a subquery):
  
  
  
  
  
  Write a query to find the cust_ID and customer name of each customer who lives on the same street and in the same city as customer ‘12345’. Include customer ‘12345’ in your query results.
  
  
  
  
  
  
  
  
  
  
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Question 3 (No Joins Allowed):
  
  
  
  
  
  Write a query to find the name of each branch that has at least one customer who has a deposit account in the branch and who lives in “Harrison”.
  
  
  
  
  
  
  
  
  
  
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Question 4 (Must include subquery):
  
  
  
  
  Write a query to find each customer who has a deposit account at
  
  
  
  
  every
  
  
  
  
  
  branch located in “Brooklyn”.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Question 5 (Must Include subquery):
  
  
  
  
  Write a query that finds the account number of all customers whose deposit account is in the city where they live. Include the customer’s id, branch city, and customer city
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Question 6 (Must include subquery):
  
  
  
  
  Write a query that returns every customer that has a loan at Yonkahs Bankahs. Include the customer name and branch name for verification.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  
  
  
  




• 
  
  
  

  
  
  
  Question 7 (Must Include Subquery):
  
  
  
  
  Write a query that returns the name and loan number of all customers with loan balances higher than $5,000.00.
  
  
  

  
  
  
  

*******************************GRADING RUBRIC BELOW*********************************

This assignment will be graded on the following rubric. Remember that questions are ALL OR NOTHING. Incorrect syntax or extraneous results will result in loss of points for that question. Graders will NOT attempt to correct malformed sql code. :