question

iamamagicstar38 avatar image
iamamagicstar38 asked

SQL-Server-2012 sql query

The instructor is asking me to write a query that will return all the DirectorIDs who are living in the same address locality.** PLEASE HELP! DirectorID Movie_ID Dir_Address 100 M7 New York 101 M4 Houston 102 M7 New York 103 M2 Orlando 104 M6 Orlando
sql-server-2012homework
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

As you said, an instructor is asking you to write this. We can help with specific questions but would rather not just write it for you. What have you tried so far?
2 Likes 2 ·
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
1 Like 1 ·
This is what i done so far..I am not 100% sure if this view i created will accomplish what he is asking: He did not give me a table name just those 3 columns, I am assuming that is the Directors table. Create View Directors AS Select * From Directors Where DirectorState IN ('New York','Houston', 'Orlando') Group By City Order By DirectorID;
0 Likes 0 ·
neeehar avatar image
neeehar answered
use rank or row_number function
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

do the directors need to be ranked or ordered?
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
Thanks for providing your SQL code for your initial attempt at answering the question. This is what you have so far: Create View Directors AS Select * From Directors Where DirectorState IN ('New York','Houston', 'Orlando') Group By City Order By DirectorID; That's a good start, but I think it needs a little work. 1. I didn't see any mention of creating a view. You might just need the SELECT statement. 2. You can't use `*` in the `SELECT` if you use `GROUP BY` unless every column is grouped. When grouping, every column in the `SELECT` must be a grouping column or use a domain aggregate function (COUNT, SUM, MAX, etc.). In addition, the instructions ask you to "return all DirectorIDs", so you shouldn't need the `*` anyway. I don't think you want `GROUP BY` because you want to return a list rather than an aggregated summary. 3. The instructions seem to imply you just want all directors for a single locality. If that is the case, you could just filter with a where clause. If you want multiple localities, you could keep them together by making the locality the first sort column.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.