Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases Reporting with SQL Ordering, Limiting and Paging Results Paging Through Results

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.

2 Answers

Steven Parker
Steven Parker
220,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:

select syntax
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!