Introduction To Structured Query Language

Peter Kitson

ISBN : -

Order a printed copy of this book from Amazon --UNAVAILABLE--


Cover Design - Introduction To Structured Query Language
  

For your free electronic copy of this book please verify the numbers below. 

(We need to do this to make sure you're a person and not a malicious script)

Numbers

 



Sample Chapter From Introduction To Structured Query Language
     Copyright © James Hoffman



Introduction


This page is a introductory tutorial of the 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. It is recommended that you print this page, so that you can easily refer back to previous examples.

Basics of the SELECT Statement

In a relational database, data is stored in tables. An example table would relate Social Security Number, Name, and Address:
EmployeeAddressTable
SSN FirstName LastName Address City State
512687458 Joe Smith  83 First Street Howard Ohio 
758420012 Mary Scott  842 Vine Ave. Losantiville Ohio 
102254896 Sam Jones  33 Elm St. Paris New York 
876512563 Sarah Ackerman  440 U.S. 110 Upton Michigan 

Now, let's say you want to see the address of each employee. Use the SELECT statement, like so:

SELECT FirstName, LastName, Address, City, State
FROM EmployeeAddressTable;

The following is the results of your query of the database:
First Name Last Name Address City State
Joe Smith 83 First Street  Howard Ohio
Mary Scott 842 Vine Ave.  Losantiville Ohio
Sam Jones 33 Elm St.  Paris New York
Sarah Ackerman 440 U.S. 110  Upton Michigan

To explain what you just did, you asked for the all of data in the EmployeeAddressTable, and specifically, you asked for the columns called FirstName, LastName, Address, City, and State. Note that column names and table names do not have spaces...they must be typed as one word; and that the statement ends with a semicolon (;). The general form for a SELECT statement, retrieving all of the rows in the table is:

SELECT ColumnName, ColumnName, ...
FROM TableName;

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. 

Conditional Selection

To further discuss the SELECT statement, let's look at a new example table (for hypothetical purposes only):
EmployeeStatisticsTable
EmployeeIDNo Salary Benefits Position
010 75000 15000  Manager
105 65000 15000  Manager
152 60000 15000  Manager
215 60000 12500  Manager
244 50000 12000  Staff
300 45000 10000  Staff
335 40000 10000  Staff
400 32000 7500  Entry-Level
441 28000 7500  Entry-Level

Relational Operators

There are six Relational Operators in SQL, and after introducing them, we'll see how they're used:
= Equal
< or != (see manual) Not Equal 
< Less Than
> Greater Than
<= Less Than or Equal To
>= Greater Than or Equal To 

The WHERE clause is used to specify that only certain rows of the table are displayed, based on the criteria described in that WHERE clause. It is most easily understood by looking at a couple of examples.

If you wanted to see the EMPLOYEEIDNO's of those making at or over $50,000, use the following:

SELECT EMPLOYEEIDNO
FROM EMPLOYEESTATISTICSTABLE
WHERE SALARY >= 50000;

Notice that the >= (greater than or equal to) sign is used, as we wanted to see those who made greater than $50,000, or equal to $50,000, listed together. This displays:

EMPLOYEEIDNO
------------
010
105
152
215
244

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 columns:

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.