HOME  |  AJAX  |  SOLUTIONS  |  TECHNOLOGIES  |  E-COMMERCE  |  ABOUT US
Fun with Queries
Structured Query Language SQL is one of the easiest languages to learn.   At the same time it is also one of the most powerful.  I personally feel that anyone who works with data should know, at the very minimun, the basics of SQL.

Even though SQL varies from database to database, the important SQL statements to get data are the same.  For the most part, you can get any data you want organized in any fasion with a simple SELECT statement.  Getting the data you want in the order you want organized they way you want, based on your given criteria can take a little bit of thought, but the syntax is still nice and simple.

If your maintaing the database, it requires a steeper learning curve.  Certainly learning to create and maintain a database will scare off most people, but everyone I know could benefit by storing their mass quantites of data in a database.

So for some of the fun stuff I have learned over the years with SQL.  All of these statements work through MySQL and should work through any of the major databases (with maybe a little language tweeking).

The distinct keyword can get you data that would otherwise take forever to find.  Say we wanted to find all of the unique accounts in our system:
SELECT DISTINCT(account_id) FROM account;
This would return all the unique accouts.  Now say we wanted to find the number of accounts in our system.
SELECT count(DISTINCT(account_id)) as account_total FROM account;
Notice here that I have used the as keyword to give the column name a nice identifier.  This becomes more important when using SQL inside of other programming languages, but will give you a nice column header in this context.

The sum function is another one of those very powerful features of SQL.  Lets say that we wanted to know the total dollar amount of all accounts spent:
SELECT sum(amount) FROM account;
This returns the amount spent from all accounts, but if we want the amount spent from a given account, we have to use slightly more complicated syntax, but once you learn it, it is quite simple:
SELECT sum(amount), account_id FROM account GROUP BY account_id;
This will return the total amount spent from each account and their respective account number.  We can modify this slightly to get some results that have much more meaningful and detailed information.
SELECT sum(amount), account_id, first_name, last_name, date_opened 
FROM account GROUP BY account_id;
This will give us quite a few details of each account and the amount spent.