The Structured Query Language

Introduction to SQL

Overview

So far, to create a query we have learned to use either the Query Wizard or the Design View of a query. In both cases, we were selecting fields and adding them to the query. This concept of building a query is based on the Structured Query Language abbreviated SQL. In fact, all queries are based on SQL and this language is used by most database development environments, including Microsoft Access.

The most fundamental keyword used by SQL is SELECT. In order to process your request, you must specify what to select. This is done using the FROM keyword. Therefore, the SELECT keyword uses the following syntax:

SELECT What FROM WhatObject;

Over all, the SQL is not case-sensitive. This means that SELECT, Select, and select represent the same word. To differentiate SQL keywords from "normal" language or from the database object, it is a good idea to write SQL keywords in uppercase. A SQL statement must end with a semi-colon.

In the SQL, the object is specified after the FROM keyword and by the WhatObject parameter of our syntax. For example, if you want to create a query based on a table named Persons, you would write the statement as:

SELECT What FROM Persons;

We also saw that, after specifying the object that holds the fields, you can then select each desired field and add it to the query. If you are using the Simple Query Wizard, you can double-click the field in the Available Fields list:

If you are using the Select Query window, you can drag the field from the list and drop it in the lower section of the view:

In the SQL, to select a field, enter its name after the SELECT keyword. For example, to select the LastName field of the Persons table, you would type

SELECT LastName FROM Persons;

If you want to include more than one field from the same table, separate them with a comma. For example, to select the first and last names of the Persons table and include them in your query, you can use the following statement:

SELECT FirstName, LastName FROM Persons;

We also saw that, to select all fields from an object and include them in your query, you could drag the asterisk field and drop it in the lower section of the query in Design View. In the same way, to include everything from the originating table or query, use the asterisk * as the field. Here is a statement that results in including all fields from the Persons table:

SELECT * FROM Persons;

1. Start Microsoft Access and open the Bethesda Car Rental1 database
2. Open the Cars table and switch it to Design View to review its list of fields
3. Close the Cars table
4. On the Database Window, click the Forms button
5. On the main menu, click Insert -> Query
6. On the New Query dialog box, click Design View and click OK
7. Click the Close button on the Show Table dialog box
8. On the main menu, click View -> SQL View. Notice that a Notepad-like window appears. 
9. Change the content of the window to SELECT Make FROM Cars;
    
10. To run the query, on the Query Design toolbar, click the Run button 
11. After viewing the query, to get back to the SQL View window, on the main menu, click View -> SQL View. 
12. To list the makes, models, and years of the cars, change the statement as follows:
     

    SELECT Make, Model, Year FROM Cars;

13. Run the query to see the result:
     
14. After viewing the query, right-click its title bar and click SQL View
15. To create a query that includes all fields from the table, change the statement as follows:
     

    SELECT * FROM Cars;

16. Run the query
17. After viewing the query, right-click its title bar and click SQL View

Record Sorting on Queries

All of the techniques used to sort records on a table can also be applied to a query that displays in Datasheet View. To create more advanced queries, the SQL allows you to sort a field on a query and use this arrangement as part of the query.

To create a query that has a field sorted as part of its statement, open the query in Design View. Once in Design View, click the Ascending field of the desired column and select Ascending from its combo box. In the same way, you can sort the field in reverse order by selecting the Descending option.

In SQL, to sort a field in ascending order, you can include the ORDER BY clause in your statement. The syntax used would be:

SELECT What FROM WhatObject ORDER BY WhatField;

The field used as the basis must be recognized as part of the selected columns. Imagine you have created a list of staff members made of their first and last names in a table named StaffMembers. If you want to order the list in alphabetical order based on the LastName column, you would use a statement such as:

SELECT FirstName, LastName FROM StaffMembers ORDER BY LastName;

If you use the * operator to include all fields, you can order the list based on any of the table's fields, as we learned during data analysis. Imagine that you have created a query that includes all fields. The following statement would list the records of the StaffMembers table based on the alphabetical order of the LastName column:

SELECT * FROM StaffMembers ORDER BY LastName;

By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in ascending order of a query that includes the first and last names, the above statement can also be written as follows:

SELECT FirstName, LastName FROM StaffMembers ORDER BY LastName ASC;

The second statement can be written as:

SELECT * FROM StaffMembers ORDER BY LastName ASC;

If you want to sort records in descending order, use the DESC keyword instead. It produces the opposite result to the ASC effect. To sort records in reverse alphabetical order, the above two statements can be written as:

SELECT FirstName, LastName FROM StaffMembers ORDER BY LastName DESC;

The second statement can be written as:

SELECT * FROM StaffMembers ORDER BY LastName DESC;

