Full Explanation & Illustration Of Sql Clauses & Keywords In Ms Access & Mysql

It is good you get used to the various SQL clauses and Keywords in Microsoft Access and MySQL Relational Database Systems as it will hasten your rate of understanding of SQL as a whole. Here, I will explain the six most commonly used SQL clauses and Keywords in MS Access and MySQL databases. I will also explain some SQL keywords in detail and illustrate how they are used in MS Access and MySQL databases.
Detailed explanation and illustration of SQL clauses

These SQL clauses include:

1. SELECT clause
2. FROM clause
3. WHERE clause
4. ORDER BY clause
5. GROUP BY clause
6. HAVING clause

This is part 2 of chapter 6 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain the various SQL clauses and keywords in Relational Database Management Systems (RDBMS) software like Microsoft Access 2016, 2013 and other lower versions.

In the previous part (Part 1), I explained How to build advanced queries and action queries in MS Access. Also see the full tutorial course content of this Microsoft Access Tutorial Series.

SELECT CLAUSE:

It consists of an operator followed by an identifier. A SELECT clause lists the fields that contain the data you wish to retrieve from a database. You can use square brackets ([]) to enclose the name of a field or identifiers in a SELECT clause. If the field name(s) does not contain spaces or special characters, the square brackets are optional. But if the field name(s) contains spaces or special characters, then you must enclose it in a pair of square brackets.

NOTES:

A name that contains spaces is easier to read and can save you time when designing forms and reports, but may make you type more when writing SQL statements. This fact should be considered when naming objects in a database.

If your SQL statement has two or more fields that have the same name but from different tables or data sources, then you must add the name of each field’s data source to the field name. The data source name is the identifier in the FROM clause of the SQL statement.

When you want to include all fields from a data source, you can either type them individually in the SELECT clause or use the asterisk wild character (*). For example, to include all the fields the customer table, you can type: customer.*.When you use the asterisk, Access determines when the query is run, which fields the data source contains and includes all those fields in the query. This helps to keep the query up to date if new fields are added later to the data source.

You can the asterisk with one or more data sources in SQL statement. If there are multiple data sources and you wish to include asterisk with only one of the data sources, then you must include the data source name together with the asterisk, so that MS Access can determine the right data source to apply the asterisk.

For example, suppose you want to select all the fields from customers table and then select only the order_dates field from the orders table, your SELECT clause should resemble this:
SELECT customers.*, oders.[order_dates];

WARNING:

Do not use the asterisk character when you intend adding new field which are not relevant to the query in the data source table, else you get undesired query results.

SELECT STATEMENT KEYWORDS

Some of the common SELECT statement keywords are:

1. THE “DISTINCT” KEYWORD:

If your data source field contains duplicate data and you want your query to return each of these duplicate data once, you add the DISTINCT keyword to your SELECT clause. For example, suppose you want your query your return distinct years from the order_year field of your order table, your SELECT clause should resemble this:
SELECT DISTINCT order.[order_date];

2. THE “AS”KEYWORD:

This is used for substituting names for field names or expression. You can change the label that is displayed for any field by using the AS keyword and a Field Alias.

A field alias is a name that you assign to a field in a query to make the result easier to read. For example, if you want your query to return data from the customer_phone field, which contains customer’s phone numbers, you can improve the readability of your query result by using a field alias in your SELECT statement as follows:
SELECT [customer_phone] AS [Customer Phone Number]

NOTE:

You must use a field alias when you use an expression in a SELECT clause.

USING A FUNCTION, AN EXPRESSION OR AN AGGREGATE FUNCTION AS AN IDENTIFIER IN A SELECT CLAUSE

Sometimes, you might want to retrieve only a part of a field’s data. For example, suppose that you want your query to return only the birth year of your customer’s from the birth_date field, then your SELECT clause might resemble this:
SELECT DatePart(“yyyy”, [birth_date]) AS [Birth Year]

