SQL Concepts.

Introduction

Structured Query Language, commonly abbreviated to SQL and pronounced as sequel, is not a conventional computer programming language in the normal sense of the phrase. With SQL you don't write applications, utilities, batch processes, GUI interfaces or any of the other types of program for which you'd use languages such as Visual Basic, C++, Java etc. Instead SQL is a language used exclusively to create, manipulate and interrogate databases. SQL is about data and results, each SQL statement returns a result, whether that result be a query, an update to a record or the creation of a database table.

The purpose of this tutorial is to give an introduction to the key concepts of SQL, and to provide sample code illustrating the use of the most common and useful of the SQL commands. The tutorial is structured around the building of a simple database of software and network users.

Relational Databases

Before looking at SQL itself it's worth looking more closely at what is meant by a database. Although some people talk about SQL databases, there is in fact no such thing. SQL itself makes no references to the underlying databases which it can access, which means that it is possible to have a SQL engine which can address relational databases, non-relational (flat) databases, even spreadsheets. However, SQL is most often used to address a relational database, which is what some people refer to as a SQL database.

The relational model of a database was first defined by Dr E F Codd in 1970, working at the IBM Research Labs at San Jose, and it describes a way of structuring data which is mathematically consistent and abstracted away from the physical implementation of the database.

The key concept of the relational model is that data sits in tables, and that data elements within different tables can be related in some way to provide meaningful information rather than just lists of data. Each table in the database has a number of fields which define the content and a number of instances of that field - think of the fields as columns in a table and the instances as rows. For example if we had a table of LAN users, then it might have fields of first name, surname and userID. The rows in such a table would contain the values which define each instance of user, for example the entry to define user John Smith might be: John, Smith, JSmith.

A single table of LAN users does not provide very much, even though it is more useful than no table at all. However the user table becomes immediately more useful when there are other tables to which it can be linked dynamically. Imagine that there is also a table of software products, with fields of Product, Version, User Licence and Product Code. Again this is useful information, especially for inventory purposes, but its value is magnified when there is a relationship between the user table and the software table. For example a link between these tables could be used to record which users are licensed for which software products. From this it would be possible to find out how many users are licensed to use product X, or which products user Y is using. Add fields of department to the user table, for example, and it's possible to derive the relationship between departmental software requirements and the software that is licensed and so on.

Obviously there is much more to the relational model then this, but as far as SQL is concerned it is the concept of tables, fields and relationships which is important.

Designing A Database

Finally, we're almost ready to start looking at some SQL code. The first place to start is in the creation of our sample database. In every kind of database project, the first step is to design the database on paper rather than jumping straight in with the code. One of the aims of this example database is to record the software that is installed on a network and to link this to users and to their departments. Additionally, building on the inventory side of things, it will also keep track of the PC hardware we have installed.

Based on these requirements four tables are required:

• User - to record users
• Software - table for the installed software products
• UserSoftware - to record the Software used by each user (more on why we need this table later)
• PC - table to record the PC details of each user

Designing each table is a fairly straightforward process of deciding what information is required for each element in the table. For the User we obviously need First Name, Last Name and Department, but we have to beware of having two users with the same name, so we need to be able to differentiate between them. One way to do this would be to use some kind of unique UserID, an email address or network login, for example. An alternative would be to either create a unique code for each user, or else to use some pre-existing unique identifier, such as an ID from a payroll application. In this example a field called EmpNo will be used to contain this unique identifier, which we shall also assume is an integer rather than a mix of alphanumerics. Finally, we want to record the type of PC for each user, so we'll add a field called PCType. Our User table therefore looks like this:

• FirstName - First Name - a text string
• LastName - Last Name - a text string
• UserID - Network User ID - a text string
• Dept - Department - a text string
• EmpNo - Employee Number from Payroll system - an integer
• PCType - Unique identifier for type of PC - a text string

The Software table has the following fields:

• ProductName - Name of Product - a text string
• Vers - Product Version - a text string
• ProductID - Unique identifier for product - a text string
• Licenses - Number of users licensed - an integer
• DateOfPurchase - Purchase data from invoicing system - a date
• Cost - PPersecutors of product -a currency code

