There are a couple minor issues with it:
"SELECT OrderDate, Count(OrderID) AS TotalForDate, Freight
FROM Orders
GROUP BY OrderDate"
You mention that it doesn't really make sense to include a column such as Freight in this query. Not only does it not make sense, but the above query won't execute - it's invalid syntax. When using aggregate functions in a query, all fields must either be an aggregate function, or included in the group by clause, or the query won't run.
Also, when discussing WHERE vs. HAVING, you state "WHERE limits the data returned by the SELECT clause; therefore, a GROUP BY is inconsequential."
That couldn't be more untrue. Looking at your original query of number of orders grouped by date, a where clause could be used in conjunction with a group by clause to return all the number of orders, grouped by date, for a specific date range (specified in the WHERE clause). Sure, you might be able to achieve the same thing using HAVING, but HAVING is nowhere near as efficient as WHERE on most database engines (including SQL Server, using TRANSACT-SQL), and you'll take a noticeable performance hit, especially on larger tables.
Overall, though, a lot of good pointers for people starting out with SQL.
Discussion on:
View:
Show:
there's some handy info in there..
I'm unsure of your target audience though..was it meant for a "User" using Access ?
I'm not so sure about this though:
" It?s everywhere, it?s easy to learn, and SQL solutions are simple to implement."
I've worked on/with some serious SQL solutions that were neither easy or simple..
I'm unsure of your target audience though..was it meant for a "User" using Access ?
I'm not so sure about this though:
" It?s everywhere, it?s easy to learn, and SQL solutions are simple to implement."
I've worked on/with some serious SQL solutions that were neither easy or simple..
I cannot express how important that is. If you are doing this from a LAMP(or similar) or a program on the client, always clean your sql inputs! users don't know that certain characters are bad and there are difinately disasters that can arise. And if this is a webapp on the internet, this is doubly so. SANITIZE YOUR SQL INPUTS!
http://xkcd.com/327/
http://xkcd.com/327/
Group By doesn't elimiate nulls was a bit iffy, almost as though it should...
Getting your head round nullability is one of the key things you have to do to get round SQL.
And why would anyone think select into was a copy????
I think it would have been polite to explain that SQl in access is quite non-standard in places but that you can use widely applicable sql in it. Relying on things like distinctrow is a mistake, always try to get to compatible sql if you can. Not saying don't use it, but know that bit won't work if you port away from your current dbms.
Getting your head round nullability is one of the key things you have to do to get round SQL.
And why would anyone think select into was a copy????
I think it would have been polite to explain that SQl in access is quite non-standard in places but that you can use widely applicable sql in it. Relying on things like distinctrow is a mistake, always try to get to compatible sql if you can. Not saying don't use it, but know that bit won't work if you port away from your current dbms.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
