Using Conditional Retrieval › Scenarios

Scenarios

Create the appropriate statements online to retrieve the needed data:

1. Periodically, a list is published giving divisions and their departments. A new department was recently added to division D09, so a new list for that division is needed. Use the DEPARTMENT table and show division code, department ID, and department name. Order by department ID.

   Hint: DIV_CODE is a character column.

2. All Commonwealth Auto employees whose last names begin with L and Mare due to have flu shots. The medical office needs to have the complete names of these individuals and the department to which each is assigned. Sort the list by last name. (Use the EMPLOYEE table.)
3. The Marketing department has a large project coming up and needs employees who have at least a medium level of competence (greater than 02) in skill 3333. Display employee ID and level of competence for each employee using the EXPERTISE table.

   Hint: SKILL_LEVEL is a character column.

4. In order to identify employees involved in media projects, the Human Resources department needs a list of employees associated with a project ID that begins with P (indicating media-related). Order the list by employee ID. (Use the EMPLOYEE table to find this information.)
5. The budget group needs a list of employees who hold a position that pays less than $25,000. Show employee ID and salary.

   Hint: The POSITION table contains information about positions held in Commonwealth Auto.