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.:confused: :confused: :confused:
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/cd/B10501_01/server.920/a96540/toc.htm
I hope this helps, if not, come back and ask more questions.
Marty
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
GRANT SELECT
ON Students
TO FirstName, LastName;
REVOKE SELECT
ON Students
FROM FirstName, LastName;
Hi,
If I separate the first and last name by commas, it complete disregards the last name
Thank you,
KLgirl:(
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
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
Hehe, that happens to the best of us... I'm glad it worked out for you.