What is SQL?

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.

Table Basics

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "weather".

city, state, high, and low are the columns. The rows contain the data for this table:
Weather
city                   state    high    low
Phoenix          Arizona    105    90
Tucson           Arizona    101    92
Flagstaff         Arizona    88    69
San Diego     California    77    60
Albuquerque  NewMexico    80    72

Selecting Data

The select statement is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement:
select "column1"
  [,"column2",etc]
  from "tablename"
  [where "condition"];
  [] = optional

The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns.

The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results.

The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where.

Conditional selections used in the where clause:=    Equal
>    Greater than
<    Less than
>=    Greater than or equal
<=    Less than or equal
<>    Not equal to
LIKE    *See note below


The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example:
select first, last, city
   from empinfo
   where first LIKE 'Er%';

This SQL statement will match any first names that start with 'Er'. Strings must be in single quotes.

Or you can specify,
select first, last
   from empinfo
   where last LIKE '%s';

This statement will match any last names that end in a 's'.
select * from empinfo
   where first = 'Eric';

This will only select rows where the first name equals 'Eric' exactly.Sample Table: empinfo
first    last    id    age    city    state
John    Jones    99980    45    Payson    Arizona
Mary    Jones    99982    25    Payson    Arizona
Eric    Edwards    88232    32    San Diego    California
Mary Ann    Edwards    88233    32    Phoenix    Arizona
Ginger    Howell    98002    42    Cottonwood    Arizona
Sebastian    Smith    92001    23    Gila Bend    Arizona
Gus    Gray    22322    35    Bagdad    Arizona
Mary Ann    May    32326    52    Tucson    Arizona
Erica    Williams    32327    60    Show Low    Arizona
Leroy    Brown    32380    22    Pinetop    Arizona
Elroy    Cleaver    32382    22    Globe    Arizona


Enter the following sample select statements in the SQL Interpreter Form at the bottom of this page. Before you press "submit", write down your expected results. Press "submit", and compare the results.
select first, last, city from empinfo;

select last, city, age from empinfo
       where age > 30;

select first, last, city, state from empinfo
       where first LIKE 'J%';

select * from empinfo;

select first, last, from empinfo
       where last LIKE '%s';

select first, last, age from empinfo
       where last LIKE '%illia%';

select * from empinfo where first = 'Eric';