This first worksheet limits the queries to only one database table.

Having completed Lab 10 and 11 and the lecture on Access, you should be able to perform the following tasks:

• Open a Database
• Close a Database
• Create a Query
• Close a Query
• Omit Duplicates
• Print the results of a Query
• Return to the Design View
• Run a Query
• Save a Query
• Sort Data in a Query
• Sort on Multiple Keys
• Use a Comparison Operator in a Criterion
• Use a Compound Criterion Involving AND
• Use a Compound Criterion Involving OR
• Use a Number in a Criterion
• Use a Wildcard
• Use Text Data in a Criterion

For this worksheet we will use the following Access table (Employee):

Your employer has determined a number of questions they want the database management system to answer. You must obtain answers to the questions posed by your employer.

Instructions: Use the Employee table above to fill in the design grid for each question.

1. Display for each employee, his or her employee identification, department name, last and first name.

2. Display in alphabetical order the last names of employees who live in Indiana. (IN)

3. Display employees (first and last name) who are older than 45. Sort by increasing age. (Hint use the birthdate)

4. Display the full names and SS number of employees who work for the marketing (MAR) or the finance department and where hired before the January 1, 2000.

5. List the names (first and last) of employees who have the same area code as the company (765)

6. List alphabetically the full names of employees plus their departments who do not have an email listed.

7.List all employees (first and last name) sorted by their departments who were hired last year.

8. List the SS#, first name and department of employees with the last name of Smith.

9.List in alphabetical order first by last name then by first name, the employees who live on Beach Street

10.List the home phone number and names of the employees who live in Muncie, IN.