Our table of software used by each user we'll call UserSoftware. In the case of the User table a code for this was included for the PCType, why not do the same with the Software? Because each user is likely to have many pieces of software, and each piece of software is likely to have many different users. It therefore makes sense to create a separate table to record this many-to-many relationship. For this simple table we need fields only for EmpNo - to uniquely identify the user - and ProductID to uniquely identify the software.

Our final table stores the data for the different PCs, and provides the value to enter into the PCType field of the User table.

• PCTypeName - a text string
• Processor - CPU Processor - a text string
• OpSys - Operating system name - a text string
• RAM - Mb of RAM - an integer
• Disk - Gb of hard disk - an floating point number
• ScreenSize - Screen size in inches - a text string
• DateOfPurchase - Purchase date from invoicing system - a date code
• Cost - Cost of purchase -a currency code

A Word About Datatypes

Many kinds of data can be stored in a database, from simple text to various types of number to Boolean flags to binary objects and graphics. This is one area where there are differences between different products. Not just in the formats of data that can be stored, but also in how these formats are named. Boolean fields, for example, are called Yes/No fields in Microsoft Access, and BOOLEAN in the later releases of MySQL. This is one of the areas where you have to refer to the documentation of whatever database platform you are using. In this tutorial datatypes (or column types as they are also known), will conform as closely as possible to those which are most commonly supported on the majority of platforms.

Creating a Database

Many database systems have graphical interfaces which allow developers (and users) to create, modify and otherwise interact with the underlying database management system (DBMS). However, for the purposes of this tutorial all interactions with the DBMS will be via SQL commands rather than via menus, wizards or any of the other tools which sit between the developer and the database. This is, after all, a SQL tutorial…It is assumed, therefore, that you have access to a DBMS system that allows the use of SQL commands directly.

SQL commands follow a number of basic rules. SQL keywords are not normally case sensitive, though this in this tutorial all commands (SELECT, UPDATE etc) are upper-cased. Variable and parameter names are displayed here as lower-case. New-line characters are ignored in SQL, so a command may be all on one line or broken up across a number of lines for the sake of clarity. Many DBMS systems expect to have SQL commands terminated with a semi-colon character, a practice that is followed in this tutorial. Finally, although there is a SQL standard, actual implementaions vary by vendor/system, so if in doubt always refer to the documentation that comes with your DBMS.

Creating a database is remarkably straightforward. The SQL command is just:

CREATE DATABASE dbname;

In this example we'll call the database AssetReg, so the command is:

CREATE DATABASE AssetReg;

Once the database is created it, is possible to start implementing the design sketched out previously.

Creating Tables With SQL

Having created the database it's time to use some SQL to create the tables required by the design. Note that all SQL keywords are shown in upper case, variable names in a mixture of upper and lower case.

The SQL statement to create a table has the basic form:

CREATE TABLE name( col1 datatype, col2 datatype, …);

So, to create our User table we enter the following command:

CREATE TABLE User (FirstName TEXT, LastName TEXT, UserID TEXT, Dept TEXT, 

EmpNo INTEGER, PCType TEXT );

The TEXT datatype, supported by many of the most common DBMS, specifies a string of characters of any length. In practice there is often a default string length which varies by product. In some DBMS TEXT is not supported, and instead a specific string length has to be declared. Fixed length strings are often called CHAR(x), VCHAR(x) or VARCHAR(x), where x is the string length. In the case of INTEGER there are often multiple flavours of integer available. Remembering that larger integers require more bytes for data storage, the choice of int size is usually a design decision that ought to be made up front.

The commands to create the other tables are:

CREATE TABLE Software (ProductName VARCHAR(15), Vers VARCHAR(10), ProductID VARCHAR(10), 

Licenses INTEGER, DateOfPurchase DATE, Cost CURRENCY);

CURRENCY is another datatype that varies by vendor/product. Consult your documentation. Where an explicit CURRENCY type is not available, there is often a DECIMAL type which can be used as a replacement.

