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
- [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.]
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.]
|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.
- Find the names of all employees who work for First Bank.
- 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.
- modified Find the names and streets of residence
of all employees who live in Rye and earn more than $100,000.
- 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].
- 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.
- Find [the names of] all employees in the database who do
not work for First Bank.
- modified Find the names of all employees in the
database who earn more than Johnson earns.
- 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''.
- Find [the names of] all employees who earn more than the average
salary of all employees of their company.
- modified Find the name of the company located in
White Plains that has the fewest employees.
- Find [the name of] the company that has the smallest payroll.
- modified List all companies that have more
employees than Small Bank has.