6. MICROSOFT ACCESS QUERIES – ADVANCED
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:
-
-
- Select list of attributes or calculated results(1)
- From list of tables with/without join condition(2)
- Where criteria rows must meet beyond the join specifications(3)
- Group by list of attributes for creating groups(4)
- Order by list of attributes for ordering the results(5)
- Having criteria groups must meet(6)
-
Each clause of the SQL statement has its counterpart in the Design View used by Access:
-
- 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.
- Tables that appear in the From clause are shown in the Relationships Area.
- Specifications for the Where clause are found in the Criteria and Or rows.
- Specifications for the Group By clause are made in the Totals row.
- Specifications for sorting are made in the Sort row.
- 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.
-
-
- 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
Consider the following SQL statements for the Sales database 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.
1) SELECT Product.ProductID, Product.ProductName
- FROM Product;
2) SELECT Category.CategoryName, Product.ProductName
- FROM Category INNER JOIN Product
- ON Category.CategoryID = Product.CategoryID;
3) For the Sales database: List products in Condiments with a unit price over $4.
- SELECT Product.ProductID, Product.ProductName
- FROM Category INNER JOIN Product
- ON Category.CategoryID = Product.CategoryID
- WHERE (((Category.CategoryName)=”Condiments”) AND ((Product.UnitPrice)>4));
4) For the Sales database: List the number of products in each category.
- SELECT Category.CategoryID, Category.CategoryName, Count(Product.ProductID)
- FROM Category INNER JOIN Product
- ON Category.CategoryID = Product.CategoryID
- GROUP BY Category.CategoryID, Category.CategoryName;
5) For the Sales database: List products for which the total quantity sold is more than 10.
- SELECT Product.ProductID, Product.ProductName
- FROM Product INNER JOIN Sales
- ON Product.ProductID = Sales.productId
- GROUP BY Product.ProductID, Product.ProductName
- HAVING (((Sum(Sales.quantitySold))>10));
