INF3707_TL_202_2011_1_ SCHOOL OF COMPUTING.

INF3707_TL_202_2011_1_ SCHOOL OF COMPUTING. 2 Lecturers responsible for INF3707 The lecturers are Mr Baldreck Chipangura and Dr E Kritzinger. Please phone us on or send e‐mails to INF3707/202/1/2010 3 | Page 3. Assignment 2 Question 1 (Lesson 4) a. The Human Resources department needs a report that displays the job ID and the average monthly salary for each job that has an average payroll that is between 5000 and 10000. The report should not include clerks. Sort the list in descending order of average salary. Sample results are shown below. Save your query as Q1A.SQL. [6] Solution select job_id, avg(salary)"Average salary" from employees where job_id not like '%CLER%' group by job_id having avg(salary) between 5000 and 10000 order by avg(salary)desc b. Create a query that displays the minimum, maximum, and total salary for each job type and the date the query was run. Sample results are shown below. Save your query as Q1B.SQl. [4] JOB_ID MINIMUM MAXIMUM SUM Date IT_PROG -APR-10 AC_MGR -APR-10 AC_ACCOUNT -APR-10 ST_MAN -APR-10 PU_MAN -APR-10 AD_ASST -APR-10 AD_VP -APR-10 SH_CLERK -APR-10 FI_ACCOUNT -APR-10 FI_MGR -APR-10 More than 10 rows available. Increase rows selector to view more rows. JOB_ID Average Salary IT_PROG 5760 MK_REP 6000 HR_REP 6500 ST_MAN 7280 FI_ACCOUNT 7920 AC_ACCOUNT 8300 SA_REP 8350 PR_REP 10000 INF3707/202/1/2010 4 | Page Solution select job_id, min(salary) Minimum, max(salary) Maximum, sum(salary) Sum, sysdate “Date” from employees group by job_id Question 2 (Lesson 6) The human Resources department needs to find the names and hire dates for all employees who were hired before 29 January 2000, along with their managers’ names. Sample results are shown in the table below. Save your query as Q2A.SQL. [6] Worker Name Hire Date Manager Name Kochhar 21-SEP-89 King De Haan 13-JAN-93 King Hunold 03-JAN-90 De Haan Ernst 21-MAY-91 Hunold Austin 25-JUN-97 Hunold Pataballa 05-FEB-98 Hunold Lorentz 07-FEB-99 Hunold Greenberg 17-AUG-94 Kochhar Faviet 16-AUG-94 Greenberg Chen 28-SEP-97 Greenberg More than 10 rows available. Increase rows selector to view more rows Solution select _name "Worker Name", _date "Hire Date", _name "Manager Name" from employees w join employees m on er_id = yee_id where _date = '29-JAN-2000' INF3707/202/1/2010 5 | Page Question 3 (Lesson 6) The Human Resources department requires you for find details of employees who earn salary that is more than the average salary for each department. Sample results are shown in the table below. Save your query as Q3A.SQL. [6] LAST_NAME SALARY DEPARTMENT_ID King 24000 90 Kochhar 17000 90 De Haan 17000 90 Russell 14000 80 Partners 13500 80 Hartstein 13000 20 Greenberg Errazuriz 12000 80 Higgins Ozer 11500 80 More than 10 rows available. Increase rows selector to view more rows Solution select last_name, salary, department_id from employees where salary = any (select round(avg(salary),2) from employees group by department_id) //alternative answer select _name, tment_id from employees a where salary (select avg(y) from employees b where tment_id = tment_id group by tment_id) order by y desc INF3707/202/1/2010 6 | Page Question 4 (Lesson 7) a. The job history table keeps record of the employees that filled other positions (job_id’s) within the organization. Use one of the set operators to list the employee information (name and last name) for all those employees that have never filled any other position within the organization .The report must list the employee_id and employee last name and first name. Order the list by the employee_id. Save the SQL as A2Q2A. [4] b. Use one of the set operators to list the names of all the employees that are in the Shipping Department and reports to Steven King. Sort the report alphabetically according to the last name of the employee. Save the SQL as A2Q2B. [4] Solution a. select employee_id, last_name, first_name from employees MINUS select employee_id,TO_CHAR(NULL), TO_CHAR(NULL) from job_history order by employee_id; // alternative select employee_id, last_name, first_name from employees where employee_id in (select employee_id from employees MINUS select distinct employee_id from job_history) order by 1 asc b. select last_name,first_name from employees where manager_id = 100 intersect select last_name,first_name from employees where department_id = 90 order by last_name; INF3707/202/1/2010 7 | Page Question 5 (lesson 8) a. Run the statement in the lab_08_ scripts to build the MY_EMPLOYEE table to be used for the lab. [2] b. Describe the structure of the table to identify the column names. [2] c. Write an insert statement in a dynamic reusable script file to load rows into the MY_EMPLOYEE table. Save your script as Q5C.SQL [2] Solution a. run the script b. describe My_EMPLOEE Table Column Data Type Leng th Precis ion Sca le Primary Key Nulla ble Defa ult Comm ent MY_EMPLOYE E ID Number - 4 0 - - - - LAST_NA ME Varchar2 25 - - - - - FIRST_N AME Varchar2 25 - - - - - USERID Varchar2 8 - - - - - SALARY Number - 9 2 - - - 1 - 5 c. insert into my_employee values(:id, :first_name,:last_name,:userid, :salry) INF3707/202/1/2010 8 | Page Question 6(Lesson 9) Create the EMPLOYEES_2 table based on the structure of the EMPLOYEES table. Include only the employees_id, initial of first name, last name, the last four digits of the phone number, salary dived by 2, and department id. Name the columns in the new table as ID, INITIAL, SURNAME, PHONE EXTENSION, SALARY, DEPRTMENT. Save the SQL as Q6A.SQL.