---
tags: 資料庫系統
title: Homework 6 (SQL II)
---
1. Consider the following relational database. Give an expression in the SQL to express each of the following queries:

**employee (employee-name, street, city)**

**works (employee-name, company-name, salary)**

**company (company-name, city)**

**manages (employee-name, manager-name)**

(a) Assume the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.
>

(b) Find the company with the most employees.

(c) Find the company with the smallest payroll.

(d) Find those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation.

(e) Find all employees who earn more than the average salary of all employees of their company.

(f) Modify the database so that Jones now lives in Newtown.

(g)Give all employees of First Bank Corporation a 10 percent salary raise.

(h) Give all managers in this database a 10 percent salary raise, unless the salary would be greater than $100,000. In such cases, give only a 3 percent raise.

(i) Delete all tuples in the works relation for employees of Small Bank Corporation.

2. Using the relations of our sample bank database (page 4 on slide 4.SQL.ppt),write SQL expressions to define the following views:

(a) A view containing the account numbers and customer names (but not the balances) for all accounts at the Deer Park branch.

(b) A view containing the names and addresses of all customers who have an account with the bank, but do not have a loan.

(c) A view containing the name and average account balance of every customer of the Rock Ridge branch.