Question

The Ch07_ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure and contents of the Ch07_ConstructCo database are shown in Figure P7.1.

Note that the ASSIGNMENT table in Figure P7.1 stores the JOB_CHG_HOUR values as an attribute (ASSIGN_CHG_HR) to maintain historical accuracy of the data. The JOB_CHG_HOUR values are likely to change over time. In fact, a JOB_CHG_ HOUR change will be reflected in the ASSIGNMENT table. Naturally, the employee primary job assignment might also change, so the ASSIGN_JOB is also stored. Because those attributes are required to maintain the historical accuracy of the data, they are not redundant.

Given the structure and contents of the Ch07_ConstructCo database shown in Figure P7.1, use SQL commands to answer the following problems.

1. Write the SQL code required to list the employee number, last name, first name, and middle initial of all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Sort the results by employee number. Assume case sensitivity.

2. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo database, write the SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the attributes shown in the results presented in Figure P7.2, sorted by project value.3. Write the SQL code that will produce the same information that was shown in Problem 2, but sorted by the employee's last name.

4. Write the SQL code that will list only the distinct project numbers in the ASSIGNMENT table, sorted by project number.

5. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should retrieve the assignment number, employee number, project number, the stored assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number.

6. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worded for each employee and the total charges stemming from those hours worked, sorted by employee number. The results of running that query are shown in Figure P7.6.

7. Write a query to produce the total number of hours and charges for each of the projects represented in the ASSIGNMENT table, sorted by project number. The output is shown in Figure P7.7.8. Write the SQL code to generate the total hours worked and the total charges made by all employees. The results are shown in Figure P7.8.

Answer 1

1.

Query:

SELECT EMP_NUM,         EMP_LNAME,    EMP_FNAME,   EMP_INITIAL

FROM EMPLOYEE

WHERE EMP_LNAME LIKE 'Smith%';

Output:

2.

Query:

SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE,

EMP_LNAME,EMP_FNAME, EMP_INITIAL, EMPLOYEE.JOB_CODE,

JOB_DESCRIPTION,JOB_CHG_HOUR

FROM PROJECT, EMPLOYEE, JOB

WHERE EMPLOYEE.EMP_NUM = PROJECT.EMP_NUM

AND JOB.JOB_CODE = EMPLOYEE.JOB_CODE

ORDER BY PROJ_VALUE;

Output:

3.

Query:

SELECT PROJ_NAME, PROJ_VALUE, PROJ_BALANCE,

EMP_LNAME,EMP_FNAME, EMP_INITIAL, EMPLOYEE.JOB_CODE,

JOB_DESCRIPTION,JOB_CHG_HOUR

FROM PROJECT, EMPLOYEE, JOB

WHERE EMPLOYEE.EMP_NUM = PROJECT.EMP_NUM

AND JOB.JOB_CODE = EMPLOYEE.JOB_CODE

ORDER BY EMP_LNAME;

Output:

4.

Query:

SELECT distinct PROJ_NUM

from ASSIGNMENT

ORDER BY PROJ_NUM;

Output:

5.

Query:

SELECT ASSIGN_NUM, EMP_NUM, PROJ_NUM, ASSIGN_CHARGE,

ASSIGN_CHG_HR * ASSIGN_HOURS AS CACLUATED_ASSIGN_CHARGE

FROM ASSIGNMENT

ORDER BY ASSIGN_NUM;

Output:

6.

Query:

SELECT ASSIGNMENT.EMP_NUM, EMP_LNAME,

SUM(ASSIGNMENT.ASSIGN_HOURS)AS SumOfASSIGN_HOURS,

SUM(ASSIGNMENT.ASSIGN_CHG_HR*ASSIGNMENT.ASSIGN_HOURS) AS SumOfASSIGN_CHARGE

FROM EMPLOYEE, ASSIGNMENT

WHERE EMPLOYEE.EMP_NUM = ASSIGNMENT.EMP_NUM

GROUP BY ASSIGNMENT.EMP_NUM, EMP_LNAME

ORDER BY ASSIGNMENT.EMP_NUM;

Output:

7.

Query:

SELECT PROJ_NUM,

SUM(ASSIGN_HOURS) AS SumOfASSIGN_HOURS,

SUM(ASSIGN_CHARGE) AS SumOfASSIGN_CHARG

FROM ASSIGNMENT

GROUP BY PROJ_NUM

ORDER BY PROJ_NUM;

Output:

Query:

SELECT PROJ_NUM,

SUM(ASSIGN_HOURS) AS SumOfASSIGN_HOURS,

SUM(ASSIGNMENT.ASSIGN_CHG_HR*ASSIGNMENT.ASSIGN_HOURS) AS SumOfASSIGN_CHARGE

FROM ASSIGNMENT

GROUP BY PROJ_NUM

ORDER BY PROJ_NUM;

Output:

8.

Query:

SELECT SUM(ASSIGN_HOURS)AS SumOfASSIGN_HOURS,

SUM(ASSIGN_CHARGE) AS SumOfASSIGN_CHARGE

FROM ASSIGNMENT;

Output: