Welcome to the Treehouse Community
Looking to learn something new?
How "Smart" are SQL Database programs?
The more I learn about SQL the more I start to think that your data base program's SQL interpretation is pretty "dumb" in regards to it can't make inferences or understand if the order of the information provided is off (like I put LIMIT before ORDER BY and that produced an error). However, there seems to be some exceptions where it appears like SQL can be inferred, like I just watched a video from Andrew where he mentioned that you can just state the number of rows in a query and it'll be inferred that you mean to OFFSET rows. I hope I'm making sense here.... Am I completely off in my understanding of how SQL is interpreted? Do different DB programs interpret SQL differently? Is there a logic to how SQL is inferred/interpreted or is it somewhat random, like placement of the same information can't be inferred/understood by SQL but other information can.
Steven Parker220,450 Points
The syntax used by a database is typically very rigid, though there may be options allowed in the syntax definition that make it possible to do the same things in different ways. Your mention of OFFSET in Sqlite is an example. The syntax for the LIMIT keyword allow it to be followed by one argument, or two separated by commas. In the two-argument version, the first argument performs the same function as the argument to an OFFSET keyword. But this is according to the definition of the syntax for LIMIT. You can't just "state the number of rows" anywhere in a query and expect it to be interpreted as a row offset.
The detailed documentation for each database will include the syntax rules for constructing queries. These rules can be quite involved and are often indicated with diagrams. For example, here's the SELECT statement syntax rules for ORACLE:
While there are a lot of options, there's nothing very "smart" about the interpretation. You either follow the rules or your query generates an error.
And knowing is half the battle!