What are the SQL Subquery and Exists Clause Statement Language Elements? (Part 6)


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.

3 Comments

  1. This topic is absolutely beneficial but guys saparate the videos to levels I mean for instance biggeners and another levels it is be more detailed.. Thank realpars

Leave a Reply

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