Task:
There is a set of tables owned by
“cis523” available on the server at La Salle. The schema for the tables is
given below. Your task is to develop queries to answer the questions given
below, and run them against the database. Capture the execution/results in a
(spool) file, and turn in the file.
Schema:
PROJECTS
ProjID Codename
JOBS
JobID JobClass ChargePerHr
EMPLOYEES
EmployID employName jobcode
FK jobcode to JOBS (JobID) (N:1)
WORKS_ON
Employnum projnum hrsonjob
FK
employnum to EMPLOYEES (employed) (N:1)
FK
projnum to PROJECTS (projID) (N:1)
Queries:
1)
Show for
JobCode 3, the project name, employee name, and hours working on the project
for all employees that worked on any project. Sort the results by project code
name.
2)
Show for
the Tornado project, the name, job class, charge per hour, and hours on job for
every employee who worked on the project this week. Sort the results by
employee name.
3)
Show for
each employee, their name and the total number hours worked on ALL projects
this week (sum). Sort the results by employee name.
4)
Show for
each project, the totals: number of hours worked on the project by all employees,
and the total charge for all employees’ work. Sort the results by project code
name.
Check your
work (the actual data is small), and make sure that what you turn in shows your
SQL commands and the resulting output.