SQL Assignment about joining tables

Question

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.

Can you post the detailed error message please?

Dresden
– Dresden

2016-04-14 19:04:27 +00:00
Commented Apr 14, 2016 at 19:04 — Dresden
– Dresden, Commented Apr 14, 2016 at 19:04

Dresden · Accepted Answer · 2016-04-14 21:49:56Z

1

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;

edited Apr 14, 2016 at 21:49

answered Apr 14, 2016 at 19:07

Dresden

5702 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Anthony Over a year ago

Hi. thanks for the solution but I am still getting an error saying "not a GROUP BY expression"

elixenide Over a year ago

This would be a much better answer if you explained it somewhat. Answers that consist of only code are not usually helpful to other users.

Anthony Over a year ago

Ehh..still giving me error: "not a group by expression"

Dresden Over a year ago

Sorry I forgot using a GROUP BY usually relates to an aggregate function like AVG or SUM in the select. That should be why it's throwing an error so using ORDER BY S.StateName should work. Please let me know if you're still having issues because I tried to do this on the fly without testing it and I'm realizing that wasn't such a good idea.

NSamson · Accepted Answer · 2016-04-14 19:06:05Z

0

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

answered Apr 14, 2016 at 19:06

NSamson

212 bronze badges

Comments

elixenide · Accepted Answer · 2016-04-14 19:11:23Z

0

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;

edited Apr 14, 2016 at 19:11

answered Apr 14, 2016 at 19:04

elixenide

44.9k16 gold badges79 silver badges103 bronze badges

Comments

Roberto · Accepted Answer · 2016-04-14 20:24:02Z

0

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

answered Apr 14, 2016 at 20:24

Roberto

5332 silver badges10 bronze badges

2 Comments

Anthony Over a year ago

Thank you so much. I never thought this can be done by nested select statement.

Roberto Over a year ago

Hi @AyazAmir if this or any answer has solved your question please consider accepting it by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this.

Collectives™ on Stack Overflow

SQL Assignment about joining tables

4 Answers 4

4 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related