Safe SQL: always be on TOP.

SQL likes being on TOP.

It's really an issue of peace-of-mind when query building.

Knowing your test query will never spin out of control.

Safe SQL always uses TOP until the last moment. When crafting a compex query one baby step at a time, TOP keeps the intermediate results short, snappy, and safe.

SELECT top 10 *... should be habitual.

Make sure you know how to use TOP in all kinds of situations.
W3C TOP

MySQL site - great resource for syntax, code examples

Mysql_logo

My preferred flavor of SQL is MySQL. Why? It comes with the web host I use most frequently. I'm not a SQL whiz. I know just enough to get by. Industrial SQL gurus - you probably have a million reasons why MySQL isn't that great, but for modest applications and on-the-side-hobby needs, MySQL is just right. It's easy to use with PHP and is ... well, priced "just right".

Also their site is a great resource of syntax and examples. For example, if you wanted to know everything you could ever do with the SELECT statement, you'll be blown away by this page.

Inner joins - relating data between tables

The SQL join is what relational DB' are all about. The syntax can either be explicit or implicit. I find the explicit notation is safer because the ON clause is a constant reminder of the join. The implicit notation uses the WHERE clause and you can more easily lose track of important relationships in complex queries. Also it's easier for others to follow explicit joins.

Sample syntax below is from wikipedia.

"SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation".

The "explicit join notation" uses the JOIN keyword to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:

SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID

The "implicit join notation" simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates (which function comparably to the join-predicates in the explicit notation).

The following example shows a query which is equivalent to the one from the previous example, but this time written using the implicit join notation:

SELECT * FROM employee, department  
WHERE employee.DepartmentID = department.DepartmentID"

GROUP BY - little gem for aggregating data

When you need to count (or sum, or aggregate) data and see the results "for each of something", then GROUP BY is probably what you want.

Note: you have to make sure that the SELECT columns appear in the GROUP BY clause - up to the last aggregator.

From W3Schools.com:

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
"

SQL Case statement: condition

This is a good tool to have in your SQL query toolbox: Sql CASE

From http://www.tizag.com/sqlTutorial/sqlcase.php

"SQL CASE is a very unique conditional statement providing if/then/else logic for any ordinary SQL command, such as SELECT or UPDATE. It then provides when-then-else functionality (WHEN this condition is met THEN do_this)."

Sample:

"USE mydatabase;

SELECT product,
      'Status' = CASE
        WHEN quantity > 0 THEN 'in stock'
        ELSE 'out of stock'
        END
FROM dbo.inventory;"