#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    11

    Unhappy Join Counting, please anyone help!


    I have two tabels:
    Column Name Data Type
    StudentID NUMBER(5,0) NOT NULL
    Name VARCHAR2(25)
    Major VARCHAR2(15)
    GPA NUMBER(6,3)


    Column Name Data Type
    StudentID NUMBER(5,0) NOT NULL
    CourseNumber VARCHAR2(15) NOT NULL
    CourseName VARCHAR2(25)
    Semester VARCHAR2(10)
    Year NUMBER(4,0)
    Grade VARCHAR2(2)

    The two things I need to do:
    For each student, count the number of courses he or she has taken.

    then modify the above query to only count CMIS courses (using a substring function on the coursenumber)

    I tried a lot of different things, which didn't work. I am a beginner and if somebody can help me I would really, really appreciate it.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Austin, TX
    Posts
    9
    Rep Power
    0
    I'm going to assume that the first table is called STUDENT, and the second is SCHEDULE. 'Cos that's what they look like.

    select a.name, a.studentid, count(b.coursenumber)
    from student a, schedule b
    where a.studentid = b.studentid
    group by a.studentid, a.name

    will get you a list of students, each with the number of courses they've taken. In order to limit the courses you want to count, you simply add it to the where clause.

    select a.name, a.studentid, count(b.coursenumber)
    from student a, schedule b
    where a.studentid = b.studentid
    and substr(b.coursenumber,1,4) = '1234'
    group by a.studentid, a.name

    Or you can limit which student you're counting by major in the same fashion. I hope this helps get you started.

    Oracle's documentation is notoriously difficult for beginners to find stuff in (okay, not just beginners), but still, I thought I'd give you the link to their sql reference section of their documentation site. May require registering for a free account.
    http://download-west.oracle.com/docs...a96540/toc.htm

    I hope this helps, if not, come back and ask more questions.

    Marty
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    11

    Thank you


    Hi Marty,
    This worked like charm...thank you very much.
    Might you be able to tell me what's wrong with these two statements?

    GRANT SELECT
    ON Students
    TO FirstName LastName;


    REVOKE SELECT
    ON Students
    FROM FirstName LastName;

    Oracle doesn't like them :-) It says wrong ending, it's probably something simple that I am not seeing!

    I appreciate your help...thanks again and I will check out that linke.
    Natalie
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    GRANT SELECT
    ON Students
    TO FirstName, LastName;

    REVOKE SELECT
    ON Students
    FROM FirstName, LastName;
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    11
    Hi,

    If I separate the first and last name by commas, it complete disregards the last name

    Thank you,
    KLgirl
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Austin, TX
    Posts
    9
    Rep Power
    0
    Hmm, Oracle doesn't allow spaces in usernames. At least I think so. That said, I don't have access to a test system at the moment.

    I'd try just putting the name in ticks, i.e.

    grant select on students to 'Bob Smith';

    Though, like I said, I don't think that you can have usernames with spaces in them. Without some doing, anyway. Are you sure that you're using the oracle username, and not an application user or something?

    Again, I hope this gets you going in the right direction. If not, stop by and ask another question.

    Marty
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    11
    Thanks Marty,
    Your input was most helpful. I was being an idiot...I had access to the user name of this person and totally forgot it. When I used the user name, it worked just fine.
    Duhhhhh, sometimes I could kick myself

    KLgirl
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Austin, TX
    Posts
    9
    Rep Power
    0
    Hehe, that happens to the best of us... I'm glad it worked out for you.

    Marty

IMN logo majestic logo threadwatch logo seochat tools logo