Structured Query Language (SQL)
"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.
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
state, high, and low are the columns. The rows contain the data for this table:
city state high low
Phoenix Arizona 105 90
Tucson Arizona 101 92
Flagstaff Arizona 88 69
Diego California 77 60
Albuquerque New Mexico 80 72
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:
"column1"[,"column2",etc] from "tablename"
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.
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.
selections used in where clause:
than or equal to
than or equal to
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:
first, last, city
first LIKE "Er%";
SQL statement will match any first names that start with "Er". Strings must be in single quotes.
you can specify,
last LIKE "%s";
statement will match any last names that end in a "s".
* from empinfo
first = "Eric";
will only select rows where the first name equals "Eric" exactly.
table called "empinfo"
first last id age city state
John Jones 99980 45 Payson Arizona
Mary Jones 99982 25 Payson Arizona
Eric Edwards 88232 32 San
Ann Edwards 88233 32 Phoenix Arizona
Ginger Howell 98002 42 Cottonwood Arizona
Sebastian Smith 92001 23 Gila
Gus Gray 22322 35 Bagdad Arizona
Ann May 32326 52 Tucson Arizona
Erica Williams 32327 60 Show
Leroy Brown 32380 22 Pinetop Arizona
Elroy Cleaver 32382 22 Globe Arizona
the following sample select statements in the SQL Interpreter Form at the
bottom of this page.
you press "submit", write down your expected results. Press
"submit", and compare the results.
first, last, city from empinfo;
last, city, age from empinfo
age > 30;
first, last, city, state from empinfo
first LIKE "J%";
* from empinfo;
first, last, from empinfo
last LIKE "%s";
first, last, age from empinfo
last LIKE "%illia%";
* from empinfo where first = "Eric";
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".
The create table statement is used to
create a new table. Here is the format of a simple create table statement:
of create table if you were to use optional constraints:
"data type" [constraint],
"data type" [constraint],
"data type" [constraint]);
[ ] =
You may have as many columns as you"d like, and the constraints are optional.
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 ";".
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).
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)
are the most common Data types:
Fixed-length character string. Size is specified in parenthesis. Max 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 parenthesis.
number(size,d) Number value with a maximum number of digits of "size" total,
with a maximum number of "d" digits to the right of the decimal.
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"
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.
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
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).
The insert statement is used to insert or
add a row of data into the table.
last, age, address, city, state)
("Luke", "Duke", 45, "2130 Boars Nest", "Hazard Co", "Georgia");
Note: All strings should be enclosed between single quotes: "string"
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.
the example above, the column name first
will match up with the value "Luke", and the column name state will match up with the value "Georgia".
time to insert data into your new employee table.
first three employees are the following:
Weber, Secretary, 28, 19500.00
Weber, Programmer, 32, 45300.00
Smith, Programmer II, 45, 75020.00
these employees into your table first, and then insert at least 5 more of your
own list of employees in the table.
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".
at least 5 of your own select statements based on specific information that
you"d like to retrieve.
The update statement is used to update or
change records that match a specified criteria. This is accomplished by
carefully constructing a where clause.
"columnname" = "newvalue"[,"nextcolumn" =
"columnname" OPERATOR "value" [and|or "column"
area_code = 623
prefix = 979;
last_name = "Smith", prefix=555, suffix=9292
last_name = "Jones";
age = age+1
first_name="Mary" and last_name="Williams";
each update, issue a select statement to verify your changes).
1 Jonie Weber just got married
to Bob Williams. She has requested that her last name be updated to
2 Dirk Smith"s birthday is
today, add 1 to his age.
3 All secretaries are now
called "Administrative Assistant". Update all titles accordingly.
4 Everyone that"s making under
30000 are to receive a 3500 a
5 Everyone that"s making over
33500 are to receive a 4500 a
6 All "Programmer II"
titles are now promoted to "Programmer III".
7 All "Programmer"
titles are now promoted to "Programmer II".
at least 5 of your own update statements and submit them.
The delete statement is used to delete
records or rows from the table.
"columnname" OPERATOR "value" [and|or "column"
[ ] =
if you leave off the where clause, all
records will be deleted!
lastname = "May";
firstname = "Mike" or firstname = "Eric";
delete an entire record/row from a table, enter "delete from"
followed by the table name, followed by the where clause which contains the
conditions to delete. If you leave off the where clause, all records will be
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.
at least two of your own delete statements, and then issue a command to delete
all records from the table.
The drop table command is used to delete a
table and all rows in the table.
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.