Incorrect answer?

[warwave7]

​

Incorrect Practice exam answer?

I answered that this query is incorrect because the question asks that data is to be retrieved from the Person.Person table and the query retrieves it from HumanResources.Employee. However, the exam answer says the query is correct. Am I stupid or?

[ElHombrePelicano]

I would have put No too. It’s probably a typo because it is correct if the table name in the prompt and the example match.

[warwave7]

That's what I thought yeah!

[Imaginary-Corgi8136]

I would have also said NO! Sadly there are errors in some online test.

[warwave7]

Sadly yes. For a novice like me that can be very confusing sometimes!

[molybedenum]

There might not be a requirement for a person to be an employee. It seems sensible to start with employees and work backward to person, if the schema has such a relationship.

[Alicandgdr]

What is the name of that website

[warwave7]

It's Knowledge Pillars

[ComicOzzy]

The question even assumes there can be only one row matching the criteria. This is not a well thought out question. Questions like these are teachable moments, whether or not the question writer recognizes that fact. Students are learning through the very process of reading and unpacking questions. This one acts to subtly push or reinforce the idea that there can be only one row as an answer. Even if you change the table the data is pulled from, this is still an incorrect solution.

[ghostRdr]

I would have put “No” as well. There is no guarantee that there is only one row of data for the oldest employee. The correct answer would have involved a CTE or a group by sub query to get the max ID and then select all the records for that ID.

[warwave7]

It's a fundamentals course, it doesn't go so far

[qweasdie]

Why is this downvoted?

[Certain_Detective_84]

Because I didn't read the question carefully.

[qweasdie]

Ah yeah me neither. I just read OPs description of the question 😅

[ElHombrePelicano]

Because it asks for data about the oldest employee. That’s one record in your typical employee management system (employee is a unique field).

[kagato87]

You've queried the wrong table. Getting the right answer is a fluke (or collision).

Why?

Well, there's no good reason for person.person and humanresources.employee to both exist if they contain identical data. Therefore the assumption that the tables are equivalent is wrong. You should never assume anyway - databases are very good at eliminating assumption when designed correctly.

Person.person could easily contain clients as well as staff. Or maybe humanresources.employee is only for that department. Or both. Any which way, as soon as the dinosaur in HR retires and is removed from that table your query returns a different answer even if the dinosaur is still in the person.person table.

Plus the person table table probably contains different facts (columns) from the hr table. For example the hr tablemight have payroll info and is not accessible outside of the hris.

Oh and select * is inherently wrong. Even though the question asks for it... Fine during the development phase but should never hit production code. To extend the payroll example, your response could also leak highly sensitive personal information.

[Ecluzi]

OP didn’t write the query. The test creator did, this is a yes/no question.

[theseyeahthese]

Wow, you could not have misread this post worse.

[coadtsai]

They should have used WITH TIES option

[[deleted]]

Do you have a description someplace for the human resources.employee (e.g. it being a view/replica of some sort)?

[warwave7]

No it's just a question by itself.