CREATE TABLE UserSoftware (EmpNo INTEGER, ProductID VARCHAR(10));

CREATE TABLE PC (PCTypeName VARCHAR(20), Processor VARCHAR(20), OpSys VARCHAR(10), 

RAM INTEGER, Disk FLOAT, ScreenSize VARCHAR(10), DateOfPurchase DATE, Cost CURRENCY);

FLOAT contains floating point numbers, a DOUBLE type often also exists for higher precision floating point numbers. DATE also comes in various flavours and formats.

There is more to the CREATE TABLE command, just as there is more to database design then we have allowed for in this example. Principally we have ignored any considerations of indexing our tables or of any kinds of constraints or data validation rules. For example you would normally want to ensure that the EmpNo field in the User is unique, that is, no two users should have the same EmpNo. Additionally, you would normally decide in advance the size of various fields, especially for text strings. SQL can code for all of these design considerations, though the syntax is likely to vary between implementations to a greater extent than the basic CREATE TABLE command.

As an example let's refine our User design so that the first name and surname fields can be no longer than 20 characters each; the userID entry can be no longer than 12 characters and that the UserID and EmpNo fields should be unique (which is what the UNIQUE keyword does for us). If you have already executed the original CREATE TABLE command your database will already contain a table called User, so let's get rid of that using the DROP command:

DROP TABLE User;

And now we'll recreate the User table we'll use throughout the rest of this tutorial:

