SQLAssignment for Chapter 3

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.