Select and Distinct

Question

Select and Distinct

yrstruly

Hall of Fame

Points: 3475
More actions
January 5, 2022 at 11:37 am

#3970078

Please assist?
Need to write a query that returns the names of all the professors and their respective courses. Each row must contain the name of the professor followed by the name of the course that the professor teaches. There must be no duplicates in terms of the rows.
My code:
SELECT dept.name as department_name, COUNT(prof.id) as professor_count
FROM Department dept INNER JOIN Professor prof ON prof.deptID = dept.deptID
GROUP BY dept.name
ORDER BY professor_count DESC;
Attachments:
You must be logged in to view attached files.
January 6, 2022 at 2:59 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

Looking at this, it looks like a homework assignment, so I am not about to just give the answer in TSQL format, but I will try to lead you to it by describing what I think you need help with on this.

First thing I'd do is look at the expected output and read the requirements. Your output is giving the department name which is 1/2 the battle, but then is giving the count of the professors NOT the professor name. Why are you counting the professors rather than just providing their names?

The other bit that I am confused about is in the title of this thread, you put "Select and Distinct" yet you are not using Distinct in your query despite indicating that it is what you are trying to do? Is there a reason you don't have distinct in the query? Is that part of the problem that you need to get a distinct list without using the distinct keyword? If so, that is a fun problem with multiple solutions that will likely never be used in a real-world scenario.

Also, since the requirements you listed do not indicate that the order is important and it doesn't sound like you need any aggregates, I would remove the grouping and ordering and ONLY add them back if they become part of the requirements.

Did the above help?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

I did a misread on the requirements because there are actually two possible answers according to the posted requirements and so had to rewrite this post.

Still, there is no need to get the Department table involved here.

The problem with the requirements are that they have not clearly identified what "respective" means. It could be ANY course in the same department as the professor or it could mean ONLY those that are scheduled.

YOU have the data and you should be able to tell what "respective" actually means by doing a little analysis. That's a part of what Developers have to do when faced with such poorly worded requirements. You can also do what I'd do in real life to be sure and that would be to ask the customer what they really meant because there are two scenarios and the data might not tell you. Your customer is... the professor that assigned this work.

My best guess as to what "respective" means is what a professor has taught or is scheduled to teach and that info is found in the "bridge" table called "Schedule" but it's still a "guess" because there are still two possibilities here.

Either way, the Department table doesn't need to be involved here.

This reply was modified 2 years, 11 months ago by Jeff Moden. Reason: Rewrote the post because of an ambiguity in the word "respective"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

Sorry... I made a mistake and had to rewrite this post.

Please see the post above. The word "respective" needs to be clarified because there are two possible solutions depending on what "respective" means.

What you could do is write code for both scenarios with comments about how the word "respective" doesn't clarify which scenario to use. In the real world, that would be a total waste of time and you'd need to get the customer involved as to what the heck they meant before writing any code.

.

This reply was modified 2 years, 11 months ago by Jeff Moden. Reason: Rewrote the post because of an ambiguity in the word "respective"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

I just noticed timers on the jpg's with just a couple of hours left. If it were me, I'd go with interpreting the word "respective" as meaning the professor and courses relationships identified in the SCHEDULE table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Select and Distinct

Attachments: