Problem 1
Display all employees who work in the INFORMATION CENTER department. Show department number, employee number and last name for all employees in that department. The list should be ordered by employee number.
Use the "old" SQL syntax that puts the join condition in the WHERE clause.
Note
Use the views VDEPARTMENT, VEMPLOYEE, and VPROJECT for problems 1 through 6.
Result
WORKDEPT EMPNO LASTNAME --- --- --- C01 000030 KWAN
C01 000130 QUINTANA C01 000140 NICHOLLS
Problem 2
Solve problem 1 again using the newer SQL syntax that places the join condition in the ON clause.
Result
WORKDEPT EMPNO LASTNAME --- --- --- C01 000030 KWAN
C01 000130 QUINTANA C01 000140 NICHOLLS
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 3. Joins 3-5
Uempty
Problem 3
Bill needs a list of those employees whose departments are involved in projects.
The list needs to show employee number, last name, department number, and project name. The list should be ordered by project names within employee numbers.
Now Bill wants to see all employees, whether or not their departments are involved a project. The list needs to show the employee number, last name, department
number, and project name. If the department of an employee is not involved in a project, display NULLs instead of the project name. The list should be ordered by project name within employee number.
Result
Problem 5
Now Bill wants to see all projects, including those assigned to departments without employees. The list needs to show employee number, last name, department number, and project name. If a project is not assigned to a department having employees, NULLS should be displayed instead of the department number,
employee number and last name. The list should be ordered by employee number within project name.
Bill wants to see all projects and all employees in one report. Projects not assigned to departments having employees should also be listed as well as employees who work in departments which are not involved in projects. The list needs to show employee number, last name, department number, and project name. If a project is not assigned to a department having employees, NULLS should be displayed instead of the department number, employee number and last name. If the
department of an employee is not involved in a project, display NULLs instead of the project name. The list should be ordered by project name within last name.
Result
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 3. Joins 3-7
Uempty
Problem 7
Which employees in department A00 were hired before their manager? Please note that you should use the real tables (rather than the views) for this problem in the exercise.
List department number, the manager's last name, the employee's last name, and the hiring dates of both the manager and the employee.
Order the list by the employee's last name.
Result
DEPTNO MANAGER EMPLOYEE M_HIREDATE E_HIREDATE ---+---+---+---+---+--- A00 HAAS LUCCHESI 1965-01-01 1958-05-16 A00 HAAS O'CONNELL 1965-01-01 1963-12-05
Problem 8
List the department number and department name of the departments which do not have any employees assigned to them. You may use any of the three "anti-join"
methods discussed in the Unit 3 lecture.
Result
DEPTNO DEPTNAME
---+---+---+---+---D01 DEVELOPMENT CENTER
End of Problem list with Expected Results
Exclusivo
formación proyecto
C.F.T.I.C.
Solutions
Problem 1
SELECT WORKDEPT, EMPNO, LASTNAME FROM VEMPLOYEE, VDEPARTMENT WHERE WORKDEPT = DEPTNO
AND DEPTNAME = 'INFORMATION CENTER' ORDER BY EMPNO;
Problem 2
SELECT WORKDEPT, EMPNO, LASTNAME
FROM VEMPLOYEE INNER JOIN VDEPARTMENT ON WORKDEPT = DEPTNO
WHERE DEPTNAME = 'INFORMATION CENTER' ORDER BY EMPNO;
Problem 3
SELECT EMPNO, LASTNAME, WORKDEPT, PROJNAME FROM VEMPLOYEE INNER JOIN VPROJECT
ON WORKDEPT = DEPTNO ORDER BY EMPNO, PROJNAME;
Problem 4
SELECT EMPNO, LASTNAME, WORKDEPT, PROJNAME FROM VEMPLOYEE LEFT OUTER JOIN VPROJECT ON WORKDEPT = DEPTNO
ORDER BY EMPNO;
Problem 5
SELECT EMPNO, LASTNAME, WORKDEPT, PROJNAME FROM VEMPLOYEE RIGHT OUTER JOIN VPROJECT ON WORKDEPT = DEPTNO
ORDER BY PROJNAME;
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 3. Joins 3-9
Uempty
Problem 6
SELECT EMPNO, LASTNAME, WORKDEPT, PROJNAME FROM VEMPLOYEE FULL OUTER JOIN VPROJECT ON WORKDEPT = DEPTNO
ORDER BY LASTNAME, PROJNAME;
Problem 7
SELECT D.DEPTNO, M.LASTNAME AS MANAGER, E.LASTNAME AS EMPLOYEE, M.HIREDATE AS M_HIREDATE, E.HIREDATE AS E_HIREDATE
FROM EMPLOYEE M, EMPLOYEE E, DEPARTMENT D WHERE D.MGRNO = M.EMPNO
AND E.WORKDEPT = D.DEPTNO AND M.HIREDATE > E.HIREDATE AND E.WORKDEPT = 'A00' ORDER BY EMPLOYEE;
OR
SELECT D.DEPTNO, M.LASTNAME AS MANAGER, E.LASTNAME AS EMPLOYEE, M.HIREDATE AS M_HIREDATE, E.HIREDATE AS E_HIREDATE
FROM EMPLOYEE AS E FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO WHERE E.WORKDEPT IS NULL;
Alternate Solution #1
SELECT DEPTNO, DEPTNAME FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO
FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO ;
Alternate Solution #2
SELECT DEPTNO, DEPTNAME FROM DEPARTMENT WHERE DEPTNO NOT IN (SELECT WORKDEPT
FROM EMPLOYEE);
End of solutions
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 4. CASE and CAST 4-1
Uempty
Exercise 4. CASE and CAST
What this exercise is about
This exercise give you the possibility to work with CASE and CAST expressions.
What you should be able to do
At the end of the lab, you should be able to:
• Use CASE expressions in the SELECT list • Use CASE expressions in the WHERE clause • Use CAST to convert between data types
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
For all employees in department D11, display their employee number, first name, last name and text specifying if the employee's salary is low, high or normal.
Problem 2
For all employees in department D11, display their last name, salary and the absolute difference (in other words, display the difference as a positive number, whether or not the salary is greater than or less than 30,000) between their salary and 30000. Sort the list in order by the difference with the lowest difference first.
Problem 3
List the employees who have a commission greater than 8 percent of their salary.
Protect from division by 0 in case someone has 0 salary. List the last name and use CAST to list percentage with three decimals.
End of problem list
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 4. CASE and CAST 4-3