This expression contains the DatePart function as an identifier and two arguments:
“yyyy” (a constant) and [Birth_date] (an identifier).

NOTES:

You can use any valid expression or function as an identifier in a SELECT clause provided it outputs a single value when given a single input value.

FROM CLAUSE:

It consists of an operator (FROM) followed by an identifier (field or expression). The FROM clause specifies the data source (table or query) that contain the data that the SELECT clause will use in a SELECT statement.

For example, suppose that you want to know the telephone number of a particular customer. Assuming tbl_customer is the name of the table stores this data, then your FROM clause would look like this:
FROM tbl_customer

NOTE:

You must use a pair of square brackets to enclose the table name if it contains spaces or special characters. I did not enclose the table name in the FROM clause above because it does not contain a special character (Note: underscore (_) is not a special character).

You can use a different name to refer to a data source in a SELECT statement by using a table alias in your FROM clause. This can be useful if the name of the data source is long or too difficult to type especially when there are multiple fields that have the same name from different tables.

For example, if two tables named tbl_customer and tbl_order respectively and both have a field named ID and you wish to select data from these fields, then your SELECT clause would look like this:
SELECT [tbl_customer].[ID], [tbl_order] FROM [tbl_customer] AS  [Customer], [tbl_order] as [Order];

One advantage of using a table alias is that when you use a table alias, you can refer to the data source in your SQL statement by using the alias or by sing the full name of the data source.

WHERE CLAUSE:

It consists of an operator (WHERE) followed by an expression. For example: WHERE last_name = ‘Bush’. Here, the expression is “last_name = ‘Bush’”.unlike the SELECT and FROM clauses, the WHERE clause is not a very necessary clause in a SELECT statement. You only use the WHERE clause to set your query criteria if you do not want your query to return all the data in the specified column.

A query criterion is similar to a formula or an expression. It is a string that may consist of field references, operators and constants. Criteria can look very different from each other depending on the data type of the field to which the criteria apply and your specific requirements.

Some criteria are simple and use only basic operators and constants. For example, > 20 OR < 30. Others are complex and use functions and special operators and also include cell references. For example:
DateDiff(“yyyy”, [birth_date], Date()) > 50.

NOTE:

If a field is used with an aggregate function, you can not specify criteria for that field in a WHERE clause. Instead, you use a HAVING clause to specify criteria for such fields.

WHERE CLAUSE SYNTAX

A WHERE clause has the following basic syntax Structure:
WHERE Field Operator Criterion

For example, if you want your query to return the data in a table where the data in the field named last_name is “Bush”, then your WHERE clause would resemble this:
WHERE [last_name] = ‘Bush’

NOTE:

The equivalence (=) operator is not the only operator used in a WHERE clause. You can use other comparison operators such as Greater than (>) or Less than (<) or the combination of any two operators such as Greater than or Equal to (>=), Less than or Equal to (<=) or Not Equal to (<>).

Sometimes you may want to combine data sources based on fields that have matching data, but different data types. For example, a field in one table may have a Number data type and you wish to compare that field to a field in another table that has a Text data type. Since you cannot create a Join between fields that have different data types, the solution is to use a WHERE clause that uses one field as a criterion for the other field as a criterion for the other field by using the LIKE keyword.

For example, suppose you want your query to return data from field_1 (a text field in table_1) and field_2 (a number field in table_2) only when the data in field _1 matches the data in field_2, then your WHERE clause would resemble this:
WHERE [field_1] LIKE [field_2].

ORDER BY CLAUSE:

This SQL clause sorts a query’s resulting records on a specified field or fields either in ascending or descending. ORDER BY clause is optional. It only becomes necessary if you want your returned data to be displayed in a sorted order.
The Syntax for the ORDER BY clause is:
ORDER BY field names(s)

The default sort order is the Ascending (A to Z, 9 to 0). If you want your data to be sorted in Descending order, then you have to add the DESC keyword to the end of each field you want to sort in descending order.

