SQL is used to retrieve data from a database. SQL is simply
a computer language (closely resembles English language) that database programs
(DBMS) or applications like Microsoft Access understand and MySQL. The knowledge
of SQL is important because every query in MS Access is first translated to its
equivalent SQL before it is being process by the database engine. Understanding
how SQL works can help you to create better queries and makes it easier for you
to fix, debug or manipulate a query in order to obtain your desired output.
WHAT IS SQL?
SQL as an acronym means “Structured Query Language”. It is a
computer language for working with sets of facts and the relationship between
them. Relational Database Management Systems (RDBMS) such as MS Access, MySQL,
etc. use SQL to work with data.
Like many other computer languages, SQL is an international
standard language recognized by standard bodies such as ISO and ANSI.
This is part 1 of chapter 6 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain how to learn SQL in Relational Database Management Systems (RDBMS) software like Microsoft Access 2016, 2013 and other lower versions.
When you use SQL, you must use the correct syntax or
statement. A syntax or statement is a set of rules by which the elements of a
language are correctly combined to achieve a particular result. SQL syntaxes
are based in English syntax and uses many of the same elements as Visual Basic
for Application (VBA) syntaxes.
You really need to be grounded in MS Access Advanced Queries
before you can flow in SQL because the latter is more complex and needs a
deeper understanding of its pre requisite which is Advanced Queries. I advise
to revise the previous chapter (chapter
5 – Advance Queries) before you continue.
WHERE TO TYPE SQL
STATEMENTS IN MS ACCESS
Microsoft Access has SQL environment (view) or interface
where you can type SQL statements directly without having to first write in
design query view.
To Access the SQL
view in MS Access:
1. Launch MS Access and open previously created database or create a new one if you wish.
2. Click Query Design located
in the Queries group under the Create tab.
3. This prompts the Show
Table dialogue box. Close the dialogue box without selecting any table by
clicking the Close button.
4. Click the View
drop down arrow located in the Results group
under the Design tab. Then select SQL view as shown below.
5. MS Access now displays the SQL view where you can now
directly type you SQL statements.
typing your SQL statements, to run the query, click the Run command located near the View
menu in the Results group as shown
WHERE TO TYPE SQL
STATEMENTS IN MYSQL DATABASE
There are many ways and places you can type your SQL
statements in MySQL database depending on your intention. MySQL database is
mainly used to store dynamic data that are displayed on websites that are coded
with server side languages like PHP.
You can type your SQL statements directly in SQL query environment.
You can also type your SQL statements in your code editor like Dreamweaver,
code++, etc. when you must have connected it to the MySQL database. In
Dreamweaver, you normally build your SQL SELECT statements using the record set
know more about PHP code editor and how to set up a MySQL database connection,
see the part two of David Powers’ tutorial at adobe website
A typical example of a simple SQL SELECT statement is:
FROM customers WHERE last_name=’Bush’;
The words in block are called SQL Clauses,
While “first_name” and “last_name” are table or query fields
“customer” is the table or query name,
“Bush” is one of the field data found in the “last_name”
I will elaborate more on each of the SQL statement clauses
SQL is not only used for manipulating data, but also used
for creating and altering the design of database objects such as table, query,
form, report, etc. The part of SQL that is used to create or alter database
objects is called Data Definition
Language (DDL). It is not covered in this chapter.
It is used to describe a set of data. It contains a complete
description of a set of data that you wish to retrieve from a database. The
• Which table contains the data you wish to retrieve?
• How data from different sources are related.
• Which fields or calculations will produce the required data?
• Criteria that data must match to be included in the in the result list.
• Whether and how to sort the results.
Like a sentence, SQL statements have clauses. Each performs
a function for the SQL statement. Some of the common SQL clauses are:
1. SELECT: Lists the
fields that contain the desired data.
2. FROM: Lists the
tables that contain the fields listed in the SELECT clause.
3. WHERE: Specifies
the criteria that must be met by each record to be retrieved.
4. ORDER BY:
Specifies how to sort the results.
5. GROUP BY: In SQL
statements that contain Aggregate functions, it lists fields that are not
summarized in the SELECT clause.
6. HAVING: In SQL
statements that contain Aggregate function, it specifies conditions that are
summarized in the SELECT statement.
Each SQL clause is composed of terms. The various SQL terms
are listed below.
1. IDENTIFIER: A
name that you use to identify a database object such as the field name. For
example, to refer to a phone_number field
in a table named customer, you type:
2. OPERATOR: A
keyword that represents or modifies an action. For example: As.
3. CONSTANT: A value
that does not change such as a number like 12 or NULL.
4. EXPRESSION: A
combination of identifiers, operators, constants and functions that evaluates
to a single value. For example: <=sales.[sales_price].
In general, SQL statements take the form:
SELECT [field_1] FROM
table_1 WHERE criterion_1;
Every SELECT statement ends with a semi-colon sign (;). The
semi-colon can appear at the last clause or on a line by itself at the end of
the SQL statement.
Access ignores line breaks in SQL statements. However,
consider using a line for each clause to help improve the readability of your
SQL statements for yourself and other and for easy debugging of errors.
Another example of a simple SQL statement for a simple
SELECT query in MS Access looks like this:
[Age] FROM customers WHERE last_name = ‘Bush’;
The above simple SQL statement has three clauses, namely:
SELECT, FROM and WHERE clauses.
The SQL statement reads, “Select data stored in the fields
named first_name and Age from the table named customers, specifically those records
in which the value of the last_name field
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.
Labels: Microsoft Access