Basic SQL structure question

[CreganTur]

I'm teaching myself SQL (through learning MS Access 2003; Resource: Microsoft Access 2003 Your Visual Blueprint for Creating and Maintaining Real-world Databases book [Great book! suggested to me by Database Admin])

I've got a basic question about the correct way to structure a SQl query:

Example: single query of a single field using both Sort and Criteria.

I know that the SQL statement should look like this:

Select tblName.Field
FROM tblName
WHERE (((tblName.Field) = "criteria"))
ORDER BY tblName.Field DESC; (I know that ";" isn't alwasy neccessary)

My question is why does "ORDER BY" have to go last in this statement, even though in Design View Sort comes before Criteria?

 

[Arainach]

Because the database has to pull the records before it can sort them.

 

[Mabrito]

Yeah, it needs to know what the records are first before you can sort them. If ORDER BY was the first statement you used, it wouldnt know what to sort cause the data isnt there yet. If ORDER BY was after the FROM statemement but before the WHERE statement it will sort all the data, then the WHERE statement would check the data against the conditions in the WHERE statement. Even if SQL allowed you to do this, it would be sorting extra data it doesnt need to sort.

Here is a tip which saves alot of typing

If you are not using two or more tables where both tables have the same column name, in your SELECT statement you can just list the column name instead of tablename.columnname.

So in your SELECT statement you can get rid of the tablename and just list the column name.

Now if you were joining two tables together and a column in one table is the same name as a column in the other table you will need to do the tablename.columnname format.

 

[CreganTur]

Here is a tip which saves alot of typing

If you are not using two or more tables where both tables have the same column name, in your SELECT statement you can just list the column name instead of tablename.columnname.

So in your SELECT statement you can get rid of the tablename and just list the column name.

Now if you were joining two tables together and a column in one table is the same name as a column in the other table you will need to do the tablename.columnname format.

Thanks for the tip...the book hasn't mentioned that yet.

It's alwasy better to learn from a real person than a book, but it's the only alternative I have.

And thanks all for the explination- the "grammar" of SQL makes sense now

 

[PTNL]

A very good resource that I still use to this day is the "SQL Pocket Guide" from O'Reilly books. Link:
http://www.amazon.com/SQL-Pocket-Gu...21/ref=sr_11_1?ie=UTF8&qid=1201630751&sr=11-1

Just the answers and plenty of examples. Goes into detail on differences between different database vendors if the implementations vary.

It's probably the best $10 I've spent at Border's

 

[maxedoutcc]

typing aside, if you are using joins it is a good idea to use explicit declaration for column names unless the column name is explanatory enough, i.e.. tblName_columnName

it helps in the future when you are reviewing code to remember what tables specific data is coming from.

 

[:LJ:]

It's worth remembering that Access (all versions) is definitely not the embodiment of a good relational database manager. The GUI masks all sorts of things that you should really know before diving into designing a database, with the result that it generates horrible SQL and lets people blithely create massive problems for themselves later on (database fields with spaces in? hello???).

That's not to say that it's useless, because it can be a decent prototyping tool and is often where people start in DB development. However, if you're interested in building database-backed systems, look at everything in that book from the point of view that usually only the basic concepts are correct and you may have to re-learn a lot of what's in there when you move to MySQL, SQL Server, Oracle et al.

 

[CmdrTallen]

If you are not using two or more tables where both tables have the same column name, in your SELECT statement you can just list the column name instead of tablename.columnname.

I always promote the usage of alias for table names, makes things so much easier when you need to JOIN another table later;

SELECT e.Firstname, e.Lastname, s.Lastname as Supervisor
FROM Employee e
LEFT JOIN Employee s ON (e.SupervisorID = s.EmployeeID)
LEFT JOIN Department d ON (d.DepartmentID = e.DepartmentID)
WHERE (d.DepartmentName = 'Sales')
ORDER BY e.Lastname, s.Lastname;

 

[mikeblas]

My question is why does "ORDER BY" have to go last in this statement, even though in Design View Sort comes before Criteria?

Maybe you could dig through tons of ANSI committee meeting minutes and find a specific answer. Or, perhaps the reason is that SQL was trying to be compatible with an older implementation of a related, existing language. Bottom line is that it's arbitrary. That's the way it works, and you code to it.

The guesses here about "knowing' one thing before another are completely wrong. The parser eats the whole statement for syntax, then builds a tree of the whole statement before it even tries to bind any of the fields, let alone create an execution plan.

SQL and lets people blithely create massive problems for themselves later on (database fields with spaces in? hello???).

Fields with spaces? Do you mean in the values? You can't possibly, but suggesting that column names shouldn't have spaces in them is just about as absurd. What "massive problems" does this cause? You're saying that DB2, MySQL, Oracle, and SQL Server are all bad relational platforms because they allow spaces in column names?

 

[:LJ:]

Fields with spaces? Do you mean in the values?

Yes. Of course that's what I meant.

*sigh*

You can't possibly, but suggesting that column names shouldn't have spaces in them is just about as absurd. What "massive problems" does this cause? You're saying that DB2, MySQL, Oracle, and SQL Server are all bad relational platforms because they allow spaces in column names?

No, that's not what I intended to say....in the other database platforms you mention, it's possible to create field names with spaces for those times when it's absolutely necessary (although I've yet to find a situation where it can't be avoided), yet in almost every tutorial and class I've seen in Access it seems to be positively encouraged. The massive problems exist when you've got a query with 15 joins in it, tons of criteria in the WHERE clause and you've forgotten the string delimiters around your field-name-with-a-space-in-it (which, from memory, are naturally different in Access to every other DBMS out there). You obviously disagree, and that's your prerogative, but I prefer not to make life difficult for myself if I don't have to, hence the lack of field names with spaces in.

 

[mikeblas]

You obviously disagree, and that's your prerogative, but I prefer not to make life difficult for myself if I don't have to, hence the lack of field names with spaces in.

If you don't want field names with spaces or other special characters, then don't create them. That's your problem and not a fatal flaw in the database manager you're using. Fact is, a query has to be written correctly whether there are spaces in the bound column names or not. It seems absurd to think that this is a "massive problem" compared to so many of the other things that can go wrong with a database application.

yet in almost every tutorial and class I've seen in Access it seems to be positively encouraged.

Again, this a problem of the classes and tutorials you're reading, and not the database itself. Is this the only substance to your opinion?

 

[mikeblas]

Ordering doesn't need to come at the end, by the way; an OVER clause can provide an ORDER BY over the window function it uses.

 

[:LJ:]

Again, this a problem of the classes and tutorials you're reading, and not the database itself. Is this the only substance to your opinion?

Admittedly I phrased it badly, but it was a warning based on the fact that it's not exactly a habit the development community as a whole warm to (in my experience), yet is likely to be present in many Access database books and tutorials. Hence the "be prepared to unlearn a lot of what you've learned" statement.

There are many other problems with Access, like inefficient table-locking, a tendency to corrupt snapshots if there are any writes in progress, lack of transaction support (not sure about the latest version, though), some query syntax that works fine within the Access GUI but not from any external code, performance, scalability and a 2GB ceiling (granted, this last isn't always a problem, but people always seem surprised).