Structured Query Language (also known as SQL) and is a pioneering effort on the World Wide Web, as this is the first comprehensive SQL tutorial available on the Internet. SQL allows users to access data in relational database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data. This page will describe how to use SQL, and give examples. The SQL used in this document is "ANSI", or standard SQL, and no SQL features of specific database management systems will be discussed until the "Nonstandard SQL" section.
Basics of the SELECT Statement
SELECT FirstName, LastName, Address, City, State
To get all columns of a table without typing all column names, use:
SELECT * FROM TableName;
Each database management system (DBMS) and database software has different methods for logging in to the database and entering SQL commands; see
the local computer "guru" to help you get onto the system, so that you can use SQL.
SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE SALARY >= 50000;
The WHERE description, SALARY >= 50000, is known as a condition (an operation which evaluates to True or False). The same can be done for text
SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE POSITION = 'Manager';
This displays the ID Numbers of all Managers. Generally, with text columns, stick to equal to or not equal to, and make sure that any text that
appears in the statement is surrounded by single quotes ('). Note: Position is now an illegal identifier because it is now an unused, but
reserved, keyword in the SQL-92 standard.
Complex Conditions: Compound Conditions / Logical Operators
The AND operator joins two or more conditions, and displays a row only if that row's data satisfies ALL conditions listed (i.e. all conditions
hold true). For example, to display all staff making over $40,000, use:
SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE SALARY > 40000 AND POSITION = 'Staff';
The OR operator joins two or more conditions, but returns a row if ANY of the conditions listed hold true. To see all those who make less than
$40,000 or have less than $10,000 in benefits, listed together, use the following query:
SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE SALARY < 40000 OR BENEFITS < 10000;
AND & OR can be combined, for example:
SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE POSITION = 'Manager' AND SALARY > 60000 OR BENEFITS > 12000;
First, SQL finds the rows where the salary is greater than $60,000 and the position column is equal to Manager, then taking this new list of rows,
SQL then sees if any of these rows satisfies the previous AND condition or the condition that the Benefits column is greater than $12,000.
Subsequently, SQL only displays this second new list of rows, keeping in mind that anyone with Benefits over $12,000 will be included as the OR
operator includes a row if either resulting condition is True. Also note that the AND operation is done first.
To generalize this process, SQL performs the AND operation(s) to determine the rows where the AND operation(s) hold true (remember: all of the
conditions are true), then these results are used to compare with the OR conditions, and only display those remaining rows where any of the
conditions joined by the OR operator hold true (where a condition or result from an AND is paired with another condition or AND result to use to
evaluate the OR, which evaluates to true if either value is true). Mathematically, SQL evaluates all of the conditions, then evaluates the AND
"pairs", and then evaluates the OR's (where both operators evaluate left to right).
IN & BETWEEN
An easier method of using compound conditions uses IN or BETWEEN. For example, if you wanted to list all managers and staff:
SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE POSITION IN ('Manager', 'Staff');
or to list those making greater than or equal to $30,000, but less than or equal to $50,000, use:
SELECT EMPLOYEEIDNO FROM EMPLOYEESTATISTICSTABLE WHERE SALARY BETWEEN 30000 AND 50000;
Look at the EmployeeStatisticsTable, and say you wanted to see all people whose last names started with "S"; try:
SELECT EMPLOYEEIDNO FROM EMPLOYEEADDRESSTABLE WHERE LASTNAME LIKE 'S%';
The percent sign (%) is used to represent any possible character (number, letter, or punctuation) or set of characters that might appear after the
"S". To find those people with LastName's ending in "S", use '%S', or if you wanted the "S" in the middle of the word, try '%S%'. The '%' can be
used for any characters in the same position relative to the given characters. NOT LIKE displays rows not fitting the given description. Other
possibilities of using LIKE, or any of these discussed conditionals, are available, though it depends on what DBMS you are using; as usual,
consult a manual or your system manager or administrator for the available features on your system, or just to make sure that what you are trying
to do is available and allowed. This disclaimer holds for the features of SQL that will be discussed below. This section is just to give you an
idea of the possibilities of queries that can be written in SQL.
DISTINCT and Eliminating Duplicates
Let's say that you want to list the ID and names of only those people who have sold an antique. Obviously, you want a list where each seller is
only listed once--you don't want to know how many antiques a person sold, just the fact that this person sold one (for counts, see the Aggregate
Function section below). This means that you will need to tell SQL to eliminate duplicate sales rows, and just list each person only once. To do
this, use the DISTINCT keyword.
First, we will need an equijoin to the AntiqueOwners table to get the detail data of the person's LastName and FirstName. However, keep in mind
that since the SellerID column in the Antiques table is a foreign key to the AntiqueOwners table, a seller will only be listed if there is a row
in the AntiqueOwners table listing the ID and names. We also want to eliminate multiple occurrences of the SellerID in our listing, so we use
DISTINCT on the column where the repeats may occur (however, it is generally not necessary to strictly put the Distinct in front of the column
To throw in one more twist, we will also want the list alphabetized by LastName, then by FirstName (on a LastName tie). Thus, we will use the
ORDER BY clause:
SELECT DISTINCT SELLERID, OWNERLASTNAME, OWNERFIRSTNAME FROM ANTIQUES, ANTIQUEOWNERS WHERE SELLERID = OWNERID ORDER BY OWNERLASTNAME,
Miscellaneous SQL Statements
I will discuss five important aggregate functions: SUM, AVG, MAX, MIN, and COUNT. They are called aggregate functions because they summarize the
results of a query, rather than listing all of the rows.
- SUM () gives the total of all the rows, satisfying any conditions, of the given column, where the given column is numeric.
- AVG () gives the average of the given column.
- MAX () gives the largest figure in the given column.
- MIN () gives the smallest figure in the given column.
- COUNT(*) gives the number of rows satisfying the conditions.
Looking at the tables at the top of the document, let's look at three examples:
SELECT SUM(SALARY), AVG(SALARY)
This query shows the total of all salaries in the table, and the average salary of all of the entries in the table.
WHERE POSITION = 'Manager';
This query gives the smallest figure of the Benefits column, of the employees who are Managers, which is 12500.
WHERE POSITION = 'Staff';