SQL - A Very Quick Guide

Standard Query Language

SQL (pronounced 'sequel') is used to define and use relational database structures. It is little more than an official notation of the methods used in Relational Databases.

Remember, every Database system has its own slightly-different version of SQL. These examples demonstrate principles rather than specifics.

SQL statements can be long but they are mostly just lists of Fields. You can place return characters in the middle of a SQL statements to make them more presentable.

We can use SQL to create and modify Tables and the Fields within them. However in practice most systems give us more user-friendly ways of doing this.

We use it most often to define new Views from those Tables by relating Fields together.

SELECT

Used to select some Fields from a Table.

E.G.
SELECT EmployeeID, EmployeeName FROM Employees

* means all fields

E.G.
SELECT * FROM Employees

WHERE

Used with SELECT to request only certain records.

E.G.
SELECT EmployeeID, EmployeeName, Salary FROM Employees WHERE ( Salary > 20000 )

Also used to define Relationships by Joining. See below.

GROUP BY

Sub-summarises the output by the field(s)

E.G.
SELECT EmployeeID, EmployeeName, Dept FROM Employees GROUP BY Dept

Various summary information can be generated.

E.G.
SELECT Dept, Sum(Salary) FROM Employees GROUP BY Dept

HAVING

Like WHERE but instead of choosing records from the table, it chooses lines from the summarised output based on summary information.

E.G.
SELECT Dept, Sum(Salary) FROM Employees GROUP BY Dept
HAVING ( Sum(Salary)>50000 )

ORDER BY

Sorts the records.

E.G.
SELECT EmployeeID, EmployeeName, Salary FROM Employees ORDER BY Salary

Joining

You can SELECT from more than one table. Use '.' to show which Fields come from which Tables.

When doing this you should define how to join the tables by using the WHERE clause.

E.G.
SELECT Employees.EmployeeID, Employees.EmployeeName, Projects.ProjectTitle FROM Employees, Projects WHERE Employees.EmployeeID = Projects.EmployeeID

It is generally agreed that using WHERE to implicitly define a Join is confusing. Therefore some database systems do things differently. Here is the previous example rewritten in Access SQL:

E.G.
SELECT Employees.EmployeeID, Employees.EmployeeName, Projects.ProjectTitle FROM Employees LEFT JOIN Projects ON Employees.EmployeeID = Projects.EmployeeID.

Access has three JOIN commands:
LEFT JOIN: e.g. All Employees, even those without Projects.
RIGHT JOIN : e.g. All Projects, even those without Employees.
INNER JOIN : e.g. Only Employees who have Projects, and vice-versa.

CREATE VIEW

Use to create a virtual table which is the output of a SELECT statement.

E.G.
CREATE VIEW ProjectEmployees AS SELECT Employees.EmployeeID, Employees.EmployeeName, Projects.ProjectTitle FROM Employees, Projects WHERE Employees.EmployeeID = Projects.EmployeeID

This View (e.g. ProjectEmployees) can be used as if it were a Table which contained the specified Fields (e.g. EmployeeID, EmployeeName, ProjectTitle) and the specified records. The data will change if the underlying Table changes.

Views can be based on other Views, not just Tables.

Other Commands

There are several other commands for defining table structure, changing data, granting security access etc, but the commands listed above are the most used.

Copyright © Murray Cumming.

This work is licensed under a Creative Commons License.