Defining terms:

Remember the terms we discussed: ROW, COLUMN, FIELD, TABLE, DATABASE

SQL is an interesting language. It's not a programming language like FORTRAN, BASIC, COBOL or PASCAL. In these languages, you specify how the computer deals with every individual piece of information. In other words, the order and the structure of the data is rigid and you must tell the computer not only where each bit of data is, but exactly what to do with it. But SQL is a nonprocedural language or a language where you simply tell a system what results you want. You don't have to tell it how to get it...the system already knows.

Elements of SQL.

SQL is a very logical set of commands. On a given query, you are allowed to specify the following conditions: the databases you want, the fields you want, the order of the rows, the order of the columns, whether you want data to be grouped by a particular attribute, whether you want to filter the data by a particular attribute and where you want the data to go.

IN SQL, simple queries usually follow this simple structure:

SELECT (fields)
FROM (databases)
WHERE (fields)<clause>(filter attribute)
GROUP BY (fields)
ORDER BY (fields)
HAVING (fields)

Here is an example of a simple database. We'll use this to illustrate a few points.

Database name = contributors

In general, when you write a query statement or ask the data a question, you should do the following steps;

• Select your fields using the SELECT command. This process involves deciding what your output table is going to look like, what fields you want and in what order they will be in. You should see the output table in your head and translate that information into the SELECT statement. If the use the * instead of a fieldname, the query will return all records.

SELECT lastname, firstname, amount


• In the FROM statement, you select which databases are needed to include all the fields from the SELECT statement. If you want information from two different databases, this is wh

FROM contributors


• The WHERE statement allows you to filter the information. There are many different ways you can do this. Some examples are:
• WHERE lastname='Flinchum' (query retrieves records of contributors named Flinchum)
• WHERE amount>75 (query retrieves contributions greater than $75)
• WHERE candidate='Dole' and amount>500 (retrieves contributors to Dole who gave more than $500)

• The GROUP BY function is one of the most confusing SQL statements. It aggregates the data by one attribute. Thus if I grouped by "Clinton" in the candidate field, all records relating to Clinton would be treated as one. The easiest way to see this is by looking at the examples below.

GROUP BY candidate


• The ORDER BY function allows you to sort the data in ascending or descending order either by magnitude or alphabetically.

ORDER BY amount DESCENDING


• The HAVING field is similar to the where statement but it is used when you have used the GROUP BY function and you want to filter the data

HAVING amount>500

Examples:

SELECT * FROM contributors

SELECT lastname, firstname FROM contributors

SELECT * FROM contributors WHERE amount > 75 (note: greater than 75 does not include 75)

SELECT * FROM contributors WHERE candidate ='Clinton'

SELECT lastname, firstname, amount FROM contributors ORDER BY amount

SELECT candidate FROM contributors GROUP BY candidate

When using the GROUP BY function, there are a number of aggregate functions you can use in the SELECT statement. Examples are SUM(fieldname), AVERAGE(fieldname), COUNT(fieldname), AND MEDIAN(fieldname).

• SUM(fieldname) adds the values in the field
• AVERAGE(fieldname) averages the values in a field
• COUNT(fieldname) counts the number of records in the field
• MEDIAN(fieldname). Calculates the median value

Examples of these are as follows.

SELECT candidate, SUM(amount) FROM contributors GROUP BY candidate

SELECT candidate, AVERAGE(amount) FROM contributors GROUP BY candidate