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. This tutorial will provide you with the
instruction on the basics of each of these commands as well as allow you to
put them to practice using the SQL Interpreter.

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 New 80 72
 Mexico

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';

Select statement exercises

Enter select statements to:

1. Display the first name and age for everyone that's in the table.
2. Display the first name, last name, and city for everyone that's not from
Payson.
3. Display all columns for everyone that is over 40 years old.
4. Display the first and last names for everyone whose last name ends in an
"ay".
5. Display all columns for everyone whose first name equals "Mary".
6. Display all columns for everyone whose first name contains "Mary".

Selecting Data Answers

 1. Display everyone's first name and their age for everyone that's in
table.
2. select first,
3. age
from empinfo;

4. Display the first name, last name, and city for everyone that's not
from Payson.
5. select first,
6. last,
7. city
8. from empinfo
9. where city <>
'Payson';

10. Display all columns for everyone that is over 40 years old.
11. select * from empinfo
where age > 40;

12. Display the first and last names for everyone whose last name
ends in an "ay".
13. select first, last from empinfo
where last LIKE '%ay';

14. Display all columns for everyone whose first name equals
"Mary".
15. select * from empinfo
where first = 'Mary';

16. Display all columns for everyone whose first name contains
"Mary".
17. select * from empinfo
where first LIKE '%Mary%';

Creating Tables
The create table statement is used to create a new table. Here is the
format of a simple create table statement:

create table "tablename"

("column1" "data type",
"column2" "data type",
"column3" "data type");

Format of create table if you were to use optional constraints:

 create table "tablename"
("column1" "data type"
[constraint],
"column2" "data type"
[constraint],
"column3" "data type"
[constraint]);
[ ] = optional

Note: You may have as many columns as you'd like, and the constraints are
optional.

Example:

create table employee

(first varchar(15),
last varchar(20),
age number(3),
address varchar(30),
city varchar(20),
state varchar(20));

To create a new table, enter the keywords create table followed by the
table name, followed by an open parenthesis, followed by the first column
name, followed by the data type for that column, followed by any optional
constraints, and followed by a closing parenthesis. It is important to make
sure you use an open parenthesis before the beginning table, and a closing
parenthesis after the end of the last column definition. Make sure you
seperate each column definition with a comma. All SQL statements should
end with a ";".

The table and column names must start with a letter and can be followed by
letters, numbers, or underscores - not to exceed a total of 30 characters in
length. Do not use any SQL reserved keywords as names for tables or
column names (such as "select", "create", "insert", etc).

Data types specify what the type of data can be for that particular column. If
a column called "Last_Name", is to be used to hold names, then that
particular column should have a "varchar" (variable-length character) data
type.

Here are the most common Data types:

Fixed-length character string. Size is specified in parenthesis. Max

char(size)
255 bytes.

varchar(size)
Variable-length character string. Max size is specified in
 parenthesis.

Number value with a max number of column digits specified in

number(size)
parenthesis.

date Date value

Number value with a maximum number of digits of "size" total, with

number(size,d)
a maximum number of "d" digits to the right of the decimal.

What are constraints? When tables are created, it is common for one or
more columns to have constraints associated with them. A constraint is
basically a rule associated with a column that the data entered into that
column must follow. For example, a "unique" constraint specifies that no two
records can have the same value in a particular column. They must all be
unique. The other two most popular constraints are "not null" which specifies
that a column can't be left blank, and "primary key". A "primary key"
constraint defines a unique identification of each record (or row) in a table.
All of these and more will be covered in the future Advanced release of this
Tutorial. Constraints can be entered in this SQL interpreter, however, they
are not supported in this Intro to SQL tutorial & interpreter. They will be
covered and supported in the future release of the Advanced SQL tutorial -
that is, if "response" is good.

It's now time for you to design and create your own table. You will use this
table throughout the rest of the tutorial. If you decide to change or redesign
the table, you can either drop it and recreate it or you can create a
completely different one. The SQL statement drop will be covered later.

Create Table Exercise

You have just started a new company. It is time to hire some employees.
You will need to create a table that will contain the following information
about your new employees: firstname, lastname, title, age, and salary. After
you create the table, you should receive a small form on the screen with the
appropriate column names. If you are missing any columns, you need to
double check your SQL statement and recreate the table. Once it's created
successfully, go to the "Insert" lesson.

IMPORTANT: When selecting a table name, it is important to select a

unique name that no one else will use or guess. Your table names should
have an underscore followed by your initials and the digits of your birth day
and month. For example, Tom Smith, who was born on November 2nd,
would name his table myemployees_ts0211 Use this convention for all of the
tables you create. Your tables will remain on a shared database until you
drop them, or they will be cleaned up if they aren't accessed in 4-5 days. If
"support" is good, I hope to eventually extend this to at least one week.
When you are finished with your table, it is important to drop your table
(covered in last lesson).

