Geog 495: GIS Database Design

10/31/05

 

SQL

Individual assignment

 

This is graded! Due by next Monday (11/7/05) sharp 1:30 pm

 

Copy Ch06_Review.mdb (located at P:\geog495aut05) to your working directory and rename it Ch06_Review_yourlastname.mdb. You will turn in this file so that I can check if SQLs run fine.

 

The mdb file stores data for a consulting company that tracks all charges to all projects. The charges are based on the hours worked by each employee on each project. Please take a moment to view data definition of each table and relationships between tables (use the object menu in the left: Tools-Relationships) before you write the SQL.

 

Write the query that performs the following job, and save each query as the name given in the bracket at the beginning of each question.

 

[Query1] Create structure for a table named EMP_1. This table is a subset of the EMPLOYEE table. (Note that the JOB_CODE is the Foreign Key to JOB.)

 

[Query2] Enter the first row for the table EMP_1 shown below.

 

EMP_NUM

EMP_LNAME

EMP_FNAME

EMP_INITIAL

EMP_HIREDATE

JOB_CODE

101

News

John

G

08-Nov-98

502

 

[Query3] Enter the second row for the table EMP_1 shown below.

 

EMP_NUM

EMP_LNAME

EMP_FNAME

EMP_INITIAL

EMP_HIREDATE

JOB_CODE

102

Senior

David

H

12-Jul-87

501

 

[Query4] Delete the row for the person named David (EMP_FNAME) in EMP_1.

 

[Query5] Copy all the records from EMPLOYEE into EMP_1 except for the record where EMP_NUM is 101.

 

[Query6] Add the attribute EMP_PCT to the table EMP_1. The EMP_PCT is the bonus percentage to be paid to each employee.

 

[Query7] Update an EMP_PCT value of 10 for the person who was hired before 1-1-91  (EMP_HIREDATE).

 

[Query8] Delete the table EMP_1.

Query9 to Query 14 use existing tables: ASSIGNMENT, EMPLOYEE, JOB, PROJECT.

 

[Query9] List all employees whose last names start with ‘Smith’. In other words, the rows for both Smith and Smithfield should be included in the listing.

 

[Query10] List David’s (EMP_FNAME) job description and charge hour.

 

[Query11] Calculate the average ASSIGN_HOURS from ASSIGNMENT

 

[Query12] Calculate the average ASSGN_HOURS grouped by PROJ_NUM from ASSIGNMENT

 

[Query13] Calculate the total sum of ASSIGN_CHARGE for each employee (EMP_NUM).

 

[Query14] How many employees are assigned to all projects according to ASSIGNMENT?

 

Make sure 14 queries are saved in your mdb file. Turn in the mdb file. In some cases, there is no single solution.