DB2 SQL Workshop Day 1 of Week 5 时间安排 40分钟 （必选） 练习内容 新增加一个表的结构：EMP_ACT（员工与项目的关系） EMPNO CHAR(6) NOT NULL, PROJNO CHAR(6) NOT NULL, ACTNO SMALLINT NOT NULL, EMPTIME DECIMAL(5, 2) , EMSTDATE DATE , EMENDATE DATE 数据如下 EMPNO PROJNO ACTNO EMPTIME EMSTDATE EMENDATE ---------+---------+---------+---------+---------+----- 000010 AD3100 10 .50 1982-01-01 1982-07-01 000070 AD3110 10 1.00 1982-01-01 1983-02-01 000230 AD3111 60 1.00 1982-01-01 1982-03-15 000230 AD3111 60 .50 1982-03-15 1982-04-15 000230 AD3111 70 .50 1982-03-15 1982-10-15 000240 AD3111 70 1.00 1982-02-15 1982-09-15 000230 AD3111 80 .50 1982-04-15 1982-10-15 000240 AD3111 80 1.00 1982-09-15 1983-01-01 000230 AD3111 180 1.00 1982-10-15 1983-01-01 000250 AD3112 60 .50 1982-02-01 1982-03-15 000250 AD3112 60 1.00 1982-01-01 1982-02-01 000250 AD3112 60 1.00 1983-01-01 1983-02-01 000250 AD3112 60 .50 1982-12-01 1983-01-01 … 000320 OP2011 140 .75 1982-01-01 1983-02-01 000320 OP2011 150 .25 1982-01-01 1983-02-01 000330 OP2012 140 .25 1982-01-01 1983-02-01 000330 OP2012 160 .75 1982-01-01 1983-02-01 000340 OP2013 140 .50 1982-01-01 1983-02-01 000340 OP2013 170 .50 1982-01-01 1983-02-01 000020 PL2100 30 1.00 1982-01-01 1982-09-15 40分钟 （必选） 1. Simple SQL Queries Problem 1. List employee number, last name, date of birth, and salary for all employees. The employees should be ordered by decreasing salaries. Problem 2. List last name, first name, and the department number for all employees. The listing should be ordered by descending department numbers. Within the same department, the last names should be sorted in descending order. Problem 3. List different education levels in the company in descending order. Problem 4. List all employees (employee number) and the projects (project number) performed by them. User the EMP_ACT table. Problem 5. List last name, salary, and bonus of all male employees. Problem 6. List last name, salary, and commission of all employees with a salary higher than $20000 and hired after 1979. Problem 7. List last name, salary, bonus, and commission of all employees with a salary higher than $20000 and a bonus of $400 or of all employees with a bonus of $500 and a commission higher than $2000. The list should be ordered by last name. Problem 8. List last name, salary, bonus, and commission of all employees with a salary higher than $20000, a bonus of $400 or $500, and a commission higher than $2000. The list should be ordered by the lats name. Problme 9. For all ‘AD’ project (project number starts with ‘AD’) with activities 10, 80, and 180, list the following: - Project number - Activity number - Starting date for activity - End date for activity The list should be sorted by project number and activity number. Problem 10. List manager number and department number for all departments to which a manager has been assigned. The list should be ordered by manager numbers. Problem 11. List employee number, last name, salary, and bonus of all employees having a bonus from $800 to $1000. The lowest bonus should appear first. Problem 12. List employee number, last name, salary, and department number of all employees of departments A00 through C01. The listing should be ordered by ascending last names. Problem 13. List all projects (ordered by the project number) for which ‘SUPPORT’ is a part of their name. Problem 14. List all departments with a ‘1’ as middle character of their department number. Order the list by department numbers. 2. Retrieving Data from Multiple Tables Problem 1. For all projects (project number) beginning with ‘AD’, list project number, project name, and activity number. List identical rows once. Order the list by project number and activity number. Problem 2. Which employees did participate in project ‘AD3113’. List employee number, last name, and project number. Order the list by employee number and project number. Problem 3. Which of the activities have been started on ‘1982-10-01’. List employee number of the person performing the activity, project number, project name, activity number, and starting date of the activity. Order the list by project number, employee number, and activity number. Problem 4. List department number, manager number, and last name of the managers of departments ‘A00’ and ‘B01’. Sort the list by department number. Problem 5. List department number, last name, project name, and activity number for activities performed by the employees of department ‘A00’. The list should be sorted by project name and activity number. Problem 6. List department number, last name, project name, and activity number for all employees. Suppress identical rows. Sort the list by department number, last name, and activity number. Problem 7. The second line managements need information on activities which started on 1982-10-15 or thereafter. For these activities, list activity number, manager number responsible for the department performing the project, the starting date for the activity, project number, and last name of the employee performing the activity. The list should be ordered by activity number and activity starting date. Problem 8. Which employees of department ‘A00’ were hired before their manager. List department number, manager’s last name, employee’s last name, hiring dates of the manager and employee. Order the list by employee last name. 3. Scalar Functions and Arithmetic Problem 1. For employees whose salary, increased by 5 percent, is less than or equal to $20000, list the following: - Last name - Salary - Salary increased by 5 percent - Monthly salary increased by 5 percent 40分钟 （必选） Sort the listing by salary. Problem 2. For all employees with an education level of 18 or 20, the salary will be increased by $1200, and the bonus will be halved. List last name, education level, the new salary and new bonus for these employees. Employees with an education level of 20 should be list first. For employees with same education level, sort the list by salary. Problem 3. The salary will be decreased by $1000 for all employees matching the following criteria: - They belong to department ‘D11’. - Their salary is more than or equal to 80 percent of $20000 - Their salary is less than or equal to 120 percent of $20000 List department number, last name, salary, and decreased salary. Sort the result by salary. Problem 4. We need a list for all employees of department ‘D11’ with at income (sum of salary, commission, and bonus) that is higher than their salary increased by 10 percent. List department number, last name, and income. Sort the result in descending order by income. Problem 5. List all department which have no manager assigned. List department number and department name. Problem 6. List project number and major project number of all projects whose project number contains ‘AD’ in position 1 and 2. If the major project number is unknown, display the text ‘MAIN PROJECT’. Problem 7. List all employees who were younger than 25 when they joined the company. List their employee number, last name, and age in years. Sort the result by age of employee when joining the company and employee number. Problem 8. We need a list of all projects which ended on 1982-12-01. Display year and month of the estimated starting date and project number. Sort the result by the project number. Problem 9. List project number and duration of all ‘MA’ projects (project number starts with ‘MA’) in weeks. The duration should be rounded and displayed with one decimal position. Order the list by the project number. Problem 10. For projects of region MA (i.e., for projects whose project number starts with ‘MA’), list project number, estimated ending dates, and expected ending date, if the duration will exceed the estimated by 10 percent. Order the list by project number. Problem 11. List the first name initial and last name of all employees whose last name starts with ‘S’. The initial should be separated from the last name by means of a period.