PLC Programming

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

Learn about some of the more advanced SQL statements.

Listen to this article

In this article, you will learn about some of the more advanced SQL statements and clause commands such as, SQL Order by Clause. You will also learn about SQL expression statement language elements.

If you would like to follow along with us and perform these lessons on your computer, please review the RealPars article, “How to install MySQL and import a sample database”, on how to download and setup MySQL database on your computer.

In the previous articles, we learned about how important, working as an automation professional, relational database management systems are to a world of data that is constantly changing and evolving every second.

We also understood that how these changes have in turn created new growth and challenges for companies around the world; and that manufacturing sites and large facilities generate a large amount of process data that is stored in databases, which we, as automation professionals are required to use the standard language for relational database management systems called SQL.

SQL allows us to create and/or retrieve records of data for data analysis, reports, graphs and archives as data sources for reports.

In the previous article, What are the SQL Where and Like statements basics, we learned how some of the basic SQL commands are used to communicate with a database.

You created SQL commands as queries to retrieve data from a database using the Select statement to retrieve records with certain columns and data using the Where and Like clauses.

‍

Now, In this article, you will learn about some of the more advanced SQL statements and clause commands such as Order by.

SQL Database and Diagram

We learned that some of the most common SQL databases in manufacturing are Microsoft SQL Server and MySQL.

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

Our sample database used in this series of articles was named realparsmodel.

Realparsmodel database has several tables.

These tables are outlined in the enhanced entity-relationship diagram.

This diagram shows the relationships between entities.

It is most commonly used to organize data within databases or information systems.

Please note the data contained within the sample database does not reflect actual RealPars student data and that the data has been created for educational purposes only.

The sample realparsmodel database representation consists of the following tables:

– Students: stores student’s data.

– Courses: stores a list of courses.

– Course lines: stores a list of course line categories.

– Orders: stores sales orders placed by customers.

– Order details: stores sales order line items for each sales order.

– Payments: stores payments made by students based on their – accounts.

– Employees: stores all employee information and organization structure.

– Offices: stores sales office data.

Remember, the SELECT statement is used to query the database and retrieve the selected data that match the criteria that you specify.

Here was the format of the SELECT FROM statement using the WHERE LIKE clause.

The results displayed only the last names containing the letter u.

SQL Statements

As we progress in learning SQL, we will learn to use statements for defining the database, statements that will manipulate and update data and statements that will grant permissions to users to access specific data.

‍

SQL Set of Rules, Syntax

SQL Statement Terminator, Semicolon

Every programming language, such as SQL, should follow a unique set of rules called Syntax. One of these rules is that all of the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, etc. and they end with a semicolon.

‍

SQL Case Sensitivity

The most important point to be noted here is that SQL keywords are NOT case sensitive. select (with small letters) is the same as SELECT (with capital letters) and they have the same meaning in SQL statements.

MySQL Table Names

It is worth mentioning that MySQL makes a difference in table names. So, if you are involving MySQL to do your project, then you need to give table names as they exist in the database.

SQL Fundamental Operations

The four fundamental operations that apply to any database are:

– SELECT: for reading the data

– INSERT: for inserting new data

– UPDATE: for updating existing data

– DELETE: for removing data

Look at this example. I’ll show you that the SQL language is subdivided into several language elements that make up the syntax for statements.

Clauses are basic components of statements and queries. Expressions can produce either variable values or tables consisting of columns and rows of data. Predicates specify conditions that can be evaluated to true, false or unknown or Boolean truth values. They are used to limit the effects of statements and queries.

‍

Queries retrieve the data based on specific criteria. And statements may have a determined effect on diagrams and data, or may control transactions, program flow, connections, sessions, or diagnostics. SQL statements also include the semicolon statement terminator.

SQL ORDER BY Clause

Now let’s learn about some of the common SQL data manipulation statement commands. We already learned the SELECT statement allows you to get the data from tables or views.

Remember, a table consists of rows and columns like a spreadsheet. The result of the SELECT statement is called a result set. the result set is a list of rows, each consisting of the same number of columns.

The ORDER BY clause allows you to sort a result set by a single column or multiple columns, and sort a result set by different columns in ascending or descending order.

Now using the select statement from Students table, and ordering the results by last names, and descending the query, would be written like this:

I press the Execute Query button to run the statement, and view the results in the Output Panel. The results display the last names in descending order.