Database - Creating Queries and SQL

Open Access and then open the CreatingRelationshipsDB database that you used in the previous exercise.

1. Click on the Queries icon in the Objects list. Use the Create Query Wizard to create a query that selects the last name, department ID and salary of each employee from the employees table. In the first screen select the employees table and then select the specific fields that you want to select from the table. Click NEXT.

2. Choose detail query in the next window. Click NEXT.

3. Name the query Salaries and then click Finish. What happens when you click finish?

4. Close the query window once you have looked at the results. Return to the list of queries and right click on the Salaries query and select Design View.

5. You now want to only select employees who work in department 80 or 90 so you need to add a condition to your query. Find the department_id column in the design view of the query. In the Criteria row under the department_id column type in "=80". Save the query and run it. Which employees are selected this time?

6. Return to the design view for this query and in the department_id column in the OR row type in
" =90". Save the query and run it.

7. Create a new query that selects the name, department_id and salary of every employee who makes more than $10,000. Based on this query which department is the best one to work in? Save your query as Money.

8. Create two queries of your own.

9. When you create a query in Access the program is actually writing an SQL statement. SQL is the actual language that is used to access any database. Access is providing a GUI interface so anyone can create a query even if they don't know any SQL programming. This is very similar to the way that Dreamweaver creates HTML code when someone designs a web page in that program. In order to see the SQL code behind your queries, open the Money query and then go to View > SQL View. Copy the SQL code from one of the queries in this exercise and paste it here. EXPLAIN WHAT EACH LINE DOES.

10. Explain what the following SQL statement does.

SELECT EMPLOYEES.LAST_NAME, EMPLOYEES.HIRE_DATE
FROM EMPLOYEES
WHERE (((EMPLOYEES.HIRE_DATE)>#12/31/1998#));

11. Show your queries and answers to your instructor for teacher check____________________