Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

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

Posted by A X
A X
12,842 Points

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
229,644 Points
Steven Parker
Steven Parker
229,644 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.