Description
Below diagram shows a relational diagram of a company's database, storing information about employees as well as the projects they are working on.
Appendices A and B provides some sample content of the various tables described in the above relational diagram. It is highly recommended you go through it as it gives you a clue as to the various attributes, data types and likely domain range / values.
Note that in the ASSIGNMENT table, the attribute named 'ASSIGN CHG HR' is related to the attribute 'JOB CHG HOUR' in the JOB table. However, there are some differences in their meanings, as follows:
JOB CHG HOUR - stores the most recently recorded hourly rate of a particular job, as of
a particular date (see attribute JOB_LAST_UPDATE)
ASSIGN CHG HR - maintains both 'current' and 'historical' hourly rates of a particular
job, (see attribute ASSIGN_DATE, for ASSIGN_JOB = 501)
Finally, do note that the attribute `JOB_CODE' in JOB and EMPLOYEE tables means the same thing as attribute `ASSIGN_JOB' in ASSIGNMENT table.
Creating Queries
Using the given database, follow the instructions in sequence, to create the queries for each question, in MS Access.
1) Write the SQL code to create the table named `EMP_1'. This table is a subset of the EMPLOYEE table, and the structure of the table is summarized as attached below.
2) After creating the table `EMP_1' in Qn 1), write the SQL code to enter the 1st 2 rows of data, based on the table attached below.
3) Instruction :
Assuming the 1st 2 rows of data specified in Qn 2) has been entered, import the rest of the data (from `EMP_1.txt' file that you downloaded), into your created table EMP_1.
4) Write the SQL code to list all attributes information for job code of 501.
5) Write the SQL code to change the job code to 501, for the person whose employee number (EMP_NUM) is 107.
6) Write the SQL code to delete the row for the person named Ramoras Anne', hired on Nov 15, 1987 and whose job code is 501. (Hint : Use logical operators to combine the criteria in your query)
7) Write the SQL code to create an empty table, named 'EMP_2'. EMP_2 should have the exact same attributes, relationships and constraints as table EMP_1.
8) After creating the empty table EMP_2 in Qn 7), write the SQL code to alter table EMP_2 to include a new attribute / columns named 'EMP -PCT'. The data type is 'NUMBER'. (Note : EMP_PCT represents a bonus percentage to be paid to each employee).
9) Write the SQL code to alter table EMP_2 to include another new attribute / columns named `PROJ NUM'. The data type is `CHAR(3)'.
10) Instruction :
Assuming Qns 7 - 9 have been done, import the rest of the data (from 'EMP_2.txt' file that you downloaded), into your created table EMP_2.
11) Referring to table EMP_2, write the SQL code to change the EMP_PCT value to 3.85, for the person whose employee number (EMP_NUM) is 103,
12) Referring to table EMP_2, using a single SQL command, write the SQL code that will change the project number (PROJ_NUM) to 18, for all employees whose job classification (JOB_CODE) is 500.
13) Referring to table EMP_2, using a single SQL command, write the SQL code that will change the project number (PROJ_NUM) to 25, for all employees whose job classification (JOB_CODE) is 502 or higher.
Note : after executing the SQL code in Qns 11 - 13), the table EMP_2 should contain the data attached in the figure below.
14) Referring to table EMP_2, write the SQL code that will change the PROJ_NUM TO 14, for those employees who were hired before Jan 1, 1994 and whose job code is at least 501.
15) Write the two SQL command sequences required to:
a) Create a temporary table named TEMP_1 whose structure is composed of the EMP2 attributes EMP_NUM and EMP PCT
b) Copy the matching EMP_2 values into the TEMP_1 table
16) Write the SQL command that will delete the newly created TEMP_1 table from the database
17) Referring to table EMP_2, write the SQL code to list all employees whose last names start with 'Smith'. (e.g. records of employees whose names are 'Smith' or 'Smithfield' should be included in the returned listing. Assume case sensitivity.)
Hint : Research on the use of 'LIKE' operator in string comparison, and the usage of asterisk as wildcard. E.g. ... LIKE 'kitty' will match all words what ends with 'kitty'.
18) Referring to the tables EMPLOYEE, PROJECT and JOB (in Appendices A & B), write the SQL code that will return the results as attached in the figure below
19) Write the SQL code to find the maximum bonus percentage in the EMP_2 table
20) Write the SQL code that will produce a listing for the data in the EMP_2 table in ascending order by the bonus percentage
21) Referring to table JOB, write the SQL code to find the average job's charge per hour.
22) Write the SQL code that will produce a listing for the data in JOB table, order by job's charge per hour, in descending order
23) Referring to the table EMPLOYEE, write the SQL code that will list only the distinct job code numbers
24) Write the SQL code to compute and update all the ASSIGN_CHARGE values in the ASSIGNMENT table, given that the value is given by the formula:
ASSIGN CHARGE = ASSIGN CHG HR *ASSIGN HOURS
25) Using the data from ASSIGNMENT and EMPLOYEE tables, write the SQL code that will produce the total number of hours worked for each employee, and the total charges based on those hours worked. The figure below shows a sample of the results your code should produce.
26) Write a query to produce the total number of hours and charges for each of the projects represented in the ASSIGNMENT table. The figure below shows a sample of the results your code should produce.
(Hint : the 2nd / 3rd column's names gives you a clue!)
27) Write the SQL code to generate the total hours worked and the total charges made by all employees. (Hint: This is a nested query. Analyze your answer for Qn 25 as the basis for the query that will produce the sample output attached in figure below)
28) Write the SQL code to generate the total hours worked and the total charges made to all projects. The results should be the same as those attached in sample output for Qn 27. (Hint: This is a nested query. Analyze your answer for Qn 26 as the basis for the query that will produce the sample output attached in figure in Qn 27)
Attachment:- db.zip