In this lab, we will write some simple queries using Microsoft Access.
Microsoft Access is a personal database program intended for small databases. Microsoft Access provides ways for creating tables for storing your data, queries for accessing your data, and various tools to produce reports and forms for manipulating your data. Access can be used as a complete environment for building database applications. In this lab, we will use Access to write some simple queries on a pre-existing database. A screenshot of the main Access window is below. We will only be using the Queries tab.
TASK: Download the sample WorksOn database and open with Microsoft Access.
Note: Access disables some features by default for security purposes, and will show a warning. It is safe to enable the blocked features for this file.
You can write queries in Access directly using SQL or using a graphical query builder. Since the query builder is easier, that is what we will use. First, we will get familiar with the Queries window (see screenshot below).
TASK: Click Queries tab to see the list of queries as below.
TASK: Right click on the query called SampleQuery and select Design View from the pop-up menu. You will see a screen as below.
The Run button (exclamation point) allows you to run your query and see a query result. The View button allows you to switch between designing the query graphically and the query result table, with a menu allowing you to view and edit the SQL. Tables are shown in the middle part of the screen. To add tables, right-click on an open area and select Show Table.. from the pop-up menu. Then pick the tables you want. Right-clicking on a table allows you to select an option to remove the table from the query.
The part that looks like a spreadsheet on the bottom is designed to allow you to add fields, selection criteria, and sorting information to your query. You can add a field by selecting it in an open column from the drop-down box. Fields will be shown in the order they are given in the columns. Access will automatically put the table in the table slot once you select a field.
The Show checkbox indicates if a column is in the result or not. A column is only in the result if the box is checked. The Criteria field is used to specify selection or filtering conditions.
TRY FOR YOURSELF: Experiment with the query interface and change SampleQuery in various ways to see what happens.
Open the query Example1_EmpTitleEE that shows only the name and salary of employees if their title is 'EE'. This query combines a projection (only want name and salary attributes) with a selection (only want employees if their title is 'EE'). Note that the title attribute is not shown, but is used only to filter out employees by their title.
TASK #1: Create a new query called Task1 that lists all projects (number and name only) that have a budget > $200,000.
Open the query Example2_OrderBy that shows only the name, salary, and birth date of Employees if their salary is >$30,000. The employees are ordered by salary in descending order (Note entry of Descending in Sort field.)
TASK #2: Create a new query called Task2 that lists all WorksOn records where the resp is "Manager" OR "Analyst". Sort the records in decreasing order by hours.
Open the query Example3_JoinQuery that joins the information in the Emp, Proj, and WorksOn relation using equijoins. Only rows where the hours worked is > 20 are kept, and the rows are sorted in descending order by employee name. Note the lines connecting the tables in the view. These lines represent the joins. For this database, Access will put them in automatically for you. If you are curious what that looks like in SQL, switch to the SQL view. Once the joins are added, everything is like with a single table, but now we have more fields to choose from (in this case from any of the three tables that we joined together).
TASK #3: Create a new query called Task3 that joins the Dept, Proj, and WorksOn tables together. In the result, only show the attributes dno, dname, pno, pname, eno, hours. Only show rows where dno = "D2" and budget > 150000.
For this lab assignment, submit your Microsoft Access file WorksOn.accdb containing your three queries called Task1, Task2, and Task3.
Home