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


If you would like to follow along with us
and perform these lessons on your computer, please review the RealPars video, “How to install MySQL and
import a sample database”, on how to download and setup
MySQL database on your computer. In the previous videos, 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 video, “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. In this video, you will learn about
some of the more advanced SQL statements and clause commands such as, “Order by”. At RealPars, we love helping you learn so if you enjoy this video as
much as we enjoyed making it, Click the like button. subscribe and click the bell and you’ll receive notifications
of new RealPars videos. so you’ll never miss another one! 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 videos was named “realparsmodel”. You can find the download links for our sample
database and its diagram in the description. “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. 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. 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. 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. 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” and are used to limit the effects
of statements and queries. Queries retrieve the data based on
specific criteria, and statements, which 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. 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 that 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 video, “What are the SQL Order by Clause and
Expression statement language elements”. Our series of subsequent videos to follow will consist of the following lessons, be sure to watch for these videos 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: What are the SQL Inner Join, And/or, Having and
Between Clause statement language elements? What are the SQL Subquery, Exists and Create
Table Clause statement language elements? What are the SQL Join and Union
Clause statement language elements? What are the SQL 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. If you enjoyed this video,
please press the like button. Please check back with us soon for
more automation control topics. Want to learn PLC programming
in an easy to understand format and take your career to the next level? Head on over to realpars.com

Leave a Reply

Your email address will not be published. Required fields are marked *