More Microsoft Access and Relational
Databases
1. Query Construction in SQL View:
Today’s lab looks at an
additional component of the Data Services layer: formulating queries that select, project and
join up data from different tables.
Access uses a GUI called the Query-By-Example so users can build queries
visually. In the background, Access
translates the visually represented query into SQL (Structured Query Language),
the standardized language used to communicate with a database. This SQL View can also be used to build
queries using straight SQL syntax.
Data Services
The main unit
of data services (data storage) is a Table.
Data storage allows for persistent data (the data stays even when
the power goes off). A table is a
collection of data about a specific topic.
Tables
organize that data in columns (called attributes) and rows (called
records). One of the attributes
(columns) of the table is almost always a number data type that is used as a
key. A key is used to uniquely identify
the data in a particular row. Key values must be unique and not null. A primary key is just the attribute(s) that
have been designated as the main key for the table.
Data
Services also includes the storage engine and the query engine which deal with
the saving, accessing and manipulation of the data. A Query is used to access and
manipulate the data. Queries are also called views because they are a user’s
“view” of the data in the database.
The important points to remember when creating a query are:
Example:
tblAdvisor.LName and tblStudent.LName
refer to 2 distinct attributes.
If you simply use LName in a query on those 2
tables, Access will not know which attribute you are referring to.
SELECT *
FROM tblAdvisor;
For the first view, the number
of rows displayed should be the same number as the records in your student
table since you asked for all students.
Now let’s take a look at creating
a query using a GUI instead of just typing in commands.
QBE, or Query By Example, is a graphical query language. This tool allows a user to follow a
"drag and drop" method of constructing a query against a data
store. You can consider the QBE as a
translation tool that takes user input in graphical locations and generates
SQL.
What goes
where in the QBE:
By breaking down the interface, a user can see how this tool is able to
construct SQL with the input provided.
You shouldn’t have to double check to see if the query is correct, but if you aren’t sure of the join, look in your tables to see what information you should get.
Determine what information is necessary to show or what has been requested. Not all attributes used in a query need to be shown to the user.
Ø
Show me all students (just their first and last
names) and the name of their Major.
Ø
Show all students (first name and last name),
their major and their advisor. Sort by
major.
Ø Show all students with an ART major OR a CSE Major. (Use two Majors you actually entered if you don’t have ART or CSE)
Ø Show who advises student number 4 (StudentID).
Ø Show all Students who have an Advisor from the certain department AND have a particular Major. Only show the student names.