SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by mohammadk-1135894 / SQL syntax / Latest Posts

RE: SQL syntax

L' Eomot Inversé — Wed, 07 Jul 2010 07:05:17 GMT

[quote][b]Paul White NZ (5/29/2010)[/b][hr][quote][b]Grant Fritchey (5/28/2010)[/b][hr]I would argue that while all three compile and run, none of them is "correct."[/quote]In the same way that 2+2=5 for sufficiently large values of 2? :-P[/quote]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.

RE: SQL syntax

James Goodwin — Thu, 03 Jun 2010 08:55:16 GMT

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

RE: SQL syntax

epriddy — Wed, 02 Jun 2010 12:47:38 GMT

lol! Too true! :-D

RE: SQL syntax

UMG Developer — Tue, 01 Jun 2010 20:30:29 GMT

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

RE: SQL syntax

Steve Jones - SSC Editor — Tue, 01 Jun 2010 08:55:01 GMT

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

RE: SQL syntax

Hugo Kornelis — Tue, 01 Jun 2010 08:54:45 GMT

Webrunner, what you ask for is already available:Above the percentages for the individual options is this:[quote]Correct answers: 62% (1099)Incorrect answers: 38% (675)Total attempts: 1774[/quote]

RE: SQL syntax

webrunner — Tue, 01 Jun 2010 08:25:08 GMT

[quote][b]Paul White NZ (5/29/2010)[/b][hr]Nice (but quite shocking!) question. I'm not at all embarrassed to say I got this one wrong.[/quote]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

RE: SQL syntax

vk-kirov — Tue, 01 Jun 2010 08:19:16 GMT

[quote][b]Grant Fritchey (5/28/2010)[/b][hr]I would argue that while all three compile and run, none of them is "correct."[/quote]What do you think about the syntax of the following statements?[code="sql"]SELECT column1 * column2 FROM table1SELECT column1 *column2 FROM table1SELECT column1* column2 FROM table1SELECT column1*column2 FROM table1[/code]Probably only the first is correct, while others are "incorrect"? :-)

RE: SQL syntax

sangeethaharilal — Tue, 01 Jun 2010 07:49:12 GMT

For me all three options worked. I am using SQL 2008 express edition .

RE: SQL syntax

Paul White — Sat, 29 May 2010 06:53:31 GMT

Nice (but quite shocking!) question. I'm not at all embarrassed to say I got this one wrong.

RE: SQL syntax

Paul White — Sat, 29 May 2010 06:53:05 GMT

[quote][b]Grant Fritchey (5/28/2010)[/b][hr]I would argue that while all three compile and run, none of them is "correct."[/quote]In the same way that 2+2=5 for sufficiently large values of 2? :-P

RE: SQL syntax

Grant Fritchey — Fri, 28 May 2010 11:51:09 GMT

I would argue that while all three compile and run, none of them is "correct." If they were correct, then so would this be, and you know it's not:[code="sql"]SELECTmycolumnFROM MyTable;SELECTmycolumn FROM MyTable;SELECT mycolumnFROM MyTable;[/code]However, I'd leave the question alone. It was interesting to see weirdness in the parser.

RE: SQL syntax

KevinC. — Fri, 28 May 2010 10:36:26 GMT

I personally didn't find the question confusing. And, although the syntax definitely is NOT advisable for readability, etc. it is interesting to know how SSMS will parse some code without the white space.Thanks for the question.

RE: SQL syntax

epriddy — Fri, 28 May 2010 10:21:25 GMT

Gotcha! The spacing between Select*from doesn't matter but there must be a space after from for the table name.... THanks for the learning!

RE: SQL syntax

epriddy — Fri, 28 May 2010 10:16:01 GMT

In testing out the theory (not pasting from the examples which inherently include a space before the table name)... only the second query runs without error:--WON'T RUN: select*fromUDM.DimDate --'no space between select, *, and from select* from UDM.UDM.DimDate -- 'no space between select and *--WON'T RUN: select *fromUDM.DimDate-- 'no space between * and fromI ran this against 2005 SQL Server via Management Studio.

RE: SQL syntax

SQLRNNR — Fri, 28 May 2010 09:48:03 GMT

thanks for the Question

RE: SQL syntax

SQL Server Youngling — Fri, 28 May 2010 09:25:46 GMT

I'd have liked to see a 4th option, "none of the above". I believe that would have added to the educational opportunity.But then, why pose this question at all? Doesn't it promote harder to read code, and the memorization of what is essentially trivia?select * from table_nameI'd argue that the above statement is: easier to read, doesn't require knowledge of this trivia, works if you substitue "my_column" for the "*", and is probably far more cross platform compatible.These are meant as constructive criticisms. I do very much appreciate the learning opportunity!

RE: SQL syntax

Steve Jones - SSC Editor — Fri, 28 May 2010 08:52:13 GMT

I have removed the sentence about error messages since it was confusing.

RE: SQL syntax

ahrar.mustafa — Fri, 28 May 2010 08:45:10 GMT

All are correct

RE: SQL syntax

ken.trock — Fri, 28 May 2010 07:33:02 GMT

