Help with sql plus query [message #448702] |
Wed, 24 March 2010 18:52  |
h0neydip
Messages: 3 Registered: March 2010
|
Junior Member |
|
|
SQL> describe crscat2;
Name Null? Type
----------------------------------------- -------- ---------
CRSID NOT NULL CHAR(6)
CRSNAME CHAR(12)
CRSPRE CHAR(6)
SQL> select * from crscat2;
CRSID CRSNAME CRSPRE
------ ------------ ------
CIS101 Fundamentals
CIS151 Prog. I CIS101
CIS251 Prog. II CIS151
CIS400 VB CIS251
CIS370 Sys. Anlys CIS151
CIS375 Internet I CIS151
CIS315 Machine Org. CIS151
CIS475 Internet II CIS375
CIS410 Op. Systems CIS315
CIS411 Database CIS370
CIS425 Java CIS251
CRSID CRSNAME CRSPRE
------ ------------ ------
CIS470 Data Comm. CIS410
CIS495 Project
SQL> describe crscat3;
Name Null? Type
----------------------------------------- --------
CRSID NOT NULL CHAR(6)
CRSNAME CHAR(12)
SQL> describe requisite;
Name Null? Type
----------------------------------------- -------- --------
CRSID NOT NULL CHAR(6)
REQID NOT NULL CHAR(6)
REQTYPE CHAR(3)
SQL> describe fallclass;
Name Null? Type
----------------------------------------- -------- ------------
CRN NOT NULL NUMBER(4)
CRSID CHAR(6)
HOURS NUMBER(3,1)
SQL> select * from fallclass;
CRN CRSID HOURS
---------- ------ ----------
1111 CIS101 3
1112 CIS101 3
1113 CIS101 3
1116 CIS101 3
1117 CIS151 3
1119 CIS151 3
1122 CIS251 3
1128 CIS375 3
1133 CIS400 3
1135 CIS400 3
1140 CIS470 3
CRN CRSID HOURS
---------- ------ ----------
1141 CIS470 3
1142 CIS475 3
1144 CIS495 1
1145 CIS495 3
SQL> select * from crscat3;
CRSID CRSNAME
------ ------------
CIS101 Fundamentals
CIS151 Prog. I
CIS251 Prog. II
CIS400 VB
CIS370 Sys. Anlys
CIS375 Internet I
CIS315 Machine Org.
CIS475 Internet II
CIS410 Op. Systems
CIS411 Database
CIS425 Java
CRSID CRSNAME
------ ------------
CIS470 Data Comm.
CIS495 Project
SQL> select * from requisite;
CRSID REQID REQ
------ ------ ---
CIS151 CIS101 pre
CIS251 CIS151 pre
CIS315 CIS151 pre
CIS370 CIS151 co
CIS375 CIS151 pre
CIS400 CIS251 pre
CIS410 CIS315 pre
CIS411 CIS370 pre
CIS425 CIS251 pre
CIS425 CIS370 pre
CIS470 CIS410 pre
CRSID REQID REQ
------ ------ ---
CIS475 CIS375 pre
CIS475 CIS411 co
I need help to write a query that will list the course id and name and requisite type for all pre and co requisite for CIS475. Thanks for your help
[Updated on: Wed, 24 March 2010 20:22] Report message to a moderator
|
|
|
Re: Help with sql plus query [message #448703 is a reply to message #448702] |
Wed, 24 March 2010 20:28   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
This can be solved by using CONNECT BY clause
[Updated on: Wed, 24 March 2010 20:28] Report message to a moderator
|
|
|
Re: Help with sql plus query [message #448719 is a reply to message #448702] |
Thu, 25 March 2010 00:21   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Sun, 07 March 2010 19:57Do not post the content of your tables but post the create table and insert statements to have this content.
And post the result you want with this data.
And ALWAYS post your Oracle version (with 4 decimals).
Regards
Michel
From your first one:
Michel Cadot wrote on Wed, 03 March 2010 07:21It is useless to post the content of your tables but it is highky useful to post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.
...
Regards
Michel
If you read the other posts,, you will see that those that posted accordingly got a direct and complete answer.
In addition, we are waiting for a feedback from you when you found a solution or when you did not.
Regards
Michel
|
|
|
|
|
Re: Help with sql plus query [message #549371 is a reply to message #546038] |
Thu, 29 March 2012 23:17  |
 |
msqueen0826
Messages: 13 Registered: March 2012
|
Junior Member |
|
|
You are absolutely correct...My apologies
The following is the correct answer...
SQL> SELECT cat.crsid, cat.crsname, req.reqtype
2 FROM crscat3 cat, requisite req
3 WHERE cat.crsid = req.crsid
4 AND req.crsid = 'CIS475';
CRSID CRSNAME REQ
------ ------------ ---
CIS475 Internet II pre
CIS475 Internet II co
|
|
|