Learn Sql In Ms Access & Mysql Databases – Step By Step Guide

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.

In the previous chapter, 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.

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.

NOTE:

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.

6. After typing your SQL statements, to run the query, click the Run command located near the View menu in the Results group as shown above.

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 feature.

To 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:

SELECT [first_name] 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 rest,

“customer” is the table or query name,

“Bush” is one of the field data found in the “last_name” field.

I will elaborate more on each of the SQL statement clauses later.

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.

SELECT STATEMENT:

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 descriptions include:

• 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. 

SQL CLAUSES:

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.

SQL TERMS

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: customer.[phone_number].

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;

NOTES:

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:

SELECT [first_name], [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 is Bush,”

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

Recommended Textbook

Learn Dynamic Web Development And DBMS:

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.

In the next part (part 2), I will explain all SQL clauses in MS Access and My SQL Databases in detail.

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.