Inner Join, And, Or, Having, and Between SQL Clauses (part 5 of 8)Learn about some of the more advanced SQL statements.
In the previous article, What are the SQL Order By Clause and Expression statement language elements?, we learned about the structure of SQL defining the clauses, expressions defining the overall statement. Also, we used the Select statement to retrieve the last names in descending order, using the Order By clause. In this article, you will learn about some of the more advanced SQL statements and clause commands such as Inner Join, AND, OR, Having, and Between. We will also learn more about the SQL syntax or rules of the SQL language.
Remember, our sample database used in this series of articles was named realparsmodel.
Also, With the Entity Relationship Diagram, you can see the realparsmodel database has several tables that are related to one another.
Here is our last SQL statement that we have written in the SQL Query Tab, using the Order By clause.
In general, the Order By clause will be used along with the Select statement to provide a descending or ascending listing of the results provided by the query.
The AND operator is a logical operator that combines two or more Boolean expressions and returns true only if both expressions evaluate to true.
Now using the SELECT statement from the Students table where the results display only records having the country of USA AND the state of California AND having a Credit limit greater than $100,000, the query would be written like this:
Press the Execute Query button to run the statement. By doing this, it shows a student from California with a large credit as a result.
The OR operator is a logical operator that combines two or more Boolean expressions and returns true when either condition is true.
Now using the SELECT statement from the Students table, where the results display records having the country of USA OR the country of France, the query would be written like this:
Once again, Press the Execute Query button to run the statement. By doing this, it shows a list of the students from the USA or France as a result.
The BETWEEN operator is a logical operator that allows you to specify whether a value is in a range or not.
Now using the SELECT statement from the Courses table where the results display records having a price BETWEEN 90 and 100, the query would be written like this:
To run this SQL statement, Press the Execute Query button. By doing this, it shows the records of the price range between 90 and 100 as a result.
INNER JOIN Clause
The INNER JOIN clause matches rows in one table with rows in other tables and allows you to query rows that contain columns from both tables.
For example, you can join tables that have key relationships like the Courselines and Courses tables.
We want to get the Coursecode and Coursename from the Courses table and the TextDescription of course lines from the Courselines table.
To do this, write the SQL statement to select data from both tables by matching rows based on the Course line columns, using the INNER JOIN clause as follows:
By pressing the Execute Query button, it shows the records of Course code, Course name and text description columns.
The HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.
Now the department asks us to write the SQL statement, using the HAVING clause from the Order details table, with results displaying the Order number column data with two calculated columns for the quantity and total amount paid for each order only having totals greater than 1000 and quantity greater than 600.
This concludes the article, What are the SQL Inner Join, And, Or, Having and Between Clause 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:
– Subquery and Exists Clause statement language elements
– Create Table Clause statement language element
– Cross Join, Inner Join, and Union Clause statement language elements
Want to Learn More?
By downloading the RealPars app, you can 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.
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.
In this blog post, you’ll learn about the mindset that helped me getting a PLC programming job with NO experience. This is my personal experience as someone who searched for a job in this field and as an employer who reviews resumes and interviews candidates for a variety of projects. So let’s get started!
Siemens has made their TIA-Portal version 16 available at a reduced price for a period of time. In this article, we will show you how to: - Purchase the TIA Portal version 16 software, - Download and install the software, and - Install the software license so that it...
A control valve is a power-operated device used to regulate or manipulate the flow of fluids, such as gas, oil, water, and steam. It is a critical part of a control loop and is an example of a final control element. The Control Valve is by far the most common final...
Learn how to program PLCs, install and wire industrial devices, and at the same time purchase them online.
+31 10 316 6400
Mon - Fri 8:30 am to 5:30 pm (CET)
Rotterdam Science Tower, Marconistraat 16,
3029AK Rotterdam, Netherlands
© 2020 RealPars B.V. All rights reserved.
Created with coffee and tea in Rotterdam.