6.9: SQL Select Statement

SQL is the standard language for relational database systems. There are variations of SQL that appear in Object-oriented database systems, and elsewhere. The study of SQL is very important and the knowledge gained here is useful in other database environments.

We will examine one SQL statement, the Select statement, used to retrieve data from a relational database. Other common data manipulation statements are the Insert, Update, and Delete used to modify or add data. Select, Insert, Update, and Delete all belong to the Data Manipulation Language (DML) subset of SQL. Another group of statements belong to the Data Definition Language (DDL) subset of SQL – these statements are used to create tables, indexes, and other structures and are discussed in a later section.

The general SQL Select statement syntax:

1. Select list of attributes or calculated results
2. From list of tables with/without join condition
3. Where criteria rows must meet beyond the join specifications
4. Group by list of attributes for creating groups
5. Order by list of attributes for ordering the results
6. Having criteria groups must meet

Each clause of the SQL statement has its counterpart in the Design View used by Access:

1. Attribute/calculated values are those for which Show is specified. If grouping is used, these must evaluate to a single value (group functions; grouping attribute) per group.
2. Tables that appear in the From clause are shown in the Relationships Area.
3. Specifications for the Where clause are found in the Criteria and Or rows.
4. Specifications for the Group By clause are made in the Totals row.
5. Specifications for sorting are made in the Sort row.
6. A Having clause specifies criteria that a group must meet to be included in the result. This clause is generated when you use an aggregate function with a criteria.

When you design a query you can switch between various views including SQL view. You can easily confirm through examples how the SQL statement is generated from Design View. For example, consider the following query and its SQL expression below. Note how MS Access has used names with dot-notation to fully specify fields and how MS Access has placed one criteria rows must meet in a Having clause.

Figure \(\PageIndex{1}\): QBE and SQL Select (below).

SQL Select Statement

SELECT Department.deptName, Course.title, Count(Course.creditHours) AS CountOfcreditHours

FROM Department INNER JOIN Course ON Department.deptCode = Course.deptCode WHERE (((Course.creditHours)=3))

GROUP BY Department.deptName, Course.title

HAVING (((Department.deptName)="ACS")) ;

Exercises

Exercise \(\PageIndex{1}\)

Consider any of the queries from a previous section. Translate the query into SQL manually and then compare your result to what you see when you view the query in SQL View.

Exercise \(\PageIndex{2}\)

Consider the following SQL statements and show how each statement would appear in Design View. You can confirm your result if you create a query, switch to SQL View, type the query statement and then switch to Design View. Unfortunately, if you make any syntax errors, Access will be unable to switch to Design View. Your database must contain the tables in the From clause.

1. SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
   FROM Categories INNER JOIN Products
   ON Categories.CategoryID = Products.CategoryID
   WHERE Categories.CategoryName = "Beverages"
   AND
   Products.Discontinued = Yes;
2. SELECT Auto.Year, Dealer.name
   FROM Auto INNER JOIN Dealer
   ON Auto.Did = Dealer.Did
   WHERE Auto.Price > 10000
   OR
   Auto.Colour = “blue” ;
3. SELECT Auto.Year, Dealer.name
   FROM Auto RIGHT OUTER JOIN Dealer
   ON Auto.Did = Dealer.Did
   WHERE Auto.Price > 10000
   OR
   Auto.Colour = “blue” ;