krazigoddezz Posted April 23, 2009 Report Share Posted April 23, 2009 So I'm working on another homework assignment... I've been working on it for about a week now and I'm having problems trying to finish of the last of it. I tried getting help from my classmates, but I seem to be the only one that is almost done. So I have to use iSQLPlus for the assignment. So what I am stuck on is retrieving information for one of the questions. I need to retrieve the SSN & number of projects reviewed by the reviewer from the Reviewer table for those people who reviewed more than two projects So the three tables which I am using: CREATE TABLE PROJECT ( PROJECT_ID INTEGER NOT NULL, TITLE VARCHAR(50), ARCHIVED VARCHAR(1), PROJECT_STATUS VARCHAR(20), PRIMARY KEY (PROJECT_ID) ); CREATE TABLE REVIEWER (SSN CHAR(9) NOT NULL, FIRST_NAME VARCHAR(20), LAST_NAME VARCHAR(20), PRIMARY KEY (SSN) ); CREATE TABLE REVIEWS ( PROJECT_ID INTEGER NOT NULL, SSN CHAR(9) NOT NULL, REVIEWER_ROLE VARCHAR(20), PRIMARY KEY (PROJECT_ID, SSN), FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT (PROJECT_ID), FOREIGN KEY (SSN) REFERENCES REVIEWER (SSN) ); So I'm trying to go with something like: SELECT SSN, COUNT(*) FROM REVIEWER WHERE SSN IN (SELECT COUNT(*) FROM REVIEWS WHERE REVIEWER_ROLE > 2 ); I know this doesn't work because REVIEWER_ROLE is not an integer. I think I need to use COUNT(*) to get the number of projects they have worked on. I was thinking that I wouldn't have to use the REVIEWER table so I could do something like: SELECT SSN, COUNT(*) FROM REVIEWS WHERE REVIEWER_ROLE > 2 I tried using the Group By and Having, even Natural Join & Exists, but I always come back to realizing that nothing will work if I don't know how to get the number of projects each reviewer reviewed. I appreciate it in advance for anyone to replies to this who can help me. I know this is something which is very simple, but I am not seeing it or figuring it out. Thanks once again. Quote Link to comment Share on other sites More sharing options...
LSW Posted April 23, 2009 Report Share Posted April 23, 2009 Well I can't test it, but this may be a starting point. SELECT SSN, COUNT(project_ID) AS reviewNumber FROM REVIEWS WHERE reviewNumber > 2 Basically You want the SSN. You want the number of projects reviewed (I would guess project_id, but maybe reviewer_role is better, your call) so as you say you count how many and asign it an alias (reviewNumber). It comes from the reviews table. Then we compare the number of reviewNumber which is now an Interger with the comparisson > 2. If the count is larger than 2 it should give you the SSN and the number. I am just doing this in a break at work so it may not be perfect, but it may give you a start to work out the kinks. If you then want to show the names of the reviewer and/or projects, then you would use JOINs. Hope this helps. Quote Link to comment Share on other sites More sharing options...
krazigoddezz Posted April 23, 2009 Author Report Share Posted April 23, 2009 LSW, it did not work, but very helpful. I didn't know I could use COUNT like that but it makes sense. The error I get is: WHERE REVIEW_NUMBER > 2 * ERROR at line 3: ORA-00904: "REVIEW_NUMBER": invalid identifier and what used was: SELECT SSN, COUNT(REVIEWER_ROLE) "REVIEW_NUMBER" FROM REVIEWS WHERE REVIEW_NUMBER > 2; I looked up the 904 error and added the quotes to REVIEW_NUMBER and removed the AS... So I'll try to do a little more research tonight. Once again, thanks! Quote Link to comment Share on other sites More sharing options...
krazigoddezz Posted April 26, 2009 Author Report Share Posted April 26, 2009 I finally got it to work. Instead of the REVIEW_NUMBER in the WHERE clause, I had to use COUNT(REVIEWER_ROLE). They I got another error that I couldn't use the SSN and COUNT(REVIEWER_ROLE). I had to use GROUP BY because I didn't know that you can't have a column and a COUNT in the SELECT statement without GROUP BY. So it finally came down to: SELECT SSN, COUNT(REVIEWER_ROLE) AS "REVIEWED" FROM REVIEWS GROUP BY SSN HAVING COUNT(REVIEWER_ROLE) >2; I really appreciate the little hint you gave me LSW! I can finally move on to the website for this. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.