Copyright 1996-1999, James Hoffman
Introduction to Structured Query Language
This page is a 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 datain 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 SQLfeatures 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.
Table of Contents
Basics of the SELECT Statement Conditional Selection Relational Operators Compound Conditions IN & BETWEEN Using LIKE Joins Keys Performing a Join Eliminating Duplicates Aliases &In/Subqueries Aggregate Functions Views Creating New Tables Altering Tables Adding Data Deleting Data Updating Data Indexes GROUP BY & HAVING More Subqueries EXISTS & ALL UNION & Outer Joins Embedded SQL Common SQL Questions Nonstandard SQL Syntax Summary Important Links
Basics of the SELECT Statement
In a relational database, data is stored in tables. An example table would relate Social SecurityNumber, 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: SELECTFirstName, 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 theEmployeeAddressTable, 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 getall 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.
To further discuss the SELECT statement, let's look at a newexample 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
There are six Relational Operators in SQL, and afterintroducing them, we'll see how they're used: = Equal <> or != (see Not Equal manual) < 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...