CIS 5.2                                                                       Professor Sokol

Introduction to Computer Applications    

Queries for the JSP Recruiters Database

1. Display the Client number and name for all clients that are situated in Tarleton.

2. Display the Client number, name, address, and zip code of all Clients whose zip code begins with 803

3. Display the entire record of the Recruiter (from the Recruiter table) whose initials are J.F.

4. You are asked to display the Client record of Ferb Dentistry, yet you are unsure whether Ferb is spelled “Furb,” “Ferb,” or “Firb.”  Design a query that retrieves the record whether you spell the name correctly or not.

5. Display the Client names, amount paid, and current due of all Clients whose amount paid is more than $20,000. and less than $30,000.

6. Display the Client number, name, and address of all Clients that have no debt.

7. Display the average amount of the Current Due per city (use the Client table).

8. Display all fields for all Clients in the city of Mason or Berridge.

9. Display the amount that each Recruiter has brought in to the company this year (hint: calculate this using commission/rate.)

10. Display the Last and First name of each Recruiter and the Client numbers and names that the Recruiter works with. The list should be sorted by the Recruiters’ last names.

11. Display the total amount that all clients of each Recruiter has paid thus far (hint: group by Recruiter number).