Couple problems on SQL - 244 and 245

Friends, Hi! Here are two first problems on SQL (and feel free to suggest more, since they are often encountered in interviews etc).

Introducing SQL

Aggregations in SQL

There easily could be flaws so feel free to tell about everything which looks suspicious to you.

Sorry for in-site sql runner is quite primitive, perhaps we'll decide to improve it in future.

P.S. I feel that though logically more complex, the second problem may be easier than the first, sorry for that.

Are we supposed to use only 1 select?

yep, honestly I'm not sure how are you going to combine two selects :) though you can use union probably if you want two.

thanks for hint, I'll add the note to problem statement.

on a single select statement

oops, sorry for this - I'll try to check what may be wrong with it and come back soon

UPD curious thing - it is substring function. it is not recognized by SQLite on server but seems to be ok for SQLite in the web-page. I guess, replace it with substr.

I shall try appending error text if any to error message.

Hi Friend!

It seems that sqlite version used on server for checking solutions is somewhat old:

select sqlite_version()     --  yields 3.36 in browser but 3.6.20 when submitted

I shall see what could be done about this, sorry. INSTR seemingly was added in 3.7.x - unlucky for us.

However unless you need a position of substring, you'd better use like '%bla%' instead!

but I'm new to SQLite

Me too :) but we haven't too much options for running sql safely on server side, so sorry for inconvenience!

The result of SQLite uses a vertical bar

Generally you shouldn't care about this. If you submit correct query, then the execution script should take care about parsing result etc (I don't remember exact details but you'll see if something is wrong in the answer when task is failed).

Also I dare to add that, unintentionally, first task on SQL is more complicated than second, so you may want try them in reverse order.

according to problem statement you need to concatenate severa fields using underscores, seemingly you missed that part.

As for italics - this is caused by markdown format used for messages at forum... I fixed the message for you :)

That's all right with || you just forget to use it between every parts being concatenated :)

select upper(first_name) || '_' || upper(last_name) ...

Not sure why you've seen error about column (or perhaps it was from different query?

as a side note you can use upper on the whole result rather than on every part.

Alexandr, Hi! Glad to hear from you!

You are right, I missed that numbers could be equal... Added country name into sorting (and added this to problem statement). Hopefully it works... Thank you!

Sam, Hi!

As discussed, SQL has many dialects which extend standard set of features. Regretfully they do it in different ways and are not compatible. Exercises use SQLite dialect. Your solution is not compatible with SQLite - it doesn't run in SQLfiddle (if you select any of two SQLite implementation there instead of default MySQL).

Moreover there is a button SQLite under solution box on this site itself, so you can try running your code before submitting. Error message "probably syntax error" is not much clarifying, of course, but it is easy to figure out what is wrong by removing parts of query step by step.

In your case it seems at least IF is not a part of SQLite dialect. Probably CONCAT too. Standard (sql-92) suggests CASE and || operator instead.

I'm sorry for this, but it is the lamentable situation in SQL world, often met in industrial practice - so I feel it is ok that exercises somewhat prepare us to it :)

(though I should have a look whether error message could be improved somehow)

Hi Esteban!

Thanks for contacting me, but please kindly note that generally "Summon Admin" button is for reporting issues, suggesting features etc - but not for "help me solve this task" :) Anyway I don't remember all the problems well myself :)

Now to your question. Honestly, you'd better try to explain yourself a bit more careful in future :) E.g. mention which exactly task you are speaking about, what you did and which issue you have, like this:

You are tryin to solve "Introducing SQL" (right?) and have written some code in SQL, which works almost well (good!)

The issue is that your code returns people who have exactly three names (first, middle, last), but does not return people who have only two names (without middle name). So for table with two persons:

insert into users values ('William', 'Jefferson', 'Clinton', null);
insert into users values ('Abraham', null, 'Lincoln', null);

your code returns only WILLIAM_J_CLINTON but misses ABRAHAM_LINCOLN. According to problem statement and example this is not correct.

Check your code and think how it works, try to identify which condition filters out the second name. Try removing your CASE... temporarily and select just first_name AS plaque_name.

I think I see how to fix it but I'm reluctant to tell you. You'll gain much more experience if you find it yourself (I believe you can! it seems easy - just spend bit more time on debugging).

You know, to learn programming means not only to write code (even school kids write code nowadays) - but to be able to find mistakes in the code and fix them - i.e. to debug - that is what makes you professional, that is why employers will pay you big wages in future :)