What this exercise is about
This exercise gives you the opportunity to work with scalar functions.
What you should be able to do
At the end of the lab, you should be able to:
• Code queries using scalar functions
Introduction
See the data model at the start of this Exercise Guide to get the column names and descriptions for each table.
Requirements
• Student handout • SQL Reference
Exclusivo
formación proyecto
C.F.T.I.C.
Problem list
Problem 1
Produce a report listing all employees whose last name ends with 'N'. List the employee number, the last name, and the last character of the last name used to control the result. The LASTNAME column is defined as VARCHAR. There is a function which provides the length of the last name. Sort the result by employee number.
Problem 2
For each project, display the project number, project name, department number, and project number of its associated major project (COLUMN = MAJPROJ). If the value in MAJPROJ is NULL, show a literal of your choice instead of displaying a null value.
List only projects assigned to departments D01 or D11. The rows should be listed in project number sequence.
Problem 3
The salaries of the employees in department E11 will be increased by 3.75 percent.
What will be the increase in dollars? Display the last name, actual yearly salary, and the salary increase rounded to the nearest dollar. Do not show any cents.
Problem 4
For each female employee in the company present her department, her job and her last name with a colon followed by one blank between job and last name.
Problem 5
Calculate the difference between the date of birth and the hiring date for all employees for whom the hiring date is more than 30 years later than the date of birth. Display employee number and calculated difference. The difference should be shown in years, months, and days - each of which should be shown in a separate column. Make sure that the rows are in employee number sequence.
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 6. Scalar Functions 6-3
Uempty
Problem 6
Display project number, project name, project start date, and project end date of those projects whose duration was less than 10 months. Display the project duration in days.
Problem 7
List the employees in department D11 who had activities. Display employee number, last name, and first name. Also, show the activity number and the activity duration (in days) of the activities started last. Multiple activities may have been started on the same day.
Problem 8
How many weeks are between the first manned landing on the moon (July 20, 1969) and the first day of the year 2000?
Problem 9
Find out which employees were hired on a Saturday or a Sunday. List their last names and their hiring dates.
End of Problem list
Exclusivo
formación proyecto
C.F.T.I.C.
Problem list with Expected Results
Problem 1
Produce a report listing all employees whose last name ends with 'N'. List the employee number, the last name, and the last character of the last name used to control the result. The LASTNAME column is defined as VARCHAR. There is a function which provides the length of the last name. Sort the result by employee number.
For each project, display the project number, project name, department number, and project number of its associated major project (COLUMN = MAJPROJ). If the value in MAJPROJ is NULL, show a literal of your choice instead of displaying a null value.
List only projects assigned to departments D01 or D11. The rows should be listed in project number sequence.
The first row in the result set above will not have the text 'NO MAJOR PROJECT' when the query is run on DB2 for Linux, UNIX, and Windows as MAJPROJ is blank and not null for PROJNO AD3100.
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 6. Scalar Functions 6-5
Uempty
Problem 3
The salaries of the employees in department E11 will be increased by 3.75 percent.
What will be the increase in dollars? Display the last name, actual yearly salary, and the salary increase rounded to the nearest dollar. Do not show any cents.
Result
For each female employee in the company present her department, her job and her last name with a colon followed by one blank between job and last name.
Result
Calculate the difference between the date of birth and the hiring date for all employees for whom the hiring date is more than 30 years later than the date of birth. Display employee number and calculated difference. The difference should be shown in years, months, and days - each of which should be shown in a separate column. Make sure that the rows are in employee number sequence.
Exclusivo
formación proyecto
C.F.T.I.C.
Result
Display project number, project name, project start date, and project end date of those projects whose duration was less than 10 months. Display the project duration in days.
List the employees in department D11 who had activities. Display employee number, last name, and first name. Also, show the activity number and the activity duration (in days) of the activities started last. Multiple activities may have been started on the same day.
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 6. Scalar Functions 6-7
Uempty Result
How many weeks are between the first manned landing on the moon (July 20, 1969) and the first day of the year 2000?
Result
WEEKS --- 1588
Problem 9
Find out which employees were hired on a Saturday or a Sunday. List their last names and their hiring dates.
Result
End of Problem list with Expected Results
Exclusivo
formación proyecto
C.F.T.I.C.
Solutions
Problem 1
SELECT EMPNO, LASTNAME,
SUBSTR(LASTNAME,LENGTH(LASTNAME),1) AS LASTCHAR FROM EMPLOYEE
WHERE LASTNAME LIKE '%N' ORDER BY EMPNO;
An alternate solution is:
SELECT EMPNO, LASTNAME,
SUBSTR(LASTNAME,LENGTH(LASTNAME),1) AS LASTCHAR FROM EMPLOYEE
WHERE SUBSTR(LASTNAME, LENGTH(LASTNAME),1) = 'N' ORDER BY EMPNO;
An even better solution, that would work with LASTNAME being defined as either CHAR(x) or VARCHAR(x), is:
WHERE RTRIM(LASTNAME) LIKE '%N'
SELECT EMPNO, LASTNAME, WORKDEPT, SALARY,
DECIMAL(SALARY * 0.0375 + 0.5, 5,0) AS AMOUNT FROM EMPLOYEE
WHERE WORKDEPT = 'E11';
or by using ROUND():
SELECT EMPNO, LASTNAME, WORKDEPT, SALARY,
DECIMAL(ROUND(SALARY * 0.0375 ,0), 5, 0) AS AMOUNT FROM EMPLOYEE
WHERE WORKDEPT = 'E11';
Round() gives in DB2 for z/OS, a DECIMAL; in DB2 for Linux, UNIX, and Windows, a FLOATING point value.
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 6. Scalar Functions 6-9
Uempty
Problem 4
SELECT WORKDEPT, CAST(RTRIM(JOB) AS VARCHAR(10)) !! ': '!! LASTNAME AS LISTING
FROM EMPLOYEE WHERE SEX='F';
Problem 5
SELECT EMPNO, YEAR(HIREDATE - BIRTHDATE) AS YEARS, MONTH(HIREDATE - BIRTHDATE) AS MONTHS, DAY(HIREDATE - BIRTHDATE) AS DAYS FROM EMPLOYEE
WHERE HIREDATE - BIRTHDATE > 300000 ORDER BY EMPNO;
An alternate solution is:
SELECT EMPNO, YEAR(HIREDATE - BIRTHDATE) AS YEARS, MONTH(HIREDATE - BIRTHDATE) AS MONTHS, DAY(HIREDATE - BIRTHDATE) AS DAYS FROM EMPLOYEE
WHERE HIREDATE > BIRTHDATE + 30 YEARS ORDER BY EMPNO;
Problem 6
SELECT PROJNO, PROJNAME, PRSTDATE, PRENDATE,
DAYS(PRENDATE) - DAYS(PRSTDATE) AS DAYS_DURATION FROM PROJECT
WHERE PRENDATE - 10 MONTHS < PRSTDATE;
Problem 7
SELECT E.EMPNO, LASTNAME, FIRSTNME, ACTNO, EMSTDATE, DAYS(EMENDATE) - DAYS(EMSTDATE) AS DAYS_DURATION FROM EMPLOYEE E JOIN EMP_ACT EA
ON E.EMPNO = EA.EMPNO
WHERE EMSTDATE = (SELECT MAX(EMSTDATE) FROM EMP_ACT WHERE EMPNO = E.EMPNO)
Problem 8
SELECT (DAYS('2000-01-01') - DAYS('1969-07-20')) / 7 AS WEEKS FROM EMPLOYEE
WHERE EMPNO = '000010';
Note that the scalar function WEEK only gives the week-number of the year for a date.
Problem 9
SELECT HIREDATE,LASTNAME FROM EMPLOYEE
WHERE DAYOFWEEK(HIREDATE) IN (1,7);
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 7. Table Expressions and Recursive SQL 7-1