SQL: a brief
overview
Structured Querry Language = A language to access
databases
In order to alter, insert or retrieve data from a database,
you will use a specialized Database language. Most modern
databases will allow you to do this via SQL or a version
of SQL. SQL stands for Structured Query Language and is a
standard for accessing and updating data in a database (once you
are connected to it....you will connect either via a database tool
or write a program to do this).
SQL is a conceptually easy language to understand.
We will not be teaching SQL in this class...but, only using simple
commands to access it. Note below table
is the name of the table you are accessing.
SOME SIMPLE SQL COMMANDS TO MANIPULATE
DATA IN A TABLE.
>see reading and above liks "More on SQL" for additional
commands.
| SQL command |
Meaning |
|
SELECT
select * from table
|
select all the keys/fields from the table |
| select Distribute from table |
select only the field Distribute from the table |
select * from table
where Name is 'Butch Grewe' |
Select all keys/fields from table where the key/field Name
= Butch Grewe |
|
INSERT
insert into table
values ('James Bond', '1 Eiffel Tower', 'Paris', 'France','Spy
Software')
|
Insert into the table, the data entry having
the following key/field entries in order:
Name = James Bond
Street Address = 1 Eiffel Tower
City = Paris
State = France (huh???)
Distribute = Spy Software |
|
UPDATE
update table set age=6 where Name='Butch Grewe'
|
updates any records with the name 'Butch Grewe'
to have the value of 6 for the field age. |
|
DELETE
delete from table where age=10
|
From table, deletes all records/rows that have
the field of age with the value 10. |
|
DESCRIBE
describe table
|
List all of the keys/fields that makeup this
table. |
|
the where clause....
| where a1=x and a2=y |
ALL of the conditions must be true |
| where a1=x or a1=y |
Only one of the conditions need to be true |
| where a1=x or a2 >z |
Only one of the conditions need to be true |
| where a1 between x and y |
Range of values |
| where a1 not between x and y |
NOT in range of values |
| where a1 in (x, y, z) |
Must be in the list of items |
| where a1 not in (x,y,z) |
Must NOT be in the list of items. |
| where a1 != x |
Must be NOT equal to value. |
| where a1 like
'M%' |
Pattern Search....means a1 must
start with a capital M. |
| where a1 like '%son%' |
Pattern Search....means a1 must have somewhere
in its value the string 'son'. Examples that would be
good: Furgeson, Forsonning, etc. |
| where a1 not like 'M%' |
Pattern Search....means a1 does NOT start
with a capital M. |
| where a1 like '%son%' |
Pattern Search....means a1 must have somewhere
in its value the string 'son'. Examples that would be
good: Furgeson, Forsonning, etc. |
|
|
the order by clause
select * from table order by name
|
Order alpahbetically in descending order the
records in the table, by their value stored in the column name. |
Creating a Table
Using SQL*Plus to create a Table
below are two examples of creating tables. The second
specifies the tablespace the table should appear in, used
if you have more than one tablespace. It also specifies the
initial size, how it grows in size and minimum number of extents
allocated for it.
|
|
SQL> create table customer(
last_name varchar2(30)
not null,
state_cd varchar(2),
sales
number);
|
|
SQL> create table products(
name
varchar2(30) not null,
description varchar2(300),
price number(4,2)
)
tablespace productspace
storage(initial 25k next 25k minextents 1);
|
Altering a Table
Using SQL*Plus to alter a Table
Use the SQL alter command to add or drop columns in a
table after it has been created.
Suppose we have a table customer
that has the following fields: last_name, state_CD, sales
|
|
To add a column tax_exempt_id
SQL>alter table customer
add tax_exempt_id
varchar2(20);
|
|
To drop the column sales
SQL> alter table customer
drop column sales;
|
|