Microsoft Access Simplest Option

Setting up Microsoft Access Database

Setting up Microsoft Access is perhaps the simplest option available.
All you need to do is download the appropriate file for Microsoft Access. When prompted about where to save the file, simply indicate the working directory you want to use for this class. The database will download and you will be ready to go. After you have downloaded the file, open the database in Access. Select the Queries tab and you will see a test query listed. This query has a single statement:

SELECT * FROM authors

When you double-click this query, you will receive a screen of results. You can close this window, returning to the Queries tabbed dialog box.

A major reason your exposure to SQL is limited when using MS Access is that Access is more user friendly than most people give it credit for being. The fact is, Access performs a majority of its actions in user-friendly environments that hide the real work that goes on behind the scenes. For a demonstration of this, build in Design view the query you see in Figure 2-7. In this relatively simple query, you are asking for the sum of revenue by period.

Figure 2-7: Build this relatively simple query in Design view.

Next, select the Design tab on the Ribbon and choose View => SQL View. Access switches from Design view to the view you see below where the SQL is generated.

SELECT Dim_Dates.Period, Sum(Dim_Transactions.LineTotal) 
AS Revenue 
FROM Dim_Dates INNER JOIN Dim_Transactions 
ON Dim_Dates.SysDate = Dim_Transactions.OrderDate
GROUP BY Dim_Dates.Period;

The concept of a database, a logically confined data storage, managed by a program is rather intuitive. When using a desktop database such as Microsoft Access, your database is a file that Access creates for every new project you start; the server-based relational database management systems use a similar concept, though the details of implementation are much more complex. Fortunately, the declarative nature of SQL hides this complexity. It tells what needs to be done, not how to do it.

In the beginning, there was a database. The database we will use throughout the module will contain all the books we have on the shelves. A book tracking database that stores titles, ISBN numbers, authors, price. The following statement creates a database named LIBRARY in your RDBMS (providing it is Microsoft SQL Server, IBM DB2, PostgreSQL or MySQL) things are a bit different with Oracle, which subscribes to a different notion of what is considered a database.

CREATE DATABASE library;

If you have sufficient privileges in the RDBMS instance, the preceding statement will create a database, a logical structure to hold your data, along with all supporting structures and files.
Oracle's syntax would be similar to this:

CREATE 
CREATE  USER library IDENTIFIED 
BY discover;

With USER being roughly an equivalent of the DATABASE in some other RDBMS.
Access Database