Introduction to Structured Query Language

Only available on StudyMode
  • Download(s) : 96
  • Published : April 6, 2012
Open Document
Text Preview
Introduction to Structured Query Language
Version 4.66
Copyright 1996-2001, James Hoffman
jhoffman@one.net

SQL Tutorial

04/07/2001

Introduction to Structured Query Language
Version 4.66 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. Also, you may be interested in joining the new SQL Club on Yahoo!, where you can read or enter messages in a SQL forum.

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 http://w3.one.net/~jhoffman/sqltut.htm Page 1

SQL Tutorial

04/07/2001

Common SQL Questions Nonstandard SQL Syntax Summary Exercises Important Links

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 Smith Scott Jones City State Ohio

512687458 Joe 758420012 Mary 102254896 Sam 876512563 Sarah

83 First Street Howard

842 Vine Ave. Losantiville Ohio 33 Elm St. Paris New York Michigan

Ackerman 440 U.S. 110 Upton

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 Joe Mary Sam Sarah Smith Scott Jones City State Ohio

83 First Street Howard

842 Vine Ave. Losantiville Ohio 33 Elm St. Paris New York Michigan

Ackerman 440 U.S. 110 Upton

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 http://w3.one.net/~jhoffman/sqltut.htm Page 2

SQL Tutorial

04/07/2001

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 010 105 152 215 244 300 335 400 441 Salary 75000 65000 60000 60000 50000 45000 40000 32000 28000 Benefits 15000 15000 15000 12500 12000 10000 10000 7500 7500 Position Manager Manager Manager Manager Staff Staff Staff Entry-Level Entry-Level

Relational Operators There are six Relational Operators in SQL, and after introducing them,...
tracking img