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 as we will do via Java&JDBC).
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 |
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....
|
|||||||||||||||||||||||||
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 Tablebelow 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(
|
SQL> create table products(
|
Altering a Table
Using SQL*Plus to alter a TableUse the SQL alter command to add or drop columns in a table after it has been created.
|
To add a column tax_exempt_id SQL>alter table customer
|
To drop the column sales SQL> alter table customer
|