1. Open the ROSH database
2. In the Tables section of the Database window, click Students1 to select it
3. To create a new query, on the main menu, click Insert -> Query and, in the New Query dialog box, double-click Design View
4. In the Students list, double-click FirstName, LastName, Gender, DOB
5. Click the box that is at the intersection of LastName and Sort to reveal its combo box
6. Click its arrow and select Ascending
    
   
7. To view the result, on the Query Design toolbar, click the Run button 
8. Close the query. When asked to save the query, click Yes
9. Type List of Students as the name of the query and press Enter
10. In the Database window, click the Queries button. To create a new query, on the Database window, click New and double-click Design View
11. In the Show Table dialog box, click Close. Right-click an empty area of the Select Query window and click SQL View
12. Change the statement as follows:
     

    SELECT LastName, FirstName, DateHired, Salary, MaritalStatus FROM Staff ORDER BY DateHired;

13. Run the query
     
14. Close the query. When asked whether you want to save the query, click Yes
15. Type Staff Members By Seniority as the name of the query and press Enter

SQL and Filter By Selection

Filter By Selection in SQL is performed using the WHERE clause in a SQL statement. Its basic syntax is:

SELECT What FROM WhatObject WHERE Expression;

We learned that, to perform data analysis, a criterion is specified using an expression. The expression could be that records should only be about students who live in a single parent home. The same types of expressions are used in SQL, following some rules. We saw that, to get a list of employees who live in Maryland, in Filter By Form, we could type an expression such as =MD or ="MD" (remember that if you type =MD and press Enter, Microsoft Access would change it to ="MD"). In SQL, to create a list of staff members who live in Maryland, we can write the following statement:

SELECT FirstName, LastName, State FROM Staff WHERE State="MD";

To build your expressions, you can use any of the Boolean, logical, and comparison operators we have studied.

1. Open the Bethesda Car Rental1 database
2. To create a new query, on the main menu, click Insert -> Query and double-click Design View
3. On the Show Table dialog box, click Close
4. To write a SQL statement, right-click an empty area in the window and click SQL View
5. To list only Ford cars, change the statement as follows:
    

   SELECT Make, Model, Year FROM Cars WHERE Make="Ford";

6. Run the query
7. After viewing the result, right-click its title bar and click SQL View
8. To query the list of available cars, change the statement to the following:
    

   SELECT Make, Model, Year, Available FROM Cars WHERE Available=True;

9. Run the query
10. Close the query. When asked whether to save, click No
11. Open the ROSH database
12. To create a new query, on the main menu, click Insert -> Query and double-click Design View
13. In the Students1 list, double-click LastName, FirstName, Gender, SingleParentHome, EmergencyName, EmergencyPhone
14. To create a list of only students who live in a single parent home, click the box that is at the intersection of SingleParentHome and Criteria. Type =True
     
15. To view the SQL statement, right-click the title bar of the window and click SQL View
     
16. Run the query
17. Switch the query back to Design View
18. Since we already know what the list includes, we do not need to show the SingleParentHome column.
    Under SingleParentHome, remove the check box of Show
19. To view the SQL statement, right-click an empty area of the window and click SQL View
20. Run the query
21. Close the query. When asked whether you want to save the query, click Yes
22. Type Students Who Live In A Single Parent Home as the name of the query and press Enter

Microsoft Access and SQL Statements

Qualifying Fields in Statements

While Microsoft Access is faithful to the SQL, it adds its own little details when creating or interpreting the statements it is confronted with. Instead of using field names as we have done so far, Microsoft Access likes recognizing the object that holds a field just by looking at the referred field.

When studying operators, we saw that the name of a field should be delimited by square brackets to reduce confusion in case the name is made of more than one word. We also mentioned that the square brackets provide a safeguard even if the name is in one word. Based on this, to create a statement for a query that includes the first and last names of a Persons table, you can write it as follows:

SELECT [FirstName], [LastName] FROM [Persons];

To identify a field as belonging to a specific table or query, Microsoft Access usually associates its name to the parent object. This association is referred to as qualification. To qualify a field, you consider the object that is holding the field, then add a period followed by the name of the field. The basic syntax of a SELECT statement would be:

SELECT WhatObject.WhatField FROM WhatObject;

Imagine you want to get a list of Persons by their last names from data stored in the Persons table. Using this syntax, you can use a statement as follows:

SELECT Persons.LastName FROM Persons;

Or

SELECT [Persons].[LastName] FROM [Persons];

In the same way, if you want to include many fields from the same table, qualify each and separate them with a comma. To list the first and last names of the records from the Persons table, you can use the following statement:

SELECT Persons.FirstName, Persons.LastName FROM Persons;

Or

SELECT [Persons].[FirstName], [Persons].[LastName] FROM [Persons];

If you want to include everything from a table or another query, you can qualify the * field and qualify it as you would any other field. Here is an example:

SELECT Persons.* FROM Persons;

Or

SELECT [Persons].* FROM [Persons];

Statement Lining

Some of the SQL keywords are used to control blocks of sub-statements. Such words as we have seen so far are SELECT and FROM. A good technique of making a SQL statement easy to read consists of isolating each major keyword and its sub-statement on its own line. Using this approach, a basic SELECT statement would follow this syntax:

SELECT WhatObject.WhatField
FROM WhatObject;

Here is an example:

SELECT Persons.FirstName, Persons.LastName
FROM Persons;