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 <LastName>_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. :