What are SQL Order by and Statement Elements? (part 4)Learn about some of the more advanced SQL statements.
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.
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.
This concludes the article, What are the SQL Order by Clause and Expression Statement Language Elements?.
Next SQL Lessons
Our series of subsequent articles to follow will consist of the following lessons.
Be sure to read these articles offering prerequisite learning for the beginner and then unto the more advanced statements of SQL learning.
The next SQL lessons soon to be available are:
– Inner Join, And/or, Having and Between Clause statement language elements
– Subquery, 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? Please share this article.
5 ACTIONABLE TIPS FOR GETTING A PLC PROGRAMMING JOB WITH NO EXPERIENCE The mindset that helped me find my ideal job as a PLC programmer with NO experience. Working as a PLC Programmer is one of the most attractive and highest paying jobs in the engineering industry....
In the previous article, What are the SQL INNER Join, And, OR, Having and Between Clause statement language elements, we learned about some of the more advanced SQL statements and clauses such as, AND, OR, BETWEEN, INNER JOIN and HAVING. We also learned about the SQL...
Today we’re going to talk about actuators. There’s probably not a day that goes by where you don’t use or encounter an actuator at home, or at work. In this article, we’re going to: - Explain the purpose of an actuator - Discuss the 2 types of actuators - Look at the...
RealPars is the world's largest online learning platform for automation engineers.
+31 10 316 6400
Mon - Fri 8:30 am to 5:30 pm (CET)
Rotterdam Science Tower, Marconistraat 16,
3029AK Rotterdam, The Netherlands
© 2020 RealPras B.V. All rights reserved.
Created with coffee and tea in Rotterdam.