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.
These SQL clauses include:
1. SELECT clause
2. FROM clause
3. WHERE clause
4. ORDER BY clause
5. GROUP BY clause
6. HAVING clause
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];
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.
In the next part (part 2), I will explain and illustrate the practical application of SQL in MS Access and MySQL databases.
Recommended MS Access Textbook
Get this book (Kindle format): Designing Professional Database Management Systems Using Microsoft Access 2013 and 2016 and MySQL.
Recommended Textbook
With HTML, CSS, PHP In Dreamweaver CS5 And CS6, MySQL And MS Access 2013 And 2016 By David Max
Click To Buy This Book Now
Click Here to know more about the book.
With HTML, CSS, PHP In Dreamweaver CS5 And CS6, MySQL And MS Access 2013 And 2016 By David Max
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.
Posts
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!