What are SQL Programming Basics? (PART 2 of 8)

If you would like to follow along with us, please review the RealPars SQL lesson, How to install MySQL and import a sample database, on how to download and setup MySQL database on your computer.

The world of data is constantly changing and evolving every second. This has, in turn, created new growth and challenges for companies around the world.

Manufacturing sites and large facilities can generate a large amount of process data that is stored in databases, which are used as data sources for reports.

At some point in time in your Automation career, sooner than later, you will be required to develop, manage and/or interface to a database of some type.

So, in a multi-lesson series, you will begin your journey in learning about relational database systems and how to manipulate and query its data using SQL.

In this article, you will learn about relational databases and about the language of SQL, and then in subsequent articles we will apply what we have learned in this training article, to view a sample database and program a few SQL queries to read and write to the database that will provide specific organized data.

Relational Database Management System or RDBMS

There are many types of database structures. One of the most popular database management systems is called Relational Database Management System or RDBMS, because of the simplicity of operation and ease of use.

Data is generally stored in tables and they are usually manipulated through the use of Structured Query Language or SQL.

Some systems that use relational database management includes

– Oracle

– IBM

– Microsoft SQL Server

– MySQL

among many others.

Why Companies Use Relational Databases?

Many companies use relational databases today to allow the integration of multiple tables of data. This allows database administrators to create smaller specific dataset rather than a single large flat database.

These tables contain unique information having a common column of information such as student number that allows a table to be joined and relate to one another.

This is one of the important features of the relational database, in that it allows queries or statement questions, to find the relationship between these tables and then provide the specific results for web page searches, automation historical trending, and financial reports just to mention a few.

The information from the queries will help you as an Automation engineer analyze, troubleshoot and quickly resolve issues occurring within your process control system or plant.

Entity Relationship Diagram or ERD

With this Entity Relationship Diagram or ERD, we can see how the tables are related to one another in the database.

Real-Time Data Collection

Real-time factory-floor data collection captures a goldmine of information that can improve operational performance.

Real-time machine tool data collection isn’t just about helping manufacturers improve productivity and profitability, the collection is also an important step towards competing in a data-driven, high-tech manufacturing world that is globally competitive.

Data Collection Sample Methods

As we have mentioned, an important tool for automation is to historically log data to a historian database, often a SQL database.

The process or manufacturing historian software will collect data using several sample methods that are based on

– Time-interval

– Event-based

– On-scan or based on an expression algorithm.

Typically in a historian, tag names identify the source of numerical or string type data derived from process automation controllers.

Whether you choose to analyze a tank level, tank temperature, tank pressure or even a control valve, the user can evaluate its operation, efficiency, profitability, and setbacks of production.

Some examples of what might be recorded in a data historian include:

Analog values such as

– Temperature

– Pressure

– Flow rates

– Levels

– Weights

Digital states such as

– Valves

– Limit switches

– Motors on and off

– Discrete level sensors

– Alarms states for out of limits signals and return to normal signals

Data retrieval for Production data, such as

– Production runs

– Trends

– Recipes

– Shifts

– Products

– Schedules

are typically stored in a time-series historian using a relational SQL database.

Relational Database Tables

Within a relational database, there are tables.

Each table is made up of records or horizontal rows also known as tuples and fields or vertical columns also known as attributes, similar to a spreadsheet.

Each table will be identified by a unique name and the name will be used by the database to find the table behind the scenes. So, all you need to know as a user is the table name in order to use it.

This ease of interaction is what placed the relational database model to gain wide acceptance.

SQL Databases

Structured Query language or SQL is the language of choice for most modern multi-user relational databases.

This is due to the fact that SQL provides the syntax and language you need to talk to, or query, a relational database.

The most common SQL databases in manufacturing are Microsoft SQL Server and MySQL.

And, in our example we will be using MySQL, it’s an open and free database that many OEMs are using.

We use SQL to communicate with a database. It is the standard language for relational database management systems.

SQL Statements

We create SQL statements to perform tasks such as update data on a database or retrieve data from a database.

The standard SQL statements such as

– Select

– Insert

– Update

– Delete

– Create

– Drop

can be used to accomplish just about everything that you’ll need to do with a database.

As we mentioned, rows will contain the records or data for the columns.

Here is a sample table called Students from the MySQL RealPars sample database, named realparsmodel; the Student Number, Last Name, First Name, Phone, City, State, Postal code and Country are some columns of Students table.

In continuing, we’re going to List specific data from this table.

Select Statements

The select statement is used to query the database and retrieve selected data that match the criteria that you specify.

Here is the format of a simple SELECT statement that we want to retrieve the last name, first name and phone number of the students:

‍

The column names that follow the SELECT keyword, determine which columns will be returned in the results.

The table name, realparsmodel.students, that follows the keyword FROM, specifies the table that will be queried to retrieve the desired results.

It is worth mentioning that we can select as many column names that we’d like, or we can use a *, to select all columns.

‍

This concludes the article, What are SQL programming basics.

By now you should be getting accustomed to simple SQL statements and ready for the next lesson in SQL.

Next SQL Lessons

Our series of subsequent articles to follow will consist of the following lessons; please be sure to read for these articles offering prerequisite learning for the beginners and then unto the more advanced statements of SQL learning.

The next SQL lessons soon to be available are:

– Where and Like statements basics

– Order By Clause and Expression statement language elements

– Inner Join, And/or, Having and Between Clause statement language elements

– Sub Query, Exists and Create Table Clause statement language elements

– Join and Union Clause statement language elements

– Cross Join and Inner Join Clause statement language elements

If you would like to get additional training on a similar subject please let us know in the comment section.

Check back with us soon for more automation control topics.

Got a friend, client, or colleague who could use some of this information? Want to know what the basics of SQL programming are? Please share this article.

The RealPars Team

How to Install MySQL and Import a Sample Database (Part 1 of 8)

▶ What are SQL Programming Basics? (Part 2 of 8)

What are SQL Where and Like Statements Basics? (Part 3 of 8)

What are the SQL Order by Clause and Expression Statement Language Elements? (Part 4 of 8)

What are the SQL Inner Join, And/or, Having and Between Clause Statement Language Elements? (Part 5 of 8)

What are the SQL Subquery and Exists Clause statement language elements? (Part 6 of 8)

What is the SQL Create Table Clause Statement Language Element? (Part 7 of 8)

What are the SQL Cross Join, Inner Join, and Union Clause statement language elements? (Part 8 of 8)

‍