Home » SQL & PL/SQL » SQL & PL/SQL » Help with sql plus query
Help with sql plus query [message #448702] Wed, 24 March 2010 18:52 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Sun, 07 March 2010 19:57
Do 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:21
It 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 #546037 is a reply to message #448702] Sun, 04 March 2012 19:14 Go to previous messageGo to next message
msqueen0826
Messages: 13
Registered: March 2012
Junior Member
Not sure what you want but from what I see

SELECT cat.crsid, cat.crsname, req.reqtype
WHERE cat.crsid = req.crsid
AND req.crsid = 'CIS475'
/
Re: Help with sql plus query [message #546038 is a reply to message #546037] Sun, 04 March 2012 19:22 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Not sure what you want but from what I see
You never get a second chance to make a first impression.
So for you very first post here you decided to resurrect a two year old thread to provide incorrect solution.
SWEET!

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Sun, 04 March 2012 19:26]

Report message to a moderator

Re: Help with sql plus query [message #549371 is a reply to message #546038] Thu, 29 March 2012 23:17 Go to previous message
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

Previous Topic: To convert from GMT time to CST time with Daylight time savings (merged 4)
Next Topic: Meaning of || '' in queries
Goto Forum:
  


Current Time: Fri Sep 30 02:13:21 CDT 2016

Total time taken to generate the page: 0.15518 seconds