CREATE TABLE User (FirstName VARCHAR (20), LastName VARCHAR (20), 

UserID VARCHAR(12) UNIQUE, Dept VARCHAR(20), EmpNo INTEGER UNIQUE, PCType VARCHAR(20);

Once a table is created it's structure is not necessarily fixed in stone. In time requirements change and the structure of the database is likely to evolve to match these. SQL can be used to change the structure of a table, so, for example, if we need to add a new field to our User table to tell us if the user has Internet access, then we can execute an SQL ALTER TABLE command as shown below:

ALTER TABLE User ADD COLUMN Internet BOOLEAN;

To delete a column the ADD keyword is replaced with DROP, so to delete the field we have just added the SQL is:

ALTER TABLE User DROP COLUMN Internet;

Additionally you can use the ADD and DROP commands to add or delete tables, constraints and table indexes.

Inserting Data

Having now built the structure of the database it is time to populate the tables with some data. In the vast majority of desktop database applications data entry is performed via a user interface built around some kind of GUI form. The form gives a representation of the information required for the application, rather than providing a simple mapping onto the tables. So, in this sample application you would imagine a form with text boxes for the user details, drop-down lists to select from the PC table, drop-down selection of the software packages etc. In such a situation the database user is shielded both from the underlying structure of the database and from the SQL which may be used to enter data into it. However we are going to use the SQL directly to populate the tables so that we can move on to the next stage of learning SQL.

The command to add new records to a table (usually referred to as an append query), is:

INSERT INTO target [(field1[, field2[, ...]])]

VALUES (value1[, value2[, ...]);

So, to add a User record for user Jim Jones, we would issue the following INSERT query:

INSERT INTO User (FirstName, LastName, UserID, Dept, EmpNo, PCType)

VALUES ("Jim", "Jones", "Jjones","Finance", 9, "DellDimR450");

Obviously populating a database by issuing such a series of SQL commands is both tedious and prone to error, which is another reason why database applications have front-ends. Even without a specifically designed front-end, many database systems - including MS Access - allow data entry direct into tables via a spreadsheet-like interface.

The INSERT command can also be used to copy data from one table into another. For example, if an organisation initially employs people on a 3-month trial period before making an offer for a permanent position, then it might want to store their details in a separate table. Let's call this table NewUsers and give it the same structure as the User, but with the addition of a new column called StartDate. If we also assume that at the end of the three-month trial period those people not offered a permanent position are removed from the table, leaving only those users who will become permanent employees, then we need to transfer the latter from the NewUsers table to the User. One way would be to re-key all the details into the User table, but this can lead to keying errors (this is obviously a bit of a contrived example, but it illustrates the point). A more elegant solution is to use an INSERT query to automate the task for us.

The SQL query to perform this is:

INSERT INTO User ( FirstName, LastName, UserID, Dept, EmpNo, PCType, Internet )

SELECT FirstName, LastName, UserID, Dept, EmpNo, PCType, Internet

FROM NewUsers;

Updating Data

The INSERT command is used to add records to a table, but what if you need to make an amendment to a particular record? In this case the SQL command to perform updates is the UPDATE command, with syntax:

UPDATE table

SET newvalue

WHERE criteria;

For example, let's assume that we want to move user Jim Jones from the Finance department to Marketing. Our SQL statement would then be:

UPDATE User

SET Dept="Marketing"

WHERE EmpNo=9;

Notice that we used the EmpNo field to set the criteria because we know it is unique. If we'd used another field, for example LastName, we might have accidentally updated the records for any other user with the same surname.

The UPDATE command can be used for more than just changing a single field or record at a time. The SET keyword can be used to set new values for a number of different fields, so we could have moved Jim Jones from Finance to marketing and changed the PCType as well in the same statement (SET Dept="Marketing", PCType="PrettyPC"). Or if all of the Finance department were suddenly granted Internet access then we could have issued the following SQL query:

UPDATE User

SET Internet=TRUE

WHERE Dept="Finance";

You can also use the SET keyword to perform arithmetical or logical operations on the values. For example if you have a table of salaries and you want to give everybody a 10% increase you can issue the following command:

UPDATE PayRoll

SET Salary=Salary * 1.1;

Deleting Data

Now that we know how to add new records and to update existing records it only remains to learn how to delete records before we move on to look at how we search through and collate data. As you would expect SQL provides a simple command to delete complete records. The syntax of the command is:

DELETE [table.*]

FROM table

WHERE criteria;

Let's assume we have a user record for John Doe, (with an employee number of 99), which we want to remove from our User we could issue the following query:

DELETE *

FROM User

WHERE EmpNo=99;

In practice delete operations are not handled by manually keying in SQL queries, but are likely to be generated from a front end system which will handle warnings and add safe-guards against accidental deletion of records. If you bear in mind that a single delete query can delete thousands of records in a single statement then you'll appreciate both the power and simplicity of SQL but also the dangers inherent in allowing users raw access to it.

Very often you would not want to issue DELETE commands on a single table in isolation. For example, if we delete one of the software packages in the Software table we may be left with a record in the UserSoftware table which refers to it (this record would be termed an orphan record). Where such relationships between tables occur then the delete operation needs to delete records in both tables at once. Some database systems, including MS Access, allow relationships between tables to be established including setting a cascading delete option, which means that deleting a record in one table will cause the delete of matching records in the linked tables. In such a case you need only issue the DELETE (or UPDATE) command to the primary table (in our case Software), and the deletion or change will ripple through to the linked tables (the UserSoftware table in this example).

Note that the DELETE query will delete an entire record or group of records. If you want to delete a single field or group of fields without destroying that record then use an UPDATE query and set the fields to Null to over-write the data that needs deleting. It is also worth noting that the DELETE query does not do anything to the structure of the table itself, it deletes data only. To delete a table, or part of a table, then you have to use the DROP clause of an ALTER TABLE query.

Populating The Sample Database

Rather than list a long sequence of SQL commands to populate the tables used for this article, the data is listed below and it will be referred to later on in this article. Please note that only those fields which will be of interest have been listed, for example the ScreenSize and DateOfPurchase fields in the PC table have not been included. Oh, and note the ancient technology this company is using, if you work for this type of organisation no wonder you're trawling the web looking for a free tutorial…

PC table

PCTypeName	Processor	OpSys	RAM	Disk
ASTAscP	Pentium 2	Win95	32	4.3
ASTAscC	Pentium 2	Win95	64	1.3
PilotDeskTop	Pentium Pro	Win98	64	4.3
DellDimR450	Pentium 2	Win98	128	12.9

User

FirstName	LastName	UserID	Dept	EmpNo	PCType
John	Smith	Jsmith	Marketing	23	ASTAscP
Mary	Jones	Mjones	Marketing	69	ASTAscP
Chloe	Feltham	Cfeltham	Finance	45	ASTAscC
Bharat	Patel	Bpatel	Development	12	PilotDeskTop
Terry	Jones	Tjones	Development	15	ASTAscC
Jim	Jones	Jjones	Development	9	DellDimR450
John	Smith	Jsmith2	Finance	78	DellDimR450

Software table

ProductName	Vers	ProductID	Licenses
MySQL	6	MySQL6	25
MySQL	5	MySQL5	25
OpenOffice	1	OpenOffice	10
J2SE1.3	5	J2SE1.3	10
VB6	6	VB6	10
PaintShop Pro	3	PSP3	1
MS Word	7	MSWord7	25

UserSoftware table

EmpNo	ProductID
69	MSWord7
69	OpenOffice
69	MySQL5
12	VB6
15	VB6
15	PSP3
9	VB6
9	MySQL6

Querying The Database

The Basic SELECT command

Having designed, built and populated our database with some sample data, we can now move on to what is considered the heart of SQL - the SELECT statement. This is the command that queries the database and which provides real value to any database.

Although the SQL SELECT statement can become extremely complex, the basic syntax is relatively straightforward:

SELECT columns

FROM table(s)

WHERE criteria;

So, to query the User to produce a list of surnames and employee numbers we could issue the command:

SELECT LastName, EmpNo

FROM User;

If we wanted to look at the entire table we could have used an asterisk as a wildcard for the entire table:

SELECT *

FROM User;

Or else you might want to catenate the LastName and FirstName fields to produce a single column with entries such as 'Jones, Terry', 'Patel, Bharat' and so on. This too is possible using the simplest of SELECT commands, though we have to create an alias column to contain the result - which in this instance we'll call FullName. SQL can then use the AS keyword to assign the result of an expression to the alias. In MSAccess the ampersand character is used to catenate the strings:

SELECT LastName & "," & FirstName AS FullName 

FROM User;

In contrast MySQL uses the CONCAT keyword to piece the strings together:

SELECT CONCAT(FirstName,",",LastName) AS FullName 

FROM User;

You can even add fixed text or expressions to SELECT queries to give results which are immediately readable, as shown in the query and results below:

SELECT FirstName & " " & Lastname & " works in the " & Dept & " department" AS Job

FROM User;

Results of query above

John Smith works in the Marketing department

Mary Jones works in the Marketing department

Chloe Feltham works in the Finance department

Bharat Patel works in the Development department

Terry Jones works in the Development department

Jim Jones works in the Marketing department

John Smith works in the Finance department

Now, what happens if we issue a simple query such as the following?

SELECT LastName

FROM User;

SQL will return a simple listing of the last names in the User, and, as you can see by looking at the data in our table, it will contain duplicates as there are three lots of Jones's listed in our database. If we want a listing of unique names then how can we generate it? SQL includes a number of 'predicates', which specifies which records the query is to return. In all of our examples so far we have assumed that we want all of the records to be returned, which is equivalent to including the ALL predicate with our statements.

If we want a listing of unique names then we can use the DISTINCT predicate to exclude those records which are not unique based on the SELECTed fields in the query. In other words the query becomes:

SELECT DISTINCT LastName

FROM User;

In other cases we may only want to exclude records where the entire record is duplicated, not just a given field or set of fields. To do this we use the DISTINCTROW predicate. In our database there are no records in the User which are duplicates, so using DISTINCTROW will return all the records in the table.

Where we are dealing with numeric data we may only be interested in a subset based on the position of the results, for example the top 10 records, or the bottom 20% and so on. The predicate for this is TOP, and differs from the previous ones in that it depends on your query to order the results so that it can pick out the relevant records.

The WHERE Clause

So far all the queries we have looked at operate on the complete contents of a table. What if you are only interested in a particular sub-set of a table? The WHERE keyword is used to provide a search mechanism which only produces records which meet a given search criterion. For example if we are only interested in our users in the Development department, we would modify one of our earlier commands as follows:

SELECT *

FROM User

WHERE Dept="Development";

We might equally have wanted to see a list of employees who do not work in development. In this case our WHERE statement can be amended to:

WHERE Dept<>"Development";

Multiple WHERE statements can be created to fine tune our queries and select very precise sub-sets of our data. For example, if we wanted to look at those employees not in development but who have Internet access, we can code the following query:

SELECT *

FROM User

WHERE Dept<>"Development" And PCType="DellDimR450";

The WHERE command can also be used with arithmetic and logical operators. For example in those systems that support a LIKE keyword, if we wanted all those employees whose surname begins with the letter J (which you might want when producing a company contact list):

SELECT *

FROM User

WHERE LastName Like "J*";

Or if your system includes a BETWEEN keyword you can select names in the group A-M:

WHERE LastName Between "A*" And "M*";

For a full list of legal WHERE criteria and keywords refer to the documentation for your particular implementation of SQL.

Sorting Query Results

Where a query might produce many records it is often useful to sort the result by a given field. SQL uses the ORDER BY keyword to do this. If we wanted to order our User by LastName, we would create the following query:

SELECT *

FROM User

ORDER BY LastName;

Or, if we wanted the table sorted by LastName within Department, the ORDER BY clause would become: ORDER BY Dept, LastName

You can also chose to have items sorted in ascending or descending order, as shown by our last ORDER BY example:

SELECT *

FROM User

Order By Dept DESC, LastName ASC;

Joining Data

What if we want to find out how much disk space the users in the development department have? Here we have data from two different tables to look and it is here that the power of the relational model becomes apparent. At this point we need to create a relationship between the User and the PC table, and this relationship is called a JOIN in SQL/relational database terminology. There are a number of different types of JOIN available, depending on the what it is you need to achieve.

The most common form of join is called an INNER JOIN, and is used to match records between tables where there are matching values in a field common to both tables. For example we have the PCType field in the User and the PCTypeName field in the PC table, and so we would want to perform an INNER JOIN on these fields. The syntax to carry this out is fairly simple:

FROM table1 INNER JOIN table2 ON table1.field1 operator table2.field2;

The operator is usually a simple '=', but other operators can be used, such as '<>', '>', '<' and so on. For the purposes of our example we want to join the tables where the User.PCType field is equal to the PC.PCTypeName field in order to extract the PC.Disk value. Our query would look as follows:

SELECT User.LastName, User.FirstName, User.PCType, PC.Disk

FROM User INNER JOIN PC ON User.PCType = PC.PCTypeName

WHERE User.Dept="Development";

What if we want to link users to software to produce a list of software packages for each user? The UserSoftware table lists the EmpNo and the ProductID code, the first of these links to the User and the second to the Software table, thus requiring two joins:

SELECT DISTINCT User.FirstName, User.LastName, Software.ProductName

FROM (UserSoftware INNER JOIN User ON UserSoftware.EmpNo = User.EmpNo) 

INNER JOIN Software ON UserSoftware.ProductID = Software.ProductID;

This generates the following result:

FirstName	LastName	ProductName
Jim	Jones	MySQL
Mary	Jones	MySQL
Mary	Jones	OpenOffice
Jim	Jones	VB6
Bharat	Patel	VB6
Terry	Jones	VB6
Terry	Jones	PaintShop Pro
Mary	Jones	MS Word

To show the PCType in the result we would also need to link in the PC table, which we can join on the field of User.PCType:

SELECT DISTINCT User.FirstName, User.LastName, Software.ProductName, PC.PCTypeName

FROM ((UserSoftware INNER JOIN User ON UserSoftware.EmpNo = User.EmpNo) 

INNER JOIN Software ON UserSoftware.ProductID = Software.ProductID) 

INNER JOIN PC ON User.PCType = PC.PCTypeName;

As you can see, the useful information available from the linked tables is far higher than it is from a simple listing of the tables themselves. By linking the different tables together we can gather together the information which is inherent in the tables but is not otherwise easily accessible.

Although the INNER JOIN is the type of join most often used, it isn't the only one. There are in fact 'outer' joins as well as an inner join. A good illustration of a useful outer join follows on from our previous examples. The table of users and software only lists those users for whom software products are recorded, but what if we want to list all of the users in our database? Clearly we would need to join the User to UserSoftware, but we would want all of the records from User listed, not just those for which there is a matching employee number. We do this using a type of outer join called a LEFT JOIN.

We can thus amend our SQL as follows:

SELECT DISTINCT User.FirstName, User.LastName, Software.ProductName

FROM (User LEFT JOIN UserSoftware ON User.EmpNo = UserSoftware.EmpNo) 

LEFT JOIN Software ON UserSoftware.ProductID = Software.ProductID;

This generates the following table of results:

FirstName	LastName	ProductName
Jim	Jones	VB6
Jim	Jones	MySQL
John	Smith	NULL
Mary	Jones	MS Word
Mary	Jones	OpenOffice
Mary	Jones	MySQL
Chloe	Feltham	NULL
Bharat	Patel	VB6
Terry	Jones	VB6
Terry	Jones	PaintShop Pro

Note that some systems report a missing value as NULL, others (including MS Access) would simply return a blank in the above result.

And what about those software products for whom there are no users? Again we need an outer join, only this time we want to include those records from the second table in the query, which is called a RIGHT JOIN.

SELECT DISTINCT User.FirstName, User.LastName, Software.ProductID

FROM (UserSoftware LEFT JOIN User ON UserSoftware.EmpNo = User.EmpNo) 

RIGHT JOIN Software ON UserSoftware.ProductID = Software.ProductID;

Aggregates

The SQL we have looked at so far has operated on single records, selecting them based on various criteria and linking them between tables. The next step is to look at how we can aggregate records so that we can calculate sums, averages, counts and so on.

One of the simplest operations to perform is a simple count of records in a table. To do this we need only add to the simplest of SELECT queries. First we create an alias to store the result, and then we use the COUNT command to count a given field - in our case EmpNo:

SELECT DISTINCT Count(User.EmpNo) AS Total

FROM User;

What if we want something a bit more complex, say a count of employees in each department. Here we want to group like records together based on a given field, in our case Dept. SQL provides the GROUP BY clause precisely for this task. The query then looks as follows:

SELECT Count(User.EmpNo) AS Total, User.Dept

FROM User

GROUP BY User.Dept;

Which generates the following table:

Total	Dept
2	Development
2	Finance
3	Marketing

Taking an average is not much more difficult. If we want to find the average Mb of RAM for the different processor types listed in the PC table we could issue the following SQL statement:

SELECT Avg(PC.RAM) AS Memory, PC.Processor

FROM PC

GROUP BY PC.Processor;

Yielding the result:

Memory	Processor
74.66	Pentium 2
64	Pentium Pro

Other aggregate functions include Min, Max, Sum etc, depending on your particular implementation of SQL. Any WHERE cause will have been applied before the GROUP BY clause takes effect, so it is easily possible to exclude certain records from the aggregate functions. This means, however, that it is not possible to use the WHERE clause to exclude some of the aggregated results. For example if you were interested in looking at the average disk size of machines for those machines not running Windows 95, we would use the WHERE clause to exclude OpSys='Win95', and then the GROUP BY clause to group by OpSys:

SELECT Avg(PC.Disk), PC.OpSys AS Disk

FROM PC

WHERE PC.OpSys<>'Win95'

GROUP BY PC.OpSys;

If we want to apply a selection process on the outcome of the GROUP BY process we have to resort to another type of selection clause. SQL uses the HAVING clause to make selections of data after the GROUP BY process has completed. For example, if we wanted a listing of the average memory per processor type but were only interested if the average were less than or equal to 64Mb, we could code a query as follows:

SELECT DISTINCTROW PC.Processor, Avg(PC.RAM) AS RAM

FROM PC

GROUP BY PC.Processor

HAVING Avg(PC.RAM) <=64;

The HAVING clause can have multiple terms, just as the WHERE clause can, and these can be linked with the normal logical operators AND, OR etc.

Thanks to : http://www.techbookreport.com/