Assignment 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”.
b. Find out the total sum of all loan amounts in the bank.
c. Find the names of all branches that have assets greater than those of at least one branch located in “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.
b. Find all employees in the database who live in the same cities as the companies for which they work.
c. Find all employees in the database who live in the same cities and on the same streets as do their managers.
d. Find all employees who earn more than the average salary of all employees of their company.
e. Find the company that has the smallest payroll.
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”.
b. Print the names ofmemberswho have borrowed all books published by “McGraw-Hill”.
c. For each publisher, print the names ofmemberswho have borrowed more than five books of that publisher.
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.
4412
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