Creating Tables Answer

Your create statement should resemble:

create table
myemployees_ts0211
(firstname varchar(30),
lastname varchar(30),
title varchar(30),
age number(2),
salary number(8,2));

Inserting into a Table

The insert statement is used to insert or add a row of data into the table.

To insert records into a table, enter the key words insert into followed by
the table name, followed by an open parenthesis, followed by a list of
column names separated by commas, followed by a closing parenthesis,
followed by the keyword values, followed by the list of values enclosed in
parenthesis. The values that you enter will be held in the rows and they will
match up with the column names that you specify. Strings should be
enclosed in single quotes, and numbers should not.

insert into "tablename"

(first_column,...last_column)
values (first_value,...last_value);

In the example below, the column name first will match up with the
value 'Luke', and the column name state will match up with the
value 'Georgia'.

Example:

insert into employee

(first, last, age, address, city, state)
values ('Luke', 'Duke', 45, '2130 Boars Nest',
'Hazard Co', 'Georgia');
Note: All strings should be enclosed between single quotes: 'string'

Insert statement exercises

It is time to insert data into your new employee table.

Your first three employees are the following:

Jonie Weber, Secretary, 28, 19500.00

Potsy Weber, Programmer, 32, 45300.00
Dirk Smith, Programmer II, 45, 75020.00

Enter these employees into your table first, and then insert at least 5 more
of your own list of employees in the table.

After they're inserted into the table, enter select statements to:

1. Select all columns for everyone in your employee table.

2. Select all columns for everyone with a salary over 30000.
3. Select first and last names for everyone that's under 30 years old.
4. Select first name, last name, and salary for anyone with "Programmer" in
their title.
5. Select all columns for everyone whose last name contains "ebe".
6. Select the first name for everyone whose first name equals "Potsy".
7. Select all columns for everyone over 80 years old.
8. Select all columns for everyone whose last name ends in "ith".

Create at least 5 of your own select statements based on specific

information that you'd like to retrieve.

Inserting into a Table Answers

Your Insert statements should be similar to: (note: use your own table name
that you created)

insert into
myemployees_ts0211
(firstname, lastname,
title, age, salary)
values ('Jonie', 'Weber',
'Secretary', 28,
19500.00);

1. Select all columns for everyone in your employee table.

select * from
myemployees_ts0211
 2. Select all columns for everyone with a salary over 30000.

select * from
myemployees_ts0211
where salary > 30000

3. Select first and last names for everyone that's under 30 years old.

select firstname, lastname

from myemployees_ts0211
where age < 30

4. Select first name, last name, and salary for anyone with "Programmer"
in their title.

select firstname, lastname, salary

from myemployees_ts0211
where title LIKE '%Programmer%'

5. Select all columns for everyone whose last name contains "ebe".

select * from
myemployees_ts0211
where lastname LIKE '%ebe%'

6. Select the first name for everyone whose first name equals "Potsy".

select firstname from

myemployees_ts0211
where firstname = 'Potsy'

7. Select all columns for everyone over 80 years old.

select * from
myemployees_ts0211
where age > 80

8. Select all columns for everyone whose last name ends in "ith".

select * from
myemployees_ts0211
where lastname LIKE '%ith'

Deleting Records
The delete statement is used to delete records or rows from the table.

delete from "tablename"

 where "columnname"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];

[ ] = optional

[The above example was line wrapped for better viewing on this Web page.]

Examples:

delete from employee;

Note: if you leave off the where clause, all records will be deleted!

delete from employee

where lastname = 'May';

delete from employee

where firstname = 'Mike' or firstname = 'Eric';

To delete an entire record/row from a table, enter "delete from" followed by

the table name, followed by the where clausewhich contains the conditions to
delete. If you leave off the where clause, all records will be deleted.

Delete statement exercises

(Use the select statement to verify your deletes):

1. Jonie Weber-Williams just quit, remove her record from the table.
2. It's time for budget cuts. Remove all employees who are making over 70000
dollars.

Create at least two of your own delete statements, and then issue a
command to delete all records from the table.

Deleting Records Answers

1. Jonie Weber-Williams just quit, remove her record from the table:

delete from myemployees_ts0211

where lastname =
'Weber-Williams';

2. It's time for budget cuts. Remove all employees who are making over
70000 dollars.

Delete from myemployees_ts0211

 where salary >
70000;

Drop a Table
The drop table command is used to delete a table and all rows in the table.

To delete an entire table including all of its rows, issue the drop
table command followed by the tablename. drop table is different from
deleting all of the records in the table. Deleting all of the records in the table
leaves the table including column and constraint information. Dropping the
table removes the table definition as well as all of its rows.

drop table "tablename"

Example:

drop table myemployees_ts0211;

Drop Table exercises

1. Drop your employee table.