Q1. Consider the following bank database, where the primary keys are underlined, construct the following SQL queries for this relational database.
branch(branch_name, branch_city, assets)
customer (customer name, customer_street, customer_city)
loan (loan number, branch_name, amount)
borrower (customer name, loan number)
account (account number, branch_name, balance )
depositor (customer name, account_number)
a. Find all customers who have an account at all the branches located in “Brooklyn”.
select distinct DP.customer_name
from depositor as DP
where not exists(
(select branch_name
from branch
where branch.city="Brooklyn")
except
(select A.branch_name
from depositor as DP2,account as A
where DP2.account_number=A.account_number and
DP.account_number=A.account_number and
DP.customer_name=DP2.customer_name))
b. Find out the total sum of all loan amounts in the bank.
select sum(mount)
from loan;
c. Find the names of all branches that have assets greater than those of at least one branch located in “Brooklyn”.
select distinct branch_name
from branch
where assets >some(select assets
from branch
where branch_city="Brooklyn");
Q2. Consider the following database, where the primary keys are underlined, construct the following SQL queries for this relational database.
employee (employee_name, street, city)
works (employee_name, company_name, salary)
company (company_name, city)
manages (employee_name, manager_name)
a. Find the names of all employees who work for First Bank Corporation.
select employee_name
from works
wbere works.company_name="First Bank Corporation";
b. Find all employees in the database who live in the same cities as the companies for which they work.
select emp.employee_name
from employee as emp
where emp.city=some( select C.city
from company as C,works as W
where C.comoany_name=W.company_name and
emp.employee_name=W.employee_name);
c. Find all employees in the database who live in the same cities and on the same streets as do their managers.
select E.employee_name
from employee as E
where E.city=some( select E2.city
from employee as E2,manages as M
where M.employee_name=E.employee_name and
M.manager_name=E2.employee_name);
d. Find all employees who earn more than the average salary of all employees of their company.
select W.employee_name
from works as W
where W.salary>avg(select salary
from works as W2
where W2.company_name=W.company_name);
e. Find the company that has the smallest payroll.
select company_name,sum_salary
from ( select company_name,sum(salary) as sum_salary
from works
grounp by company_name)
where sum_salary=min(sum_salary);
Q3. Consider the following database, where the primary keys are underlined, construct the following SQL queries for this relational database.
member(memb no, name, age)
book(isbn, title, authors, publisher)
borrowed(memb no, isbn, date)
a. Print the names ofmemberswho have borrowed any book published by “McGraw-Hill”.
select distinct M.name
from member as M
where M.memb in(select memb
from borrowed as B
where B.isbn= some(select book.isbn
from book
where book.publisher="McGraw-Hill));
b. Print the names ofmemberswho have borrowed all books published by “McGraw-Hill”.
select distinct M.name
from member as M
where M.memb in(select memb
from borrowed as B
where B.isbn= all(select book.isbn
from book
where book.publisherr="McGraw-Hill));
c. For each publisher, print the names ofmemberswho have borrowed more than five books of that publisher.
...I Don't know..
d. Print the average number of books borrowed permember. Take into account that if an member does not borrow any books, then that
member does not appear in the borrowed relation at all.
