Joining three tables in Oracle11g SQL

Question

Normally, this wouldn't be a problem for me, but 1. My instructor never taught us how to join three tables like this and 2. It wasn't even covered on this week's assignment. But because these databases aren't in Oracle interface database that we normally use, I have no way of even doing trial and error.

I have three tables. The contents aren't important to the question. The first table is STUDENTS, with columns LAST_NAME, SID, and MID (two of the MID cells have NULL values here). Second table is COURSES, with columns COURSES (don't know why whoever designed this question decided to name it the same as the table) and CID (I'm assuming that's a primary key) Third table is ENROLLED, with columns SID and CID.

I'm asked the following question "Use the three tables shown above to answer the following questions: a. What kind of join would you use to join all three tables? Write the syntax that would produce the desired result. b. Name two tables that could be used to retrieve data from a natural join. Write the syntax that would produce the desired result."

I'm stuck on this one. Internet hasn't been much help so far, not even Oracle's materials. We learned about joining, but we never joined more than two tables. Any help would be appreciated.

there's nothing special to do. just add more join clauses. the magic happens when you decide what KIND of joins to use for each of the tables. — Marc B, Apr 05 '15 at 05:27
Exactly. This is new ground that was never covered because my instructor doesn't actually teach us anything. So I have not idea what joins to use. — mthomp81, Apr 05 '15 at 05:34
you can learn on w3schools for that link is http://www.w3schools.com/sql/ — Pradnya Bolli, Apr 05 '15 at 06:04
I KNOW how to do basic SQL, but when there's not at least a virtual database with THESE exact tables, I can't figure it out. I have to actually do it, I'm unable to just magically write syntax when the tables don't exist in an actual database. Can some PLEASE just tell me how to figure this out? — mthomp81, Apr 05 '15 at 06:16
So you are asking us to write a query, because you don't have a database to test your own work on? Joining more than two tables works the same as joining one or two tables. If you want to really experiment, you can install a Oracle Personal Edition locally, or use a tool like [SQLFiddle](http://sqlfiddle.com/). — GolezTrol, Apr 05 '15 at 06:18

score 0 · Accepted Answer · answered Apr 05 '15 at 08:43

0

STUDENTS, with columns LAST_NAME, SID, and MID COURSES, with columns COURSES and CID ENROLLED, with columns SID and CID.

a table can be joined with another table if both the table have one column in common. so here STUDENTS TABLE can be joined with ENROLLED,

ENROLLED.SID =STUDENTS.SID

and COURSES can be joined with ENROLLED

ENROLLED.CID=COURSES.CID

answered Apr 05 '15 at 08:43

GVR

320
1
3
15

I had a feeling this was the "how", but I didn't know the name of the join. I got in touch with my teacher. He said the answer he's looking for is just a "many-to-many" join, a term that doesn't show up in any of the text we're required to study (and I spend a lot of time studying because the quizzes and exams throw nothing but curve-ball questions rather than what's vital.) But basically, an equi join. But this helps a lot. Thank you, gabbi. – mthomp81 Apr 05 '15 at 20:57

score 0 · Answer 2 · edited May 23 '17 at 10:34

What kind of join would you use to join all three tables? Write the syntax that would produce the desired result.

select last_name, courses
  from students 
    left join enrolled on students.sid = enrolled.sid
    left join courses on enrolled.cid = courses.cid

or

select last_name, courses from students
  left join (select * from enrolled join courses using (cid)) using (sid)

SQLFiddle

Here I used left join to show student even if he did not enroll anywhere. But you can use also inner join(s) to avoid such situation or full join to show everything - students who enrolled somewhere, these who did not enroll and courses that have no students. In this Stack Overflow topic you have very good explanation how different types of joins works.

Name two tables that could be used to retrieve data from a natural join. Write the syntax that would produce the desired result.

These pairs are: ENROLLED and STUDENTS (common key SID) and ENROLLED and COURSES (common key CID).

select * from enrolled natural join students;

select * from enrolled natural join courses;

You can use SQLFiddle to test your own ideas, like I used for first query. Choose database (Oracle) from listbox, next build schema (create tables, insert data) in left panel then write queries in right panel. If query runs succesfully results will be visible at bottom.

Joining three tables in Oracle11g SQL

2 Answers2