SQL syntax

Question

Post reply

SQL syntax

Viewing 8 posts - 31 through 37 (of 37 total)

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

vk-kirov SSCertifiable Points: 7686 More actions · Answer 1

Grant Fritchey (5/28/2010)
I would argue that while all three compile and run, none of them is "correct."

What do you think about the syntax of the following statements?

SELECT column1 * column2 FROM table1

SELECT column1 *column2 FROM table1

SELECT column1* column2 FROM table1

SELECT column1*column2 FROM table1

Probably only the first is correct, while others are "incorrect"? 🙂

webrunner SSC-Dedicated Points: 31863 More actions · Answer 2

Paul White NZ (5/29/2010)
Nice (but quite shocking!) question. I'm not at all embarrassed to say I got this one wrong.

Yes, Paul, thanks. I too got it wrong and am not ashamed to say so. It didn't occur to me that the first choice could be correct (as in, run without error), even if there would no doubt be a lot of criticism of the formatting from a readability standpoint.

Also, one quibble - well, more of a feature request; I suppose I should submit it to Steve as well: It seems to me that scoring the choice percents individually should be augmented, when checkboxes are presented as question choices, with the percentage of people who got the correct combination of selections correct. For example, in the current question, what percentage of people correctly picked all 3 boxes (versus how many overall picked box 1 vs box 2 vs box 3). That will give you an accurate count of who got the question wholly correct. It may be more trouble to program than it's worth, but it would be cool to see.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

Webrunner, what you ask for is already available:

Above the percentages for the individual options is this:

Correct answers: 62% (1099)
Incorrect answers: 38% (675)
Total attempts: 1774

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

webrunner, that's a good point. I'll add that to the list, and I like the idea.

UMG Developer SSChampion Points: 13482 More actions · Answer 5

I think I wouldn't hire anybody that wrote code like that. Using * at all is bad enough. 😉

epriddy Old Hand Points: 343 More actions · Answer 6

epriddy

Old Hand

Points: 343

June 2, 2010 at 12:47 pm

#1175510

lol! Too true! 😀

James Goodwin Hall of Fame Points: 3691 More actions · Answer 7

While I got the answer right, the explanation of the answer: "The "*" Specifies that all columns are returned, so space is not necessary in between, before, or after * in a SELECT statement" doesn't explain what is happening.

The SQL Parser is using non-alphanumeric (or, possibly, reserved) characters to determine the end of each string token. It uses non-numeric characters to determine the end of numeric tokens. Thus SELECT 1FROM <table> works, but SELECT1FROM <table> doesn't.

There was probably no decision made to allow SELECT*FROM. This is probably a result of wanting to allow mathematical formulas without forcing spaces around the operator (e.g.) field1 + 2 vs field1+2.

--

JimFive

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Paul White NZ (5/29/2010)
Grant Fritchey (5/28/2010)
I would argue that while all three compile and run, none of them is "correct."
In the same way that 2+2=5 for sufficiently large values of 2? 😛

I have to agree with Grant. What does the formal syntax definition of a select statement specify? I believe it requires whitespace between the SELECT keyword and the select list. The fact that the T-SQL parser has a wiggle (don't know whether it was an intended feature or an unintended consequence of the handling of * in its multiply operator role, so call it a "wiggle" rather than a "feature or a "bug", although I think "bug" would really be more appropriate) which allows it to correct some incorrect syntax doesn't make that syntax correct, it just makes it usable. Of course I may have misinterpreted the BNF definition of the syntax - maybe someone should check me.

Tom