March 28, 2019 at 6:51 am
The question is "Find the names of courses that are prerequisites for other courses. List the course number and name, and the number and name of the prerequisite course."
Any help would be appreciated!
Here are the 2 tables:
SQL> DESC COURSE;
Name Type
----------------------
COURSE_NAME VARCHAR2(20)
COURSE_NUMBER VARCHAR2(8)
CREDIT_HOURS NUMBER(38)
OFFERING_DEPT VARCHAR2(4)
SQL> DESC PREREQ;
Name Type
---------------------
COURSE_NUMBER VARCHAR2(8)
PREREQ VARCHAR2(8)
Here's what I have, but it doesn't work properly...
SELECT c.course_name AS CourseName, c.course_number AS CourseNum, c.course_name AS PreName, p2.prereq AS PreNum
FROM PREREQ p, Course c, PREREQ p2
WHERE p.course_number = c.course_number
AND p2.prereq = p.prereq;
March 28, 2019 at 8:47 am
I figured it out!
SELECT c.course_name AS CourseName, c.course_number AS CourseNum, c2.course_name AS PreName, c2.course_number AS PreNum
FROM Prereq p, Course c, Course c2
WHERE p.course_number = c.course_number
AND p.prereq = c2.course_number;
March 28, 2019 at 11:48 am
Glad you figured it out, you may want to learn the more modern join syntax though, where the join conditions are specified within parts of the FROM clause instead of mixed in with the filtering logic of the WHERE clause:
https://www.w3schools.com/sql/sql_join.asp
March 28, 2019 at 1:24 pm
Ahh... Yes, so it would be something like this....
SELECT c.course_name AS CourseName, c.course_number AS CourseNum, c2.course_name AS PreName, c2.course_number AS PreNum
FROM Prereq p
INNER JOIN Course c ON p.course_number = c.course_number
INNER JOIN Course c2 ON p.prereq = c2.course_number;
Ok, no this doesn't work.... Could you show me how to do multiple joins in this fashion?
Thanks so much!
March 28, 2019 at 2:33 pm
whats the SQL server you're using?
March 29, 2019 at 12:57 am
This was removed by the editor as SPAM
March 29, 2019 at 10:22 am
from the use of VARCHAR2 I'm guessing they are using Oracle, but Oracle has supported INNER JOIN and OUTER JOIN syntax since version 9i
February 25, 2020 at 1:55 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply