CODEWITHSUNDEEP
sqloracle-databaseinner-join
Anthony
Anthony

Reputation: 21

SQL Assignment about joining tables

I am working on a SQL assignment in Oracle. There are two tables.

table1 is called Person10:

fields include: ID, Fname, Lname, State, DOH, JobTitle, Salary, Cat.

table2 is called StateInfo:

fields include: State, Statename, Capital, Nickname, Pop2010, pop2000, pop1990, sqmiles.

Question:

Create a view named A10T2 that will display the StateName, Capital and Nickname of the states that have at least 25 people in the Person10 table with a Cat value of N and an annual salary between $75,000 and $125,000. The three column headings should be StateName, Capital and Nickname. The rows should be sorted by the name of the state.

What I have : 

CREATE VIEW A10T2 AS
SELECT StateName, Capital, Nickname
FROM STATEINFO INNER JOIN PERSON10 ON
     STATEINFO.STATE = PERSON10.STATE
WHERE Person10.CAT = 'N' AND 
      Person10.Salary in BETWEEN (75000 AND 125000) AND 
      count(Person10.CAT) >= 25
ORDER BY STATE;

It gives me an error saying missing expression. I may need a group expression... but i dont know what I am doing wrong.

Upvotes: 0

Views: 581

Answers (4)

Dresden
Dresden

Reputation: 559

Yeah I originally messed this up when I first answered this because it was on the fly and I didn't have a chance to test what I was putting down. I forgot using a GROUP BY is more suited for aggregate functions (Like SUM, AVG and COUNT in the select) and that's probably why it's throwing the error. Using a ORDER BY is probably the correct option in this case. And you want to order your results by the state so you would use StateName.

SELECT S.StateName, S.Capital, S.Nickname
FROM STATEINFO S
INNER JOIN PERSON10 P ON S.STATE = P.STATE
WHERE P.CAT = 'N' 
AND P.Salary BETWEEN 75000 AND 125000 
ORDER BY S.StateName
HAVING count(P.CAT) >= 25;

Upvotes: 1

Roberto
Roberto

Reputation: 533

You can try using a Sub Query like this.

CREATE VIEW A10T2 AS
SELECT statename, capital, nickname
FROM stateinfo
WHERE statename IN (SELECT statename 
                    FROM person10 
                    WHERE Cat = 'N'
                    AND Salary BETWEEN 75000 AND 125000
                    GROUP BY statename
                    HAVING COUNT(*) >= 25)
ORDER BY statename

Upvotes: 0

elixenide
elixenide

Reputation: 44831

I'm no Oracle expert, but I'm pretty sure 

Person10.Salary in BETWEEN (75000 AND 125000)

should be

Person10.Salary BETWEEN 75000 AND 125000

(no IN and no parentheses). That's how all other SQL dialects I know of work.

Also, move the COUNT() from the WHERE clause to a HAVING clause:

CREATE VIEW A10T2 AS
SELECT StateName, Capital, Nickname
FROM STATEINFO INNER JOIN PERSON10 ON
     STATEINFO.STATE = PERSON10.STATE
WHERE Person10.CAT = 'N' AND 
      Person10.Salary BETWEEN 75000 AND 125000
ORDER BY STATE
HAVING count(Person10.CAT) >= 25;

Upvotes: 0

NSamson
NSamson

Reputation: 21

Try moving your count() to HAVING instead of WHERE. You'll also need a GROUP BY clause containing StateName, Capital, and Nickname.

I know this link is Microsoft, not Oracle, but it should be helpful.

https://msdn.microsoft.com/en-us/library/ms180199.aspx?f=255&MSPPError=-2147217396

Upvotes: 0

Related Questions