Jump to content

SQL Queries


krazigoddezz

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...