An interesting problem is that of how to write queries
based on some criteria referring to a varying list of values. This case
is best illustrated by looking for employees who have certain skills,
using the three tables shown in Figure 11-6. The
skillset table links employees to
skills, associating a 1 to 3
skill_level value to distinguish between
honest competency, strong experience, and outright wizardry.
Figure 11-6. Tables used for querying employee skills
Finding employees that have a level 2 or 3 SQL skill is easy enough:
select e.employee_name from employees e where e.employee_id in (select ss.employee_id from skillset ss, skills s where s.skill_id = ss.skill_id and s.skill_name = 'SQL' and ss.skill_level >= 2) order by e.employee_name
(We can also write the preceding query with a simple join.) If we want to retrieve the employees who are competent with Oracle or DB2, all we need to do is write:
select e.employee_name, s.skill_name, ss.skill_level from employees e, skillset ss, skills s where e.employee_id = ss.employee_id and s.skill_id = ss.skill_id and s.skill_name in ('ORACLE', 'DB2') order by e.employee_name
No need to test for the skill level, since we will accept any level. However, we do need to display the skill name; otherwise, we won't be able to tell why a particular employee was returned by the query. We also encounter ...