SQL Versions

There are also different versions of SQL. There are currently two versions of the SQL language, and a third version is in the works. The two current versions of SQL are referred to as SQL-89 and SQL-92. SQL-92 is the latest version and functions at a more advanced level because it contains more features than SQL-89.

Currently, most versions of Microsoft Access come with version SQL-89 installed. In Microsoft Access 2002 and higher you can set the SQL version through the user interface for the current database and as the default setting for new databases.

SQL Components

The Structured Query Language is broken up into three components: DDL, DML, and DCL.

The Data Definition Language (DDL) component is used to create tables and establish relationships among tables.

The Data Manipulation Language (DML) component is used to manage the database by performing such operations as retrieving data, updating data, deleting data, and navigating through data.

The Data Control Language (DCL) component is used to provide security features for the database.

SQL Syntax

In order to implement SQL, you must follow a series of rules that state how SQL script must be written. These rules are referred to as syntax. When a syntax rule is violated, the DBMS will return a system-generated error message to the screen. Stick to the syntax and you can reduce the probability of seeing these unpleasant messages.

The SQL language is made up of a series of keywords, statements, and clauses. The keywords, statements, and clauses are combined to enable users to create queries that extract meaningful data from the database. A query is a question or command posed to the database, and keywords are reserved words used within queries and SQL statements. Keywords are considered reserved because they cannot be used to name parts of the database. For example, you cannot use a keyword to name the database, tables, columns, or any other portion of the database.

A clause is a segment of an SQL statement. Think of a clause as a prepositional phrase that modifies the data in some way. A WHERE clause, for example, restricts where the data comes from. An ORDER BY clause orders the data. Clauses combine to form the entire SQL statement, which combines keywords with data to form a database query. Since you cannot have an actual conversation with the database like you would a person, keywords, statements, and clauses help you convey what you need to accomplish. Within the next few chapters, you will learn how to implement keywords, statements, and clauses in Microsoft Access.

The Query Wizard

Microsoft Access provides several tools to enable you to create queries. Probably the most popular and simplest query tool to use is called Query Wizard. The Query Wizard, shown in Figure 2-1, enables the user to create simple queries by simply answering a series of questions. The questions pinpoint which columns you want to display and how you want to display the results from a query.

Figure 2-1. Query Wizard

The Query Design Tool and SQL View

Another straightforward query tool that is simple to use is referred to as Query Design view or Design view. The Query Design tool, shown in Figure 2-2, enables users to create queries by selecting table and column names and specifying conditions on the data they want to retrieve. The Query Design tool is a little more powerful because of the extra added feature of being able to set conditions on data. It also contains an SQL view that displays the SQL script from the queries created in Query Design. The SQL Query Design tool is so useful that Microsoft incorporated it into their SQL Server product a few years ago. Most people who develop in Access find that it is all that they need to write queries, and for many people this is true. (But you want to become a real power user, so we leave the Query Design view and move on to true SQL.)

SQL view is useful because you can use it to examine and learn SQL script so that you can eventually create your own customized queries. Microsoft Access makes it easy for you to switch back and forth between Query Design and SQL view by simply clicking the View button to choose which tool you want. Figure 2-2 points out the View button.

Figure 2-2. Query Design tool

Opening Microsoft Access

To create a new database in Microsoft Access, open Microsoft Access and click Blank Database under the New Blank Database heading.

At this point you must give your database a name. All databases must be named at the time they are created. You may name your database whatever you want, although it is generally a good idea to give it a short, descriptive name. While a database name can contain characters other than text or numbers, it is generally preferable to avoid these characters since they may confuse the SQL parser. Also, it is not a good idea to use SQL reserved words when naming your database since this, too, is an invitation to future problems. Remember, SQL keywords are reserved words used only within SQL statements. To name your database, type the name of your database in the File Name box located on the right side of the screen. Next, click the small folder icon to the right of the File Name box to locate where you want to save a copy of your database. The File New Database dialog will pop up so that you can browse for a location for your database. After you choose a location, click Create to save your database. Figure 2-3 illustrates the Microsoft Access window used to open an existing database or create a new database.

Figure 2-3. Microsoft Access Getting Started screen

Switching to SQL View in Microsoft Access 2007

Now that you have created a database, let’s go to the area (SQL view) where most of the SQL script in this book will be implemented. To switch to SQL view, click Create from the menu running across the top of the screen. Next, click the Query Design button near the top-right side of the screen. This will cause the Show Table dialog box to appear. Click Close in this dialog box without selecting any tables. Take a look at Figure 2-4.

Figure 2-4. Show Table dialog box

Next, locate the View drop-down button near the top left of the screen. To see the name/description of any button or tool, simply place your cursor over it. Figure 2-5 shows the View button in Query Design view.

Figure 2-5. Query Design view

To switch to SQL view, use the View button and select SQL View. (Click the down arrow and scroll to the SQL View option.) This is the view in which you will type most of the SQL script in this book. You must use the Run button to execute SQL script typed in SQL view. Figure 2-6 shows the Run button in SQL view.

Figure 2-6. SQL view

Switching to SQL View in Microsoft Access 2003

If you are using a version of Microsoft Access that was released earlier than Microsoft Access 2007 (e.g., Microsoft Access 2003), the process for locating SQL view will differ slightly.

To locate SQL View in Microsoft Access 2003, open Microsoft Access and either create a new database or select an existing database. Next, click Queries on the left, and then click the New button located near the top of the screen. When the New Query dialog box appears, select Design View and click OK. Take a look at Figure 2-7.

Figure 2-7. Microsoft Access 2003 New Query dialog with Design View selected

Click Close in the Show Table dialog box (do not select any tables). You are now in Query Design view. Next, locate the View button near the top of the screen. Figure 2-8 shows the View button in Query Design view.

Figure 2-8. Microsoft Access 2003 Query Design view

To switch to SQL view, use the View button and select SQL View. (Click the down arrow located on the View button to find the SQL View option.) You must use the Run button to execute script typed in SQL view. Figure 2-9 shows the Run button in SQL view.

Figure 2-9. Microsoft Access 2003 SQL view

Quiz 2

Project 2

Practice locating SQL view without looking at the instructions for doing so.