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 SELECT clause. This post assumes you have some understanding of the FROM clause (which can be found here)
Here is our starting point.
SELECT *
FROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
This is a join involving four tables. Here is an Table Structure Diagram showing all four tables and how they are related.
There are 20 columns here! And some columns repeat. Some are non-information-bearing keys. In fact, the output of this is so wide I am not going to put it into this post. If only there was some way to select which columns we want to see rather than take everything. Some way to select ... some way to select ... some way to select ...
SELECT!!! It is the SELECT clause that controls what is projected back at you from the query.
Let's have a closer look at our options.
When choosing the columns I want to see, it is a safe bet I don't want to see some of these keys over and over again. Also, I may not really care about some of these other columns. In fact, for my purposes, maybe I only want to see the highlighted columns below.
So now it is just a matter of altering the SELECT clause to select which columns I want projected into my results.
Let's select the columns for the EXAMPLE_COURSES table.
SELECT ec.faculty,
ec.course,
ec.titleFROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
Notice that, because our query has more than one table, we are prefixing each column ANYWHERE IT APPEARS IN THE QUERY with the table alias (discussed in the post I did about the FROM clause)
When we run this query, we will get many rows of what seems the same information. That's okay ... In this case, we have many students taking 2 courses and, as we are looking at this data set FOR EACH STUDENT, even if we aren't looking at student information, we are going to see one course for each student!
Let's add the SECTION info.
SELECT ec.faculty,
ec.course,
ec.title,
es.campus,
es.roomFROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
We are starting to see evidence of diversity in the data. Here is what we are seeing so far -
* - I am removing rows manually for the sake of the length of the post. In another post, we will talk about removing rows through SQL.
Now, let's work out the ENROLLMENTS which really only has the Grade these students have in these classes.
SELECT ec.faculty,
ec.course,
ec.title,
es.campus,
es.room,
er.gradeFROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
(ALL A'S!!! This instructor must be a push over!!!)
Okay, now to bring into the projection the STUDENT information.
SELECT ec.faculty,
ec.course,
ec.title,
es.campus,
es.room,
er.grade,
s.student_name,
s.advisorFROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
This is a major accomplishment! One thing, before I leave this is to suggest you are probably going to want to control the order of your columns (the order of your rows, I will leave for another post). Just put the columns in the order you want them to be seen.
SELECT s.advisor,
s.student_name,
ec.faculty,
ec.course,
ec.title,
es.campus,
es.room,
er.gradeFROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
NOTE : One common thing that messes us newbies is that comma. The comma separates columns. The last column should NOT have a comma trailing it.
You would never do this, right?
SELECT ,
FROM EXAMPLE_COURSES ec
Or this?
SELECT ec.faculty,
FROM EXAMPLE_COURSES ec
But, when you get lots of columns, you may try this ...
SELECT ec.faculty,
ec.course,
ec.title,
FROM EXAMPLE_COURSES ec
If you see an error message like ORA-00936: missing expression maybe the first thing you should do is look to make sure you don't have an "extra" column in your SELECT.
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 more information about the ANSI SELECT clause, I would go to -
For this post, I am going to do a quick examination of the SELECT clause. This post assumes you have some understanding of the FROM clause (which can be found here)
Here is our starting point.
SELECT *
FROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
This is a join involving four tables. Here is an Table Structure Diagram showing all four tables and how they are related.
There are 20 columns here! And some columns repeat. Some are non-information-bearing keys. In fact, the output of this is so wide I am not going to put it into this post. If only there was some way to select which columns we want to see rather than take everything. Some way to select ... some way to select ... some way to select ...
SELECT!!! It is the SELECT clause that controls what is projected back at you from the query.
Let's have a closer look at our options.
When choosing the columns I want to see, it is a safe bet I don't want to see some of these keys over and over again. Also, I may not really care about some of these other columns. In fact, for my purposes, maybe I only want to see the highlighted columns below.
So now it is just a matter of altering the SELECT clause to select which columns I want projected into my results.
Let's select the columns for the EXAMPLE_COURSES table.
SELECT ec.faculty,
ec.course,
ec.titleFROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
Notice that, because our query has more than one table, we are prefixing each column ANYWHERE IT APPEARS IN THE QUERY with the table alias (discussed in the post I did about the FROM clause)
When we run this query, we will get many rows of what seems the same information. That's okay ... In this case, we have many students taking 2 courses and, as we are looking at this data set FOR EACH STUDENT, even if we aren't looking at student information, we are going to see one course for each student!
Let's add the SECTION info.
SELECT ec.faculty,
ec.course,
ec.title,
es.campus,
es.roomFROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
We are starting to see evidence of diversity in the data. Here is what we are seeing so far -
| FACULTY | COURSE | TITLE | CAMPUS | ROOM |
|---|---|---|---|---|
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 |
| <<Snip>> * | ||||
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 |
* - I am removing rows manually for the sake of the length of the post. In another post, we will talk about removing rows through SQL.
Now, let's work out the ENROLLMENTS which really only has the Grade these students have in these classes.
SELECT ec.faculty,
ec.course,
ec.title,
es.campus,
es.room,
er.gradeFROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
| FACULTY | COURSE | TITLE | CAMPUS | ROOM | GRADE |
|---|---|---|---|---|---|
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| <<Snip>> | |||||
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 |
(ALL A'S!!! This instructor must be a push over!!!)
Okay, now to bring into the projection the STUDENT information.
SELECT ec.faculty,
ec.course,
ec.title,
es.campus,
es.room,
er.grade,
s.student_name,
s.advisorFROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
| FACULTY | COURSE | TITLE | CAMPUS | ROOM | GRADE | STUDENT_NAME | ADVISOR |
|---|---|---|---|---|---|---|---|
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 | Nicholas J | Grant |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 | Jacob A | Mary |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 | Paul D | Steve |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 | Paul D | Steve |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 | Lindsay M | Dave |
| <<Snip>> | |||||||
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 | W T | Grant |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 | Chantel | Mary |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 | Divya | Steve |
| CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 | James F | Dave |
| CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 | Steve D | Grant |
This is a major accomplishment! One thing, before I leave this is to suggest you are probably going to want to control the order of your columns (the order of your rows, I will leave for another post). Just put the columns in the order you want them to be seen.
SELECT s.advisor,
s.student_name,
ec.faculty,
ec.course,
ec.title,
es.campus,
es.room,
er.gradeFROM EXAMPLE_COURSES ec
INNER JOIN EXAMPLE_SECTIONS es
ON (EC.COURSE_ID = ES.COURSE_ID)
INNER JOIN EXAMPLE_ENROLLMENTS er
ON (ER.COURSE_ID = ES.COURSE_ID
AND ER.SECTION = ES.SECTION)
INNER JOIN EXAMPLE_STUDENTS s
ON (S.STUDENT_ID = ER.STUDENT_ID)
| ADVISOR | STUDENT_NAME | FACULTY | COURSE | TITLE | CAMPUS | ROOM | GRADE |
|---|---|---|---|---|---|---|---|
| Grant | Nicholas J | CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| Mary | Jacob A | CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 |
| Steve | Paul D | CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 |
| Steve | Paul D | CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| Dave | Lindsay M | CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| <<Snip>> | |||||||
| Dave | Daniel | CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| Grant | W T | CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| Mary | Chantel | CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| Steve | Divya | CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 |
| Dave | James F | CCIS | 2701 | Database Design & SQL | Eden Prairie | H257 | 4 |
| Grant | Steve D | CCIS | 2801 | Systems Analysis | Eden Prairie | H255 | 4 |
NOTE : One common thing that messes us newbies is that comma. The comma separates columns. The last column should NOT have a comma trailing it.
You would never do this, right?
SELECT ,
FROM EXAMPLE_COURSES ec
Or this?
SELECT ec.faculty,
FROM EXAMPLE_COURSES ec
But, when you get lots of columns, you may try this ...
SELECT ec.faculty,
ec.course,
ec.title,
FROM EXAMPLE_COURSES ec
If you see an error message like ORA-00936: missing expression maybe the first thing you should do is look to make sure you don't have an "extra" column in your SELECT.
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 more information about the ANSI SELECT clause, I would go to -
(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?)
No comments:
Post a Comment