Back to course home page

FORDHAM UNIVERSITY, Fordham College Lincoln Center
CSEU 3500 -- Data Base Systems
Dept. of Computer & Info. Sciences, Spring, 2004

Homework for Chapter 4, Set 1
Due date: Wednesday, March 24

4.2

[48 pts.] Consider the employee database of Figure 4.13 [as modified for use with PostgreSQL. The primary keys are underlined. Note that the key of company has been enlarged to include city so that a company can be located in more than one city. For brevity, the word ``Corporation'' has been left off the names of the companies in the database.]

employee ( employee_name , street, city )

works ( employee_name , company_name , salary )

company ( company_name , city )

manages ( employee_name , manager_name )

Figure 4.13 Employee database.

Give an expression in [standard] SQL for each of the following queries. [Perform the queries using the PostgreSQL interactive query processor on host erdos, or other SQL query processor available to you. If you use another system, copy the data from the class web page to populate the tables. Hand in a hard copy of both the queries and the results. The questions marked modified are different from the corresponding queries in the text.]

(a)

Find the names of all employees who work for First Bank.

(b)

modified Find the names and companies of all employees who work for companies that are located in White Plains. List alphabetically by company, then by name.

(c)

modified Find the names and streets of residence of all employees who live in Rye and earn more than $100,000.

(d)

Find [the names and cities of residence of] all employees in the database who live in [any of] the same cities as the companies for which they work [are located in].

(e)

Find [the names, streets and cities of] all employees in the database who live in the same cities and on the same streets as do their managers.

(f)

Find [the names of] all employees in the database who do not work for First Bank.

(g)

modified Find the names of all employees in the database who earn more than Johnson earns.

(h)

modified List all the companies in the database together with their payrolls. (A company's payroll is the total of all the company's employees' salaries.) Sort the list so it is printed with the largest payroll first. Label the column containing payroll amounts ``payroll''.

(i)

Find [the names of] all employees who earn more than the average salary of all employees of their company.

(j)

modified Find the name of the company located in White Plains that has the fewest employees.

(k)

Find [the name of] the company that has the smallest payroll.

(l)

modified List all companies that have more employees than Small Bank has.