cmc-csci040

cmc-csci040 copied to clipboard

Hi Mike,

For the SQL Quiz answers, is there a difference between the answer "no output" vs 0?

Also, for problem 5, why is the answer 0? If we adjusted the question to be: SELECT id FROM users WHERE age = NULL;, why does SQLFiddle still say the answer is 0 since Kristin doesn't have an age value?

Thanks! William

@wgalbreath Hey Will, I think question 5 is 0 because it's looking for an id with no username and there isn't a place in the schema where that's the case. Hope this helps!

Hi Mike,

For question 16 shown below:

UPDATE users SET username='@realdonaldtrump';
SELECT count(*) FROM messages WHERE username LIKE '@%';

I am getting an error, I believe due to this line: username TEXT NOT NULL UNIQUE, in which the error stems from the UNIQUE constraint since updating all usernames to 'realdonaldtrump' is not unique. Additionally, it seems like the query is trying to use the column username to filter the messages, but there is no username column in messages. The same column error is happening in Problem 17. Is this supposed to happen?

@wgalbreath

For the SQL Quiz answers, is there a difference between the answer "no output" vs 0?

Yes. You should only put 0 as the answer if the SELECT statement actually returns the number 0. For example, if the problem were:

UPDATE users SET age=0;
SELECT age FROM users WHERE id=1;

then the answer is 0, but if the problem is

SELECT age FROM users WHERE id=10000;

then the answer is No Output because there is no row the satisfies id=10000. @jsilva22 I've deleted your answer to this question because it was incorrect.

---

@wgalbreath

Also, for problem 5, why is the answer 0? If we adjusted the question to be: SELECT id FROM users WHERE age = NULL;, why does SQLFiddle still say the answer is 0 since Kristin doesn't have an age value?

The correct answer is No Output and NOT 0. On sqlfiddle, you should be getting output that looks something like

The query returned 0 rows

which is equivalent to No Output. If the answer was actually 0, you would get a result that looks like

id
0

---

@KaranGoel1

Eeeek!!! Everything you said is correct, and I made a mistake. This came up in 2nd section, and I promised to get it fixed and reuploaded, but I totally forgot. I'm very sorry. Corrected versions of the problems have been uploaded. Thank you for pointing this out.

The corrected problem 16 is:

UPDATE users SET password='@realdonaldtrump';
SELECT count(*) FROM users WHERE password LIKE '@%';

and problem 17 is:

UPDATE users SET username='@realdonaldtrump' WHERE id=1;
SELECT count(*) FROM users WHERE username LIKE '@%';

Also, for problem 5, why is the answer 0? If we adjusted the question to be: SELECT id FROM users WHERE age = NULL;, why does SQLFiddle still say the answer is 0 since Kristin doesn't have an age value?

@wgalbreath I think that would still return no output because for NULL we need to use: is NULL instead of = NULL. So it would work if the line was:

SELECT id FROM users WHERE age is NULL