Chapter 4

1. Write a query that will select all columns from the projectAssignment table without using the (*) in your query. You may wish to use the DESCRIBE command to examine the structure of the projectAssignment table.

2. Your manager is concerned about the cost of hospital equipment. Using the equipment table, produce a listing that includes the EquipmentNumber, Description, and OriginalCost where the cost is greater than $1,000.00. Sort the output in descending order by OriginalCost.

3. Data about services that can be provided to patients are stored in the service table. Management needs a listing that includes the ServiceID, Description, and StandardCharge for any service where the standard charge is $5,000.00 or more. The output should be sorted from most expensive to least expensive service.

4. From the service table list the categories of services offered (CategoryID) without listing any duplicates. The result table for this query is a single column. Show all rows of the result table.

5. Management is concerned about prescriptions issued for medicine that falls within a specific medicine code category. Using the prescription table, list the medicines that have been prescribed that fall within the medicine code value 9999012. The listing should include PrescriptionNumber, PatientID, and MedicineCode columns. Show all rows of the result table.

Chapter 5

1. Execute a query that will display all male employees in department number 8. The result table should list each employee's first name, last name, gender, and department number. In order to display each row on a single output line, format the employee first and last name columns as A15. Use meaningful column names such as "First Name."

2. Execute a query that will display all employees with a Salary value that is more or equal to $20,000, and are either in department 3 or are male. List each employee first name, last name, salary, gender, and department number.

3. Execute a query that will display all employees who earn exactly $5,500, $4,550, or $2,200. List each employee's first name, last name, and salary. Use the IN operator. Do not concern yourself with NULL values.

4. Execute a query that will display all employees who DO NOT earn exactly $5,500, $4,550, or $2,200, and who have a last name that begins with the letter 'S'. List each employee's first name, last name, and salary. Do not concern yourself with NULL values.

5. Execute a query that will display all employees whose last name contains the lower case letter 'o'. List each employee's first and last name.

6. Execute a query that will display all employees whose last name contains the lower case letter 'o' except for the second character (i.e., the second character can be anything but o. List each employee's first and last name.

7. Execute a query that will display each employee's last name, annual salary, monthly salary, and weekly salary. The list should only include employees with a weekly salary that is less than $1,000.00. Label the column names for annual salary, monthly salary, and weekly salary as Annual, Monthly, and Weekly, respectively. Sort the output by employee last name. Format the columns named Annual, Monthly, and Weekly as $999,999.99. Be careful in how you compute the weekly salary! List the first five rows of the result table in your report. (Note: The salary values in the Employee table are Monthly Salary.)

8. Execute a query that lists all employee table rows that contain a null value in the SupervisorID column. List each employee's last name and supervisor identifying number.

9. Execute a query that lists all female employees with a job title (Title column) of 'M.D.' list each employee's last name, first name, title, and gender.

10. Execute a query for the ProjectAssignment table to list each employee who worked on either project 3 or 4, and who has worked more than 15 hours on the selected project. List each employee's identifying number (EmployeeID), project number, and hours worked.