Many people, when encountering a language like SQL, run into issues trying to work out what it actually does. I am going to focus on the SELECT statement over the next few posts. A SQL statement usually has many clauses.
For this post, I am going to do a quick examination of the WHERE clause. This post assumes you have some understanding of the FROM clause (which can be found here) and the SELECT clause (which can be found here)
Here is an Table Structure Diagram showing all four tables and how they are related.
We are going to focus on EXAMPLE_COURSES and EXAMPLE_SECTIONS
Here is our starting SQL with its result set.
SELECT EC.FACULTY,
EC.COURSE,
EC.TITLE,
ES.CAMPUS,
ES.ROOM,
ES.START_DATE,
ES.END_DATE
FROM EXAMPLE_COURSES EC
JOIN EXAMPLE_SECTIONS ES
ON (EC.COURSE_ID = ES.COURSE_ID)
A quick warmup - If want to see only the row where the TITLE is "Database Design & SQL", we would need to add another clause; we would add the WHERE clause.
SELECT EC.FACULTY,
EC.COURSE,
EC.TITLE,
ES.CAMPUS,
ES.ROOM,
ES.START_DATE,
ES.END_DATE
FROM EXAMPLE_COURSES EC
JOIN EXAMPLE_SECTIONS ES
ON (EC.COURSE_ID = ES.COURSE_ID)
WHERE EC.TITLE = 'Database Design & SQL'
Let's look more closely at the WHERE clause.
WHERE EC.TITLE = 'Database Design & SQL'
So, this clause added the idea that we only wanted titles that were 'Database Design & SQL' in our result set. If we wanted to see only courses that were offered in the campus of Eden Prairie, we would change the query's WHERE clause to this ...
SELECT EC.FACULTY,
EC.COURSE,
EC.TITLE,
ES.CAMPUS,
ES.ROOM,
ES.START_DATE,
ES.END_DATE
FROM EXAMPLE_COURSES EC
JOIN EXAMPLE_SECTIONS ES
ON (EC.COURSE_ID = ES.COURSE_ID)
WHERE ES.CAMPUS = 'Eden Prairie'
... and if we wanted only courses that were offered in the campus of Eden Prairie AND had the title of "Database Design & SQL", we would alter the SQL to be this...
SELECT EC.FACULTY,
EC.COURSE,
EC.TITLE,
ES.CAMPUS,
ES.ROOM,
ES.START_DATE,
ES.END_DATE
FROM EXAMPLE_COURSES EC
JOIN EXAMPLE_SECTIONS ES
ON (EC.COURSE_ID = ES.COURSE_ID)
WHERE ES.CAMPUS = 'Eden Prairie'
AND EC.TITLE = 'Database Design & SQL'
Note : This SQL statement has THREE clauses. It has a
The "AND" is a conjunction (just like in English) that combines conditions.
WHERE ES.CAMPUS = 'Eden Prairie'
AND EC.TITLE = 'Database Design & SQL'
And, if you wanted to just ensure one was true, you could use an OR.
WHERE ES.CAMPUS = 'Eden Prairie'
OR EC.TITLE = 'Database Design & SQL'
Let's imagine we want to know what courses are being offered today (4/21/2014). We would be looking for courses where today is between the start and end date of the course. Here is one way.
WHERE ES.START_DATE <= TO_DATE('04/21/2014','mm/dd/yyyy')
AND ES.END_DATE >= TO_DATE('04/21/2014','mm/dd/yyyy')
(The TO_DATE is an Oracle date conversion function. If you are not using an Oracle database, I am sure you can figure out something similar in your own world!)
When looking at this, notice the <= ? That just means "Less than or Equal to" (it's brother >= is, as you would suspect "Greater than or Equal to"). But there is a better way to do this.
WHERE TO_DATE('04/21/2014','mm/dd/yyyy')
BETWEEN ES.START_DATE AND ES.END_DATE
"BETWEEN" is also a comparison operator but it simplifies a the code! Remember that, in the distant past, SQL was written for Business Analysts! Not swanky developers, all coding in C. There are definitely times where we can (and should) use some of these more "English" operators.
Another good one is "IN." Let's imagine we want to know what courses are in two rooms (H255 and H257). We could use the following WHERE clause for this.
WHERE ES.ROOM = 'H255'
OR ES.ROOM = 'H257'
(We would not use AND here as that would ask which course was offered in BOTH rooms)
A cooler way to do this would be to use the IN clause.
WHERE ES.ROOM IN ('H255','H257')
Notice that this simplifies the WHERE clause and will make it easier if we want to add more rooms!
We are basically asking for rooms that are IN the parentheses!
There are two more comparative operators (LIKE and IS NULL) but I will leave those for another post. Also, I should add that if you were looking for something that is NOT EQUAL, BETWEEN or IN, the syntax would be ...
But that is good enough for now. The BEST way to learn this is to try (and fail) with this code, so I would strongly encourage you to try this stuff on your own. You have all the code. You can have access to an environment! It's up to you!!!
For this post, I am going to do a quick examination of the WHERE clause. This post assumes you have some understanding of the FROM clause (which can be found here) and the SELECT clause (which can be found here)
Here is an Table Structure Diagram showing all four tables and how they are related.
We are going to focus on EXAMPLE_COURSES and EXAMPLE_SECTIONS
Here is our starting SQL with its result set.
SELECT EC.FACULTY,
EC.COURSE,
EC.TITLE,
ES.CAMPUS,
ES.ROOM,
ES.START_DATE,
ES.END_DATE
FROM EXAMPLE_COURSES EC
JOIN EXAMPLE_SECTIONS ES
ON (EC.COURSE_ID = ES.COURSE_ID)
| FACULTY | COURSE | TITLE | CAMPUS | ROOM | START_DATE | END_DATE |
|---|---|---|---|---|---|---|
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 16-JAN-2014 | 15-MAY-2014 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 13-JAN-2014 | 19-MAY-2014 |
A quick warmup - If want to see only the row where the TITLE is "Database Design & SQL", we would need to add another clause; we would add the WHERE clause.
SELECT EC.FACULTY,
EC.COURSE,
EC.TITLE,
ES.CAMPUS,
ES.ROOM,
ES.START_DATE,
ES.END_DATE
FROM EXAMPLE_COURSES EC
JOIN EXAMPLE_SECTIONS ES
ON (EC.COURSE_ID = ES.COURSE_ID)
WHERE EC.TITLE = 'Database Design & SQL'
| FACULTY | COURSE | TITLE | CAMPUS | ROOM | START_DATE | END_DATE |
|---|---|---|---|---|---|---|
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 16-JAN-2014 | 15-MAY-2014 |
Let's look more closely at the WHERE clause.
WHERE EC.TITLE = 'Database Design & SQL'
- WHERE - Starts the WHERE clause
- EC.TITLE - The column we are restricting on.
- = - the "comparison operator"
- 'Database Design & SQL' - the character literal we are looking the TITLE to be EQUAL to.
So, this clause added the idea that we only wanted titles that were 'Database Design & SQL' in our result set. If we wanted to see only courses that were offered in the campus of Eden Prairie, we would change the query's WHERE clause to this ...
SELECT EC.FACULTY,
EC.COURSE,
EC.TITLE,
ES.CAMPUS,
ES.ROOM,
ES.START_DATE,
ES.END_DATE
FROM EXAMPLE_COURSES EC
JOIN EXAMPLE_SECTIONS ES
ON (EC.COURSE_ID = ES.COURSE_ID)
WHERE ES.CAMPUS = 'Eden Prairie'
| FACULTY | COURSE | TITLE | CAMPUS | ROOM | START_DATE | END_DATE |
|---|---|---|---|---|---|---|
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 16-JAN-2014 | 15-MAY-2014 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 13-JAN-2014 | 19-MAY-2014 |
... and if we wanted only courses that were offered in the campus of Eden Prairie AND had the title of "Database Design & SQL", we would alter the SQL to be this...
SELECT EC.FACULTY,
EC.COURSE,
EC.TITLE,
ES.CAMPUS,
ES.ROOM,
ES.START_DATE,
ES.END_DATE
FROM EXAMPLE_COURSES EC
JOIN EXAMPLE_SECTIONS ES
ON (EC.COURSE_ID = ES.COURSE_ID)
WHERE ES.CAMPUS = 'Eden Prairie'
AND EC.TITLE = 'Database Design & SQL'
| FACULTY | COURSE | TITLE | CAMPUS | ROOM | START_DATE | END_DATE |
|---|---|---|---|---|---|---|
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 16-JAN-2014 | 15-MAY-2014 |
Note : This SQL statement has THREE clauses. It has a
- SELECT clause (determining the columns to be returned),
- FROM clause (determining where the data is coming from), and a
- WHERE clause (determining the conditions which much be satisfied for a row to be returned.
Every SELECT statement MUST have a SELECT clause and a FROM clause.
It may have a WHERE clause. If it does, it only has ONE! So, if you were restricting on TWO things, the following syntax would NOT work!
WHERE ES.CAMPUS = 'Eden Prairie'
WHERE EC.TITLE = 'Database Design & SQL'
WHERE EC.TITLE = 'Database Design & SQL'
The "AND" is a conjunction (just like in English) that combines conditions.
WHERE ES.CAMPUS = 'Eden Prairie'
AND EC.TITLE = 'Database Design & SQL'
And, if you wanted to just ensure one was true, you could use an OR.
WHERE ES.CAMPUS = 'Eden Prairie'
OR EC.TITLE = 'Database Design & SQL'
Let's imagine we want to know what courses are being offered today (4/21/2014). We would be looking for courses where today is between the start and end date of the course. Here is one way.
WHERE ES.START_DATE <= TO_DATE('04/21/2014','mm/dd/yyyy')
AND ES.END_DATE >= TO_DATE('04/21/2014','mm/dd/yyyy')
(The TO_DATE is an Oracle date conversion function. If you are not using an Oracle database, I am sure you can figure out something similar in your own world!)
When looking at this, notice the <= ? That just means "Less than or Equal to" (it's brother >= is, as you would suspect "Greater than or Equal to"). But there is a better way to do this.
WHERE TO_DATE('04/21/2014','mm/dd/yyyy')
BETWEEN ES.START_DATE AND ES.END_DATE
"BETWEEN" is also a comparison operator but it simplifies a the code! Remember that, in the distant past, SQL was written for Business Analysts! Not swanky developers, all coding in C. There are definitely times where we can (and should) use some of these more "English" operators.
Another good one is "IN." Let's imagine we want to know what courses are in two rooms (H255 and H257). We could use the following WHERE clause for this.
WHERE ES.ROOM = 'H255'
OR ES.ROOM = 'H257'
(We would not use AND here as that would ask which course was offered in BOTH rooms)
A cooler way to do this would be to use the IN clause.
WHERE ES.ROOM IN ('H255','H257')
Notice that this simplifies the WHERE clause and will make it easier if we want to add more rooms!
We are basically asking for rooms that are IN the parentheses!
There are two more comparative operators (LIKE and IS NULL) but I will leave those for another post. Also, I should add that if you were looking for something that is NOT EQUAL, BETWEEN or IN, the syntax would be ...
- WHERE ES.ROOM <> 'H255'
- WHERE TO_DATE('04/21/2014','mm/dd/yyyy')
NOT BETWEEN ES.START_DATE AND ES.END_DATE - WHERE ES.ROOM NOT IN ('H255','H257')
But that is good enough for now. The BEST way to learn this is to try (and fail) with this code, so I would strongly encourage you to try this stuff on your own. You have all the code. You can have access to an environment! It's up to you!!!
Also, see http://www.w3schools.com/sql/sql_where.asp and http://www.w3schools.com/sql/sql_and_or.asp
(Why do you link to W3SCHOOLS so much? Because W3SCHOOLS is ANSI and do a MUCH BETTER JOB with these topics that I am! So, why reinvent what they have already done?)
