fbpx

Inner Join, And, Or, Having, and Between SQL Clauses (part 5)

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.

AND Operator

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.

OR Operator

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.

BETWEEN Operator

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 codeCourse name and text description columns.

HAVING Clause

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?

Also be sure to download the RealPars app (iOS, Apple TV, Android, Android TV, firetv, Roku). 

When you download the app, you’ll be able to watch an entire free course on PLC Hardware. Just like this article, the lessons are all high-quality and also very easy-to-follow.  

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.

The RealPars Team
By Wally Gastreich

By Wally Gastreich

Automation Engineer

Posted on Feb 3, 2020

By Wally Gastreich

Automation Engineer

Posted on Feb 3, 2020

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

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

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...

What is an Actuator?

What is an Actuator?

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.

Have questions? 

hello@realpars.com

+31 10 316 6400

Mon - Fri  8:30 am to 5:30 pm (CET)

Rotterdam Science Tower, Marconistraat 16,
3029AK Rotterdam, The Netherlands

COMPANY

About

Privacy

Cookies

LEARN

Courses

PLC Certificate

Blog

Search

Sign in

HELP

FAQ

Contact Us

© 2020 RealPras B.V. All rights reserved.

Created with coffee and tea in Rotterdam.