In the previous video, “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 syntax or rules of the SQL language.
In this video,
“What are the SQL Subquery and Exists Clause statement language elements”,
you will learn about some of the advanced data definition SQL statements
such as, "Subquery" and "Exists".
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.
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!
Ok, first let’s recap what we have learned in our SQL lessons.
Our sample database used in these lessons is named “realparsmodel”.
You can find the download links for our sample database and its diagram in the description.
“Realparsmodel” database has several tables.
Our sample RealPars database can be developed and viewed using an ERD diagram.
The ERD displays tables and relationships to each other,
for example, “CourseLines” and “Courses” tables are related,
using the “CourseLine” column name.
The ERD model may be explained in further detail in subsequent videos.
This model is another way to create and visualize your database.
This is our last SQL statement that we have written in part 5 of this video series,
using the “Having” clause.
The “HAVING” clause was used for displaying the “OrderNumber” column data
with two calculated columns:
“ItemsCount” and “Total”,
created for the quantity and total amount paid for each order,
only having “Total” greater than 1000
and the quantity greater than 600 from the “OrderDetails” table.
Now let’s learn about some additional common SQL clause manipulation statements.
A subquery is a query nested within another query
used in the “SELECT”, “INSERT”, “UPDATE” or “DELETE” statement.
Also, in MySQL, a subquery can be nested inside another subquery.
A subquery is named an inner query
while the query that contains the subquery is named an outer query.
A subquery can be used anywhere that expression is used
and must be closed in parentheses.
In this example, when the query is executed, the subquery runs first
and returns all office codes of the offices located in the USA.
Then, this result set is used as an input to the outer query,
selecting the “last name” and “first name” columns from the “employees” table.
Let’s try the statement by writing it in the “SQL Query Tab”.
I press the “Execute Query” button to run the “subquery where” statement.
The results will be displayed in the “Output Panel”.
Now let’s move on, the “EXISTS” operator
is a Boolean operator that returns either true or false.
The EXISTS operator is often used in a subquery
to test if data exists condition.
For example, in each row in the “Students” table,
the query checks the “StudentNumber” in the “Orders” table.
If “StudentNumber”, which appears in the “Students” table,
also exists in the “Orders” table, the subquery returns matching rows.
As a result, the EXISTS operator returns true.
Otherwise, the subquery returns no row and the EXISTS operator returns false.
Suppose you want to find the student who has placed at least one order,
you would use the EXISTS operator as follows:
By pressing the “Execute Query” button,
the result shows the records of the students who have placed at least one order.
To get the student who has not placed any sales orders,
you use the “NOT EXISTS” operator as the following statement.
We can test this statement in MySQL Workbench,
so, I press the “Execute Query” button to run the “where not exists” statement.
The result will be displayed in the Output Panel
with only a single student who has not placed an order.
Now we are ready to learn about additional database definition statements
such as joining tables together to list columns in a result from multiple tables.
This concludes the video,
“What are the SQL Subquery and Exists Clause statement language elements”.
The next SQL lessons soon to be available are:
What is the SQL Create Table Clause statement language element?
What are the SQL Cross Join, Inner Join and Union 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.
Also, be sure to download the RealPars app.
When you download the app,
you’ll be able to watch an entire free course on PLC Hardware.
Just like this video, the lessons are all high quality and also very easy to follow.
By downloading the RealPars app,
you have access to a wealth of practical knowledge
as an automation engineer right in your pocket
and you will also receive new fresh out of the oven videos each and every week.