For example, suppose you want your query result to sorted first by the values by the values in the first_name in descending order and if there is no records with the same value for first_name, they should be sorted next by the value in the last_name field in ascending order,, then your ORDER BY clause should resemble this:
ORDER BY first name DESC, last_name

NOTES:

If you specify a field containing OLE object data in the ORDER BY clause, an error because MS Access database engine does not sort on fields of this type.

ORDER BY clause is usually the last item in SQL statement.

GROUP BY CLAUSE:

When you use aggregate functions such as SUM, COUNT, etc. in your SELECT statement, you must also use the GROUP BY clause. This clause combines records with identical values in the specified field list into a single record.

The GROUP BY clause lists all the fields in a query to which you do not want to apply an aggregate function. If you apply aggregate functions to all the fields in a query, then you do not have to use the GROUP BY clause.

The GROUP BY clause immediately follows the WHERE clause or the FROM clause if there is no WHERE clause. Where there is a WHERE clause, MS Access groups value after applying the WHERE condition to the result. The GROUP BY clause also lists the fields as they appear in the SELECT clause.

For example, if you selected two fields: first_name and Last_name, then applied an aggregate function to the first_name field but not to the last_name, then your GROUP BY clause would resemble this:
GROUP BY [last_name]

NOTES:

Summary values are omitted if there is no SQL aggregate function in the ELECT statement.
Null values in GROUP BY fields are not evaluated in any SQL aggregate function.
Use the WHERE clause to exclude rows you do not want grouped and use the HAVING clause to filter records after they have been grouped.

A field in the GROUP BY field list (fields used in the GROUP BY clause) can refer to any field in any table listed in the FROM clause even if the field is not included in the SELECT statement, provided the SELECT statement includes at least one SQL aggregate function. This fact becomes invalid if the field used in the GROUP BY clause contains memo or OLE object data. This is because MS ACCESS cannot group on Memo or OLE object fields.

All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.

HAVING CLAUSE:

If you want to use criteria to limit your results, but the field you want to apply the criteria to is used in an aggregate function, then you cannot use a WHERE clause. Instead, you use a HAVING clause. The HAVING clause works like a WHERE clause but is used for aggregate data.

For example, suppose you selected two filed, phone_number and surname respectively the tbl_customer table and you aggregate the phone_number field with a COUNT function and also you want your query to return rows if there are more than one phone number with the surname, then your SQL statement should resemble this:
SELECT COUNT([phone_number]), surname FROM [tbl_customer] HAVING COUNT([phone_number]) > 1;

NOTES:

A query can have a WHERE clause which contains criteria for fields that are not aggregated and also a HAVING clause which contains criteria for aggregated fields.

HAVING clause is similar to WHERE clause. The latter determines which records re selected and the former determines which records are displayed after the records are grouped with a GROUP BY clause. This means that the GROUP BY clause occurs after the WHERE clause and before the HAVING clause.

A HAVING clause can contain up to 40 expressions linked by logical operators such as AND and OR.

The SQL statement below illustrates the points stated in the last two notes above:
SELECT [sales_ID], Sum([qty_sold]) FROM [tbl_sales] WHERE [sales_ID] >= 50 GROUP BY [sales_ID] HAVING Sum(qty_sold) >= 80 And LIKE “DOZ*”;

Hope you can now interpret the above complex SQL statement?
Try it out.

This is the end of part 2 of chapter 6 of this FREE online MS Access tutorial training series. 

Recommended MS Access Textbook

Recommended Textbook

With HTML, CSS, PHP In Dreamweaver CS5 And CS6, MySQL And MS Access 2013 And 2016 By David Max
learn dynamic web development and DBMS ebook

Click To Buy This Book Now

Click Here to know more about the book.

In the next part (part 2), I will explain and illustrate the practical application of SQL in MS Access and MySQL databases.

Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.

No comments:

Post a Comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!