[quote][b]malleswarareddy_m (5/27/2010)[/b][hr]I did not understand this question.in question it shows that to select wrong sytax.but all resturns table data without any error.then i did not found the wrong answer.so i applied three answers.got my point.[/quote]He did say "select all that apply"[quote]So 1st you have to select all the statements that are correct, then guess the error statements?[/quote]The part about guessing the error messages, if there are any, are for extra credit:-DKen

RE: SQL syntax

Tom Garth — Fri, 28 May 2010 07:25:58 GMT

I'm afraid I have to join the chorus in saying the question was poorly worded.Interesting info though.

RE: SQL syntax

Mike Dougherty — Fri, 28 May 2010 07:17:58 GMT

"A crime against readability" I'll reuse that quote soon enough.

RE: SQL syntax

Daniel Bowlin — Fri, 28 May 2010 07:12:41 GMT

They may all work, but they are all a crime against readability. I make a point of white space for readability just about everywhere.

RE: SQL syntax

vk-kirov — Fri, 28 May 2010 04:36:08 GMT

[quote][b]ziangij (5/28/2010)[/b][hr]However was wondering, when SSMS executes the query, it would have to do some effort to remove spaces between the words :-)[/quote]SSMS itself does not execute queries because it is a client application. SSMS divides input text into batches, sequentially sends those batches to SQL Server, and displays received results. No spaces are added or removed while sending batches to the server. You can use SQL Server Profiler to see it.

RE: SQL syntax

ziangij — Fri, 28 May 2010 02:54:47 GMT

[quote]I've seen that too before, same like this:declare @a int set @a=1select @aHowever, select1 or select@a won't work. It seems that a keyword preceded by an integer always works. I would definitely avoid using it though, it only confuses me.[/quote]agreed, it just increases confusion. However was wondering, when SSMS executes the query, it would have to do some effort to remove spaces between the words :-)

RE: SQL syntax

Carlo Romagnano — Fri, 28 May 2010 02:28:46 GMT

[quote][code="sql"]EXEC('SELECT*FROM[sysobjects]ORDERBY1desc')[/code]In this case the "order by" clause is missingORDERBY1desc is the alias of [sysobjects]

RE: SQL syntax

Ronald H — Fri, 28 May 2010 02:18:09 GMT

[quote][b]Carlo Romagnano (5/28/2010)[/b][hr]Try this:select*from sysobjects order by 1descNo space in 1desc[/quote]I've seen that too before, same like this:declare @a int set @a=1select @aHowever, select1 or select@a won't work. It seems that a keyword preceded by an integer always works. I would definitely avoid using it though, it only confuses me.

RE: SQL syntax

J.Faehrmann — Fri, 28 May 2010 01:59:13 GMT

[code="sql"]SELECT*FROM[sysobjects]ORDER BY 1desc[/code]or even[code="sql"]EXEC('SELECT*FROM[sysobjects]ORDERBY1desc')[/code]welcome to SQL golfing - omg LOLThe second thing is especialy bad, play with the order by to see why :-D

RE: SQL syntax

Hugo Kornelis — Fri, 28 May 2010 01:55:13 GMT

I agree with others that the second sentence of the question: "Please guess error message for those stements which are wrong. (select all that apply)" gave the impression that we had to check which answers would result in an error message.Otherwise an intrigueing question about an interesting bit of information that, I hope, nobody will ever use in practice. :-) I'm hoping to see more questions from you in the future.

RE: SQL syntax

Carlo Romagnano — Fri, 28 May 2010 01:27:33 GMT

???????????????????????????????????:cool:Try this:select*from sysobjects order by 1descNo space in 1desc

RE: SQL syntax

sharath.chalamgari — Fri, 28 May 2010 00:53:06 GMT

all the query works without any errorYou could have provided the a 4th option which says that all works without any error.confusing :hehe:

RE: SQL syntax

emginet-874462 — Fri, 28 May 2010 00:42:52 GMT

Hi All the querys are correct

RE: SQL syntax

mohammadk-1135894 — Fri, 28 May 2010 00:36:41 GMT

Hi, It seems my question was little confusing. but I want to ask which of these SQL statement are right?So all of the SQL statements was right!!! so you are rightly using the sytatement "select *from table_name"Sorry for the confusion.

RE: SQL syntax

Jinesh-I — Fri, 28 May 2010 00:30:29 GMT

I have been using select *from table_name in SSMS 2005, since 2 years & it works fine.Please try once.Jinesh.

RE: SQL syntax

Henrico Bekker — Thu, 27 May 2010 23:56:08 GMT

The question was: "Which of these SQL statements is correct? Please guess error message for those stements which are wrong. (select all that apply)"So 1st you have to select all the statements that are correct, then guess the error statements??

RE: SQL syntax

mohammadk-1135894 — Thu, 27 May 2010 22:33:58 GMT

Question was "Which of these SQL statements is correct?". Since all the SQL statements are correct then you have to check all the three answers.

RE: SQL syntax

malleswarareddy_m — Thu, 27 May 2010 22:24:47 GMT

I did not understand this question.in question it shows that to select wrong sytax.but all resturns table data without any error.then i did not found the wrong answer.so i applied three answers.got my point.

SQL syntax

mohammadk-1135894 — Thu, 27 May 2010 22:20:23 GMT

Comments posted to this topic are about the item [B]SQL syntax[/B]